Break-Even Analysis Spreadsheet for Small Business

Break-even analysis answers one of the most important questions in business: at what revenue level do we stop losing money? Every pricing decision, hiring decision, and product launch should start with this calculation.

The Core Formula

Break-even point = Fixed Costs ÷ Gross Margin %

Or in units: Break-even units = Fixed Costs ÷ (Price per unit − Variable cost per unit)

Fixed costs: Costs that don’t change with revenue — rent, salaries, insurance, subscriptions, loan payments.

Variable costs: Costs that scale with revenue — materials, shipping, sales commissions, credit card fees, direct labor by the piece.

Gross margin: Revenue minus variable costs, expressed as a percentage: (Revenue − Variable Costs) ÷ Revenue × 100

Building the Spreadsheet

Section 1: Fixed Costs

List every fixed monthly cost:

• Rent: $2,500
• Staff salaries: $8,000
• Insurance: $400
• Software subscriptions: $200
• Loan payments: $600
• Total fixed costs: $11,700/month

Section 2: Variable Costs (as % of revenue)

List every variable cost as a percentage of sales:

• Materials: 30%
• Shipping: 5%
• Payment processing: 2.9%
• Sales commissions: 5%
• Total variable cost %: 42.9%
• Gross margin: 57.1%

Section 3: Break-Even Calculation

Break-even revenue = $11,700 ÷ 57.1% = $20,490/month

That’s how much you need to sell before you make any profit.

Making the Spreadsheet Interactive

Add input cells (yellow background) for:

• Each fixed cost line item
• Each variable cost percentage
• Current price
• Target units sold

Add output cells (green background) for:

• Total fixed costs
• Gross margin %
• Break-even revenue
• Break-even units
• Profit/loss at current volume

Connect all outputs with formulas referencing the input cells. Now you can change any assumption and see immediately how it affects your break-even.

Using Break-Even for Decisions

Pricing: If you’re at break-even at $50/unit, what happens if you raise to $55? Recalculate break-even — you need fewer units, and each additional unit above break-even contributes more profit. Price increases often dramatically improve break-even position.

New hires: Adding a $60,000 salary increases fixed costs by $5,000/month. At a 57% gross margin, you need $8,772 more in monthly revenue to cover one hire. That’s your minimum revenue threshold for the hire to be justified.

New product lines: Model each new product separately. Products with high variable costs and low margins have poor break-even characteristics — they require more volume to justify than they look like.

Discounts: Every discount compresses gross margin. At 57% margin, a 10% discount drops to ~51% margin, requiring 12% more volume just to break even on the same total revenue. Discounting often destroys more profit than it generates volume.

The Contribution Margin Lens

Contribution margin = Revenue − Variable Costs = what’s left to cover fixed costs and profit

Product A: $100 price, $40 variable cost = $60 contribution margin (60%) Product B: $60 price, $10 variable cost = $50 contribution margin (83%)

Product B has a lower price but a higher contribution margin percentage. Selling more of Product B is better for profit despite the lower price. Most businesses don’t know this about their own products because they haven’t run the numbers.

Build your break-even model this week. Enter your actual fixed costs and actual variable costs by category. The result will tell you where you actually stand — and usually reveals pricing or cost leverage you hadn’t seen.