How To Build A Menu Costing Spreadsheet Template

Written by: JJ Tan, Founder, Jelly

Key Takeaways

• A menu costing spreadsheet template stays accurate only when ingredient prices are updated every time a supplier invoice arrives.
• The four-tab structure (Ingredients Master, Recipe and Sub-Recipe, Menu Summary, Unit Conversion) keeps calculations consistent and prevents unit mismatches.
• Always use VAT-exclusive selling prices in food cost and GP formulas to avoid overstating margins by around 5.5 percentage points.
• Calculate yield and wastage sequentially for trim loss and cooking loss, then apply them to batch prep sub-recipes so costs flow automatically to every dish.
• Jelly automates invoice scanning, price alerts and recipe costing, cutting costing time dramatically.

See How Jelly Keeps Costs Live

Now that you know what the template should achieve, gather what you need to build it.

Before You Begin: What You Need On The Desk

Before you open Excel or Google Sheets, collect these items:

• Recent supplier invoices with pack sizes and prices
• Recipe specs with portion sizes in grams or millilitres
• Batch and prep recipes for sauces, stocks and dough
• Current menu selling prices (VAT-inclusive, as printed)
• POS sales data if you plan to add a sales mix layer later

Ownership stays clear from the start. The head chef owns recipe and yield data. The owner or finance manager owns selling prices, VAT treatment and GP targets. Both should sign off before the template goes live.

A basic menu costing spreadsheet template usually takes an evening to build. It then needs ongoing price maintenance. Every time a supplier invoice arrives with a changed line-item price, someone must update the sheet. That maintenance burden is real and worth flagging early, because it is where many templates quietly fail.

Talk To Jelly About Live Costing

Step 1: Set Up The Four-Tab Architecture Of A Menu Costing Spreadsheet Template

The same four-tab structure works in Excel and Google Sheets. Google Sheets users can use ARRAYFORMULA and VLOOKUP for dynamic lookups. Excel users can use fill-down and XLOOKUP. The logic stays identical in both tools.

Tab 1: Ingredients Master Sheet

Use one row per SKU. Columns should include:

• Supplier name
• Pack description (e.g. “Beef brisket, 5 kg case”)
• Pack size in base units (grams or millilitres)
• Pack price (ex-VAT, from the invoice)
• Cost per gram or ml (formula: pack price ÷ pack size)
• Last updated date

The last updated date column serves as the earliest warning that a row has gone stale.

Tab 2: Recipe And Sub-Recipe Sheet

Use one section per dish, with sub-recipe rows for sauces, dough and batch prep that feed into dishes as ingredients. Columns should include:

• Dish name
• Ingredient name (linked to Tab 1 via VLOOKUP or XLOOKUP)
• Quantity used in base units (grams or ml)
• Wastage or yield percentage
• Cost per portion (formula: cost per gram × quantity × (1 + wastage %))

Sub-recipes such as a red wine jus, a batch of dough or a house sauce should be costed as their own rows and then referenced as an ingredient in the dish. A change in the sauce’s raw ingredient cost then flows automatically to every dish that uses it.

Tab 3: Menu Summary Sheet

Use one row per dish. Columns should include:

• Dish name
• Portion cost (pulled from Tab 2)
• Selling price ex-VAT
• GP per serve (selling price ex-VAT minus portion cost)
• GP percentage
• Target GP percentage
• Flag column (conditional formatting or IF formula to highlight dishes below target)

Tab 4: Unit Conversion Table

Build a reference table covering kg to g, litres to ml and case to each. This tab is the single place where pack-size conversion logic lives. The Ingredients Master and Recipe sheets then calculate cost per gram or ml consistently. Without this tab, unit mismatches creep in silently and corrupt formulas downstream.

See How Jelly Automates The Four Tabs

Step 2: Core Formulas For Menu Costing

Four formulas drive the entire template. In plain text:

• Cost per portion = (pack price ÷ pack size in base units) × quantity used × (1 + wastage %)
• Food cost % = portion cost ÷ selling price ex-VAT × 100
• GP per serve = selling price ex-VAT − portion cost
• Suggested selling price = portion cost ÷ target food cost %

The UK layer that most template downloads miss is simple. Always use the selling price excluding VAT in these formulas. HMRC’s Tax Confident guidance for hospitality confirms that meals eaten in a restaurant or café, hot and cold, carry the standard 20% VAT rate. To get the ex-VAT figure, divide the VAT-inclusive menu price by 1.20. A £12.00 menu price becomes £10.00 ex-VAT, with £2.00 of VAT. Mixing a VAT-inclusive price into a food cost percentage calculation is the most common error UK operators make and it overstates the margin by around 5.5 percentage points.

A temporary 5% VAT rate for qualifying children’s meals applied from 25 June to 1 September 2026 and has since expired. Standard-rated items now revert to 20%. For further detail on catering VAT liability by item type, HMRC VAT Notice 709/1 covers catering and takeaway food treatment in full.

Step 3: How To Calculate Food Cost Percentage In Excel

1. Enter the pack price and pack size in the Ingredients Master (Tab 1).
2. Calculate cost per gram or ml with a simple division formula: pack price ÷ pack size.
3. Pull that cost into the Recipe sheet (Tab 2) with VLOOKUP (Excel) or VLOOKUP/ARRAYFORMULA (Google Sheets).
4. Multiply by quantity used and apply the wastage percentage: cost per gram × quantity × (1 + wastage %).
5. In the Menu Summary sheet (Tab 3), divide portion cost by the VAT-exclusive selling price and format the cell as a percentage.

Watch Jelly Calculate Food Cost In Seconds

Step 4: Unit Conversion And Pack-Size Maths

Accurate costing depends on turning a case price into a cost per gram or millilitre before you use it in a recipe. Without this step, the Ingredients Master fills with pack prices that you cannot compare or combine.

The maths stays straightforward. A 5 kg case of an ingredient at £18.50 gives a cost per gram of £18.50 ÷ 5,000 = £0.0037 per gram. That figure goes into the cost-per-gram column in Tab 1 and flows into every recipe that uses the ingredient.

The Unit Conversion tab (Tab 4) is the single place this logic lives. If a supplier switches from 5 kg cases to 4 kg cases, you update one row in Tab 4 and the change flows through. Without a dedicated conversion tab, operators end up with conversion maths buried inside individual recipe rows. When a pack size changes, every affected row must then be found and corrected by hand.

Step 5: Yield And Wastage On Batch Prep

Yield loss happens in two stages: trim loss during raw preparation and cooking loss through evaporation, moisture loss and fat rendering during heat treatment. The correct approach is to calculate these sequentially, trim yield first and cooking yield second, because the combined yield is the product of the two, not their simple addition. For example, a 78% trim yield followed by an 80% cooking yield gives a combined AP-to-served yield of 62.4%, not 58%.

For batch prep such as a red wine jus, a stock or a sauce base, cost the complete batch and divide by the finished weight or number of portions, not the raw input quantity. A sauce that reduces from 2 litres to 500 ml goes from £3.00 per raw litre to £12.00 per finished litre; a 30 ml spoon costs 36p, not the 9p the raw volume implied.

In the spreadsheet, each sub-recipe should be its own costed row in Tab 2, with its finished cost per gram or ml calculated from the batch. That row is then referenced as an ingredient in every dish that uses it. When the sauce’s raw ingredient costs change, the dish costs update automatically.

Apply a wastage percentage to batch prep lines rather than guessing at the finished portion cost. A 6% prep wastage allowance on a sauce batch means the effective quantity consumed is grossed up to cover it, so the recipe carries the real cost of what leaves the kitchen.

Automate Yield And Wastage Calculations

Step 6: Worked Example For A Pub Dish From Invoice To Menu Price

This worked example costs a braised beef brisket dish from invoice to menu price. It shows how each cost layer, from pack price through trim loss, cooking loss, sub-recipes and accompaniments, builds to a final portion cost and GP. The table tracks the running total at each stage so you can see exactly where the money goes.

To answer the 40% food cost question directly, if the same portion cost of £2.89 were to represent 40% of the ex-VAT selling price, the required selling price ex-VAT would be £2.89 ÷ 0.40 = £7.23 ex-VAT (£8.67 VAT-inclusive). At that price, GP per serve would be £4.34, a significantly thinner margin. The UKHospitality × Christie & Co Benchmarking Report 2022 found food gross margin of 67% across its survey, equivalent to a food cost of 33% of net sales, with all eight market segments falling between 65.9% and 69% food GP. A 40% food cost sits materially above that range and would require investigation.

For comparison, in Marji’s worked example, the cost of a 5 oz cooked portion of braised beef brisket flat is $3.17, based on a hypothetical $7.00/lb raw price and a 69% USDA braising yield.

Get Live GP Per Dish Without The Spreadsheet

Step 7: The 7 Menu Pricing Methods In Brief

Seven recognised methods appear in hospitality pricing. The menu costing spreadsheet template supports the first three directly. The remaining four rely on judgement or data outside the sheet.

• Cost-plus: Divide portion cost by target food cost percentage to find the minimum viable selling price ex-VAT. Directly supported by the template.
• Margin/GP-based: Set the selling price to achieve a target GP percentage or GP per serve in pounds. Directly supported by the template.
• Mark-up: Apply a multiplier to ingredient cost (for example 3.3× for a 30% food cost target). Directly supported by the template.
• Psychological pricing: Use price endings (.95, round numbers) to signal positioning. This method uses judgement and the template provides the cost floor, not the final display price.
• Competitive pricing: Anchor prices to comparable local venues. This method requires external market research outside the sheet.
• Bundle or menu mix pricing: Price combinations or sets to drive volume and blended margin. This method requires POS sales mix data alongside the template.
• Dynamic or delivery-adjusted pricing: Adjust prices for peak periods or delivery platform commissions. This method requires POS integration and platform data beyond the template’s scope.

These methods give you the pricing logic, and they all rely on the template staying accurate. The next section covers the points where that accuracy breaks down.

Where The Menu Costing Spreadsheet Template Stops Working

A well-built template gives a strong starting point. The following points show where it stops being worth maintaining.

Supplier price creep. Vendor prices typically creep up 2–3% per quarter per item, with higher movements on proteins. Every invoice means manually updating the Ingredients Master. A sheet that is a week out of date already misleads. Under a manual spreadsheet method, ingredient prices are updated on average only every 4 to 7 months per recipe, while costs move far more frequently.

Time cost. Manual recipe costing takes 15–20 minutes per recipe for experienced staff. Costing dishes by hand in a spreadsheet competes directly with service and menu development, the work that actually generates revenue.

Version drift across sites. Multiple copies of the template diverge the moment anyone saves a local copy. Nobody knows which version is the source of truth. Comparing food cost across sites becomes meaningless when each site’s sheet has been edited independently.

VAT and GP confusion. Manual sheets make it easy to mix VAT-inclusive and VAT-exclusive figures. Using a VAT-inclusive price as the denominator in a food cost percentage calculation overstates the margin by around 5.5 percentage points on a standard-rated dish. As noted earlier, this overstates margins by around 5.5 percentage points and makes the business look more profitable than it is.

Jelly is the logical next step for operators who have understood the build and hit these limits. Jelly scans every invoice line item automatically and updates ingredient costs as invoices arrive, keeping dish costs and GP margins live without manual data entry. The Price Alert feature flags every supplier price increase or decrease, giving operators the evidence needed to negotiate credits or switch suppliers. The Cookbook lets chefs build dishes directly from scanned ingredients, with all unit conversions and wastage calculations handled automatically, reducing the time to cost a menu item from around 28 minutes to around 3 minutes. POS integration with Square, Lightspeed, EPOS Now and Toast brings sales mix and real-time margins into the same platform. Jelly charges a flat £129 per month per location, with no variable charges per user or feature, and onboarding generates initial value in the first week.

For operators building on a pub-specific context, the Pub Menu Costing Spreadsheet: Track GP & Food Costs guide covers that build in detail. For a broader comparison of spreadsheet alternatives, see Chef Menu Costing Spreadsheet Alternatives Compared.

See How Jelly Solves The Maintenance Burden

How To Keep Your Menu Costing Spreadsheet Template Accurate

If you are sticking with a spreadsheet for now, these three habits will keep it accurate for as long as possible. These habits form a simple cycle: update prices immediately, record when you did it and review the impact weekly.

• Update the Ingredients Master the day a supplier invoice arrives. A price change that sits unrecorded for seven days means every dish costed from that ingredient is wrong for seven days of service.
• Date-stamp every price change. The last updated date column in Tab 1 is the audit trail. Without it, there is no way to know whether a cost figure reflects this week’s invoice or last quarter’s.
• Review dishes flagged below target GP weekly. The flag column in Tab 3 exists to surface problems before they compound. A dish running 5 percentage points above target food cost for four weeks is a significant, recoverable loss, but only if it is caught in week one.

This discipline keeps a template working rather than stale. It is also the first thing to slip when kitchens get busy, which is precisely when accurate cost data matters most.

Keep Costs Accurate Without Manual Updates

Frequently Asked Questions

Is There An App To Calculate Food Cost?

Software like Jelly automates invoice-driven costing, updating dish costs and GP margins as invoices arrive rather than relying on manual data entry. The Price Alert feature flags every supplier price movement, and the Cookbook builds dishes from scanned ingredients with unit conversions handled automatically. For a broader overview of when spreadsheets stop being the right tool, see Free Menu Costing Software: When Spreadsheets Stop Working.

Should I Use Excel Or Google Sheets For A Menu Costing Spreadsheet Template?

Either tool works. Google Sheets suits multi-site sharing because it lives in the cloud, updates in real time for all users and requires no file distribution. Excel suits operators who need complex formula structures, offline access or integration with existing Windows-based finance tools. The four-tab architecture described in this guide works identically in both. The only difference is the specific functions used for lookups (XLOOKUP in Excel, VLOOKUP or ARRAYFORMULA in Google Sheets).

How Often Should I Update Ingredient Prices?

Update prices on every invoice, without exception. At an absolute minimum, update weekly. Proteins and seafood can move weekly or bi-weekly. Produce shifts with seasons and weather. Dairy moves monthly. Dry goods move quarterly. Waiting for a monthly review means weeks of margin erosion are already locked in before anyone sees the number. The practical discipline is to treat the Ingredients Master update as part of the goods-received process, not a separate admin task.

What Is A Reasonable Food Cost Percentage For A UK Pub Or Restaurant?

The directional planning range for most established UK full-service operations sits roughly between 28–35% food cost, equivalent to 65–72% food GP. As mentioned earlier, the UKHospitality × Christie & Co benchmark found food GP between 65.9% and 69% across all segments. These are survey averages from a specific period, not universal targets. The right figure for any individual site depends on its menu mix, average spend per cover, fixed cost structure and whether food and drink revenue are tracked separately. Food cost percentage alone does not indicate dish profitability. GP in pounds per cover matters as much as the percentage.

Try Jelly’s Invoice Scanning For Free

Conclusion

A menu costing spreadsheet template built on four tabs, Ingredients Master, Recipe and Sub-Recipe, Menu Summary and Unit Conversion, with VAT-exclusive formulas and disciplined yield maths, gives a strong starting point. It will give accurate GP per serve on the evening you build it. It will keep giving accurate numbers as long as someone updates the Ingredients Master every time an invoice lands.

That maintenance discipline sets the real constraint. When kitchens get busy, price updates slip. When a second site opens, template versions diverge. When a supplier creeps a price by 3% across a dozen line items, the sheet misses it until month-end. At that point, the maintenance burden has simply outgrown what a spreadsheet can realistically sustain.

Automation becomes the next step when that happens. Jelly scans every invoice line item, updates ingredient costs as invoices arrive and keeps dish costs and GP margins live, at a flat £129 per month per location, with value generated in the first week of onboarding.

Start Your Free Trial Today