Free Excel template
Free Excel break-even analysis template
For small business owners and anyone pricing a product who needs to know how many units they must sell each month to cover costs. Enter the selling price, variable costs per unit and monthly fixed costs; the template calculates the contribution margin, break-even units and revenue, the units needed for a profit target, your margin of safety, and a table of profit or loss at different volumes.
Preview and try it
This is the template's sheet, calculated by the same engine as the Cellwise app. Change any number to see the formulas react before you download.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Break-even analysis | |||||
| 2 | Per-unit figures | Monthly fixed costs | ||||
| 3 | Selling price per unit | 48 | Rent | 3200 | ||
| 4 | Materials per unit | 14.5 | Salaries (fixed staff) | 9500 | ||
| 5 | Direct labor per unit | 6 | Insurance | 420 | ||
| 6 | Packaging and shipping per unit | 3.25 | Software and subscriptions | 310 | ||
| 7 | Payment fees (% of price) | 0.03 | Marketing (planned) | 1800 | ||
| 8 | Variable cost per unit | 25.19 | Loan payment | 650 | ||
| 9 | Contribution margin per unit | 22.81 | Utilities and phone | 380 | ||
| 10 | Contribution margin ratio | 0.47520833333 | Total fixed costs | 16260 | ||
| 11 | ||||||
| 12 | Break-even units per month | 713 | Result | |||
| 13 | Break-even revenue | 34224 | Short of break-even by 13 units | |||
| 14 | Target monthly profit | 5000 | ||||
| 15 | Units for target profit | 933 | Table starts at (units) | 400 | ||
| 16 | Expected units this month | 700 | Table step (units) | 100 | ||
| 17 | Profit at expected units | -293 | ||||
| 18 | Margin of safety | -0.018571428571 | ||||
| 19 | ||||||
| 20 | Profit at different sales volumes |
How the formulas work
Contribution margin per unit
Price minus the variable cost of one unit: what each sale contributes toward fixed costs and then profit. Variable cost in B8 adds materials, labor and packaging and the payment fee, which is a percentage of the price (B3*B7). Here each $48 sale leaves $22.81.
Break-even units, rounded up
Fixed costs divided by the contribution margin. ROUNDUP matters: $16,260 / $22.81 is 712.8, and selling 712 units still leaves a small loss, so the answer is 713. The IF returns 0 when the price does not cover variable cost, because then no volume breaks even.
Units needed for a profit target
Treat the profit you want as one more fixed cost and divide by the contribution margin. To make $5,000 a month this business needs 933 units, a third more than it expects to sell, which tells you to raise the price, cut a cost or lower the target.
Margin of safety
How far expected sales can fall before you make a loss, as a share of expected sales. A negative number means you are already below break-even: at 700 units the sample is 13 units short, and D13 says so in words.
Profit at each volume
Revenue minus variable costs minus fixed costs, for each row of units. Change the first row and the step in E15 and E16 to zoom in around break-even, and select A21:A31 with E21:E31 and use Insert > Line chart for a break-even chart.
Make it yours
- Test a price change: raise B3 from 48 to 52 and watch break-even fall from 713 to 610 units. Price usually moves break-even more than small cost cuts.
- Only put costs that stay the same whatever you sell in the fixed list. If a cost grows with each sale (commission, shipping, card fees), it belongs in the per-unit section.
- Selling several products? Use a weighted average price and variable cost based on your expected sales mix, or copy the sheet per product and split fixed costs between them.
- Color losses red in the table: select E22:E31, Home > Conditional Formatting > Highlight Cells Rules > Less Than, and type 0.
FAQ
What is the break-even formula in Excel?
Break-even units = fixed costs / (price per unit - variable cost per unit). In Excel: =ROUNDUP(fixed/(price-variable),0). Break-even revenue is those units times the price, or fixed costs divided by the contribution margin ratio.
What is the contribution margin ratio?
The share of each sales dollar left after variable costs: contribution margin per unit divided by price. At 47.5%, every $100 of sales contributes $47.52 toward fixed costs and profit.
Should loan payments count as fixed costs?
For a cash break-even, yes, because the payment has to be made every month. For an accounting break-even, only the interest is an expense. Pick one approach and say which on the sheet.
Functions used
Related guides
- How to round to the nearest 5, 10 or 100 in Excel
- How to calculate percentage in Excel
- Absolute vs relative cell references in Excel
- How to calculate percent change in Excel
More free templates
- Expense report templateFor employees who need to claim back business costs, and for the managers and bookkeepers who check those claims.Free .xlsx
- Purchase order templateFor small businesses, office managers and buyers who send purchase orders to suppliers and want to track what actually arrives.Free .xlsx
- Accounts receivable aging report templateFor owners, bookkeepers and credit controllers who invoice customers on terms and need to see who owes what and for how long.Free .xlsx
- Weekly employee shift schedule templateFor managers of shops, cafes, restaurants and small teams who build the rota in a spreadsheet.Free .xlsx
Updated