Performing what-if analysis in Excel using the Scenario Manager and Goal-Seek.
Where/when to use the technique
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.
- 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(s) 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
- Select cell B7 and go to Data --> What-If Analysis --> Goal Seek
- Choose to Set cell B7 to a value of 27,500, by changing B3
- Click OK and watch as Excel runs the numbers to find out what the value of our variable in B3 needs to be in order to have B7 result in a value of $27,500
- Roll back to the base case using the Scenario manager
- Try goal seeking to work out the interest rate you’d need to achieve in order to earn $27,500 given the 1500 contribution over 10 years.
Lesson notes are only available for subscribers.