What-If Analysis: Goal Seek & Scenarios
Work backwards from the answer you want with Goal Seek, and compare whole sets of assumptions side by side with Scenario Manager.
What you will learn
- Explain what What-If analysis is for
- Back-solve an input with Goal Seek
- Compare best/worst cases with Scenario Manager
Asking “what if?”
Normal formulas run forwards: you put in numbers and get a result. What-If Analysis lets you ask the reverse and the comparative questions every business cares about: “What input do I need to hit this target?” and “How does my result change under different assumptions?” Excel has a small toolkit for this under Data > What-If Analysis.
Goal Seek: work backwards from the answer
Goal Seek answers: “I know the result I want — what input gets me there?” Instead of guessing and re-typing, Excel finds the input for you. It needs three things: the cell to change to hit your goal, the result cell, and the target value.
A worked example. You sell an item for a price in B1 and quantity in B2, with revenue =B1*B2 in B3. Right now the price is 50 and you sell 8, so revenue is 400. You want revenue to be exactly 600 — what price do you need, keeping quantity at 8?
| Cell | Holds | Value now |
|---|---|---|
| B1 | Price | 50 |
| B2 | Quantity | 8 |
| B3 | Revenue =B1*B2 | 400 |
Data > What-If Analysis > Goal Seek
Set cell: B3
To value: 600
By changing cell: B1Note: Output:
Goal Seek sets B1 to 75 (and B3 becomes 600).
Excel tried different prices until B1*8 hit 600, landing on a price of 75. You stated the answer you wanted and Excel back-solved the input — no trial and error by hand. Goal Seek changes only one input to reach one target.
Watch out: Goal Seek overwrites the changing cell with its found value. If you want to keep your original number, note it first (or click Cancel in the result box). It also needs the result cell to be a formula that depends on the changing cell — it cannot solve a plain typed number.
Scenario Manager: compare whole sets of assumptions
Goal Seek changes one input. Scenario Manager lets you save several named sets of inputs — like Best case, Worst case and Expected — and flip between them to see how the result changes. Each scenario is a saved snapshot of several input cells at once.
Say a simple profit model has units in B1, price in B2, and cost in B3, with profit =B1*(B2-B3) in B4. You define three scenarios that each set B1, B2 and B3 differently:
| Scenario | Units (B1) | Price (B2) | Cost (B3) | Profit (B4) |
|---|---|---|---|---|
| Worst case | 80 | 40 | 30 | 800 |
| Expected | 100 | 45 | 30 | 1500 |
| Best case | 130 | 50 | 28 | 2860 |
Data > What-If Analysis > Scenario Manager > Add
Scenario name: Best case
Changing cells: B1,B2,B3
Values: 130, 50, 28Note: Output: Selecting “Best case” and clicking Show fills B1, B2, B3 with 130, 50, 28, and B4 (profit) jumps to 2860. Switch to “Worst case” and the same cells change and profit drops to 800. One model, three saved futures you can flip between instantly — and a Summary report lays all three side by side.
Tip: There is a third tool, Data Table, for showing how one result changes across many values of an input (for example, profit at every price from 40 to 60 in a single grid). Goal Seek answers “what input hits this target?”, Scenario Manager compares named cases, and a Data Table sweeps a whole range — together they are the core “analysis” in spreadsheet analysis.
Q. You want to know what price makes your revenue exactly ₹10,000, with quantity fixed. Which tool fits best?
✍️ Practice
- Build the price/quantity/revenue model and use Goal Seek to find the price that makes revenue 600 at quantity 8.
- Create the profit model and save three scenarios (Worst, Expected, Best), then switch between them and note how profit changes.
🏠 Homework
- Make a simple loan or savings model where one result depends on a rate and an amount. Use Goal Seek to find the amount needed to reach a target, then build Worst/Expected/Best scenarios and produce a Scenario Summary.