Written by: JJ Tan, Founder, Jelly
Key Takeaways
- A three-tab spreadsheet structure (Invoice & Price Log, Recipe & Menu Costing Sheet, Weekly/Monthly Summary Dashboard) keeps supplier prices current and pushes changes through to dish costs automatically.
- UK restaurants typically work within food cost percentages of 28–35% of net (ex-VAT) revenue, with variance between theoretical and actual kept under 2 percentage points.
- Strip VAT by dividing gross menu prices by 1.20 to keep food cost percentage accurate (see Step 5 for detail).
- Link tabs with VLOOKUP or INDEX-MATCH so price updates in the Invoice Log recalculate portion costs and GP margins without manual re-entry.
- Jelly automates invoice scanning, price alerts, and live dish costing, which removes the manual entry burden that causes spreadsheets to go stale.
See how Jelly automates your invoice entry
Before You Begin
Before you build anything, gather the inputs you will need. Have the following to hand before opening Excel or Google Sheets:
- Recent supplier invoices (last two to four weeks)
- Your recipe and ingredient list
- POS sales data for the same period
- Current menu prices ex-VAT (net selling price)
- Access to Excel or Google Sheets
Treat this as a recurring process you will return to weekly. Because this is ongoing work, ownership usually sits with the owner, finance manager, or head chef, with input from both kitchen and finance. Budget 30 minutes to build the structure, then plan for ongoing weekly invoice entry, which suits most UK operators.
Why This Food Cost Spreadsheet Matters
As noted in the Key Takeaways, UK food cost benchmarks sit around 28–35% and should always be compared on a net-of-VAT basis. A well-built spreadsheet gives you margin visibility, faster reaction to supplier price changes, fewer manual errors, and harder data for supplier negotiations. Food cost percentage works best as a trend you track weekly or monthly and then investigate when it moves meaningfully against your target.
Step 1: Build the Invoice & Price Log (Tab 1)
This tab is the single source of truth for all supplier prices. Every other tab reads from it. When a price changes here, it flows through to dish costs automatically. The table below shows the columns you need and a worked example row for a sea bass fillet.
| Date | Supplier | Ingredient/SKU | Pack Size | Unit Price ex-VAT | Quantity | Line Total |
|---|---|---|---|---|---|---|
| 2026-09-30 | Example Supplier | Sea Bass Fillet | 1 kg | £14.20 | 5 | £71.00 |
Every ingredient you buy needs a current unit price ex-VAT recorded here, because this tab is the only place supplier prices live. To get there, you will need your recent supplier invoices. The result is a complete price log where every ingredient has a current unit price ex-VAT on record.
Step 2: Build the Recipe & Menu Costing Sheet (Tab 2)
This tab calculates the true cost of every dish and builds in waste and yield. The table below shows how a single dish line is structured, with the waste-adjusted portion cost feeding directly into the GP margin column.
| Dish Name | Ingredient | Quantity Used | Unit | Waste/Yield % | Waste-Adjusted Portion Cost | Total Dish Cost | Net Selling Price | GP Margin % |
|---|---|---|---|---|---|---|---|---|
| Sea Bass | Sea Bass Fillet | 500g | g | 56% | £12.68 | £14.50 | £22.00 | 34% |
Waste-adjusted portion cost formula:
=(Ingredient Cost Per Unit × Quantity Used) ÷ Yield PercentageA 500g sea bass yields roughly 280g of clean fillet, a 56% yield, so recipes must reflect actual ingredient yield or theoretical food cost will always sit below actual food cost. Ignoring yield makes your theoretical food cost unachievable, so build it into every recipe line. You will need your recipe and ingredient list plus current menu prices ex-VAT; the result is an accurate cost per portion and a visible GP margin for every dish.
Step 3: Build the Weekly/Monthly Summary Dashboard (Tab 3)
This tab tracks actual food cost percentage against theoretical and highlights variance. The table below shows a worked month, with the variance column highlighting the gap you need to investigate.
| Period | Total Food Cost | Total Food Sales ex-VAT | Food Cost % | Theoretical Food Cost % | Variance % |
|---|---|---|---|---|---|
| Oct 2026 | £16,000 | £50,000 | 32% | 30% | 6.67% |
Formulas:
Actual Food Cost % = (Opening Stock + Purchases − Closing Stock) ÷ Net Revenue × 100 Variance % = (Actual Food Cost % − Theoretical Food Cost %) ÷ Theoretical Food Cost % × 100Inputs required: POS sales data and stock counts for the same period. The successful result is a single dashboard that shows whether your food cost percentage sits within benchmark and where variance is hiding.
Step 4: Link the Tabs So Price Changes Flow Through
Linking the tabs keeps every recipe current when supplier prices move. Use VLOOKUP or INDEX-MATCH to pull unit prices from the Invoice & Price Log into the Recipe & Menu Costing Sheet.
VLOOKUP structure:
=VLOOKUP(Ingredient_SKU, 'Invoice & Price Log'!A:F, 5, FALSE)INDEX-MATCH structure (more robust):
=INDEX('Invoice & Price Log'!E:E, MATCH(Ingredient_SKU, 'Invoice & Price Log'!C:C, 0))Step by step:
- Reference the ingredient/SKU in Tab 2.
- For new ingredients, add them to Tab 1 first, then reference them in Tab 2.
- When a price changes in Tab 1, dish costs and GP margins in Tab 2 update automatically.
Even a well-built spreadsheet requires manual invoice entry to stay current. In Excel, chained preparations send the price change from ingredient to preparation and then to dish, and cross-tab references often break as soon as somebody inserts or moves a row. Enter invoices weekly to prevent dish costs from going stale.
Step 5: Strip VAT Correctly (UK Rule)
With your tabs linked, the next thing to get right is VAT. Divide the VAT-inclusive menu price by 1.20 to get the net selling price.
Worked example: a £24 menu price ÷ 1.20 = £20.00 net selling price.
Subtracting 20% from £24 gives £19.20, which understates your net selling price by £0.80 and makes your food cost percentage look better than reality. HMRC’s VAT Notice 700 and GOV.UK catering VAT notice 709/1 confirm that standard-rate VAT is 20% of the net price, so the VAT fraction is one-sixth of the gross. Dividing gross till figures by food cost can make a UK restaurant’s food cost percentage look about five points better than reality. Use exact terminology throughout and label figures as “ex-VAT” and “net selling price.”
Step 6: Run Variance Analysis (Theoretical vs Actual Food Cost)
Variance analysis shows whether your operation runs close to the margins your recipes promise. Use the variance formula from Step 3.
Variance % = (Actual Food Cost % − Theoretical Food Cost %) ÷ Theoretical Food Cost % × 100If your theoretical food cost is 30% and actual is 32%, you have a 2-percentage-point gap. On £50,000 monthly food sales, that is £1,000 of unexplained cost. Common sources include over-portioning, waste, spoilage, theft, and unrecorded supplier price rises.
Investigate by category such as proteins, produce, and dairy to find where the leak occurs. Best-in-class operators aim to keep the variance between actual and theoretical food cost below 1 percentage point, and a gap of 2 to 3 points can represent a material amount across a month or a multi-location group.
Step 7: Add UK Benchmark Ranges and Conditional Formatting
Benchmark ranges by service style help you see at a glance when food cost drifts out of line. Benchmark ranges below draw on Restaurant365’s published benchmarks and Orbisk’s food cost benchmark ranges. Use the row that matches your service style to set your conditional formatting threshold.
| Service Style | Food Cost % Range |
|---|---|
| Casual Dining / Pubs | 28–32% of net (ex-VAT) food revenue |
| Fine Dining | 30–35% |
| QSR / Fast Casual | 25–30% |
The table above sets the range for each service style. Treat anything above the top of your range as a red flag and set your conditional formatting accordingly so high food cost percentages turn cells red and variance stands out at a glance.
Ideal Food Cost Percentage for UK Restaurants
The benchmark ranges in Step 7 are the ones to apply. The key point is that your target depends on your service style and must always be compared on a net-of-VAT basis. Compare your actual percentage to your service-style benchmark using food sales ex-VAT only.
How to Calculate Food Cost Percentage in a Restaurant
The formula matches the one used in Step 3:
Food cost percentage = (Opening Stock + Purchases − Closing Stock) ÷ Food Sales ex-VAT × 100.
Once you have the result, compare it to the benchmark range for your service style.
Can AI Build This Spreadsheet for You?
You might wonder whether AI tools can shortcut this build. ChatGPT and Copilot for Excel can generate formulas and structure such as the VLOOKUP, the waste-adjusted portion cost formula, and the variance calculation. They cannot keep supplier prices current. Google Sheets offers easier collaboration for teams, and Excel has more robust conditional formatting. The fundamental problem remains: someone must enter every invoice line item. An out-of-date spreadsheet looks exactly like an up-to-date one, so it fails silently rather than loudly.
Common Mistakes and Troubleshooting
- Subtracting 20% instead of dividing by 1.20: This understates net selling price and makes food cost percentage look artificially low. Always divide gross by 1.20.
- Ignoring yield and waste: As Step 2 explains, ignoring yield makes theoretical food cost unachievable. Build yield percentages into every recipe line.
- Hard-coding prices instead of linking tabs: Price changes do not flow through. Use VLOOKUP or INDEX-MATCH.
- Forgetting to update the Invoice & Price Log: Dish costs go stale. Enter invoices weekly.
- Mixing VAT-inclusive and ex-VAT figures: Food cost percentage becomes distorted. Keep everything ex-VAT except the final gross menu price.
- Missing storage areas during stock counts: Estimating and skipping storage areas are the two biggest culprits for distorted calculations.
See Jelly in action for food cost control
When The Spreadsheet Stops Working
A spreadsheet reaches its limits at predictable thresholds. Six signals indicate when a spreadsheet stops being adequate: chained in-house preparations, more than one venue, more than one person editing the file, suppliers with variable prices, loss of trust in the figures, and frequent menu changes.
Vendor prices typically creep up 2–3% per quarter per item, and four quarterly increases of 2% compound to 8.2%; on $60,000 a month in purchasing, that is roughly $22,000 in added cost in year one. A spreadsheet catches none of this automatically.
Jelly solves this problem directly. Jelly gives growing restaurants, pubs, and hotels a simple way to manage food and beverage operations by automating invoices, inventory, and real-time menu profitability. Relevant capabilities include:
- Automated invoice scanning via photo or email, with every line item captured without manual entry
- Price Alert so you instantly see which ingredient prices have gone up or down, by how much, and from which supplier
- Live dish costing so dish costs and GP margins update in real time as invoices change ingredient prices
- Flash Report for a daily, weekly, or monthly view of GP margin
- POS integrations with complementary tools such as Square, EPOS Now, Toast, and Lightspeed
Jelly charges a flat rate of £129/month per location, onboards in the first week, and customers cut food costs by 3% on average in the first 3 months.
| Attribute | Manual Spreadsheet | Jelly |
|---|---|---|
| Price source | Manual invoice entry | Automated invoice scanning (photo or email) |
| Price change alerts | None | Price Alert flags every increase or decrease |
| Dish costing | Manual recalculation | Live dish costing updates in real time |
| POS integration | None | Integrates with complementary tools such as Square, EPOS Now, Toast, and Lightspeed |
| Multi-site visibility | Multiple files, conflicting versions | Centralised dashboard |
Talk to Jelly about automating food cost control
How to Measure Success
A correctly built and maintained three-tab spreadsheet produces three measurable outcomes:
- Food cost percentage within your service-style benchmark
- Variance under 2% between theoretical and actual food cost
- Price changes reflected in dish costs within one week of invoice receipt
Advanced Tips and Next Steps
Once the core three-tab system is running, three extensions add further value:
- Use Price Alert data, whether from your spreadsheet log or from Jelly, to negotiate supplier prices at contract renewal with a documented price history.
- Cost delivery menus separately. Factor in platform commission as an overhead before setting the delivery price. Base commission is typically 15–30%, but the blended all-in cost, once payment processing, marketing and advertising are added, typically lands at 25–35% of every marketplace order.
- Standardise recipes across multiple sites using a shared Invoice & Price Log. Note that a single spreadsheet becomes unwieldy once it is shared across more than three people or locations: four to six is a grey zone, and seven to ten requires a move to a database.
For further reading on formula construction and variance diagnosis, see the Excel Formula To Calculate Food Cost Percentage (UK Guide), the Food Cost Percentage Variance Analysis: Diagnose the Gap guide, and the Food Costing Template for Caterers: Stop Losing Margin article. The WRAP Guardians of Grub programme also provides a 7-day tracking sheet for kitchens to help build the food inventory counting habit.
Explore Jelly for multi-site food cost control
Frequently Asked Questions
Here are the questions UK operators ask most often when building and maintaining this spreadsheet.
How Do You Calculate Food Cost Percentage in a Restaurant?
Food cost percentage = (Opening Stock + Purchases − Closing Stock) ÷ Food Sales ex-VAT × 100. Calculate opening and closing stock at the same point in time, at the beginning and end of the same week or month. Use food sales only, net of discounts, refunds, and complimentary meals, and exclude drinks, service charges, and delivery fees. In the UK, always strip VAT from menu prices before using them as the sales denominator by dividing the gross till figure by 1.20 to get the net selling price.
Is 32.8% Food Cost Acceptable for a UK Restaurant?
For UK casual dining and food pubs, where the target is the 28–32% range shown in the benchmark table and anything above the top of that range is a red flag, 32.8% sits slightly above target but within acceptable limits. For QSR and fast casual operations, where 25–30% is typical, it would sit above benchmark. The more important point is whether your actual food cost percentage sits close to your theoretical food cost percentage. A 32.8% actual against a 30% theoretical represents a 2.8-percentage-point variance, which is worth investigating by category before concluding the percentage itself is the problem.
How Often Should I Update the Spreadsheet?
Enter invoices weekly. Run variance analysis weekly or monthly depending on your volume and margin sensitivity. Recalculate dish costs whenever a supplier price changes by more than a trivial amount; some operators use a supplier invoice price change above roughly 5% as the trigger for a full recipe review or re-cost. Weekly invoice entry is the single most important habit because a spreadsheet with stale prices produces misleading food cost percentages and makes variance analysis meaningless.
Who Should Own the Food Cost Spreadsheet?
Ownership typically sits with the owner, finance manager, or head chef, with cross-functional input between kitchen and finance. The person entering invoices must have access to supplier invoices as they arrive. The person reviewing variance must have access to POS sales data and stock count results. In practice, the head chef often owns recipe costing and stock counts, while the owner or finance manager owns the summary dashboard and benchmark comparison, and both need to see the same numbers.
What If I Have Multiple Suppliers or Multiple Sites?
For multiple suppliers, add all suppliers to the Invoice & Price Log with the supplier name in its own column. The VLOOKUP or INDEX-MATCH will pull the most recent price for each ingredient SKU. For multiple sites, a single spreadsheet becomes unwieldy quickly. Each site needs its own Invoice & Price Log, and consolidating them into a single summary dashboard requires manual copy-paste or complex cross-file references that break easily. At two or more sites, the manual maintenance burden typically exceeds the cost of purpose-built software. Jelly provides centralised visibility across locations at a flat rate of £129/month per site, with automated invoice scanning replacing manual entry entirely.
Conclusion
The three-tab build (Invoice & Price Log, Recipe & Menu Costing Sheet, and Weekly/Monthly Summary Dashboard) gives UK operators clarity, control, and repeatability within the constraints of a spreadsheet. Treat it as your starting point. Apply the UK VAT rule correctly by dividing by 1.20, link tabs with VLOOKUP or INDEX-MATCH, account for yield in every recipe, and run variance analysis weekly. When supplier price changes become too frequent, sites multiply, or the spreadsheet stops being trusted, Jelly provides a more robust long-term solution.
Book a Jelly demo for your venue
