How to Make a Break-Even Analysis in Excel: Complete Guide
A break-even analysis finds the sales level at which total revenue equals total cost, so the business makes neither a profit nor a loss. To make one in Excel, enter your fixed costs, price per unit, and variable cost per unit. Divide fixed costs by the difference between price and variable cost, then chart revenue against total cost.
This guide covers the formulas, the inputs you need, three Excel steps, and a worked example. It also shows how to use Goal Seek and data tables to test what changes if prices or costs move.
What a break-even analysis is
The US Small Business Administration defines the break-even point as "the point at which total cost and total revenue are equal." Below that point, each sale still leaves the business short of covering its costs. Above it, each sale adds profit.
The SBA lists a break-even analysis among the reasons to calculate startup costs, alongside estimating profits and securing loans. Lenders and investors often ask for one because it shows how much the business must sell before it stops losing money.
A break-even analysis answers three practical questions. How many units do we need to sell? How much revenue does that represent? And how sensitive is the answer to price and cost changes?
It also works as a quick test of a new idea. Before you commit to a new product or location, estimate the three inputs. Then ask whether the required volume is realistic for the market you serve.
The break-even formulas
The SBA gives two versions of the break-even formula.
In units:
Break-even point (units) = fixed costs / (sales price per unit - variable cost per unit)
In sales dollars:
Break-even point (sales dollars) = fixed costs / contribution margin
The SBA explains the second term as "the difference between the price of a product and what it costs to make that product." For the sales-dollar formula, it calculates that margin as a ratio: price minus variable cost, divided by price.
That distinction matters in a spreadsheet. The contribution margin per unit is a dollar amount, such as $3 on a $5 product. The contribution margin ratio is a percentage, such as 60%. Use the dollar figure for the units formula and the ratio for the sales-dollar formula.
The SBA also sets a boundary: "This break-even analysis is based on the foundation of a single product or service." A later section covers what to do with more than one product.
What you need before you start
Three inputs drive every break-even analysis. Getting the cost split right matters more than the Excel work.
| Input | What it means | Examples |
|---|---|---|
| Fixed costs | Costs that stay the same regardless of how much you sell | Rent, salaries, insurance, software subscriptions |
| Variable cost per unit | Costs that rise with each unit sold | Materials, packaging, payment fees, shipping |
| Price per unit | What the customer pays for one unit | List price, or average selling price after discounts |
Use the same time period for every input. If rent is monthly, the result is a monthly break-even point. Mixing a yearly salary with a monthly rent produces a number that means nothing.
Variable cost per unit is the input people most often guess. Estimate it from history instead. Take last quarter's total variable costs and divide by the units sold in the same quarter. If the result moves a lot from quarter to quarter, use an average of several periods.
Some costs are mixed. A phone plan with a base fee and usage charges has a fixed part and a variable part. Split them rather than guessing which bucket they belong in.
How to make a break-even analysis in Excel
The three steps below build a working break-even model and chart. They use standard formulas that also work in Google Sheets.
Step 1: Set up the inputs
Open a blank sheet and label cells A1 to A3 as Fixed costs, Price per unit, and Variable cost per unit. Enter the values in B1 to B3.
Keep inputs in their own cells, never typed into formulas. That way, every later calculation updates when an input changes, and you can test scenarios by editing one cell.
Format the input cells with a light fill color. It signals to anyone opening the file which numbers they are allowed to change.
Step 2: Calculate the break-even point
Add the calculations in rows 5 to 8.
- In A5, type Contribution margin per unit, and in B5 enter
=B2-B3. - In A6, type Break-even units, and in B6 enter
=ROUNDUP(B1/B5,0). - In A7, type Contribution margin ratio, and in B7 enter
=B5/B2. - In A8, type Break-even sales, and in B8 enter
=B1/B7.
ROUNDUP matters in B6. You cannot sell a fraction of a unit, and rounding down would leave you just short of break-even.
Check that B8 roughly equals B6 multiplied by the price. If it does not, one of the inputs is in the wrong cell.
Step 3: Build the break-even chart
Below the calculations, build a small table. In column D, list unit volumes from 0 upward in even steps, such as 0, 500, and 1,000. Keep going past your break-even point. In column E, calculate revenue with =D11*$B$2. In column F, calculate total cost with =$B$1+D11*$B$3.
Select columns D to F and insert a chart. A scatter chart with straight lines works best, because it treats the units column as a true numeric axis.
The revenue line starts at zero and rises steeply. The total cost line starts at the fixed cost level and rises more slowly. Where they cross is the break-even point. Add a data label there so readers do not have to estimate it. Shade the area to the right of the crossing point in a light color to show the profit zone.
A worked example
A coffee cart has fixed costs of $6,000 a month. It sells coffee at $5.00 a cup, and each cup costs $2.00 in beans, milk, cups, and card fees.
| Calculation | Result |
|---|---|
| Contribution margin per unit | $5.00 - $2.00 = $3.00 |
| Break-even units | $6,000 / $3.00 = 2,000 cups |
| Contribution margin ratio | $3.00 / $5.00 = 60% |
| Break-even sales | $6,000 / 0.60 = $10,000 |
The cart needs to sell 2,000 cups a month, or $10,000 in revenue, to cover its costs. Every cup after that adds $3.00 of profit.
A target profit uses the same logic. To earn $3,000 a month, add it to fixed costs: $9,000 divided by $3.00 is 3,000 cups.
The margin of safety shows how much room there is. If the cart expects to sell 2,600 cups, it can fall 600 cups short before losing money. That is about 23% of expected sales.
Use Goal Seek to find the break-even price
Sometimes the question runs the other way. You know your volume and want to know what price breaks even.
Microsoft's support page frames the use case simply. You know the result you want from a formula, but not the input that produces it. Goal Seek finds that input by adjusting one cell.
Add a profit cell, such as B9, with =B2*C1-B1-B3*C1, where C1 holds your expected volume. Then follow Microsoft's steps. On the Data tab, in the Forecast group, select What-If Analysis, then Goal Seek. Set cell B9 to value 0 by changing cell B2.
Excel adjusts the price until profit is exactly zero. At 1,500 cups a month, the coffee cart would need a price of $6.00 to break even.
Microsoft notes one limit: "Goal Seek works only with one variable input value." To solve for several inputs at once, it points to the Solver add-in.
Sensitivity: what if price or costs change?
A single break-even number hides how fragile it can be. A sensitivity table shows the answer across a range of inputs.
Using the coffee cart, a small change in price or cost moves break-even noticeably:
| Scenario | Contribution margin | Break-even units |
|---|---|---|
| Base case: $5.00 price, $2.00 cost | $3.00 | 2,000 |
| Price rises to $5.50 | $3.50 | 1,715 |
| Variable cost rises to $2.50 | $2.50 | 2,400 |
| Fixed costs rise to $7,500 | $3.00 | 2,500 |
Excel's Data Table feature, under What-If Analysis, can build a grid like this automatically. Put a range of prices down one column and a range of variable costs across one row. Then point the table at the break-even units cell.
Compare changes of the same size to see which input matters most. In this example, a 50-cent change adds 400 cups to break-even when it hits variable cost, but removes only 285 when it goes on the price. That is why variable cost is often the first cost worth negotiating.
Break-even analysis for more than one product
The SBA's formula assumes a single product. Most businesses sell several, each with its own margin.
The usual approach is a weighted average contribution margin. Estimate the share of sales each product contributes, multiply each product's margin by its share, and add them up. Divide fixed costs by that weighted figure to get break-even units across the mix.
Here is a short example. Product A has a $3 margin and makes up 60% of units. Product B has a $5 margin and makes up 40%. The weighted margin is $3 times 0.6 plus $5 times 0.4, or $3.80. With fixed costs of $7,600, break-even is 2,000 units: 1,200 of A and 800 of B.
The SBA notes that you may also want to do the calculation individually for each product if sales vary month to month. It adds a caution worth keeping in mind. The break-even point is an estimate for planning and lender viability, and it is not intended to replace detailed accounting.
If the mix changes, the break-even point changes with it. A shift toward low-margin products raises break-even even when total sales hold steady.
How to present a break-even analysis
Most readers need three numbers and one chart. Lead with break-even units, break-even sales, and the margin of safety. Put the chart directly underneath, with the crossing point labeled.
Then show the sensitivity table. It answers the question every lender and manager asks next: what happens if costs rise or sales fall short?
Keep the inputs visible. A short list of fixed costs, the price, and the variable cost per unit lets a reader check your logic in a minute. The SBA notes that the break-even point is an important calculation in a business plan, so expect it to be read closely.
Common mistakes
- Mixing time periods. Monthly rent with a yearly salary gives a meaningless result. Convert everything to one period.
- Using list price when customers pay less. If discounts are common, use the average price actually received.
- Forgetting small variable costs. Card fees, packaging, and shipping add up per unit and shift the answer.
- Ignoring capacity. Fixed costs often jump at certain volumes, such as when a second shift or a larger space is needed. Recalculate at each step.
- Rounding down. A fractional result means you need the next whole unit, not the one before.
Doing it faster with AI
The spreadsheet method works well once the cost split is clear. The slow part is usually getting there, because costs sit in a general ledger export or a pile of invoices.
An AI workspace can do that sorting. Upload the cost export to Powerdrill Bloom and ask it to classify each line as fixed or variable, then calculate break-even units and sales. Ask for the sensitivity table and chart in the same request.
Review the classification before you trust the result. Mixed costs, such as utilities, often need a judgment call that only you can make.
For the wider planning picture, the sensitivity analysis generator and AI financial modeling pages cover related models. Our guide to a budget vs. actual report shows how to track whether real results are landing above or below plan. For timing rather than totals, a cash flow report shows when the cash actually arrives.
If you have a cost export ready, you can try Powerdrill Bloom and compare its break-even result with your own spreadsheet.
Frequently asked questions
What is a break-even analysis?
A break-even analysis calculates the sales level at which total revenue equals total cost. At that point, a business makes neither a profit nor a loss. It is commonly used in business plans and loan applications.
How do you calculate the break-even point in units?
Divide fixed costs by the contribution margin per unit, which is the sales price minus the variable cost per unit. With $6,000 in fixed costs and a $3 margin per unit, the break-even point is 2,000 units.
How do you do a break-even analysis in Excel?
Enter fixed costs, price, and variable cost in separate cells. Calculate the margin as price minus variable cost, then divide fixed costs by it. Chart revenue and total cost against units, and read the crossing point.
What is the difference between contribution margin and contribution margin ratio?
Contribution margin per unit is a dollar amount: price minus variable cost. The ratio is that amount divided by price, shown as a percentage. Use the dollar figure for break-even units and the ratio for break-even sales.
Why is break-even analysis important?
It shows how much a business must sell before it stops losing money. It also reveals which input, such as price, variable cost, or rent, moves that threshold most. The SBA lists it among the reasons to calculate startup costs.
Sources: US Small Business Administration, Calculate your startup costs · Microsoft Support, Use Goal Seek. The worked example uses illustrative figures.