What-If Analysis

Quick reference

• 00:04 Let's take a look at What-If or sensitivity analysis in Excel.
• 00:09 Now, I'm gonna show you the formula that I'm working with here and
• 00:12 it's not really important that you understand this formula, but just so
• 00:15 you're aware of what's going on.
• 00:17 This is a future value function, and the concept here is that it's telling me how
• 00:22 much money I should expect to earn if I'm investing $1,500 a year for
• 00:26 10 years at a 6.5% interest rate.
• 00:28 In theory I should have $20,000 by the time we're done.
• 00:31 But you know what's gonna happen with this when I put something like this out of my
• 00:34 organization.
• 00:35 My boss is gonna come running into the office and
• 00:37 he's gonna wanna play with this.
• 00:38 Well, what if we have $2,500 in contributions or
• 00:40 what if the years go to 12, or 8, or what not?
• 00:43 So what I'd like to do is I'd like to set up a couple of tools to play around
• 00:47 to actually answer those questions.
• 00:50 And the first thing that I'm gonna do is, I want to record my base assumptions so
• 00:54 that I can go back and very easily switch back to them after we play around.
• 00:58 To do that, we're gonna use a tool called the scenario manager which you can
• 01:02 find on the Data tab underneath What-If Analysis.
• 01:06 And I always recommend before you start actually tweaking these things, it's
• 01:09 a good idea to set up a base case scenario so that you can easily fall back to it.
• 01:13 So we'll say Add and we create a new scenario called Base Case.
• 01:19 Notice it says, would you like to change cells B5 to B7?
• 01:22 This is where my original assumptions are going to be, and
• 01:25 I get the option to put in a comment.
• 01:28 I'm gonna say OK, and it will then come back and say for
• 01:31 each of the cells in that range what are the constants that you want in place.
• 01:35 Those are good.
• 01:36 It's read them directly from the cells.
• 01:37 We'll say OK.
• 01:39 Now at this point, I'm gonna click Close.
• 01:42 And now we're gonna play around.
• 01:44 The boss comes in, he says, what if we go with $2,500?
• 01:47 We say, well at that point you're gonna have 33,000.
• 01:49 He says,
• 01:50 great, I'm gonna wanna know whether we should actually mark that one down.
• 01:54 Well, no problem.
• 01:55 I can go back to my What-If Analysis,
• 01:58 go to the Scenario Manager, and I can add a new scenario.
• 02:02 This one here we can call it $2,500 contribution.
• 02:07 Again, changing cells B5 to B7, when I say OK,
• 02:12 it says these are the new assumptions, and we can say OK.
• 02:16 We now have two different scenarios in here.
• 02:20 Now the cool thing is even though we've got our $2,500 contribution scenario
• 02:25 showing now, I can now quickly go and return to the base case so
• 02:28 that I can save that before I put it out.
• 02:31 And this is really useful because now if the manager comes back and says,
• 02:34 hey I wanna see what happens if we're 12 years.
• 02:37 And we're gonna go with 7.5%.
• 02:40 And we're gonna drop the payment down to a $1,000.
• 02:44 Yeah, okay never mind.
• 02:45 I don't think I can get 7.5%, so never mind.
• 02:47 We'll throw that one away, or we can very easily go back to the scenario manager and
• 02:52 say Show, switch it back, and we're good to go.
• 02:55 And I've work with scenarios where I've had six,
• 02:58 seven different scenarios pre-programmed with different things that I
• 03:01 know are gonna be asked in the board meeting.
• 03:03 The nice thing here is we could quickly flip between them, but
• 03:06 we also have the ability to go and mark, and update, and change the cells,
• 03:09 and even record what those look like for playing around in future.
• 03:12 So it's a very very useful tool.
• 03:14 But then there's one more challenge that comes up.
• 03:17 Somebody comes back and says, look, I want $35,000 for my future value.
• 03:21 I know I'm gonna get 6.5% interest rate and
• 03:23 I know it's gonna be ten years because I'm buying buying a bond from the bank.
• 03:26 So the only question is how much money do I need to put in on an annual basis.
• 03:30 So what we don't want to be doing is this.
• 03:35 Well 2,500 gets me to 33, okay?
• 03:37 2,600 gets me to 30, well that's a little bit too high, so
• 03:41 2,575, this is a kind of crazy game to try and play around with.
• 03:46 So it would be much better if we could come back and say,
• 03:49 I'd really like to figure out how to get this cell to $35,000.
• 03:54 Wouldn't it be nice if all you had to do is go to your what if analysis and
• 04:00 go to goal seek and say I'd like to set cell B9 to be 35,000.
• 04:05 And the cell that I'd like to change in order to do it is this one here, and
• 04:11 at that point, when we click OK, it goes back and it says, all right, no problem.
• 04:17 We found a solution.
• 04:18 You need to put in exactly $2,593.66.
• 04:21 And if I suddenly go and change this to say, you know what,
• 04:26 that goal seek actually needs to be 45,000 by changing the contribution.
• 04:32 Then at that point, it will go through and it'll find out what that needs to be.
• 04:36 And when I'm done, if we realized it's not achievable, I can go back to my
• 04:41 What-If Scenario Manager and I can show the base case scenario and say Close.
• 04:47 So this gives me a lot of tools to be able to create scenarios,
• 04:50 record scenarios, and also to very quickly go through and
• 04:54 figure out exactly what I need by playing around with goal seek.

Lesson notes are only available for subscribers.