Break-Even Analysis in Excel, Step by Step

A break-even analysis in Excel finds how many units you must sell to cover all your costs, so profit is exactly zero. You need three numbers (fixed costs, price and variable cost per unit) and one formula: fixed costs divided by price minus variable cost. This guide builds a small, reusable model you can use for a side business, a product launch or a finance interview task.

What is break-even analysis?#

The break-even point is the sales level where total revenue equals total costs. Below it you lose money; above it you make a profit. Three terms come up again and again:

• Fixed costs stay the same whatever you sell, such as rent, salaries and software.
• Variable cost per unit is what each extra unit costs to make or deliver, such as materials and packaging.
• Contribution per unit is the price minus the variable cost. It's what each sale contributes towards fixed costs.

The formula follows from these:

Break-even units = Fixed costs / (Price per unit - Variable cost per unit)

Step 1: Set up the inputs#

Put each input in its own labelled cell, so you can change one number and see the effect. Never type numbers straight into the formulas.

Give the input cells a light fill color so anyone using the sheet knows these are the cells to edit. Everything else will be formulas.

Step 2: Calculate break-even units#

Break-even units are fixed costs divided by contribution per unit. In B6, type:

=B1/(B2-B3)

The result is 800. Each unit contributes 25 − 10 = 15 towards fixed costs, and 12,000 ÷ 15 = 800.

The brackets matter. Without them, =B1/B2-B3 divides 12,000 by 25 first and then subtracts 10, giving 470, which is wrong. Excel follows the order of operations: division before subtraction, unless brackets say otherwise. If formulas like this are new to you, the first formulas lesson covers cell references and operators step by step.

When the answer isn't a whole number, round it up. Selling 642.86 cakes isn't possible, and 642 would still be a loss:

=ROUNDUP(B1/(B2-B3),0)

Step 3: Calculate break-even revenue and margin of safety#

Break-even revenue is the sales value at the break-even point. In B7:

=B6*B2

That gives 20,000 (800 × 25). You can also get it in one step by dividing fixed costs by the contribution margin ratio, the share of each sale left after variable costs: =B1/((B2-B3)/B2). Here that's 12,000 ÷ 0.6 = 20,000.

The margin of safety shows how far expected sales can fall before you make a loss. In B8:

=(B4-B6)/B4

The result is 0.2, or 20% once formatted as a percentage. Sales can drop by a fifth before the business loses money. The guide to the multiply formula in Excel covers more ways to multiply cells and ranges.

Step 4: Build a profit table with absolute references#

A profit table shows the profit at several sales levels, so you can see the break-even point in context. Put unit levels in A11:A15 and this formula in B11:

=A11*($B$2-$B$3)-$B$1

Fill it down with Ctrl+D (Mac: ⌘D). The $ signs lock the price, variable cost and fixed costs, so every row uses the same inputs. A11 has no $, so it moves down to A12, A13 and so on.

Revenue is =A11*$B$2 and total cost is =$B$1+A11*$B$3. Profit rises by 3,000 for every 200 extra units, which is 200 × 15. Press F4 (Mac: ⌘T) while the cursor is on a reference to add the $ signs. Practise relative and absolute references if you're not sure when to lock a cell.

Step 5: Find the units for a target profit#

Break-even is profit of zero, but you can use the same idea for any target. Add the target profit to the fixed costs:

=(B1+B9)/(B2-B3)

If B9 holds a target profit of 4,500, the result is 1,100 units: (12,000 + 4,500) ÷ 15.

You can also let Excel search for the answer with Goal Seek. Choose Data > What-If Analysis > Goal Seek, set a profit cell to 0 (or to your target) by changing a units cell, and click OK. Goal Seek is handy when the model is too complex for a simple formula, such as when prices drop at higher volumes. For tiered prices, the guide to a price list with discounts shows how to build them.

Step 6: Chart the break-even point#

A line chart makes the break-even point easy to explain. Select the Units, Revenue and Total cost columns of the profit table and choose Insert > Line or Area Chart > Line (or a scatter chart with lines). The two lines cross at 800 units and 20,000. That crossing point is the break-even point.

Add more rows to the table, every 100 units for example, to make the lines smoother and the crossing easier to read.

Common break-even mistakes#

These are the slips that most often give a wrong answer:

1. Missing brackets. =B1/B2-B3 is not the same as =B1/(B2-B3).
2. Mixing periods. Monthly fixed costs need monthly sales. Don't divide yearly rent by a monthly contribution.
3. Putting fixed costs into variable cost. Rent doesn't grow with each unit, so it belongs in fixed costs only.
4. Rounding down. Break-even units should be rounded up with ROUNDUP, not ROUND or INT.
5. Price below variable cost. The result goes negative, and if price equals cost you get #DIV/0!. Every sale must contribute something.

Once the break-even model works, link it to a monthly plan. The guide to a simple cash flow forecast in Excel shows when the cash actually comes in, and the invoice with formulas guide uses the same price and cost cells.

Practise it#

Try the bakery exercise on this page: one formula with brackets and ROUNDUP. Then practise your first formulas in a live spreadsheet to get confident with operators and brackets, and practise absolute references so your profit tables fill down correctly every time.