Locked lesson.

Upgrade

About this lesson

Highlight the situations where checks are useful, and how the different checks fit into 3 categories. Error Checks, Sensitivity Checks, and Alert Checks.

Exercise files

Download this lesson’s related exercise files.

Overall Checks.xlsm
40.3 KB Overall Checks - Solution.xlsm
40.3 KB

Quick reference

Overall Checks

Overall checks explained using the CRaFT methodology.

When to use

Many modelers add checks as an afterthought. Basically, it is too late to create them then. While building a model, a developer knows what situation might break a formula. That is when a check should be created. When the issue is foremost in your mind, create the check there and then.

Instructions

  • Check can be performed manually or automatically
  • Tests must be objective and repeatable
  • Aim is to detect and / or alert users to the state of particular outputs or the occurrence of particular events
  • Various checks should be explicit in the model
  • e.g. Balance Sheet and Cashflow Statement checks
    • Identification of omissions / double counting
    • Identification of incorrect accounting entries
    • Time saving – link error check through to every sheet
  • 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
  • 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
  • Checks worksheet system

 

Login to download
  • 00:05 Next, an area often neglected by modelers, even experienced ones, that's checks.
  • 00:11 Often thought about putting in right at the end,
  • 00:14 it's something you should be considering at the beginning and
  • 00:16 integrating into the template if you're setting one up.
  • 00:19 Let me explain.
  • 00:20 The idea of self review in a model is not something new.
  • 00:26 We like to check things because it can be rather career limiting if we make major
  • 00:31 mistakes that go through with the line manager working it out before we do.
  • 00:36 Therefore, there's tools that can help us.
  • 00:40 The Auditing Toolbar on the Formulas tab of the ribbon has error checking,
  • 00:44 trace dependents and precedent tools to show whether
  • 00:47 formulas are actually being used, and we can evaluate formula if we want to.
  • 00:51 This isn't about that, that's an auditing course for another day, but
  • 00:55 it's things that can help you.
  • 00:56 We can also the Go To dialog box, Ctrl+G or F5 which will let you go and
  • 01:01 look for things.
  • 01:02 You can go to back where you were before,
  • 01:04 you can go find data validation as I showed you by pressing the special button.
  • 01:08 What I want to talk about more is the idea of checking.
  • 01:13 Yes, it can be performed manually, but why do all the heavy lifting yourself?
  • 01:17 Why not get the model to help you?
  • 01:19 The test has to be objective and repeatable.
  • 01:21 Well surely that is exactly what a formula is.
  • 01:24 It aims to detect and alert the user as soon as it happens.
  • 01:28 As soon as you type something in, you want it there.
  • 01:30 This why checks should be in at the beginning.
  • 01:35 We've talked about checks before, there's all sorts of them.
  • 01:37 You want to see if there's omissions, double counts, incorrect entries,
  • 01:41 whatever.
  • 01:42 Wouldn't it be good if you had a tick saying yes, everything is fine or
  • 01:46 a cross, no, it's not.
  • 01:49 That's what we're talking about here, different types of checks.
  • 01:52 Now soon, I'll be talking about the different classifications,
  • 01:55 error sensitivity and alert, that's next time out.
  • 01:57 And we would therefore have dedicated checks worksheets,
  • 02:01 which we might split up by those three types too.
  • 02:04 The idea then would be to have indicator flex and say everything's fine, and
  • 02:08 of course we can make it go green if it's okay, and red if it's not,
  • 02:11 by using conditional formatting.
  • 02:14 We need master checks, we might need a nice formula that summarizes them all up.
  • 02:20 And what you do is use a formula like MIN, which takes the minimum, SUM,
  • 02:23 which takes the sums.
  • 02:24 So the min of the sum of all the totals on 1 will be 1, if the total equals 1 or
  • 02:30 more and 0, if the total is 0, if all the values are not 1.
  • 02:35 This gets you what we call Boolean algebra,
  • 02:38 in the everything is either a naught or 1.
  • 02:41 Always say if it's 0, everything is working fine and 1 is an error.
  • 02:45 That way you can count the errors in the model by adding up to the ones,
  • 02:49 not exactly rocket science.
  • 02:52 Checking all data, we have a summary sheet.
  • 02:55 It just brings in all the else where, so in this illustration here,
  • 02:59 my data is complete with all the invalid start date due to rest code whatever.
  • 03:03 I can bring you to in, have a summary of errors which sums up the numbers of both,
  • 03:08 ones to actually restricted to a maximum of a value of one.
  • 03:12 So how does it work then?
  • 03:15 Well we have some worksheets and we calculate error checks in them.
  • 03:19 You can see them, they're in green in the right hand side of both of those sheets.
  • 03:24 They feed through to a dedicated text worksheet where they are summarized.
  • 03:29 This is then creating a summary of errors total, which flows through
  • 03:33 perhaps save the output sheet where we can see if there's an actual issue.
  • 03:37 Once that's all right, we can flow through to the rest of the model.
  • 03:41 So the top of each sheet you can see this check flag to see whether everything's
  • 03:44 fine or not.
  • 03:45 Look, better to see it with the next cell example, don't you think?
  • 03:53 Here's something I prepared earlier.
  • 03:54 This is a prettier version of the template file we've been putting together.
  • 03:59 You can see the wonderful things like company logos, the name of the company,
  • 04:04 what it's actually doing on a cover sheet, we've even got a table of contents sheet.
  • 04:10 And look, here we have a Style Guide to actually describe all the customization we
  • 04:14 put in and if I go to Cell Styles, they're all here.
  • 04:17 And I just put them in, so people can understand when they print this model out.
  • 04:23 I've got details about things I might be using, and I have a dedicated check sheet,
  • 04:27 which isn't do anything at the moment.
  • 04:29 There's no checks in here, it's blank.
  • 04:31 This is my overall check with that mean of one sum of above.
  • 04:36 So you can see that it will either give a value of naught or 1.
  • 04:39 And this check here links to all the other sheets, that check there links back.
  • 04:45 But click on this, it's a hyper link, it'll take me back.
  • 04:47 Now to demonstrate how this checks worksheet system works.
  • 04:52 You can see it wherever you are and why she put it at the beginning,
  • 04:55 let me show you with a very basic example.
  • 04:57 The timing sheet here is creating the actual dates.
  • 05:00 I'll explain all that when we're looking at application at the financial
  • 05:04 modeling course.
  • 05:06 I'm going to create a check down here to make sure that the date is actually
  • 05:10 a positive number.
  • 05:11 If I were to just do equals that is greater than 0, that'll be true.
  • 05:16 Because the serial number here is going to be an excessive 40,000, so it's positive.
  • 05:20 That will cause a weird message down here,
  • 05:22 because this has actually been formatted to expect either a 0 or a 1.
  • 05:27 So we get this strange set of symbols down here,
  • 05:29 because it's actually coming up as true at the moment.
  • 05:32 To turn it into a 1, I actually put a bracket around it.
  • 05:36 Remember, true times 1 is 1, and now it's a red cross.
  • 05:40 Well a yellow cross in a red background.
  • 05:43 One of the things here is,
  • 05:45 we don't want to report that 17,465 things are working fine.
  • 05:49 We want to say we've got some errors.
  • 05:52 So it's better to actually say how many we've got by saying it's not
  • 05:55 greater than or equal or greater than, but less than or equal to.
  • 06:00 See, and that's working fine.
  • 06:01 Now how did we achieve this?
  • 06:04 Well we used conditional formatting in here.
  • 06:07 So if I go to Conditional Formatting and click on Manage Rules,
  • 06:10 you can see I've set it all up so that it goes red if there's an error.
  • 06:15 The other thing I did was I actually formatted the number, custom by putting
  • 06:20 a cyrillic y, if it's positive or negative or a cyrillic p, if it's 0.
  • 06:24 Why on Earth would I do that?
  • 06:26 Because I'm using Wingdings.
  • 06:30 And a cyrillic y looks like a red cross,
  • 06:33 looks like a yellow cross and cyrillic p looks like the ticks.
  • 06:38 That's how I've got this check working.
  • 06:41 So if I link this here to that check I just created.
  • 06:44 And then what I'll do, is I'll make it go wrong here by
  • 06:49 putting say -3 there, you'll see the error goes off.
  • 06:55 It goes off on that sheet, it goes off on that sheet,
  • 06:57 it goes off on all the sheets where it's visible.
  • 06:59 Do you see?
  • 07:00 Cuz it's fed through.
  • 07:02 This is the whole concept of the checks worksheet system.
  • 07:05 You need to put it to the beginning,
  • 07:07 because then you can see as you as you type something in that's wrong,
  • 07:11 you can flag it, yes, I need to correct it and this is why this is important.
  • 07:15 Next stop, we will look at different classification of our object.

Lesson notes are only available for subscribers.

Gift this course