Microsoft Office Tutorials and References
In Depth Information
Making the Case with Scenario Manager
✓ 0.02 in the Sales_Growth text box
✓ 0.25 in the COGS text box
✓ 0.35 in the Expenses text box
Because this is the last scenario that I want to add, I then click the OK button
instead of Add. Doing this opens the Scenario Manager dialog box again,
this time displaying the names of all three scenarios — Most Likely Case,
Best Case, and Worst Case — in its Scenarios list box. To have Excel plug
the changing values assigned to any of these three scenarios into the Sales
Forecast 2014 table, I click the scenario name in this list box followed by the
After adding the various scenarios for a table in your spreadsheet, don’t forget
to save the workbook after closing the Scenario Manager dialog box. That way,
you’ll have access to the various scenarios each time you open the workbook
in Excel simply by opening the Scenario Manager, selecting the scenario name,
and clicking the Show button.
Producing a summary report
After adding your scenarios to a table in a spreadsheet, you can have Excel
produce a summary report like the one shown in Figure 8-8. This report
displays the changing and resulting values for not only all the scenarios you’ve
defined, but also the current values that are entered into the changing cells
in the worksheet table at the time you generate the report.
To produce a summary report, open the Scenario Manager dialog box
(Data ➪ What-If Analysis ➪ Scenario Manager or Alt+AWS) and then click the
Summary button to open the Scenario Summary dialog box.
This dialog box gives you a choice between creating a (static) Scenario
Summary (the default) and a (dynamic) Scenario PivotTable Report (see
Chapter 9). You can also modify the range of cells in the table that is included
in the Result Cells section of the summary report by adjusting the cell range in
the Result Cells text box before you click OK to generate the report.
After you click OK, Excel creates the summary report for the changing values
in all the scenarios (and the current worksheet) along with the calculated
values in the Result Cells on a new worksheet (named Scenario Summary).
You can then rename and reposition the Scenario Summary worksheet before
you save it as part of the workbook file.