Written by: JJ Tan, Founder, Jelly
Key Takeaways
- An Excel menu profitability spreadsheet uses three linked tabs, Ingredient Price List, Recipe Costing and Menu Matrix, to calculate ingredient costs, dish costs, food cost percentages and contribution margins for every menu item.
- Exact-match XLOOKUP or VLOOKUP formulas connect the price list to recipes, while yield adjustments convert pack prices into usable portion costs and prevent silent costing errors.
- Food cost percentage is calculated as total dish cost divided by ex-VAT selling price, and GP % is 1 minus that figure. UK VAT rules require prices to be entered ex-VAT for accurate benchmarking.
- The menu matrix classifies dishes as Stars, Plow Horses, Puzzles or Dogs based on popularity and contribution margin, so operators can see which items to protect, adjust, re-market or remove.
- Jelly automates invoice scanning, real-time cost updates and supplier price alerts, so the weekly manual refresh that makes Excel unsustainable for growing sites disappears.
Ingredient Price List, Recipe Costing And Menu Matrix: The Three Tabs
Build the workbook in this order so each tab has a single clear job. The tab names must be typed exactly as written below because the lookup formulas reference them by name.
- Ingredient Price List. This tab is the single source of price truth, so no price lives anywhere else in the workbook. Set up these columns in this order so the first four identify the ingredient, the next four carry the cost chain from pack price to usable cost, and Last Updated shows when you last checked the price: Ingredient, Supplier, Pack Size, Purchase Unit, Purchase Price (ex-VAT), Unit Cost (per g/ml/each), Yield %, Usable Unit Cost, Last Updated.
- Recipe Costing. This tab holds only a lookup that pulls the usable unit cost from the Ingredient Price List. Columns: Dish, Ingredient, Quantity, Unit, Yield-Adjusted Cost, Line Cost, Total Dish Cost, Selling Price (ex-VAT), Food Cost %, GP %.
- Menu Matrix. This tab holds only results pulled from the Recipe Costing tab. Columns: Dish, Units Sold, Popularity %, GP per Dish, Margin Category (Star / Plow Horse / Puzzle / Dog), Action.
The discipline of keeping prices in one place and pulling them everywhere else keeps the workbook maintainable. A single price update on the Ingredient Price List flows through to every linked recipe instantly, but only when no recipe tab contains a hard-coded price.
Build This Three-Tab Structure With Jelly
How To Link An Ingredient Price List To Recipes In Excel
The lookup formula connects the Ingredient Price List to every recipe line, and incorrect syntax produces silent errors that are worse than no formula at all.
XLOOKUP is available in Excel for Microsoft 365, Excel 2021, and Excel 2024, but is not available in Excel 2016 or Excel 2019. Where it is available, use:
=XLOOKUP(A4, 'Ingredient Price List'!A:A, 'Ingredient Price List'!H:H, "Not in price list", 0)
For older Excel versions, use VLOOKUP:
=VLOOKUP(A4, 'Ingredient Price List'!A:H, 8, FALSE)
In a worked example, cell A4 on the Recipe Costing tab contains “chicken breast”. The XLOOKUP searches column A of the Ingredient Price List for an exact match, then returns the value from column H, the Usable Unit Cost expressed as cost per gram. A 180g portion then multiplies cleanly: 180 × Usable Unit Cost = Line Cost.
The exact-match argument matters. The 0 in XLOOKUP and FALSE in VLOOKUP force an exact match. A symmetrically mangled ingredient key, where a normalisation step stripped the trailing ‘s’ from singular names such as ‘sea bass’ to ‘sea bas’, matched a reference row keyed on the same mangled string, and the pricing layer then silently returned a wrong container size, category and amount with no errors logged. The formula succeeds, the cell shows a number, and the dish cost is wrong.
Mixing units in the same column is a costly template error: entering flour as grams in one row and kilograms in the next silently inflates or deflates line cost by 1,000×. Standardise on the purchase unit and convert recipe quantities before entry.
Handle #N/A errors by supplying the fourth XLOOKUP argument, "Not in price list", so a new ingredient shows a readable label rather than breaking the dish total. When that label appears, add the ingredient to the Ingredient Price List rather than typing a price into the recipe tab. A typed price is invisible to the price list and becomes the first place the workbook goes stale.
Unit Conversions, Yield And Wastage
The conversion chain from pack price to usable portion cost is where most template workbooks introduce their largest errors. For a kg-priced pack, the formula is:
Unit Cost per g = Purchase Price (ex-VAT) / (Pack Size × 1000)
For a litre-priced pack:
Unit Cost per ml = Purchase Price (ex-VAT) / (Pack Size × 1000)
Yield adjustment then converts the raw unit cost into the cost of what is actually served:
Usable Unit Cost = Unit Cost / Yield %
In a worked example, sirloin at £18/kg with 20% trim and cooking loss (80% yield) becomes £22.50 per usable kg. A 200g portion costs £4.50, against the £3.60 the invoice implies. That gap is why ignoring yield is the single biggest accuracy error in template workbooks. Because every ingredient on the plate carries the same error, it compounds.
Yield sits at the bottom of a chain, running ingredient, sub-recipe, plate and menu matrix. A rounding error or inverted division at the ingredient level does not announce itself but quietly moves a dish into the wrong menu-engineering quadrant, so operators act on a misclassification.
Record trim, staff meals and spoilage as their own lines rather than burying them, so the gap between theoretical and actual food cost stays investigable. WRAP estimates food waste costs the UK hospitality and food service sector £3.2 billion a year, around £10,000 per outlet on average, waste that disappears into unexplained variance when it is not tracked explicitly.
How To Calculate Food Cost For A Menu In Excel
With usable unit costs in place, food cost for a menu is each dish’s total ingredient cost divided by its VAT-exclusive selling price, expressed as a percentage. GP % is 1 minus that figure. The menu total is the sales-weighted average across dishes, not a simple average, because a dish selling 200 covers a week carries far more weight than one selling 10.
The formulas, in order:
Line Cost = Quantity × Usable Unit CostTotal Dish Cost = SUM(Line Costs)Food Cost % = Total Dish Cost / Selling Price (ex-VAT)GP % = 1 - Food Cost %
A 30% food cost target means ingredient cost is 30% of the ex-VAT selling price, so the minimum viable net price is:
Selling Price (ex-VAT) = Dish Cost / 0.30
The Selling Price (ex-VAT) column must contain the menu price with VAT stripped out. Dividing cost by a VAT-inclusive price understates food cost by roughly five percentage points at 20% VAT. That error is consistent, so every dish looks more profitable than it is. The UKHospitality × Christie & Co Benchmarking Report 2022 put UK food gross margin at 67%, equivalent to a food cost of 33% of net sales. Benchmarking against a VAT-inclusive denominator produces a figure that is not comparable to that industry data.
Building The Menu Matrix: Stars, Plow Horses, Puzzles And Dogs
The menu matrix classifies every dish on two axes, popularity (units sold against expected share) and profitability (contribution margin per dish in money against the menu average). The profit axis uses contribution margin in money, price minus cost, not food cost percentage. Sorting on percentage pushes operators toward cheap dishes with attractive percentages that make very little money per cover.
Popularity threshold formula:
Popularity % = Units Sold ÷ Total Units Sold (within the same group or menu section) × 100
Compare each dish’s Popularity % and contribution margin per dish against the menu averages. A dish above both averages is a Star; above popularity but below margin is a Plow Horse; below popularity but above margin is a Puzzle; below both is a Dog.
Actions per quadrant:
- Stars — keep and protect, never discount.
- Plow Horses — cut cost or nudge price.
- Puzzles — re-market, rename or re-engineer.
- Dogs — remove or rebuild.
UK Specifics: VAT, Ex-VAT Costs And Delivery Commission
Enter purchase prices ex-VAT where input VAT is recoverable. Enter menu prices ex-VAT by dividing the menu price by 1.2 at the standard 20% rate. The VAT-correct pricing formula, working from a target food cost to a customer-facing price, is:
Menu Price (inc-VAT) = (Dish Cost / 0.30) × 1.2
VAT treatment varies by product and service. Eat-in food and drink and hot takeaway food are standard-rated at 20%. Cold takeaway food is generally zero-rated, but confectionery, crisps, ice cream, soft drinks and alcohol are always standard-rated. The VAT divisor is not always 1.2, so check HMRC VAT Notice 709/1 and HMRC VAT Notice 701/14 for the specific product and service.
For delivery, build a separate delivery menu tab that deducts platform commission before calculating GP. Delivery platforms such as Deliveroo and Uber Eats charge average commissions of 30%, squeezing restaurant margins. A dish returning 70% GP in the dining room returns roughly a third of the net price once commission is taken. Sushi Revolution uses Jelly to set separate target gross profits on dine-in and delivery menus to account for that commission. The result is actual gross profits 2–3% higher on average, evidence that setting separate target GPs for each channel keeps both profitable.
Keeping The Excel Menu Profitability Spreadsheet Accurate And Knowing When To Switch
A static workbook goes stale the moment a supplier price moves. Half of supplier list-price changes are followed by another change within 19 days, while a quarter change again within 6 days. The weekly maintenance routine has three steps:
- Refresh invoice prices on the Ingredient Price List.
- Refresh units sold from the POS export into the Menu Matrix.
- Re-check every dish flagged red on GP %.
Suppliers do not phone to announce price increases, the line simply rises on the invoice and gets paid because the delivery is already in the fridge. The NIQ and Prestige Purchasing Foodservice Price Index for June 2026 found UK hospitality food and drink prices rose 1.8% month on month, with broad increases across nearly all categories. That pattern means the refresh cadence is not optional.
The manual refresh becomes the bottleneck as sites multiply. One workbook per site, consolidated by hand, is where the model breaks. For multi-site restaurant groups, manual consolidation of each site’s data in Excel is a systemic source of error with potentially considerable consequences for profitability.
Jelly is the automated alternative in this software category. Because invoices are digitised line by line with no manual entry, dish costs and GP update live as new invoices land. The Price Alert feature provides daily notifications when a supplier changes a price, surfacing supplier price increases and decreases as invoices arrive rather than waiting for a weekly summary. Sales Mix then pulls item-level POS data from integration partners, so the margin reporting reflects what actually sold. Onboarding generates value in the first week, and the flat rate is £129/month per location with no per-user charge.
Amber, a Mediterranean restaurant in East London, saves £3,000–£4,000 per month using Jelly’s invoice automation, price alerts and real-time costing. Chef-Owner Murat Kilic says, “Jelly keeps my business alive.” Other tools that come up in this conversation are not really built for the job. Eat App is reservation and guest-management software, and its published feature set contains no dedicated recipe-costing or dish-level profitability functionality. Spreadsheet123 is a template vendor, while RestaurantOwner.com is primarily a membership platform providing restaurant management resources, training and tools, of which templates form only one part. Neither provides live dish-level margin tracking from invoices and POS data.
If you want to weigh the alternatives in more depth before committing, these two guides cover the trade-offs: Is Excel Effective For Restaurant Food Cost Tracking? and Chef Menu Costing Spreadsheet Alternatives Compared.
Stop Manually Updating Spreadsheets
Frequently Asked Questions
How To Calculate Profitability In Excel?
Pull the total dish cost from the Recipe Costing tab, divide it by the ex-VAT selling price, and subtract the result from 1 to give GP %. Multiply GP % by units sold to get total margin per dish over the period. The menu-level figure is the sales-weighted average of all dish margins, not a simple average across dishes.
Is There A Free Spreadsheet I Can Use For Food Costing?
Template vendors publish free workbooks, but they typically arrive without the lookup syntax, yield adjustment and UK VAT mechanics explained in this guide. The starting structure for a free template is covered in How To Build A Menu Costing Spreadsheet Template. A template is a starting point, and the formulas and conventions in this guide are what make it accurate.
What Are The Main Menu Pricing Methods?
Commonly cited menu pricing methods include cost-plus pricing (dish cost divided by target food cost percentage), target food cost percentage (working backwards from a margin target), markup on cost (multiplying cost by a fixed factor), contribution margin pricing (setting price to achieve a target cash GP per cover), competitive pricing (anchoring to market rates), psychological pricing (charm pricing, price anchoring, menu design), and menu engineering (using the Stars, Plow Horses, Puzzles and Dogs matrix to optimise the mix of dishes by popularity and margin). Different sources group and count these methods differently, so treat the list as a practical toolkit rather than a fixed set of seven.
How Often Should I Update A Food Cost Spreadsheet?
Update invoice prices on your top 20 ingredients every week, run the full matrix monthly, and update immediately when a supplier price moves more than a few percent on a key line. As the price index cited above shows, category-level price movement varies sharply month to month, so a single blanket annual review is insufficient for any kitchen where proteins, fish or coffee feature prominently on the menu.
Conclusion
A well-built Excel menu profitability spreadsheet uses three linked tabs, Ingredient Price List, Recipe Costing and Menu Matrix, connected by exact-match lookups, yield-adjusted costs and a contribution-margin matrix. Built correctly, it gives dish-level visibility that most operators have never had from a static file.
Jelly is the best menu profitability tool for UK restaurants, pubs and boutique hotels that want live dish-level margins, because it does the weekly refresh for you, with every invoice line scanned and every dish cost and GP updated as prices land.
Finish the workbook, run the weekly routine, and move to Jelly when the manual refresh becomes the constraint rather than the costing itself.
See How Jelly Replaces Your Spreadsheet
