Written by: JJ Tan, Founder, Jelly

Key Takeaways

  • A restaurant stock control Excel workbook needs three core tabs: Item Master, Stock Count, and Summary/COGS, with all costs stored ex-VAT and consistent units throughout.
  • Build the Item Master first with standardised names, pack sizes, recipe units, yield percentages and par levels before adding any formulas.
  • Use XLOOKUP, SUMIFS, data validation and conditional formatting to automate cost calculations, prevent free-text errors and highlight stock or margin issues.
  • UK-specific requirements include line-level VAT rates, pack-to-recipe unit conversions, real-time wastage recording and HMRC-compliant invoice retention.
  • Jelly automates invoice scanning, live dish costing and price alerts, removing the manual workload that causes Excel to break at scale.

Talk To Jelly About Automating Your Stock Control

What A Restaurant Stock Control Excel Workbook Contains

Item Master Tab Structure For UK Kitchens

The Item Master acts as the single source of truth for every ingredient in your kitchen. Each row represents one ingredient. A UK kitchen Item Master needs these columns:

  • Item name, stored in a standardised format because free text is the biggest cause of item master drift
  • Supplier name
  • Pack size and pack unit, for example 6 × 2.5 kg or 1 × 5 L
  • Recipe unit such as kg, L or portion
  • Unit cost ex-VAT, which is the cost per recipe unit rather than per pack
  • VAT rate of 0%, 5% or 20%, stored at line level instead of as a single invoice rate
  • Yield or wastage percentage, for example 0.85 for a protein with 15% trim loss
  • Category such as food, beverage or dry goods
  • Par level for that ingredient

The unit cost ex-VAT column carries the financial weight of the workbook. HMRC’s VAT Regulations 1995, regulation 14(2), require a VAT invoice to state the amount payable excluding VAT per line, so your Item Master should always store net costs. Recording costs inclusive of VAT inflates COGS and makes GP% incomparable to your net sales figure.

Stock Count Tabs That Match The Item Master

Create one Stock Count tab per period, either weekly or monthly depending on your rhythm. Each Stock Count tab should include these columns:

  • Item name, selected from the Item Master via data validation instead of typed free-form
  • Opening stock in recipe units
  • Purchases in the period in recipe units
  • Closing stock, counted physically in recipe units
  • Wastage, recorded at the point it happens rather than estimated at period end
  • Usage, calculated as opening plus purchases minus closing minus wastage

The unit-conversion step keeps UK workbooks honest. A supplier delivers a case of 6 × 2.5 kg chicken breasts. Your recipes use grams. Your stock count uses kilograms. You need a conversion factor in the Item Master that stores pack size in recipe units so every downstream formula works in a consistent unit. No ranking competitor on the current SERP teaches this step explicitly. Without a consistent conversion layer, usage figures lose meaning.

Summary And COGS Tab For GP Visibility

The Summary tab pulls from the Stock Count tabs and the Item Master to produce the core numbers:

  • Usage equals opening stock plus purchases minus closing stock
  • COGS equals usage multiplied by unit cost ex-VAT
  • GP% equals net sales minus COGS, divided by net sales, multiplied by 100

UK hospitality benchmarks put food GP at 65–72% for food-led operations and 65–75% for wet-led venues, according to Hops HQ’s June 2026 GP benchmarking guide. A blended F&B GP below 60% usually signals a structural problem such as over-portioning, stale supplier prices or unrecorded wastage.

See Jelly In Action With Your Own Numbers

How To Make Stock Control In Excel For A Restaurant (Build Sequence)

  1. Build the Item Master first. Build the Item Master first because every other tab looks up to it. Add each ingredient as a single row and standardise its name before you write a single formula. Inconsistent naming causes item master drift, and once formulas point at two spellings of the same ingredient the workbook silently double-counts. Then add the pack size, recipe unit, unit cost ex-VAT, VAT rate, yield percentage and par level columns that the downstream formulas will read.
  2. Set up data validation drop-downs. On every Stock Count tab, apply a data validation list to the item name column that pulls from the Item Master. This forces staff to select from the approved list rather than typing free text. That single control does more than any other step to keep the workbook structurally sound.
  3. Create Stock Count tabs. Duplicate a template tab for each period so the layout stays consistent. Link the opening stock for period two directly to the closing stock from period one instead of retyping it. This link keeps periods continuous and reduces typing errors.
  4. Link Stock Count tabs to the Summary and COGS tab. Use SUMIF or SUMIFS to aggregate usage by item across periods. Use XLOOKUP to pull unit cost from the Item Master into the COGS calculation. The formula for COGS per item is usage from the Stock Count tab multiplied by unit cost ex-VAT from the Item Master.
  5. Add conditional formatting. Highlight any item where closing stock falls below par level. Highlight any dish or category where GP% drops below your target threshold. These visual flags replace the need to scan every row manually and draw attention to the lines that need action.

For a deeper walkthrough of the stocktake process that feeds this workbook, see the Restaurant Stocktake Spreadsheet: Free UK Template Guide.

The Best Excel Formulas For Restaurant Inventory Control

Four functions handle most of the work in a well-built restaurant stock control Excel workbook.

XLOOKUP pulls unit cost, pack size and yield percentage from the Item Master into a Stock Count tab by matching on item name or SKU. Example: =XLOOKUP(B2, ItemMaster!A:A, ItemMaster!E:E, "Not found") returns the unit cost ex-VAT for the item in B2 and displays “Not found” instead of an error if the item is missing from the master. Microsoft documents XLOOKUP as the modern replacement for both VLOOKUP and HLOOKUP, available in Excel for Microsoft 365, Excel 2021 and Excel 2024. If your team uses Excel 2016 or 2019, use INDEX/MATCH instead.

SUMIF / SUMIFS totals usage or COGS by category, by supplier or by period. Example: =SUMIFS(COGSColumn, CategoryColumn, "Food") returns total food COGS for the period. Use SUMIFS when you need to filter by more than one criterion, such as food COGS from a specific supplier in a specific week.

Data validation creates the drop-down lists that prevent free-text item names from corrupting the Item Master. In Excel, select the item name column on a Stock Count tab, go to Data → Data Validation → List, and point the source at the item name column of the Item Master. This control keeps the workbook usable over time by enforcing consistent naming.

Conditional formatting highlights items below par level and dishes whose GP% has dropped. Apply a rule to the closing stock column so that if the value is less than or equal to the par level cell for that row, the cell fills red. Apply a second rule to the GP% column on the Summary tab so that if GP% falls below your target, for example 65%, the cell fills amber. These rules create a visual stocktake dashboard without extra formulas.

Get A Walkthrough Of These Formulas In Jelly

UK Specifics That Break US Templates

Most free restaurant stock control Excel templates on the SERP are built for a US kitchen. UK operators run into four specific issues when they try to use them.

VAT treatment on purchases. Every cost column in your Item Master and Stock Count tabs must carry the net ex-VAT figure. On a mixed-rate supplier invoice, the reclaimable input VAT is the sum of the VAT column line by line. A produce invoice with £140 of standard-rated lines and the rest zero-rated carries £28 of reclaimable VAT because only the standard-rated lines carry VAT. Store VAT rate at line level in the Item Master. Raw meat, fish, fruit, vegetables, bread and milk are zero-rated. Alcohol, soft drinks, confectionery, crisps and ice cream are standard-rated at 20%. A restaurant stock spreadsheet should apply VAT per line rather than one rate to an entire supplier invoice.

UK supplier invoice line items and pack sizes. UK suppliers invoice in cases, kilograms, litres and portions, often mixed on the same invoice. Your Item Master needs a pack-size column and a conversion factor to recipe units. A case of 6 × 2.5 kg, for example, converts to 15 kg, so your unit cost ex-VAT is the case price divided by 15.

Wastage and yield percentages. UK kitchen prep generates trim loss on proteins, vegetable peelings and portioning waste. Record yield percentage in the Item Master, for example 0.78 for a whole chicken after butchery. Your effective unit cost for costing purposes is unit cost ex-VAT divided by yield percentage. A chicken breast costing £4.00/kg with 85% yield has an effective cost of £4.71/kg for recipe costing.

Making Tax Digital compliance. Under HMRC’s MTD rules, VAT-registered UK businesses must keep digital records and file through MTD-compatible software, rather than manually re-keying figures from a spreadsheet into a VAT filing portal. Your stock workbook functions as a management tool rather than a VAT record. Retain original supplier invoices, scanned or physical, separately. HMRC’s VAT Notice 700/21 states that typing selected figures into a spreadsheet does not replace retaining the original invoice evidence.

Running A Stocktake That Feeds The Workbook

Count discipline keeps the workbook trustworthy. Count in the same units as the Item Master, so if the master records chicken in kilograms you count in kilograms rather than portions or packs. Counting in different units from the master is the most common reason a workbook produces nonsensical usage figures.

Record wastage at the point it happens instead of at period end. A spoiled delivery, a dropped portion or a staff meal recorded on the day it occurs produces an accurate usage figure. Estimated wastage entered at month end produces a plausible-looking but wrong GP%.

Count the highest-value lines most frequently. Hops recommends a weekly rhythm of counting the 15–20 highest-value stock lines, with full counts at least monthly. Shorter periods make problems easier to trace.

For a full stocktake methodology including a free UK template, see the Restaurant Stocktake Spreadsheet: Free UK Template Guide.

Where The Spreadsheet Breaks

A well-built workbook serves a single-site kitchen with a stable supplier list and a team that counts consistently. It breaks at five specific points.

Item master drift. Free-text entry, staff turnover and supplier name changes cause the same ingredient to appear under multiple names. XLOOKUP returns errors. SUMIFS misses rows. The workbook produces wrong totals silently. Excel breaks quietly, because a single dragged formula or deleted row changes the answer and nothing warns the user.

Stale supplier prices. Excel does not automatically update recipe costs when a supplier raises a price, so the recipe cost stays wrong until someone manually edits the relevant cell. Fresh produce prices can move weekly. If a supplier’s rate has drifted up since a menu launched and the Item Master was never updated, actual food cost runs above theoretical food cost with no warning.

Manual entry errors. Ray Panko’s spreadsheet error research found an average cell error rate of 3.9% across laboratory studies, with field audits of real operational spreadsheets finding errors in 84–94% of workbooks. A 2026 study published in Information (17(5), 468) by Nandam and Paternina-Arboleda found manual spreadsheet processing error rates of 3.0–4.4% rising with transaction volume, versus 0.2% for automated processing.

Invoice volume. A supplier invoice with 200 line items, keyed by hand, takes significant time and introduces compounding error risk. At 10–20 hours a week of manual data entry, price checking and reconciliation, the admin cost of the workbook exceeds the cost of replacing it.

Multi-site consolidation. Every additional site doubles the workbook’s complexity. In Excel, every additional storage location doubles the workbook’s complexity, whereas inventory software scales unchanged from one to five locations.

For a detailed comparison of the structural trade-offs, see Stock Control Software Vs Excel: A UK Operator’s Guide and The Downsides Of Spreadsheets For Restaurant Inventory.

When the workbook stops scaling, Jelly is the natural next step. Jelly scans every line item of every supplier invoice, by photo or email, so there is no manual keying. Ingredient costs update with each new invoice, which means dish costs and GP margins update automatically. Price Alert flags every price change the moment it appears, giving chefs the data to challenge supplier price creep. The Flash Report gives a daily, weekly or monthly GP view by connecting to POS systems including Square, EPOS Now, Toast and Lightspeed, each in about five minutes. Xero integration pushes digitised invoices straight to accounting. The cost is a flat £129/month per location with no per-user charges.

Larger operators sometimes consider a purchase-to-pay system such as Procure Wizard instead of a spreadsheet. Procure Wizard is a finance-led purchase-to-pay system with electronic purchase orders, three-way invoice matching, approval hierarchies and 85+ finance and EPoS integrations, priced at £900/month for the Essential tier and £1,490/month for the Professional tier on a 36-month minimum term, according to OmniPATH’s October 2026 comparison of UK hospitality procurement software. Its help centre documents that stock-control governance depends on correctly configured roles and permissions, including stock-take lock approvals, outlet access, invoice acceptance restrictions and price-approval roles. That level of administration suits a large hotel group with a dedicated finance team and formal approval chains. A growing single-site or two-site kitchen usually needs live dish costs and invoice automation with far less configuration overhead. Jelly scans every invoice line item automatically and updates dish costs and GP in real time, while a template-led approach or a purchase-to-pay system still relies on manual entry and periodic reconciliation to reach the same dish-level insight.

Within the restaurant stock control and inventory management software category, Jelly is the recommended first choice for UK operators at the £500k+ revenue stage who want live dish costs, automated invoice processing and real margin visibility without the configuration overhead of an enterprise procurement platform.

See How Jelly Replaces Your Manual Workbook

Free UK Restaurant Stock Control Spreadsheet: What To Look For

If you are downloading a free restaurant stock control Excel template rather than building from scratch, check it against these criteria before using it in a live kitchen:

  • UK VAT handling with costs stored ex-VAT at line level and a VAT rate column per ingredient, instead of a single rate applied to the whole invoice
  • Supplier invoice line items with a pack size column and a conversion factor to recipe units, so a case of 6 × 2.5 kg maps correctly to a per-kg cost
  • Wastage and yield with a yield percentage column in the Item Master that adjusts effective unit cost for recipe costing
  • Unit conversions with explicit conversion factors between purchase units such as cases, litres and packs, and recipe units such as kg, g, ml and portions
  • XLOOKUP-compatible Item Master with a structured table and consistent column headers so XLOOKUP and SUMIFS work without modification
  • Google Sheets compatibility so that if your team works in Google Sheets rather than Excel for Microsoft 365, you confirm XLOOKUP support or substitute VLOOKUP or INDEX/MATCH

A template meeting these criteria gives UK operators a sound starting point for a restaurant stocktake spreadsheet they can use immediately. When prices change faster than the Item Master is updated, when invoice volume makes manual entry impractical, or when a second site makes consolidation unmanageable, Jelly becomes the practical next step.

Compare Your Current Template With Jelly

Frequently Asked Questions

How Do I Make A Stock Control Spreadsheet For A Restaurant In Excel?

Build three tabs in sequence. First, create an Item Master with one row per ingredient, containing item name, supplier, pack size, recipe unit, unit cost ex-VAT, VAT rate, yield percentage, category and par level. Second, create a Stock Count tab with columns for opening stock, purchases, closing stock, wastage and usage, calculated as opening plus purchases minus closing minus wastage. Third, create a Summary and COGS tab that pulls usage from the Stock Count tab and multiplies it by unit cost from the Item Master to produce COGS, then divides COGS by net sales to produce GP%. Apply data validation drop-downs to the item name column on every Stock Count tab so staff select from the Item Master rather than typing free text. That single step prevents the item master drift that corrupts most restaurant inventory spreadsheets over time.

What Formulas Do I Need For Restaurant Stock Control In Excel?

Four functions cover the majority of the work. XLOOKUP pulls unit cost, pack size and yield percentage from the Item Master into Stock Count tabs by matching on item name or SKU. SUMIFS totals usage or COGS by category, supplier or period when you need to filter by more than one criterion. Data validation creates the drop-down lists that enforce consistent item naming. Conditional formatting highlights items below par level and dishes where GP% has dropped below your target. For backward compatibility with Excel 2016 or 2019, substitute INDEX/MATCH for XLOOKUP.

How Often Should I Stocktake?

Count your 15–20 highest-value lines weekly and run a full count at least monthly. Shorter periods make problems easier to trace, because a weekly count that reveals a 5-point GP variance points to the current week’s deliveries and prep, whereas a monthly count that reveals the same variance could point to any of four weeks. Fast-moving lines such as proteins, spirits and premium ingredients warrant weekly counts. Dry goods and packaging can be counted fortnightly or monthly. The count discipline matters more than the exact frequency, so count in the same units as the Item Master, record wastage at the point it happens and close each period before opening the next.

How Do I Handle VAT On Stock Purchases In A UK Restaurant Spreadsheet?

Store all costs ex-VAT in the Item Master and Stock Count tabs. Record the VAT rate at line level for each ingredient because a typical UK produce invoice contains zero-rated lines alongside standard-rated lines. The reclaimable input VAT is the sum of the VAT column line by line. Your GP% calculation must use net sales ex-VAT as the denominator and net costs ex-VAT as the numerator so the two figures are comparable. Retain original supplier invoices separately, because HMRC’s VAT Notice 700/21 states that typing selected figures into a spreadsheet does not replace retaining the original invoice evidence, and VAT-registered businesses must keep records for at least six years.

When Should I Stop Using Excel For Restaurant Stock Control?

A spreadsheet works for a single site with a stable supplier list, a small number of SKUs and one person maintaining the file. It stops working reliably when supplier prices change faster than the Item Master is updated, invoice volume makes manual entry take more than a few hours a week, a second site requires consolidation across two workbooks or more than one person needs to update the file simultaneously. At that point, the admin cost of maintaining the workbook, often 10–20 hours a week, exceeds the cost of replacing it. Jelly is the best restaurant stock control solution for UK operators who want accurate dish costs and live margins without rebuilding a spreadsheet every week, because it scans every supplier invoice line item automatically, updates dish costs and GP in real time from POS sales and flags every ingredient price change the moment it happens. Operators using Jelly cut food costs by an average of 3% and add an average of 2 percentage points to gross margins in the first three months.

Conclusion

A properly built restaurant stock control Excel workbook, with an Item Master that stores ex-VAT costs and yield percentages, Stock Count tabs with data validation drop-downs and a Summary and COGS tab linked by XLOOKUP and SUMIFS, gives a UK kitchen a genuinely useful tool. The formula set stays simple. The discipline of counting in consistent units, recording wastage in real time and keeping the Item Master current demands more effort than the formulas themselves.

The workbook’s limits come from structure rather than effort. It does not update itself when a supplier raises a price. It does not flag a margin drop. It breaks quietly when a formula is dragged incorrectly or a row is deleted. Field audits of real operational spreadsheets consistently find errors in the large majority of workbooks, and error rates rise with transaction volume and the number of people touching the file.

Jelly is the best restaurant stock control solution for UK operators who want accurate dish costs and live margins without rebuilding a spreadsheet every week, because it scans every supplier invoice line item automatically, updates dish costs and GP in real time from POS sales and flags every ingredient price change the moment it happens. As the FAQ above sets out, Jelly replaces the weekly rebuild with automatic invoice scanning and live dish costs. If you are running one site and want to start with the workbook, build it using this guide. If you are running multiple sites or spending more time on the spreadsheet than on the kitchen, book a Jelly demo.

Book Your Jelly Demo