# 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.

---

- **Canonical URL:** https://dothecalculation.com/blog/templates/excel-break-even-and-margin-of-safety
- **Category:** Templates
- **Author:** Do The Calculation Team
- **Published:** 2026-08-03
- **Reading time:** 10 min read
- **Publisher:** Do The Calculation (https://dothecalculation.com)
- **Methodology:** https://dothecalculation.com/methodology

---

## 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?"

Tool: [Try the break-even calculator](https://dothecalculation.com/calculators/break-even-calculator) — Get break-even units and revenue from fixed costs, price, and variable cost per unit.

## The business

**Inputs, in named cells**
| Name | Value |
| --- | --- |
| Price | $79.00 |
| VariableCost | $34.50 |
| FixedCosts | $186,000 |
| UnitsSold | 6,200 |

**The four core figures**

```
Contribution = Price − VariableCost  ·  CM ratio = Contribution ÷ Price  ·  BE units = FixedCosts ÷ Contribution  ·  BE revenue = FixedCosts ÷ CM ratio
```
- Contribution: 79.00 − 34.50 = $44.50 per unit
- CM ratio: 44.50 ÷ 79.00 = 56.33%
- Break-even units: 186,000 ÷ 44.50 = 4,180 units
- Break-even revenue: 186,000 ÷ 0.56329 = $330,202

> **Use ROUNDUP on the unit figure** — The exact answer is 4,179.78 units. You cannot sell 0.78 of a unit, and rounding down leaves the business fractionally short of break-even. =ROUNDUP(FixedCosts/Contribution, 0) is the correct formula and it matters at the margin.

## Margin of safety: how much room there is

**Margin of safety**

```
In units: Actual − BreakEven    ·    As a percentage: (Actual − BreakEven) ÷ Actual
```
- 6,200 − 4,180 = 2,020 units of room
- 2,020 ÷ 6,200 = 32.6%
- In revenue: $489,800 − $330,202 = $159,598

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.

**Reading the margin of safety**
| 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

**Degree of operating leverage**

```
DOL = Total contribution ÷ Operating income
```
- Total contribution: 6,200 × 44.50 = $275,900
- Operating income: 275,900 − 186,000 = $89,900
- DOL: 275,900 ÷ 89,900 = 3.07

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.

**What leverage does at this business**
| 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% |

> **Leverage rises as you approach break-even** — At 4,500 units the degree of operating leverage is 14.05, so a 5% sales slip cuts profit by 70%. The closer a business sits to its break-even point, the more violently profit responds to small changes. High leverage and a thin margin of safety together are the dangerous combination.

## Units for a target profit

**Target profit, before and after tax**

```
Before tax: (FixedCosts + Target) ÷ Contribution    ·    After tax: (FixedCosts + Target ÷ (1 − TaxRate)) ÷ Contribution
```
- For $120,000 of operating profit: (186,000 + 120,000) ÷ 44.50 = 6,877 units
- For $120,000 after tax at 25%: (186,000 + 160,000) ÷ 44.50 = 7,776 units
- The tax adjustment adds 900 units, which is 13% more sales for the same take-home result.

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.

**Weighted average contribution**

```
=SUMPRODUCT(Contributions, MixPercentages)
```
- Mix percentages are each product's share of unit sales, summing to 1.
- Break-even units then divides fixed costs by that weighted figure.
- The answer is only valid at that mix. A shift toward a lower-margin product raises the break-even point without anything else changing.

> **The mix assumption is the weak point** — A multi-product break-even is a statement about a specific sales mix. Report it with the mix stated alongside, and rerun it whenever the mix shifts materially. A business can hit its unit target, miss its profit target, and have nothing wrong with the model.

## 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.

---

_Source: [Do The Calculation](https://dothecalculation.com/blog/templates/excel-break-even-and-margin-of-safety). Quote freely with attribution and a link to this page._