Indirect Cash Flows Checks

Quick reference

• 00:04 So we have one last thing to do on the cash flow statement.
• 00:07 We've gonna put some checks in, two of them to be precise.
• 00:11 It's our last time at the model!
• 00:14 Parting is such sweet sorrow, maybe it isn't.
• 00:18 It might be thank god this is almost finished.
• 00:21 We finished off last time, if you remember if we went to the cash flow statement.
• 00:26 We've got down at the bottom, the indirect extract, and it actually reconciled.
• 00:32 But what I want to do, let's put in checks so that we can always prove that.
• 00:39 I've put in a little check section down here.
• 00:42 Two checks, the prima facie error check, just like we had in the balance sheet.
• 00:45 And a reconciliation check, which is going to check that our net operating cash flow
• 00:49 is calculated the same in both ways.
• 00:50 And it's very similar formula to the ones we used in the balance sheet,
• 00:54 you recall here.
• 00:55 Back here, it's this one, the if is error.
• 00:59 And I'm just going to combine
• 01:02 the actual overall cash with the indirect extract that we've done.
• 01:05 So I'll just add them together for this one,
• 01:07 rather than subtract total equity from net assets.
• 01:11 I'm going to actually subtract one operating cash flow from the other.
• 01:14 Otherwise, I'm going to use the rounding accuracy.
• 01:16 Which if you recall from the model parameters page the rounding accuracy
• 01:21 is down here, where is it?
• 01:26 There it is, and
• 01:27 it's showing that we're going to calculate it to five decimal places.
• 01:31 So let's go back to the cash flow statement then, and
• 01:34 put these corresponding checks in.
• 01:36 And look, if we're gonna do that, why don't I just take these cells here and
• 01:41 go Ctrl+C, copy.
• 01:44 I'll put them in here, paste special as formats.
• 01:48 So I've got all the actual formatting done nicely.
• 01:54 This is going to equal the minimum open brackets of the sum
• 01:58 of the cells to the right, and 1, close bracket.
• 02:04 So and we'll copy there, so if I put 14 in here, for instance, I'll get two errors.
• 02:09 So yes, it's all working nicely.
• 02:11 So now I just need to put the actual formulas in.
• 02:14 So =if(iserror) just as I showed you a moment ago.
• 02:21 We're going to add up the net increase or
• 02:24 decrease in cash held with the other operating cash flow that we did.
• 02:31 Close bracket, and if that is an error, we put a one, otherwise we put a zero.
• 02:37 And then we'll copy that across, pretty straightforward.
• 02:40 The other one only calculates as long as there is no error.
• 02:43 So equals if open bracket,
• 02:46 this cell is not equal to 0 then 0.
• 02:51 Otherwise open brackets round, and we're going to take
• 02:57 this net operating cash flow less this net operating cash flow.
• 03:03 To we use the Rounding_Accuracy that I explained before to five decimal places.
• 03:07 Close bracket, is not equal to 0, times 1, close bracket, press Enter.
• 03:15 Lovely, just like the balance sheet.
• 03:17 Now we copy that across, all happy.
• 03:20 But they're not linking yet into the overall checks here.
• 03:24 You see I've put in two lines, for Cash Flow Statement has no errors, and
• 03:27 Cash Flow Statement reconciles.
• 03:28 But there's nothing there!
• 03:30 So I need to actually go and link them in.
• 03:33 To do that, remember I need to first all give them range names.
• 03:36 So that if the sheet name changes, it will still work.
• 03:40 Okay, so I'll go up to the name box here and
• 03:46 call the first one HL_Cash_Flow_Statement_Error, press Enter.
• 03:56 Always go back in to check if it is working, yup.
• 03:59 And the next one, I'm gonna call this one
• 04:06 HL_Cash_Flow_Statement_Rec_Check.
• 04:15 And again, check it actually works.
• 04:17 Brilliant, I've got my two hyperlinks getting ready there on my destinations.
• 04:21 So let's go to our error checks and put these in.
• 04:24 The first one is gonna go, well I just start typing in HL_, underscore not dash.
• 04:31 There they are, there's the error one first of all.
• 04:34 Press Tab to select it, Enter.
• 04:36 And this one is going to be HL_Cash.
• 04:40 This is going to be the rec check, Tab Enter.
• 04:43 We need to actually make these into hyperlinks now.
• 04:47 So that's simple, I can go to insert hyperlink or Ctrl+K,
• 04:52 place in this document and it's HL Cash Error, that one.
• 04:57 Bang, done and this one Ctrl+K, do it the other way,
• 05:03 HL_Cash_Flow_Statement_Rec_Check.
• 05:08 And I'm just, I can't click on this, cuz if I click on that,
• 05:11 it will take me to that check.
• 05:13 So I have to use the arrow keys to get me here.
• 05:16 And I'm going to take that one here, let's say,
• 05:19 Home > Format Painter, and select both of these, bang, done.
• 05:24 That is now, now we'll check if this goes 17, that's triggered.
• 05:30 And you can see it's not coming off here.
• 05:32 And you can set output = 4/0.
• 05:36 That one's triggered, and it still goes off, lovely.
• 05:39 We've completed our error checks, we've now got our indirect and
• 05:44 direct cash flow statements to reconcile.
• 05:48 We've got our three-way integrated financial statement working.
• 05:51 Everything is balancing, we have finished albeit, a rather simple model.
• 05:56 But hopefully, you understood the concepts of our model, congratulations!
• 06:00 Round of ammunition, please.
• 06:02 You've finished your first, okay be it simple.
• 06:05 But hopefully you understand the concepts, financial model, well done!

Lesson notes are only available for subscribers.