How to use goal seek to find break-even units sold in an excel p&l model
- Step 1Build or open the P&L model — Make sure your sheet has the standard pieces: a units cell (the input), price, unit variable cost, fixed cost, and a profit cell that computes
(Units×Price) − (Units×UnitCost) − Fixed. The profit cell must be a formula, and it must use supported functions and single-cell references. - Step 2Upload the .xlsx — Drop the workbook onto the tool —
.xlsxonly, parsed locally by SheetJS so your margins stay private. Goal Seek is a Developer-tier feature; lower tiers are blocked with a tier message. - Step 3Set the target to the profit cell — Enter the profit cell's reference in
targetCell(e.g.D8). This is the cell you want to control. Confirm it holds the profit formula, not a pasted number. - Step 4Keep the target value at 0 — Break-even means profit = 0, so leave
targetValueat its default0. If you instead want the units for a target profit (say $10,000), settargetValueto that number — same tool, different goal. - Step 5Set the input to the units cell — Enter the units cell in
inputCell(e.g.B2). Seed it with a rough current volume; the solver brackets its search around that value. SetsheetNameif the model isn't on the first sheet. - Step 6Run and round the result — Read the break-even units from the report (to six decimals). Round up to the next whole unit for a sellable target — selling exactly the fractional break-even leaves you a few cents short. Type that number into your model; the tool returns a report, it doesn't edit the file.
Break-even Goal Seek setup
The exact options for a units-sold break-even solve. Only the profit and units references change between models; the target value stays at 0.
| Option | Break-even value | Notes |
|---|---|---|
targetCell | your profit cell, e.g. D8 | Must be the profit formula — Revenue − VarCost − Fixed. |
targetValue | 0 | Default. Profit = 0 is the break-even point. Change it for a target-profit solve. |
inputCell | your units cell, e.g. B2 | The volume to vary. Seed it with a plausible current figure. |
sheetName | blank or your model tab | Defaults to first sheet. |
Worked P&L: contribution margin vs Goal Seek
A simple model where both methods agree. Price 25, variable cost 10, fixed cost 4,500. Contribution margin = 15/unit; break-even = 4500 / 15 = 300 units.
| Quantity | Formula in model | Value at break-even (300 units) |
|---|---|---|
| Price (C2) | static | 25 |
| Unit variable cost (C3) | static | 10 |
| Fixed cost (C4) | static | 4,500 |
| Contribution / unit | C2 − C3 | 15 |
| Revenue (D2) | B2 * C2 | 7,500 |
| Profit (D8) | (B2*C2) − (B2*C3) − C4 | 0 ← target |
Common break-even model mistakes the solver exposes
What goes wrong and what the report tells you.
| Model issue | Symptom | Fix |
|---|---|---|
| Profit cell is a typed number | "must contain a formula" error | Rebuild profit as a formula |
| Price ≤ variable cost | no convergence, large residual | Contribution is negative — model can never break even |
| Variable cost uses SUMPRODUCT or a range | NaN, search fails | Expand to single-cell arithmetic |
| Fixed cost cell references a stale subtotal | wrong break-even | Re-save workbook so cached values are current |
Cookbook
Break-even solves across a few common P&L shapes. Each shows the model cells, the options, and the report.
Classic single-product break-even
Price 25, variable cost 10, fixed cost 4,500. Find the units that drive profit to zero.
Sheet1: B2 = 1 (input — units) C2 = 25 (price) C3 = 10 (variable cost / unit) C4 = 4500 (fixed cost) D8 = (B2*C2) - (B2*C3) - C4 Options: targetCell = D8 targetValue = 0 inputCell = B2 Report: ✓ Goal Seek converged after 48 iteration(s). Input cell: B2 = 300.000000 Output cell: D8 = 0.000000 Target value: 0 → Break-even at 300 units.
Break-even with a sales commission
Add a per-unit commission to variable cost. Price 25, cost 10, commission 2, fixed 4,500. Contribution drops to 13/unit, so break-even rises.
Sheet1: B2 = 1 (input — units) C2 = 25 (price) C3 = 10 (cost) C5 = 2 (commission / unit) C4 = 4500 (fixed) D8 = (B2*C2) - (B2*C3) - (B2*C5) - C4 Options: targetCell = D8 · targetValue = 0 · inputCell = B2 Report: ✓ converged after 49 iteration(s). Input cell: B2 = 346.153846 Output cell: D8 = 0.000000 → Round up: 347 units to cover costs.
Target-profit instead of break-even
Same model, but you want the units for $10,000 profit, not zero. Just change the target value.
Model: D8 = (B2*25) - (B2*10) - 4500 Options: targetCell = D8 targetValue = 10000 inputCell = B2 Report: ✓ converged after 50 iteration(s). Input cell: B2 = 966.666667 Output cell: D8 = 10000.000000 → 967 units to clear $10k profit.
A model that cannot break even
Price below variable cost means every unit loses money — there's no positive volume that reaches zero profit. The solver reports a best approximation.
Sheet1: B2 = 1 D8 = (B2*8) - (B2*10) - 4500 (price 8 < cost 10) Options: targetCell = D8 · targetValue = 0 · inputCell = B2 Report: ⚠ did not fully converge (best residual: 4.5e+3). Best found: Input cell: B2 = 0.000000 Output cell: D8 = -4500.000000 → Negative contribution: model never breaks even.
Tiered fixed cost using IF
Fixed cost steps up above 500 units (a second shift). IF is supported, so the step model solves directly.
Sheet1:
B2 = 1
D8 = (B2*25) - (B2*10) - IF(B2>500, 9000, 4500)
Options:
targetCell = D8 · targetValue = 0 · inputCell = B2
Report:
✓ converged after 51 iteration(s).
Input cell: B2 = 300.000000
Output cell: D8 = 0.000000
→ Break-even (300) is below the 500-unit step, so
the lower fixed cost applies. Verify the tier.Edge cases and what actually happens
Price below variable cost
No convergenceIf unit price is at or below unit variable cost, contribution margin is zero or negative — no volume ever covers fixed costs. The solver returns a best approximation (usually input near 0) with a residual equal to your fixed cost. The fix is the pricing, not the solver.
Profit cell is a pasted total
RejectedIf someone hard-coded the profit number instead of leaving the formula, you get Target cell must contain a formula for Goal Seek. It currently holds a static value. Restore the profit formula so the solver has something to recompute.
Variable cost uses SUMPRODUCT or a range
NaN / search failsMulti-component cost lines built with SUMPRODUCT, or with SUM(C2:C9) range syntax, aren't supported and return NaN. Expand the cost build into explicit single-cell arithmetic, or precompute the per-unit cost into one cell that the profit formula references.
Fractional units in the answer
ExpectedBreak-even is rarely a whole number — 346.15 units, say. That's correct: it's the exact zero-profit point. Round up to the next whole unit for an actionable target, since selling 346 units would still leave a small loss.
Fixed cost references a stale subtotal
Cached values usedThe solver recomputes only the profit formula; cells it references (including a fixed-cost subtotal that is itself a formula) are read at their cached values and not recalculated. If those caches are stale, the break-even is wrong. Re-save the workbook to refresh all cached results first.
Step-fixed cost crosses inside the search
Verify the tierAn IF-based step cost (fixed jumps above some volume) is supported, but bisection can land near the step boundary. Confirm the converged units fall on the side of the step the answer assumes — re-run with the input seeded just above or below the step if the result looks off.
Multi-product break-even
Out of scopeIf break-even depends on a sales mix across several products (several input volumes), that's a multi-variable problem for Solver, not Goal Seek. Goal Seek varies one cell only. Collapse the mix into a single blended-contribution input, or use Solver in desktop Excel.
Target profit is unreachable downward
No convergenceAsking for a profit the model's structure can't produce (e.g. a target above the maximum the formula can reach) returns a best approximation with a non-zero residual. Read the residual to see how far off the achievable maximum is.
Frequently asked questions
How do I find break-even units with this tool?
Set targetCell to your profit cell, leave targetValue at 0, and set inputCell to your units cell. The solver searches for the unit volume that makes profit exactly zero and reports it to six decimals. Round up to the next whole unit for a real-world target.
Why use Goal Seek instead of the break-even formula?
For a plain model, Fixed / (Price − VarCost) is faster by hand. Goal Seek wins once the P&L gets messy — tax, tiered fixed costs, commissions, step pricing — where the closed-form formula stops being a single clean expression. As long as the profit formula uses supported functions, Goal Seek solves it without you re-deriving the algebra.
My break-even is a fraction — is that wrong?
No, it's exact. Break-even is the precise point where profit is zero, and that's usually fractional (e.g. 346.15 units). Round up to the next whole unit for an achievable target — 346 units would still post a small loss.
Can I solve for a target profit, not just break-even?
Yes. Change targetValue from 0 to your profit goal (e.g. 10000). Everything else stays the same — the solver finds the units that produce that profit.
What if the model can never break even?
When price is at or below variable cost, contribution is non-positive and no volume covers fixed costs. The solver returns a best approximation with a residual roughly equal to your fixed cost and labels it not converged. That's the model telling you the unit economics don't work.
Does my profit formula need a particular shape?
It must be a formula (not a typed number), use supported functions (arithmetic, SUM, IF, POWER, ROUND, etc.), and reference single cells — no A1:A10 ranges, no SUMPRODUCT, no lookups. A standard (Units×Price) − (Units×Cost) − Fixed is fine.
Can I handle tiered or step-fixed costs?
Yes — IF is supported, so IF(units>500, 9000, 4500) works as a step-fixed cost. Just verify the converged units land on the side of the step your answer assumes, since the solver could approach from either side.
Do I need Excel installed?
No. The workbook is parsed in your browser by SheetJS and solved locally — no Excel, no add-in, no upload. Your P&L numbers never leave the machine.
Will it write the break-even units into my file?
No — it returns a text report. Read the break-even number and type it into your model. To then freeze recalculated results into static cells, run formula-to-value.
Why is my break-even different from the spreadsheet's display?
Usually stale caches: the solver recomputes only the profit formula and reads referenced cells (including subtotals) at their saved values. Open and re-save the workbook so every cached value is current, then re-run.
What tier and size limits apply?
Goal Seek is Developer-tier. Developer accounts get up to 500 MB files and unlimited rows. Free, Pro, and Pro-media accounts are blocked from this tool.
How can I check my model before running?
Use the formula explainer to confirm the profit formula only uses supported functions, and the dependency map to see which cells feed it — so you know which cached values matter.
Privacy first
Every JAD Excel tool runs entirely in your browser using SheetJS and ExcelJS. Your spreadsheets, formulas, and data never leave your device — verified by zero outbound network requests during processing.