Locked lesson.
About this lesson
Learn to perform what-if analysis in Excel using the Scenario Manager and Goal-Seek.
Exercise files
Download this lesson’s related exercise files.
What If Analysis - Begin.xlsx22.5 KB What If Analysis - Complete.xlsx
22.6 KB
Quick reference
What-If Analysis
Performing what-if analysis in Excel using the Scenario Manager and Goal-Seek.
When to use
These tools work very well when you either want to try seeing how things would look with different variables in place, or when you need to work out what variables will drive the outcome you’re looking for.
Instructions
Scenario Manager
- NOTE: It is highly recommended to create a Base Case scenario that you can roll back to!
- Open the Scenario Manager by going to Data --> What-If Analysis --> Scenario Manager
- Click Add, enter Base Case for the Scenario Name
- Pick the cell you want to change in the Changing Cells box and click OK
- Click OK to set the original value
- Click Add and enter a descriptive name for the Scenario Name
- Pick the cell you want to change in the Changing Cells box and click OK
- Click OK to set the new value, then click OK
- Select the new scenario and click Show (your data will change)
- Click Base Case and Show, and your original data will come back
Goal Seek
- Select a cell that contains a formula that is based on a value from another cell
- Select go to Data --> What-If Analysis --> Goal Seek
- Choose to Set the cell with the formula to a new value by changing the precedent cell
- Click OK and watch as Excel runs the numbers to work out the required value for the precedent cell in order to have the formula return the value you requested
- 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.