Tip #93 - Excel Scenario Manager

100 Computer Tips in 100 Days

Tip #93 - Excel Scenario Manager

We're opening a sweet treats shop and want to
be able to create a variety of success scenarios.
The values in D4 through D8 are our variables.

A helpful tool in Excel that allows you to store a variety of scenario models is the Scenario Manager. You create and save different groups of values and then switch to any of these stored scenarios to view different results.

Once your base values are entered into a worksheet
you then create an alternate scenario by naming it...

...and then defining the values that should be entered
for that scenario.

All of the scenarios you have defined will be available
in the Scenario Manager. Clicking on a scenario in the
list and clicking the Show button changes the information
in the worksheet.

For example, suppose are opening a sweet treats store and you wanted to find out your profit using different sales figures. You can use the Scenario Manager to create multiple scenarios on the same worksheet, and then switch between them. For each scenario, you specify the cells that change and the values to use for that scenario. When you switch between scenarios, the contents of the cells change to reflect the different values. It is helpful if you name the cells that you are going to be changing.  To create scenarios: 

• Open a worksheet and put in all of the information  for one scenario (see the sample worksheet). 
• From the Data tab choose What If Analysis and Scenario Manager.
• Name the scenario and click OK
• Enter the variables in the cells for that scenario and click OK.
• After you have created all the scenarios that you need, you can create a scenario summary report that incorporates information from those scenarios. A scenario report displays all the scenario information in one table on a new worksheet.

Clicking on the Scenario Summary Report option in the Scenario
Manager results in a report showing all of the scenarios on
a new worksheet.

It is a good idea to name the cells that are to be modified or the report will list the cell addresses and not the names as they are in this finished report. For example to the right of 1: it might say $B$12 rather than donuts. Review Tip #67 if you've forgotten how to name cells.

This tip probably benefits from having a video to view. I will be taping these after all of my tips have been posted. I’ll let you know when the video is available.

Happy Computing!

View Diane's Webpage for more tips.