Scenario Manager in Excel: Compare Best and Worst Cases

Scenario Manager in Excel stores several sets of input values, such as a best case, a base case and a worst case, and lets you swap them into your model in one click. It can also build a summary sheet that compares the results of every scenario side by side. This guide is for anyone who plans budgets, sales targets or projects and needs to answer "what happens if things go better, or worse, than expected?"

What does Scenario Manager do?#

Scenario Manager is one of Excel's What-If Analysis tools. A scenario is a named set of values for some input cells, called the changing cells. You save as many scenarios as you like, then:

• Show one: Excel types its values into the changing cells and the model recalculates.
• Summarize them all: Excel creates a new sheet with every scenario's inputs and results in one table.

It is the right tool when several inputs change together. In a bad year, you might sell fewer units, at a lower price, with higher costs, all at once. A what-if data table handles only one or two inputs, and Goal Seek finds a single input for a target.

Set up the model first#

Scenario Manager works on any model where results are formulas that depend on typed inputs. Here is a simple product profit model:

With these base values, B8 returns 5,000. The same structure sits behind a break-even analysis or a monthly budget.

Before you add scenarios, give the key cells names: select B3 and type Units in the Name Box, then do the same for Price, Unit_cost and Profit. The summary report will then show those names instead of cell addresses. The guide to named ranges shows other ways to create names.

How to add scenarios#

Add one scenario for each case you want to compare:

1. Go to Data > What-If Analysis > Scenario Manager.
2. Click Add.
3. Type a Scenario name, such as Base.
4. In Changing cells, select B3:B5 (Units, Price and Unit cost). Hold Ctrl (Mac: ⌘) to select cells that aren't next to each other.
5. Click OK, type the values for this scenario, and click OK again.
6. Repeat for the other cases.

For this model, use these values:

Add the Base scenario first with your current values. Showing a scenario overwrites the input cells, so a saved Base lets you get your original numbers back. Each scenario can have up to 32 changing cells.

How to show a scenario#

Open Scenario Manager, select a scenario in the list and click Show. Excel writes its values into B3:B5, and every formula that depends on them recalculates:

The worst case is a loss: 800 × (45 − 32) − 15,000 = −4,600. The best case is 1,200 × (52 − 29) − 15,000 = 12,600.

To describe the gap as a percentage, use percent change: best against base is (12,600 − 5,000) / 5,000 = 152% more profit.

How to create a scenario summary report#

The summary puts every scenario into one table on a new sheet, ready to print or paste into a report:

1. Open Scenario Manager and click Summary.
2. Choose Scenario summary.
3. In Result cells, select B8 (Profit). You can pick several results, such as profit and margin.
4. Click OK.

Excel adds a sheet called Scenario Summary. It shows a column for the current values and one for each scenario, with the changing cells at the top and the result cells below. If you named the cells, the rows read Units, Price, Unit_cost and Profit.

The summary is a snapshot. It doesn't update when you change the model, so run Summary again after any change. You can also choose Scenario PivotTable report to get the same comparison as a pivot table.

Limits of Scenario Manager#

Scenario Manager is useful but has some rough edges:

• Changing cells must be inputs. If a changing cell holds a formula, showing a scenario replaces it with a plain value.
• Scenarios belong to one sheet. To use scenarios made on another sheet or workbook, use Merge in the Scenario Manager box.
• Values are hidden. You can only see a scenario's numbers by opening the dialog or building a summary.
• Built for desktop Excel. Create and manage scenarios in Excel for Windows or Mac.

A formula alternative: switch scenarios with INDEX or CHOOSE#

Many modelers prefer to keep scenarios on the sheet, where everyone can see them. Put the three cases in columns, add a scenario number in B1 (1 = Worst, 2 = Base, 3 = Best), and pick the right column with INDEX:

=INDEX(B3:D3,B1)*(INDEX(B4:D4,B1)-INDEX(B5:D5,B1))-B6

With B1 set to 1, this returns −4,600; change B1 to 3 and it returns 12,600. Add a drop-down list in B1 and anyone can switch cases without opening a dialog. CHOOSE works the same way: =CHOOSE(B1,B3,C3,D3) returns the units for the chosen case. The CHOOSE function guide has a full scenario example. This approach also feeds nicely into a cash flow forecast with best and worst case lines.

Practise it#

Scenarios only help if the model under them is right. Practise a budget model in a live spreadsheet: you build totals and differences, and the sheet checks each formula instantly. Then practise percent change to describe how far apart your best and worst cases are. Or try the scenario switch exercise on this page.