📎 This article includes 1 downloadable practice file ↓
“How many units do we need to sell to break even?” “What price gets us ₹1 lakh profit?” “How big a loan fits a ₹15,000 EMI?” These are what-if questions, and Excel has three built-in tools for them under Data › What-If Analysis: Goal Seek, Data Tables and Scenario Manager.
In this article
The example model
| Cell | Input | Value |
|---|---|---|
| B2 | Price per unit | ₹650 |
| B3 | Variable cost per unit | ₹410 |
| B4 | Fixed cost per month | ₹1,80,000 |
| B5 | Units sold | 900 |
Profit (B7): =(B2 - B3) * B5 - B4 → ₹36,000Goal Seek: one input, one target
Data › What-If Analysis › Goal Seek:
- Set cell: B7 (profit)
- To value: 0
- By changing cell: B5 (units)
Excel answers 750 units, the break-even point. Change “To value” to 100000 and it finds about 1,167 units. Point “By changing” at B2 instead and it finds the price needed at the current 900 units: about ₹721.
The rules: the Set cell must be a formula, the changing cell must be a typed value (not a formula), and the formula must depend on it.
The formula way (updates by itself)
For simple models you can solve the equation once and never run Goal Seek again:
Break-even units: =ROUNDUP(B4 / (B2 - B3), 0)
Units for target profit: =ROUNDUP((B4 + Target) / (B2 - B3), 0)
Price for target profit: =B3 + (B4 + Target) / B5
Margin of safety: =(B5 - B4/(B2-B3)) / B5 → 16.7%
Affordable loan: =PV(rate/12, years*12, -EMI)Use Goal Seek when the model is too tangled to rearrange: tax slabs, tiered commissions, multi-sheet budgets.
One-variable Data Table: many answers at once
Profit for units from 600 to 1,200:
- Type 600, 700 … 1200 down E3:E9.
- In F2 (one row above, one column right), type
=B7. - Select E2:F9, What-If Analysis › Data Table, Column input cell: B5.
Excel fills profit for each unit level. It’s live: change the price and the whole table recalculates.
Two-variable Data Table: a sensitivity grid
Price across the top row, units down the first column, the profit formula in the corner cell. Select the grid, Data Table, Row input = B2, Column input = B5. Add conditional formatting (red below zero) and you have the classic “where do we make money” chart that management likes.
| Units ↓ / Price → | 600 | 650 | 700 |
|---|---|---|---|
| 700 | -47,000 | -12,000 | 23,000 |
| 900 | -9,000 | 36,000 | 81,000 |
| 1,100 | 29,000 | 84,000 | 1,39,000 |
Scenario Manager
For named sets of inputs (“Best case”, “Expected”, “Worst case”), use Scenario Manager: add each scenario with its values, then Summary builds a side-by-side report. It suits board packs; for daily use a small inputs table plus CHOOSE or XLOOKUP on a scenario drop-down is easier to maintain.
Where people go wrong
| Problem | Cause |
|---|---|
| “Cell must contain a value” in Goal Seek | The changing cell has a formula; point it at the raw input |
| Data Table shows the same number everywhere | Row and column input cells swapped, or input cell on another sheet (it must be on the same sheet) |
| Goal Seek answer like 749.9998 | It stops when close enough; round the result or raise precision in File › Options › Formulas |
| Need several inputs to change together | That’s a Solver job |
Practice
Download the practice workbook below. Answer each question by formula, then confirm it with Goal Seek; build a Data Table as a bonus. Each task has an automatic ✓ check, and the Answers sheet has a working formula if you get stuck.
Next: Excel Solver.
Test formulas dynamically in our web formula laboratory or download the companion Excel workbook.
📎 Practice files for this article
- 📗Goal Seek practice workbookA small business and a loan: break-even, target profit, price, margin of safety and EMI questions, each answerable by Goal Seek and by formula.⬇ XLSX · 13 KB
Free to use for learning. Files with macros (.bas) are plain text — import them with Alt+F11 → File → Import File, and always test on a copy.
Stuck on a step? Ask a question and the AI answers using this article.