DEV Community

Sebastian Bardach
Sebastian Bardach

Posted on Edited on

Cost a restaurant menu in a spreadsheet: four formulas and one sheet layout

#spreadsheets #excel #googlesheets #tutorial

A restaurant menu is a small data model: ingredients with prices, recipes that use them, and dishes with a selling price. Most kitchens keep it in their heads. Here is the version that fits in one spreadsheet, with no macros, in Excel 365 or Google Sheets.

The data: three sheets

Sheet One row per Columns
Ingredients purchased item A name, B pack size, C pack unit, D pack price, E usable %, F cost per usable unit
Recipes ingredient in a dish A dish, B ingredient, C quantity (in the ingredient's pack unit), D unit, E unit cost, F line cost
Dishes menu item A dish, B portions per batch, C waste allowance %, D extras per plate, E menu price (net of tax), F target food cost %, G plate cost, H food cost %, I suggested price

One rule saves most errors: recipe quantities use the same unit as the pack price. If onions are priced per kg, write 0.12 kg, not 120 g (or add a conversion column).

Formula 1 — cost per usable unit (Ingredients!F2) 

=D2/(B2*E2)
Enter fullscreen mode Exit fullscreen mode

A 10 kg sack of onions at 15.00 with 90 % usable after peeling: 15 / (10 × 0.9) = 1.67 per kg, not 1.50.

Formula 2 — plate cost

Look up the unit cost and the line cost in Recipes:

E2:  =XLOOKUP(B2, Ingredients!A:A, Ingredients!F:F)
F2:  =C2*E2
Enter fullscreen mode Exit fullscreen mode

(Older Excel: =INDEX(Ingredients!F:F, MATCH(B2, Ingredients!A:A, 0)).)

Then, per dish in Dishes:

G2 (plate cost):  =SUMIF(Recipes!A:A, A2, Recipes!F:F) * (1 + C2) / B2 + D2
Enter fullscreen mode Exit fullscreen mode

Worked example: a batch costs 24.00 in ingredients, 5 % waste allowance, 8 portions, 0.35 of extras per plate: 24 × 1.05 / 8 + 0.35 = 3.50.

Formula 3 — food cost % (H2) 

=G2/E2
Enter fullscreen mode Exit fullscreen mode

At 14.00 net: 3.50 / 14.00 = 25.0 %, leaving 10.50 per plate before labour and overheads.

Formula 4 — the price that hits your target (I2) 

=G2/F2
Enter fullscreen mode Exit fullscreen mode

At a 28 % target: 3.50 / 0.28 = 12.50. Conditional formatting on "food cost % > target" shows which dishes to re-price or re-portion.

Keep it current

Supplier prices move; the menu does not. Once a month, update Ingredients!D from the invoices and the whole menu re-costs. Then compare with actual food cost for the month — (opening stock + purchases − closing stock) ÷ food sales. A gap of a few points usually points to portioning, waste, comps or theft rather than prices (a common rule of thumb, not a fixed threshold).

Try it without building it

The same formulas for one dish run in a free calculator, no sign-up, nothing stored: food cost and menu price calculator.

This article was created with the help of AI. The formulas and example numbers were checked by running them. I built the free calculator linked above.

Top comments (0)