Locked lesson.
About this lesson
Learn to perform what-if analysis in Excel using the Scenario Manager and Goal-Seek.
Lesson versions
Multiple versions of this lesson are available, choose the appropriate version for you:
Exercise files
Download the ‘before’ and ‘after’ Excel workbooks from the video tutorial and try the lesson yourself.
What-If Analysis.xlsx12 KB What-If Analysis - Completed.xlsx
12.1 KB What-If Analysis - Extra Practice.xlsx
13.6 KB
Quick reference
Topic
What-if analysis.
Description
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 cell F11 and go to Data > What-If Analysis > Goal Seek
- Choose to Set cell F11 to a value of 850, by changing E11
- Click OK and watch as Excel runs the numbers to find out what the value of our variable in E11 needs to be in order to have F11 result in a value of $850
- 00:04 What-if Analysis in Excel is built around the concept of where we've created a model of some kind
- 00:11 and then we're looking at trying to run different scenarios through it to see how things actually react to changes in that model.
- 00:18 So if we look at the dataset that we have here we've got some Discharges,
- 00:22 we've got Charges that were covered. The Total Medical Payments and we've got an average payment.
- 00:27 The average payment is set up by dividing these two cells together. So one of the scenarios we may want to look at is where we were to change
- 00:35 either the Total Medical Payments or the Total Discharges and see what effects it might have on
- 00:40 the actual Average Payment. So we have a couple of tools we can use to actually do this. The first one
- 00:46 is the Scenario Manager. So that is under What- if Analysis on the Data tab
- 00:52 And we go into the Scenario Manager.
- 00:55 Now right now we have no scenarios defined. I would always suggest before you actually go and start using these things in earnest that you
- 01:03 actually create a Base Case scenario that allows you to get back to where you were. So we're going to start with Base Case
- 01:10 and it's going to say what cells would you like to change? I'm going to change C4 Total
- 01:16 Discharges for this particular procedure. It tells me that I've created this and say OK.
- 01:23 And it'll ask me what value I want. I'm going to set it to just the value inside the cell right now say OK.
- 01:28 So this will always give me my Base Case that I can return to.
- 01:32 Now I'm going to go and say you know what? Let's try another one here. Let's try and change this
- 01:37 For an increase of say 50. So we'll call this one Increase
- 01:44 DRG039
- 01:50 to 79 Discharges. That's what we'll call this particular scenario
- 01:55 that we're looking at. Something that gives me a real good clue later when I'm looking back at it.
- 02:00 I am certainly going to change this particular cell
- 02:04 and say OK
- 02:07 and then what value do I want 79
- 02:09 and say OK.
- 02:11 And nothing happens at this point until I click Show and what happens when I click Show is this
- 02:16 value will update as will the formula that it's based off of.
- 02:21 This is the reason why I say that you need your Base Case because if I close this right now
- 02:26 it's going to stay like that. So I can always roll back right now to my Base Case and say Show and it'll return to what I had beforehand.
- 02:34 So that's using the Scenario Manager. You can create all kinds of diferent scenarios for
- 02:39 one by one line changes and whatnot but remember to always have that Base Case there.
- 02:43 Now another tool that we have is Goal Seek and Goal Seek is really neat
- 02:47 kind of stuff because you can actually run scenarios through formulas to see what actually ends up happening.
- 02:52 Let's say for example that maybe for some reason somebody is looking at these particular billings and says you know what
- 02:59 this number is way too high. We want to try and set a target that makes this particular operation a lot more affordable to people because its such
- 03:05 an important one we want to to get that down to 850 that's our target.
- 03:10 So how do we do that? Well, we know that because it's based off of these two cells we can
- 03:15 start toggling either one of these things to try and come up with what it is. So
- 03:20 some users might say ok well if I change this up to 20 what happens to it or
- 03:26 if I go to 25 where does it get to. So that's one way of doing it. I'm just going to back those off.
- 03:31 The other way, the faster way
- 03:33 is to go into our What-if Analysis and go to Goal Seek
- 03:38 and what Goal Seek says is I'm going to set the cell you choose to a specific value.
- 03:44 So we're going to set this cell
- 03:47 F11 we're going to change it to the value of 850.
- 03:51 What cell would we like to change in order to make that happen? This is part of the formula that refers to E11 and C11. Which
- 03:59 one would we like to change? Well let's see if we can change the medical payments
- 04:04 to get the average payment down what would we actually need to do to those payments?
- 04:09 So this is what our completed Goal Seek scenario looks like. Set cell F11 to value 850
- 04:14 by changing cell E11 and we say OK.
- 04:19 And as you can see it spins through, it makes some modifications and it basically does all the
- 04:23 math of increasing or decreasing to try and get it to the right area so
- 04:27 that's how to use Goal Seeking in Excel; a really useful and actually quite under used tool for sure.
Lesson notes are only available for subscribers.