Written by: JJ Tan, Founder, Jelly
Key Takeaways
- Manual bar stock control often leads to shrinkage, over-pouring, and margin erosion. A structured spreadsheet helps you spot and reduce these losses.
- A UK-focused spreadsheet tracks opening stock, deliveries, closing stock, usage, and variance so you can answer four core operational questions.
- Essential columns fall into three groups: product identifiers, stock movement figures, and calculated metrics such as variance and pour cost.
- Regular stock takes, accurate formulas, and measured pours help you catch variance early and keep shrinkage around 0.5–1%.
- When spreadsheets start to consume too much time, tools like Jelly can automate the workflow and handle the heavy lifting.
What Is A Bar Stock Control Spreadsheet?
A bar stock control spreadsheet tracks inventory levels across a stock period, calculates actual usage, and highlights variance between what your POS says you should have sold and what your physical count shows you actually used. The tool applies to any UK bar, pub, or restaurant with a drinks offering, from a single-site independent to a boutique hotel with multiple bar points.
The spreadsheet answers four operational questions in a clear, repeatable way: what you bought, what you sold, what should still be on hand, and what is missing. Without clear answers to all four, it becomes hard to know whether the bar is truly profitable.
For broader context on stock control principles, see our complete guide: Bar Stock Control: The Complete Guide For UK Bars.
Essential Columns For A Bar Stock Control Spreadsheet
The following columns form the foundation of a UK-specific bar stock control spreadsheet. They fall into three groups: product identifiers (Item Name, Unit Size, Unit Cost), stock movement figures (Opening, Received, Closing), and calculated metrics (Usage, Variance, Pour Size, Cost per Pour, Par Level).
- Item Name And Category. Group by spirit, beer, wine, mixer, and soft drink. Use consistent naming such as “Gordon’s Gin 70cl” to prevent duplicates and keep filters clean.
- Unit Size. UK standard for spirits is 70cl bottles. Include keg sizes such as 50L, wine bottles at 75cl, and mixers like 1L bottles or 330ml cans to cover your full range.
- Unit Cost. Record the VAT-inclusive price per bottle or keg from your UK supplier. This is the figure you actually pay and the base for all cost calculations.
- Opening Inventory. Enter the physical count at the start of your stock period, typically Monday morning. This becomes your baseline for all subsequent calculations.
- Received (Deliveries). Log all stock delivered during the period against delivery dockets on the day of receipt so nothing slips through.
- Closing (Ending) Inventory. Record the physical count at the end of the period. Count every bottle and avoid estimates to keep your data reliable.
- Usage. Calculate this as Opening + Received − Closing. The result shows what was actually consumed during the period.
- Variance. Calculate Expected Usage from POS sales divided by pour size, then subtract Actual Usage. A positive variance indicates more usage than expected, which often signals over-pouring, spillage, or theft.
- Pour Size. Use the legal measures for spirits mentioned earlier and record the standard pour you use for each product.
- Cost Per Pour. Divide bottle cost by the number of pours per bottle, for example 70cl ÷ 25ml equals 28 pours. This figure underpins your pricing decisions.
- Par Level. Set the minimum stock you should hold based on average weekly usage plus safety stock. Review par levels quarterly or when the menu changes.
How To Make A Bar Stock Control Spreadsheet In Excel
Set Up Your Column Headers
Open Excel or Google Sheets and create headers across row 1: Item Name, Category, Unit Size, Unit Cost, Opening Inventory, Received, Closing Inventory, Usage, Variance, Pour Size, Cost per Pour, Par Level. Format the header row in bold with a dark fill and white text. Freeze the top row so headers remain visible as you scroll through your full product list.
Format Cells For Currency And Quantities
Format the Unit Cost and Cost per Pour columns as currency (£). Format all inventory columns as whole numbers. Use data validation on the Category column to create a dropdown for spirit, beer, wine, and mixer. This approach prevents typos and keeps your data clean for filtering and reporting.
Enter Your Opening Stock
Start with an initial physical count of everything behind the bar, in the cellar, and in storage. Count in a consistent order such as speed rails, back bar, under-bar storage, cellar, and walk-in to cut counting time by up to 20% and prevent missed sections. Enter these figures in the Opening Inventory column so they form the baseline for all future calculations.
Record Deliveries And Closing Stock
Each time a delivery arrives, log it in the Received column against the correct item. Count inbound stock against the delivery docket the same day to catch supplier short-measures and delivery errors. At your next stock take, enter the physical count in the Closing Inventory column.
Add The Key Formulas
Three formulas drive the entire spreadsheet and keep your numbers consistent.
- Usage:
=Opening + Received − Closing(for example,=D2+E2-F2) - Variance:
=Expected Usage − Actual Usagewhere Expected Usage comes from your POS sales divided by pour size - Stock Value:
=Closing Inventory × Unit Cost
Use Excel Tables (Ctrl+T) to keep formulas intact as you add rows. Apply conditional formatting to highlight negative variance in red. That colour becomes your shrinkage signal and prompts immediate investigation.
How To Calculate Pour Cost In A Bar Stock Control Spreadsheet
The Pour Cost Formula
Pour Cost % = (Cost of Liquor Used ÷ Liquor Sales) × 100.
Consider a UK example. If your gin usage for the week cost £180 and your gin sales were £600, your pour cost is 30%. Target pour costs vary by category, with spirits typically at 18–24%, beer at 20–28%, and wine at 30–40%. A pour cost above these ranges often points to over-pouring, pricing gaps, or unrecorded waste.
Calculating Cost Per Pour
A 70cl bottle contains 700ml, yielding 28 single 25ml measures or 20 single 35ml measures. For a 70cl bottle of vodka costing £18 including VAT, the maths is straightforward.
- At 25ml pours: 700ml ÷ 25ml = 28 pours per bottle
- Cost per pour: £18 ÷ 28 = £0.64 per pour
Add a Cost per Pour column using the formula =Unit Cost / (700 / Pour Size in ml). This shows exactly what each drink costs before you price it and flags immediately when a supplier price increase erodes your margin.
A free-poured 25ml spirit measure is rarely 25ml and typically measures 32–35ml, while most bar staff remain unaware of the overpour. That extra volume costs £0.12–£0.18 per pour. Over 50 pours a day, that adds up to £6–£9 daily, or £1,800–£2,700 per year on a single spirit line. Your spreadsheet will surface this variance only when you use measured pours to generate reliable expected usage figures. Accurate stock takes then keep those figures trustworthy.
How To Do A Stock Take Efficiently
To conduct a stock take efficiently, follow these four steps and repeat the same process every period.
Step 1: Count In A Consistent Order
Always count in the same physical order such as speed rails, back bar, under-bar storage, cellar, and walk-in. Order your spreadsheet rows to match this physical route. This approach cuts counting time and prevents missed sections.
Step 2: Use Two People
Assign one person to count and one person to record. This pairing reduces transcription errors and keeps the pace steady. When you reach open spirit bottles, the counting person should weigh them on digital scales with 1g accuracy and convert the weight to ml using the spirit’s density, while the recording person logs the result. A full 70cl bottle of standard spirit weighs approximately 790g, and a half-full bottle weighs roughly 395g.
Step 3: Record Immediately
Enter counts directly into your spreadsheet as you go. Every transcription from paper to spreadsheet creates another opportunity for error, and paper sheets that need typing up later double the risk of bad data.
Step 4: Set A Regular Schedule
Count high-value spirits weekly and lower-value items monthly. A weekly count takes 1.5–2 hours and catches issues before they become expensive patterns. Monthly counts alone move too slowly to manage variance effectively.
Free Bar Stock Control Spreadsheet Template Sources
Ready-made bar inventory spreadsheet templates are available from Backbar, Smartsheet, and Microsoft Excel, but they are predominantly US-focused. Bottle sizes often default to 750ml, pour sizes assume 1.5oz measures, and pricing excludes VAT. These templates provide a useful starting point only if you are prepared to adapt them.
The build approach in this guide stays UK-specific throughout, using 70cl bottles, the legal measures mentioned earlier, and VAT-inclusive unit costs. It also includes pour cost logic that generic templates usually lack. Follow the steps above to build a daily bar inventory sheet that reflects how UK bars actually operate.
When To Upgrade From Spreadsheets To Software
A well-built spreadsheet works for a single-location operation with a manageable SKU count. Clear signs that a spreadsheet has been outgrown include stock held in more than one location, two people editing the sheet simultaneously, no audit trail of who changed what, and the need for barcode scanning.
If a GM or Beverage Director spends more than 4 hours a week managing inventory spreadsheets, valuable leadership time is being diverted from guests and staff. Maintaining a spreadsheet for weekly bar inventory costs approximately £1,200–£1,600 per year in labour time alone, a cost many operators overlook.
Jelly automates invoice scanning, inventory tracking, and real-time profitability, saving 10–20 hours per week and adding 2 percentage points to gross margins on average. If you find yourself spending more time updating your spreadsheet than serving customers, automation becomes a practical next step.
See Jelly in action to understand how it replaces your manual stock control workflow, and read more about the case for switching in our guide: Bar Inventory Spreadsheet Alternative: Why UK Pubs Switch.
Frequently Asked Questions
How Often Should I Do A Stock Take?
High-value spirits and fast-moving lines should be counted weekly. A weekly count takes 30–45 minutes of physical measurement plus time for data entry and reconciliation and catches shrinkage before it becomes a pattern. Monthly counts alone move too slowly, because by the time a problem surfaces you have already lost four weeks of margin. Lower-value or slow-moving items can be counted monthly without significant risk, provided your weekly counts cover the lines that drive the most revenue.
What Is The Difference Between Usage And Variance?
Usage shows what you actually consumed during a stock period using the formula Opening Inventory + Received − Closing Inventory. It is a factual figure derived from physical counts and delivery records. Variance is the difference between expected usage and actual usage. Expected usage comes from your POS sales data divided by your standard pour size and represents what you should have used if every pour was correct and every sale was recorded. A positive variance means you used more product than your sales justify, which points to over-pouring, spillage, unrecorded comps, or theft. A negative variance may indicate underpours, a recording error, or a missing delivery entry.
How Do I Account For Spillage And Waste?
Log every spill, breakage, and wastage immediately in a notes column or a dedicated waste log tab within your spreadsheet. If you skip recording at the time, it appears as unexplained variance at the end of the period. Train staff to report waste openly, because unexplained variance harms your analysis far more than a transparent waste record. Line cleaning waste for draught products should also be logged separately, as it is a predictable operational cost rather than a shrinkage event.
Can I Use This Spreadsheet For A Pub Or Restaurant?
Yes. The same structure applies whether you run a pub with 30 spirit lines, a restaurant with a compact bar, or a boutique hotel with multiple service points. Adjust counting frequency based on your volume, with high-turnover venues counting spirits weekly, and set par levels based on your actual sales mix rather than generic industry averages. For hotel bars with stock moving between the main bar, lounges, and function rooms, add a transfers column to track inter-bar movements and prevent inventory disappearing during events.
What Is A Realistic Variance Target For A UK Bar?
A variance of 0.5–1% on wet sales is considered acceptable and normal for a well-run UK bar. Anything above 1.5% means you are losing money at a rate that warrants investigation, and above 2% is a serious problem requiring immediate action. The most common cause of variance above 1% is over-pouring rather than theft. The over-pouring issue discussed earlier creates a permanent variance that no spreadsheet formula can correct without switching to measured pours using jiggers or optics.
Conclusion: Take Control Of Your Bar Stock Today
A properly structured, UK-specific bar stock control spreadsheet, built with the right columns, usage formulas, and pour cost logic, gives you the visibility to identify shrinkage, control costs, and protect your margins. The template structure in this guide accounts for 70cl bottles, 25ml and 35ml legal measures, and VAT-inclusive pricing, which reflects the realities of running a UK bar.
The spreadsheet provides a strong starting point. When manual counting becomes too time-consuming, stock spans multiple locations, or you need an audit trail that a shared Excel file cannot provide, Jelly can help. It automates the entire process, from invoice scanning to real-time profitability, and delivers the time and margin savings mentioned earlier.
Talk to our team about replacing your spreadsheet so you can focus more of your time on running your bar.