Scenario Manager in Excel
How Use to Scenario Manager in Excel
If you’ve ever used a worksheet to answer the question “What if?”, you’ve already performed what-if analysis. For example, what would happen if your advertising budget increased by 40 percent? How about 50 percent?.
A scenario is a set of input values that you can substitute in a worksheet to perform what-if analysis. For example, you could create scenarios to show various interest rates, loan amounts, and terms for a mortgage. Excel’s scenario manager lets you create and store different scenarios in the same worksheet.
Use Scenario Manager
- Select the cells that contain values that could change.
- Click the Data tab on the ribbon.
- Click the What-If Analysis button.
- Select Scenario Manager.
The Scenario Manager dialog box appears with the message “No Scenarios defined. Choose Add to add scenarios.
- Click the Add button to add a new scenario.
- Type a name for the new scenario.
If you already have cells selected, the Changing cells field will already be populated with your selection. If you didn’t select cells up front, you’ll have to specify the cells here.
- Click OK.
The Scenario Values dialog box appears, showing each of the variable cells you selected.
If you name the worksheet cells you're changing, the cell names appear here, making it easy to tell what value you're working with.
- Update any values you want to see for the given scenario.
To make sure you don’t lose the original values for the changing cells, use the original cell values in the first scenario you create.
- Click OK.
The scenario is added and is listed in the Scenario Manager. If you click Add, the Add Scenario dialog box appears again so you can add another scenario.
Repeat steps 5-9 to add all the desired scenarios.
- Select the scenario you want to view.
- Click the Show button.
The worksheet’s values are changed to the values you specified in the scenario. You can select any scenario here to update the values in your spreadsheet.
View a Scenario Summary
A scenario summary report is a single compiled report that summarizes the results from several scenarios. It’s easier to read than switching between different scenarios. Once you’ve created at least two scenarios, you can create a summary report.
- Click the Data tab.
- Click the What-If Analysis button.
- Select Scenario Manager.
The Scenario Manager dialog box appears, displaying all the scenarios you’ve created.
- Click Summary.
The Scenario Summary dialog box appears. Ensure the Scenario summary option is selected.
- Click OK.
A summary report for each scenario is generated on a separate sheet so you can compare each one side by side.
FREE Quick Reference
Click to DownloadFree to distribute with our compliments; we hope you will consider our paid training.
- LinkedIn-ready certificate
- Skill assessments
- Bite-sized, interactive lessons
- Saved progress