Linking the Financial Statements

Quick reference

• 00:04 The checks have now been completed.
• 00:06 Next up, we have to link the financial statements together.
• 00:10 A reminder then.
• 00:13 A conceptual order of the three financial statement types.
• 00:17 They're not all created equally.
• 00:19 It's back to the Goldilocks analogy.
• 00:20 We have baby bear, mommy bear, and daddy bear.
• 00:23 Sorry to be sexist again.
• 00:25 But the smallest of the three is the income statement.
• 00:28 That's because it only looks at that operating profit after tax,
• 00:31 just the operating side.
• 00:33 Whereas, the cash flow statement doesn't just look at operating activities.
• 00:36 It also looks at investment and financing ones too.
• 00:39 But because they both link into the balance sheet,
• 00:42 this is the big daddy of the three.
• 00:44 So, therefore, we should be looking at what are the income statement first,
• 00:47 get it out of the way.
• 00:48 Then look at the cash flow statement, whatever's left, we'll build that and
• 00:51 finish that off.
• 00:51 And then whatever's left of the balance sheet, we'll put that in at the end.
• 00:55 So let's link them together, bank, put the check's architecture in, and
• 00:59 that is the whole idea of three-way integrated.
• 01:02 Now, I've already done the check's infrastructure now now, so
• 01:05 what we've got to do at this stage is put in the links.
• 01:07 And would it surprise you to learn, I've said this before,
• 01:10 there's only two links you need to put in between the three financial statements.
• 01:14 All the other links are sourced from the calculations.
• 01:17 So we need to feed net profit after tax from the income statement
• 01:22 into the net profit after tax section of the retain profits calculation
• 01:26 in the equity section of the balance sheet.
• 01:28 And similarly, the net increase decrease in cash out for
• 01:31 the period also has to fit into the cash balance on the balance sheet too.
• 01:36 If we do that, it's going to balance.
• 01:39 So let's do, in this session, the first of those two links.
• 01:46 Let's go linking.
• 01:47 First of all, I'm going ahead to the navigator sheet.
• 01:52 Where are am I actually going to go?
• 01:54 Well, the income statement links into the balance sheet, and
• 01:56 the cash flow statement links into the balance sheet.
• 01:57 So, I think I have my answer, the balance sheet.
• 02:01 Now, one links into the cash line of current assets in the balance sheet.
• 02:05 But the one thing to look at this time is down here in the equity section,
• 02:09 rows 47 to 50.
• 02:11 This is where the net profit after tax from the income statement
• 02:14 feeds into retained profits section of the equity section of the balance sheet.
• 02:19 Now, there are two common methods of linking into the balance sheet, and
• 02:22 I'm gonna show you both of them.
• 02:24 In a real model, remember consistency for business flexibility, and transparency.
• 02:28 I go for consistency, and link them both the same way because it would be easy for
• 02:32 an end user to understand.
• 02:34 However, this is a training session, so
• 02:36 I'm going to show you both of the common methods here so you are aware of them.
• 02:40 I do recommend this one though of the two because this is more transparent.
• 02:44 It may look like a more long-winded, but to be honest, it's easier for
• 02:47 everyone to follow.
• 02:48 Let's get on with it.
• 02:50 So this one requires us to bring in the open retain profits from
• 02:53 the previous period.
• 02:54 They both actually have an issue like this in the fact that what we're
• 02:57 actually linking in is something that is for
• 03:00 a period of time into something that is at a point in time.
• 03:04 So we need a cumulative number, so we've got to keep a running total.
• 03:08 And that's how we have our opening retained profits from a previous period.
• 03:10 We're adding in all of it up til now.
• 03:13 And then what we're going to do is then bring in the net profit after tax,
• 03:17 knock out the dividends, and
• 03:18 then add that on to whatever we bring in the following period, etc, etc, etc.
• 03:23 So in order to do that in the first period,
• 03:25 we're gonna have to link back to the balance sheet.
• 03:28 Otherwise, we'll link back to the previous period's closing balance.
• 03:31 We need a consistent formula.
• 03:33 So, this is going to mean an IF statement, = IF, open brackets.
• 03:38 This counter, that's what we're going to link to here.
• 03:41 So you're going to be very, very useless formula, a j$9 equals 1.
• 03:45 Put a dollar sign in so that we can copy this formula down,
• 03:48 and it will always link to the counter.
• 03:51 And if j$9 = 1,
• 03:52 you're gonna realize it's just second nature for if it's the first period.
• 03:56 We're going to go to the opening balance sheet and bring in the retained profits.
• 04:00 Now, be careful, make sure you bring in the right ones.
• 04:02 It's not this number, we want the closing balance, the running total.
• 04:05 So it's this retaining profits here and we're going to make that absolute.
• 04:10 Otherwise, we're going to come back to the balance sheet,
• 04:14 and we're going to link to the previous period I50, close brackets.
• 04:19 Bang, done, easy.
• 04:21 Make sense?
• 04:22 Well, not quite.
• 04:25 There's a bit of an issue here.
• 04:26 Let me just explain, we need to remove this Balance Sheet, right here, [i50].
• 04:31 Because, if we don't and we copy this sheet, again, depending on how we copy it,
• 04:35 it may be that everything links to the Balance Sheet[i50]
• 04:38 rather than the copied sheet.
• 04:40 So always, always, always, remove the actual links back to the sheet you're on.
• 04:45 And this happens every time you go into Excel, write a formula where you links
• 04:48 somewhere else, then you link back to the original sheet.
• 04:50 So, always try and remove that in case someone copies the sheet,
• 04:53 because it could end up with linking to the wrong place.
• 04:56 Now, I've done that, press Enter.
• 05:00 I can copy this across, bang, done.
• 05:04 Hang on a minute, balance sheet doesn't balance.
• 05:08 What's going wrong here?
• 05:10 Mustn't move on until we've done that.
• 05:12 Why isn't the balance sheet balancing?
• 05:13 We've got to have the balancing item.
• 05:15 What is the balancing item for helping retain profits?
• 05:19 Look, net asset doesn't equal total equity anymore.
• 05:23 Well, the answer is I don't know what the balancing item is.
• 05:25 If I go back to the opening balance sheet here, the retained profit's here.
• 05:29 The opening balance, that's just one of them.
• 05:32 So to actually get the balance sheet to balance,
• 05:34 I need to bring in all the other opening balances.
• 05:36 So what am I going to do here?
• 05:38 I've got a thing for the way of getting the model to work.
• 05:43 My problem here is the opening balance numbers.
• 05:47 I said before, we're only responsible for
• 05:49 the movement in net assets equaling the movement in total equity.
• 05:53 Well, this is the opening balance sheet, and there's a clue.
• 05:56 I've got three ways of doing this.
• 05:58 Number one, is I could bring in all the opening balances now.
• 06:01 The problem then is I don't know when I link them all in,
• 06:03 which formula then are finished, and which ones I've gotta go back to.
• 06:09 That's not ideal.
• 06:09 Number two is just accept the balance sheet doesn't balance.
• 06:13 Put everything in, and hope at the end, everything works.
• 06:16 Well, that's what everyone does, and it never works that way.
• 06:19 There's an option three.
• 06:21 Cheat, and that's what we're going to do.
• 06:24 We're only responsible for
• 06:26 the movement in net assets equaling the movement in total equity.
• 06:29 So when we go back to the opening balance sheet, you get rid of the problem.
• 06:33 I'm gonna take everything here, right down here,
• 06:36 I'm not going to actually drag it across.
• 06:39 I'm gonna go copy, Ctrl+C, not cut, copy.
• 06:42 I'm going to go two columns, then I can go paste, Ctrl+V.
• 06:46 Now, I'm going to do something you might not feel so comfortable about.
• 06:51 Take all this back, and hit Delete.
• 06:54 I have cleared out the opening balance sheet, but
• 06:57 I can bring it back in by copying and pasting from column K back into column I.
• 07:01 Now, my balance sheet balances.
• 07:04 Now, anything that actually goes wrong, any errors we have, it's my fault,
• 07:09 it's on me.
• 07:09 The opening balance sheet has been zeroed.
• 07:12 Fine, we can carry on.
• 07:15 Net profit after tax next, that's pretty easy.
• 07:17 So the income statement, right down to the bottom.
• 07:21 Here's my net profit after tax.
• 07:23 Copy that across.
• 07:25 And then finally, we've got dividends declared to deduct.
• 07:29 Hang on a minute, how do we do that?
• 07:33 I have no idea yet.
• 07:34 So, because I have no idea, we have linked it.
• 07:37 We'll actually highlight the cells, and we'll go and call it work in progress.
• 07:41 And in column A of that row,
• 07:43 we're going to put in the letter W for work in progress.
• 07:46 Just as I explained previously.
• 07:49 We've now linked our first financial statement in.
• 07:52 We've got one down, one to go.
• 07:56 This is something that is work in progress.
• 07:58 We will come back and remember it.
• 08:00 Because remember, when we save,
• 08:01 we will always notice that there's something in row 49 to do.
• 08:04 But don't worry,
• 08:04 the process we're using to build up this model, we'll come back and revisit anyway.
• 08:09 Let's move on.

Lesson notes are only available for subscribers.