How to create scenarios in Excel

Suppose that you want to create a budget but are uncertain of your revenue. By using scenarios, you can define different possible values for the revenue and then switch between scenarios to perform what-if analyses.

For example, assume that your worst case budget scenario is Gross Revenue of $50,000 and Costs of Goods Sold of $13,200, leaving $36,800 in Gross Profit. To define this set of values as a scenario, you first enter the values in a worksheet, as shown in the following illustration:

The Changing cells have values that you type in, while the Result cell contains a formula that is based on the Changing cells (in this illustration cell B4 has the formula =B2-B3).

You then use the Scenario Manager dialog to save these values as a scenario. Goto the Data tab > What-If Analysis > Scenario Manager > Add.

In the Scenario name dialog, name the scenario Worst Case, and specify that cells B2 and B3 are the values that change between scenarios. If you select the Changing cells on your worksheet before adding a Scenario, the Scenario Manager will automatically insert the cells for you, otherwise you can type them by hand, or use the cell selection dialog to the right of the Changing cells dialog box.

Note: Although this example contains only two changing cells (B2 and B3), a scenario can contain up to 32 cells.

Protection – You can also protect your scenarios, so in the Protection section check the options that you want, or uncheck them if you don't want any protection.

• Select Prevent Changes to prevent editing the scenario when the worksheet is protected.

• Select Hidden to prevent displaying the scenario when the worksheet is protected.

Note: These options apply only to protected worksheets. For more information about protected worksheets, see Protect a worksheet

Now suppose that your Best Case budget scenario is Gross Revenue of $150,000 and Costs of Goods Sold of $26,000, leaving $124,000 in Gross Profit. To define this set of values as a scenario, you create another scenario, name it Best Case, and supply different values for cell B2 (150,000) and cell B3 (26,000). Because Gross Profit (cell B4) is a formula - the difference between Revenue (B2) and Costs (B3) -  you do not change cell B4 for the Best Case scenario.

After you save a scenario, it becomes available in the list of scenarios that you can use in your what-if analyses. Given the values in the preceding illustration, if you chose to display the Best Case scenario, the values in the worksheet would change to resemble the following illustration:

Use Excel Scenarios to store several versions of the data in a worksheet. For example, create 2 different budgets - best case and worst case. Download the sample file and watch the video or follow the written instructions

Author: Debra Dalgleish

Although Excel scenarios can be complex, a simple example is used here. Delete all sheets except Sheet1 Rename Sheet1 as Budget On the Budget sheet, enter the Marketing budget, as shown below Name the following cells (there are Naming instructions here: Name a Range). Naming the cells is not required, but will make it easier to manage the scenarios, and read the reports: Name cell B1 as Dept Name cell B3 as Sales Name cell B4 as Expenses Name cell B6 as Profit In cell B6, enter the following formula:       =Sales - Expenses

Create First Excel Scenario

1. On the Ribbon's Data tab, click What If Analysis
2. Click Scenario Manager
   

3. In Excel's Scenario Manager, click the Add button
4. Type name for the Scenario. For this example, use Marketing.
5. Press the Tab key, to move to the Changing cells box
6. On the worksheet, select cells B1
7. Hold the Ctrl key, and select cells B3:B4
   Note: There is a limit of 32 changing cells
8. Press the Tab key, to move to the Comment box
9. (optional) Enter a comment that describes the scenario.
10. Click the OK button
    

11. The Scenario Values dialog box opens, with a box for each changing cell.
12. You could modify these values, but in this example they contain the values currently on the worksheet, and don't need to be changed.
13. Click the OK button, to return to the Scenario Manager.
14. Click the Close button, to return to the worksheet  
    

Create Second Excel Scenario

1. To prepare for the Finance scenario, change the values in cells B1, B3 and B4, as shown below

2. On the Ribbon's Data tab, click What If Analysis, then click Scenario Manager.
3. In the Scenario Manager, click the Add button
4. Type name for the second Scenario. For this example, use Finance.
5. The Changing cells box should show the previous selection -- B1,B3:B4 -- so leave that as is.
6. Press the Tab key, to move to the Comment box
7. (optional) Enter a comment that describes the scenario.
8. Click the OK button
9. The Scenario Values dialog box opens, with a box for each changing cell.
10. Click the OK button, to return to the Scenario Manager.
11. Click the Close button, to return to the worksheet  

Show an Excel Scenario

Once you have created Scenarios, you can show them. In this example, the Finance scenario is currently visible. To change to a different scenario:

1. On the Ribbon's Data tab, click What If Analysis, then click Scenario Manager.
2. The Scenario Manager dialog box opens, showing a list of scenarios in the workbook
3. In the list of Scenarios, select Marketing
4. Click the Show button
5. Click the Close button.
   

Add Scenario to Excel Ribbon

An easier way to switch between Scenarios, is to add a command to the Ribbon. Follow these steps, to add a Custom Group, and put the Scenario command in that group.

1. Right-click on the Ribbon, and click Customize the Ribbon
2. From the drop down list at the top left, select All Commands
3. In the list of commands that are currently on the Ribbon, click the plus sign for Data, then click Data Tools. That group contains the Scenario Manager, so we'll add the new group beside it.
   

4. Below the list, click the New Group button.
5. Then, click the Rename button, type Scenario as the name for the group, and click OK

6. At the left, in the list of All Commands, scroll down to find Scenarios
7. Make sure that the new Scenario group is still selected in the list at the right.
8. Click on Scenarios, then click Add, to put Scenarios in the Scenario group.
   

9. Click OK, to close the window, then click the Data tab, and select a Scenario to view.
   

Spending Scenarios - Low, Mid, High

For holiday spending this year, what is your best case scenario? Do you have a big budget to spend for the holidays? Or, in your worst case scenario, will you spend as little as possible? Or will your holiday budget be somewhere in the middle?

You can use Excel Scenarios to store several versions of a budget, and compare the results.

In the steps below, see how to set up a worksheet where you can compare three scenarios for holiday spending.

Set Up the Worksheet

The first step is to set up the worksheet. Some of the cells will be the same in each Excel scenario, and other cells will change.

Note: There’s a limit of 32 changing cells in an Excel Scenario.

• On a worksheet named Budget, add headings, spending categories, and amounts, as shown in the screen shot below.
• Add a Total label, and a sum of the spending amounts.

Create the First Scenario

The first scenario is for Extravagant spending, which will contain the highest amounts.

• On the Ribbon, click the Data tab.
• Click What-If Analysis, then click Scenario Manager.

• In the Scenario Manager, click Add
• Type name for the Scenario. For this example, use High.
• Clear the Changing cells box
• With the cursor in the Changing cells box, click cell B4 on the worksheet. That’s one of the cells that will change in each scenario.
• Hold the Ctrl key, and select cells C6:C11. Do not include any of the category labels, or the total cells.
• (optional) Enter a comment that describes the scenario.
• Click OK to close the Edit Scenario box.

Add the Scenario Values

The Scenario Values dialog box opens, with a box for each changing cell. It automatically displays the current value in each changing cell. You could modify one or more of the existing values, or leave them as is.

We’ll make the Gifts – Family amount a bit higher, and leave the other values untouched.

• For item 5, change the value from 500 to 600.
• Click OK to return to the Scenario Manager. Notice that the value on the worksheet didn’t change – it still shows 500 as the amount for Gifts – Family.

Create Another Scenario

You can add more scenarios by changing the worksheet values, and following the steps that you used to build the first scenario.

Or, you can add an Excel scenario directly into the Scenario Manager.

1. In the Scenario Manager, click Add
2. Type name for the next scenario. For the second scenario, use Mid.
3. Leave the existing cells in Changing cells box
4. (optional) Enter a comment that describes the second scenario.
5. Click OK to close the Add Scenario box.
6. In the Scenario Values dialog box, enter the worksheet heading and values for the second scenario.
7. Click OK to return to the Scenario Manager.
8. Create the third scenario – Low – and enter the lowest amounts for that scenario.
9. Click Close, to return to the worksheet.

Show a Scenario

Once you have created the Excel Scenarios, you can show them. On the worksheet, the original values for Extravagant scenario are showing. To change to a different scenario:

1. On the Ribbon, click the Data tab.
2. Click What-If Analysis, then click Scenario Manager. (In earlier versions, click Tools>Scenarios)
3. In the list of Scenarios, click on a Scenario name
4. Click Show, then click Close.

Show Excel Scenario Summary

After you create the Excel Scenarios, you can view them in an Excel Scenario Summary report. These scenario reports let you see the values and totals side-by-side, for an overall comparison.

Note: The Excel Scenario Summary does NOT update automatically if you change the scenario values. You can delete the old summary and create a new one.

To create a Scenario Summary:

• On the Ribbon, click the Data tab.
• Click What-If Analysis, then click Scenario Manager. (In earlier versions, click Tools>Scenarios)
• In the Scenario Manager, click the Summary button
• In the Scenario Summary dialog box, for Report type, select Scenario Summary
• Click in the Result cells box, and on the worksheet, click the Total calculation cell (C12).
• Click OK, to close the dialog box.

A Scenario Summary sheet is added to the workbook.

• To show or hide the details, click the + / – buttons at the left side and top of the worksheet

Improve the Scenario Summary

In the Scenario Summary shown above, the changing cells are shown as addresses. If you name the value cells, the Scenario Summary will show those names, instead of the cell addresses

You could change the colour scheme too, unless you love grey and purple!

Get the Sample File

Get the zipped sample Excel Scenario file, with the Marketing and Finance example. The zipped file is in xlsx format, and does not contain any macros

More Scenario Tutorials

Scenario Summaries

Automatically Show Scenarios

Scenarios -- Programming

Scenarios Excel 2003