Using the Scenario Manager

Excel's Scenario Manager feature enables you to analyze your data to see how changing one or more values in the worksheet affects the other cells in the worksheet. This feature comes in handy for figuring out what would happen if certain factors in your business changed.

After you create a simple scenario in this hour , you'll learn about hiding and protecting scenarios to prevent others from making changes to them. Then you will view different scenarios on the worksheet. Finally, you will create a scenario summary to view all scenarios from your worksheets in one report.

Creating Scenarios

In many cases, you use worksheets to perform what-if analysis. After you set up a series of calculations, you can change the values of certain cells to view different scenarios. For example, "What if I sold 15% more products this year? What if I reduce inventory? How would these changes affect my total income?" Being able to anticipate the effect of changes is what makes a spreadsheet so valuable .

The Tools, Scenarios option enables you to substitute one or more values with a range of values and observe how the new values affect the rest of the data in the worksheet. You can ask Excel, "What if the value changes?" and the Scenario Manager instantly shows the substitutions and their effects directly on the worksheet. For instance, perhaps you want to see what happens to your projected income if sales rise or drop, or if you increase or decrease inventory. You can use the Scenario Manager to enter all the possibilities.

Follow the instructions in the exercise to see how the Scenario Manager works. You start by creating a new workbook and entering data to prepare for creating a simple scenario. Your goal is to create the best scenario for projected sales and projected inventory.

To Do: Create a Scenario

1. Create a new workbook and name it Data Analysis.

2. In Sheet1, type the data shown in the worksheet in Figure 17.1. Cell C6 contains the Profit formula, =C3-C4 .

   Figure 17.1. Setting up a worksheet with values and variables .

   

3. Click cells C3:C4. These cells contain the data you want to change. You want to create the best scenario for projected sales and projected inventory.

4. Select Tools, Scenarios. The Scenario Manager dialog box opens, as shown in Figure 17.2.

   Figure 17.2. The Scenario Manager dialog box.

   

5. To add a new scenario, click the Add button. Excel displays the Add Scenario dialog box (see Figure 17.3), which is where you define the scenario.

   Figure 17.3. The Add Scenario dialog box.

   

6. In the Scenario Name box, type Best . Notice that cells C3:C4 appear in the Changing Cells box. These are the cells on the worksheet that this scenario will change.

7. Click OK. Excel opens the Scenario Values dialog box, which is where you enter the input values for the scenario. Each input value is assigned a number. In this case, you have two changing cells, and you should see the numbers 1 and 2 at the left edge of the dialog box.

8. In box 1, you should see 200000 . In box 2, you should see 40000 . These values represent the projected figures for sales and inventory.

9. Click OK to return to the Scenario Manager dialog box.

10. Click the Show button to change the values in the worksheet. Excel displays the input values in the worksheet and recalculates the formulas to reflect the changed values. In this example, note the input values remained the same when you clicked the Show button. There were no changes to the values on the worksheet because the input values, 200,000 and 400,000, were the best scenario numbers you selected for the scenario.

11. Add another scenario for cells C3:C4 and name it Worst. For scenario values, enter 1000 in box 1 and 500 in box 2. Show the values on the worksheet.

12. Click the Close button to close the Scenario Manager dialog box. Figure 17.4 shows the result of the Scenario Manager changing the worksheet.

    Figure 17.4. Result of the Scenario Manager changing the worksheet.

    

You can enter all kinds of possibilities by creating other scenarios with different input values to see different results. To do so, repeat the steps for creating a scenario using the new input values as the changing cells. The Scenario Manager begins its calculations using the new cell values. For instance, you can decrease sales and increase inventory to create the worst scenario, or you can increase both sales and inventory to create a better scenario. Changing these values can result in other scenarios.

Hiding and Protecting Scenarios

You can hide a scenario to prevent others from seeing sensitive or confidential information in the scenario. A hidden scenario does not appear in the Scenarios list in the Scenario Manager dialog box.

You can also protect your scenario from changes. By default, the Prevent Changes option is on, thereby preventing anyone from making changes to the scenario. However, you can turn it off to allow changes.

When you add or edit a scenario, you can hide or protect it. To do so, choose Tools, Scenarios. In the Scenario Manager dialog box, click the Add button or Edit button. The Add Scenario or Edit Scenario dialog box appears. Figure 17.5 shows the Edit Scenario dialog box.

Figure 17.5. The Edit Scenario dialog box.

At the bottom of either dialog box, in the Protection section, click the Hide check box to hide the scenario. If you want to protect the scenarios, leave the check mark in the Prevent Changes check box. If you want to unprotect the scenario and allow changes, click the check box to remove the check mark in the Prevent Changes box. Then click OK to confirm your choices. Click Close in the Scenario Manager dialog box to return to the worksheet.

Viewing a Scenario

Suppose you want to play out different scenarios you had created in order to make some business decisions. You can view each scenario you added and then analyze the sets of data in the scenarios. To do so, choose Tools, Scenarios. Select a scenario in the Scenarios list and click Show. Repeat the step to view different scenarios.

Creating a Scenario Summary Report

If a summary of the scenarios is really what you're interested in, and you'd like to view all your results on one sheet, you can get there directly with the Scenario Summary feature.

To create a scenario summary, choose Tools, Scenarios. In the Scenario Manager dialog box, click the Summary button. The Scenario Summary dialog box opens, as shown in Figure 17.6.

Figure 17.6. The Scenario Summary dialog box.

Select the Scenario Summary option and click OK. Excel displays the scenario summary on a new sheet before the Sheet 1 tab. The new sheet is called Scenario Summary, as shown in Figure 17.7.

Figure 17.7. The Scenario Summary report on a new sheet.

You should see a tree structure to the left of the row heading numbers in the Scenario Summary sheet. There are minus signs and plus signs for collapsing and expanding report sections. Click on a minus sign () to hide the section in the report. Click on a plus sign (+) to expand the section in the report.

You can print the report as you would any worksheet by using the File, Print command.

If you want to delete the report, click the Scenario Summary sheet tab, and choose Edit, Delete Sheet. Click Delete to confirm the deletion.