Spreadsheets

Break-even calculator sheet for a small shop or home business

Build a break-even sheet for a small shop or home business: fixed costs, price and variable cost per unit give the break-even quantity and rupees.

Editorial illustration for Break-even calculator sheet for a small shop or home business

Break-even quantity is fixed costs divided by what each unit contributes after its own cost: =Fixed/(Price-VariableCost). A tiffin service with ₹18,500 of fixed monthly costs, a price of ₹90 per tiffin and ₹52 of food and packaging per tiffin has to sell about 487 tiffins a month to cover costs, which is ₹43,830 of sales.

Sort your costs into two piles

Fixed costs are paid whether you sell ten units or a thousand. Variable costs rise with each unit. For the tiffin example:

  • Fixed, per month: rent ₹8,000, gas cylinders ₹4,500 (treated as fixed to keep the model simple), helper ₹6,000. Total ₹18,500.
  • Variable, per tiffin: vegetables, rice, dal and oil ₹45, plus container and delivery bag ₹7. Total ₹52.
  • Price, per tiffin: ₹90.
  • Costs that sit in between, such as electricity, go in whichever pile is closer to the truth, and you can adjust later.

The cells and formulas

  1. B1 Fixed costs per month: =8000+4500+6000, or the total.
  2. B2 Price per unit: 90.
  3. B3 Variable cost per unit: 52.
  4. B4 Contribution per unit: =B2-B3.
  5. B5 Break-even units: =IF(B4<=0, "Price too low", ROUNDUP(B1/B4, 0)).
  6. B6 Break-even sales in rupees: =B5*B2.
  7. B7 Units per working day: =ROUNDUP(B5/26, 0), if you work 26 days a month.

With the example numbers, contribution is ₹38, break-even is 18500/38 = 486.84, rounded up to 487 tiffins, sales of ₹43,830, or 19 tiffins on each of 26 working days. ROUNDUP is used because you cannot sell a fraction of a tiffin.

What happens if I change the price?

PriceContributionBreak-even unitsSales neededUnits per day (26 days)
₹90₹38487₹43,83019
₹95₹43431₹40,94517
₹100₹48386₹38,60015

A small rise in price cuts the volume you need by a lot, because every tiffin then contributes more toward the fixed costs. A small cut in price pushes the target up quickly. That is the use of the sheet: seeing the sensitivity before you change a menu.

How many units do I need to earn a target profit?

Add the profit you want to the fixed costs: =ROUNDUP((B1+Target)/B4, 0). To take home ₹10,000 a month at the same prices, you would need (18500+10000)/38 = 750.0, so 750 tiffins. Profit here is before your own salary and any tax, so decide whether the target includes a wage for yourself.

How far above break-even am I?

Say a month ends at 600 tiffins. Profit is 600 x ₹38 - ₹18,500 = ₹4,300. The margin of safety is (600-487)/600, about 18.8%, meaning sales could fall by that much before you start losing money. Put =(Actual-B5)/Actual in a cell and watch it as the months go by. A thin margin means one slow week, a festival break or a price rise in vegetables can push you into a loss.

What if I sell more than one item?

Break-even gets harder to state with a menu of different prices. A simple way is to use an average: total sales divided by total units gives an average price, and total variable cost divided by total units gives an average variable cost. Plug those into the same cells. It is a rough guide that holds only while your mix of items stays about the same.

How do I know my numbers are right?

  • Use a full month of real bills, not what you think you spend.
  • Check that the variable cost per tiffin includes wastage and spoilage.
  • Update the sheet when the rent, gas price or ingredient prices change.
  • Treat the answer as a floor for sales, not the target.

If you pay these costs from an account you already use, importing the statement into SpendixAI and adding categories for rent, gas and ingredients gives you the real monthly figures to type into the first two cells, instead of estimates.

SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports