A break-even analysis answers one of the most fundamental business questions: how much do you need to sell before you stop losing money? In Excel, you can build one in about 15 minutes that gives you a clear number and a chart that makes it visual.
The Break-Even Formula
Break-even units = Fixed Costs / (Price per Unit - Variable Cost per Unit)
The denominator (Price minus Variable Cost) is your contribution margin per unit — the amount each sale contributes toward covering fixed costs.
If your fixed costs are $10,000/month, your price is $50 per unit, and your variable cost is $20 per unit, your contribution margin is $30. Break-even is 10,000/30 = 334 units.
Setting Up Your Inputs Table
Create an inputs table at the top of your sheet:
- Fixed Costs (monthly): rent, salaries, software, insurance — costs that don't change with volume
- Price per Unit: your selling price
- Variable Cost per Unit: materials, shipping, payment processing fees — costs that scale with volume
Keep these as input cells you can change. All formulas reference these cells, so changing one number updates the whole model.
The Break-Even Calculation
Calculate contribution margin:
=B2-B3
Where B2 is price and B3 is variable cost. Calculate break-even units:
=B1/B4
Where B1 is fixed costs and B4 is contribution margin. Calculate break-even revenue:
=B5*B2
Where B5 is break-even units and B2 is price.
Building the Break-Even Chart
Create a data table with units in column A (0 to 2x your break-even point) and three columns: Fixed Costs (constant at your fixed cost amount), Total Costs (fixed costs plus variable costs per unit times volume), and Total Revenue (price times volume).
=B1
=B1+(B3*A10)
=B2*A10
Where A10 is the unit volume in that row. Plot all three lines as a line chart. The point where Total Revenue crosses Total Costs is your break-even point — visually obvious on the chart.
Sensitivity Analysis
Add a sensitivity table showing break-even units at different price and variable cost combinations. Use Excel's Data Table feature (What-If Analysis, Data Table) to populate the grid automatically. This shows how sensitive your break-even point is to pricing and cost changes.
The Easy Way: Using SheetXAI in Excel
Example 1: You know your costs and pricing.
"Build a break-even analysis with fixed costs of $15,000/month, price of $75 per unit, and variable cost of $28 per unit. Include a break-even chart and a sensitivity table showing break-even at different price points."
SheetXAI builds the inputs table, calculates the break-even point, generates the chart, and builds the sensitivity table.
Example 2: Your cost data is in your accounting software.
"Pull our fixed and variable costs from QuickBooks and build a break-even analysis for our main product line at our current pricing."
SheetXAI pulls the cost data and builds the full break-even model.
Try SheetXAI free and see what it builds for you.
Published May 2026. See also: How to Build a Profit and Loss Report in Google Sheets, How to Create a Cash Flow Forecast in Google Sheets, and Google Sheets AI Guide.
