Break-Even Analysis in Excel: Formulas, Goal Seek, and a Data Table
Build a break-even model that answers four questions at once: how many units, at what revenue, what happens if costs rise, and what price you would need. Uses only built-in Excel features.
Break-Even Analysis in Excel
Break-even is the volume at which contribution exactly covers fixed costs. The formula is one division. The reason to build it in a spreadsheet rather than on paper is that the interesting questions are the ones that come after: what if the supplier raises prices, what if I discount, what price do I need to break even at half this volume.
Try the break-even calculatorGet break-even units, revenue, and contribution margin without building the model first.Step 1: the model
Swipe sideways to compare columns.
| Cell | Label | Value or formula |
|---|---|---|
| B1 | Price per unit | 48.00 |
| B2 | Variable cost per unit | 29.50 |
| B3 | Fixed costs per month | 12500 |
| B5 | Contribution per unit | =B1-B2 |
| B6 | Contribution margin % | =B5/B1 |
| B7 | Break-even units | =B3/B5 |
| B8 | Break-even revenue | =B7*B1 |
| B9 | Units to hit a target profit | =(B3+B11)/B5 |
| B11 | Target monthly profit | 4000 |
Step 2: getting fixed and variable right
The model is only as good as the split, and this is where most break-even analysis goes wrong. Variable costs change with each unit sold. Fixed costs do not change within the relevant range.
Swipe sideways to compare columns.
| Cost | Usually | Watch for |
|---|---|---|
| Materials, cost of goods | Variable | Volume discounts make it step down |
| Payment processing | Variable | Roughly 3% of price, easy to forget |
| Outbound shipping | Variable | Free-shipping thresholds distort it |
| Sales commission | Variable | Only where it is a percentage of the sale |
| Rent, insurance, software | Fixed | Steps up when you outgrow the space |
| Salaried staff | Fixed | Becomes semi-variable with overtime |
| Hourly production labour | Variable | Fixed if the hours are guaranteed |
Semi-variable costs, such as a utility bill with a standing charge plus usage, should be split across both lines rather than forced into one. Putting the whole bill in fixed overstates break-even; putting it in variable understates it.
Step 3: Goal Seek for the reverse questions
Goal Seek answers "what input produces this output" when you cannot rearrange the formula quickly. It lives under Data, What-If Analysis, Goal Seek.
Swipe sideways to compare columns.
| Question | Set cell | To value | By changing |
|---|---|---|---|
| What price breaks even at 400 units? | B7 | 400 | B1 |
| What fixed cost can I carry at 500 units? | B7 | 500 | B3 |
| What variable cost do I need at 550 units? | B7 | 550 | B2 |
The first returns a price of $60.75. That is the more useful form of the question: not "what is my break-even" but "if I can only realistically sell 400, what would I have to charge", which is often the point at which a product idea gets abandoned or repriced.
Step 4: a two-way data table for sensitivity
Goal Seek answers one question at a time. A data table answers a grid of them at once, showing break-even units across a range of prices and variable costs together.
- Put the formula =B7 in a corner cell, say D14.
- Across row 14 to the right of it, enter price values: 44, 46, 48, 50, 52.
- Down column D beneath it, enter variable cost values: 27, 28, 29.50, 31, 32.
- Select the whole block from D14 to the bottom right.
- Data, What-If Analysis, Data Table. Row input cell B1, column input cell B2.
Swipe sideways to compare columns.
| Var. cost \ Price | $44 | $46 | $48 | $50 | $52 |
|---|---|---|---|---|---|
| $27.00 | 736 | 659 | 596 | 544 | 500 |
| $28.00 | 782 | 695 | 625 | 568 | 521 |
| $29.50 | 862 | 758 | 676 | 609 | 554 |
| $31.00 | 962 | 834 | 735 | 657 | 595 |
| $32.00 | 1,042 | 893 | 782 | 695 | 625 |
The grid makes the asymmetry obvious. A $2 price rise cuts break-even by about 70 units. A $1.50 increase in unit cost adds about 50. Because contribution is the denominator, small moves at either end produce large moves in required volume, and that sensitivity is the real output of the exercise.
Step 5: the break-even chart
Build a small table of units from 0 to roughly twice break-even, in steps. Three columns: total revenue =units*$B$1, total cost =$B$3+units*$B$2, and profit =revenue-cost. Select the units column and the two totals, then insert a line chart.
The crossing point is break-even. The vertical distance between the lines at your expected volume is your profit, and the angle between them is your contribution margin, which is why a shallow angle means the business is fragile to a small drop in volume.
Step 6: margin of safety
This is the figure worth putting on a dashboard rather than break-even itself. A margin of safety under about 20% means a normal bad quarter takes you into a loss, and that is a warning you can act on before it happens.
Try the profit margin calculatorCheck the contribution margin behind your break-even and see what a discount does to it.What is the break-even formula?
Break-even units = fixed costs divided by contribution per unit, where contribution is price minus variable cost per unit. Break-even revenue is that unit figure multiplied by the price.
How do I use Goal Seek for break-even?
Data, What-If Analysis, Goal Seek. Set the break-even cell to the volume you can realistically sell, and change the price cell. Excel solves for the price that makes it true. Copy the model first, because Goal Seek overwrites the input.
Is payment processing a fixed or variable cost?
Variable. It scales with each sale, usually around 3% of the price, and leaving it out of variable costs understates break-even by a meaningful margin on low-margin products.
What is a good margin of safety?
There is no universal figure, but below about 20% a routine drop in sales pushes you into a loss. Read it alongside how volatile your demand actually is rather than against a benchmark.
Written by
Do The Calculation Team
Do The Calculation
Do The Calculation is built by a small team of data analysts and spreadsheet developers. Where a guide depends on a published formula, standard, or government rule, the calculator it links to names that source directly so you can check the number yourself.
About the team