We've got a short 30-minute session.

For those that I haven't met yet, my name is Maria Weber, and I'm one of the trainers at Financial Edge, and I'm going to be doing today's session on three-statement modeling fundamentals.

I'm going to be using a couple of slides just to help us chat through some concepts, but then we'll spend time actually working in Excel and doing a simple model. And you'll see there's two Excel documents. There is an empty document, so if you want to work along with me in the empty document or if you just want the solution and you're going to follow along in the solution, we've also got a full solution file.

So, what are we going to be doing today? Well, guys, it's in the name, right? We're going back to the fundamentals, absolute basics.

Like I mentioned, it's a short session today, 30 minutes.

We're going to start with a brief bit of Excel setup.

When we're doing modeling, we want to work as efficiently, as accurately as possible. So we'll start with a bit of setup.

Then we'll go over the process of creating a model.

Then when we look at the model, we'll talk about formatting and the importance of formatting. Then we're going to build a simple income statement and balance sheet, and then we're going to build a cash flow statement that then will then feed into the balance sheet to get our cash balance.

And then finally, we'll have a quick chat about just before you send the model to somebody else, what should you think about? So to get started, if we talk about Excel setup, so obviously open up Excel. You can open up the empty or full file in the link in the chat.

We'll make some setup changes. So you're going to go to File and Options.

Now, ideally, you want to get away from using your mouse in Excel, right? You want to be using your keyboard as much as possible.

It's quicker, more accurate. So we've got some shortcuts here for you, Alt to help you access your ribbons instead of clicking on the ribbon with the mouse. Just to be aware, if you are working on a Mac, these shortcuts don't work kind of as standard.

There are things you can do to try activate them, and there's also plugins and stuff.

At your desks, most likely going to be working not on a Mac, but if anyone's on a Mac today, I'll help you find them, hopefully. Okay.

So, if you're not on a Mac, we're going to go to File, Options, and then we're going to go to Formulas. So Alt, F, T to help us get to the File menu, and then T for Options.

If you're on a Mac, I think it's under Excel Preferences.

So you need to go into your Settings, Excel Preferences.

Now we're going to go to Formulas.

If you're on a Mac, I think this might be under a tab called Calculations.

So just look around, you should find something similar.

So the first thing we want to check is on the right-hand side here.

Can you see I've got Enable iterative calculations, and that is ticked.

We want that unticked when we are working.

If that is ticked, what it means is, if you create a circular reference, Excel will calculate it whether you meant to make that circle or not. Right? Now, sometimes we intend to create a circular reference. So for example, if you're doing a model and there's interest in the model and you trying to calculate interest, you would use the average balance of cash or the average balance of the revolver, and that creates a circle because the ending cash balance impacts the interest, which impacts the ending cash, which impacts the interest, and that's intentional, and we want Excel to solve it. So sometimes, yes, we do want that ticked. If I've got interest in a model, I'm finished working, I tick it, I put it on. But while I'm working, I want to be warned if I create a circular reference, because in case it's a mistake, I want Excel to tell me before it just goes on and calculates it.

Okay, so we want to work with that unticked.

The second thing we want to just check is here you can see workbook calculation modes. You've got automatic, partial, and manual.

Now, if you are in a very big model where there's lots of links to other files, links to other tabs, loads of tabs, lots of sensitivity data tables, your model can run very slow because every time you change something, Excel will update all the affected cells. So if you're in a massive model, working on manual will speed things up, but that does mean that you then need to manually refresh when you've done a batch of changes.

So while you're making those changes, your numbers will be stale.

When you've finished, you F9 to refresh, okay? But working on manual can speed things up.

Partial is kind of between the two, so stuff like not updating data tables.

Okay, I tend-- I don't work in massive models.

Automatic's fine for me, but that's something to be aware of.

Okay, so that's the first bit of setup.

Second bit of setup, we still in File, Options.

Okay? On a Mac, it's your Excel Preferences.

And we're going to go now to Advanced.

If you're on a Mac, I think this would fall under Editing.

So just look and see if you can see a tab or something under your Excel Preferences called Editing. We here, not on a Mac, on Advanced.

So the first thing is after pressing Enter, move selection.

You can see mine is unticked. If that is ticked, which is the default, when you press Enter in Excel, you're going to jump down into the cell beneath because it says here, "Move selection down." Now, that's a little bit irritating because very often once I've done something in a cell, I want to format it, so I want to bold it, highlight it, whatever, and then in modeling, we very often are copying to the right.

So it's irritating to have to keep jumping back up to go copy to the right.

So rather just stay in the cell that you are in.

I know it maybe sounds like, okay, so what? It's one movement.

But guys, if you're doing hundreds of these things a day, it all adds up.

Okay, so we want to work with that unticked, and then the final thing is here, a little bit further down, Allow editing directly in cells.

Allow editing directly in cells. You can see I've got that unticked. What that means is when I'm editing a formula, I'm going to be editing not in the tiny little cell, I'm going to be editing up in the formula bar at the top of my spreadsheet.

And that's just easier because first of all, I'm always just looking at the top.

I don't have to look around the spreadsheet into tiny little cells.

And secondly of all, if you've got a very long formula, you can't see the whole thing at once if you're editing in the cell.

So rather untick that, and then any editing you do will be up in your formula bar.

Okay, so that's just first bit of Excel setup.

Then we're not going to go through-- We've got half an hour to do a simple model, but it's important to try be efficient. Use your keyboard, not your mouse.

We do have some videos on this, depending on your Felix access, I'll show you at the end. But using your arrow keys to move around, using control and your arrow keys as a speed move, jumping you quickly between different bits of data.

Copying and pasting, I think most people are familiar, Control C, Control V.

But copying right and down, Control R, or on a Mac, I think it's Command R, and Control D or Command D to copy down. Very useful.

We'll do this now when we do our model.

Then if you want to select something in Excel, if you use your mouse, you would drag, click and drag. Not using your mouse, it's Shift and your arrow keys.

And then finally, to edit without actually clicking into the cell you want to edit, you press F2. F2 will jump you into your formula bar.

You do your editing. F2 jumps you back out of the formula bar into your spreadsheet. Okay, so just a couple of helpful tips, but let's now move on to thinking about a model.

When we're doing a model, we're trying to figure out what something will look like, right? So typically, a model would start with some kind of actual data that we've got.

Now, what we're going to be doing today is we're going to be doing three financial statements, simple income statement, balance sheet, and cash flow statement.

But you could be doing a model for anything.

I could be doing a valuation model, a discounted cash flow.

I could be looking at an LBO. I could be looking at M&A, looking at accretion, dilution. So why you're doing the model would be different, but very often we've got some historic data that we would start with.

We then take that historic data. If it's a public company, we would get it from publicly available information. If it's a private company, from management.

We then analyze it. We build some ratios and statistics so that we can understand what has happened in the past, what drives this company's performance, what's important, et cetera.

We then make the future assumptions.

Now that we're going to get from management, our own assumptions, industry, research, if it's a publicly listed company, the equity research analysts' reports.

And then you actually do your forecast. You do your model.

And like we said, assignment specific, it will depend on why you're doing this.

Now, we're going to be doing a simple income statement, balance sheet, cash flow statement. You need a bit of accounting knowledge for this, but just a very brief reminder in case you're a bit rusty.

The balance sheet shows us the assets of the business on the one side, liabilities plus equity on the other.

Income statement shows us profit or loss for the period.

The link between the income statement and balance sheet is that bottom line of the income statement affects equity.

It makes your retained earnings go up.

And then finally, the link with the cash flow statement.

Profit, not the same as cash flow. We've got a separate statement that's explaining the change in cash on the balance sheet from one balance sheet date to the next.

Okay, so let's jump into our model.

You've got the solution file. I'm just going to post it one more time in the chat for those of you that have just joined.

The empty file if you want to work along, full solution file if you want to just look at the answer.

So first thing I want to do always when I start working, I've just done this, but Alt F T or File Options or Excel Preferences. Go check I've got iterative calculation enabled. I want to turn that off.

Okay, so you've got to check it's not permanently off or permanently on.

It changes depending on how a document is saved.

I want to work with it off, so if I create a circle, I'm warned.

If it's a mistake, then I know. If it's intentional, then I can deal with it and link it to a switch. Okay, so check that iterations are off. Then if we look at the Info tab, Info tab is going to tell us what this model does, who prepared it, the company, the currency, the units, et cetera. But what I want to draw your attention to is this bit over here, because we said we're going to talk about formatting a little bit.

Now, it's really important that you apply consistent formatting to different things.

So we want to be able to visually see very easily what is an input into this model.

So your inputs, that's generally your assumptions.

Those are the things that are going to be driving the model.

Those are things that are subject to change that we can play around with, and they'll feed through into the model.

We then want a different format for hard-coded numbers, numbers that are typed in, numbers that I want to say are fact.

They're not going to change. That's typically your historic numbers, right? If I'm putting in a historic balance sheet, historic income statement, those are facts. Those should be blue.

So people picking this up know don't change those.

And then finally, your calculations, your formulas, we've got here in a black font. Now, depending on where you work, you might see different conventions here. I know I've seen some banks using light yellow background instead of light blue. It really doesn't matter as long as you are consistent and follow what is being used. Because remember, you're not going to be the only one working on a model. Typically, you've got multiple people, and you want to make it as easy as possible. Okay, so follow that convention You can also have other stuff. I've seen links as well.

If you've got links to other sheets, often that's formatted in green, links to data providers. So the point that I'm trying to make here is look at the key and follow it consistently.

Okay, let's then move on. So we've got three models here.

Simple one, simple two, simple three. Simple one is a bit too simple.

Simple three, we don't have time to do the whole of simple three.

So I'm going in the middle. We're going to do simple two.

Okay, but you've got the solution for simple three if you want to have a look at that afterwards.

So first thing we want to do when we open a model, unless you're creating the model yourself from scratch, obviously, you need to familiarize yourself with what it's doing, what the setup is.

So we can see here we've got two historic periods, and then we've got three forecast periods. We've then got an instruction at the top that just tells us there's no dividends, there's no share issuances, there's no share buybacks.

We'll use this when we need it, but it's got to do with equity.

We've then got a section for assumptions.

So this is what's going to drive our model.

Then we've got a simple income statement.

We've got a simple balance sheet, and we've got a simple cash flow statement. Okay, and then underneath, we've got simple model 2B, which you can try afterwards. Note, I'm navigating in column A, super thin navigation column. All that's in there is just the headings, and you're using your Control and Arrow key to speed move.

Control Arrow, right? Helping us jump quickly between the bits of populated cells.

Now, let's start by looking at-- We said, okay, first things first, you're going to put in historic information. Right? That historic information has already been put in for us.

You would download it from SEC data or wherever you're getting your information or typing it in, but I feel like that's a bit outdated. But I want to draw your attention to something really important. Notice here with the income statement, first of all, formatted blue, so it's a hard code. We know it's not a calc.

Okay? Secondly of all, notice that we've put in the revenues and the costs, but we haven't hard-coded in the profit, the 40. Right? Instead, we want to calculate that. And the reason is it gives us confidence that if I go now calculate what the net profit is historically, it gives me confidence that I've actually captured the historic numbers correctly, the 100 and the 60. This is very simple. Obviously, you're going to have more line items if you're doing a more detailed model. So it gives me confidence that I've captured those numbers correctly and also that my formula is correct because what I want to do now is I want to copy this to the right and already have that set up for my first forecast year. So I don't have to then worry about calculating a profit figure. I know I've got a calculation that works. I check the historic financials.

Yes, I see profit of 40, profit of 42. I'm confident.

Okay, so we're going to copy one column to the right.

We do the same thing with the balance sheet.

So balance sheet, simple balance sheet here. We've got cash and investments.

Add them together to get your total assets.

I want to copy this to the right. So you could do your Control + C and then Control + V. But guys, stay in the cell you're in, Shift, Arrow key, and then Control + R. Or if you're on a Mac, I think it's Command and R, copy right.

Let's do the same thing for the total liabilities.

So we've got historic total liabilities, 10 plus 20, that's 30. Remember, we're going to check that 30 to the source documentation, get confidence, yes, number's correct.

And then we're going to Shift, right Arrow, Control + R or Command + R. And then finally, we want total equity and liabilities. So guys, don't do this.

I do want to add the total liabilities and equity, and in theory, I'll get the correct answer here of 60, but this is messy modeling practice.

I don't want to be including row 26. That's a blank row.

That's not meant to be part of the calculation.

So don't be messy in your modeling. Go pick up the cells that you actually want.

So I want row 25 and row 27 only. And then Shift, right Arrow, Control + R or Command + R to copy to the right.

We always need a balance sheet check.

You can build in a fancy if statement, but guys, what works just as well is to say total assets minus total liabilities and equity. And I want that difference to be zero.

If my balance sheet doesn't balance, I know I've made a mistake somewhere, so we always want a balance check.

People sometimes want to do this to say total assets equals total liabilities plus equity, and then that looks quite cool because you get true if it's true.

But the problem comes in that if I have made a mistake, right? So say, for example, I here didn't add the cash, and that's quite a common mistake because you do the cash last from your cash flow statement. So imagine I didn't add the cash.

Can you see my balance check's not very helpful? It's just telling me false.

Okay, so what? How much am I out by? If I have the actual difference here, I can see there is a difference of 10.

The first thing I'm going to do is scan down the balance sheet.

Do I see a 10 anywhere? Go check, and straight away, I'll see here I've made a mistake. So I correct that, and it makes my life easier.

So balance check, total assets minus total liabilities, and equity must be zero.

Okay, historic cash flow statement we don't need because I've got the historic cash balances in the balance sheet.

We're just going to forecast the cash flow statement going forward.

Guys, any questions, please ask.

I'm going to keep going, but type in the chat, type in the Q&A pod.

Okay, so we have now done step one Step two's been done for us, building the ratios and statistics.

So where do we see that? Here we see it at the top.

We've calculated what the historic revenue growth is, what the historic costs as a percentage of revenue are, et cetera.

This then helps us understand what has happened in the business in the past. Now, that doesn't necessarily mean it's definitely going to continue into the future, okay? But at least we have an idea.

Then step number three is making these forecast or future assumptions. Okay? Now, purpose of these modeling exercises is to focus on the mechanics of the modeling.

So you can see we've just kind of flat-lined everything, but this is where a lot of work thought goes in to actually thinking about what's revenue growth, et cetera.

Note the formatting change. Very easy for me to see.

These are calculations. These are inputs into the model.

So we have done the first three steps.

What's left for us to do is to actually do the model.

So we're going to start with the income statement.

Now, before we actually start working, spend a little bit of time assessing what the assumptions are.

Right? So this is a revenue growth percentage from the prior year. Costs are given as a percentage of revenue.

Investment growth is given in dollar terms.

That's not a percentage change in investments, that's an investment growth.

Okay? And then finally, accounts payable, percentage of costs, and I have got long-term debt increase or decrease.

Sorry, I see a question has just come through.

Where do we have the ratios? So the ratios, if you are following along in the empty file, if you go to the simple two, up at the top here, we've just calculated those ratios from the historic data. So that's just linking to the historic data, those calculations of ratios. Okay? So that's just to help us understand what's happened in the past.

And then this, we would need to spend some time talking to management, equity research, our own research, industry analysis.

And then obviously, if we don't have a view, if we think things are going to stay the same, then we can be guided by just keeping it at what it is in the past. So I'm not sure if I've answered your question properly, but please do come back to me, if not, and I will address it.

Okay, so let's do our income statement.

Income statement, revenues. I've got a percentage growth from the prior year. So I'm going to have to take one plus.

Now, do not be tempted to type in 5% hard code into your formula. Bad modeling practice.

We want to link up to the assumption, because then I can change the assumption and my model will update.

So one plus the revenue growth multiplied by the prior year, that will give me forecast revenue for the first year.

Then for costs, as I said, it's given as a percentage of revenue. So percentage of revenue.

Now, as you can see, I'm only doing one year.

I'm not going to do revenue copy for all years, costs copy for all years.

Because if you make a mistake, you've got to keep copying, copying.

Once I've done it for one year, I'll do a sense check.

Does it make sense? Nothing's changed too dramatically.

If I had revenue of 100 going up to revenue of 400, I would say, "Whoa, did I make a mistake, or is it my assumption?" And then I can justify that assumption.

So sense checking things. Also structure checking. I'm dealing with column E.

Everything for the first forecast year should be column E.

But wait a minute, I see a D in my formula.

That's fine because it's linking to the prior year because it's a growth percentage from the prior year. Okay, so sense check, structure check, and then I'm happy everything looks fine, so I'm going to select it. So Shift Arrow keys, and then Control R or Command R will copy to the right.

What we're going to do next is we're going to do the balance sheet.

Balance sheet, we're not doing cash because that's going to come from the cash flow statement. So let's do our investments.

Go pick up your assumption first. That is a growth, not a growth percentage. It's a dollar growth from the prior year, so add it to last year's number.

Accounts payable.

We've got the 19% of costs for accounts payable, and then the change in long-term debt, that is a dollar change from the prior year.

Yes, it's zero now, but in future years it's different.

So we need to model this in. And can you see I'm not hard coding anywhere.

You link to the assumption, so that's where things can be updated.

So I'm just going to add that to the prior year.

Equity, we would normally do a separate calculation for equity, as you'll see in simple model three. But here, this is where this note becomes important. There's no dividends, no share issuances, no share buybacks. So the only change in equity is going to be the profit for the year.

The profit for the year is going to get added on to retained earnings, and that makes equity go up.

So let's do the equity. So equity, we got to take the profit for this year from the income statement. We're making 44.1.

That belongs to the shareholders.

We're going to add it to last year's equity balance.

And if you are anything like me, first instinct here might be a bit of panic because why is my balance sheet not balancing? But take a deep breath.

It's not balancing because we haven't done the cash flow statement yet.

So we need to do the cash flow statement and then we'll have another look and see if the balance sheet is balancing Okay, I'm not going to copy to the right just yet because I want just everyone to make sure they've got the formulas on screen.

But remember, you've also got the solution file, right? So you can use the solution file instead.

Now let's go do the cash flow statement.

Cash flow statement, you do need accounting knowledge here, guys.

So I'm going to go relatively quickly, assuming you know how to do a cash flow statement. So we start with the net income from the income statement.

That's not a profit figure. We now need to look and say, okay, what's happened in the different balance sheet accounts so that we can reconcile the profit figure to what's actually happened from a cash perspective. So not every single purchase I would have made would have been for cash. Not all my expenses would've been made in cash.

So let me look at what's happened in my accounts payable account, and then when I put that together with the income statement and the other things in the cash flow statement, I'll get to a cash position.

So for a liability, we know increase in liabilities is good from a cash flow perspective because I've effectively got more credit, I've borrowed more. So I take this year minus last year, increase good for cash.

And so my cash flow from operations is 44.7 coming into the business. I've then got to look at the investing activities. This business doesn't have CapEx, but this is typically where your capital expenditure would go.

But we do have some financial investments.

Now we know an asset account increasing is a use of cash. More cash has been sucked into that asset. So my formula is the opposite way around for an asset.

I take last year's account balance minus this year's balance.

So an increase will automatically come through as a negative, an outflow. More money has been tied up in that asset.

And then finally, the only financing cash flow we have, we don't have dividends, we don't have share issuances, we don't have share buybacks. The only financing cash flow has got to do with our debt, and I'm going to take this year's debt figure minus last year's because it's a liability. Increase would be good, should come through as a positive. And so my only cash flow from financing activities is that one, and for this year it's zero.

But going forward, we will see that change.

The final thing we need to do is get to this ending cash balance, and we want to model in a clean, easy way.

So I'm going to start by taking last year's ending cash balance from last year's balance sheet because that's a fact. That is what cash was.

Cash was 12 at the end of last year.

So all I'm doing is I'm just linking up to that 12.

That then enables me to say, okay, well, if I ended last year with 12, that means I start the next year with 12.

So that is my beginning cash balance.

Then I need to add together everything that's happened for this year.

So this year, I generated 44.7 from our operating activities. I used five in my investing activities, and then nothing happened on financing activities.

So I've generated 37.9, and that gives me an ending cash balance of 51.7.

And that is what we then go plug into the cash flow statement.

So that 51.7 I'm going to go link to in that row 19. I'm going to go pick up the 51.7 and we now see, look at that beautiful balance check. It's giving me a zero.

So sense check, structure check. You can even stress check, change things, see it reacts as you predict it should react. Happy with it? Select copy to the right. So let's just do that to see all of our year's balance, and then guys, we will wrap up. I know we are on the half an hour, but we have done our model. And there we have it.

We can see the balance sheet is balancing for all years, and we have got our three statement model.

One more thing we need to do because let's just have a quick check.

Have we done everything that we said we were going to do in the session? So we've done Excel setup. We talked about how we approach modeling.

We've done the formatting chat. We've built the income statement, balance sheet, and cash flow statement. Before you now send this model on to somebody else, what do you do? Check your output.

Does it look reasonable? Consistent formatting.

Is your layout intuitive? If you've built this model, can someone else easily understand what you've done? Have you broken down complex calculations? If you look at model three, you'll see we've got a separate place to do your PP&E calc, your equity calc. Don't try squish a million little calcs into one cell. So break down complex calcs.

Is your model documented where necessary? So on that info tab, are there any special instructions? Have you said what the model does? If you've got a circular interest in the model or another circularity, then when you've finished, you need to enable the iterative calculation setting so Excel will solve it. And if you're using a switch, you got to turn your switch on as well. We haven't done that today because we're just doing the fundamentals. And then finally, do a beauty save. You do not want somebody to open up your spreadsheet and it opens on the fifth tab in cell Z89, right? You want it top left-hand corner, cell A1 for every single tab. Save it nicely on the first tab, and then it's nice, clean when you open it.

So that is it for this session. I just do want to show you that you do have the solution file, right? So you've got the full file. Simple model three is more complex, so that would be a nice challenge for you to now take what we've done, apply it to simple three. Also note you've got model 2B as well, and simple 3B. And in 2B, costs are shown as negatives, so you just need to think, "Oh, well, what does that mean then for my formula? If costs are shown as a negative, I need to adjust my formula to work out profit." So give yourself a challenge, have a look at those, and then depending on your Felix access that you've got, if you want more on modeling, if you go to topics and investment banking, you'll find it under financial modeling.

So topics, investment banking, financial modeling.

If you want to work with iterations, circular references, switches, there you've got that playlist, and we've got loads more.

We've got some basic Excel as well up at the top.

And then if you want more Excel stuff, if you go to Microsoft Office, you'll find all the Excel playlists over there.

So guys, that is it from my side. I hope you found today useful. I'll stick around now in case there's any questions.

And please, the person that asked the question earlier, if I didn't address it properly, please stay on and ask your question again.

Thanks very much to those of you that are logging off now.

Hope you enjoy the rest of your days.

You have a wonderful weekend, and I hope to see you again soon on another Felix Live. Thanks, everyone.