Scenario Manager in What-If Analysis

Scenario Manager is a powerful tool in Excel’s What-If Analysis feature that allows you to explore different scenarios by changing input values. This is especially helpful for making decisions based on various possible outcomes.

Why Use Scenario Manager?

• To evaluate how changes in key variables affect results.
• To compare different business strategies or forecasts.
• To simplify the process of testing multiple scenarios without altering the original data.

Key Terms

• Scenario: A set of input values that represent a specific situation or outcome.
• Changing Cells: The cells where input values are adjusted for different scenarios.
• Result Cells: The cells that show the calculated outcomes based on changing cells.

How to Use Scenario Manager

1. Prepare Your Data
   Create a table with formulas that depend on input values.
   Example: Input Value Sales Price 100 Units Sold 500 Fixed Costs 2000 Variable Costs 20 Formula for Profit: = (Sales Price * Units Sold) - (Fixed Costs + (Variable Costs * Units Sold))
2. Open Scenario Manager
   • Go to the Data tab on the Ribbon.
   • In the Forecast group, click What-If Analysis and select Scenario Manager.
3. Add a Scenario
   • Click Add in the Scenario Manager dialog box.
   • Name your scenario (e.g., “Best Case” or “Worst Case”).
   • Specify the changing cells (e.g., cells containing Sales Price, Units Sold, etc.).
   • Enter the values for this scenario and click OK.
4. Add More Scenarios
   Repeat the process to add other scenarios with different input values. Examples:
   • Best Case: Higher sales price and units sold.
   • Worst Case: Lower sales price and fewer units sold.
5. View Scenarios
   • Select a scenario in the list and click Show. The changing cells will update with the values for that scenario.
   • Observe how the results in your formula cells change.
6. Generate a Summary Report
   • In the Scenario Manager dialog, click Summary.
   • Choose the result cells (e.g., the cell showing profit) for the summary.
   • Excel will create a new worksheet with a comparison of all scenarios.

Example

For the above table:

• Changing Cells: Sales Price and Units Sold.
• Scenarios:
  • Best Case: Sales Price = 120, Units Sold = 600.
  • Worst Case: Sales Price = 80, Units Sold = 400.

The Scenario Summary might look like this:

Scenario	Sales Price	Units Sold	Profit
Base Case	100	500	15,000
Best Case	120	600	26,000
Worst Case	80	400	6,000

Tips for Using Scenario Manager

• Use descriptive names for scenarios to make comparisons easier.
• Keep a backup of your data to avoid accidental changes.
• Combine Scenario Manager with other tools like Data Tables for more in-depth analysis.

Scenario Manager makes it easy to compare outcomes and make informed decisions based on different sets of assumptions. It is a great way to test and present possible business strategies effectively.