Sensitivity Check

Quick reference

• 00:04 The second of the three types of check is a sensitivity check.
• 00:08 Just as a reminder,
• 00:10 checks are things you can put into Excel that give a value of zero or
• 00:15 one, one if something's wrong, zero if everything's working fine.
• 00:21 A sensitivity check is to alert you that, you're not in the base case.
• 00:27 Now, an example here might be as follows.
• 00:30 Years ago, I worked on a juvenile's part of a big four accounting firm,
• 00:35 where are very large well-known company was considering how to
• 00:39 get more money back into its parent country, which is Australia,
• 00:44 which is where I live these days.
• 00:46 They have got their own modeling team.
• 00:48 They've got their own tax team.
• 00:50 And they had actually put together a model where they thought they could save a lot
• 00:55 of tax to the tune of somewhere around $50 million, let's say.
• 00:59 I'm changing the figures to protect the innocent.
• 01:01 You don't even know what the company is.
• 01:03 But the fact is, this really did happen.
• 01:06 This went to the board.
• 01:07 The board thought, this sounds great, this looks like something we should do.
• 01:12 But let's get it checked by one of these accounting firms to verify whether or
• 01:16 not there's any mistakes in the model.
• 01:18 You see, checking is important.
• 01:19 You're gonna make a critical business decision on it.
• 01:23 Now, what happened was, I headed up a modeling team.
• 01:27 And we were checking the model to see if there was any formulaic errors in there.
• 01:31 And a colleague of mine was looking at the tax advice,
• 01:33 which seemed to be reasonably sound.
• 01:34 In fact, because the model had been built by people who modelled all the time for
• 01:37 this company, their error rate was still around the 5% that everyone has,
• 01:43 but they were pretty good with it.
• 01:45 We found a couple of minor errors, nothing too major, but
• 01:48 we came across a table that looked a bit like the one in the illustration here.
• 01:53 The scenario illustration.
• 01:55 How it works is that, the cells in yellow in column J,
• 01:58 in this particular graphic, were the ones that we used in the model.
• 02:03 So we're using a base year unit price of $3.70, a unit price growth rate of 2%,
• 02:08 base year volume of 80,000, etc., etc., etc.
• 02:13 And you can see this was coming from column L, scenario 1,
• 02:17 where it was highlighted in green using conditional formatting, that was fine.
• 02:24 But what we found was, scenario 1 was the base case.
• 02:28 But the one that they based all their savings on was one of the other scenarios,
• 02:31 be it scenario 2,3,4, or 5.
• 02:34 And, when we changed it back to scenario 1, the base case,
• 02:38 the savings went from somewhere around $50 million savings to about $5 million loss.
• 02:44 Now, they can't say exactly what the numbers were.
• 02:46 The point is,
• 02:47 it shows that not being in the base case had a dramatic effect on the decision.
• 02:52 And therefore you want a sensitivity check going on in your model that say,
• 02:56 warning, warning, you are not in the base case.
• 02:59 You are not in the base case because otherwise,
• 03:02 people might made the wrong decisions, and that's what all this is about.
• 03:05 There's no Excel file this time, it's more,
• 03:08 just an idea that if you are going to put alternative numbers in there.
• 03:12 Like you have table like this or something else, and
• 03:15 we will talk about that perhaps another time.
• 03:18 But the fact is, if you do, do that and
• 03:20 you're not at the base case numbers, you may not be having the right result, and
• 03:24 therefore, it's worth alerting people to the sensitivity check.
• 03:29 Talking of alerts, next one.up is an alert check and we'll cover that next time.

Lesson notes are only available for subscribers.