Break-Even and Operating Leverage in Excel: A 10% Sales Drop, a 31% Profit Drop
Break-even units is one division. The useful part is what sits around it: margin of safety, operating leverage, and the target-profit formula. Here is all four on one business, where sales are 33% above break-even and profit is three times as sensitive.
The Division, and the Three Things Around It
Break-even is fixed costs divided by contribution per unit. That is the easy part and it is where most spreadsheets stop. The three figures that make it useful are margin of safety, operating leverage, and the units needed for a target profit.
Together they answer a different question: not "when do we stop losing money?" but "how much room do we have, and how fast does profit move when sales do?"
Try the break-even calculatorGet break-even units and revenue from fixed costs, price, and variable cost per unit.The business
Swipe sideways to compare columns.
| Name | Value |
|---|---|
| Price | $79.00 |
| VariableCost | $34.50 |
| FixedCosts | $186,000 |
| UnitsSold | 6,200 |
Margin of safety: how much room there is
Sales can fall 32.6% before this business stops making money. That is the single most useful number in the whole set, because it converts an abstract break-even point into a statement about how much bad news the business can absorb.
Swipe sideways to compare columns.
| Margin of safety | Reading |
|---|---|
| Below 10% | Fragile. A normal quarter of variation puts it into loss. |
| 10% to 25% | Workable, but a recession or a lost customer is dangerous. |
| 25% to 50% | Comfortable for most businesses. |
| Above 50% | Very safe, and often a sign of underinvestment in growth. |
Operating leverage: how fast profit moves
A degree of operating leverage of 3.07 means a 1% change in sales produces a 3.07% change in operating income. It cuts both ways and it is the number that surprises people in a downturn.
Swipe sideways to compare columns.
| Change in units sold | Units | Operating income | Change in profit |
|---|---|---|---|
| −20% | 4,960 | $34,720 | −61.4% |
| −10% | 5,580 | $62,310 | −30.7% |
| Base | 6,200 | $89,900 | — |
| +10% | 6,820 | $117,490 | +30.7% |
| +20% | 7,440 | $145,080 | +61.4% |
Units for a target profit
The after-tax version is the one to use when the target came from an owner rather than from a budget, because a target stated as "I want to clear $120,000" almost always means after tax.
More than one product
With several products there is no single break-even in units, because the answer depends on the mix. Compute a weighted average contribution margin instead.
The chart
- Build a small table of unit volumes from zero to about 150% of current sales, in ten steps.
- Three columns beside it: revenue as units times price, total cost as fixed plus units times variable cost, and profit as the difference.
- Insert a line chart on revenue and total cost. The crossing point is break-even.
- Add the profit line on a secondary axis, or as a separate chart, since it crosses zero at the same volume.
- A vertical line at actual sales makes the margin of safety visible as a distance rather than a percentage.
What break-even analysis assumes
- That costs split cleanly into fixed and variable. Most do not. Step costs, such as hiring a supervisor at a certain volume, break the straight line entirely.
- That fixed costs stay fixed across the whole range. They do not: doubling volume usually needs more space, more people, and more systems.
- That the price holds at every volume. Selling 50% more usually means discounting, which lowers the contribution margin exactly when volume rises.
- That variable cost per unit is constant. Volume discounts lower it and overtime raises it.
- That everything produced is sold. Break-even in units is a sales figure, not a production figure, and inventory build hides the difference.
- Nothing about cash. A business can pass break-even on the income statement and run out of money, because timing and working capital sit outside this model.
- Nothing about whether the fixed cost base is the right one. A lower break-even from cutting fixed costs may cost more in capability than it saves.
What is the difference between contribution margin and gross margin?
Contribution margin subtracts only variable costs, wherever they appear on the income statement. Gross margin subtracts production costs, whether fixed or variable. Sales commission is variable and not cost of goods; factory rent is production cost and not variable.
What is a good margin of safety?
Above 25% is comfortable for most businesses; below 10% means a normal quarter of variation can produce a loss. It should be read alongside operating leverage, since a thin margin of safety on a high-leverage cost base is considerably more dangerous than the percentage alone suggests.
Why does a 10% sales drop cut profit by 31%?
Because fixed costs do not fall with sales. The whole of the lost contribution comes straight out of profit. The degree of operating leverage, contribution divided by operating income, quantifies this: at 3.07, every 1% of sales movement is 3.07% of profit movement.
How do I find break-even for several products?
Use a weighted average contribution margin based on the sales mix, then divide fixed costs by it. The result is valid only at that mix, so state the mix alongside the answer and recompute when it shifts.
Should break-even units be rounded up or down?
Up, always. At 4,179.78 units the business is fractionally short of covering its fixed costs, so 4,180 is the first whole unit at which it breaks even. Use ROUNDUP rather than ROUND.
How do I include a target profit after tax?
Gross the target up before adding it to fixed costs: divide it by one minus the tax rate. A $120,000 after-tax target at 25% becomes $160,000 pre-tax, and the required volume rises from 6,877 to 7,776 units.
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