Checking Again Part 2

Quick reference

• 00:04 One more time for error checks.
• 00:07 I've covered off two checks now on my balance sheet.
• 00:10 I've done a prima facie error check to make sure there
• 00:13 are no errors in here like equals three divided by zero.
• 00:18 All fitness and balance.
• 00:22 But what about if I'm insolvent?
• 00:24 That is my net assets.
• 00:25 My assets less my liability is less than zero.
• 00:28 That's if my equities are at zero.
• 00:31 Well, I can only check for that cause there's no prima facie errors, and
• 00:34 my balance sheet balances.
• 00:35 If it doesn't balance, it's meaningless.
• 00:37 So I'm going to put in here equals if, open brackets,
• 00:43 the sum of the two checks above,
• 00:50 don't equal zero, then zero.
• 00:53 So we're not gonna bother checking for it, otherwise we're going to just check
• 00:58 that my net assets here J42 are less than zero, times one.
• 01:03 So only flag if it's less than zero.
• 01:07 Now again, I'll go in here and I'll actually go to my format paint brush.
• 01:13 And I'll come in and I'll bring this down.
• 01:16 And I'll call this range name.
• 01:20 hl_bs_insolvency.
• 01:30 Now, if I make that 7, it doesn't balance.
• 01:37 If I put -7 in here it does balance and I'm insolvent.
• 01:42 This isn't an error, this is actually an alert check.
• 01:46 We've actually got warning, warning, working, working fine.
• 01:49 It's balancing, but
• 01:51 you might want to know you're not supposed to trade in this particular situation.
• 01:54 So this is an example of a different type of check.
• 01:57 Not a sensitivity one where we're flagging it's in the base case or not.
• 02:01 This is actually saying this is something you might want to know.
• 02:05 Now we should put that on its own sheet because it's a simple training model
• 02:08 I'm just going to put this into the actual error check sheet and
• 02:12 have them all in one so I'm going to have insolvency check.
• 02:19 For balance sheet.
• 02:25 And again what I'm going to do here, if I click on that aah!
• 02:28 We go here, so this problem with actually having hyperlinks.
• 02:31 So you have to click on them with the cursor.
• 02:33 So you go down here, like this.
• 02:36 And then that way you can Ctrl+C Ctrl+V so you've got a copy of it.
• 02:40 We're going to make it equal to h l.
• 02:43 And we're going to go make hit this one, the insolvency one.
• 02:50 Control K, file to the link, we want to change it again.
• 02:54 We want it be HLB and some solvency.
• 02:56 And then we've got to go and use the format painter.
• 03:02 So now in here to the paint brush.
• 03:04 And then just click on that, done.
• 03:07 I've got three.
• 03:07 Now, you may notice the little green comments that keep coming up everywhere
• 03:11 on here and here.
• 03:11 This is because we've got formula on cells that should be unprotected.
• 03:16 Now, these should be unprotected.
• 03:18 If if go Ctrl+1, it's best to keep these cells unprotected, unlocked like this.
• 03:24 Because hyperlinks won't work necessarily in all versions of Excel if they're not.
• 03:29 But Excel provides this error.
• 03:31 Now if I go into Tools, Options of File, Options down here,
• 03:36 I can actually switch that off by going to formulas and
• 03:40 I actually want to enable background Error Checking, and uncheck it and click OK.
• 03:44 Not sure I recommend this, but at least that gets rid of those little comments,
• 03:48 should you do that.
• 03:49 Right, so I've now done that for the balance sheet.
• 03:52 Aren't I forgetting something?
• 03:54 Shouldn't I do something for the opening balance sheet?
• 03:57 Shouldn't I have similar ones down here?
• 03:59 Shouldn't I just put something there?
• 04:01 Well, again, by the magic of editing, voila, I now have my checks in here.
• 04:07 I'm just doing the same thing again.
• 04:09 I've gone through, I've made the opening balance sheet, prima facie error check.
• 04:14 I've done an opening balance sheet.
• 04:16 I already checked to check the balances and solve them in C-1.
• 04:19 And that's just that rounding accuracy there,
• 04:22 I said that we shouldn't put hard code in.
• 04:23 If you want to know what rounding accuracy is,
• 04:26 just look at it in all these we've got.
• 04:28 You see, there's rounding accuracy, it'll find it.
• 04:31 I've actually defined, in my parameters,
• 04:33 rounding accuracy to find decimal places, that was the idea.
• 04:38 F5 and take me back to where I was.
• 04:39 So there it is, I've got my balance check.
• 04:42 Notice as well I've given these all range names, HL open balance sheet,
• 04:46 arrows, HLL open balance sheet balance, HL opening balance sheet solvency and
• 04:51 of course HL stands for hyperlink.
• 04:53 I'm trying to put them all in one place.
• 04:55 So if I go to Formulas and Name Manager and
• 04:57 I want to look at all my hyperlinks quickly, they're all together.
• 05:00 They're all in the HL sections, I can find them quickly, that's the whole point.
• 05:05 But, better than that, I've also added them to my actual sheet here.
• 05:11 I've got all six of these all hyperlinked in, it's just summing them.
• 05:15 Taking a minimum of one and the sum of all of these values so I've got them all here.
• 05:19 Click on one of these and it takes me back.
• 05:22 Looking good.
• 05:23 So I've now got my check saved.
• 05:24 I've put my financial statements together.
• 05:27 Next step, linking.

Lesson notes are only available for subscribers.