Locked lesson.

Upgrade

About this lesson

Understand the different types of checks required when building a Financial Model and how to add them into the model.

Exercise files

Download this lesson’s related exercise files.

Checking Again.xlsm
80.3 KB Checking Again - Solution.xlsm
80.4 KB

Quick reference

Checking Again

Discover how to add checks.

When to use

When constructing a basic financial model.

Instructions

Overview

  • Key Attributes of Checks
    • Checks classification
      • Error checks
      • Sensitivity checks
      • Alert checks
    • Dedicated checks worksheets
      • Errors
      • Sensitivities
      • Alerts
    • Check indicator flags
    • Check cell conditional formatting
  • Master Checks
    • Checks are custom formatted to appear as “OK” or “Error”, however they are actually numbers to enable us to calculate a master check
      • Master Check = MIN(SUM(All_Checks),1)
      • If checks are not numbered this leads to a long and complicated embedded IF statement with lots of words
      • Only the master check needs to be shown at the header. If it is showing ‘Error’ then the user can trace back to the Check worksheet to find which check(s) is are actually failing
Login to download
  • 00:04 You can see how important checks are 'cuz I keep coming back to them.
  • 00:08 It's something you need to do at the beginning and
  • 00:10 not as an afterthought at the end.
  • 00:14 I've called it error checks it's a whole lot cuz this is what people
  • 00:17 refer to them as error checks being the three times.
  • 00:20 Yes, you can perform them manually or
  • 00:22 automatically but wouldn't it be better if they're in the model already?
  • 00:26 Remember what we can do is we can use the emissions double count,
  • 00:30 checking entries and so on.
  • 00:31 We want to tick to say everything's working fine across otherwise.
  • 00:36 And we have different classifications, the error, the sensitivity tempted deal that
  • 00:40 I can use our worksheet system to put it together.
  • 00:44 We have an overall check.
  • 00:45 So if we utter the check for all period,
  • 00:47 we have an over our check the entire calculation.
  • 00:50 Which is the minimum of the sum of all of those checks in one.
  • 00:54 So that every valley you can only ever be zero or one.
  • 00:57 Zero, everything is fine.
  • 00:59 One, there's a problem.
  • 01:01 We therefore have a dedicated check sheets or have all our checks on here.
  • 01:05 We summarize them at the bottom and that's the thing that flows through the model.
  • 01:10 The checks worksheet system,
  • 01:12 put your checks on each sheet, link them through to the dedicated check sheet.
  • 01:17 Think it through to all of the sheets.
  • 01:20 Just as you go through you'll see the actual error check flags as you go,
  • 01:23 that's it.
  • 01:25 So an example of an error check, prima facie errors.
  • 01:27 Does your balance sheet balance?
  • 01:28 Is the cash in your cashflow statement equal to the cash on your balance sheet?
  • 01:33 Sensitivity check.
  • 01:34 Are we in the best case?
  • 01:35 Yes or no?
  • 01:37 And alert checks, just everything else.
  • 01:40 Revenues are negative.
  • 01:41 Debt service cover ratio is below the actual minimum required, etc., etc.
  • 01:48 Let's go do something in Excel.
  • 01:51 We've covered these sorts of checks before.
  • 01:53 I'm going straight to the balance sheet in our model that we put together before.
  • 01:58 You may remember that people think the first thing you should check for
  • 02:01 in a balance sheet is the balances.
  • 02:03 But first we want to check there aren't any fundamental prima facie errors.
  • 02:08 So let's put a formula in.
  • 02:10 So in my actual Excel file let's go here to this cell,
  • 02:14 cell J56 where I've built a little check section.
  • 02:18 And I'm going to use the formula I use in the preparation section of the course,
  • 02:23 which is f open brackets is error, open brackets,
  • 02:28 the actual letter sets less to the equity.
  • 02:32 If that is an error, than put a one otherwise put a zero.
  • 02:38 It's not and if I put in here equals three divided by zero.
  • 02:42 It's now a one.
  • 02:43 That's what it's doing.
  • 02:44 It's checking for any primer phase e errors.
  • 02:47 Now I need to stall this and I'm going to actually steal this format from up here.
  • 02:51 Let's use that.
  • 02:52 I'm just gonna make it a milder green.
  • 02:55 I'm going to make the, it's size ten instead.
  • 03:00 And we're going to make the font color a little paler green so
  • 03:03 that we can see it a little bit easier like this.
  • 03:06 And I can copy this across.
  • 03:07 And this is using conditional formatting.
  • 03:12 So the home tab, conditional formatting.
  • 03:15 We would look to this in the preparation.
  • 03:17 So managing the rules here we've got one here that's the cell value is at zero,
  • 03:22 make it yellow on red.
  • 03:23 And then I've also used number formatting, so Ctrl+1, and
  • 03:27 I've used these special characters here.
  • 03:30 And what I've done is used in terms of
  • 03:34 we'll go back here Wingdings to make it look like a tick or a cross.
  • 03:38 Just nice and cool like that.
  • 03:39 Makes it look good.
  • 03:41 Now remember we need an overall check as well.
  • 03:42 So we'll take this and we're going to use this formatting here.
  • 03:46 And I'm going to take that and put it here.
  • 03:49 We're going to make it darker green.
  • 03:52 So I've got to show this as my overall check and
  • 03:55 that is going to equal the minimum.
  • 03:57 Open brackets of the sum of all of those.
  • 04:03 And one, for the reasons we explained before.
  • 04:05 So we got an overall check.
  • 04:07 And I'm gonna give that a range name.
  • 04:10 I'm gonna call that one HL_BS_Errors,
  • 04:15 so that's picking that up there.
  • 04:21 Wonder, I'm going to have a balance check then.
  • 04:24 If you remember what I did for
  • 04:25 that before, that's going to be equal to if, open brackets.
  • 04:29 I have got in here this value here is not equal to zero.
  • 04:36 Then it's just going to be zero.
  • 04:39 Otherwise in brackets are going to round the net assets minus
  • 04:43 the total equity to, well I could say five decimal places.
  • 04:48 But I've actually got a linear to co variance system called Rounding_Accuracy.
  • 04:53 I'll show you in a moment,
  • 04:54 Rounding_Accuracy and then we'll go close bracket.
  • 04:58 It's not equal to zero, close brackets, times one, close brackets.
  • 05:04 And that's actually checking to see yes, it's outside of totals and balancing.
  • 05:08 And again we'll star this.
  • 05:13 Okay, that's cross.
  • 05:15 I'm gonna take this formula down.
  • 05:17 Or be this arranged name
  • 05:22 of HL_BS_Balance.
  • 05:26 Now coming to the solution in a minute but
  • 05:28 let's just show you how these now go into my error check sheet.
  • 05:32 So if I click on here for the error checks sheet.
  • 05:34 I'm going to have here balance sheet has no errors.
  • 05:42 And that's going to be equal to HL_, and
  • 05:45 you see by going through this I get the balance errors, and that's all fine.
  • 05:51 And then I copy this to cheat so I don't have to do it a second time around.
  • 05:55 Clear in a minute and I'd like to insert a hyperlink.
  • 05:59 As we have actually done before, insert hyperlink, place in this document.
  • 06:04 And I want it to be which is why we've scrolled down here HL,
  • 06:07 I will find them in a minute, HL_BS_Errors, click OK.
  • 06:13 It doesn't look right which is why I've used this particular formatting here.
  • 06:17 That's what your formatting links it back all nice and pretty then.
  • 06:21 Click on that.
  • 06:22 It's now hyperlink working fine.
  • 06:24 To finish off then, I'm going to have balance sheet balances.
  • 06:30 And that's going to equal HL_B.
  • 06:38 You can see it balances the first tab,
  • 06:41 same thing again Ctrl+K is the keyboard shortcut.
  • 06:45 Look for my actual balance sheet balancing in here.
  • 06:49 It's not long, click okay.
  • 06:51 Again looks horrible.
  • 06:52 So we'll take this.
  • 06:54 Use the format painter.
  • 06:57 And then what we can do is if we can go back to the balance sheet,
  • 07:00 if I were to put a 7 in here so it no longer balances.
  • 07:02 Do you see that the average book, look on these sheets.
  • 07:05 Cuz all these sheets would cross when you put in there,
  • 07:07 the checks worksheet system is now working.
  • 07:09 We have a problem Houston but everything's working fine.
  • 07:12 So we've got time, let's look at checks again in the next session.
  • 07:16 Also look at insolvency and the opening balance sheet.

Lesson notes are only available for subscribers.

Gift this course