Written by: JJ Tan, Founder, Jelly
Key Takeaways
- A reliable food cost calculator in Excel converts pack sizes to base-unit costs, applies yield, strips 20% UK VAT and includes delivery commission.
- A four-sheet workbook structure with Ingredients, Recipes, Menu Summary and Dashboard gives accurate cost per portion and clear margins for dine-in and delivery.
- Typical spreadsheet errors include wrong pack sizes, missing yield, comparing costs to VAT-inclusive prices and skipping delivery commission.
- Regular maintenance keeps figures accurate. Update volatile prices weekly, review the full sheet monthly and recalculate margins after price or commission changes.
- When manual updates take too long, Jelly scans invoices, updates dish costs and sends price alerts so margins stay current.
Before You Begin Your Workbook Build
Gather the following before opening Excel:
- Recent supplier invoices showing pack sizes and prices
- Recipe specs with portion weights in grams, millilitres or each
- POS sales data for the Menu Summary sheet
- Current selling prices inclusive of VAT
The head chef usually builds and maintains the sheet. The owner or finance manager reviews margins. Building a basic food cost calculator Excel template takes roughly 1–2 hours to set up, while more advanced versions with dashboards and live data feeds can take 6–12 hours. The sheet stays accurate only when ingredient prices are updated whenever a supplier invoice changes. Ingredient prices must be taken from the most recent vendor invoice, not from quoted prices or memory.
Why This Process Matters For UK Kitchens
A food costing spreadsheet that UK operators actually maintain delivers three operational advantages: margin visibility per dish, faster reaction to supplier price changes and hard data for supplier negotiations. The first advantage matters most. Small price increases in meat, dairy or cooking oil can significantly reduce the profitability of dishes that rely heavily on those ingredients, while other menu items are barely affected. Without per-dish margin visibility, some dishes quietly become far less profitable. A maintained sheet surfaces that drift before it compounds.
Check Your Dish Margins With Jelly
How To Create A Food Cost Spreadsheet: The Step-By-Step Build
Step 1: Set Up The Four-Sheet Workbook
Create four named sheets in a single Excel workbook:
- Ingredients, the master list of every ingredient with its supplier, pack size and unit cost
- Recipes, individual recipe cost cards pulling unit costs from the Ingredients sheet
- Menu Summary, one row per dish showing cost per portion, selling price, GP and food cost percentage
- Dashboard, a conditional-formatted overview that flags dishes above your target food cost percentage
Step 2: Build The Master Ingredient List
In the Ingredients sheet, create the following columns: Ingredient, Supplier, Pack Size (base units), Pack Cost (£), Unit Cost (£). Pack size must always be entered in base units such as grams, millilitres or each. Never enter “5kg” as “5”. Enter it as “5000”. Unit mismatch, such as treating a case price as a per-unit price, is the most common spreadsheet error in pack-size-to-unit-cost conversion and corrupts every downstream number in the recipe costing.
Step 3: Convert Pack Size To Unit Cost
This step converts every ingredient into a clean cost per base unit. The formula is:
Unit Cost = Pack Cost ÷ Pack Size (in base units)
Worked example: a 5 kg bag of chicken breast at £35.00 contains 5,000 g, so the unit cost is £35.00 ÷ 5,000 = £0.007 per gram. In Excel, if Pack Cost is in column D and Pack Size in column C, the formula in column E is =D2/C2. The standard starting point in professional recipe costing is to normalise every supplier quote into a common unit cost, such as cost per kilogram, per litre or per each, before using it in a recipe.
For catch-weight items such as cheese, meat and fish, the pack size is a fact about one delivery rather than about the product. Store these per kilo and update the pack cost from each invoice.
Step 4: Build The Recipe Cost Card
In the Recipes sheet, create columns: Ingredient, Quantity Used (base units), Unit Cost (£), Line Cost (£). Pull unit cost from the Ingredients sheet using =VLOOKUP(A2,Ingredients!A:E,5,0). Line cost is =B2*C2.
At the bottom of the card, add three summary lines:
- Total Recipe Cost =
=SUM(D2:D[last row]) - Cost Per Portion = Total Recipe Cost ÷ Number of Portions
- Food Cost Percentage = (Cost Per Portion ÷ Selling Price ex-VAT) × 100
Worked example: a lamb shoulder dish with a total recipe cost of £18.40 producing 4 portions costs £4.60 per portion. At a VAT-exclusive selling price of £16.67 (see Step 6), food cost percentage is (£4.60 ÷ £16.67) × 100 = 27.6%. UK operators typically target a food cost of 28–35% depending on venue type.
Step 5: Apply Yield And Waste Adjustment
Worked example: beef trimmed to 800 g usable from a 1,000 g as-purchased weight has an 80% yield. If the as-purchased cost is £0.022 per gram, the true cost per usable gram is £0.022 ÷ 0.80 = £0.0275. Typical yield percentages include whole chicken at 60–65%, beef tenderloin at 70–80%, onions at 88–90% and leafy greens at 70–80%. Add a Yield % column to the Ingredients sheet and adjust the Unit Cost formula to =D2/(C2*E2).
Step 6: Handle UK VAT And Menu Pricing
UK hospitality businesses must add standard 20% VAT to any food and drink prepared for catering or takeaway, including meals eaten in a restaurant. UK menu prices are VAT-inclusive by law for consumer-facing pricing, so the price on the menu already contains VAT.
Strip VAT before calculating food cost percentage:
Selling Price ex-VAT = Menu Price (inc-VAT) ÷ 1.20
Example: a £20.00 menu price gives a VAT-exclusive selling price of £16.67. Using the VAT-inclusive sticker price directly understates the true food cost percentage. Comparing a £4.60 ingredient cost against a £20.00 inc-VAT price gives 23.0%. The correct figure against £16.67 ex-VAT is 27.6%. That four-point gap flatters the margin and leads to mispriced dishes.
Step 7: Adjust For Delivery Commission
Delivery platforms such as Deliveroo and Uber Eats charge average commissions of 30%, which squeezes restaurant margins. A dish that hits a healthy food cost percentage in-house can fail on a delivery platform once commission is applied.
Use this formula for effective selling price after commission:
Effective Selling Price = Menu Price ex-VAT × (1 − Commission Rate)
Example: a £20.00 menu price gives £16.67 ex-VAT. At 30% commission, the effective selling price is £16.67 × 0.70 = £11.67. Food cost percentage on the delivery channel becomes (£4.60 ÷ £11.67) × 100 = 39.4%, which sits well above the target range set out in Step 4. All three major UK delivery platforms charge 20% VAT on top of their commission. A 30% commission therefore costs 36% of the order value once VAT is added. VAT-registered restaurants can reclaim this input VAT, but only if commissions are recorded as a separate expense with the VAT element correctly identified.
Sushi Revolution uses separate target gross profits on dine-in and delivery menus, accounting for 30% delivery commissions, resulting in actual gross profits 2–3% higher on average. Build a separate delivery column in the Menu Summary sheet that recalculates food cost percentage using the commission-adjusted effective selling price.
Step 8: Build The Menu Summary And Dashboard
The Menu Summary sheet needs five columns for every dish: Cost Per Portion, Selling Price ex-VAT, GP Per Portion, Food Cost % (dine-in) and Food Cost % (delivery). In the Dashboard sheet, apply conditional formatting to highlight any dish where food cost percentage exceeds your target. Use red for above threshold and green for within range. This layout gives a single-view margin check across the full menu.
How Do I Calculate Food Cost In Excel?
If you only remember one thing from the eight steps above, make it the core formula. Total recipe cost divided by number of portions gives cost per portion. Cost per portion divided by the VAT-exclusive selling price, multiplied by 100, gives food cost percentage. In formula terms: =(Cost_Per_Portion/Selling_Price_ExVAT)*100. The full build follows the eight steps above: set up the four-sheet workbook, build the ingredient list, convert pack sizes to unit costs, build the recipe cost card, apply yield adjustment, strip VAT from the selling price, adjust for delivery commission and pull everything into the menu summary.
Free Templates Worth Downloading And Fixing
Several UK-facing template sources are commonly referenced for food costing spreadsheets, including UK Sheets, CaterCost, Marji and AFLUYO. Each provides a starting structure, but none include the pack-size-to-unit-cost conversion formula, yield adjustment, VAT stripping or delivery commission columns covered in this guide. Use this guide as the instruction manual for populating, correcting and maintaining those files. The formulas in Steps 3 through 7 apply directly to any template’s ingredient and recipe sheets.
Common Mistakes And Troubleshooting
Most problems trace back to a few repeat errors. Use this list as a quick diagnostic when numbers look wrong.
- Wrong Pack Size Unit: Entering 5 instead of 5,000 for a 5 kg bag skews every unit cost and every downstream recipe cost. Always enter pack size in grams, millilitres or each.
- Missing Yield Adjustment: Costing on as-purchased weight understates cost per portion. A beef sirloin bought at £22.00/kg that loses 25% when trimmed actually costs £29.33 per usable kilogram.
- Comparing Cost ex-VAT To Price inc-VAT: This practice overstates margin by several percentage points on every dish. Always divide the menu price by 1.20 before running the food cost percentage formula.
- Ignoring Delivery Commission: A dish priced for a 30% food cost in-house can exceed the delivery margin range when a platform charges 30% commission. Build a separate delivery column.
- Stale Ingredient Prices: If recipes are costed against ingredient prices from three months ago, the theoretical food cost is not accurate and neither is any analysis that flows from it.
Get A Live Margin Check For Your Menu
Where The Spreadsheet Breaks Over Time
The workbook above is accurate on the day it is built. It begins to drift the moment a supplier changes a price and the sheet is not updated. If a recipe cost was set six months ago and key ingredient prices have moved since then, theoretical cost is understated while actual cost reflects current prices. That gap compounds silently until a period-end review surfaces it, often weeks after the margin has already eroded.
Multi-supplier SKUs for the same ingredient create a second failure point. When two suppliers provide the same item at different prices, the recipe must point at the correct SKU for the current week’s delivery. Manual version control across dozens of ingredients and multiple suppliers is where most operators lose confidence in the sheet.
Delivery commission adds a third layer. Thirty per cent comes off a gross margin that was never thirty per cent to begin with. A delivery column in the Menu Summary helps, but only if it is recalculated every time ingredient prices or commission rates change.
A practical maintenance routine starts with the ingredients that move most. Update volatile ingredient prices such as meat, fish and dairy weekly. Once a month, review the full ingredient list to catch slower drift. At each review, flag any dish where food cost percentage has moved more than two points since the last review. High-volume restaurants benefit from weekly reviews of inventory, purchasing, waste and menu performance.
How To Measure Success Of Your Sheet
The workbook is functioning correctly when every dish has a live cost per portion, food cost percentage is visible per dish for both dine-in and delivery channels and any supplier price change is reflected in the sheet within one week. Directional indicators of a well-maintained sheet include fewer invoice discrepancies, faster menu repricing decisions when a key ingredient rises and the ability to enter a supplier negotiation with specific price-change data rather than estimates.
Advanced Tips And Next Steps
Once the single-site workbook is stable, extend it by standardising recipe cards across additional sites and reviewing the Menu Summary monthly against POS sales data. This review highlights which dishes are high-cost and low-volume. Connecting the sheet to POS export data allows a basic sales mix analysis by multiplying GP per portion by covers sold to rank dishes by total GP contribution rather than food cost percentage alone.
At the point where the workbook spans multiple sites, dozens of suppliers and weekly invoice volumes that make manual price updates impractical, most operators look for a system that updates ingredient costs automatically from scanned invoices. Jelly automates invoice scanning, live dish costing and price alerts so margins stay current without manual updates. What used to take 28 minutes to cost a single menu item now takes 3 minutes. The comparison below shows how the three main approaches differ on time and effort.
| Costing Method | Time To Cost One Dish |
|---|---|
| Manual Spreadsheet (Built From Scratch) | ~28 minutes |
| Downloaded Template | Recipe Cost Calculator states that most people get their full ingredient list imported in under 30 minutes |
| Automated Platform (Jelly) | ~3 minutes |
See How Jelly Automates Your Costing
Frequently Asked Questions
How Often Should I Update A Food Cost Calculator Excel Template?
Update volatile ingredients such as meat, fish and dairy weekly. Run a full ingredient list review monthly. Any supplier price change above 5% on a high-cost ingredient should trigger an immediate update to the Ingredients sheet, because that change flows through to every recipe that uses the ingredient. Leaving prices stale for more than a month means the food cost percentages on the Menu Summary no longer reflect what the kitchen is actually spending.
How Do I Handle An Ingredient Supplied By More Than One Supplier?
Create a separate row per supplier SKU in the Ingredients sheet, labelled clearly with the supplier name and pack size. Point the recipe line at the SKU you are actually buying from in the current week. Review which SKU is cheapest per base unit quarterly, and update the recipe VLOOKUP reference when you switch suppliers. This approach keeps the recipe cost accurate without deleting historical price data.
Should I Cost Dishes Ex-VAT Or Inc-VAT?
Always calculate food cost percentage against the VAT-exclusive selling price. Divide the inc-VAT menu price by 1.20 to strip 20% VAT before running the formula. UK menu prices must be VAT-inclusive for consumer-facing display, so the number on the menu is not the revenue the kitchen earns. One sixth of it belongs to HMRC. Comparing ingredient cost against the inc-VAT price overstates margin by several percentage points on every dish.
How Do I Account For Delivery Commission?
Reduce the effective selling price by the commission rate before calculating food cost percentage. The formula is: Effective Selling Price = Menu Price ex-VAT × (1 − Commission Rate). Then recalculate food cost percentage using that reduced figure. A dish that passes at 28% food cost in-house can exceed 39% on a platform charging 30% commission. Build a separate delivery column in the Menu Summary sheet and treat dine-in and delivery as two separate margin calculations for every dish.
What If My Recipe Uses A Sub-Recipe?
Cost the sub-recipe, such as a sauce, a stock or a dressing, as its own recipe card in the Recipes sheet, calculating its cost per unit produced (per litre, per 100 g, per portion). Then treat a measured quantity of that sub-recipe as a single ingredient line in the parent dish, using its cost per unit as the unit cost. This structure keeps the parent recipe card clean and ensures that any price change to a sub-recipe ingredient flows through automatically to every dish that uses it.
Conclusion
A food cost calculator Excel template built across four sheets, Ingredients, Recipes, Menu Summary and Dashboard, with pack-size-to-unit-cost conversion, yield adjustment, VAT stripping and delivery commission columns gives UK operators a true cost per portion and real margin visibility per dish. As noted earlier, setup takes 1–2 hours for a basic version, with more time required for dashboards and live data feeds. The discipline of maintaining it determines whether it stays useful. When price drift and multi-site complexity outgrow the spreadsheet, Jelly automates invoice scanning, live dish costing and price alerts so every recipe cost updates with each new invoice.
See How Jelly Works For Growing UK Kitchens
Read Next
- Restaurant Food Costing Excel Workbook for UK Margins
- Food Cost Calculator With Inventory Tracking: UK Restaurants
- Excel Formula To Calculate Food Cost Percentage (UK Guide)
- How to Master Recipe Costing: A Step-by-Step Guide for UK Professional Kitchens
- Recipe Cost Calculator App: Protect UK Kitchen Margins
