Once you've built your income statement forecast you might feel like you're ready to dive in and build your balance sheet forecast straight away. But there are a couple of items that we need to build some extra calculations for, these often non-current assets, in this case, property, plant, and equipment and intangible assets and also equity. For both of these, we're gonna use base analysis to help us forecast the ending figure that we can then include in our forecast balance sheet. Base stands for beginning, add, subtract, ending, helping us to remember that we can forecast the ending balance sheet value, starting with the beginning balance sheet value. I'm gonna start with the base calculation for PP&E and intangible assets. And the first thing that I'm going to do is to link the ending balance in the last historical year into the balance sheet value.

That's 7,826. We refer to this as anchoring our formula, as it means that we will always start our base analysis with a correct historical figure. Never be tempted to try and build base analysis in the previous historical years, as things like M&A and foreign currency will mean that your ending balance in your last historical year won't match what's in the balance sheet. We now ask ourselves, what is the beginning balance in our first forecast year? This must be the ending balance from the previous year. So I'm just gonna link this cell to the anchored value.

We now ask ourselves, what will increase PP&E and intangible assets during the year? And the answer is capex, or capital expenditure. And we can forecast this using the capex assumption at the top of the model.

We can see here that this is forecast as a percentage of revenues on the basis that as a company's revenues grow, its capex would also grow. So let's build our capex forecast.

We grab our assumption and then multiply this by forecast revenue, giving forecast capex of 568.9. We can now ask ourselves what would reduce PP&E and intangibles during the year? And the answer is depreciation and amortization, or D&A. Now let's have a look at the assumption for this.

If we scroll up, we can see that D&A is forecast as a percentage of prior year PP&E and intangible assets on the basis that as the asset balance grows, D&A will also increase. Let's forecast our D&A.

So we grab our assumption and then multiply this by the prior year ending balance.

Now, one thing I need to remember is that this needs to be subtracted from my base calculation. So I'm gonna go back into my formula and multiply this by minus one. So it's now showing as a negative. This means that when I move to the next row, I can then use Alt + Equals to AutoSum all of the rows above, and I now have the ending balance on my PP&E and intangible assets of 7,995.8. Let's now build a similar calculation for equity.

And as before, I'll start with anchoring my ending balance in my balance sheet.

And that then becomes my beginning balance in my first forecast year.

What makes that equity increase during the year? Of course, that's net income, and we can grab that from the bottom of our forecast income statement.

What makes equity decrease during the year? That's dividends, and we can get that from our dividend assumption.

We can see that dividends are forecast as a payout ratio. That's as a percentage of net income. So let's forecast our dividends.

So grab your assumption and then multiply that by net income and then multiply that by minus one so that we're subtracting the dividends from our base calculation. We can then AutoSum, Alt + Equals, to give the ending balance in our equity of 3,205.5.

Once we've finished our base calculations, we can just copy them over to the right, so we have five years of forecast figures. So select all of your year-one values and then select over to column J and Control + R.

And we've now finished our balance sheet calculations.