The first thing we want to do in a model is make sure the historical information is correct. This model already has historical information in, particularly for revenues, COGS, et cetera, but it doesn't have any subtotals yet. What we want to do is put all of those subtotals in and this is super important because we can then check it to the company's historical accounts. Make sure the subtotal figures are correct, then we can copy our subtotal formulas to the right into the forecast period and we know those formulas are correct. So we want the subtotal figures to be correct and we then know that our subtotal formulas are correct. So we start with Gross Profit. I want to sum the items above. I'm going to use Alt Equals to do that or if I was on a Mac, I'd use Command Shift T. I can then select the cells to the right that I want to copy into and I'm gonna copy it into the forecast period as well, the projected period. I'm then gonna press Control R to copy it into the forecast period. With EBIT, I can't use Alt equals because I need to jump over this gap. So I'm gonna type in the sum formula manually, copy it to the right with Control R and EBITDA. Similar again, total up the two above and the EBIT.

Now this is a slightly unusual income statements because we've got EBITDA after EBIT. You have to think of this a bit like a footnote as a useful number that's really handy for us to have but you wouldn't normally put it underneath EBIT in an income statement. So when we come to do our next subtotal, our earnings before tax, I'm going to sum those three items there but I'm not going to include EBITDA here. I'm going to include EBIT. So earnings before interest and tax and then we take off the interest. Copy it to the right. That then enables me to get down to Earnings From Continuing Operations, Then Net Income, which takes Earnings From Continuing Operations and then includes Discontinued Earnings.

And that gets us all the way down to the income statements. We now need to do exactly the same thing in the balance sheet. So by our Total Current Assets, I can press Alt Equals or Command Shift T, copy it to the right. And I've now got my current assets. For Total Assets, I'm gonna have to type in the sum formula manually, include the Current Assets and those Non-Current Assets, Current Liabilities, ALT Equals, copy to the right.

Total Liabilities include the Current Liabilities and the Non Currents, and then Total Liabilities and Equity.

An important part in any balance sheet is always making sure that it balances historically, then you've got a really good indication that your subtotals are working as expected. So I'm going to take my Total Assets subtract the Total Liabilities and Equity and hopefully I'll get a zero and I do. so initially I'm feeling really good. and if I copy that one to the right, I'm still feeling great, amazing. But if I copy it a little bit more to the right, oh no, I start to freak out. Why on earth, isn't it balancing? Well, something I notice is that the Long Term Debt has got these blue fonted hard coded numbers and that then changed into a black fonted zero. And you might notice it's actually linking down to a cell. This is a formula, and because it's a formula, we use a black font color. I've got exactly the same thing for my Short Term Borrowings going on. So let's go down to E211 and see what's going on there. We've got Short Term Borrowings here and then we've got the ability to create a subtotal for Long Term Debt.

This is really a device for us to introduce you to the Net Debt section of this model. I'm going to press Equals and E211 for Short Term Borrowings and I'm now going to show you where to find that. If I scroll up in row 143, we get to the Net Debt section and here we calculate the amount of cash we've got available to pay off debt. I want to go down to the debt section though, and here in row 160, E160 I can see I've got Ending Short Term borrowings 5, 225.

That's the figure that I want to appear at the bottom and that is now linking into the balance sheet. Great. But I can do exactly the same thing with my Long Term Debt. I'll tell you right now we've got three different debt tranches in our debt section. The first one is this 4.625% term loan. So I'm going to click on that, press comma and then I'm going to scroll down to the 3.125% bond, include that, press comma again and then I'm gonna scroll down to our third long term debt, the 6.375% term loan, and click on that. I close the brackets and press Enter. And I've got another 40,000 of long term debt debt there. So this was really a device to show you the Net Debt section and how we're going to aggregate up those debt items. Let's go back to the balance sheets. We've got those figures put in there now, so the short term borrowings is there, the long term debt of 40,000 is there and does the balance sheet balance? Hold your breath. Woo-hoo. It does. Amazing. (exhales) I'm feeling a lot better now. So this model is now all set up. I check my subtotals to the company's own subtotals. They look good. I'm feeling confident about my formulas.