What-If Analysis

Quick reference

• 00:04 Let me hit you with a little scenario here.
• 00:05 In this cell I have a Fv function, which calculates the future value of a certain
• 00:10 contribution of cash flows for ten years given a defined interest rate of 6.5%.
• 00:15 That's all good so far, except that now I'm going to tell you that I want to earn
• 00:20 $25,000 in this ten year period.
• 00:22 And the best interest rate I can get is 6.5%.
• 00:25 So how much of a contribution do I have to make every single year?
• 00:28 What most people will do with this is they'll start playing around and saying,
• 00:32 well, let's try going up to 1750, well, that's 23,000, that won't work.
• 00:36 Let's try going to 2000.
• 00:39 That's too much.
• 00:39 Okay, how about 1800 and they start trying to dial this in a little bit,
• 00:43 chipping off the boundaries, to try and figure out where it actually belongs.
• 00:47 I'm going to roll this back to 1500 here.
• 00:49 And I want to show you that we have a tool to make this job a whole lot easier.
• 00:53 If we go to the Data tab, under what if analysis, we have Goal Seek.
• 00:59 And what Goal Seek does it allows Excel to do all of that hard work for you.
• 01:03 Now one thing I will say, I always select the cell I want to change and
• 01:07 it always puts it here.
• 01:08 And that's the wrong thing to have in the cell.
• 01:10 We want to set our outputs to a value of 25,000 by
• 01:15 changing the input cell that I'm working with.
• 01:19 So we want to make sure those references are correct.
• 01:22 And when we say OK it does all the work of iterating through to,
• 01:26 figure out that the contribution I need is $1,852.62, awesome.
• 01:31 Now, if I like that, I can just click OK and it'll put that value in the cell.
• 01:35 If I don't, I can click Cancel and it'll go back to the original, which is great.
• 01:39 So this allows me now to try this out and say, well, I only have 1500.
• 01:43 How many years do I need to contribute to make it work?
• 01:45 Let's go back to what if analysis and Goal Seek.
• 01:48 We'll say, let's set the cell here B9 to 25,000
• 01:53 by changing this time our number of years.
• 01:57 And it says, look, Ken, if you're going to give me $1,500 and 6.5% interest, it's
• 02:02 going to take you 11.65 years in order to be able to make that happen, okay?
• 02:06 So, this is a really,
• 02:07 really useful tool to be able to quickly figure out what a specific constant needs
• 02:12 to be changed to, in order to get our output that we're looking for.
• 02:16 Now, that's one tool.
• 02:18 But what if we actually want to set up some scenarios,
• 02:21 that we can very quickly change between.
• 02:23 Because we're going to be in a meeting and
• 02:25 we need to actually have some scenarios pre-prepared.
• 02:27 This is my base case, so I'm going to go in, I'm going to tell my boss, look,
• 02:31 I mean, if we're investing 1500 at 10 years at 6.5%, it's going to return this.
• 02:35 But I want to very quickly switch this out to something else.
• 02:38 For this we actually have a tool called Scenario Manager.
• 02:41 And I'm going to go and take a look at this one.
• 02:43 We're going to open up the Scenario Manager.
• 02:45 And one of the things that I highly recommend before you start using this,
• 02:49 is that you set up a base case to always return it to your original values.
• 02:53 So I'm going to choose Add.
• 02:55 The scenario name is going to be called Base Case.
• 02:59 There we go.
• 03:00 And I'm going to change these cells here.
• 03:01 And if you haven't selected multiple cells,
• 03:03 you can just click the little arrow, select multiple and click this again.
• 03:07 And this for a comment is going to be base case
• 03:12 with 1500 times 10 years at 6.5%.
• 03:17 I'm going to leave myself a little bit of stuff behind here,
• 03:20 to know what's going on, and we're going to say OK.
• 03:22 And it says let's go, and change your values to what you want.
• 03:25 All right, awesome.
• 03:26 I'm going to go and say OK, add would allow me to add another cell.
• 03:29 So we'll say OK, there we are, we have a base case.
• 03:32 Now what I'm going to to do is, I'm going to add a new scenario here.
• 03:35 And this one here is going to be $2,500 investment.
• 03:39 I'm still going to change the same cells.
• 03:42 And this is going to be a $2,500 investment by 10 years, at 6.5%.
• 03:51 Although actually, you know what?
• 03:52 Let's make this 9.5%, let's say this is really, really aggressive.
• 03:58 So we'll say OK to that.
• 04:01 And we'll change this to be a $2,500 at 0.095, for 9.5% interest.
• 04:08 And we can say OK, and now what we can do, is we can say, show me this case.
• 04:14 So if I show that, it changes all of the values in here.
• 04:17 And then if I go back to my base case, I can say show, and
• 04:20 it will actually flip me back to the base case.
• 04:23 And this is pretty cool, because everything is all set up here.
• 04:26 So I can now come back in at any time into my scenario manager and
• 04:30 see what am I actually going to be working with.
• 04:33 And there we go, I can say Close.
• 04:34 And if that's the scenario that my boss wants to go with, I can work with that.
• 04:38 If it's not, I'll just go back in to scenario manager.
• 04:42 Set it back to my base case and show it, and I'm right back to my original values

Lesson notes are only available for subscribers.