Scenarios in Excel

By | 31st March 2015

Excel has a variety of data analysis tools built-in, some of them very sophisticated. One of the easiest to use is a scenario that gives a snapshot of what things would be like if certain conditions were to change. These are particularly useful in financial models or in situations where the effects of the predictions need to be seen in a clear way.

A scenario is a set of values that influence the outcome of a worksheet model. They can be used to forecast the effects of just about anything, from moving premises to the profit results if certain assumptions about costs and revenues are taken into account.

Scenarios can be named and saved with different sets of figures to represent specific values. When several scenarios have been saved they can be viewed to compare the different results each would bring. For example, if you create a budget but are not sure of the amount of revenue your business will generate a scenario can be defined for each predicted revenue figure and then switched between to calculate “what-if” analysis.

You can create a Scenario Report from the sets of figures if you want a record of the changes each would make. This report can also be turned into a Pivot Table report which makes it ideal for further analysis, especially if the data sets become large.

Using the Scenario Manager

From the Data tab navigate to “What-if Analysis” and click Scenario Manager. Select all the changing cells that you want to include in your scenario.  These should be value cells not formula cells.

scenario in Excel 2013

The Scenario Manager dialogue box will be displayed. Click on the Add button to add another version of the figures. The Add Scenario dialogue box will be displayed

data analysis

Type in the scenario name. If necessary, modify the cell addresses in the Changing cells box and set any other required options before clicking the OK button:

version one

The Scenario Values dialogue box will be displayed:

changing values

If necessary change the values you want for the changing cells.

To create the scenario, click on the OK button. If you want to add another set of figures, click the Add button and repeat the previous 4 steps to create additional scenarios.

displaying scenarios

When you finish creating scenarios, click on the OK button, and then click on the Close in the Scenario Manager dialogue box.

To switch between Scenarios using the Scenario Manager

From the Data tab navigate to “What-if Analysis” and click Scenario Manager. The Scenario Manager dialogue box will be displayed. Click on the name of the scenario you want to switch to, and then click on the Show button.

the high option

The appropriate values appear in the spreadsheet. When you have finished viewing the different scenarios, click Close.

To switch between Scenarios using the Scenario box

A much more convenient way of displaying different versions of events is to add a tool to the Quick Access Toolbar.  All your saved scenarios are listed there and you can switch from one to another with the minimum of fuss. It also makes it very easy to compare different outcomes when discussing budgets or presenting to a group.

From the drop-down menu at the right of the Quick Access Toolbar, click Customize Quick Access Toolbar.

Under Choose Commands From: pick Commands not on the Ribbon.

Find Scenario in the list and click Add.

Scenarios
Scenarios
Scenarios

Once it has been added you can display a list of scenarios and pick the one you want to see.

Click on the drop-down arrow to the right of the Scenario button.

Click on the name of the scenario you want to switch to:

scenarios_10

 To edit a Scenario

You may wish to modify either the changing cells themselves or the values that they contain, or update the comment box for a given scenario.

Click on the Data tab – What-if AnalysisScenario Manager command to display the Scenario Manager Dialogue box. Click on the name of the scenario you want to edit, and then click on the Edit button. Make your required changes and click OK.

Type the values you want for the changing cells if necessary, then click on the OK button, to return to the dialog box.

To delete a Scenario

Open the Scenario Manager dialog box then click on the name of the scenario that you want to delete. Click on the Delete button to erase it.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.