Here, we want to start modeling the income statement. In particular, the top half to start with. I'm gonna start with revenues. To get my revenue, I'm going to need to take a growth figure from the Assumptions tab. So I'll press in equals, open brackets, one plus. Now, I'll go back to the Assumptions tab, and I can see in row 12, we've got the growth rate selected at 5.3%. That is the base case and we use an index function to return that in row 12. I'll close the brackets and then I want to multiply that by last year's revenue figure. So back to the Model 1 tab, select last year's revenue figure, and I've now got the revenue 576.9. Next, I'm gonna do something strange. I'm gonna skip over the operating costs and go to the EBITDA. I can get that from the EBITDA margin on the Assumptions tab. So here's our margin selected, it's in F18. I need to multiply that by revenue because margin is a percentage of revenue. So I scroll up, find that revenue, and I've now got my EBITDA. Now because I had the consensus estimate given to me of revenue and the consensus estimate given to me of EBITDA, I really wanted to use those figures, but it leaves me with this gap for operating costs and there's no assumption given. I didn't have to have an assumption here because I can just fill this gap, it's gonna be the plug between those two numbers. I want it to be shown as a negative, so I'll type in equals negative sign, and then open brackets, revenue, minus EBITDA.
Now, this does mean we have an unusual thing happening for the EBITDA formula. In column C, I'm summing up the items above, same in D, same in E, but then in column F, I have a completely different formula. I'm taking my assumption multiplied by the revenue. This means I've got inconsistent formulas within a row. This is something that does happen in modeling with estimate. We've just got to be aware of it and make sure we don't accidentally copy our EBITDA historical formula. Over to the right, we'd end up getting ourselves a circular and we certainly don't want that. I then carry on down to depreciation. And for depreciation, I want to go and do the PP&E calculation, the Property, Plant, and Equipment calculation, we've got that down in rows 28 to 31. This is an example of a base analysis. Base stands for beginning, and then you add a number, subtract a number to get to an ending number. We'll start with a historical ending PP&E and I can get that from the balance sheets.
The net PP&E here was 83.5.
Great, now that becomes last year's ending and I can link my next year beginning to that figure. I need to add the CapEx. I can go to my Asumptions tab, scroll down to my balance sheet assumptions, and I can see that CapEx is a percentage of revenues, and multiply that by, scroll up to find my revenue, 576.9.
And then for depreciation, again, I can go to my assumptions, it's in the income statement section and it's a percentage of the beginning net PP&E, so 27% multiplied by beginning net PP&E. I'm going to put a minus sign at the beginning, so it's shown as a negative, and now my ending net PP&E, I can sum the items above. Because I put that minus sign in for the depreciation, it means when I sum it, it effectively subtracts it.
Fantastic. I've got my PP&E. Hang on a moment to scratch my head. Why have I come here and done my PP&E? It was the depreciation. We're halfway through our income statement and it was the depreciation I really wanted to calculate here, but then use it in my income statement. So I scroll back up, my depreciation's here, if I just check last year's figure is negative so I want this year's figure to also be shown as a negative. We've now got that.
Amortization comes from the Assumptions tab.
It was negative historically, and then it's just a zero. Now, my EBIT formula, I can take last year's formula and just copy it to the right because I want the same thing to be happening here. You may find sometimes when you're modeling with estimates that you're given your projected EBIT figure. If that's the case, then you just want to be a little careful here. You probably won't be able to just copy your EBIT formula from the historicals into the projected period.
Income Statement - Top Half Empty
