What-If Analysis: Goal Seek, Data Tables and the Model That Answers Backwards

The board wants 12,000 of operating profit a month. Somebody opens the model, types 50 into the price cell, looks at the answer, types 52, looks again, types 55, overshoots, types 54, tries 54.5, and eleven guesses later writes 54.44 on a whiteboard as though it had been difficult.

Excel would have said 54.44 in four seconds, exactly, and it would have said it without destroying the 48 that used to be in that cell.

That is the smallest version of what this article is about. A spreadsheet runs in one direction — inputs, formulas, answer — and almost every question a business asks runs the other way. What price gets us there. How many units. What happens if the cost goes up and volume goes down at the same time. Which of these five numbers is the one worth arguing about. Excel has four tools for running a model backwards, they are not interchangeable, and picking between them is entirely a matter of what shape your question is.

What this covers. Goal Seek, Data Table and Scenario Manager live under Data → Forecast → What-If Analysis in Excel 2013 and later, and under Data → What-If Analysis in 2007–2010; on Mac it is Data → What-If Analysis. Solver ships with Excel but is switched off until you enable the add-in — section 11. Google Sheets has Goal Seek as an add-on and has neither data tables nor scenarios; LibreOffice Calc has Goal Seek, Scenarios, and Multiple Operations in place of Data Table. Nothing here is a formula, which is exactly why it is worth learning: these are the four machines that drive the formulas you already have.

1) Forwards Models and Backwards Questions

Four tools, and the honest way to tell them apart is not what they do but what shape of question they answer.

Only Goal Seek and Solver genuinely run a model backwards — they search for an input. Data tables and scenarios run it forwards, repeatedly, and remember the results. That distinction predicts almost everything else about them, including which ones can fail to find an answer (the two that search) and which ones can quietly go stale (the two that remember).

The other thing they share: none of them improves your model. They interrogate it. A model with an assumption hardcoded inside a formula will be interrogated politely and will lie fluently to all four. Which is section 2.

2) Build the Model So It Can Be Asked Anything

Three rules, and every one of the four tools depends on all three.

1. Every input is a constant, alone in its own cell. Not embedded in a formula, not shared with a label, not derived from something else.
2. Every formula reads those cells. No number is ever typed inside a formula unless it is a genuine mathematical constant.
3. There is one cell that carries the answer. If the answer is spread across a row of subtotals, you have nothing to point Goal Seek at.

The grid at the top of this article is that shape. Five inputs, and two answers to build:

B7:  =B3*(B2-B4)-B5-B6          operating profit       →  3,950
B8:  =(B5+B6)/(B2-B4)           break-even units       →  1,101.5038

Read B7 out loud and it is just the model: units × contribution per unit, less fixed costs, less marketing. B8 is the same identity rearranged — the fixed cost pile divided by what one unit contributes.

You will want to round B8. Do not round it here. =ROUNDUP((B5+B6)/(B2-B4),0) gives you the honest 1,102 units to tell people about, and it also turns a smooth ramp into a staircase that Goal Seek cannot climb — section 4 shows what that failure looks like. Keep the raw value in the model cell and round in a display cell beside it. This is a general rule for any cell you intend to interrogate: round for humans, never for the solver.

Now the failure that rule 2 prevents. Suppose somebody had typed the price into the formula instead:

B7:  =B3*(48-B4)-B5-B6          looks identical, returns 3,950, is not the same model

Every one of the four tools breaks against this, and each breaks in its own unhelpful way. Goal Seek changes B2 and reports that it "may not have found a solution" because the target never moves. A data table over prices returns the same number seven times in a row, which reads as a bug in Excel rather than a bug in the formula. Scenario Manager cheerfully writes 52 into B2 and shows you a profit that has nothing to do with 52. Not one of them says your model ignores that input — they just answer the question you did not ask.

Formulas → Trace Precedents on the answer cell is the thirty-second check. If an arrow does not reach back to every input you plan to move, the tool you are about to use will not move it either.

3) Goal Seek: One Question, One Input

🎯 Scenario: The board wants 12,000 of operating profit a month. What has to be true?

Data → What-If Analysis → Goal Seek. Three boxes, and each has a rule that people discover by breaking it:

That middle box is the one that surprises people. "To value" takes a literal, so a target that lives in a cell has to be retyped every time it changes — or you restructure the model so the gap is the thing you seek to zero. =B7-B10 in a spare cell, Goal Seek that to 0 by changing B2, and the target can now live in B10 and be edited like any other input. That trick is worth more than it looks; it is how you make Goal Seek work against a moving target.

Run it four times against our model, once per lever, and the answers are worth reading side by side:

The last row is the important one. Goal Seek does not know that a marketing budget cannot be negative. It solved the arithmetic correctly: even at zero marketing spend this model earns 10,750, so reaching 12,000 through that lever alone requires marketing to pay you 1,250. The number is exact, defensible and useless — and Goal Seek returned it with no warning of any kind, because Goal Seek has no concept of a constraint. That single limitation is the entire argument for Solver, and it is section 11.

Two more things about that table.

54.44 is exact; 1,552.63 is not. Goal Seek is an iterative search: it stops when the result is within Maximum Change of the target — 0.001 by default — or when it has burned through Maximum Iterations, 100 by default. Both live in File → Options → Formulas → Calculation options, and both apply to Goal Seek even though the surrounding checkbox is about iterative calculation. Our model happens to be linear in price, so the search lands dead on 54.44. Volume divides into a non-terminating figure, and the cell will actually hold something like 1552.6315789. Round it yourself before you quote it, and never assume a Goal Seek result is exact just because the answer cell looks tidy.

Goal Seek overwrites your input, permanently. Clicking OK replaces the 48 in B2 with 54.44. Cancel puts it back, and Ctrl+Z immediately afterwards puts it back, but two edits later the 48 is gone and nothing anywhere on the sheet records that it was ever the base case. Before a session of Goal Seeks, either copy the input block to one side, or — better — save the base case as a scenario first, which is genuinely the job Scenario Manager was built for.

4) The Four Ways Goal Seek Fails

Goal Seek has one error dialog and it covers several unrelated problems, so the message tells you almost nothing. The symptom does.

The last one is worth seeing properly, because it looks like a bug in Excel and is not.

Suppose B8 had been written as =ROUNDUP((B5+B6)/(B2-B4),0) and you Goal Seek it to 1,000 by changing B2. The true answer is a price of 50.70. But B8 now moves in whole-number jumps: at 50.70 it reads 1,000, at 50.71 it still reads 1,000, and at 50.63 it jumps to 1,001. Goal Seek's search works by nudging the input and measuring how much the output moved — it needs a slope. Across most of the range it finds a slope of exactly zero, concludes it is getting nowhere, and gives up. Same maths, same model, one ROUNDUP, no answer.

IF, MIN, MAX, INT, CEILING, FLOOR and every lookup that returns from a banded table have exactly this property. If a Goal Seek fails on a model you are sure is right, follow the precedent arrows from the target back to the input and look for the first function on that path that produces steps instead of a ramp. It will be there.

Two smaller notes. Goal Seek works across sheets — set cell on one, changing cell on another — as long as the dependency exists. And it will not touch a locked cell on a protected sheet, which is a good reason to leave input cells unlocked when you protect a model.

5) One-Variable Data Tables: A Column of Answers at Once

🎯 Scenario: The price is the argument. Show the whole ladder at once and stop debating a single number.

The layout is the only hard part, and it stops being hard the moment you have seen it once. The inputs run down a column. The formulas sit in the row above, starting one column to the right. The corner is empty.

        D          E             F
  1                =B7           =B8          ← formula row, one column right of the inputs
  2       44
  3       46
  4       48
  5       50
  6       52
  7       54
  8       56

Select the whole block including the empty corner and the formula row — D1:F8 — then Data → What-If Analysis → Data Table. Because the inputs run down a column, you fill in Column input cell: B2 and leave Row input cell completely empty. That empty box is not an oversight; it is how Excel knows which way round the table runs.

The row in bold is the base case, and its presence is the check that the table is wired up correctly: the profit against 48 must equal what B7 says right now. If it does not, you have the wrong input cell.

Now read it as an argument rather than a table. The 54.44 that Goal Seek produced sits between the 54 and 56 rows — the two tools agree, which is reassuring, but the table shows something the single number could not: every 2 of price is worth exactly 2,500 of profit, all the way up and all the way down. Perfectly straight. That straightness is not a fact about the business; it is a confession about the model. Nothing in it says that units fall when the price rises. The moment you add that assumption, this column stops being a line, acquires a peak, and the peak is the answer everyone was actually looking for. A data table shows you shape. Goal Seek can only ever hand you a point.

Three layout rules that catch people:

• E1 must be =B7, a reference to the model's answer cell. You can retype the whole formula there instead, and it works — but only while it still points at the same inputs, and it is one more thing to keep in sync.
• Do not put a heading in the corner cell. D1 stays empty in a one-variable table. See the next section for what a heading does to a two-variable one.
• No gaps. The input column must be immediately below the corner and the formula row immediately to its right. A blank spacer column between them breaks the table with no error message.

6) Two-Variable Data Tables: The Corner Cell Nobody Fills In

🎯 Scenario: The argument is really about price and volume together. One number for every pair.

One variable runs across the top, the other runs down the side, and here is the whole trick: in a two-variable table, the formula goes in the top-left corner cell itself.

Not a heading. Not "Profit". Not "Sensitivity Grid" — which is exactly what is sitting in D1 in the grid at the top of this article, and it is there on purpose. Build a two-variable data table over a corner that contains a label and Excel does not object; it returns a grid of that text repeated, or a grid of #VALUE!, and a reasonable person concludes that data tables are broken. They are not. The corner is not decoration — it is where Excel looks to find the thing it is supposed to calculate.

Replace D1 with =B7. Then:

• E1:G1 hold the prices — 44, 48, 52
• D2:D6 hold the volumes — 900, 1,100, 1,250, 1,500, 1,700
• Select D1:G6, corner included
• Row input cell: B2 — the prices lie along a row
• Column input cell: B3 — the volumes run down a column

Two cells in that grid are worth pointing at.

3,950 sits where the base inputs intersect — 1,250 units at 48 — and it matches B7 exactly. That is the sanity check for a two-variable table, and it is worth deliberately including your live inputs on both axes so the check exists. Notice too that the whole 48 column reproduces the one-variable table from section 5 at the matching volumes; the two tables are the same model asked twice.

-40 is the break-even line crossing the grid. Break-even is 1,101.5 units at this price, so 1,100 units misses it by a hundredth of a percent of revenue. On a printed grid nobody spots that; a conditional formatting rule of =D2<0 across the results turns the entire loss region red in one go and the picture stops needing a caption.

The mnemonic for the input boxes: the inputs in a row go in the Row input cell. Get it backwards and Excel raises nothing at all — it substitutes volumes into the price cell and prices into the volume cell and fills your grid with arithmetically perfect nonsense. In this model that produces a grid where 900 "price" and 44 "units" yield some enormous number, so you would catch it. In a model where both inputs happen to be similar magnitudes, you would not. The intersection check above is what catches it every time.

7) What TABLE() Actually Is, and Why the Workbook Got Slow

Click any cell inside a finished data table. The formula bar reads:

{=TABLE(B2,B3)}

That is not a formula you wrote and not one you can write. TABLE cannot be typed by hand — it exists only as the thing Excel leaves behind — and the braces are not the old Ctrl+Shift+Enter array notation despite looking identical. What they do share is being a single object across the whole block, which has consequences:

• You cannot edit or delete one cell of the results. Excel says "You cannot change part of a data table."
• You cannot insert or delete rows or columns through it.
• You can delete the entire results block, and that is the only way to unmake one.
• You cannot copy the results to another sheet as live formulas; paste them as values.

Then there is the cost, which is the part that actually bites. A data table re-runs your whole model once for every result cell, on every recalculation of the workbook. The 3 × 5 grid above is 15 full evaluations of a six-cell model — you will never notice it. A 20 × 30 grid over a model with four thousand formulas is 600 full evaluations, 2.4 million formula calculations, on every keystroke anywhere in the file. You notice that within about a minute of building it, and it is the single most common reason a healthy workbook suddenly becomes unusable.

Excel has a switch specifically for this: Formulas → Calculation Options → Automatic Except for Data Tables. Everything else keeps recalculating normally; data tables recalculate only when you press F9. Two things to know about it. It is workbook-level and saved with the file, so it travels to whoever opens it next. And it is the first thing to check when someone reports that a colleague's grid "isn't updating" — the numbers are stale by design and the formulas are innocent.

8) When a Mixed-Reference Grid Beats a Data Table

🎯 Scenario: The same grid, but it has to be sorted, charted, edited and pasted into a deck by someone who has never heard of a data table.

A data table is not the only way to fill a grid. If the model is simple enough to restate as one formula, mixed references do the same job with none of the baggage:

E2:  =$D2*(E$1-$B$4)-$B$5-$B$6

Fill it across E2:G6 and every cell is right. $D2 locks the column so each cell reads its volume from D; E$1 locks the row so each cell reads its price from row 1; $B$4, $B$5 and $B$6 are locked in both directions because they are single cells the whole grid shares. Dollars point outwards, towards the edges of the block.

That last row is the entire trade, in both directions. A data table can never disagree with your model because it is your model, run repeatedly — which is exactly why it costs what it costs. A mixed-reference grid is a copy of the model, and copies rot: change the model to include a volume discount and the data table picks it up on the next F9 while the grid keeps confidently reporting last week's answer.

Use the data table when the model is real and complicated and correctness matters more than convenience. Use the grid when the model is three terms long and you want cells that behave like cells.

9) Scenario Manager: Naming a Whole Set of Inputs

🎯 Scenario: Three possible futures, five inputs each, and a meeting on Thursday.

Goal Seek moves one input. A data table moves one or two. Scenario Manager moves up to 32 at once, and — the actual point — gives the set a name you can say in a meeting.

Data → What-If Analysis → Scenario Manager → Add, then:

• Scenario name: Base
• Changing cells: B2:B6
• Comment: Excel pre-fills your username and today's date. Leave it — six months later it is the only record of who invented these numbers.
• Then it prompts for the value of each changing cell.

Do Base first, before you have touched a thing, because that is your undo. Then the other two:

Select a scenario, click Show, and the values are written into the cells. The whole workbook moves with them — dependent sheets, charts, conditional formats, everything. It is the only one of these tools that changes the live model rather than reporting on it, and that is simultaneously the point and the hazard.

Four things worth knowing before you rely on it:

• Changing cells must be on the active sheet. Scenarios are stored per worksheet, not per workbook. A model spread across three sheets needs three separate sets of scenarios that nothing keeps in sync. Build a single consolidated input sheet instead; it is less work than it sounds and it fixes this permanently.
• 32 changing cells is a hard ceiling, and the dialog is painful well before you reach it — it asks for the values one cell at a time, in a scrolling list, with no paste.
• Show is destructive. It overwrites whatever is in those cells with no confirmation. Ctrl+Z works right then and not later. Save a Base scenario first, every time.
• Nothing warns you when a scenario goes stale. This is the real bug factory. Add a sixth input to the model — a shipping cost in B9, say — and your three existing scenarios simply do not set it, because they were defined over B2:B6. Every Show from then on leaves B9 holding whatever it last held, so your Push case runs on a shipping assumption that arrived with Cautious. The numbers stay plausible, the scenario names stay reassuring, and nothing in the interface mentions it. Whenever the input block grows, open each scenario's Edit dialog and re-select the changing range.

10) The Scenario Summary, and What It Hides

Scenario Manager → Summary builds a comparison on a new sheet. Choose Scenario summary — the PivotTable option needs at least two scenarios defined over identical changing cells and is rarely what anyone wants — and give it the result cells: B7:B8.

Two habits turn the output from unreadable to useful:

Name the input cells first. The summary labels each row with the cell's defined name if it has one and with $B$2 if it does not. Five minutes in the Name Box — Unit_price, Units_sold, Variable_cost — is the difference between a report you can hand to someone and a report you have to narrate.

Understand the "Current Values" column. It is not a scenario. It is whatever happened to be sitting on the sheet at the moment you clicked Summary, printed in the leftmost column next to three named cases as though it were a fourth. If you had been mid-experiment, your half-finished experiment is now in the board pack. Show your Base scenario, then build the summary.

And then the thing the summary genuinely hides. Push earns 19,380 while Cautious loses 3,795 — a swing of 23,175 on a model whose base case earns 3,950. That is a dramatic-looking table, and it does not tell you a single thing about why, because four inputs moved at once. Was it the price? The volume? Did the extra 7,500 of marketing pay for itself, or was it carried by the unit cost falling to 20.60? The summary cannot say. Section 12 can.

11) Solver: When the Answer Needs More Than One Lever

The negative marketing budget from section 3 is the entire case for Solver. Solver is Goal Seek with three things added: many changing cells (up to 200), constraints, and an objective that can be maximise or minimise rather than one specific number.

Turn it on once: File → Options → Add-ins → Manage: Excel Add-ins → Go → tick Solver Add-in. It appears at the far right of the Data tab. On Mac it is Tools → Excel Add-ins.

The same question, properly stated: maximise B7, by changing B2 and B6, subject to B2 <= 52 because that is what the market will bear and B6 >= 4000 because that is contractual. Now the answer has to be achievable, because you told Solver what achievable means.

The honest caveat is that Solver only earns its keep when the model contains a real trade-off — volume that falls as price rises, a capacity ceiling, a fixed budget split between two channels. Point Solver at a linear model with no constraints and it will happily drive an input towards infinity, which is the correct answer to a badly posed question.

The three solving methods, and picking the wrong one is the usual reason Solver "doesn't work":

That last row closes the loop with section 4: the same step functions that stop Goal Seek dead also rule out both of Solver's fast methods.

12) Reading a Sensitivity Grid Without Fooling Yourself

🎯 Scenario: Before Thursday, find out which input is actually worth arguing about.

Move one input at a time, by ±10% from the base case, and record what the profit does:

Sort it by the last column and you have a tornado chart in table form. It takes fifteen minutes and it reorders most people's agenda: price is worth nearly twice what volume is worth, and nearly nine times what the entire marketing budget is worth. The meeting that was going to be about whether to spend another 2,000 on ads should be about the price list.

Three ways this table lies, and all three are worth saying out loud in the meeting rather than after it:

• ±10% is not equally plausible across the rows. A 10% price rise might be unthinkable while a 10% swing in units is a normal month. A tidy percentage applied uniformly ranks the impossible above the likely. Use the range each input could realistically take — best case to worst case, from whoever owns that number — and the ordering can change completely.
• It assumes the inputs are independent, and they are not. Put the price up 10% and units will not politely stay at 1,250. Every one-at-a-time sensitivity overstates its top row for exactly this reason, and price is nearly always the top row. The two-variable data table in section 6 is where you go to watch a pair move together, which is the honest version of this question.
• A linear model produces beautifully straight lines regardless of reality. Every number in that table is exactly proportional, because nothing in the model curves. That is a property of the spreadsheet, not of the business. If your sensitivity grid is suspiciously tidy, the assumption doing the real work is the one you never modelled.

Which is the note to end on. These four tools are very good at answering the question you asked of the model you built. Neither of those is the same as the truth, and the gap between them is where every bad forecast lives.

13) Mini Exercises

Copy the grid into a blank sheet starting at A1. Each answer is one formula or one dialog box.

1. Build the model. Write B7 and B8, then Goal Seek B7 to 12,000 by changing B2. State the price to two decimals and say why it comes out exact rather than approximate.
2. The impossible lever. Goal Seek B7 to 12,000 by changing B6. Report the number Excel gives you, and say in one line what it means and why Goal Seek was perfectly happy to hand it over.
3. A column of answers. Build a one-variable data table over prices 44 to 56 in steps of 2, returning both B7 and B8. Say which input box you filled in, which you left empty, and why that is what tells Excel the orientation.
4. The corner. Build the two-variable table in D1:G6. Name the cell that must contain =B7, then name the two input cells that go in the dialog — the right way round — and describe the check that would have caught you swapping them.
5. Same grid, no data table. Write the single formula for E2 that fills E2:G6 by hand, and say which half of which reference each dollar is protecting.
6. Three futures. Store Base, Cautious and Push in Scenario Manager and produce a summary over B7:B8. Then add a shipping cost in B9, wire it into B7, re-run each scenario, and describe in one sentence what the summary now gets wrong.
7. Which lever. Build the ±10% table from section 12 without touching the model — one formula, filled down and across. Say which input the business should actually be arguing about, and which of the three caveats most undermines your answer.

Summary

A model runs forwards and every question runs backwards, so the skill is matching the question to the machine. One exact target, one input: Goal Seek. A range of inputs and you want to see the shape: Data Table. Named sets of inputs you have to compare in a meeting: Scenario Manager. A best answer subject to rules that must not be broken: Solver.

All four depend entirely on rule 2 from section 2 — no number typed inside a formula — and all four fail silently when it is broken, which is why Trace Precedents on the answer cell is the cheapest thirty seconds in this whole article.

Three specifics carry most of the practical value. The corner cell of a two-variable data table holds the formula, never a heading. ROUNDUP and its family turn a ramp into a staircase and stop Goal Seek dead, so round for humans and leave the model cell raw. And Goal Seek has no constraints at all, which is why it will offer you a marketing budget of -1,250 with a completely straight face.

The board wanted 12,000, and 54.44 was always the answer. What the eleven guesses could never have told anyone is that price is worth nine times the marketing budget — and that is the number that should change what happens next.