Let's kick off. So first of all my name's Phil Spocks, and today is all about three statement modeling and modeling fundamentals.

And if I just jump into the into the next page, you can see that we're gonna do a little bit about setting up Excel.

We'll then kick off with a three statement model, a simple version.

We'll use mini max functions, and then we'll basically end up at a buffer, three statement model using circular references.

So, we're gonna spend the vast majority deep in Excel.

So, I'm hoping you've got Excel running on your machine.

I think it's a good idea to try and keep up and try and do what I'm doing.

But, if not just follow up on the screen.

The session is recorded so you can watch it again.

And then you'll have the availability of both the empty files and the full files from what we're going to do over the next hour.

So let me just rearrange things a little bit, minimize that outta the way.

Just going to look, if I can find where Zoom has put my just find where Zoom has put my I'm just gonna see if I can find, just gonna move things across a little bit and see if I can find the chat box there. It's okay.

My colleague, has a link up on the screen on, in the chat box, sorry.

We are going to be using today.

There should be initially three files there.

A simple setup file and then a simple modeling file.

Both an empty version and a full version.

And we're also in the, we're also gonna put up in the same location.

We're gonna put up the, the two tougher models both the empty and the full version.

And those should be there in a few minutes.

You might need to hit refresh on that, on our website.

So if you can grab those files make sure you've got copies of those files then, you can follow along.

You can see what we're see. What I'm doing I'm going to do is I'm going to open Excel and I'm gonna open the simple version there. We are modeling simple forecast empty, and it should look something like this.

Apologies, I need drag this across on the screen and I afford, what I'm doing.

So this this first page that you should see on the screen at the moment is about setting up Excel. It's about how to set up Excel, um, of the options. And you'll see at the top of the page here, I've basically said, how do, how you get into the Excel options.

And you can either click on the file menu and then options, and then advanced or alternative. If you wanna jump straight into this hold down the alt key and then press F and then T and that'll jump into the relevant section.

And I'll just show you what I'm going to do.

Apologies, Kevin, there we are.

Apologies. It's just moved this out of the way, which is not awfully helpful.

So, I've got Excel open on the screen. I'll make it a little bit bigger when we jump into the actual modeling itself.

So we need to go open to the file option up here.

And then we're going to go down to options.

I'm just as I said, you can do this with the keyboard shortcuts alt F T.

And the first thing we're going to do is we're going to go into formulate and I'm just gonna make sure that enable iterative calculations is turned off.

Second thing I'm going to do is I'm gonna go into the advanced menu here on the left hand side and to make you do and make sure you do to help you with using Excel and using Excel a little bit more efficiently.

First is top menu here.

Top button here, which says, after pressing enter, move selection down, I want you to turn that off.

So, basically it doesn't jump down into the next cell when you've finished inputting a calculation.

Because most what we want to do is you want to copy that to the right or copy it down so we, it, it's inefficient if we jump out of that cell.

The second thing I want you to do is to turn off this thing here where I'm pointing with my arrow that says, allow editing directly in sales.

Try and turn that off. And again, it forces you to go into the, into the formula bar to actually do your editing.

Now the other thing I'm just gonna click cancel on that. Just gonna move the bottom of this spreadsheet a little bit out the way.

So you can just see a little bit some hints here in terms of using the keyboard rather than using your mouse.

And it's really good for the speed and accuracy of your modeling to try and use the keyboard more and the mouse less.

So that's what we're gonna do.

And there's a little bit, there's some hints here in terms of good keyboard chalk.

There are loads of keyboard shortcuts and lots of you will know some, some of these already.

But here are the absolute key ones for modeling.

First of all, use the arrow keys for moving around and page up and down help as well for jumping between tabs.

And then use shift for highlighting sections rather than moving around with your mouse.

The second thing is C and control V pasting when you've done a formula of the lot al alt equals or sums sections of numbers.

And you'll use that a great deal at the bottom of columns or at the end, the right hand end alt equals is your friends.

And then finally two of my other favorites, control D, control R for copy down and copyright.

So when you've done a sum at the very bottom, and then just highlight where you want it to copy, want it to go.

And then do Control R and it'll copy and paste to the right.

Second thing I'm just going to do is I'm just, so another page on here just to give you an idea of your approach to modeling.

So good modeling goes something like this.

First of all, get the historic data and then build ratios and statistics on that historic data.

So from a set of accounts, you know, look at the ratio of things like costs to revenue and so on.

And they may not be what you forecast for the future, but it gives you a good start.

If a business is making a gross profit margin of say, 30% or 40% sensible to at least start a same mix of customers, the same mix of suppliers, the same way of working.

The, for that 30 or 40% gross profit margin is a good start for your forecast.

So you make those few assumptions often built on the historic ratios.

And then you move on to actually forecasting your financials.

In terms of the order, generally start with the income onto the balance sheet, and finally onto the cash flow.

When you do your balance sheet, you won't be able to have, you won't be able to put cash because you won't have done flow.

So when you finish the cash flow, then you link or wire that cash balance that you've calculated back into the balance sheet and all the time, check as you make sure your balance sheet balances and so on.

We'll do one other thing.

When we get onto our second we're gonna look, use a min and a max function, and where that cash goes, it's gonna go into the cash, if it's gonna go into the revolver line.

So without further ado, we're gonna jump straight into a nice, simple model.

And hopefully there it is.

Yes, it arrives on the screen.

So this is the first one I want you to download.

This is the, it says modeling simple forecast empty.

If you look up at the top of the screen where the title of the spreadsheet is.

So what we're gonna do, you'll see at the bottom that there are three simple one, simple two, and simple three.

We're gonna go for the middle one. We're gonna start with simple two, which is a relatively simple as the name implies a relatively simple three statement model.

So we're just gonna get our sort of, you know, our just gonna get our hands dirty a little bit.

Just think about the way that a simple, that a simple three statement model works.

And then for the remainder of the session, we'll move on to much more complex three statement model, we think like circularity and so on.

So, let's just have a little look at the structure.

We've got a set of assumptions here on revenue costs investment payable. So we've got some balance sheet assumptions and some income statement assumptions.

We've then got an incredibly simple income statement with just two lines, revenue and costs, giving us net income at the bottom.

We then have a fairly simple balance, and excuse me.

And you'll see that it's got cash, it's got investments and so on.

But again, it's a relatively, simple uh, balance sheet.

And then we get down, we've got a cash flow state totally empty at the moment.

So we're gonna populate that.

These historic numbers, the blue on white, that's the code that we tend to use.

Numbers that are hard coded straight from a set of statutory accounts.

So those numbers have come in from the company that we're looking to model going forward.

And this is from the historic numbers.

The black on white numbers are calculations, and then the blue on payable is the code that's a lot of investors for assumptions that you're making.

So you'll see that we've got historic revenue growth of 5%, and we're assuming that that level carries on.

We've got historic cost as a percentage, revenue 60%, and we're assuming that carries on as well.

And so on. We're gonna jump straight in. We're gonna go to our income statements.

And the very first thing we do before we forecasting any numbers is do our subtotals important not to just key the numbers in, hard key in leave those for calculations.

Because then, then what that does is, first of all, it verifies that the picked up from the statutory accounts.

And secondly, if your subtotals work correctly for history, then if you copy them to the right hand side, then they're gonna work for your forecast as well.

So we're gonna jump into this first one, and we're gonna say equals revenue minus costs.

And that's gonna give us, a profit of 40, which is pretty straightforward.

And we'd go away, we'd check that, we'd look at the statutory accounts, and we'd make sure that it tied up to 40.

If we're happy with that, we just highlight the whole row, Holding down shift row.

And then we go, troll is right, and then we end up with a subtotal.

Now, I'm just gonna put over on the right hand side, I'm gonna put some formulas, using a formula text function so that,, you'll see what we've got as we've actually populate it.

So that next thing we do is we basically start populating, my suggestion is always to just do one period at a time. Just do the first period, make sure you're comfortable with that make sure it, make sure it works correct.

And then once you're happy with it, you can copy it off to the right, because you know, your logic works.

So first thing we do is it says we've got revenue growth at the top of 5%, so we're gonna have to go bracket one 5% close brackets multiplied by my previous year, my prior year at revenue.

And that gives me a figure of 110.3, which looks pretty plausible.

And always, always just stop and think, you know, my revenue was 105, now it's 110.

I know it's going right.

Even if you don't mentally recalculate the 5%, you're just checking, does it make sense? Is it the right direction? Is it negative or positive? Those sort of things. Next thing, cost.

It tells me that I've got costs of 60% of revenue in my forecast, so I can populate that pretty easily.

And again, what are my numbers? I look at the very bottom and it says that I've got net income of 42 last year.

I've income of 40 year therefore it's gone up a bit.

That 5% is rippling through.

So I'm comfortable with that as a whole period.

I copy and I highlights that particular that that particular set of cells, that particular period one, highlights everything to the right.

And then do control R and control R will basically forecast my income statements for the next three years.

Then I go to my balance sheet and I do the same thing.

I start with, excuse me, I start with subtotals in my balance sheet. And this is where alt equals is your friend.

So in the balance sheet, most items are kind of portrayed as positive numbers, even if they're negative numbers, even if they're liabilities or equities.

So I'm gonna just get to the bottom of the total assets here.

I'm gonna hit alt equals, and it automatically sums up those two numbers above that looks about right.

I'm gonna copy that off to the right.

I'm then gonna go down to liabilities, do exactly the same alt equals hit return.

And you can see that I'm not jumping down by one cell, I'm staying in the same cell. So it's now very easy to just highlight that row copy, right? Control R and I've populated that as well.

Now, I can't do alt equals here.

I need to say equals equity plus the total liabilities.

And again, that looks like it works.

Copy to the right and I'm off.

And a final thing with the balance sheet is always, always check that your balance sheet balances.

So we're gonna take our total assets minus the total liabilities and equity, and it's 0.

So we, the fact that it's zero gives us a sense that the balance sheet balances.

So there we are, we've got our balance sheet. It's underway. Obviously, we haven't actually forecast anything yet.

Now the first thing we're going to do, so we're gonna go to the top and we're gonna look at cash, and we're gonna say, we're not gonna do cash yet.

We'll do cash when we've actually done our cash flow statements.

What about investments? This is effectively, it could, it could be on balance sheet investments, it could be some sort of PP&E. It's all wrapped up into one line.

So we're gonna start with that number.

What's are our assumptions? We've got an assumption line here, and it's just got literally numbers, growth in investments as a single line item.

I don't have any amortization or depreciation in this relatively simple, three statement model.

So I'm gonna go up, grab that increase of 5, add that to the previous year, and that gives me 65.

I'll just put the formula there so you can see it.

Then what about accounts payable? I've got an assumption that says my accounts payable is 19%, of the cost, which sounds pretty plausible, so I'm gonna say equals in my accounts payable that 19% multiply by my cost figure.

Again, just pause a little bit.

It was 12 last year, it's 12.6 this year.

It's both positive. It feels reasonable.

My revenue is growing. I'd expect those accounts payable balances to also grow with that increase in volume.

I've then got long-term debts.

Now here I've got, I need to be a little bit careful because I've got an increase or decrease in long-term debts, but this year I haven't got anything.

It's 0 but I still need to put the formula in.

So when I copy it off to the right hand side where my my long term debt goes down by five in the next year, then it's gonna ripple through into my balance sheet.

So I say equals the increase or decrease plus the prior year.

Obviously it doesn't change, but it will do when we copy it off to the right.

And the final other piece in this is my equity.

So equity you remember, is made up of both the stock, the shares themselves, and also retained earnings.

How does retained earnings move? Basically it increases with net income. That's effectively profit you have made for the shareholders.

And then it decreases as we pay dividends. We're not paying any dividends here.

It's a nice simple model.

So all we're doing is increasing the equity and therefore the retained earnings and therefore equity by the net income.

So I go up to my net income figure at the bottom of the income statements, I then add it to the previous year's equity, and I get a really healthy rise in, I get a really healthy rise in equity up to 84.1. Now it's worth just pausing again.

I've now got a balance sheet that's out by 51 owe dear, but in fact, the reason why we don't have any, why my balance sheet doesn't balance, it's basically, because I don't have any cash yet.

So it's worth just pausing at this point and thinking does the amount by which the balance sheet is out make a bit of sense? Well, if I'd simply brought forward the last year's cash into this year's cash, that would be 12.

That would basically add 12 to my total assets, which would make it 77 which would start to bridge the gap a little bit.

But the other thing that I'm not including is net income.

Net income is going into retained earnings into equity, but isn't being shown in an increasing cash.

And you'd normally expect at least some of your profits turn into cash.

So when I basically include my cash, including lashes cash plus the movements as a result of net income, I'd expect the cash balance to go up by quite a lot.

And therefore that mismatched balance to hopefully disappear is always worth, always worth just pausing and thinking, is it the right direction? Do I understand why it's mismatched even? You're not recalculating it, you're just looking and saying, does it make a little bit of sense? Before I then say I'm happy, therefore, with this calculated balance sheet, I'm gonna copy it off to the right control R and then go onto my cashflow.

And of course what you're seeing, if I look at the mismatch on the balance sheet is I'm seeing it's gradually getting worse. And the reason for that is we're including net income in equity, but we're not including the cash impact of that net, of that net income, the increase in cash as a result of the profits they're making flowing into the cash line itself.

So let's go and do our cashflow and hopefully that'll fix our three statements.

So the first thing in a cash flow is net income.

We go to the very top here and we basically say it's 44.1.

Next we look at movements on operating working capital.

Now we've only got one item of operating working capital here, which is the accounts payable.

And we have to be careful. This is where people regularly make mistakes in this three summit modeling.

Which direction is this? So this says I've got accounts payable going from 12 to 12.6 because this is a liability and amounts owing and it's going up.

This is actually good news for cash.

This is good for our cash balance.

Because what's basically happening is our suppliers are supplying us with raw materials that we're then turning into finished goods and selling and making a profit from, but we haven't yet paid them.

We're basically, we are relying on or taking advantage of the credit of those suppliers.

So that increase in accounts payable, it is actually good news for cash.

So when I go to my increase or decrease in accounts payable, what I need to do, I want it to be showing as a positive number.

So I'll say 12.6 minus the 12 of the previous year.

That gives me 0.6, 44 plus 0.6 alt equals gives me 44.7.

Okay, what about my in, what about my investments activities? Well this is basically the increase in those investments on the balance sheet. Now this is an asset. It's gone up from 60 to 65.

I just go up to here. It's gone up from 60 to 65.

Now how would that have happened? Basically because we bought more investments.

So this rise in assets on the balance sheet must be bad for cash.

It must be negative for cash.

So I need to make sure I get my direction right.

So I'm gonna go to this increase in investments and I'm gonna start with the 60 and then I'm gonna subtract the 65.

And what that's gonna show me is, oops, sorry, there we go.

What that's gonna show me is a decrease of five.

I'll just put formerly along the side here just so you can see.

And then I'll put a little subtotal underneath, even though it's only one line.

Just good habit to get into. So we've got a subtotal from those for those investing activities.

And then final item is movement on financing items.

So it's the cash flow from financing.

So I'm gonna say equals, and again, I'm gonna go up to that long-term debt.

Now it hasn't moved, but when it does move in the following year, I need to make sure this is taken into account.

Now, basically, if that long-term debt had gone up, this would be good for cash.

I.e. the bank orders has come along to us and said, here's some extra money, the liability the loan goes up.

But this other side of that on the balance sheet is my cash has gone up.

Because the effect fee, the bank has given me that extra amount of loan. So what I need to do is to say equals this year minus the previous year, and when my bank loan goes up, this will be a positive number.

Alt equals at the bottom again to create a total.

And then I can go onto the final section.

Now, I'm gonna do a little sort of spiral effect here or a base calculation.

So at the end of the previous year is 12, and that then becomes my opening cash this year.

And then I can say, and how much cash have I actually made? So I'll just just say equals the cash flow from operations plus the cash from investing activities, which are actually negative plus the cash from financing activities.

And you might and that definitely looks like it's on the right track, doesn't it? So to bridge our gap, so we get to the bottom, what's our closing cash alt equals, again, our closing cash is 51.7 and a little whoop of joy because that's exactly the figure we need to push into our cash to push into our balance sheet, to make our balance sheet balance.

So I'm very confident, I'm very comfortable with that cashflow statement.

I can copy that off to the right.

And again, if I look at the bottom line, you can see they're exactly the amounts that we need to go into our cash balance in the balance sheet and make the balance sheet balance, copy that off to the right.

And there we go. We've done our first ever three statement model.

So, let's make things a little bit harder.

Now, just before I do, just before I leave this spreadsheet alone you should have both this spreadsheet the empty versions and also full versions that you can download from our website.

So if you want a bit of practice, you'll see right underneath there's something that says simple 2B, and it's exactly the same, same layout, same structure.

Be a little bit careful because in this presentation this is more of a European presentation where we have revenue as positive and costs as negative.

So be a little bit more careful with your directions.

But fundamentally it's the exactly the same structure.

If I look at simple three, you can see it's really quite a lot more complex, a lot more line items in here.

But again, you've got two versions of this and also the solution in the full version of these.

Okay, so I'm gonna minimize this and I'm going to just go to one more item.

One more item. And I'm just gonna point out when we get to our next more complex model, we're gonna end up at the end with a circular reference.

I'm just gonna mention this here so we don't get sort of dragged away from the spreadsheet.

When we get to the end, right at the very end of our more complex model we're basically gonna have something called a circular reference.

And that's because we're gonna calculate interest and we're gonna find that interest relies on the cash balance.

And the cash balance in turn relies on the interest because we're gonna calculate interest based on the opening, the average of the opening and closing cash balances.

So we're gonna end up with something like a, something called a circular reference, which we don't want, we don't want circular references in our spreadsheets. So, how do we deal with that? Well, we basically do two things.

First of all, we turn on something called iterative calculations, which end, which stops Excel falling over when it has a circular reference.

And the second thing is we're gonna have a switch.

So we can basically turn on or off that circular reference that's triggering that sort of circular reference error.

So next thing we're gonna do is we're gonna load up the more complex spreadsheets.

And this is something called three statement model workout, empty.

And oops, let me just drag this camera on. There we are. Let's just make this a little bit bigger.

Oh, we are, okay, so it says this is the name of the spreadsheet. It says three sm, so three statement model in circ workout.

And so interest circular reference workout empty. So that's the one we're gonna do. Now we're gonna look at the first model here.

We're gonna look at the first model.

I'll make it a little bit bigger so we can see it on the screen.

And we're gonna gradually work through this over the next half an hour.

So you'll see that it's relatively simple.

Sorry, it's not simple.

It's relatively similar in that it's got income statements, assumptions, balance sheet assumptions and net debt assumptions.

So the same sort of thing that we had in the previous one, that's just more line items.

There are more of these. You'll see also that we've already got some of the historic numbers and those historic ratios calculated.

So you'll see things like revenue growth of two and 10%. You'll see operating costs 77%, and we're basically assuming that that 77% continues.

You'll see that we've got things like operating current assets and operating current liabilities.

And those percentages in the final year we're basically assuming that they roughly continue for the, Forecast period.

You also see that we've got an income statements this time with a few more lines, a few more lines.

We've got some sub subsidiary calculations, we've got a balance sheet, and then we've got a cashflow statements.

And we also have at the bottom some interest calculations. And that's where it's gonna get a little bit complex, a little bit messy.

So we're gonna go up to the top up to our income statements.

I'll make this just a little bit bigger, so hopefully it's a little more obvious.

And we'll do exactly the same as we did before. We'll put our subtotals in, but now we've got a few more lines, so we need to be a little bit more careful with this.

So first thing we'll do, we've got an EBIT figure.

So earnings before interest in tax, which is equal to the revenue minus the costs as before.

And we would obviously do that calculation, we'd do that, a little calculation there, 1,596.

And then we'd go back and look at the real statutory accounts for this business and make sure that that 1,596 number tied up to the statutory accounts.

And if it does, we'll just basically copy that all the way to the right.

Now, again, what I will do is I'm just gonna put over on the right hand side a whole stream of calculations so you can see what I'm doing as I get to the end of each, line, quite a lot more lines, a hundred lines on this model.

So there we are.

The next thing we're gonna do is we go down to the earnings before tax and we'll do our subtotals down here.

Now we need to be, again, a little bit careful.

This specifically says these items are tax sorry, interest expenses and other expenses.

So we can't just, these are these, these, these aren't negative items, sorry, these are negative items, but portrayed, posted, presented as positive.

So we need to do some pluses and minuses. So we need to say EBIT minus that interest expense minus that other expense is gonna give me that 1380.9, copy it off to the right and then down at the bottom, net income, again, it's equal to earnings before tax minus the tax expense.

It tells us that it's a tax expense, not tax rebate.

Copy that off to the right.

And then what we can do is we can start walking down our income statements and populating what we can.

Now, the one thing we're not gonna do is we're not gonna do interest for the time being that's right at the end. It's a little bit like our approach to cash.

just looking at one of the questions, the simple Excel financial previously is saved, but not the one you are currently going over.

It should be, you might need to Sasha, you might need to, to refresh.

This you might need to refresh the link.

I will, if I can just, I'll just see if I can pop it in the chat box.

Just in case that isn't there, just give me a moment for the chat box.

Just let me see if I can pop it in the chat box. Three seven model workout empty.

Let me just see if I can pop that in the Q&A.

It won't let me. Oh, okay, great.

Sasha Cena, excellence. That's good news.

I was just, just struggling to actually paste it. Thank you.

Thanks Sasha. So, uh, hopefully you can see this, right? So let's carry on. So the first thing we're gonna do is we're gonna look at revenue exactly the same format as before.

What we need to do is to find the revenue rise.

And the revenue rise is up at the top. It's 15.3.

And there's even a little note there that says why the revenue is rising so much in 2022.

Useful, very useful thing to do.

Just remind you when you're looking back at your spreadsheets or tell your boss or tell the user of the spreadsheets the user of the model basically why numbers have perhaps where the why there are perhaps, you know, particular figures and particularly when like this the amounts have risen quite significantly, sort of out of, out of line of the sort of general trends.

So, what we do is we just pop that 15% rise.

Don't worry about these values.

It's basically because I'm putting formula text in here just to show you the calculations as we gradually populate and then copy to the right, then everything is going to, everything all work.

Operating costs are exactly the same.

We've basically got in our income statements assumptions, we've got something that says operating costs 77% of revenue in line with the previous year.

So multiply that by the revenue figure and we end up with that number.

And again, always just worth a little pause.

My EBIT was 2,043, it's now 2,306, which given we've got that 15% rise in revenue, makes sense.

It's gone up by the, the right sort of right sort of amount.

As I said, we are not going to do interest expense.

We'll do that right at the end, but we are going to include this other expenses line.

And this is just a hard coded, uh, number and basically says a hundred, oops, 120.

Gonna pop that in here, okay? One calculation we now need to do, we didn't do this in our very simple version before we're gonna work out the tax. You can see right at the top there I've got an 18% tax assumption.

So I'm gonna say equals 18%, grab that 18% tax assumption, multiply by the profit before tax, the earnings before tax.

Again, a little pause. Last year it was 350 14.

This year it's 393, our profit has gone up.

We don't yet have the interest expense in here, so it makes sense that the tax has gone up.

Okay? So, I think we're comfortable with that as a whole year's worth of income statements.

Always worth just doing just one column.

Just get one column right? And then if you are comfortable with it, highlights everything, copy it off to the right control R, and it works really, really quickly.

Now, what we've got next are three subsidiary calculations.

We're not gonna do the operating one, the operating assets yet. We'll come back and do that when we do our cash flow.

But these other items here PP&E and equity are what we call base calculations. So beginning, addition, subtraction, and end.

They're things that basically gradually increase or gradually decrease over the course of the years.

Now, we can't do this for history, we don't have enough information to do this in detail.

But we can do them from our, for our forecast period.

So we're basically going to grab, for the last historic period, we're gonna grab the closing PP&E figure from the balance sheet, which is down there.

And then we're gonna say, well, what happens to PP&E in our forecast? Well, when we have some capital expenditure, when we basically buy some more PP&E it goes up and then when we depreciate, PP&E then it goes down.

So we're gonna incorporate both of those and we've got assumptions for both of these.

So our starting points for PP&E is the closing point for last year.

And the fact that I've done it like this means basically once I've got my first projected year, right, I can copy this all the way off to the right and it'll work, it'll, it'll flow forward.

So how much is our CapEx often a very good way of forecasting CapEx is basically to link it to my revenue.

So it's says up here, CapEx is a percentage of revenue, 5.5% multiplied by my revenue figure.

So does make sense. Basically, if we want to sell some more things, then we need some more machines and some more IT and so on.

So again, you can see that that makes sense.

It was 495, our revenue's gone up a lot of 15%, so it's now fit the 568 of CapEx this year that we need.

That makes sense. And then our depreciation is a function of the brought forward PP&E balance.

So again, that makes sense.

The more p the more PP&E you've got the higher level of depreciation.

So again, multiplied by the brought forward PP&E gonna want it to be negative. So I'm gonna multiply by minus one and I'll just copy these down so you can see the formula.

And then I get to the bottom and I alt equals to add up those three rows.

And that gives me a closing PP&E figure.

Gone from 7.8 to 7.9 or nearly 8 makes sense because our business is gradually growing.

So I'm comfortable with that as a mini calculation for PP&E copy off to the right.

There I go. And you can see it's gradually rising as our revenue gradually rises.

Now, equity gonna do exactly the same with equity.

I'm gonna go and grab the closing figure for the last set of statutory accounts from here.

There we go. 2.7 million, and that becomes the opening figure.

I'll just put a formula alongside that so you can see where it comes from.

And that becomes the opening figure for the next year, 2.7.

Then what does that, how does equity go up? As we mentioned previously, our net income adds to the equity.

So we go to the bottom of our income statements, which is the, and I'll just put formulas on the right hand side, and then why does that go down? It goes down when we pay dividends and we've got an assumption, so it's a little bit more complex than the previous model.

We've got an assumption for dividends and it says up here we've got a dividend payout ratio of 75% of net income. So I'm gonna grab that 75%, I'm gonna multiply it by the previous year, sorry, by the net income.

And then I'm gonna multiply it by minus one because it's negative.

When I look at my net, my retained earnings alt equals at the bottom.

And you can basically see my retained earnings, my equity, my equity is going up as a result of the increase in retained earnings.

So again, that looks sensible, it looks plausible.

I'm gonna hit control R and copy all of that across. And you can see that's gradually growing, growing across.

We'll deal with the assets and liabilities, the working capital in just a second.

So we move on to our balance sheet.

Now this should be relatively straightforward because we've already done some of this calculation.

And again, alt equals is your friend.

So the first thing I do is I just put subtotals underneath each of the main section.

So under assets, liabilities, alt equals copy off to the right, and then our total for liabilities and equity.

And I can see that my two sides of my balance sheet balance, but I will just put a little check at the bottom.

Total assets and liabilities minus, sorry, total liabilities and equity minus total assets gives me a balance sheet check of 0 and it works excellent.

So then we can go to the, then we can go to the main workings within our balance sheet. So the first thing, we've got current operating assets, so things like inventory receivables and so on.

And these are often a function of revenue up here.

We've basically got a fairly simple assumption.

It says if I go up to my balance sheet assumptions, it says I've got current operating assets as a percentage of revenue is 25%.

Some could be expressed as things like receivable days and inventory days, which would be absolutely fine.

This one is just taking a percentage. So I multiply take that and I multiply by my revenue figure, which And again, always just worth pausing just a little bit because just to make sure it's the same sort of order.

Yeah, last year it was 2.2, now it's 2.5.

I know my revenue's gone up a little bit. It's by 15%. So that looks plausible. PP&E, this is easy to do. We've already calculated PP&E. It's that closing balance there.

What about our liabilities? Now we're not gonna do cash and we're not gonna do revolver at the moment.

So, we're gonna net those two off.

We're gonna deal with those separately.

So, we're gonna jump past that. We're gonna go to current operating liabilities.

And I remember seeing an assumption about liabilities and it was up here somewhere.

It says current operating liabilities as a percentage of revenue in line 13.

So multiply that by my revenue figure.

And there I have it. And again, just a little moment just to pause.

Is it the right sort of audit yet? It was 1.5, now it's 1.7.

Now it's believable. Long term debt.

Do we have some assumptions about debt? I'm sure we do. We've got a movement on debt. I remember seeing it.

It's up here.

So it says long-term debt issuance or repayments of 50.

So I wanna take that minus 50.

And then plus the previous years debt.

And you can see my debt has fallen slightly equity.

We know our equity, we've already calculated that in one of those little sub calculations here.

There we are. And so we're on our way to having a fully populated balance sheet.

A little pause a little moment.

I've got a, a mismatch balance sheet of 4 7, 5.

And it's actually because my assets are slightly higher than my liabilities.

Now the reason why is because I'm not bringing forward the revolver or the cash.

Okay, 900 in revolver, 300 in cash a net of about 600.

And my balance sheet is out by 400, nearly 500.

So it looks plausible it once I've done my cash and my revolver calculations, it looks like my, hopefully my balance sheet will, will balance.

So if I'm comfortable with all of that, I can grab the balance sheet, I can copy it off to the right.

And again, you can see it's going gradually more and more out of sync, out of balance.

But that's okay. Once we do our cash flow, we'll probably be okay.

Now the last thing I'm gonna do, just gonna do this little subsidiary calculation just while we're here before we go off to our cash flow itself.

So I'm gonna grab the operating current operating assets and I'm gonna grab the current operating liabilities and I'll make them negative because they're liabilities multiplied by minus one. There we are. And then alt equals at the bottom.

And the reason I'm gonna do that is because we are gonna need that when we move into our balance. When we move into our cash flow, we're gonna look at the movements in the movements in operating working capital.

Okay? It is just worth just pausing here.

These are asset balances.

So overall my assets are bigger than my liabilities.

My asset balance is going up. That's bad news for cash.

It's like having more inventory.

If you have more inventory, then it's because you've gone out and bought that inventory, which would be bad for cash.

Here I've got a net assets figure of 7 0 5 net operating assets for working capital going from 705 to 796.

They're po positive numbers, it's going up. So that I would expect a negative figure of around 90 in my cash flow.

So off I go. Let's calculate our cash flow.

This is the where life gets a little more complex than we had before. So it's a slightly more complex slightly more complex version.

So we're gonna say net income.

We need to go and jump to the bottom of the income statements and go and find that figure.

There it is 1,793.

And again, I'll just put calculations alongside so you can see as we gradually, as we gradually uh, populate this thing.

Okay, next thing, let's add back depreciation.

And again, we've got a figure, we did our little subsidiary calculations.

We've got a figure for depreciation there. It is DNA, but remember this is adding DNA back into our cash flow.

It's a negative figure of 399 in our net income, but we want to reverse that.

So we need to point it the 399 and multiplied by minus one.

There we are.

Then we need our movements in operating working capital, which we've already talked about.

It's these figures just up here.

We need to make it negative.

So we're gonna say equals 705 minus the 796.

And that should give me a figure of approximately minus 90 of movements in operating working capital alt equals at the bottom.

And that gives me my operating cash flow.

CapEx again, gonna be fairly easy to do because we've got our capital expenditure.

As one of the items in our subsidiary calculation up here when we were looking at PP&E remember again, this is negative PP&E is going up.

So therefore from a cash perspective, this is bad news.

We spent money on PP&E so we need to multiply that by minus 1 and it gives me minus 568 alt equals underneath it just to have an investing cash flow subtotal.

Next dividends paid.

Again, we did that within our equity sub calculation.

So here is our equity sub calculation.

We paid a dividend of 1344, that's okay. It's the right sign for our in, our cash flow statement because we paid this amount in cash to our equity holders.

Final thing, long-term debt.

Again, this is fairly similar to the previous example. We've got long-term debt.

It was 5161, it's now gone down to 5111.

Therefore we've paid off some debts, therefore that must have gobbled up cash. We paid cash to repay some of that debt.

So we say this year minus last year.

And that's gonna give me the negative 50 figure that we included within that line item.

That's all of our financing. Cash flows alt equals to give as a total and then we add all three of them up.

2101 plus 568 plus 1394.

That gives me a figure of 137 increase in cash flow.

Now there's two sort of complex bits at the bottom of this model.

So the first thing is in terms of cash we've actually got two items, that are flexible. We've got a real cash item of 329 and we've got revolver of 942.

And of course it's, you know, these things are both flexible.

You could draw down a bit more from your revolving credit facility and your cash will go up, your revolver would go up or you could use some of your cash to repay the revolver.

So where are we going to basically put this cash flow? We could just put it all into the cash balance.

But that wouldn't give us a very accurate answer.

So what we're gonna do to start this off is we're going to assume that we use that residual 329 of cash to repay some of the revolver.

So we're gonna start off with a net revolver position of 642.

If cash was greater than revolver, we'd start off with a net cash position. We'd have used the cash to pay down the revolver, so the revolver will be nil.

So we're gonna start here.

I'm gonna say my ending cash net of revolver is equal to the actual cash balance of 329 minus the revolver balance of 942.

Gives me a figure of approximately minus 600.

And then I'm gonna say that becomes the opening position for next year.

And then my closing cash or revolver is basically the sum of those two.

The opening position of minus 600 plus the cash that I actually generated of a 137, 475 is exactly my balancing figure up here.

So surely we just, I grab all of that and copy it off to the right 'cause I know it must be right.

And you can see that the bottom line is exactly the same as the mismatch on the balance sheet.

So surely all I do is I basically put my revolver balance down to 475 oh. And I need to flip it round.

I need to make it multiplied by minus 1 because it's a positive revolver balance.

I were in debt to the bank and as I do that, you can see that my balance sheet basically now balances.

So it's right. However, there's something odd happening over here in 2024, and that's because we're generating cash gradually.

So my revolver balance becomes negative.

Negative revolver balance basically means cash.

So what I need to do is to be a little bit more sophisticated and basically when the cash balance is positive, I need it to go into the cash line.

When it's negative, I need to go into the revolver line. So I'm gonna do that with a min and a max function. So I'm gonna start with max and I'm gonna say I want it to go into cash.

I'm gonna use max of 0 or that bottom line number that's closing cash position.

And you'll see that it's basically 0 because 0 is obviously larger than a negative number.

But once the number goes positive, the positive number is greater than 0.

And so it pops into the cash line. So that's what we want to happen.

Now we need to get rid of the those negative items in the revolver.

And what we do is we do that with a min function. So we're gonna say equals minus min, and we want the lower of 0 or the closing balance, uh, which is somewhere down here. There I go, closing balance. I think that's gonna work. Yep.

So that pops in 475 when it's negative, copy it off to the right and basically once it becomes a positive number then the lower of 0 or a positive number is 0, and therefore this bottoms out at 0.

So we're almost done.

We've got about 8 minutes left.

So we've got one more thing to do, which is our interest calculation.

So we're gonna go to this bottom where our interest calculations are here and there's a couple of things we need to do. The first thing is what we're going to do, we're gonna be a little bit sophisticated about the way that we calculate interest.

We could do on the opening balance or on the closing balance, we're gonna take the average balance.

So if the revolver shifts as it does here from 600 to 475, then we're gonna say if that sort of is generated, that movements is generated roughly evenly across the year.

Then the thing to do, to calculate interest, it's to look at the average balance across the year.

So we're gonna say average.

And I'm gonna go up to the balance sheet and find the revolver balance, which is up here.

I'm gonna say I'm gonna take the average of last year's figure and this year's figure, and then I'm gonna multiply that by the interest, the percentage of interest on the revolver, which is up in my assumptions somewhere up here, here I am, debts and interest assumptions.

There's my revolver interest rates 2%.

So that gives me a revolver. Average interest rates.

Then I'm gonna do the same thing with the long-term debt average of the long-term debt balances, which is that 5161 and 5117 this year.

And then again, multiply that by jump all the way up to those interest rate assumptions, Which are somewhere up here.

There they are.

There's my long-term interest assumption of 3.5%.

and then finally I do the same with cash.

So I'll say average of the cash balance, which is the, and then multiply that by the cash interest rates.

We're almost done with 5 minutes to go.

So where's the cash interest rates? Here it is. There we go. 1% hit return and then add it all up.

And I say equals the top two, which are interest expenses minus the interest on cash, which is an interest income.

And that gives me 192. So that looks all pretty plausible.

Gonna copy that off to the right hand side.

I'm just gonna check something in my options before I do my last little, before I do my last little calculation.

Okay? So, okay, right, so the last thing I need to do is I need to link my interest into, that interest calculation in my income statements.

So I'm just gonna say equals, and I'm gonna jump all the way down to the interest expense.

Somewhere down here.

Long way down there I go to 192.

Sorry, that was it was F192, so I'm just gonna hard code that in F192.

There I go.

I think it wasn't F192. I think it was F92. Sorry about that. Just it was, oh, it was 1 92 was number it was it was, F86. That's the number I need. Sorry about that. F86.

So let's just link that in properly.

I'm just gonna hard code that number in. Okay.

So, and it tells me, it tells me I've got a problem. It tells me I've got a problem, I've got a circular reference. So you can see bottom left hand side it says that I've got circular reference and now it's giving me these blue lines to basically tell me that I've got a circular reference. And if you just think about why that's happening, why that's happening is because, um, the interest that I've just calculated is now going into the interest line in the, in the income statements, the interest in the income statements, the net income for at the bottom of the income statements is then going into the top of the cashflow.

The cashflow is then changing the cash balance and the revolver balance, which in turn is changing the interest.

So you've got two things relying on each other.

So how do we fix this? There are two things to do.

The first thing is we go alt ft. We go to our options where we set up Excel before we go into formulas, and we're gonna turn on something called iterative calculations.

And this basically tells Excel to ignore those to ignore those circular references and just basically go round the cycle a hundred times.

And so what it does is it goes around this cycle and it gravitates to it gravitates to that 192 number, which is what we had at the bottom of the interests calculation.

But we need to be a little bit more careful because actually leaving this turned on all of the time is not a great idea.

What we want excel to do is to tell us when we have those circular reference problems, when it finds a circular reference.

So what we want to do is really we want to edit our cell.

We want to edit our spreadsheet with iterative calculations turned off, but the problem is it's now gonna tell us all the time you've got the circular reference in your interest calculation.

So what we need to do is to make it not be a circular reference, we need to break the link between the interest, so between the income statements and that interest calculation.

So we're gonna do that with a switch and it'll take me just a minute to show you.

You'll see over here I've got something called a circular switch and it's even given a name.

You can see that it says switch up in the very top left hand side, it says switch.

And this cell here can be just zero or one.

So what we're going to do is we are going to say, we're gonna modify our function, modify our formula, and we're gonna say equals if switch, earn it, references switch.

If that switch is one, then I do want you to link to the interest calculation in F86 right at the very bottom.

Otherwise I want it to be 0.

And if that switch is turned off, therefore this therefore, I don't have any interest in here and therefore I don't have a circular reference.

So Excel is fine.

Excel will basically tell me if I have any inadvertent circular references in my spreadsheet.

And then when I want to basically turn on the interest when I've finished my spreadsheet, I want to save it and send it to my boss as a complete model.

Then what I do is I turn that iterative calculations on and I basically go back to my info tab, I turn the circular reference switch on, I go back to my model and now you can see that I've got interest rippling all the way through.

And again, that, that interest ripples all the way down goes into the net income and net income, goes into my equity figure.

My balance sheet still works.

My balance sheet still works.

And you might actually spot that actually these cash and revolver balances are not quite the same as they were before.

I seem to remember this one was around 80 and now it's 246 and that's basically 'cause we're paying more interest.

So it's taking a little longer to actually move from a revolver balance to a cash balance.

But the real secret here is that my balance sheet still actually balances that interesting point here because we've got this iterative calculation because we got these very, very small calculations.

Although my balance sheet balances, it tells me it's 0.

There's obviously a little bit of a number in here, probably, you know, the 10th decimal place or something like that.

But you know, the fact that it's gone from a positive 0 to a negative 0 just implies that there is a little bit of rounding going on between the two sides of the balance sheet.

So there you go. As as if we planned it's exactly one hour.

We've been through two, three statement models.

This second one is really pretty tough.

It's really quite hard.

Basically we will show you sorry if you, if you, if you have a look into model two, there's another version here.

This is actually a three statement model relating to Apple.

Um, and then we have a model three as well. They're all pretty complex and I would suggest having a go at all three of them.

You should be able to download both this, the empty version and also the full version from our website.

And good luck with that. Good luck with your modeling.

I hope that's been useful. Do leads, leave us some feedback and download certificates from our website if you found it useful.

And look forward to seeing you on another webinar very soon.

Okay, thanks a lot then. Goodbye.