Break-Even Analysis in Excel: Formulas, Margin of Safety, Sales Mix, Sensitivity Table and Chart
How to do a break-even analysis in Excel: separate fixed and variable costs, calculate contribution margin, break-even units and revenue, target profit, margin of safety and operating leverage, handle a multi-product sales mix, build a sensitivity table and a break-even chart, with a small product business example.
Quick answer: break-even is the sales level where revenue exactly covers all costs, so profit is zero. In Excel you need three inputs (price per unit, variable cost per unit and fixed costs per period) and two formulas:
- Break-even units = Fixed costs ÷ (Price − Variable cost) →
=ROUNDUP(Fixed/(Price-Variable),0) - Break-even revenue = Fixed costs ÷ Contribution margin ratio →
=Fixed/((Price-Variable)/Price)
Example: a small candle business with $4,200 of monthly fixed costs, a $28 price and $9.80 of variable cost per candle has to sell 231 candles a month (about $6,462 in sales) to break even. Below you’ll extend that to target profit, margin of safety, operating leverage, a product mix, a sensitivity table and a chart. All numbers are illustrative.
Step 1: Sort costs into fixed and variable
| Cost | Type | Why |
|---|---|---|
| Wax, fragrance, jar, wick | Variable | Every candle needs them |
| Packaging and shipping supplies | Variable | Per order or per unit |
| Payment and marketplace fees | Variable | A percentage of each sale |
| Studio rent, insurance, software | Fixed | Same whether you sell 50 or 500 |
| Baseline marketing budget | Fixed | Planned per month |
| Owner’s salary | Fixed (if you pay yourself a set amount) | Include it, or break-even means working for free |
Costs that change with volume in steps (an extra part-timer once you pass 400 units a month) are fixed within a range. Note the range in your model and recalculate if you move past it. If you already record spending in a business expense tracker, add a Fixed/Variable column and summarize it with SUMIFS.
Step 2: Inputs sheet
| Name | Value | Note |
|---|---|---|
| Price | $28.00 | Average selling price after discounts |
| Variable | $9.80 | Materials $7.30 + packaging $1.10 + fees $1.40 |
| Fixed | $4,200 | Rent $900, software $120, insurance $80, marketing $600, owner pay $2,500 |
| TargetProfit | $2,000 | Monthly profit goal |
| ExpectedUnits | 300 | Your sales forecast |
Name each input cell (Formulas → Define Name) so the formulas below read like sentences.
Step 3: Core results
| Metric | Formula | Result |
|---|---|---|
| Contribution margin per unit | =Price-Variable |
$18.20 |
| Contribution margin ratio | =(Price-Variable)/Price |
65.0% |
| Break-even units | =ROUNDUP(Fixed/(Price-Variable),0) |
231 |
| Break-even revenue | =Fixed/((Price-Variable)/Price) |
$6,461.54 |
| Units for target profit | =ROUNDUP((Fixed+TargetProfit)/(Price-Variable),0) |
341 |
| Profit at expected units | =ExpectedUnits*(Price-Variable)-Fixed |
$1,260 |
| Margin of safety | =(ExpectedUnits-Fixed/(Price-Variable))/ExpectedUnits |
23.1% |
| Degree of operating leverage | =ExpectedUnits*(Price-Variable)/(ExpectedUnits*(Price-Variable)-Fixed) |
4.33 |
How to read them:
- Each candle contributes $18.20 toward fixed costs. Once 231 candles have covered the $4,200, every further candle adds $18.20 of profit.
- At the forecast of 300 candles, sales could fall about 23% before the business starts losing money.
- An operating leverage of 4.33 means a 10% change in sales moves profit by roughly 43%, in either direction. High fixed costs make profit more sensitive to volume.
Step 4: Sensitivity table (what if price or costs change?)
| Scenario | Change | Break-even units |
|---|---|---|
| Base case | — | 231 |
| Raise price to $30 | Contribution $20.20 | 208 |
| Materials up $1 | Contribution $17.20 | 245 |
| Add $500/month ads | Fixed $4,700 | 259 |
Build a two-way grid: prices across row 2 (for example $24 to $34), variable costs down column B ($8 to $12), and in each cell =ROUNDUP(Fixed/(C$2-$B3),0). Color cells green where break-even is below your expected units and red where it’s above. Excel’s Data → What-If Analysis → Data Table does the same thing using the formula in the corner cell. The grid answers questions like “if wax goes up a dollar, how much do I need to raise the price to keep the same break-even?”
Step 5: More than one product (sales mix)
Most businesses sell several products with different margins. Use the weighted average contribution margin:
| Product | Price | Variable cost | Contribution | Mix |
|---|---|---|---|---|
| Candle | $28 | $9.80 | $18.20 | 70% |
| Reed diffuser | $42 | $15.00 | $27.00 | 30% |
- Weighted contribution per unit:
=SUMPRODUCT(D2:D3,E2:E3)= $20.84 - Total break-even units:
=Fixed/SUMPRODUCT(D2:D3,E2:E3)≈ 202 units, split as roughly 141 candles and 61 diffusers - Break-even revenue ≈ $6,489
The result only holds while the mix holds. If customers shift toward the lower-margin product, break-even rises, so track the actual mix from your sales data each month. The guide to pricing handmade products covers how to set prices that leave enough contribution in the first place.
Step 6: Break-even chart
- In a helper table, list units from 0 to 450 in steps of 25.
- Revenue column:
=A20*Price. Total cost column:=Fixed+A20*Variable. Fixed cost column:=Fixed. - Select the three columns and insert a Scatter with Straight Lines chart, so the horizontal axis is to scale.
- The point where revenue crosses total cost is break-even. Shade the area to the right as the profit zone, or simply add a vertical line at 231 units.
A chart makes the conversation easier with a partner, lender or investor: they see at a glance how far current sales are from the line.
Cash break-even vs accounting break-even
The analysis above uses costs as they appear in the profit and loss statement. Two adjustments give a cash view:
- Depreciation is a cost but not a monthly cash payment, so it can come out of fixed costs for a cash break-even.
- Loan principal repayments are cash out but not expenses, so they should be added.
If cash is tight, run both versions and check the result against your monthly cash plan. Your profit and loss statement template is the best source for the fixed cost total.
Common mistakes
- Leaving out the owner’s pay: break-even then means a business that can’t pay its owner.
- Using list price instead of average price after discounts and promotions.
- Forgetting percentage fees (payment processing, marketplace commissions) in variable costs.
- Not rounding units up: you can’t sell 230.8 candles.
- Treating the result as permanent: recalculate when prices, suppliers or fixed costs change.
Frequently asked questions
How is break-even different from payback period?
Break-even answers “how much must I sell each period to cover costs?” Payback answers “how long until I recover the money I invested up front?” Both matter when starting a business or launching a product.
Can a service business use break-even analysis?
Yes. Use billable hours or projects as the unit, your hourly rate as price, and any per-job costs (subcontractors, software per seat, travel) as variable cost.
Does this work in Google Sheets?
Yes. ROUNDUP and SUMPRODUCT work the same. Google Sheets has no Data Table tool, so use the direct-formula grid from Step 4.
For a quick margin check on single products, see the profit margin calculator in Excel. More guides are in Small business finance.