Break-Even Analysis Worksheet

About this template

Where each product covers its costs

Three sheets: ‘Dashboard’ · ‘Input data’ · ‘Usage guide’. The analysis is on ‘Input data’, one row per product or service. Fourteen columns run No. · Product/service · Division · Units sold · Unit price · Unit variable cost · Unit contribution · Contribution margin (%) · Revenue · Variable cost total · Contribution total · Allocated fixed cost · Operating P/L · Break-even units, with seven sample lines in rows 2-8.

Four values are yours to enter

1. Units sold (column D), Unit price (column E) and Unit variable cost (column F). Variable cost takes only what grows when you sell one more — materials, subcontracted processing, commission, shipping.
2. Allocated fixed cost (column L): rent, permanent payroll, depreciation, split across products. Fix the basis you split on — revenue share, work hours — and keep using it.
3. Division (column C), which is what Summary by division groups on.

How the formulas run

• Unit contribution =E2-F2, contribution margin =IFERROR(ROUND(G2/E2*100,1),0). Row 2 is 180,000 − 108,000 = 72,000, a margin of 40.
• Revenue =D2*E2 · Variable cost total =D2*F2 · Contribution total =I2-J2 · Operating P/L =K2-L2.
• Break-even units =IFERROR(ROUNDUP(L2/G2,0),0) — allocated fixed cost over unit contribution, rounded up. The smart inventory terminal needs 945 units, label and ribbon supplies 12,000, the annual maintenance contract 89.
• Break-even revenue is allocated fixed cost divided by the margin: a column H reading of 40 means dividing by 0.4.

Reading the products by margin

By contribution margin the sample runs annual maintenance contract 60 · label and ribbon supplies 54.7 · user training 42.2 · smart inventory terminal 40 · field terminal cradle 37.5 · barcode scanner 36.5 · installation and commissioning 31.7. The biggest sellers and the best margins are not the same products. Installation and commissioning is second largest by revenue at 252,000,000 but last on margin, so pushing resources at it on revenue alone will not lift profit by as much.

Sensitivity is tested by changing a cell

There is no scenario sheet. Everything is chained, so changing one unit price or unit variable cost recalculates contribution, operating P/L and break-even units together. Cut the smart inventory terminal’s price by 5%, from 180,000 to 171,000, and unit contribution falls from 72,000 to 63,000 while break-even units rise from 945 to 1,080 — a 5% discount paid for with 14% more volume. A 10% jump in materials is tested the same way. Work on a copy.

The trend table is updated by hand

‘Revenue by quarter (KRW m)’ holds Q1 118 · Q2 132 · Q3 127 · Q4 156. The four value rows are typed in; only Total 533 and Average 133.25 are formulas. Note your break-even revenue alongside and it is obvious which quarters cleared it. The Operating P/L total, 365,700,000, is already net of allocated fixed cost — but if company-wide overhead has not been allocated, it is not the profit of the business.

What goes wrong

• Slipping a fixed cost into the variable cost cell — unit contribution shrinks and break-even units come out higher than they are.
• Asking for break-even units on a product whose unit contribution is 0 or less. No volume covers the fixed cost; price and variable cost make-up are what need revisiting.
• Changing the allocation basis every quarter. Operating P/L then moves for reasons that have nothing to do with the product.
• Rounding break-even units down. At 944 instead of 944.4 you are still making a loss — which is why the formula uses ROUNDUP.