Scenario Analysis in Excel: Example and Setup Guide

How-to basics · Updated November 18, 2026

A scenario analysis Excel example compares how a result changes when you vary key assumptions. For a simple sales model, put units, selling price, variable cost per unit, and fixed costs in separate cells, calculate profit with formulas, then use Data > What-If Analysis > Scenario Manager to save and compare base, downside, and upside cases.

What scenario analysis means in Excel

Scenario analysis asks, “What would the result be if several assumptions changed together?” A sales business might consider lower demand alongside a discount and higher supplier costs. Rather than changing one number and forgetting the original, you record a coherent set of inputs for each case and compare the calculated outputs.

In Excel, the model is a worksheet: input cells hold assumptions, formula cells calculate results, and a scenario tool or comparison table organizes the alternatives. Scenario analysis does not predict which case will happen. It shows the consequences of specified assumptions, so the quality of the conclusion depends on whether those assumptions are plausible and clearly described.

This is useful for budgets, staffing plans, project estimates, inventory planning, and other “what if” questions. A scenario is different from a probability-weighted forecast: unless you add probabilities and a separate calculation, Excel’s Scenario Manager simply displays the results for each saved set of values.

Worked example: a small sales model

Suppose a business wants to see how monthly operating profit responds to changes in sales volume, price, unit cost, and fixed costs. Create a worksheet with these labels in column A and values or formulas in column B:

  • A2: Units sold; B2: 1,000
  • A3: Selling price per unit; B3: $50
  • A4: Variable cost per unit; B4: $30
  • A5: Monthly fixed costs; B5: $12,000
  • A7: Revenue; B7: calculated revenue
  • A8: Variable costs; B8: calculated variable costs
  • A9: Operating profit; B9: calculated profit

With units in B2 and price in B3, enter =B2*B3 in B7. The base case returns $50,000 in revenue. With units in B2 and unit variable cost in B4, enter =B2*B4 in B8; it returns $30,000. With revenue in B7, variable costs in B8, and fixed costs in B5, enter =B7-B8-B5 in B9. The base-case operating profit is $8,000.

These formulas make the model respond when an assumption changes. If you typed $8,000 directly into B9 instead, Excel would have no calculation to update when units, price, or costs changed. Keep assumptions and outputs separate, and label units and time periods so a reader can tell whether a value is monthly, annual, per item, or total.

Create scenarios with Scenario Manager

In Excel for Microsoft 365 on Windows, Scenario Manager is on the Data tab under What-If Analysis. The scenarios below change the four input cells B2:B5 while leaving the formulas in B7:B9 intact. Use the model above, then follow these steps:

  1. Select Data > What-If Analysis > Scenario Manager, then choose Add.
  2. Enter a descriptive scenario name, such as Base case. For Changing cells, select B2:B5. Use the same changing cells for every scenario so Excel can compare the cases consistently.
  3. In the Scenario Values dialog, enter 1000, 50, 30, and 12000 in order, corresponding to B2, B3, B4, and B5. Select OK.
  4. Choose Add again to create Downside. Keep B2:B5 as the changing cells and enter 800, 48, 32, and 13000.
  5. Add Upside with values 1200, 52, 29, and 12000. Select OK to return to Scenario Manager.
  6. Select a scenario and choose Show to place its assumptions into the worksheet. To create a comparison, choose Summary, select Scenario summary, and identify B7:B9 as the result cells.

Scenario Manager stores the selected values for changing cells; it does not replace the formulas in the result cells. In the example, the downside case returns $38,400 revenue, $25,600 variable costs, and a loss of $10,200: $38,400 minus $25,600 minus $13,000. The upside case returns $62,400 revenue, $34,800 variable costs, and $15,600 profit. The summary report lets you compare those outputs with the $8,000 base-case profit.

In Excel for Mac, look on the Data tab for What-If Analysis and Scenario Manager; ribbon placement and availability can vary by Excel release. If the command is not available in the version being used, a worksheet comparison table is a practical alternative. Excel for the web may not offer the same desktop what-if tools, so open the workbook in the desktop app when Scenario Manager is needed.

Read the results as a comparison, not a forecast

The example makes the combined impact visible: the downside is not simply “sell fewer units.” It also assumes a lower price, a higher unit cost, and higher fixed costs. Those changes work together, which is exactly why a scenario can be more informative than changing a single input in isolation.

Look at both the assumptions and the outputs. A loss in the downside case may matter, but the next question is whether the assumed 800 units, $48 price, and $32 unit cost are reasonable for the decision at hand. Record the source or rationale for important assumptions, and state the time period covered. If a result seems surprising, inspect the formulas and check that costs are represented consistently as positive amounts being subtracted.

Scenario Manager does not assign likelihoods, explain causes, or identify the best decision automatically. It also does not turn a small selection of cases into a complete range of possible outcomes. Treat the scenarios as structured comparisons, not guarantees. If probabilities are appropriate for the analysis, document them separately and make clear how any probability-weighted result is calculated.

Scenario Manager or a comparison table?

Scenario Manager is convenient when you want to save named sets of assumptions, show one set on the worksheet, or generate a Scenario Summary report. It is useful when a model has a manageable number of important input cells and a few cases that people will revisit.

A manual table can be easier to understand and share. For example, you can put Base, Downside, and Upside in columns, list each assumption in rows, and calculate each case in a separate formula column. That layout makes the assumptions visible at once and is often preferable for a short model, a dashboard, or a report that needs custom formatting. It also avoids switching the worksheet between scenarios.

Excel’s one-variable and two-variable Data Tables, found under Data > What-If Analysis, answer a different kind of question: they show how one or two inputs affect a formula across a range of values. Scenario Manager is better suited to named combinations of assumptions. For example, a data table might vary units sold from 700 to 1,300 while holding price and costs fixed; the three named scenarios above vary several assumptions together.

Common setup problems and good practices

  • Wrong changing cells: Confirm that B2:B5 contains only the assumptions you intend to vary. Including an output formula cell can overwrite the formula when a scenario is shown.
  • Inconsistent order: When adding scenarios, use the same changing-cell range and enter values in the same cell order. Clear labels beside the input cells reduce mix-ups.
  • Unclear assumptions: Name cases for what they represent, and note the reason for the values. “Supplier delay and price pressure” can be more informative than “Scenario 2.”
  • Hard-coded outputs: Keep outputs formula-driven. If the profit cell is a typed number, the scenario comparison will not reflect changes correctly.
  • Overly optimistic or pessimistic cases: A scenario is only useful if its assumptions are internally consistent and relevant. Avoid changing inputs merely to make a preferred result appear.
  • Unnoticed changes after showing a case: Showing a scenario changes the worksheet’s input values. Before continuing other work, show the intended case again or restore the base values; save a clean copy when the starting state matters.

For a model used by other people, add a brief note explaining which cells are inputs and which are formulas. Consider applying a consistent fill color to input cells, but do not rely on color alone: labels and instructions should make the model understandable when printed or viewed without color.

Frequently Asked Questions

How many scenarios should an Excel analysis have?

There is no required number. Three cases—base, downside, and upside—are a common starting point because they make a comparison easy to read, but they are not automatically sufficient. Add cases when they represent distinct, decision-relevant conditions, rather than creating many variations that are difficult to explain.

Can Scenario Manager change several cells at once?

Yes. When you add a scenario, specify a range or selection of changing cells, then provide a value for each selected cell. In the example, B2:B5 changes together, while the revenue, cost, and profit formulas remain in place.

Does Excel Scenario Manager calculate which scenario is most likely?

No. It applies the values stored in each scenario and shows the resulting worksheet outputs. It does not estimate probabilities or choose a likely case. Any probability assumptions or expected-value calculation must be designed and explained separately.

Where is Scenario Manager in Excel?

In Excel for Microsoft 365 on Windows, select Data > What-If Analysis > Scenario Manager. On Mac, look under the Data tab for the corresponding what-if command; exact placement and availability can depend on the release. If it is missing in a web version, use desktop Excel or build a comparison table.

What is the difference between Scenario Manager and a data table?

Scenario Manager compares named combinations of input values, such as changes to units, price, and costs at the same time. A one-variable or two-variable Data Table calculates results across a grid of values for one or two inputs. Choose based on whether you need named cases or a range-based sensitivity view.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.