Build a Break-Even and Pricing Calculator Spreadsheet in Excel

You get an Excel layout that separates fixed and variable costs, formulas for break-even and margin at three prices, and a ranking of which inputs move the result most.

Prompt

Prompt
You are a small-business financial analyst who builds clear, simple Excel models.

Your job: build a break-even and pricing calculator spreadsheet for my product or service. First ask me these questions in one message, then wait for my answers:
1. What do you sell, and what is one unit (an item, an hour, a monthly subscription)?
2. What are your monthly costs that stay the same regardless of sales, and your costs per unit sold, with rough amounts?
3. What price do you charge now, or what range are you considering?
4. How many units do you expect to sell per month?

Rules:
- Put fixed and variable costs on an Inputs sheet, one cost per row, so I can edit any number.
- Give exact Excel formulas with cell references for contribution margin, margin %, break-even units (rounded up) and profit.
- Model three price points: current, 15% lower and 15% higher.
- Change price, fixed costs and variable cost by 10% each, report the effect on break-even units, and rank the inputs by impact.
- Flag any cost that looks missing or unrealistic.

Output format:
1. Sheet layout table (Cell, Label, Value or Formula)
2. Price comparison table (Price, Contribution margin, Margin %, Break-even units, Profit at expected volume)
3. Sensitivity ranking table (Input, Effect of +10%, Rank)
4. Three-sentence takeaway

End with one line on assumptions made and costs that may be missing. This is not professional financial advice; check major pricing decisions with an accountant.

Example output

Example for: a home candle business selling 8oz soy candles at $24, expecting 300 sales a month.

1. Sheet layout (Inputs sheet)
CellLabelValue or Formula
B4Studio rent (fixed)650
B5Part-time helper (fixed)1,200
B6Store platform (fixed)39
B7Insurance (fixed)45
B8Design software (fixed)26
B9Total fixed costs=SUM(B4:B8) → 1,960
B12Wax per candle2.10
B13Jar and lid1.80
B14Wick and fragrance1.60
B15Packaging0.90
B16Variable cost per unit=SUM(B12:B15) → 6.40
B18Price24.00
B19Expected units/month300
B21Contribution margin=B18-B16
B22Margin %=B21/B18
B23Break-even units=ROUNDUP(B9/B21,0)
B24Profit at expected volume=B19*B21-B9
E2Price −15%=B18*0.85
F2Price +15%=B18*1.15
2. Price comparison
PriceContribution marginMargin %Break-even unitsProfit at 300 units
$20.40$14.0068.6%140$2,240
$24.00$17.6073.3%112$3,320
$27.60$21.2076.8%93$4,400
3. Sensitivity ranking (from current price)
InputEffect of +10%Rank
Price ($24 → $26.40)Break-even falls from 112 to 98 (−12%)1
Fixed costs ($1,960 → $2,156)Break-even rises to 123 (+10%)2
Variable cost ($6.40 → $7.04)Break-even rises to 116 (+4%)3
4. Takeaway

You break even at 112 candles, well below your expected 300, so the business has a healthy buffer. Price is the strongest lever: a 15% increase adds $1,080 a month if volume holds. Variable costs matter least, so negotiating wax prices is lower priority than testing a $26–28 price.

Assumptions and gaps: volume assumed unchanged across prices; payment processing fees (about 3%), shipping and your own pay are not included and would raise break-even.

Written by AI from this prompt. Your results will vary.

Tips and common edits

Why this works

  • Asking four questions first means the model works with your real costs instead of generic numbers.
  • Requiring cell-referenced formulas gives you a working sheet, not just a one-off calculation.
  • The 10% sensitivity test shows which input to focus on, turning numbers into a pricing decision.

Use when setting or reviewing a price. Have your monthly fixed costs, per-unit costs, current price and expected sales ready. Works in ChatGPT, Claude or Gemini; paste the formulas straight into Excel or Google Sheets.

Common edits

  • Change the price points to fixed dollar amounts you are actually considering.
  • Add a column for a target monthly profit and ask for units needed to reach it.

Prompt details

By
Prompts-GPT editors
Published

Did this prompt work?

Keep exploring

More in Excel & Spreadsheets