A break-even analysis spreadsheet turns costs, pricing, and sales assumptions into a practical target: how many units or how much revenue you need before the business covers its operating costs. This guide shows how to build the model in Excel or Google Sheets, check the assumptions behind it, compare pricing scenarios, and refresh the results when your offer or cost structure changes.
Overview
Break-even analysis identifies the sales level at which total revenue equals total costs. Below that point, the model shows an operating loss; above it, each additional sale contributes toward profit after variable costs have been covered.
A useful break-even calculator spreadsheet separates three core inputs:
- Fixed costs: Costs that do not change directly with the number of units sold during the period, such as rent, salaried labor, software subscriptions, insurance, or planned administrative expenses.
- Variable cost per unit: The cost associated with producing, delivering, or selling one unit, such as materials, packaging, transaction fees, commissions, or fulfillment.
- Selling price per unit: The amount collected for one unit, before deciding whether taxes, discounts, refunds, or shipping should be modeled separately.
The key calculation is the contribution margin: selling price minus variable cost per unit. It is the amount each sale contributes toward fixed costs and then profit.
For a single product, the main formulas are:
- Contribution margin per unit = Selling price − Variable cost per unit
- Contribution margin ratio = Contribution margin per unit ÷ Selling price
- Break-even units = Fixed costs ÷ Contribution margin per unit
- Break-even revenue = Fixed costs ÷ Contribution margin ratio
A break-even result is not a sales forecast. It is a decision-support estimate based on the assumptions entered. Use it alongside a cash flow forecast spreadsheet to consider the timing of receipts and payments, and use a KPI dashboard spreadsheet to compare the model with actual performance.
How to estimate break-even in Excel or Google Sheets
Build the workbook so that assumptions, calculations, and outputs are easy to distinguish. A simple structure makes the model easier to audit and update.
1. Create an assumptions block
On a sheet named Break-Even Model, place labels in column A and values in column B:
| Cell | Input | Example |
|---|---|---|
| B2 | Fixed costs for the period | 24000 |
| B3 | Selling price per unit | 80 |
| B4 | Variable cost per unit | 32 |
Label the period clearly. Fixed costs might be monthly, quarterly, or annual, but the sales target must use the same period. Format prices as currency and use data validation or notes to explain whether amounts include discounts, commissions, shipping, or taxes.
2. Add the calculation block
Use formulas rather than manually typing calculated values. For example:
- B7, contribution margin per unit:
=B3-B4 - B8, contribution margin ratio:
=B7/B3 - B9, break-even units:
=B2/B7 - B10, break-even whole units:
=ROUNDUP(B9,0) - B11, break-even revenue:
=B2/B8
Add safeguards if the sheet will be shared. For example, =IFERROR(B2/B7,"Check price and variable cost") prevents a confusing error when the contribution margin is zero or negative. A selling price that is equal to or below the variable cost cannot produce a conventional break-even point through unit sales alone.
3. Add a target-profit calculation
Break-even is only one useful target. Add a desired operating profit in B12, then calculate required units in B13:
=(B2+B12)/B7
This formula shows how many units are needed to cover fixed costs and reach the selected profit target. Round the result up when converting it into an operational sales goal. You can also calculate target revenue with =(B2+B12)/B8.
4. Add a comparison table
Create columns for scenario name, price, variable cost, contribution margin, contribution margin ratio, break-even units, and break-even revenue. Link each row to the relevant assumptions or enter scenario values separately. Typical rows include:
- Current pricing
- Higher price with the same variable cost
- Lower price with a higher expected sales volume
- Reduced variable cost after a supplier or process change
- Higher fixed costs after hiring or adding capacity
Conditional formatting can highlight the lowest break-even volume, but do not treat that result as automatically best. A higher price may reduce demand, while a lower variable cost may require a one-time investment that belongs in fixed costs or a separate cash flow view.
Inputs and assumptions to check
The arithmetic in a break-even analysis is straightforward; classification is where many models become unreliable. Review the following decisions before relying on the result.
Keep the time period consistent
If fixed costs are monthly, use the expected monthly sales target. If a cost is annual, divide it into a monthly assumption only when that allocation is useful and consistent. Avoid comparing annual rent with one week of sales.
Separate fixed and variable behavior
Some costs are mixed. A delivery contract may include a fixed base fee and a per-order charge. Put the base fee in fixed costs and the per-order amount in variable cost per unit. Staff costs may also change when sales pass a capacity threshold, so consider a stepped-cost scenario rather than assuming the same cost at every volume.
Define the unit carefully
A unit might be a product, order, customer, subscription month, service hour, or project. Choose a unit that matches how prices and variable costs are recorded. If the business sells bundles, calculate the price and variable cost per bundle or convert everything to a common unit.
Account for discounts and sales mix
List price is not always realized price. If discounts are common, use an average net selling price or model separate scenarios. For multiple products, a single break-even formula requires an assumed sales mix. Calculate the weighted average contribution margin using the expected share of each product, then divide fixed costs by that weighted margin.
For example, if Product A supplies 60% of unit sales with a contribution margin of 48, and Product B supplies 40% with a contribution margin of 30, the weighted average contribution margin is:
=(60%*48)+(40%*30)
Do not use a product mix model when the mix is unknown or changes substantially. Build separate scenarios instead, and document the mix assumption beside the calculation.
Worked example: comparing sales targets and pricing
Assume a business has fixed monthly costs of 24,000, a selling price of 80 per unit, and a variable cost of 32 per unit.
The contribution margin is 48 per unit, and the contribution margin ratio is 60%. Break-even units are 24,000 divided by 48, or 500 units. Break-even revenue is 24,000 divided by 60%, or 40,000.
Now compare three scenarios:
| Scenario | Price | Variable cost | Margin per unit | Break-even units | Break-even revenue |
|---|---|---|---|---|---|
| Current | 80 | 32 | 48 | 500 | 40,000 |
| Higher price | 90 | 32 | 58 | 414 | 37,241 |
| Lower variable cost | 80 | 26 | 54 | 445 | 35,556 |
These results show the mechanical effect of the assumptions, not the likely demand response. The higher-price scenario may have a lower target volume, but it is useful only if customers continue to buy at that price. Add an expected volume or demand assumption in a separate column, then compare expected sales with break-even units. A scenario analysis spreadsheet can help organize these best-case, base-case, and downside assumptions.
When to recalculate
Revisit the model whenever a meaningful input changes, not only at year-end. Update it when prices, discounts, supplier costs, commissions, staffing, rent, software subscriptions, or delivery arrangements change. A new product, sales channel, promotion, or service package can also change the relevant unit economics.
A practical monthly review has four steps:
- Copy actual fixed costs and realized sales data into an actuals section.
- Compare actual price, variable cost, contribution margin, and unit volume with the assumptions.
- Explain material differences rather than overwriting the original forecast.
- Refresh the forward-looking assumptions and reset the sales target.
Keep an assumptions-change log with the date, changed input, old value, new value, and reason. This preserves context when results shift. Link the break-even target to your sales tracker or KPI dashboard, and review it with the wider plan in a strategic planning spreadsheet. If staffing, capacity, or deadlines affect the target, connect the analysis to a project management spreadsheet.
Finally, test the model before using it in a decision: change one input at a time, check that the outputs move in the expected direction, and verify that the units and time period are consistent. A small, transparent break-even analysis Excel template or Google Sheets template is usually more useful than a complex financial model whose assumptions are difficult to inspect.
