Apr 11, 2016

I created three Scenarios using the Scenario Manager feature in my previous blog. I used the following spreadsheet data:

The Scenarios I created are:

1. Original Projections
2. Advertising Push
3. Reduced Expenses

These scenarios will show management two options for increasing the company’s Profit.

While you can display each scenario’s result separately by selecting the scenario name and clicking the Show button, you most likely will not take the laptop or PC with you to show these scenarios to the management.

You will want to create a professional Scenario Summary report and print it out. To create a Scenario Summary Report, perform the following steps:

1. Click the Data tab on the Ribbon
2. Click the What-If Analysis button
3. Click the Scenario Manager… button. The following dialog box will display:
4. Click the Summary… button
5. Click OK

You will see the following report:

This report looks OK, but we do not know what the Changing Cells references represent. If you print out this report and presented it to your manager, probably he or she will say that this is rubbish. So let’s make this report better.

We will go back to the spreadsheet we used to create these scenarios. We will name the ranges first. To name the ranges in this spreadsheet:

1. Select the ranges you want to name, in our example select all numbers and their related labels (see below):

2. Click the Formulas tab on the Ribbon
3. Go to Defined Names group
4. Click the Create from selection button. You will see this dialog box.


Excel will use the rows labels to create the names.

5. Click OK. Click the arrow next to the Name Box

1. Click the Data tab on the Ribbon
2. Click the What-If Analysis button
3. Click the Scenario Manager… button. The following dialog box will display:

4. Click the Summary… button
5. Click OK

You will see the following report:

Now the report looks great!

The left hand side of the report show a plus sign (+). Click the plus sign (+). The comments you added to scenarios will display so everyone looking at this report will know what values you did change.

Watch out for other great Microsoft Excel blogs soon.