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.
Instant Connection to an Excel Expert
Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.
Leave a Comment