How to Produce a Summary Scenario Report in Excel 2016

By Greg Harvey

After adding your scenarios to a table in a spreadsheet, you can have Excel 2016 produce a summary report like the one shown. 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.

Scenario Summary report showing the various scenarios added to the Sales Forecast for 2017 table.

Scenario Summary report showing the various scenarios added to the Sales Forecast for 2017 table.

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. 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. For this example, the cell range B7:F7 is chosen with the named cells, Q1_Income, Q2_Income, Q3_Income, Q4_Income, and Total-Income as the Result Cells for this summary 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.