Excel allows us to store different sets of data on the same cells on a worksheet through the Scenario Manager.  Each set of data is considered one scenario.

Figure 1.  Final result:  Scenario Manager

How to create scenarios?

We enter a scenario one by one.  Suppose we have three possible plans for a weekly production run.  In order to create a scenario for each plan, we follow these steps:  

  • Enter the plan in D2 with the corresponding number of cases in D5:D9.  
    • In D2, enter “Plan 1”
    • In D5:D9, enter the values 1300, 2000, 700, 1300, 1000
    • D10 is the sum of the cases with the formula =SUM(D5:D9)

Figure 2.  Entering the values for Scenario 1

  • Click Data tab > What-If-Analysis > Scenario Manager  

Figure 3.  Scenario Manager in Data tab

The Scenario Manager dialog box will appear.

Figure 4.  Scenario Manager dialog box

  • Click Add and enter the following in the textboxes in Edit Scenario:
    • Scenario name: Plan 1
    • Changing cells: D2,D5:D9
    • Comment: Plan 1

Note:

In order to select multiple cells in Changing Cells, click the button then press the Ctrl key while selecting the cells D2, D5:D9.  

Figure 5.  Edit Scenario dialog box

  • Click OK.  The Scenario Values dialog box will appear, showing the values for the first scenario.  

Figure 6.  Scenario Values dialog box

  • Click OK.  The Scenario Manager will show that we have successfully created a scenario named Plan 1.  

Figure 7.  Output: How to create a scenario

  • For the second and third scenarios, we use the following values and follow the same procedure above to create the three scenarios.  

Figure 8.  Sample data for the three scenarios

Scenario Manager

Now that we have created three scenarios, we can select any of the scenarios through the Scenario Manager by following these steps:

  • Click Data tab > What-If-Analysis > Scenario Manager  
  • Choose from the list of available scenarios and click Show

Figure 9.  Select a scenario

Scenario Manager will instantly show the values for the selected plan in cells D2, D5:D9.  

Figure 10.  Output: Scenario Manager

We can now browse from one scenario to another through the Scenario Manager.

Scenario Summary

We can also create a scenario summary or scenario analysis through these steps:

  • Click Data tab > What-If-Analysis > Scenario Manager  > Summary

Figure 11.  Summary option in Scenario Manager

  • In the Scenario Summary dialog box, tick Scenario summary and in Result cells, select cell D10, which contains the value for the total number of cases.  

Figure 12.  Scenario Summary

  • The scenario summary will be created in another sheet named “Scenario Summary”

Figure 13.  Output: Scenario Summary

The scenario summary shows the values for the current selected scenario, Plan 2.  It also shows the values for all the scenarios, where the changing cells are filled with gray fill color.  

The result cells are displayed at the last row, showing the total number of cases for each of the scenarios.