8.1 Goal Seek and Data Tables

Goal Seek: one output, one input

Quick Answer: Goal Seek finds the single input that makes a single formula cell equal a target. The dialog is Set cell (the output formula), To value (a typed number), and By changing cell (one input Excel is allowed to overwrite). Data tables are the live sensitivity engine: one-way varies one input, two-way varies two inputs and returns one output. You create them with Data → What-If Analysis → Data Table, which writes {=TABLE(...)} — you cannot type TABLE as an ordinary function. Sensitivity changes one driver at a time; scenario analysis changes several drivers together (downside / base / upside). CFI's core Scenario & Sensitivity Analysis in Excel course, and CFI models, prefer a scenario number + CHOOSE/INDEX toggle over Excel's Scenario Manager.

Excel is about 10% of CFI's published FMVA topic-weight graphic. After the 27 February 2026 refresh, Scenario & Sensitivity Analysis in Excel is required core, not an elective. The final will not score you for reciting the ribbon. It will score you for recovering a required volume, reading a two-way value grid, or recognizing which driver set is the downside case.

The Goal Seek dialog

Data → What-If Analysis → Goal Seek (the same family as Data Table and Scenario Manager) presents three boxes:

Excel iterates the changing cell until the set cell is as close as it can get to the target, or until it concludes there is no solution. On OK, the new input is committed as a number. Cancel restores the prior value.

Worked Goal Seek: volume that delivers $3.0 million of EBIT.

A simple operating block:

Contribution margin per unit is $40 − $22 = $18. Management wants EBIT of $3,000,000. Algebra, so you can audit Excel:

18 × Q − 2,400,000 = 3,000,000 → 18 × Q = 5,400,000 → Q = 300,000 units.

Goal Seek: Set cell B10, To value 3000000, By changing cell B4. Excel overwrites B4 with 300,000. Revenue becomes $12,000,000, variable costs $6,600,000, EBIT $3,000,000.

Four traps sit in that three-box dialog.

1. Goal Seek destroys whatever is in the changing cell. If B4 had been =INDEX(...) or a link to a driver table, the formula is gone. Change a blue input, never a black calculation cell.
2. One output, one input. You cannot Goal Seek EBIT by changing both price and volume in one run. That is Solver (constraints, several changing cells) or a two-way data table you read by eye.
3. The target is a number. Typing B20 in To value does not link to B20. If the set cell is formatted as a percent, 15% is 0.15, not 15.
4. Goal Seek is not live. If fixed costs later move from $2.4 million to $2.6 million, B4 stays at 300,000 until you re-run Goal Seek. A data table, or an algebraic inverse such as Q = (Target EBIT + Fixed) / (P − VC), stays live.

Solver can handle several changing cells and constraints (volume ≥ 0, a capacity cap). FMVA core work uses Goal Seek for a single unknown: required volume, required WACC for NPV = 0, required exit multiple. Do not spend case-study minutes opening Solver unless the item clearly needs constraints.

Goal Seek can also miss the economically relevant root if the output is not monotonic — IRR-style polynomials, circular interest, or a stepped tax rate. When the answer looks absurd (negative volume, WACC of 400%), undo, inspect the formula with F2, and start from a better guess.

Data tables: the engine of sensitivity

Sensitivity analysis answers: if I change one driver, holding the rest at the selected case, what happens to NPV, equity value, or debt / EBITDA? Excel's dedicated tool is the Data Table, not a column of copied models and not Goal Seek run five times.

Build the model once. Point a table at the live output. Excel substitutes trial values into the specified row input cell and/or column input cell, recalculates, and writes the results as an array.

You cannot author {=TABLE(row_input, column_input)} by typing it. The TABLE array is created only by Data → What-If Analysis → Data Table. Pasting =TABLE(...) as a normal formula fails. That is a high-frequency exam trap. After the tool runs, those result cells are an array: you cannot edit one interior cell; you replace or delete the whole table.

One-way data table (column-oriented): put the output formula (or a link to it) in the cell above the result column. List trial input values in the column to the left, starting in the row below the formula. Select the whole block. Data Table dialog: Column input cell = the model input you are varying; leave Row input cell blank.

One-way data table (row-oriented): trial values across a row, formula to the left of that row, fill Row input cell, leave Column input blank.

Two-way data table: formula (or link) in the top-left corner; a row of trial values for input A along the top; a column of trial values for input B down the left. Data Table: Row input cell = the model input that matches the top row; Column input cell = the model input that matches the left column. A two-way table returns exactly one output. If you need NPV and IRR, you need two tables, or a one-way table with two output formulas across the top.

Data tables must live on the same worksheet as the input cells they shock. A dashboard table that tries to vary Assumptions!C5 will not calculate correctly. Put the grid next to the DCF output and the WACC / g cells, or host those inputs on the sensitivity sheet. That Excel limitation is why CFI layouts often keep a sensitivity block on the same sheet as the live drivers.

If workbook calculation is Automatic except data tables, the TABLE array lags until you press F9. On a timed case, a "dead" table is often a calc-mode issue, not a broken formula. Data tables can also slow a large file; that is why the except-data-tables mode exists — know which mode you are in before you trust a stale grid.

Worked one-way table: NPV versus discount rate

Project: invest $8,000,000 at t = 0; cash inflows $2,200,000 at the end of each of years 1–5. NPV lives in F12 as =NPV(C5,F7:F11)+F6 with F6 holding the negative outlay. Base discount rate in C5 is 10%.

Five-year annuity factor at 10%: (1 − 1.10^−5) / 0.10 = 3.7907868.

NPV = −8,000,000 + 2,200,000 × 3.7907868 = $339,731.

Build a column-oriented one-way table with the formula =F12 above the result column and trial rates 8%, 9%, 10%, 11%, 12% down the left. Column input cell = C5. Row input cell blank.

The table must reprint $339,731 in the 10% row. If it does not, you swapped the input cell, pointed at a pasted number instead of the live NPV formula, never ran the Data Table tool, or calculation is set to skip data tables. IRR sits between 11% and 12% because NPV changes sign there. Goal Seek on F12 to value 0 by changing C5 is the single-rate complement to this table: same model, one unknown, not a grid.

Audit the NPV formula with F2 before you build the table. NPV(rate, value1, ...) discounts the range as if the first cell were the end of period 1. The t = 0 outlay must sit outside that range (added after, as +F6). A table that is internally consistent and still economically wrong is usually this sign/timing error, not a TABLE bug.

Worked two-way mini table: EBIT versus price and volume

Return to the EBIT block. Contribution is still P − 22; fixed costs remain $2,400,000. Put =B10 in the top-left corner of a blank grid. Across the top, prices $36, $38, $40, $42, $44. Down the left, volumes 200,000, 225,000, 250,000, 275,000, 300,000. Select the whole rectangle. Data Table: Row input cell = B3 (price, matching the top row); Column input cell = B4 (volume, matching the left column).

The $40 / 250,000 intersection is the $2.10 million live base. The $40 / 300,000 intersection is the $3.00 million Goal Seek result. That cross-check is how you audit a table on the exam: the base-case coordinates must match the model before you read any off-base cell.

Swapping row and column input cells is the classic construction error. Every number will look "smooth" and still be wrong, because Excel is shocking price with the volume list and volume with the price list. Always verify the base-case intersection first. If $40 / 250,000 does not show 2.10, stop and rebuild the dialog; do not interpret a transposed grid.

Sample interior math you can recompute by hand if a case asks you to challenge one cell: at P = $36 and Q = 200,000, EBIT = (36 − 22) × 200,000 − 2,400,000 = $400,000. At P = $44 and Q = 300,000, EBIT = (44 − 22) × 300,000 − 2,400,000 = $4,200,000.

Sensitivity versus scenario — and why CFI does not live in Scenario Manager

CFI's official modeling language (and this guide's formula list) is strict:

• Sensitivity = change one input at a time (data tables).
• Scenario = change several inputs together into a coherent case — typically downside / base / upside.

A two-way WACC-by-g table is sensitivity. A package that simultaneously moves volume to 200,000, price to $36, and WACC to 10.5% is a downside scenario. Do not call a two-way data table a scenario analysis. Do not call three pasted copies of a workbook a sensitivity table.

Excel's Scenario Manager (Data → What-If Analysis → Scenario Manager) stores named sets of input cells. It works. It is the wrong default for CFI-style models.

CFI models typically put a scenario number in a blue cell (1 = downside, 2 = base, 3 = upside) and pull each driver with CHOOSE or INDEX:

=CHOOSE($C$2, 200000, 250000, 300000)
=INDEX(VolumeRow, $C$2)

Worked toggle:

With C2 = 2, volume is 250,000 and price is $40, so EBIT is $2.1 million. Switch C2 to 1 and several inputs move together: EBIT at $36 and 200,000 units is $0.40 million from the two-way table. That is scenario analysis. Then, still sitting in downside, a one-way WACC table around 10.5% is sensitivity inside a scenario. INDEX is usually cleaner once the driver table is a block; CHOOSE is readable when there are only three cases.

On the timed Excel case: do not rebuild three full models. Build one model, one scenario toggle, and one data table. That is the design of the core course Scenario & Sensitivity Analysis in Excel. Later chapters apply the same tools to debt schedules and DCF; this section is the Excel engine those chapters assume you can operate under a 45-minute case budget.

Exam close for 8.1

• Goal Seek: one formula, one typed target, one blue input; then re-run if drivers move.
• Data Table tool, not a typed TABLE formula; same sheet as the inputs; F9 if calc mode skips tables.
• Two-way = two inputs, one output; verify the base-case intersection before reading the rest.
• Sensitivity = one at a time; scenario = several together; CFI toggle = scenario number + CHOOSE/INDEX, not Scenario Manager as the primary control.