Written by: JJ Tan, Founder, Jelly
Key Takeaways for UK Restaurant Stocktakes
- A restaurant stocktake spreadsheet records physical counts of food, drink and supplies so you can calculate stock value, usage and variance using COGS and variance formulas.
- Manual stocktaking can consume up to 20 hours per week and cause 3–7% stock variance, which creates significant margin leakage through waste, theft and unrecorded consumption.
- Essential spreadsheet columns include item name, unit of measure, par level, current stock, theoretical usage, variance, unit cost and supplier, with formulas for total value and clear reorder alerts.
- UK operators must record costs ex-VAT, maintain tiered count frequencies and document waste to comply with HMRC VAT rules and licensing requirements.
- Spreadsheets become unsustainable as operations grow; see how Jelly handles multi-site inventory without the spreadsheet overhead
The Problem: Weekly Stocktakes Drain Time and Margin
Manual stocktaking is one of the most resource-intensive back-of-house tasks in UK hospitality. Manual inventory tracking can demand up to 20 hours per week for physical counts, spreadsheet updates and discrepancy corrections. That is time owners, head chefs and operations managers cannot afford to lose.
The margin damage compounds quickly. Restaurants using manual inventory methods suffer stock variance of between 3% and 7%, which in a high-volume operation represents tens of thousands of pounds leaked annually through waste, theft and unrecorded consumption. For UK pubs specifically, stock loss on wet sales can quietly cost thousands of pounds per year. Free-poured spirit measures often exceed the intended pour due to over-pouring, which hides margin losses that never appear on a spreadsheet.
Price creep from suppliers quietly erodes GP as well. Restaurants can experience unauthorised price increases from vendors, adding substantial costs annually when undetected. Without automated price alerts, those increases pass silently through invoices and erode GP before anyone notices.
The first step to regaining control is building a stocktake system that captures these issues before they compound.
How to Build a Working Restaurant Stocktake Spreadsheet
A functional stocktake spreadsheet requires more than a list of items and quantities. To calculate accurate COGS, track variance and trigger reorders reliably, you need eight core data points working together as a single system. The eight minimum columns needed for a spreadsheet to function as an operational tool are:
- Item name, using a standardised naming convention to avoid duplicates
- Unit of measure, with one consistent unit per ingredient (kg, litre, case). Mixing units such as grams and kilograms for the same ingredient produces plausible but systematically incorrect totals.
- Par level, the minimum stock quantity that triggers a purchase order, reviewed monthly for seasonality
- Current stock, the physical count quantity with count date noted
- Theoretical usage, expected consumption based on sales and recipe yields
- Variance, actual usage minus theoretical usage to identify waste, portioning issues or theft
- Unit cost, the most recent supplier price per unit, recorded ex-VAT for cost tracking (see VAT note below)
- Supplier, for purchase order reference and grouping (for example Bidfood, Brakes, Booker, JJ Food Service)
Add a Total Value column using the formula =Counted_Qty * Unit_Cost. Add a Status column with conditional formatting using =IF(Current_Stock<Par_Level,"REORDER","OK"). Conditional formatting creates visual reorder alerts when quantity drops below the reorder point, which turns each row into an actionable instruction.
UK VAT handling: HMRC requires restaurants to charge 20% VAT on all food and drink prepared for catering or eaten in. Record all ingredient costs ex-VAT in your stocktake sheet so your COGS calculation uses net figures. Supplier invoices must capture unit cost, total cost, VAT where applicable and supplier VAT number. UK tax record retention periods vary by tax type and taxpayer, typically up to six years for companies but five years after the submission deadline for self-employed individuals.
Tiered count frequency: High-value items such as proteins, seafood, spirits and wine should be counted weekly, mid-value items such as dairy, oils and cheese fortnightly, and low-value stable items such as salt, flour and tinned goods monthly. This tiered approach keeps effort focused where variance hurts most.
Waste and spoilage tracking: Add a dedicated Waste column per item and a separate Waste Log tab with columns for Date, Item, Quantity, Reason (spoilage, over-portioning, spillage, line cleaning) and Cost Impact. Documenting waste categories enables variance reconciliation during HMRC audits and shows where training or process changes will recover margin.
How to Calculate COGS from Your Stocktake
Once you complete your physical count and record closing stock values, you can calculate your actual cost of goods sold for the period. The standard COGS formula is:
COGS = Opening Stock + Purchases − Closing Stock
In Excel or Google Sheets, enter this as a single cell equation referencing your named ranges:
=Opening_Stock + Purchases - Closing_Stock
For example: £6,200 opening stock + £9,400 purchases − £5,900 closing stock = £9,700 COGS. Divide COGS by food sales to obtain your actual food cost percentage. Target food cost for UK restaurants is 28–35% of selling price to achieve a 65–72% gross margin, while wet stock should target 20–30% cost for 70–80% margin.
For a detail-level COGS worksheet, include columns for SKU, beginning quantity and value, purchase quantity and value, freight allocation, returns or discounts, ending quantity and value, and COGS per SKU, where COGS_SKU = Beg_Value + Purch_Value + Freight_Alloc + Returns_Discounts − End_Value. Under IAS 2, the IFRS inventories standard used in the UK, LIFO is prohibited as an inventory valuation method. This rule affects how you structure your costing policies.
Stocktake Variance Formula and How to Use It
Your COGS calculation shows what you spent, but not whether you spent it efficiently. Variance reveals the gap between what your recipes say you should have used and what your stocktake shows you actually used:
Variance = Actual Usage − Theoretical Usage
Actual Usage equals Opening Stock + Purchases − Closing Stock. Theoretical Usage equals dishes sold multiplied by recipe yield per dish, pulled from your POS.
A 2 kg counting error on lamb at £24/kg creates a £48 variance hole, whereas the same error on flour costs under £1. This difference is why variance investigation should prioritise high-value lines first. Well-managed operations target variance under 3% per ingredient category. Anything consistently above this threshold on proteins or spirits indicates portioning drift, receiving errors, undocumented waste or potential theft.
Use variance patterns to drive specific actions:
- Positive variance (used more than expected): investigate over-portioning, unrecorded waste or theft.
- Negative variance (used less than expected): check for receiving short-deliveries or recipe yield errors.
- Consistent variance on one supplier’s lines: request a credit note and renegotiate terms.
Best Practices for Reliable UK Restaurant Stocktakes
The formulas and calculations above only deliver accurate results when your physical counting process is reliable. These operational best practices create a consistent system so your stocktake data stays trustworthy week after week.
Organise stocktake sheets in physical walk-order by storage zone, such as Dry Store, Walk-in Chill, Freezer, Bar or Cellar and Prep Fridge, rather than alphabetically. This layout reduces counting time by approximately 40% and improves accuracy.
Additional best practices for UK operators work together as a single routine:
- Two-person counts: one counter physically touches items and calls numbers, while one recorder enters data, using a consistent left-to-right, top-to-bottom room pattern to minimise human error.
- Consistent timing: count at the same point each week, typically at close of trading on Sunday or at opening on Monday before deliveries begin, to avoid timing errors.
- FIFO enforcement: label every item with arrival date and use-by date, stack new deliveries behind existing stock and run a daily check of cool room and dry storage for items approaching expiry.
- Partial bottles: use the tenths method, estimating liquid level to the nearest tenth against the back label, for speed and consistency in weekly bar stocktakes.
- Supplier price tracking: maintain a Price History tab per supplier, such as Bidfood, Brakes or Booker, with columns for Date, SKU, Previous Price, New Price and percentage change to build negotiation evidence over time.
- VAT record-keeping: UK operators must keep stock records showing opening stock, all purchases, all sales and closing stock at intervals of no more than 13 weeks under VAT rules and licensing law.
Beyond the tiered frequency outlined earlier, active restaurants benefit from daily spot checks on the highest-value lines, such as proteins, seafood and top-shelf spirits, so variance is caught before it compounds.
When Spreadsheets Become Unsustainable
Spreadsheets are a viable starting point, but they have a clear breaking point. As a business grows from managing around 50 products to 500 products, spreadsheets shift from workable to a liability because of collaboration conflicts, manual data-entry errors, broken formulas, accidental deletions and hours of maintenance time.
Beyond the operational burden, accuracy itself degrades. Inventory records can become inaccurate under manual tracking, with human error often triggering stockouts or overstocking events. Manual inventory counts can produce frequent errors, with the largest discrepancies on high-value proteins and alcohol.
The visibility problem grows at the same time. When recipe costing runs on a system that does not update ingredient prices automatically, the theoretical cost of a dish remains static while actual costs rise. Finance teams then struggle to identify GP decline until it is too late. For multi-site operators, a single shared spreadsheet offers no live price alerts, no automated variance flagging and no central source of truth. Each site’s data is only as current as the last manual entry.
Restaurants that track food cost more frequently can experience lower variance between actual and theoretical food cost. Increasing count frequency in a manual spreadsheet multiplies the admin burden, which is why automation becomes essential as operations mature. The data makes the case clearly.
How Jelly Turns Stocktake Data into Live Margin Control
The spreadsheet limitations outlined above, including manual data entry, formula maintenance, delayed price visibility and multi-site coordination, are the exact problems Jelly solves. Here is how the workflow changes.
Jelly replaces the manual spreadsheet workflow with an automated flow from invoice to live GP. Every supplier invoice, whether from Bidfood, Brakes, Booker or any other supplier, is captured by photo or email. Jelly scans every line item automatically and updates ingredient costs across all dish recipes in real time. You avoid manual data entry and formula maintenance.
The Price Alert feature flags every ingredient price increase or decrease the moment a new invoice is processed. Operators receive concrete evidence to challenge suppliers and claim credit notes. The Flash Report delivers a daily, weekly or monthly view of gross profit margin calculated from live invoice costs and POS sales data, without waiting for a monthly accountant report.
Jelly integrates natively with Square, EPOS Now, Lightspeed and Toast via real-time API, delivering item-level sales data the moment a transaction completes. Connecting any supported POS takes approximately five minutes. Sushi Revolution’s monthly stocktake using Jelly takes 5–20 minutes, down from 2–3 hours previously.
Across Jelly’s customer base, operators save 10–20 hours of admin every month and add an average of 2 percentage points to gross margins within the first three months. One operator improved gross profit from 65% to 72% within 12 weeks on approximately £500,000 in revenue. Cairn Lodge Hotel’s Head Chef Stuart Noble cut food costs by 5% in a single month after switching from manual tracking to Jelly’s live dish costing.
Jelly is priced at a flat rate of £129 per month per location, with no variable charges per user or feature.
See how Jelly delivers live margin control without manual data entry
Frequently Asked Questions About Restaurant Stocktakes
How long does a restaurant stocktake take in the UK?
A disciplined weekly count of high-value items such as draught lines, open spirits and proteins takes 45 to 90 minutes when storage is well organised. A monthly full stocktake covering all categories typically takes 2–4 hours depending on range depth and site size. Operators using automation tools such as Jelly report full monthly stocktakes completing in as little as 5–20 minutes, because ingredient data is already populated from scanned invoices and only physical counts need to be entered.
Should I count stock weekly or monthly?
The industry standard for active restaurants is a weekly full count of all food and beverage items, supported by daily spot checks on high-value lines such as proteins, seafood and top-shelf spirits. Slow-moving dry goods and cleaning supplies can be counted monthly without meaningful loss of accuracy. Weekly counting keeps variance smaller and faster to investigate. Operators who switch from monthly to weekly counts typically recover 1–2 gross profit points within eight weeks by catching over-portioning and waste earlier.
How do I handle partial bottles and open items in a stocktake?
The standard method for bar stocktakes is the tenths method. Estimate the liquid level in each open bottle to the nearest tenth by holding it against the back label, then record it as a decimal, such as 0.7 of a bottle. This approach is fast, consistent and widely accepted for weekly counts. For open kegs, use a measuring stick to dip the keg and record the estimated percentage full. For open food items such as bags of flour or containers of oil, weigh them on kitchen scales and record the net weight in your standard unit of measure.
Can I integrate my existing POS with inventory software?
Yes. Jelly integrates natively with Square, EPOS Now, Lightspeed and Toast via real-time API. Each integration delivers item-level sales data the moment a transaction completes, which Jelly uses to calculate theoretical usage and live gross profit margins per dish. Connecting any supported POS takes approximately five minutes through the Jelly integrations panel. For operators on other POS systems, Jelly continues to add integration partners over time.
Is a spreadsheet enough for multi-site operations?
A spreadsheet is workable for a single site with a small menu and stable supplier pricing. For multi-site operations, spreadsheets create significant problems. There is no single source of truth, data is only as current as the last manual entry, price changes from one supplier affect all sites but must be updated manually in each file, and there is no automated variance flagging or GP visibility across locations. Operators expanding to two or more sites consistently find that the admin burden of maintaining accurate spreadsheets across sites exceeds the cost of purpose-built automation.
Conclusion: From Manual Stocktakes to Automated Profitability
A well-built stocktake spreadsheet with correct COGS and variance formulas, tiered count frequency, UK VAT handling and supplier price tracking delivers immediate control over food and beverage costs. The template and formulas in this article give any UK restaurant, pub or boutique hotel operator a working foundation today.
The limitations of spreadsheets become clear as revenue grows, supplier relationships multiply and the cost of a single undetected price increase or portioning error compounds across weeks of service. Jelly provides the automation layer that removes those limitations. Automated invoice scanning, live dish costing, real-time price alerts and POS integration replace the manual workflow, saving 10–20 hours of admin per month and adding measurable GP points within the first quarter.
Make the move from manual stocktakes to live margin control