Excel 2010's Scenario Manager enables you to create and save sets of different input values that produce different calculated results as named scenarios (such as Best Case, Worst Case, and Most Likely Case). The key to creating the various scenarios for a table is to identify the various cells in the data whose values can vary in each scenario. You then select these cells (known as changing cells) in the worksheet before you open the Scenario Manager dialog box.
The steps below follow a specific example for using Scenario Manager to help you understand how to use this feature. The figure shows a Sales Forecast table after selecting the three changing cells in the worksheet — H3 named Sales_Growth, H4 named COGS (Cost of Goods Sold), and H6 named Expenses — and then opening the Scenario Manager dialog box.
The example uses three scenarios based on the following sets of values for the three changing cells:
Most Likely, where the Sales_Growth percentage is 5%, COGS is 20%, and Expenses is 25%
Best Case, where the Sales_Growth percentage is 8%, COGS is 18%, and Expenses is 20%
Worst Case, where the Sales_Growth percentage is 2%, COGS is 25%, and Expenses is 35%
Use the Scenario Manager to add and switch to different scenarios in your worksheet.
Follow these steps to use the Scenario Manager:
On the Data tab of the Ribbon, choose What-If Analysis→Scenario Manager in the Data Tools group.
The Scenario Manager dialog box appears.
To create a scenario, click the Add button.
The Add Scenario dialog box appears.
Create a scenario in the Add Scenario dialog box.Type the name of the scenario (Most Likely, in this example) in the Scenario Name text box, specify the Changing Cells (if they weren't previously selected), and click OK.
Excel displays the Scenario Values dialog box.
Enter the values for each of the changing cells in the text boxes.
In this example, you would enter the following values for the Most Likely Case scenario:
0.05 in the Sales_Growth text box
0.20 in the COGS text box
0.25 in the Expenses text box
Click the Add button.
Excel redisplays the Add Scenario dialog box.
Repeat Steps 3 through 5 to enter the other scenarios. When you finish entering values for the final scenario, click OK instead of Add.
The Scenario Manager dialog box makes another appearance, this time displaying the names of all scenarios in its Scenarios list box.
To have Excel plug the changing values from any scenario into the table, click the scenario name in the Scenarios list box and then click Show.
Click the Close button when you're finished with the Scenario Manager.
After adding the various scenarios for a table in your worksheet, don't forget to save the workbook. That way, you'll have access to the various scenarios each time you open the workbook in Excel by opening the Scenario Manager, selecting the scenario name, and clicking the Show button.