Break-Even Analysis in Excel (Formula, Chart and Goal Seek)

What you will build

Brightline Trading Co. is launching a gift box that sells for $45.00. Each box costs $18.00 to make, pack, ship and get paid for, and the business has $10,850.00 of fixed costs a month. How many boxes does it need to sell each month to break even? One sheet answers that and the follow-up questions:

Every formula works in Excel 2010 and later, Microsoft 365 and Excel for the web. Goal Seek is on the Data tab in all desktop versions.

The break-even formula

Break-even is the sales volume where total revenue equals total cost, so profit is exactly zero. Every unit you sell brings in its price and costs its variable cost. The difference is the contribution margin: the amount each sale contributes toward the fixed costs. Once enough units have covered the fixed costs, each extra unit is profit.

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

The hardest part is not the formula, it is classifying the costs. Your monthly profit and loss statement is the best starting point: cost of goods sold and payment fees are usually variable, while rent, salaries, insurance and software are usually fixed.

Step 1: Enter the price, variable costs and fixed costs

1. In A2:B2 enter the headers Price and costs (per unit) and Amount.
2. In A3:B6 enter the selling price and the variable costs: Selling price 45.00, Materials 10.75, Packaging 2.40, Shipping 3.50.
3. Card fees are 3% of the price, so in B7 enter =ROUND(B3*3%,2). It returns 1.35.
4. In B8 enter =SUM(B4:B7) for the total variable cost: $18.00.
5. In A10:B16 list the monthly fixed costs: Rent 2,400.00, Wages 6,200.00, Insurance 350.00, Software 300.00, Marketing 1,200.00 and Utilities 400.00. In B17 enter =SUM(B11:B16): $10,850.00.

If you track spending in an expense tracker, average the last three to six months of each fixed cost rather than using a single month, so one unusual bill does not skew the result.

Step 2: Calculate the contribution margin

In D3 type Contribution margin / unit and in E3 enter:

=B3-B8

Then in D4 type Contribution margin ratio and in E4 enter =E3/B3. Format E4 as a percentage.

Step 3: Find the break-even point in units and dollars

1. In E5 (Break-even units (exact)) enter =B17/E3. It returns 401.85.
2. You cannot sell 0.85 of a box, so in E6 (Break-even units) round up:
   Copy

   =ROUNDUP(E5,0)

3. In E7 (Break-even sales) enter =B17/E4, fixed costs divided by the contribution margin ratio. It returns $18,083.33.

Why ROUNDUP and not ROUND? ROUND would give 402 here, but with a different set of numbers it could round down and leave you short. Selling 401 boxes gives 401 x $27.00 = $10,827.00 of contribution, a loss of $23.00. Selling 402 gives $10,854.00, a profit of $4.00. Always round a break-even point up.

Step 4: Add a target profit and the margin of safety

Breaking even is not the goal. To find the volume for a target profit, add the profit to the fixed costs and divide by the contribution margin again:

The margin of safety tells you how far sales can fall from the plan before the business makes a loss. With 550 planned units and a break-even point of 401.85, sales can drop by 26.9% and still cover every fixed cost. A low margin of safety, say under 10%, means a slow month puts you in the red.

Step 5: Build the break-even chart

1. In G2:J2 enter the headers Units, Revenue, Total Cost and Profit.
2. In G3:G11 enter 0, 100, 200 and so on up to 800.
3. In H3 enter =G3*$B$3, in I3 enter =$B$17+G3*$B$8 and in J3 enter =H3-I3. Copy them down to row 11.
4. Select G2:I11 and choose Insert > Charts > Scatter with Straight Lines. Add the axis titles Units sold per month and Dollars.

A scatter chart is the right type here because the horizontal axis is a number (units), not a category. Total cost starts at $10,850.00, the fixed costs, even when you sell nothing. Revenue starts at zero and rises faster, by $45.00 a unit against $18.00. The lines cross at the break-even point: 401.85 units and $18,083.33. The Profit column shows the same story in numbers: a loss of 50 at 400 units and a profit of 2,650 at 500.

Want the chart to compare actual sales with the break-even target month by month? A combo chart for actual versus target does exactly that.

Step 6: Check the answer with Goal Seek

Goal Seek works backward: you tell Excel the result you want and which input it may change, and it finds the value. It is a good independent check of the formula.

1. Click E11, the planned profit.
2. Go to Data > What-If Analysis > Goal Seek.
3. Set cell E11, To value 0, By changing cell $E$10, then click OK.
4. Goal Seek changes E10 to 401.85, the same as the formula in E5. Click Cancel (or press Ctrl+Z after OK) to put the plan of 550 units back.

For a permanent check, E13 calculates =ROUND(E5*E3-B17,2), the profit at the exact break-even point, and F13 shows =IF(E13=0,"Breaks even","Check"). It should read 0.00 and Breaks even.

Break-even for cash, debt and several products

Two refinements matter in real businesses. First, the break-even above is an accounting break-even: it covers expenses, including non-cash ones such as depreciation, but not loan principal, which is not an expense. If the business is repaying a loan, add the principal part of the monthly payment to fixed costs for a cash break-even, and treat the interest as a fixed cost either way. The loan amortization schedule splits each payment for you, and the result belongs in your cash flow forecast.

Second, with several products, use a weighted-average contribution margin based on your expected sales mix: multiply each product's contribution margin by its share of units sold and add them up, exactly the weighted average with SUMPRODUCT pattern. The break-even point is then in "average units", which you split back into products using the same mix.

Common mistakes and quick fixes

Frequently asked questions

Final checklist

• Every cost is classified as variable (per unit) or fixed (per month).
• Percentage-based costs such as card fees are formulas based on the price.
• The contribution margin is positive.
• Break-even units are rounded up with ROUNDUP.
• The chart is a scatter chart and the lines cross at the break-even point.
• Goal Seek and the check row in E13 agree with the formula.
• For a cash view, loan principal is added and depreciation is left out.

Break-even analysis turns your profit and loss statement into a sales target. If the business is financing equipment, the loan amortization schedule with extra payments shows how the interest and principal feed into the fixed costs.