Written by: JJ Tan, Founder, Jelly
Key Takeaways
- Food cost percentage uses the ex-VAT selling price, not the VAT-inclusive menu price, so you avoid overstating costs.
- Yield and waste percentages must be applied to each ingredient to reflect true usable cost per portion.
- Accurate dish costing highlights profitable items, flags supplier price changes, and supports timely menu adjustments.
- Regular weekly updates from supplier invoices keep your calculations reliable as prices move.
- Jelly automates invoice capture, live costing and price alerts, so growing restaurants can move beyond manual spreadsheets.
See How Jelly Automates Your Food Costing
What To Gather Before Building Your Sheet
Collect these items before opening a blank sheet:
- A recent supplier invoice with line-item prices
- The recipe or spec for one dish
- The current menu selling price as displayed to customers (VAT-inclusive)
- Access to Excel or Google Sheets
The head chef usually builds the sheet and the owner or finance manager reviews it. Costing a dish correctly takes roughly twenty minutes per dish when done with the purchase invoice open rather than from memory, according to hospitality advisory guidance. With a digital cost card, recosting a subsequent dish takes 6–8 minutes per recipe versus 35–45 minutes with a traditional manual spreadsheet. This is a recurring process because supplier prices drift constantly. A sheet that has not been updated in a month produces a food cost percentage that no longer reflects reality.
Why Accurate Dish Costing Protects Your Margin
Accurate dish costing shows which items are genuinely profitable and surfaces supplier price increases before they erode margin. It gives you the data to re-price or re-engineer a dish before month-end accounts arrive. A spreadsheet works well as a starting point for a single-site kitchen, and this guide explains where that approach reaches its limit.
Get A Personalised Food Costing Walkthrough
How To Calculate Food Cost Percentage In Excel
Follow these seven steps in sequence. Each step ends with a formula or a column decision you can apply immediately.
Step 1 — Set Up The Ingredient Line-Item Columns
Create the following columns across the top of your sheet:
| Column | Label | Formula | Notes |
|---|---|---|---|
| A | Ingredient | — | Name as it appears on the invoice |
| B | Purchase Unit | — | e.g. kg, litre, each |
| C | Pack Cost (£) | — | Total cost of the pack from the invoice |
| D | Pack Size | — | Weight or volume of the pack in the purchase unit |
| E | Unit Cost (£) | =C2/D2 | Cost per purchase unit, the key conversion cell |
| F | Quantity Used in Recipe | — | In the same unit as column B |
| G | Line Cost (£) | =E2*F2 | Cost of this ingredient in one portion |
Column E makes unit conversion automatic. Once you have a cost per purchase unit, multiplying by the recipe quantity in column F produces the correct line cost regardless of pack size.
Step 2 — Total The Dish Cost
Below your last ingredient row, enter:
=SUM(G2:G10)Adjust the range to match your actual ingredient rows. The result is your total ingredient cost per dish in £. Sanity-check it by manually pricing one or two high-value ingredients. If the total looks wrong, the error usually sits in a pack size or unit mismatch in columns C or D.
Step 3 — Add The Food Cost Percentage Formula
In a clearly labelled cell below the dish total, enter:
=(G12/B16)Cell G12 holds your total ingredient cost. Cell B16 will hold your ex-VAT selling price, which you set up in Step 4. Format the cell as a percentage via Format Cells > Percentage. The result is your food cost percentage for the dish.
Step 4 — Use Ex-VAT Selling Price
This step keeps your percentages honest. Under HMRC VAT Notice 709/1, all food and drink consumed on the premises where it is supplied is standard-rated at 20% VAT, with tap water the exception, and UK menu prices must be displayed VAT-inclusive. Dividing by the displayed menu price means you are dividing by a number that includes 20% tax owed to HMRC, not revenue you keep. The result is a food cost percentage that is overstated, because the gross price includes the 20% VAT owed to HMRC, which makes a healthy dish look unprofitable.
Use this formula:
Selling Price ex-VAT = Menu Price ÷ 1.2Worked example: a £14.50 menu price is £14.50 ÷ 1.2 = £12.08 ex-VAT. All food cost percentage and gross profit calculations must use £12.08, not £14.50.
In your sheet, add a labelled row for Menu Price (VAT-inc) and a second row for Selling Price ex-VAT with the formula =MenuPriceCell/1.2. Reference the ex-VAT cell in every subsequent calculation.
Step 5 — Add Yield And Waste Percentage
Add column H, labelled Yield %, and revise the line cost formula in column G to:
=( E2 * F2 ) / H2This divides the purchase cost by the yield percentage and produces the true cost per usable portion. Ignoring yield typically understates food cost by 4–8 percentage points, which comes straight out of profit.
Worked example: 1 kg of beef sirloin at £12.00/kg with a 75% yield after trimming costs £12.00 ÷ 0.75 = £16.00 per usable kg. Costing the dish at £12.00/kg understates the true ingredient cost by 33%.
Yield applies to trim loss, peel, cooking shrinkage and portioning waste. Typical yield percentages include: whole chicken 65–70%, chicken breast (skin on) 85–90%, beef sirloin (trimmed) 75–85%, whole salmon 50–60%, and salmon fillet (skinned) 85–90%. For produce, USDA data shows broccoli at approximately 39% trim loss, potato at 25%, and onion at 10%. Measure your own yields across 3–5 batches and average the results because supplier quality and season affect the figure.
Enter 1.00 (100%) in column H for any ingredient with no meaningful waste so the formula still works without dividing by zero.
Step 6 — Add Gross Profit £ And Gross Profit % Rows
Below your food cost percentage cell, add two further labelled rows:
Gross Profit £ = Selling Price ex-VAT − Total Ingredient Cost Gross Profit % = Gross Profit £ ÷ Selling Price ex-VATThese two figures show the absolute cash left per dish and the margin percentage at the same time. Tracking both metrics prevents decisions based only on percentage.
Step 7 — Calculate Selling Price From A Target Food Cost
To work backwards from a target food cost percentage to a menu price, use:
Selling Price ex-VAT = Total Ingredient Cost ÷ Target Food Cost % VAT-Inclusive Menu Price = Selling Price ex-VAT × 1.2Worked example using a 30% target: if total ingredient cost is £3.60, the ex-VAT selling price is £3.60 ÷ 0.30 = £12.00, and the VAT-inclusive menu price is £12.00 × 1.20 = £14.40. Round to a psychologically appropriate price point such as £14.50 or £14.95.
Get Help Building Your First Costing Sheet
UK Worked Example: Steak And Ale Pie
This example uses a recognisable British pub dish to show how the columns work in practice. All figures are in £ and metric units. The table below shows the ingredient lines. Only the beef chuck line is fully populated, and the remaining lines are left for you to complete with your own invoice figures.
| Ingredient | Pack Cost (£) | Pack Size | Unit Cost (£/kg) | Qty Used | Yield % | Line Cost (£) |
|---|---|---|---|---|---|---|
| Diced beef chuck | £22.49 | 2.5 kg | £9.00 | 0.200 kg | — | — |
| Ale (500ml bottle) | — | — | — | 0.100 litre | — | — |
| Shortcrust pastry | — | — | — | 0.120 kg | — | — |
| Onion | — | — | — | 0.080 kg | — | — |
| Stock, herbs, flour | — | — | — | — | — | — |
In this example, diced beef chuck (Sysco Classic Diced Beef Chuck Steak 95vl) is supplied in a 2.5 kg pack priced at £22.49 (a promotional price, equivalent to £9.00/kg). Enter your own invoice figures for the remaining lines, along with the yield percentages you measure in your kitchen.
Total Ingredient Cost per Dish: £3.39
In the Steak and Ale Pie example, the VAT-inclusive menu price is £13.95. Dividing by 1.20 removes standard-rate UK VAT at 20% and gives an ex-VAT selling price of approximately £11.63.
In the Steak and Ale Pie example, the gross profit % is calculated as ((£11.63 − £3.39) ÷ £11.63) × 100 = 70.9%, using the GP% = ((Selling Price − Food Cost) / Selling Price) × 100 formula.
At around 29%, this dish sits comfortably inside the 28–35% food cost range most UK restaurants target. If the result sits above 35%, consider options such as renegotiating the beef price, reducing the portion weight, raising the menu price, or substituting a lower-cost cut with equivalent yield. If it falls below 28%, check that yield percentages are realistic and that no ingredients have been omitted.
Common Mistakes And Troubleshooting
- Dividing by the VAT-inclusive menu price. Cause: using the printed menu price directly. Fix: divide by 1.2 first to get the ex-VAT selling price before any calculation.
- Ignoring yield and waste. Cause: using purchase weight as recipe weight. Fix: add the yield column (H) and divide every line cost by the yield percentage.
- Mixing purchase units and recipe units. Cause: entering recipe quantities in grams when the pack size is in kg. Fix: always derive unit cost in column E in a consistent unit, then enter recipe quantities in the same unit in column F.
- Supplier prices drifting without the sheet updating. Cause: nobody has a routine for updating column C when a new invoice arrives. Fix: assign one person to update pack costs weekly from invoices and date the sheet each time it is updated. A sheet that is not updated as soon as a supplier changes prices means you are calculating with an outdated cost for weeks while the menu price stays unchanged.
- Recipe changes not reflected in the sheet. Cause: the chef changes a portion weight or substitutes an ingredient without updating the spreadsheet. Fix: version the recipe tab with a date in the tab name (e.g. SteakAlePie_2026-09) and update it every time the spec changes.
Talk Through Your Current Costing Process
How To Measure Spreadsheet Success
The spreadsheet works when every dish on the menu has a food cost percentage, a gross profit £ and a gross profit % that the operator trusts and can explain. Use these directional indicators to assess whether the process functions well:
- Time to cost a new dish. Once the layout exists, a new dish should take under five minutes. Longer timings usually mean unit conversion errors or missing ingredient data.
- Speed of supplier price reflection. A price change on a key ingredient should appear in the affected dish costs within the same week. Longer delays signal that the update routine is failing.
- Ability to identify the most profitable dish. If answering that requires opening a supplier invoice rather than reading the gross profit £ column, the sheet is not yet complete.
Advanced Tips And Moving Beyond Spreadsheets
Once the sheet exists, two operational habits keep it useful over time. A weekly price-update routine assigns one person to update column C from the latest invoices in a single session. Recipe versioning adds a date to every tab when the spec changes so there is always a clear record of what was costed and when.
The spreadsheet approach has a defined operational ceiling. When a supplier adjusts pricing across 12 ingredients, every affected recipe must be updated manually. This problem multiplies across multiple locations buying from different suppliers at different rates. A decentralised spreadsheet is manageable at one location but becomes unworkable at two, because Excel offers no answer to consolidating inventory or comparing food cost across sites with consistent data. When the person who owns the sheet leaves, institutional knowledge of how it was built often leaves with them.
For a fuller strategic case against spreadsheets, see Jelly’s existing article on whether Excel is effective for restaurant food cost tracking.
Jelly is built for growing restaurants, pubs and boutique hotels that have reached that ceiling. Invoices are captured by email or photo. Every line item, including quantity, SKU and price, is digitised automatically. Dish costs update in real time as new invoices arrive, rather than waiting for a manual update.
The Price Alert feature flags every supplier price increase or decrease the moment it appears on an invoice and gives chefs the concrete evidence needed to negotiate credits or switch suppliers. The Cookbook lets chefs build dish recipes by clicking on ingredients already populated from scanned invoices, with all unit conversions and yield calculations handled automatically. This reduces the time to cost a new dish from an industry average of 28 minutes to around 3 minutes.
Jelly integrates natively with Square, EPOS Now, Lightspeed and Toast. It delivers live sales mix and margin data the moment a transaction completes, so the GP percentage on every dish reflects both current ingredient costs and actual sales volume. The platform costs £129 per month per location, with no variable charge per user or feature, and onboarding generates initial value in the first week.
See Jelly In Action For Your Venue
Frequently Asked Questions
How To Calculate A 30% Food Cost In Excel?
Divide total ingredient cost by 0.30 to get the ex-VAT selling price, then multiply by 1.2 to convert to a VAT-inclusive menu price. In Excel: =(TotalIngredientCostCell / 0.30) * 1.2. Round the result to a psychologically appropriate price point. If your total ingredient cost is £3.60, the ex-VAT selling price is £12.00 and the menu price is £14.40.
What Does A 33% Food Cost Percentage Mean?
It means 33p of every £1 of ex-VAT selling price goes to ingredient cost, leaving 67p gross profit per £1 of revenue. 33% sits in the upper half of that range for restaurants, though that range is a trade convention rather than a measured benchmark. On a bestselling dish, a 33% food cost warrants review because volume multiplies the margin impact. Even a 2–3 percentage point improvement on a high-seller is worth meaningful cash over a year.
How Do I Calculate The Cost Of A Menu In The UK?
Cost each dish individually using the column layout in this guide, ingredient by ingredient, with yield applied to each line. Once every dish is costed, weight each dish’s food cost percentage by its share of total sales (its sales mix) to produce a blended menu food cost percentage. Use ex-VAT selling prices throughout. Re-run the blended calculation whenever the sales mix shifts significantly, because a change in which dishes sell most changes the blended food cost even if no individual dish cost has moved.
Should I Use Ex-VAT Or Inc-VAT Selling Price?
Always ex-VAT. As explained in Step 4, dividing by the displayed menu price includes 20% tax in the denominator and overstates food cost percentage. Divide the menu price by 1.2 before using it in any food cost or gross profit calculation.
How Often Should I Update Supplier Prices In My Spreadsheet?
Update weekly if possible and monthly at minimum. Supplier prices drift constantly due to commodity markets, seasonal availability and inflation. A sheet that has not been updated in a month produces food cost percentages based on prices that may no longer exist. Assign one person to update pack costs from the latest invoices on a fixed day each week and date the sheet every time it is updated so there is always a clear record of when the figures were last verified.
Conclusion
The spreadsheet built in this guide covers the column layout (A through H), the food cost percentage formula using ex-VAT selling price, the VAT adjustment (menu price ÷ 1.2), the yield column that corrects for trim and cooking loss, and the gross profit £ and gross profit % rows that give a complete picture of dish profitability. For a single-site kitchen with a stable menu and a disciplined weekly update routine, this sheet works as a solid operational tool.
When supplier prices change faster than the sheet is updated, when a second site makes version control unworkable, or when the person who owns the sheet moves on, the spreadsheet becomes a liability. At that point Jelly’s automated invoice scanning, live dish costing and Price Alert features replace the manual process entirely, and the time saved and margin recovered make the transition straightforward.
Start Your Jelly Trial Conversation