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:
- Select Data > What-If Analysis > Scenario Manager, then choose Add.
- 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.
- In the Scenario Values dialog, enter 1000, 50, 30, and 12000 in order, corresponding to B2, B3, B4, and B5. Select OK.
- Choose Add again to create Downside. Keep B2:B5 as the changing cells and enter 800, 48, 32, and 13000.
- Add Upside with values 1200, 52, 29, and 12000. Select OK to return to Scenario Manager.
- 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
- How to Add Colour to Data Validation Cells in Excel
- Data Validation in Excel: Rules, Lists, and Setup Tips
- Excel Extract Text From the End of a String in 365
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Smartsheet DataTable Explained: Uses, Setup, and LimitsA Smartsheet DataTable is a centrally managed data store that can feed selected records into Smartsheet sheets, helping…
- How to Change Views in the New Outlook for WindowsIn the new Outlook for Windows, open the View tab to change how your mail or calendar is…
- Excel File Hidden? How to Unhide Worksheets and WorkbooksTo unhide a worksheet in Excel for Microsoft 365 on Windows, right-click any visible sheet tab, choose Unhide,…
- How to Insert and Use Checkboxes in Word for the WebTo add an interactive checkbox list in Word for the web, open the Home tab, choose Checklist in…
- Excel Data Validation From a Named Range: How It WorksTo use a named range for Excel data validation, select the cells that need a drop-down, choose Data…
- Google Sheets Count If Not Blank: Formulas and FixesTo count cells that are not blank in Google Sheets, use =COUNTA(A2:A100), replacing the range with your own.…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.