Linking the Financial Statements Part 2

Quick reference

• 00:01 Without further ado, let's just pick up from where we left off last time.
• 00:10 You may recall linking the financial statements,
• 00:13 we've just got two links put in to the three financial statements.
• 00:17 Last time out, we did the net profit after tax and we fed that
• 00:20 into the retained profit section at the equity section of the Balance Sheet.
• 00:25 One we have left, the Cash Flow Statement.
• 00:27 We've got to link that into the current asset section for the Balance Sheet.
• 00:31 Let's get going.
• 00:34 You recall last time out we focused on row 48
• 00:36 getting the net profit after tax figure into the actual Balance Sheet.
• 00:41 Linking the first of our two financial statements in.
• 00:44 We did this using the many control account approach in rows 47 to 50.
• 00:49 We have opening retained profit in row 47, we add in our profits.
• 00:53 We don't into clarities but we're going to allow for it, gives us our running total.
• 00:58 And it's important.
• 01:00 The reason we have to do this is that the Income Statement is for
• 01:03 a period of time, here a year.
• 01:05 Whereas the Balance Sheet is at a point in time, it's cumulative.
• 01:09 So it needs to keep a running total of both Income Statement and
• 01:12 Cash Flow Statement figures.
• 01:14 That's why we need to have these totals going.
• 01:17 And just while we're here, be clear,
• 01:18 there is a difference between retained profits and net profit after tax.
• 01:22 Let's just do a dictionary definition.
• 01:24 Retained profits means all profits except the current period
• 01:30 after any dividends have been taken.
• 01:34 Whereas net profit after tax is for the current period only and
• 01:38 does not include any dividends that are to be taken.
• 01:42 That's why we need the dividend deduction for the current period.
• 01:45 So just be clear, we have to keep this running total.
• 01:48 Now I think this is the most transparent way of doing it.
• 01:51 Remember, craft, consistency, robustness, flexibility, transparency.
• 01:56 It's clear, I can follow this on a piece of paper.
• 01:59 But there is an alternative approach that is used.
• 02:01 Now, as I say, you want to be consistent.
• 02:03 You should link both the Income Statement and
• 02:06 the Cash Flow Statement in the same way.
• 02:07 This is a training course.
• 02:09 So I want to show you the alternative method that is used regularly,
• 02:12 even if I don't think it is quite as transparent.
• 02:17 What we're going to do is go up to row 14, the Cash, and link this in.
• 02:21 Now what that should be is the previous period's cash balance plus the cash
• 02:25 movement for the period.
• 02:26 Now we'll find the previous period's cash balance in the cell to the left,
• 02:29 apart from the very first period, or we'll have to link to the Opening Balance Sheet.
• 02:33 So we're gonna need an if statement.
• 02:35 But after that, for the current period cash we're going to have to link to
• 02:38 the total in the Cash Flow Statement.
• 02:40 So there's two parts to this.
• 02:41 So let's look first of all at linking to the previous period's cash balance.
• 02:46 With the if statement, again checking for the first period,
• 02:51 so =if[, counter, F4 twice so I get j$9=1,.
• 02:56 Must be getting used to that meaning, if it's the first period.
• 02:59 Go to the Opening Balance Sheet, now careful.
• 03:01 Don't link to this cell,
• 03:02 that's going to defeat the whole purpose of blanking out the Opening Balance Sheet.
• 03:07 And also, when we put all the numbers back later,
• 03:10 you'll be linking to the wrong place.
• 03:12 We are looking at making sure that our movement in net assets equals our movement
• 03:16 in total equity.
• 03:18 Therefore we've blanked out our Opening Balance Sheet, so
• 03:20 do make sure you leave it to the right place.
• 03:22 And do make sure you make it absolute sure that you copy that formula across.
• 03:27 We actually linked to the right place, and don't press the wrong button.
• 03:31 And do remember so that you actually make this absolute so
• 03:35 that when you copy it across it's not going to change cells.
• 03:39 We need it to actually link to that,
• 03:41 otherwise we're going to go back to our Balance Sheet.
• 03:44 We're going to link to the previous period, I14].
• 03:48 Now remember to knock out the reference to the current sheet you're working on for
• 03:52 the reasons I explained last time.
• 03:54 That makes it easier to follow.
• 03:57 And notice, look, it's either linking to the Opening Balance Sheet, cell I14,
• 04:00 or the current balance sheet, cell I40.
• 04:03 This is the beauty of consistency.
• 04:06 It shows that you're linking to the right places if you have your
• 04:09 sheets consistently set up.
• 04:11 If I've done I14 and I17, or J22 or something like that,
• 04:15 now I've made a mistake.
• 04:17 It helps both the end user understand the model and
• 04:20 the model developer make sure they don't actually make an error as they go.
• 04:25 It's why we do it consistently.
• 04:28 Now what we do is we add in the cash from the current period.
• 04:31 So we go the Cash Flow Statement and add in the total in cell J37, press Enter.
• 04:37 Copy that across, it's all going to be 0 for
• 04:39 the moment because we've done no cash calculations.
• 04:42 And we're not bringing in a cash balance from the Opening Balance Sheet, and
• 04:45 we have linked it in the other way.
• 04:47 Now I don't recommend doing it in this one line but
• 04:49 I did need to show you this alternative approach.
• 04:52 But congratulations,
• 04:53 we've now linked the two financial statements in and we can get going.

Lesson notes are only available for subscribers.