Excel - Scenario Manager

Background Information

What-If Analysis is the label for a collection of tools to project and display multiple scenarios. A helpful collaborative tool contained in the What-If Analysis is the Scenario Manager.

Adding a Scenario

Name the Scenario:

  1. Highlight the range of cells that will be changed as part of your scenario.
  2. From the Data Tab, click the What-If Analysis button, then click Scenario Manager. This opens the Scenario Manager dialogue box.
  3. Press Add.
  4. Type the desired Scenario Name.
  5. Press OK. This opens the Scenario Values dialogue box.

Specify the Values:

  1. Type the appropriate scenario values.
  2. Press Add to create additional scenarios.
  3. Repeat steps 4-6.
  4. Press OK when all scenarios have been added.
  5. Press Close.

Viewing Existing Scenarios

  1. From the Data Tab, click the What-If Analysis button, then click Scenario Manage. This opens the Scenario Manager dialogue box.
  2. Double Click the Scenario to view (or select and press the Show button).

Summarizing Existing Scenarios

  1. From the Data Tab, click the What-If Analysis button, then click Scenario Manager. This opens the Scenario Manager dialogue box.
  2. Click the Summary button. This opens the  Scenario Summary dialogue box.
  3. Choose Scenario summary for a side-by-side comparison of the scenarios.
  4. Press OK.
  5. The Summary table will be created in a new worksheet labelled Scenario Summary.

Merging Existing Scenarios From Other Workbooks

  1. Open any workbook containing a scenario to be merged.
  2. From the Data Tab of the main workbook, click the What-If Analysis button, then click Scenario Manager. This opens the Scenario Manager dialogue box.
  3. Click Merge. This opens the  Merge Scenarios dialogue box.
  4. Choose the Book and Sheet(s) to be merged.
  5. Press OK.

Notes

  1. If a scenario is marked as Prevent Changes, it cannot be changed or deleted if the workbook is protected.
  2. If a scenario is marked as Hidden, it cannot be viewed if the workbook is protected.
  3. Scenarios work best with a small list of values.
  4. Name cells before creating scenarios to help in managing them:
  • Click the cell to be named.
  • Type the desired name in the Name Box (on the left-side of the formula bar - naming restrictions apply).
  1. If you use Scenarios regularly, you may want to add the Scenario Manager to your Quick Access Toolbar:
  • From the Data Tab, click the What-If Analysis button, then right-click Scenario Manager.
  • Click Add to Quick Access Toolbar.

 

To review images or save/download/print a copy of this Job Aide, please select the pdf attachment.

 

Print Article