Scenario Manager for Business Planning

Data Tables show you the full spectrum of possible outcomes across a range of values. Scenario Manager does something different — it lets you define, name, save, and switch between specific combinations of assumptions that represent distinct business narratives. Best Case, Worst Case, and Expected Case are not just different numbers. They are different stories about how the future might unfold, and Scenario Manager lets you tell all three from a single model.

What Scenario Manager Does

Scenario Manager (Data → What-If Analysis → Scenario Manager) stores multiple sets of input values under named labels. Each scenario saves the values of up to 32 assumption cells simultaneously. Switching between scenarios instantly updates every formula in the model that depends on those cells — the entire forecast, every KPI, every chart, all at once.

Unlike a Data Table, which is passive and read-only, Scenario Manager actively changes the assumption cells when you switch scenarios. This makes it the right tool for live presentations — you can flip between Best Case and Worst Case in front of a room and let the charts update in real time.

Defining Your Three Scenarios

Before opening Scenario Manager, decide what each scenario means in business terms. Vague labels like "High" and "Low" are less useful than narratives grounded in specific business conditions.

• Worst Case narrative — a market contraction scenario: revenue declines slightly, margins compress due to competitive pricing pressure, discounting increases to defend volume, and returns rise due to lower product quality investment;
• Expected Case narrative — continuation of recent trends: growth holds at the 2022–2024 CAGR, margins stay consistent with 2024 actuals, and operational metrics remain stable;
• Best Case narrative — an upside scenario: a new product launch or market expansion drives above-trend growth, operational improvements lift margins, and a premium positioning strategy reduces discounting and returns.

Creating Scenarios in Excel

1. Go to Data → What-If Analysis → Scenario Manager;
2. Click Add;
3. Scenario name: "Worst Case". Changing cells: Growth_Rate, Gross_Margin, Discount_Rate, Return_Rate, Marketing_ROI_Multiplier (hold Ctrl to select non-adjacent named cells);
4. Click OK;
5. Enter the Worst Case values from the table above → click OK;
6. Repeat steps 2–5 for Expected Case and Best Case.

To switch between scenarios: select any scenario name → click Show. The assumption cells update instantly and every dependent formula recalculates.

To return to your working assumptions after previewing scenarios, either click Show on Expected Case or close the dialog and press Ctrl+Z.

Generating a Scenario Summary Report

Scenario Manager can produce a static comparison table showing all scenarios side by side — the closest thing Excel has to a built-in scenario dashboard without any additional formula work.

1. Open Scenario Manager → click Summary;
2. Choose Scenario Summary (not PivotTable — simpler and more presentation-ready);
3. In Result cells, select the key outputs you want to compare:
   • SUM of Assumption_Forecast (Total Revenue 2025);
   • SUM of Projected_Profit (Total Profit 2025);
   • SUM of Net_Revenue (Net Revenue 2025);
   • The Gross_Margin named cell;
4. Click OK.

Excel generates a new sheet called Scenario Summary showing each scenario's input values and output results in a clean side-by-side table. Format it with your color scheme and it is presentation-ready immediately.

Building a Scenario Toggle with INDEX and MATCH

The Scenario Summary report is static — it captures a snapshot but doesn't update when data changes. A more powerful approach is a live scenario toggle: a dropdown cell that switches the entire forecast between scenarios dynamically without opening Scenario Manager at all.

Step 1 — Build a scenario assumptions table:

In your Assumptions sheet, create a reference table listing all three scenarios with their values:

Step 2 — Add a scenario selector dropdown:

In a prominent cell (e.g. B1), add a Data Validation dropdown:

• Data → Data Validation → List;
• Source: "Worst Case,Expected Case,Best Case";
• Name this cell: Active_Scenario.

Step 3 — Replace hardcoded assumption values with INDEX/MATCH lookups:

Growth_Rate cell: =INDEX(C5:E5, MATCH(Active_Scenario, C4:E4, 0))

Where C4:E4 contains the scenario names and C5:E5 contains the Growth_Rate values for each scenario.

Repeat for every assumption row. Now changing the dropdown from "Expected Case" to "Best Case" updates every assumption cell simultaneously — and therefore every forecast formula, every KPI, and every chart in the model — in a single click.

Protecting Scenario Assumptions

Once your scenarios are defined and the toggle is working, protect the scenario reference table so it can't be accidentally edited. Leave only the Active_Scenario dropdown cell unlocked.

1. Select all cells in the scenario reference table;
2. Ctrl+1 → Protection tab → check Locked;
3. Select the Active_Scenario dropdown cell → uncheck Locked;
4. Review → Protect Sheet → enter a password (optional). Check: Select unlocked cells, Use AutoFilter.

Now users can switch scenarios freely using the dropdown but cannot accidentally overwrite the scenario assumptions that underpin the model.

Task 1: Create the Three Scenarios

Goal: Define and save Best Case, Worst Case, and Expected Case scenarios in Scenario Manager.

1. Before opening Scenario Manager, write a two-sentence business narrative for each scenario in comment cells next to your assumption block — what market conditions would produce these numbers?
2. Open Data → What-If Analysis → Scenario Manager and create all three scenarios using the values from the table in this lesson.
3. Cycle through each scenario using the Show button and observe how the Assumption_Forecast column, Projected_Profit, and Net_Revenue update in your forecast table.
4. Generate a Scenario Summary report with Total Revenue, Total Profit, and Net Revenue as result cells. Rename the generated sheet Scenario_Summary and format it with your model's color scheme.
5. Add a row at the bottom of the summary manually calculating the variance between Best Case and Worst Case for each output — label it "Upside / Downside Range".

Task 2: Build the Live Scenario Toggle

Goal: Replace Scenario Manager with a dropdown-driven live toggle that updates the entire model in one click.

1. In your Assumptions sheet, build the scenario reference table with all three scenarios as columns and all five assumptions as rows.
2. In cell B1, add a Data Validation dropdown listing the three scenario names. Label it clearly: "Active Scenario". Name the cell Active_Scenario.
3. Replace the manually entered values in your assumption block with INDEX/MATCH formulas referencing the scenario table and the Active_Scenario cell.
4. Test the toggle: switch between all three scenarios and confirm that Growth_Rate, Gross_Margin, Discount_Rate, Return_Rate, and Marketing_ROI_Multiplier all update correctly each time.
5. Protect the scenario reference table cells while leaving Active_Scenario unlocked (Review → Protect Sheet).
6. Switch to Best Case and note the total 2025 forecast revenue. Switch to Worst Case and note it again. Calculate the difference — this is your model's full upside/downside range and the single most important number to communicate to stakeholders.