How to calculate the break-even point in Excel

Step 1: total fixed costs and variable cost per unit

Fixed costs stay the same whether you sell 10 meals or 1,000: the truck lease, kitchen rent, insurance, permits, market fees and the POS subscription. Here they total $3,565 a month with =SUM(E2:E7). Variable costs rise with every unit sold: ingredients, packaging and the card fee.

The card fee is the one people forget, because it is a percentage, not a fixed amount. At 2.9% of a $14 price it is about $0.41 a meal, so the variable cost per meal is 4.10 + 0.45 + 14 × 2.9%. Keep the rate in its own input cell, B5, and use the rate your own provider charges. If you raise the price, the fee rises with it.

Step 2: contribution margin

The contribution margin is what each sale leaves over to pay the fixed costs: price minus variable cost per unit. Each $14 meal contributes about $9.04. Divided by the price, it is the contribution margin ratio, 64.6%, the share of every sales dollar that goes toward fixed costs and then profit.

If the contribution margin is 0 or negative, you can never break even, however much you sell. The break-even formula then gives a negative number or #DIV/0!. Check for that first, as in the next step.

Step 3: break-even units, rounded up

Divide fixed costs by the contribution margin. The exact answer is 394.18 meals, and you cannot sell 0.18 of a meal. Rounding normally with ROUND gives 394, and at 394 meals the truck still loses about $2. Always round break-even units up with ROUNDUP(...,0).

LET names the pieces so the formula reads like the definition, and IF returns a message instead of a nonsense number when the price does not cover the variable cost. LET needs Excel 365 or 2021; in older versions write the same thing without the names.

Break-even revenue and margin of safety

There are two ways to get break-even sales in dollars. Units times price, =ROUNDUP(...)*B2, gives $5,530, the revenue from 395 whole meals. Fixed costs divided by the contribution margin ratio, =SUM(E2:E7)/((B2-(B3+B4+B2*B5))/B2), gives $5,518.58, the exact point before rounding. Both are right; they differ only by the rounding. Use the ratio version when you sell many products at different prices and only know your average margin.

Margin of safety tells you how far sales can fall before you make a loss. With 900 expected meals a month, sales could drop by about 56% before the truck stops covering its costs.

Units for a target profit

Break-even is profit of zero, and nobody runs a food truck for zero. The owner's pay is not in the fixed costs here, so break-even means the truck pays its bills but the owner earns nothing. Add the profit you need to the fixed costs and divide by the contribution margin again. Put the target in an input cell, B7, so you can change it.

A what-if table: profit at different volumes and prices

In Excel 365 or 2021, one formula can build a small table. SEQUENCE makes the meal counts 250 to 550 in steps of 50, and HSTACK puts revenue and profit next to them. Change any input and the table updates. Profit turns positive between 350 and 400 meals, which matches the 395 break-even.

The same idea works for price. =LET(p,SEQUENCE(5,1,12,1),HSTACK(p,ROUNDUP(SUM(E2:E7)/(p-(B3+B4+p*B5)),0))) lists break-even meals for prices from $12 to $16: 502, 442, 395, 356 and 325. Each $1 of price cuts break-even by roughly 40 to 60 meals, if customers keep buying.

In older versions, use Data > What-If Analysis > Data Table with a column of volumes, or Goal Seek (Data > What-If Analysis > Goal Seek) to find the volume where a profit cell equals 0.