12.2 What-If Analysis: Goal Seek & Scenario Manager

What-If Analysis: Goal Seek & Scenario Manager

Financial models are usually built to answer the forward question: given these inputs, what is the result? Objective 3.4 requires the reverse and the parallel. Goal Seek runs the model backwards — you name the answer you need and Excel finds the input that produces it. Scenario Manager runs the model sideways — you store several complete input sets and switch between them on demand. Both live in the same menu:

Data tab  ▸  Forecast group  ▸  What-If Analysis  ▾
                                   Scenario Manager...
                                   Goal Seek...
                                   Data Table...

---

Goal Seek: Solving Backwards for One Input

Goal Seek answers questions shaped like "what unit price gets me to a $2,000,000 gross margin?" It repeatedly substitutes trial values into one input cell until the formula cell reaches the target.

┌─ Goal Seek ──────────────────────────┐
│ Set cell:          [ $D$14      ] ◄── must contain a FORMULA
│ To value:          [ 2000000    ] ◄── must be a typed NUMBER
│ By changing cell:  [ $B$4       ] ◄── must contain a CONSTANT
└──────────────────────────────────────┘

The Three Rules That Govern Every Field

The changing cell may be an indirect precedent — Goal Seek follows the whole dependency chain, so an input three formulas upstream is still valid.

How the Solution Is Found

Goal Seek is an iterative numerical approximation, not an algebraic solve. It converges under the same two controls that govern circular-reference iteration, set at File > Options > Formulas:

• Maximum Iterations — default 100
• Maximum Change — default 0.001

When the model has no solution, several solutions, or a discontinuity, the status dialog reports that Goal Seek "may not have found a solution." Tightening Maximum Change and raising Maximum Iterations improves precision on stubborn models at the cost of speed.

Committing or Discarding the Result

Goal Seek writes its answer straight into the changing cell — the original input is gone from the sheet the moment the search finishes.

Copy the baseline input to a scratch cell before running Goal Seek on a live model; Ctrl+Z works, but only until the next recalculation-heavy edit.

Scope Limits

Goal Seek adjusts exactly one cell. Multi-variable optimisation, constrained targets, and integer requirements need the Solver add-in (File > Options > Add-ins > Excel Add-ins > Solver Add-in), not Goal Seek.

---

Scenario Manager: Storing Complete Input Sets

Where Goal Seek changes one cell, a scenario stores an entire named combination of inputs — Best Case, Base Case, Recession — and applies them all at once.

Building a Scenario

1. Data > What-If Analysis > Scenario Manager > Add...
2. Scenario name: a descriptive label such as Recession Case.
3. Changing cells: the input cells this scenario overrides. Hold Ctrl to pick non-contiguous cells. Microsoft documents the ceiling precisely: "A scenario can have a maximum of 32 different values, but you can create as many scenarios as you want."
4. Comment: Excel pre-fills the author and date; overwrite it with the assumption rationale.
5. Protection: Prevent changes and Hide take effect only once the worksheet itself is protected via Review > Protect Sheet.
6. Enter the value for each changing cell and click OK, or Add to define the next scenario immediately.

There is no limit on the number of scenarios, only on the changing cells inside one.

Applying and Reporting

The Scenario Summary dialog offers two report types and a Result cells box for the output cells you want compared:

• Scenario summary — a static, outlined table with one column per scenario plus a Current Values column reflecting whatever was on the sheet when the report was generated. Microsoft notes you do not need result cells for this report type.
• Scenario PivotTable report — a PivotTable of the same comparison. Result cells are required here.

[!NOTE] Name your changing cells and result cells before generating the report. Without defined names the summary column headings read $B$4 and $D$14; with them, they read Unit_Price and Gross_Margin.

Traps Worth Memorising

• A scenario report is a snapshot. Editing the model afterwards does not refresh it — regenerate the summary.
• If a changing cell contains a formula, applying a scenario overwrites that formula with a constant. Restrict changing cells to hard-coded inputs.
• Merge only sees workbooks that are currently open, and the merged scenarios must reference the same changing cells to be meaningful.