CRaFT - Robustness

Quick reference

• 00:04 Let's take a look at our second key quality, robustness.
• 00:11 Again, it makes up part of the ideology of CRaFT.
• 00:16 CRaFT, a reminder, has four key qualities,
• 00:19 the second of which is robustness, so what do we mean by that?
• 00:26 Well models should be materially free from error, mathematically accurate and
• 00:30 readily auditable.
• 00:32 Now let me just be clear what I mean here.
• 00:34 Materially free from error means there's no big mistakes left in it.
• 00:38 The perfect model, as far as I'm aware, has yet to be built.
• 00:42 Mathematically accurate doesn't mean that the inputs are accurate, but
• 00:46 that the operations on the inputs are correct.
• 00:50 An auditable means it's easy for somebody to actually check it.
• 00:55 Therefore we need to develop a review spreadsheet under philosophy
• 00:58 of keeping things simple, consistent and transparent.
• 01:02 It's all back to the idea of CRaFT..
• 01:05 So long as you should provide strong inherent levels of structural, logical,
• 01:09 and formulaic sturdiness.
• 01:11 That's what we're trying to promote here.
• 01:13 Ideally then, well built models should contain no hidden macros.
• 01:17 That doesn't mean you can't have macros, it means they shouldn't be hidden.
• 01:20 People should be aware they exist.
• 01:22 There should be checks in there.
• 01:23 And we split checks.
• 01:25 Into three types called error, sensitivity, and alert checks.
• 01:28 Don't worry about that for the time being.
• 01:30 We're gonna talk about that later.
• 01:32 And we should be able to check the formulas and
• 01:34 be able to audit them as we see fit.
• 01:38 So an example with checks then could be, you know,
• 01:39 that they should be explicit in the model.
• 01:41 They should be highly visible and you know if they're working or not,
• 01:44 as the case may be.
• 01:45 An example for the balance sheet or cashflow statement could therefore be
• 01:49 identifying omissions or double counting, incorrect accounting entries, and
• 01:53 they should highlight through every sheet, so matter where you are,
• 01:57 you can see it as a problem Let's look at the idea of robustness, then,
• 02:00 from an Excel examples perspective.
• 02:06 Let's take a look at this example of what I call a rather poor model.
• 02:11 It's not really a model, as a workbook with a few calculations in it.
• 02:15 There's two sheets all-together, we've got Sheet1 and we've got Sheet2.
• 02:21 Isn't that exciting?
• 02:24 No labels, errors everywhere.
• 02:27 You see, hash two, zero.
• 02:27 And here we've got hash name and hash ref.
• 02:31 These are what we know as Prima facie errors.
• 02:36 They start with the hash or the pound symbol, and then they have various names
• 02:39 like hashdivisionbyzero, hashed, hashnull, hostname, question mark, hashbrown.
• 02:46 No, that one's me, one I've made up, sorry.
• 02:48 I told you I have a bit of a sense of humor.
• 02:53 It's embarrassing to send out a model that's got these sorts of errors in them,
• 02:57 it looks like we don't know what we're doing and
• 02:59 it's so easy to fix these before we send them to anyone.
• 03:02 You see home tab?
• 03:05 You see the final grouping the editing section has a magnifying glass at the end
• 03:09 find and select, if I click on this and go down to the first one, find,
• 03:13 Keyboard shortcut CTRL + F.
• 03:16 It brings up this wonderful dialogue box.
• 03:18 Now before I use this,
• 03:20 I'm going to change the options by selecting the Option button.
• 03:23 And I'm going to make sure it's set to Workbook rather than Sheet.
• 03:26 Sheet tends to be the default.
• 03:28 Last time I used it, it was on Workbook It's up to you whether you choose rows or
• 03:32 columns, but you need to select for both formulas and values.
• 03:37 What are we actually looking for?
• 03:38 Well, like the police, there's a joke coming.
• 03:42 We're searching for hash.
• 03:44 I'm going to go not Find Next, but Find All.
• 03:47 And it shows me two examples.
• 03:50 Now you might not have spotted that cell E5 has a formula in it that's
• 03:54 got a hash ref.
• 03:56 It never occurs because one doesn't equal two but there's a mistake in there,
• 04:00 nonetheless.
• 04:02 Similarly, this one is also being
• 04:05 found being found because of the fact that it's a formula with an error in it.
• 04:09 But the off is on.
• 04:09 Now why is that?
• 04:10 Well if I change to values and then find all, you'll see why.
• 04:15 Values is here, there's no error.
• 04:17 But it's referring to something that has an error in it.
• 04:19 What the problem here is, is it's showing errors
• 04:24 that result from formulas rather than the formulas themselves.
• 04:27 Well, I can extend the box for C.
• 04:29 Notice, I can toggle down here, and I can change them.
• 04:34 So for instance, for this one I can delete the by zero.
• 04:39 Press enter, and it's fixed.
• 04:41 You may notice it's not updated.
• 04:44 In the list.
• 04:45 And that's because you have to press Find All again.
• 04:47 When you do, it's fixed it.
• 04:50 And there was another cell that was referring to it as well and
• 04:52 that's been fixed, too.
• 04:53 And you should clear all of those before you continue and save the model.
• 04:58 Very, very useful tip.

Lesson notes are only available for subscribers.