You raised your prices once, three years ago, and you've been nervous about doing it again ever since. Meanwhile your rent went up, your supplies cost more, and you're working just as hard for what feels like less. Most small-business pricing is set by gut feel and what competitors charge. That's a starting point… but a simple break-even model shows you exactly what your prices need to be for the business to work.
Here's how to build one in Excel in under an hour.
Key terms in plain language
- Fixed costs: Expenses that stay about the same each month no matter how much you sell. Rent, insurance, software, salaried pay.
- Variable costs: Costs that go up with each sale. Materials, packaging, shipping, payment processing fees, commissions.
- Contribution margin: Price minus variable cost per unit. It's what each sale contributes toward fixed costs and profit.
- Break-even point: The number of sales where total contribution equals fixed costs. Below it, you lose money. Above it, you make money.
Step 1: List your fixed costs
On a sheet called Inputs, list your monthly fixed costs in column A with amounts in column B:
- Rent
- Utilities
- Insurance
- Software and subscriptions
- Salaries or owner pay
- Loan payments
- Other
At the bottom, total them: =SUM(B2:B8). Name that cell FixedCosts using the Name Box to the left of the formula bar. Named cells make formulas easier to read.
Use your QuickBooks or bank records to get real numbers, not guesses.
Step 2: Calculate variable cost per unit
Below that, list what each unit or job costs to deliver:
- Materials
- Packaging
- Shipping (if you pay it)
- Payment processing (price times your processor's rate)
- Commission or labor per unit
Total them and name the cell VarCost.
For service businesses, a "unit" might be one hour, one appointment, or one project. Pick whatever you sell in.
Step 3: Enter your price and calculate break-even
Add a cell for your current price and name it Price. Then:
- Contribution margin per unit:
=Price-VarCost - Contribution margin %:
=(Price-VarCost)/Price - Break-even units per month:
=FixedCosts/(Price-VarCost)(name this cellBreakEvenUnits) - Break-even revenue per month:
=BreakEvenUnits*Price
Wrap the units in ROUNDUP, like =ROUNDUP(FixedCosts/(Price-VarCost),0), since you can't sell a fraction of a product.
Step 4: Add a profit target
Break-even means zero profit. You probably want more than that. Add a cell for your monthly profit goal, named ProfitGoal, and calculate:
=ROUNDUP((FixedCosts+ProfitGoal)/(Price-VarCost),0)
This tells you how many units you need to sell to hit your goal.
Step 5: Build a price sensitivity table
This is where it gets useful. On a new sheet, list a range of prices in column A, like your current price and several steps above and below it. In the next columns, calculate for each price:
- Contribution margin per unit
- Break-even units
- Units needed for your profit goal
Now you can see, at a glance, how much a small price change affects the number of sales you need.
For a more advanced version, use Data > What-If Analysis > Data Table to test combinations of price and volume in a grid.
Step 6: Try Goal Seek
Want to know the exact price needed to hit your profit goal at your current sales volume? Use Data > What-If Analysis > Goal Seek:
- Set cell: a monthly profit cell, such as
=(Price-VarCost)*UnitsSold-FixedCosts, whereUnitsSoldis a named cell holding your current monthly sales - To value: your profit goal
- By changing cell: Price
Excel finds the price for you.
Example: a small candle business
Here's a made-up example to show how it works. A candle maker has $2,400 in monthly fixed costs. Each candle costs $7 in wax, wick, jar, label, and packaging, plus processing fees. She sells them for $22.
- Contribution margin: $15 per candle
- Break-even: 160 candles a month
- To reach a $1,500 monthly profit: 260 candles
Her sensitivity table shows that at $25, she'd need 134 candles to break even and 217 to hit her goal. That gives her a clear way to think about a price increase. If she's selling about 260 candles now, she could lose up to 43 sales a month at the higher price and still hit the same goal. If she expects to lose fewer than that, the increase is likely to improve her profit.
Common mistakes to avoid
Forgetting hidden variable costs. Payment processing, shipping supplies, and platform fees add up.
Leaving out owner pay. If you don't include a reasonable wage for yourself as a fixed cost, your break-even point is too low.
Using one blended price for very different products. If your products have very different margins, build the model for each major product or use a weighted average.
Assuming volume stays the same at any price. Price changes can affect demand. Use the model to find the trade-off, then test carefully.
Never updating it. Review costs at least a couple of times a year.
Use your model to make confident decisions
A break-even model takes the fear out of pricing conversations. Instead of guessing, you'll know what each price means for your business.
Build smarter models
Our Advanced Excel for Business Analysis course teaches the skills that make models like this reliable and easy to update: clean input layouts, named ranges, formulas, conditional formatting, and charts that make the answer obvious.