A break-even model shows how many units, sales or billable hours a business must generate before revenue covers its costs. This guide explains how to build a practical break-even analysis in Excel, choose reliable assumptions, test pricing and volume scenarios, and decide when the model needs to be updated.
Overview
Break-even analysis is a planning tool rather than a prediction. It answers a focused question: what level of activity produces zero operating profit after the costs included in the model? Activity above that point creates a contribution towards profit; activity below it produces a loss under the stated assumptions.
The basic break-even formula is:
Break-even units = Fixed costs ÷ Contribution per unit
Contribution per unit is the amount left from each sale after its variable cost:
Contribution per unit = Selling price per unit − Variable cost per unit
For a business selling several products, a single-unit calculation may be misleading. You can instead use a contribution margin ratio, based on the expected sales mix:
Break-even sales value = Fixed costs ÷ Contribution margin ratio
In Excel, the model is most useful when assumptions are separated from calculations. Keep input cells for price, variable cost and fixed costs, then use formulas for contribution, break-even volume and scenario outputs. This makes the workbook easier to audit and update than a calculation built from hard-coded numbers.
A break-even analysis calculator can support decisions about pricing, staffing, capacity, product launches, sales targets and project viability. It should not replace cash-flow planning: a business can pass its accounting break-even point and still have a short-term cash requirement because of payment timing, financing, tax or capital expenditure.
How to estimate break-even in Excel
Start with a simple input table. For example, place the following labels in column A and values in column B:
- Selling price per unit: the revenue recognised for one sale, using a consistent VAT treatment.
- Variable cost per unit: the cost that changes with each unit sold.
- Total fixed costs: costs that remain broadly unchanged over the planning period.
- Target profit: optional profit required above break-even.
Assume the selling price is in cell B2, variable cost per unit is in B3, fixed costs are in B4 and target profit is in B5. The core formulas could be:
=B2-B3for contribution per unit.=B4/(B2-B3)for break-even units.=ROUNDUP(B4/(B2-B3),0)for the first whole unit that reaches break-even.=(B4+B5)/(B2-B3)for units required to cover fixed costs and the target profit.=B4/(B2-B3)*B2for break-even sales revenue.
Use ROUNDUP when the result represents physical units, orders or billable projects. A fractional result can remain useful for analytical work, but a business cannot usually sell part of a finished unit. Add a check to prevent misleading results when the contribution is zero or negative. For example, =IF(B2<=B3,"No positive contribution",ROUNDUP(B4/(B2-B3),0)) flags a price that does not cover variable cost.
For a more complete model, add expected sales volume and calculate the operating result:
Operating profit = (Sales volume × Contribution per unit) − Fixed costs
This lets you compare the break-even point with a realistic sales forecast. A sales forecast template in Excel can help you document the volume assumptions behind the model rather than entering a single unsupported target.
Inputs and assumptions
The quality of a break-even analysis depends on how costs are classified. Fixed costs may include rent, core salaries, software subscriptions, insurance and other overheads that do not change directly with each sale during the selected period. Variable costs may include materials, transaction charges, delivery, packaging, sales commission or subcontractor time linked to an order.
Some costs are mixed. A utility bill, warehouse cost or support team may contain a fixed base and a volume-related element. Split these where practical, or document the simplifying assumption in a notes column. Avoid moving every expense into one category simply to make the formula work.
Choose a time period before entering fixed costs. If the price is per month but fixed costs are annual, the result will be distorted. Convert all inputs to the same period, such as monthly, quarterly or annual. If activity is seasonal, calculate separate periods or use a scenario model rather than treating the annual average as a typical month.
Use a consistent VAT basis. For a VAT-registered business, profitability analysis will often use net sales and net costs where VAT is recoverable, but the correct treatment depends on the transaction and the purpose of the model. Do not mix VAT-inclusive revenue with VAT-exclusive costs. For invoice and rate checks, see the guide to the VAT calculator in Excel for UK businesses.
Document assumptions beside the input cells: source, date, owner, period covered and whether the figure is an estimate. Use named ranges or an Excel table if the workbook will be maintained by several people. Protect formula cells, apply input formatting and include a warning for blank, negative or zero values.
Worked examples
Single-product example
Suppose a business sells a service package for £120, with a variable delivery cost of £45 per package. Monthly fixed costs are £9,000. Contribution per package is therefore £75:
£120 − £45 = £75
The monthly break-even volume is:
£9,000 ÷ £75 = 120 packages
The corresponding break-even sales value is £14,400, calculated as 120 packages multiplied by £120. If the business wants a monthly operating profit of £3,000, the required volume becomes £12,000 divided by £75, or 160 packages. This example also shows why revenue alone is not enough: the contribution left after variable costs determines how quickly sales cover overhead.
Testing a pricing decision
Now test a lower price of £110 while keeping the £45 variable cost and £9,000 fixed-cost assumption unchanged. Contribution falls to £65, so break-even volume rises to approximately 139 units after rounding up. The price reduction must generate enough additional volume to compensate for the lower contribution per sale.
Build a small scenario table with columns for price, variable cost, fixed costs, break-even units, expected volume and operating profit. Include at least a downside, base and upside case. Excel's data tables or simple linked formulas can show how sensitive the result is to price, cost and volume changes. The Excel scenario planning template provides a useful structure for keeping these cases separate.
Multiple products
For multiple products, calculate the contribution margin for each product and apply an assumed sales mix. If the expected mix changes, the blended contribution margin changes too. A high-margin product may reduce the break-even sales value, while a lower-margin product may require more revenue even if total units appear strong. Record the mix assumption explicitly and compare it with actual sales after each reporting period.
When to recalculate
Recalculate the model whenever a key input changes, not only at year-end. Review it when prices, supplier costs, wages, rent, subscriptions, commission arrangements or staffing levels change. Revisit it before launching a new product, accepting a large contract, changing a discount policy or adding capacity.
In a UK business, payroll-related overhead can change independently of sales volume. If employee costs form a significant part of fixed costs, maintain the assumptions alongside a detailed Excel payroll cost calculator. Review VAT treatment when the purpose of the analysis changes from profitability to customer pricing or cash collection.
At a minimum, compare the model with actual results on a regular reporting cycle. Record actual units, average selling price, variable cost per unit, fixed costs and operating result. Investigate the difference rather than simply replacing the original assumptions. Forecast errors may come from volume, mix, discounting, cost allocation or timing.
For a practical next step, create one controlled workbook with four tabs: Inputs, Calculations, Scenarios and Actuals. Add a review date and an owner to the Inputs tab. Then run three checks: confirm that all values use the same period, verify that contribution is positive, and compare the calculated break-even point with a credible sales forecast. This turns a one-off break-even calculator into a repeatable decision tool that can be refreshed whenever pricing or cost assumptions move.
