Locked lesson.
About this lesson
How to add Financial Statements to the model in particular the Income Statement.
Exercise files
Download this lesson’s related exercise files.
The Income Statement .xlsm58.4 KB The Income Statement - Solution.xlsm
65.3 KB
Quick reference
The Income Statement
Discover how to add an Income Statement.
When to use
When constructing a basic Financial Model.
Instructions
Follow the conceptual order of smallest to largest financial statements when building a model.
- Build financial statements from chart of accounts
- Ensure Opening Balance Sheet balances
- Put in (sub) totals
- Incorporate links between the statements
- Build error checks
- 00:04 We've done our timing.
- 00:05 What's next?
- 00:07 Remember this?
- 00:10 This was our order of working through a model.
- 00:13 We said, look, we need to look at the operational part first of all.
- 00:16 We do revenue, then COX, then OPX, then CAPX.
- 00:20 Then look at the working capital adjustments, the debtors,
- 00:22 the creditors etc.
- 00:23 And we're plowing through to get to the financial statements,
- 00:27 the income statement, the balance sheet, and the cashflow.
- 00:30 Below I've got my three little orange financial statement types.
- 00:34 The income statement, cash flow statement, and balance sheet.
- 00:36 And I said there was an order to this, I called it the Goldilocks analogy.
- 00:40 We've got baby bear, the income statement, Mommy bear, the cash flow statement, and
- 00:45 balance sheet, the daddy bear of them all.
- 00:47 And this should be the order we built them in, so let's just think about that.
- 00:52 We've got here a PNL, this may not always be your income statement, but
- 00:57 this is the order we should build in.
- 00:59 We said, let's do revenue first.
- 01:01 Let's do cost of goods sold second.
- 01:02 Let's do OPEX third, etc.
- 01:04 Do you see we're building a PNL naturally?
- 01:07 So that's the order we should attack it.
- 01:10 And once we finish that, making sure that the balance sheet balances at all times.
- 01:14 We can then move on to Mommy bear, the cash flow statement, and
- 01:17 work through whatever we haven't done so far.
- 01:20 But some of them will already be done.
- 01:23 But we won't need to revisit the income statement again.
- 01:26 And finally, once the cashflow statement is completed, we'll actually be able to do
- 01:30 the balance sheet and finish off anything that's missing there.
- 01:33 So that's the order, income statement, cashflow statement, balance sheet.
- 01:38 Let's start by building an income statement then.
- 01:41 Building an income statement into a model that we're preparing isn't that difficult.
- 01:46 First, we head for the timing sheet again.
- 01:49 So it's before the table of contents, timing, here's the timing sheet.
- 01:54 And what I'm going to do, is I'm going to make a copy of this.
- 01:57 Hold all the Ctrls down and do this, and I'm going to call this, income statement.
- 02:03 I'm just renaming it.
- 02:05 Now, what happens to Cell A1 when I press Enter?
- 02:08 So we got that wonderful formula we chosen before, it automatically updated yet.
- 02:14 There's some things we need to change.
- 02:16 We don't need this stuff down here, that can all go.
- 02:20 We're gonna be linking this back to the timing sheet, so I'm gonna go here,
- 02:25 equals, this goes to my timing sheet cell.
- 02:27 A sent copy that down, it's all formatted horribly, that's okay.
- 02:32 I take that, I put it in here and I copy it across.
- 02:38 I don't need this.
- 02:40 And I certainly don't need all this stuff down here.
- 02:43 Now it maybe when we copied, we made some duplicates of range answers.
- 02:47 It's always a good idea to go to the formulas tab and
- 02:49 name manager just in case that anything's here.
- 02:52 So, let's have a look what's going on?
- 02:55 We may have created here some errors.
- 02:57 So let's go filter Names with errors?
- 03:00 Yes, we made some duplicates.
- 03:01 Member, we don't want range names.
- 03:04 Highlight all those.
- 03:05 Delete.
- 03:06 You are the weakest link.
- 03:07 Goodbye.
- 03:09 I'm gonna to call this an income statement.
- 03:12 I'm not going to type in income statement here.
- 03:15 If I do that, that's a lot of characters, and that takes up bytes.
- 03:19 In the full size, in send go delete, make it equal to cell A1,
- 03:23 same effect but smaller, as a result.
- 03:28 I don't need this either, so let's get rid of the actual heading.
- 03:31 I'm just going to put an income statement in, and so
- 03:33 you can see which the Gridlines all.
- 03:36 Next, the view tab on the ribbon and I'm clicking on Gridlines here, just so
- 03:39 that it's easier to follow.
- 03:42 So I've got things that I actually need to do.
- 03:45 I think to sort of not have it quite as narrow as this necessarily,
- 03:50 you can make these columns a little wider if you think I need this one to be wider.
- 03:55 So I'm gonna make this fourth column fit longer so
- 03:56 it can actually put all this stuff in here.
- 03:59 And we put in here our income statement, our chart of accounts.
- 04:02 Which you get from your management information system.
- 04:05 I'm gonna go for revenue.
- 04:09 Cost of goods sold and that will mean money grows profit.
- 04:17 And I'm gonna make that bold because it's a subtotal.
- 04:19 Now, we are going to have some inventory in this particular model, so
- 04:24 this is where it actually deviates from as extended chart of accounts,
- 04:29 we're going to have wastage and we're going to have Operating Expenditure.
- 04:35 I've done it like this deliberately.
- 04:37 I've put Operating Expenses with a capital O and a capital E.
- 04:40 You need to get your cases right.
- 04:41 How you're going to do this: once your title gets in,
- 04:44 you can get confused quite quickly.
- 04:47 Typically, I recommend actually making operating expenditures in lower case.
- 04:51 Case because if you do that you'll actually spell it correctly
- 04:54 as well would help.
- 04:57 By doing this, what you've actually got is a standard way of writing everything.
- 05:01 You're not getting confused by I should have capitalize this or not.
- 05:05 Next, earnings before interest tax depreciation amortization.
- 05:10 That's EBITDA and then we'll do the rest.
- 05:13 So by the magic invested in me, let's just go click our fingers and
- 05:17 make the rest of them happen.
- 05:19 There you go, magic happens.
- 05:22 We've actually put the rest in.
- 05:24 We've got depreciation, interest expense, tax expense, and
- 05:27 these totals, looking good.
- 05:30 Now, we need to concentrate on where we're going to put units.
- 05:34 We don't necessarily need them on an output sheet because we can just say,
- 05:37 all items are expressed in dollars, thousands unless stated otherwise.
- 05:42 But I want to show you so we're gonna go through.
- 05:44 Gonna make them con-generate, a little less wide.
- 05:47 And this is going to be equal to currency.
- 05:51 So that's currency, remember I showed you about that before.
- 05:54 And we were working in US dollars.
- 05:56 So, I'm gonna go here to the Home tab > Cell Style, and I've got in here Units.
- 06:02 And that's my actual units, I'm gonna copy that into all the places I need it.
- 06:08 So I'm just gonna Copy Paste.
- 06:13 Holding the control button down to select these.
- 06:17 So I've got all those in here, brilliant.
- 06:19 Now, the last thing I want to do is I want to put in the subtitles.
- 06:23 So, I want to actually have all these numbers formatted.
- 06:26 So that I can just type numbers in, I don't have to keep coming back and
- 06:28 styling them.
- 06:29 So I'm gonna go in here, cell styles.
- 06:31 I'm going to use my number style that I've set up here.
- 06:33 Yep, and then this is going to be a row total.
- 06:36 It's gonna be equal to the sum, open brackets for the two rows above.
- 06:42 And I'm gonna to make it bold and give it a top border and copy to cost.
- 06:50 Similarly, I need to put one in for IBITDA here, so I can copy this formatting down,
- 06:55 Ctrl+C.
- 06:55 Paste it down to here, paste threshold, Ctrl+V.
- 07:00 We put the actual formula is wrong, it's summing row 17 and 18 only,
- 07:04 I need it to include row 15 as well.
- 07:06 So that's okay, I'll just put it click in here and go J-15 as well.
- 07:11 And this copy that across.
- 07:15 And we need to continue,so I need to go and copy these.
- 07:18 And so again, by the magic invested in me, it's time to click fingers again.
- 07:23 And yet again, magic happens.
- 07:26 We've got how the subtitle's all in here.
- 07:29 So I've got this in and
- 07:30 use the plus rather than the sum function here to shut top.
- 07:33 And each of these assumes that the expense is all going to be put in this negative
- 07:38 numbers.
- 07:39 I'll have a total at the bottom from that profit after tax.
- 07:41 And that's it, that's how we set up our income statement.
- 07:44 It's all ready to go.
- 07:45 We have our actual, if I type a number in here, for instance.
- 07:49 Seven, you can see it flows through.
- 07:51 That's what we'll do.
- 07:52 We're getting ready to put all our numbers in when we build our model.
- 07:57 Okay, that's the income statement.
- 07:59 One down, two to go.
Lesson notes are only available for subscribers.