Using Scenarios in Microsoft Excel
Part two of a two-part series of blogs

Scenarios are incredibly useful when performing "what-if analysis". They allow you to quickly swap a range of input values to test different possibilities without having to retype any values. Read on to find out how they work!

  1. Using Scenarios in Microsoft Excel
  2. Viewing Scenarios in Microsoft Excel (this blog)

Posted by Andrew Gould on 27 February 2012

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Viewing Scenarios in Microsoft Excel

Once you have created scenarios in a worksheet you'll want to be able to view them.  This part of the series explains how to switch between different scenarios and how to modify a toolbar to create a quick way to do this.

Viewing Scenarios Using the Scenario Manager

The standard way to view a scenario is to use the Scenario Manager dialog box.  To do this:

  1. From the ribbon select: Data -> What-If Analysis -> Scenario Manager...
Scenario manager

Use this dialog box to view different scenarios. The numbered steps are described below.

  1. Select the scenario you wish to view.
  2. Click the Show button to view the selected scenario.
Changed scenario

When you click the Show button the input cells will change to the values saved in the selected scenario.

Adding the Scenario Tool to the Toolbar

The only downside to the above method of viewing scenarios is that you must have the Scenario Manager dialog box open in order to do it.  A more convenient solution is to add the Scenario tool to one of the toolbars.  To do this:

  1. Click the drop arrow at the end of the Quick Access Toolbar, as shown below:
Customising toolbar

The image shown here is taken from Excel 2010, but you can do the same thing in Excel 2007.

 
  1. Click the More Commands... option to customise the toolbar.
Customising toolbar

Follow the numbered steps as described below.

  1. Select All Commands from the drop down list.
  2. Find and select the Scenario option.
  3. Click the Add>> button to transfer the tool to the list on the right.
  4. Make sure that the Scenario tool appears in the list on the right and then click OK.

Once you've added the Scenario tool to the toolbar you can use it to switch between different scenarios.

Choosing scenarios from toolbar

Click the Scenario tool to see a list of available scenarios and then click on the one you want to see.

 

Viewing a Scenario Summary

Rather than viewing one scenario at a time, you might want to see a side-by-side comparison of the scenarios in a sheet.  To do this:

  1. Select the worksheet containing your scenarios.
  2. From the ribbon select: Data -> What-If Analysis -> Scenario Manager...
Scenario manager

You should be looking at the now-familiar Scenario Manager dialog box.

 
  1. On the dialog box, click the Summary... button.
Setting up summary

Use this dialog box to set up the summary report. The numbered steps are described below.

  1. Choose which type of report you would like to create.  Here we're choosing the basic type of summary report.
  2. Choose the cells containing calculations whose results will change based on the values altered by the scenarios.  Here we know that the Loan and Monthly payment will change as we apply different scenarios so we select these as our result cells.
  3. Click OK to view the summary report.
Scenario summary

A scenario summary report.  The advantage of using range names is again quite obvious!

The summary report will appear on a separate worksheet and show a side-by-side comparison of the scenarios.  The values in the summary are not linked to any other values, so if you change anything on the original worksheet you'll need to run the summary report again.

You can download the finished version of the file used in this article by clicking here.

  1. Using Scenarios in Microsoft Excel
  2. Viewing Scenarios in Microsoft Excel (this blog)
This blog has 0 threads Add post