How to Do Scenario Analysis in Excel

To do scenario analysis in Excel, store each scenario's assumptions in its own row, add a single switch cell (1, 2, 3) for base, bull, and bear, then drive your live inputs with CHOOSE or INDEX so the whole model flips when you change one number. This is cleaner and more auditable than Excel's built-in Scenario Manager for financial models.

Two ways to run scenarios in Excel

Excel ships with Scenario Manager (Data tab, What-If Analysis, Scenario Manager), which stores named sets of input values and swaps them in on demand. It works, but it hides assumptions inside a dialog box where they are hard to audit, easy to forget, and impossible to see side by side.

The professional approach is a switch-driven scenario block. You lay every scenario's assumptions out in plain cells, add one switch cell, and use a lookup function to feed the active scenario into your model. Reviewers can see all cases at once, and the live inputs visibly change when you flip the switch.

This guide focuses on the switch method because it is the standard in investment banking and corporate FP&A. We cover Scenario Manager briefly at the end for completeness.

Build a switch-driven scenario block

Imagine three drivers (revenue growth, gross margin, capex as a percent of sales) and three cases. Lay the scenarios out in a grid, then build one row of live inputs that the model actually reads.

1. Create a label cell for the switch, for example B1, and type 1 (1 = Base, 2 = Bull, 3 = Bear). Add a data-validation dropdown so only 1, 2, or 3 are allowed.
2. Build a scenario table. Put driver names down column A (rows 4, 5, 6) and case values across columns: Base in C, Bull in D, Bear in E. For revenue growth that might be 10%, 15%, 4%.
3. Create a live input column, say B4:B6, that your model reads from. These cells must contain the switch formula, never typed numbers.
4. In B4 enter =CHOOSE($B$1, C4, D4, E4). CHOOSE returns the Base value when the switch is 1, Bull when 2, Bear when 3.
5. Copy B4 down to B5 and B6. The dollar signs on $B$1 keep the switch fixed while the case columns shift per row.
6. Point every downstream formula in your model at B4:B6, the live inputs, not at the scenario table.
7. Test it: change B1 to 2 and confirm every live input and your outputs update to the Bull case.

Use INDEX when you have many scenarios

CHOOSE is perfect for three or four cases, but it gets unwieldy past five because you list every column by hand. For a wide scenario set, INDEX scales better and is easier to extend.

With scenarios in C4:G4 (five cases) and the switch in B1, write =INDEX(C4:G4, 1, $B$1). INDEX returns the value in position $B$1 along the row, so adding a sixth scenario just means widening the range to C4:H4.

• CHOOSE: best for 2 to 4 scenarios, very readable, breaks if a case is deleted.
• INDEX: best for 5+ scenarios, returns the Nth column, extends by widening one range.
• Always label the switch clearly (1 = Base, 2 = Bull, 3 = Bear) near the cell so reviewers know what each number means.

When to use Scenario Manager instead

Scenario Manager makes sense for quick one-off what-ifs on a few cells where you do not want to build a switch block, or when you want Excel to generate a summary report comparing scenarios.

To use it: select the input cells, go to Data, What-If Analysis, Scenario Manager, Add, name the scenario, and enter the values for those cells. Repeat for each case. Click Summary to produce a comparison table. The drawback remains that the assumptions live inside the dialog, so for any model you will maintain or hand off, the switch method wins.