Break-even units and target margin: a worked example
Calculate unit contribution, break-even volume, forecast profit, and a price from target gross margin using a worked example with explicit cost assumptions.
Break-even units equal fixed costs divided by unit contribution, where unit contribution is selling price less variable unit cost. The result is meaningful only when unit contribution is positive and the cost assumptions apply to the same planning period. The Small Business Administration uses this relationship in its break-even guidance.
How do you calculate break-even units in a spreadsheet?
Consider a fictional product priced at $80 with $50 of variable cost per unit and $12,000 of fixed costs for the period. Contribution is 80-50, or $30 per unit. Break-even volume is 12000/30, or 400 units.
| Item | Amount |
|---|---|
| Selling price per unit | $80 |
| Variable cost per unit | $50 |
| Contribution per unit | $30 |
| Fixed cost per period | $12,000 |
| Break-even units | 400 |
With price in B11, variable cost in C11, and fixed cost in D11, calculate contribution in F11 as =B11-C11. Then use =IF(F11<=0,"",ROUNDUP(D11/F11,0)) for whole-unit break-even volume. The break-even workbook keeps those steps visible.
If the quotient is 400.2, 400 whole units do not cover fixed cost; rounding up gives 401. When contribution is zero or negative, the model shows no finite break-even quantity rather than dividing by zero or returning a misleading negative volume.
What profit does a planned volume of 500 units produce?
Forecast operating contribution after these fixed costs is 500*30-12000, or $3,000. Revenue is $40,000, variable cost is $25,000, and fixed cost is $12,000. Those three figures reconcile to the same profit.
This is a single-product model. If several products share fixed costs, do not assign the entire fixed cost to each product and add their break-even answers. Use a justified allocation or model the sales mix and its weighted contribution separately.
How do you set price from a target gross margin?
Gross margin divides profit by selling price. For a $60 unit cost and a 40 percent target margin, price is 60/(1-0.40), or $100. The $40 profit is 40 percent of price and approximately 66.67 percent of cost.
Applying a 40 percent markup to the same $60 cost gives an $84 price. Its margin is 24/84, approximately 28.57 percent. The different denominators explain why markup and margin cannot be substituted for each other. The pricing and margin workbook shows both measures.
Which assumptions can change a break-even result?
Check whether payment fees, shipping, waste, commissions, and returns belong in variable cost. Check whether labor and capacity costs remain fixed over the modeled volume. Use matching periods for fixed costs and planned sales.
A calculation can be arithmetically correct while its cost classification is unsuitable. Treat the worksheet as a documented scenario and replace its fictional inputs with costs you can support.