Locked lesson.

Upgrade

About this lesson

How to create a prima facie error check.

Exercise files

Download this lesson’s related exercise files.

Error Checks.xlsx
9.3 KB Error Checks - Solution.xlsx
9.4 KB

Quick reference

Error Checks

Error checks explained using the CRaFT methodology.

When to use

Many modellers 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

Overview

  • Error checks – the model contains flawed logic or prima facie errors, e.g. Balance Sheet does not balance, cash in cashflow statement does not reconcile with the balance sheet, or the model contains #DIV/0! errors etc;

Example

  • Balance Sheets have to balance so this example will put an error check in to ensure it balances
  • The formula =C6=C10 is pretty straightforward, but it is not recommended. Most financial models will have many time periods and therefore will need a check for each period. Further, that’s just one check out of many. It would be difficult reading through all of the error checks and making sure they all equal TRUE?
  • First thing is to put the formula in brackets and multiply the bracketed expression by 1:
  • The Balance Check in this illustration, =(ROUND(C6-C10,5)<>0)*1 alerts when C6 (Net Assets) does not equal C10 (Total Equity) to five decimal places.
  • In example, the reference in cell C5 no longer exists giving rise to an #REF! error. Unfortunately, this does happen in models.
  • Therefore, if someone accidentally deletes a key reference, an error check could alert the modeler. The problem is, in the example above, while the check may show the alert as, #REF! is not necessarily the ideal way to display this. A better process is using the 1 / 0 system

 

  • The Prima Facie check=IF(ISERROR(E6-E11),1,), provides the value 1 if Net Assets less Total Equity may not be evaluated.
  • The Balance Check could now be updated to be =IF(E14<>0,0,(ROUND(E6-E11,5)<>0)*1)
Login to download
  • 00:03 Having looked at checks overall,
  • 00:05 I'm now going to look at the most important of the three, error checks.
  • 00:13 I mentioned last time out that checks can be performed manually or automatically,
  • 00:17 but obviously this is something where automation can clearly win.
  • 00:21 It's good to put them in the model right at the outset, and
  • 00:25 what better than the actual errors?
  • 00:28 Now, what do I mean by an error check?
  • 00:30 An error check is something where the model does not contain flawed logic or
  • 00:33 the prima facie errors I've discussed previously.
  • 00:37 It's not about materiality of the errors,
  • 00:39 it's about the fact whether an error exists or not.
  • 00:42 That is, if it's wrong, it's wrong.
  • 00:45 The end, nothing more simple than that.
  • 00:48 So, you can come up with breaking it down into a simple basis of knowing whether or
  • 00:54 not something should be correct or not correct, black and white.
  • 00:58 Now the graphic in this particular slide is showing a true.
  • 01:01 I think really you should use zeros or ones, and
  • 01:04 that's what I'm going to To do in the following Excel example.
  • 01:09 How about this for the most basic balance sheet model you ever did see?
  • 01:13 Now, if you aren't an expert on balance sheets, don't worry.
  • 01:17 We are going to be talking about balance sheets and what they mean later.
  • 01:20 Essentially it has two totals in it, which have to equal each other.
  • 01:25 Eleven, row six, and eleven here, see 70 and 70.
  • 01:29 The net assets total is in
  • 01:30 the International Financial Reporting Standards, IFRS presentation,
  • 01:35 rather than United States generally accepted accounting Principles.
  • 01:39 US gap, we have net assets as one of the totals.
  • 01:43 If your accounting firms says, no, it's not put into it like this, don't worry.
  • 01:47 This is trying to show you about checks,
  • 01:49 let's not get too hung up on accounting at this stage.
  • 01:52 Net assets is assets less liabilities, so
  • 01:56 100 minus 30 equals 70 For the total equity,
  • 02:01 it's made up of what's the shareholder's balance, in terms of what's due to them.
  • 02:06 And the retained profits, 50 plus the 20.
  • 02:09 It equals.
  • 02:11 Now one of the things I can do is, I can do a simple check here saying,
  • 02:14 okay This equals that, this gives you either a true or false.
  • 02:18 And as I said before, this isn't what we want, we want it to be naughts or
  • 02:22 ones, so putting it in brackets, as we have done before, and multiply it by one,
  • 02:27 gives us a naught of one, false times one is naught, true times one is one, see?
  • 02:32 You're learning your true times tables Now,
  • 02:35 I don't want anything that's good to be a one.
  • 02:39 I don't want to know I've got 15,412 things working correctly.
  • 02:42 I want to know I've got two errors.
  • 02:44 So better than saying E6 equals E11,
  • 02:47 I'm going to actually say E6 does not equal E11 by going.
  • 02:51 E6 less than greater than E11.
  • 02:53 Because less than great is the actual way of putting an Excel formula not equals to.
  • 03:00 That's okay on it's own, but sometimes you have a limitation Excel which totals.
  • 03:06 because Excel calculates to significant figures and uses and and
  • 03:10 all sort of other algorithms.
  • 03:12 Nothing end in rhythms.
  • 03:14 It can actually cause minor errors.
  • 03:17 When you actually add on numbers, they can just find that the ninth and tenth decimal
  • 03:21 place is dismounting which comes up to nowhere, which is not your fault it's just
  • 03:26 one of the limitation of excel, to counter this we can use function called round.
  • 03:32 what round does is it rounds numbers up And you can round them off to the nearest
  • 03:35 whole number, or one decimal place, or two decimal places, as you see fit.
  • 03:40 So if I =(E6 to five decimal places, okay, I'm not supposed to put
  • 03:45 hard code in here, but look, I'm trying to show something else at the moment.
  • 03:51 And do the same on this side, that will get rid Of the actual round issue.
  • 03:57 But I've got two round functions in here which will slow a formula down
  • 04:02 compared to using operators like times and rates and etc.
  • 04:06 Other way to get round that we need to do instead E6
  • 04:10 minus E11 To five decimal places, isn't equal to 0.
  • 04:14 Not only is it shorter, it's actually using only one function.
  • 04:19 That make sense?
  • 04:20 Nice simple error check.
  • 04:22 It doesn't balance, it's telling me error, need to fix it.
  • 04:28 Problem with this, is This one actually be linking to something else,
  • 04:32 let's say Total Liabilities, TL, 30, over here and I actually link to this.
  • 04:41 Somebody accidentally deletes this.
  • 04:44 I know get to that happens in sometimes.
  • 04:48 It's not necessarily my fault.
  • 04:50 But do you see the balance check is left out?
  • 04:53 Don't really want that do we?
  • 04:55 The way we get around that is the face check.
  • 05:00 Another error check.
  • 05:02 And we are going to say equals if And we use an is function.
  • 05:10 Is function of this minus that, is error gives a value of true,
  • 05:16 if it's an error, and a value of false if it isn't.
  • 05:22 So I can actually go here, is error E6-E11 A 1 if it's an error, and a 0 otherwise.
  • 05:30 And then here, I can use another if statement saying, okay, equals,
  • 05:36 if this value here is anything other than 0,
  • 05:41 I want you to put 0 here, don't even bother calculating it.
  • 05:45 We are only going to calculate it when there isn't an error.
  • 05:49 That way if I have got an error it tells me on this one and
  • 05:52 we just format this the same.
  • 05:58 And if I don't have an error, which it doesn't balance, that one will go off.
  • 06:05 And if it's all working perfectly, they'll both be fine.
  • 06:08 These are examples of various checks in reality.

Lesson notes are only available for subscribers.

Gift this course