CRaFT - Transparency

Quick reference

• 00:04 And so we come to the fourth of our four key qualities of our financial model.
• 00:09 You're probably getting sick of the idea of CRaFT by now,
• 00:15 Consistency, Robustness, Flexibility, and finally, Transparency.
• 00:22 You see a lot of people forget that when you're actually modeling,
• 00:25 you are not the principle customer.
• 00:27 It's the people who use it.
• 00:29 Most people who use it, they're going to use it for
• 00:31 decisions may not actually look at the Excel file.
• 00:35 They might actually be looking at some output at the end of a Word document or
• 00:40 on a Powerpoint deck.
• 00:42 So the models need to be clear, concise and cohesive.
• 00:45 So you can follow them on a piece of paper without the formula bar.
• 00:49 If you're going to create assumptions, calculations and outputs,
• 00:52 make it so it's so clear that the end user can actually see it and
• 00:57 decide whether they agree with you or not.
• 00:59 You've got to have a good logical flow.
• 01:01 Think about the number of categories you require and
• 01:04 how the assumptions should be grouped.
• 01:07 KEEP IT TRANSPARENT.
• 01:10 Don't use methods others don't follow.
• 01:12 Try not to show off with the latest function you've read about on one of our
• 01:15 newsletters.
• 01:16 Avoid array functions.
• 01:18 If you don't know what an array function is, that's great.
• 01:21 And don't hide rows and columns which contain relevant formulae,
• 01:24 show them a good look at what you're doing.
• 01:28 Calculation order, I said it before, should go from left and right and
• 01:32 down the page.
• 01:33 And the model then is easier to follow.
• 01:36 There's a good reason why I've got a picture of a thumb there,
• 01:39 and let me explain with the Excel example that follows.
• 01:44 Let's take a look at this transparency example.
• 01:48 I'm going to start off with a bad example.
• 01:52 I've got some dates in row 4.
• 01:54 Take a look at this wonderful formula in row 6.
• 02:00 You've probably seen formulas like this in the real world, and maybe even worse.
• 02:06 What on earth is that doing?
• 02:08 You may or may not be aware of the functions,
• 02:11 but it looks like a whole load of functions inside each of them.
• 02:16 These are known as nested functions, not a good idea.
• 02:20 If you were looking at this model, and you had to understand it, would
• 02:25 your heart sink if you had to explain to your line manager, what that was doing.
• 02:30 And you've got 30 seconds to do it.
• 02:33 Maybe this is a better way to achieve the same result.
• 02:37 That might give the right answer, but there are better ways to do it.
• 02:43 We can step it out.
• 02:44 Let's have a look at the better example.
• 02:48 Now why I call this a better example is I am going to allow
• 02:53 hard code in here just to illustrate a different point.
• 02:56 In reality you wouldn't have the hard code in the formulas,
• 02:59 you'd have those as constants or inputs somewhere else.
• 03:02 But more on that later.
• 03:04 I want to demonstrate the idea of transparency.
• 03:07 So I've got my dates and now I have my calculation in row 14.
• 03:12 How about I actually step it out?
• 03:14 Now if you don't know all these functions here, don't worry,
• 03:18 we'll talk about a lot of these later on.
• 03:20 That's not the point of this.
• 03:22 Make sure you're clear on the concept.
• 03:25 So to get the day from a date, you just type in here =day open brackets and click
• 03:32 on the actual cell here, which I'll make it F$4, we're using the F4 function key.
• 03:38 So that I can copy this across if I need to make
• 03:41 it somewhere else later it will always refer to row 4.
• 03:46 The month similarly, the month in itself.
• 03:52 And I'm gonna click on cell F4 again.
• 03:56 You may notice I actually type my functions in lowercase so
• 04:00 that if I press enter if they capitalize I know I haven't made a typo.
• 04:06 And equals here.
• 04:09 I'm stepping this out.
• 04:16 Good to see I'm keeping up to date with the years.
• 04:19 Now you might not know how to do a weekday.
• 04:22 There is actually a function in Excel called Weekday.
• 04:26 What you do is you actually put the date in.
• 04:29 The serial number.
• 04:32 And then you put comma, it says how it's going to score them.
• 04:36 Now, I want it to be a weekday, so I want it to be a Monday through to a Friday.
• 04:40 Apologies to those who don't work Fridays and other things here,
• 04:43 but I'm gonna go on Mondays to Fridays being the weekdays in this example.
• 04:49 I'm going to use type sixteen, down here.
• 04:52 Where Saturday is one, Sunday is two, and Friday is seven.
• 04:59 With that in place, I can actually go then,
• 05:02 fine, let's actually work this through and come up with the answers if I can.
• 05:07 So it's going to be this one, remember, okay select with the mouse and
• 05:11 press the Tab button.
• 05:12 Sixteen press Enter and I want that to be greater than two.
• 05:22 Put brackets around it nothing at times gives me true.
• 05:25 But if I put multiply by one, true times one, is one, but false times one is not.
• 05:32 So I'm replacing true and false with ones and zeroes, what we call Boolean Algebra.
• 05:38 If I copy that across.
• 05:42 Then the data is in the second half of the year.
• 05:45 Well, that's just if this is strictly greater than six times one.
• 05:54 Month is even requires another function
• 05:57 I'm not going to explain here is the mod function.
• 06:01 Basically, if I take the mod of the month, comma 2,
• 06:05 if that is 0 it means it's even.
• 06:09 Don't worry about that now.
• 06:12 I'm just.
• 06:13 Calculating it here so we can see it's even or it's odd.
• 06:16 We see here these two are 0 because it's 3 and 7 there.
• 06:21 The year is 1999.
• 06:30 Yep.
• 06:31 About to print here.
• 06:32 I'm going to party where it's 1999.
• 06:34 And the date is the end of the month.
• 06:36 I'm going to use another feature here.
• 06:39 The date here equals eomonth is end of month.
• 06:45 And when we do end of month without the comma zero,
• 06:48 it means the end of that month.
• 06:50 If I put comma one, it means the end of the next month, and so on.
• 06:53 So that's just checking for those dates.
• 06:58 Now, to work out that the date is a weekday, and
• 07:00 the final day of an even month in the second half of 1999.
• 07:03 Deep breath.
• 07:04 We're going to go here, equals, well, it's a weekday.
• 07:11 Multiplied by the fact that it's in the second half of the year,
• 07:15 multiplied by the fact it's even, multiplied by the fact it's a year,
• 07:20 multiplied by the fact it's end of the month.
• 07:22 Take that across, there's only two of them.
• 07:26 Now, I could actually use the product function,
• 07:29 which does exactly the same thing, a lot of people aren't aware of.
• 07:33 And do 8 to 12, and that gives me
• 07:39 exactly the same so isn't that cool and better.
• 07:43 Look, this highlights.
• 07:45 This is one, one, one.
• 07:48 This is one, nought, one.
• 07:49 There's an actual mistake in here and
• 07:52 it turns out that weekday here should be greater than two.
• 07:55 In here I've made the weekday greater than one.
• 07:59 Yes, obvious.
• 08:01 Come in here, change that to a two and copy it across.
• 08:07 Now it works.
• 08:07 Do you see?
• 08:11 Less chance of making a mistake.
• 08:13 More chance of making a mistake.
• 08:16 Easier to follow on a piece of paper.
• 08:19 This is why transparency is so important.
• 08:24 This whole idea of writing very, very short formulas,
• 08:32 Is known as the rule of thumb.
• 08:35 In that formulas should be no longer in general than your thumb.
• 08:42 I think that's a nice principle to adhere to if you can because it makes you spit
• 08:46 it out and when it's set out other people can follow your logic on a piece of paper,
• 08:51 then help you if there is an issue with your horrible formula.

Lesson notes are only available for subscribers.