5.2What-if analysis: Goal Seek & Solver
The essentials in 30 seconds
- Goal Seek = one unknown: you fix the result cell (e.g. margin = 0) and Excel adjusts one input cell to reach it — ideal for break-even.
- Scenarios = compare: the Manager stores assumption sets (pessimistic, realistic, optimistic) and switches in one click, with a comparison report.
- Solver = optimize: an add-in to enable that adjusts several variables to maximize or minimize an objective under constraints.
Running mini-case — Wassim and the break-even point
Wassim, an accountant in Algiers, must answer his management: at what selling price does the new line reach its break-even point? He uses Goal Seek on his model (margin in B10, price in B2) and finds the break-even price. Then management asks for three cost assumptions: he stores a pessimistic, realistic and optimistic scenario and produces the comparison report. Finally, to allocate the budget between two channels under a total-budget constraint, he enables Solver. Continuation of case 5.1 — Wassim moves from the dashboard to the « what if…? » questions.
Continuation of case 5.1 — Wassim answers his management's « what if…? » questions.
1. Goal Seek: one unknown
Goal Seek (Data ▸ What-If Analysis) answers: « which input gives this result? ». You fix the result cell (e.g. Wassim's margin in B10 = 0, the break-even) and Excel adjusts one input cell (the price in B2) to reach it. Ideal for a single-unknown question.
Condition: the cell to change must be an input (a typed number), never a formula — Goal Seek « works back » from the result to the input. Wassim checks that his price is indeed an entry before running: if the target modified a formula, the model would break.
2. Scenario Manager
The Scenario Manager stores several assumption sets (pessimistic, realistic, optimistic) and switches between them in one click, with a comparison report. Wassim fixes his three cost assumptions in Algiers and presents to his management: « under these assumptions, here's the result » — without duplicating the workbook into three diverging versions.
Each scenario names its variable cells and their values; the summary report shows the results of each set side by side. Perfect to frame a decision: you discuss the assumptions, not the files.
3. Solver: optimize under constraints
Solver (an add-in to enable) goes further: it adjusts several variables to maximize or minimize an objective under constraints (limited total budget, positive quantities). Wassim allocates his budget between two channels to maximize sales, with the constraint that the sum doesn't exceed the voted budget. It's optimization, not a simple formula.
You declare: the objective cell (to maximize, minimize or set), the variable cells (to adjust) and the constraints (the limits). Then you let Solver find the best combination — and you check the result: an optimization is only good if its constraints describe reality.
🔎 Concrete example — Reach a break-even point
The margin (B10) depends on the price (B2). What price for a margin of 0?
Data > Goal Seek:
Set cell: B10 (the margin)
To value: 0
By changing cell: B2 (the price, an ENTRY)
-> Excel computes the price that zeroes the margin (break-even)Reading: Goal Seek « reverses » the calculation: from result to input.
Lesson: one unknown → Goal Seek; several variables + constraints → Solver.
Section vocabulary
- Goal Seek
- The tool that finds the input (a single cell) producing a wanted result.
Simple example: Wassim finds the price that zeroes his margin.
What it is not: a tool that adjusts several variables at once.
Reflex: do I really have only one unknown?
- Scenario Manager
- The tool that stores and compares several assumption sets.
Simple example: Wassim switches between pessimistic, realistic and optimistic in one click.
What it is not: three diverging copies of the same workbook.
Reflex: are my assumptions named and comparable?
- Solver
- The add-in that optimizes several variables under constraints.
Simple example: Wassim allocates his budget between two channels to maximize sales.
What it is not: a formula that computes a direct result.
Reflex: do I have an objective, variables and constraints?
- Constraint
- A limit imposed on the variables during optimization.
Simple example: Wassim imposes that the sum of the channels doesn't exceed the voted budget.
What it is not: a simple cell format.
Reflex: are my real limits written down?
- What-if analysis
- Exploring the effect of assumptions on a result before deciding.
Simple example: Wassim tests three costs before setting his price.
What it is not: deciding on a single implicit assumption.
Reflex: which assumption would change my decision?
In practice — Explore assumptions
- Build a mini-model (price, cost, formulated margin).
- Use Goal Seek to find the price that zeroes the margin.
- Create 3 scenarios (pessimistic/realistic/optimistic) and compare them.
- Enable Solver and optimize a simple allocation under one constraint.
Ready-to-say script — present the three assumptions to your management
« For the new line's price, here's my model: with Goal Seek, the break-even is at such a price. I stored three cost scenarios — pessimistic, realistic, optimistic — and the comparison report shows the margin in each case. If we have to allocate the budget between the two channels, I propose an optimization under a total-budget constraint with Solver. We decide on the assumptions, not on a single figure. »
Say it in a meeting to frame the decision on explicit assumptions.
Copy-paste template — what-if analysis sheet
WHAT-IF ANALYSIS SHEET — question: ___ (one unknown? several? constraints?)
TOOL: Goal Seek / Scenarios / Solver (circle)
GOAL SEEK: result ___ = ___ · entry to adjust ___ (check: it's an ENTRY)
SCENARIOS: pessimistic ___ · realistic ___ · optimistic ___ (variable cells: ___)
SOLVER: objective ___ (max/min) · variables ___ · constraints ___ (e.g. sum <= budget)Solo / small budget
Wassim alone: Goal Seek is enough for break-even — Solver will wait for the first real budget to allocate.
SME / small team
Three named scenarios and a comparison report: the whole team debates the same assumptions.
SME in Algiers
Wassim sets up a budget checkpoint in Algiers: projected scenarios, constraints written before any optimization.
Team getting started
Pair up: one writes the assumptions, the other checks the objective, variables and constraints before running Solver.
✓ Best practices
- One unknown → Goal Seek.
- Several assumptions to compare → Scenarios.
- Optimize under constraints → Solver.
✗ Common pitfalls
- Using Goal Seek for several variables (impossible).
- Changing a cell containing a formula (an input is needed).
- Forgetting to enable the Solver add-in.
Key takeaways
- Goal Seek: an input for a wanted result (1 unknown).
- Scenario Manager: compare assumption sets.
- Solver: optimize several variables under constraints.
- You choose the tool by the number of variables and the presence of constraints.
48h challenge — apply tomorrow
On your real model, find via Goal Seek the input that reaches your objective, create 3 named scenarios, then optimize a simple allocation with Solver under one written constraint.
- I can say when to use Goal Seek, Scenarios or Solver.
- My Goal Seek adjusts an input, not a formula.
- My Solver has an objective, variables and written constraints.
Frequently asked questions
Goal Seek or Solver?
Goal Seek for a single unknown and an exact objective (reach a value). Solver when there are several variables, an optimization (max/min) and constraints. The Scenario Manager, in turn, compares fixed assumptions.
I can't find Solver?
It's an add-in to enable: File ▸ Options ▸ Add-ins ▸ Excel Add-ins ▸ tick « Solver Add-in ». It then appears in the Data tab.
You want the price that zeroes your margin (a single unknown). Which tool?