What-If Analysis

Quick reference

• 00:04 If you've ever had a manager that you've been working with who comes back and
• 00:08 asks you for different views or different alternatives to the same thing,
• 00:12 you'll love this piece on what if analysis.
• 00:14 Because this is all about taking a scenario that we have in place and
• 00:19 saying, well, what would happen if we changed the number to this?
• 00:22 Or how would we actually come up with this particular value,
• 00:25 given the inputs that we have?
• 00:27 So let's take a look at how this works.
• 00:30 In order to get to the what if analysis, we're gonna go to the Data tab and
• 00:33 there's actually two pieces that we wanna work with.
• 00:36 Before you start playing with what if analysis and goal seeking and
• 00:40 things like that, I highly suggest that you set up a Scenario Manager.
• 00:44 So we go into Scenario Manager and it says that there's no scenarios defined,
• 00:47 choose one.
• 00:48 Or add one so we'll say Add, and it says what's the scenario name?
• 00:52 We're gonna say this is Base Case.
• 00:56 And then it says which cells would you like to change?
• 00:58 What I'm gonna do is I'm gonna select these three cells.
• 01:03 And we'll say OK.
• 01:05 And it says, what are the the values for these three cells?
• 01:08 So this is gonna give me the way to go back and
• 01:11 reset these cells to these values at any point in time.
• 01:14 So we'll say, okay those are the values that are in there.
• 01:17 No problem, this is Base Case.
• 01:19 Now I can go back and my manager comes along and
• 01:21 says all right well, what I'd like to know is when I'm looking at this.
• 01:26 We actually have a formula down here which is FV, which is the future value formula.
• 01:30 So it's taking $1,500, running it over ten years at a 6.5% interest rate, and
• 01:35 says, if you contribute for $1,500 for
• 01:38 ten years at this interest rate, you'll have $20,241 at the end of it all.
• 01:42 So our manager wants to know,
• 01:44 well what would happen if I increased my contribution to $2,500?
• 01:47 So now we can go back to what if analysis and we can say,
• 01:51 let's use the Scenario Manager, and say, let's add a new scenario,
• 01:56 and we'll call this one $2,500 contribution.
• 02:00 And it says which cells would you like to change?
• 02:03 We'll say you know what, I only need to change this one here.
• 02:06 We'll say OK.
• 02:09 And there we go, we'll set that to 2,500, and say OK.
• 02:13 And now we can click Show.
• 02:16 And it'll recalculate to show what we have.
• 02:17 But the nice piece here is that then if I want to go back to the Base Case that I
• 02:22 had before, I can just select Base Case and it will revert this.
• 02:26 This is awesome, because now we say, well, what if the interest rate went to 7.5% and
• 02:31 we decided to do it for 12 years, and we want it to go to 1,750?
• 02:34 So we set this out and here's what our scenario ends up looking like.
• 02:39 And the manager decides he's gonna leave at this point in time.
• 02:43 We can say okay, no problem,
• 02:44 we can set it back by going to What If, Scenario Manager.
• 02:48 Show the Base Case and roll it back.
• 02:51 So that's a really, really helpful tool.
• 02:53 And I've used Scenario Managers, I've had six,
• 02:54 seven, eight different scenarios in here for different sensitivity analysis.
• 02:59 Makes it very easy to flip through and recalculate those things when the manager
• 03:02 wants to look at something and we've changed other inputs.
• 03:04 So this is very, very useful.
• 03:07 In addition you may end up getting the question saying, well, you know what?
• 03:11 What I'd really like to do is I'd like to know I need to have $25,000 in ten years,
• 03:16 I believe the interest rate's gonna be about the same here, so it's gonna be
• 03:21 ten years so what contribution do I need to make in order to come up with that?
• 03:25 And this is where we go into goal seeking.
• 03:28 In order to use goal seeking what we need is we need a formula here
• 03:32 that actually has inputs that are driven by other cells.
• 03:35 Okay that's the big key that we need here, because what we're gonna do is we're gonna
• 03:39 go to What If Analysis, we're gonna choose Goal Seek.
• 03:42 What Goal Seek says is you would like to set your cell with your formulas in it,
• 03:48 so cell B7, to a specific value.
• 03:51 And that value's gonna be $25,000 in this case.
• 03:53 And it says, which cell would you like to change to make that happen?
• 03:57 So we'll click on this box and we'll say you know what,
• 03:59 let's change the contribution.
• 04:01 And what will happen now is Excel, when I click OK,
• 04:04 will run through different variables to come through and says hey,
• 04:08 if you're gonna get a 6.5 interest rate, you're gonna donate for ten years.
• 04:11 You need to come up with $1,852.62, so this is a heck of a lot easier
• 04:17 than actually sitting here saying well I want to get to 23,000 so let me try 17.50.
• 04:22 No, it's going to be less than that.
• 04:24 It's more than this, so 17.15, we don't want to play that game so
• 04:29 if we want to get to 23,000, what we'll do is we'll go and
• 04:33 say Goal Seek, let's go and set this cell here,
• 04:38 to 23,000 by changing our contribution, and away we go.
• 04:45 So a heck of a lot easier than trying to hunt and
• 04:47 figure out exactly where to get it.
• 04:49 Cuz it would take a long time to dial into that exact number,
• 04:51 trying to get closer and closer every time so.
• 04:53 So very, very useful tools for trying different things.
• 04:57 And remember, when I'm all done I can go back to Scenario Manager,
• 05:00 show my Base Case, and revert everything back to the way it was.

Lesson notes are only available for subscribers.