Creating Scenarios

You already know that Excel provides the ability to play "what if" with your worksheets. As an adjunct to this capability, Excel provides the Scenario Manager, which allows you to save different data scenarios. Using this tool, you can save different variables for your sheet and call them up quickly and easily.

As an example, let's suppose that you work for Talbot Industries, and you have been charged with developing a profitability analysis for a new product—the Potato Chip Peeler. You develop your sheet, taking into account all the appropriate information. However, you know that it is prudent at your company to actually prepare three forecasts. The first would be the worst-case scenario, the second is the most-likely scenario, and the final is the best-case scenario.

To save a scenario, follow these steps:

1. Make sure the worksheet reflects one of your scenarios.
2. Select the cells that will be changing in the scenario. For instance, you might select the cells at B4:B11 and F5:F11, if these are the cells that will change from one scenario to another.
3. Display the Data tab of the ribbon.
4. Click the What-If Analysis tool (in the Data Tools group if you are using Excel 2007, Excel 2010, or Excel 2013) or click the What-If Analysis tool (in the Forecast group if you are using Excel 2016 or a later version) and then click Scenario Manager. Excel displays the Scenario Manager dialog box. (See Figure 1.)



Figure 1. The Scenario Manager dialog box.

5. Click on the Add button. Excel displays the Add Scenario dialog box. The cells you selected in step 2 should already be indicated in the Changing Cells field. (See Figure 2.)



Figure 2. The Add Scenario dialog box.

6. Enter a name for the scenario, such as Most Likely or Worst Case.
7. Click on the OK button. Excel shows you a dialog box containing a list of the values in the scenario. Each cell is listed along with its value. If the cell has a name assigned to it, the name is used. If not, the cell address is used.
8. If you like, you can change the values stored in the scenario.
9. When you are satisfied, click on the OK button. The scenario is then saved under the name you specified, and your original worksheet is unchanged.

Once you have a scenario saved, you can make changes to your workbook and save your changed figures under a different scenario.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (8567) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, 2021, 2024, and Excel in Microsoft 365. You can find a version of this tip for the older menu interface of Excel here: Creating Scenarios.