Amal Nagm

A scenario is a set of values that Microsoft Office Excel saves and can substitute automatically on your worksheet. You can create and save different groups of values as scenarios on a worksheet and then switch between these scenarios to view the different results.

If several people have specific information that you want to use in scenarios, you can collect the information in separate workbooks, and then merge the scenarios from the different workbooks into one.

After you have all the scenarios you need, you can create a scenario summary report that incorporates information from all the scenarios

Scenarios are part of a suite of commands called what-if analysis tools. When you use scenarios, you are doing what-if analysis.

What-if analysis is the process of changing the values in cells to see how those changes will affect the outcome of formulas on the worksheet. You can use scenarios to create and save different sets of values and switch between them. You can also create a scenario summary report, which combines all the scenarios on one worksheet. For example, you can create several different budget scenarios that compare various possible income levels and expenses, and then create a report that lets you compare the scenarios side-by-side.

Kinds of what-if analysis There are three kinds of what-if analysis tools in Excel: scenarios, data tables, and goal seek. Scenarios and data tables take sets of input values and project forward to determine possible results. Goal seek differs from scenarios and data tables in that it takes a result and projects backwards to determine possible input values that produce that result.

Like data tables, scenarios help you explore a set of possible outcomes. Unlike data tables, scenarios from several different worksheets or workbooks can be merged. Scenarios make it easy to gather data about possible outcomes from a variety of sources, and then combine the data.

Each scenario can accommodate up to 32 variable values. If you want to analyze more than 32 values, and the values represent only one or two variables, you can use data tables. Although it is limited to only one or two variables (one for the row input cell and one for the column input cell), a data table can include as many different variable values as you want. A scenario can have a maximum of 32 different values, but you can create as many scenarios as you want.

For information about other what-if analysis tools, see the See Also section.

Scenario basics

Creating scenarios 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.

Note This section explains how to use scenarios, and shows sample data and the results of applying scenarios that use that data. It does not provide step-by-step instructions. For step-by-step instructions, see the section Create a scenario, later in this article.

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:

Changing cells have values that you type in.

The result cell contains a formula that is based on the changing cells (in this illustration, =B1-B2).

You then use the Scenario Manager dialog box to save these values as a scenario, name the scenario Worst Case, and specify that cells B1 and B2 are values that change between scenarios.

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

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 B1 (150,000) and cell B2 (26,000). Because Gross Profit (cell B3) is a formula— the difference between Revenue (B1) and Costs (B2)— you do not change cell B3 for the Best Case scenario.

After you save a scenario, it becomes available on 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:

Changing cells

Result cell

Merging scenarios There may be times when you have all the information in one worksheet or workbook that is required to create all the scenarios that you want to consider. However, you may want to gather scenario information from other sources. For example, suppose you are trying to create a budget for a larger company. You might collect scenarios from different departments, such as Payroll, Production, Marketing, and Legal, because each of these sources has different information to use in creating scenarios.

You can gather these scenarios into one worksheet by using the Merge command. Each source can supply as many or as few changing cell values as you want. For example, you might want each department to supply expenditure projections, but only need revenue projections from a few.

When you collect different scenarios from various sources, you should use the same cell structure in each of the workbooks. For example, Revenue might always go in cell B2 and Expenditures might always go in cell B3. If you use different structures for the scenarios from various sources, it can be difficult to merge the results.

Tip Consider first creating a scenario yourself, and then sending your colleagues a copy of the workbook that contains that scenario. This makes it easier to be sure that all the scenarios are structured the same way.

Scenario summary reports To compare several scenarios, you can create a report that summarizes them on the same page. The report can list the scenarios side by side or present them in a PivotTable report (PivotTable report: An interactive, crosstabulated Excel report that summarizes and analyzes data, such as database records, from various sources, including ones that are external to Excel.). A scenario summary report based on the preceding two example scenarios would look something like the following:

Note By default, the summary report uses cell references to identify the changing cells and result cells. For this example, names were created for those cells to make the summary report easier to read. If you create names for the cells before you run the summary report, the report will contain the names instead of cell references.

A note appears at the end of the summary report explaining that the Current Values column represents the values of changing cells at the time the Scenario Summary Report was created, and that the cells that changed for each scenario are highlighted in gray.

For more information about creating a scenario summary report

Create a scenario

Before you create a scenario, you should have an initial set of values already on the worksheet. To make scenario summary reports easier to read, you should also consider naming the cells that you plan to use in scenarios.

1. On the Data tab, in the Data Tools group, click What-If Analysis, and then click Scenario Manager.
2. Click Add.
3. In the Scenario name box, type a name for the scenario.
4. In the Changing cells box, enter the references for the cells that you want to specify in your scenario. For example, if you want to see how changing the values of cells B1 and B2 will affect the outcome of a formula based on those cells, enter B1,B2.

   Note To preserve the initial values for the changing cells, add a scenario that uses those values before you create additional scenarios that use different values.

5. Under Protection, select the options that you want.

   Note These options apply only to protected worksheets. For more information about protected worksheets, see the See Also section.

   • Select Prevent Changes to prevent editing of the scenario when the worksheet is protected.
   • Select Hidden to prevent display of the scenario when the worksheet is protected.
6. Click OK.
7. In the Scenario Values dialog box, type the values that you want to use in the changing cells for this scenario.
8. To create the scenario, click OK.
9. If you want to create additional scenarios, repeat steps 2 through 8. After you finish creating scenarios, click OK, and then click Close in the Scenario Manager dialog box.

Display a scenario

When you display a scenario, you switch to the set of values that are saved as part of that scenario. The scenario values are displayed in the cells that change from scenario to scenario, in addition to the results cells. For example, using the preceding scenarios, if you display the Best Case scenario, cell B1 displays 150000, cell B2 displays 26000, and cell B3 displays 124000.

1. On the Data tab, in the Data Tools group, click What-If Analysis, and then click Scenario Manager.
2. Click the name of the scenario that you want to display.
3. Click Show.

   Note After you close the Scenario Manager dialog box, the values from the last scenario that you displayed remain on the worksheet. If you saved your initial values as a scenario, you can display those values before you close the Scenario Manager dialog box.

Merge scenarios

1. Select the worksheet in which to store the merged scenarios results.
2. On the Data tab, in the Data Tools group, click What-If Analysis, and then click Scenario Manager.
3. Click Merge.
4. In the Merge Scenarios dialog box, click the arrow next to Book and select a workbook that contains scenarios that you want to merge in your results.
5. In the Sheet box, click the name of the worksheet that contains scenarios that you want to merge.
6. Click OK to merge the scenarios from the selected worksheet into the current worksheet.

   The Merge Scenarios dialog box closes, and the scenarios that you merged now appear in the Scenario Manager dialog box.

7. Repeat the preceding four steps as needed until you have merged all the scenarios that you want.

   When you are finished, the scenarios that you merged are all part of the current worksheet. You can close the Scenario Manager dialog box, or leave it open to continue your analysis.

Create a scenario summary report

1. On the Data tab, in the Data Tools group, click What-If Analysis, and then click Scenario Manager.
2. Click Summary.
3. Click Scenario summary or Scenario PivotTable report.
4. In the Result cells box, enter the references for the cells that refer to cells whose values are changed by the scenarios. Separate multiple references with commas.

Notes

• Scenario reports do not automatically recalculate. If you change the values of a scenario, those changes will not show up in an existing summary report, but will show up if you create a new summary report.
• You don't need result cells to generate a scenario summary report, but you do need them for a scenario PivotTable report.