Stock variance in pubs: Excel vs real tracking

Last updated: 26 June 2026

Most pubs running stock on spreadsheets have no idea what their actual variance is—because they’re measuring the wrong thing. You count bottles and kegs on a Monday morning, write the numbers into a grid, and call it stock variance. But you’re not seeing the losses that matter: the 35ml free-pours that should be 25ml, the keg that sat at the wrong temperature and lost two litres to waste, the till that doesn’t ring up every drink. A 1% stock loss on wet sales quietly costs a typical pub £3,000–£5,000 a year—and most of that never shows up in a spreadsheet count.

Running a stocktake is not the same as tracking variance. One tells you what you have; the other tells you what you’re losing and where. When I moved from a tangle of spreadsheets to a disciplined weekly count with proper weighing and dipping, my variance went from guesswork to a number I could trust within a fortnight—and my GP went up.

This article walks you through what stock variance actually is, why spreadsheets fail to catch the real leaks, and the simple routine that turns variance into something you can act on.

What Is Stock Variance?

Stock variance is the gap between what your EPOS says you sold and what your physical stock count shows you actually had. In theory, if you sold £500 of Guinness last week and you had 40 pints at the start and finished with 10, you should have sold 30 pints. If your till says you sold 32, you have a variance of 2 pints—a loss.

But here’s the catch: that simple formula only works if every other number is accurate. Your till assumes every pour is exactly 25ml. Your count assumes you weighed every open bottle and dipped every keg. Your reconciliation assumes you logged every staff comp, every tasting, every split pint poured down the sink because the head was wrong.

In real pubs, almost none of those things are true. A variance of 2–4% is normal. Anything above 5% is a red flag. But you’ll never see the real number unless you measure it properly.

Why Spreadsheets Fail to Catch Real Losses

I spent years running stock on Excel. I had columns for opening stock, closing stock, sales, variance percentage, cost price, and gross profit. It looked thorough. It made sense on a Monday morning. And it told me almost nothing about where my money was actually going.

Here’s why spreadsheets fail:

• They count bottles, not volume. A bottle marked “full” might be 85% full. A keg marked “in use” might have three litres of gunk and water in the line. You’re not measuring liquid; you’re guessing.
• They ignore over-pouring. A free-poured 25ml spirit is often 32–35ml. If one person pours generously and another pours tight, your variance swings by 3–5% before anyone steals anything. A spreadsheet sees it as loss; it’s actually wage cost hidden in the drink.
• They don’t reconcile to the till the same day. You count on Monday, your till data sits in your EPOS, and by Friday when you finally check whether they match, you’ve forgotten what happened on Wednesday. A proper reconciliation happens within 24 hours.
• They hide waste and comps. Someone poured a pint that was 90% foam. Someone comped a pint because the customer complained. Someone tasted three measures to check the bitter wasn’t off. A spreadsheet logs this as loss; it’s actually operations cost.
• They have no cellar discipline. Temperature fluctuations cause draught loss. Bad line cleaning wastes litres. A cask sits in the cellar for three days half-empty before anyone notices. A spreadsheet doesn’t see any of this.

The real problem: spreadsheets measure closing stock, not variance. They’re a snapshot. Variance is a story—a story that starts when a delivery arrives, continues through every pour, and ends when you count again.

The Weekly Count Routine That Works

Stop counting once a week and calling it stock control. Count weekly, but do it properly.

Here’s the routine I use now:

• Pick the same day and time every week (I use Tuesday 10 a.m.). Consistency means you’re comparing like with like. Same day of the week, same opening stock, same till data range.
• Weigh every open spirit bottle on a set of digital scales accurate to 1g. A 70cl bottle of vodka at full density weighs 880g. Every 100g lost is roughly three 32ml measures. Record the weight, not the level.
• Dip every cask and partial keg with a dipstick (not your eye). Record the dip in millilitres or centimetres—convert to pints later. Temperature matters; a 4°C cask loses liquid differently than a 12°C cask.
• Count bottles and cans by hand. Full, half-full, empty—separate piles. Don’t estimate “about 6 cases”; count 6 cases.
• Reconcile against till data the same day. Pull your EPOS report for the 24 hours since your last count. Don’t wait until Friday.
• Log every comp, taste, and wastage the moment it happens, not retrospectively. One person logging it means it actually gets logged.

The first time I ran this routine, it took two hours. By week three, it took 45 minutes. By week six, I knew exactly which lines were bleeding and where the problem was.

Variance by Line: Where Your Money Goes

The number that actually matters is wet GP by line, not a single headline stock figure. You need to know whether you’re losing money on Guinness, on spirits, on lager, or on wine—because the answer changes what you fix.

Here’s why:

• Spirits hide losses in over-pouring. A free-poured measure can drift 7ml without anyone noticing. Train one person to pour tight and another to pour generous, and your spirit variance swings by 4–6%. That’s not theft; that’s training.
• Draught hides losses in cellar control. Temperature fluctuations, bad line cleaning, and lazy purging waste litres. A keg that sat at 8°C instead of 4°C might lose two litres to spray and waste before anyone knows.
• Bottled beer hides losses in stock rotation. Old stock sits at the back. New stock goes forward. Bottles get knocked. A 2–3% variance on bottles is often just turnover waste and damage.
• Wine hides losses in spillage and sampling. Wine is poured slowly. Glasses chip. Tasting is constant. A 3–4% variance on wine is normal if you’re doing any quality control at all.

I built a spreadsheet that breaks variance down by category. Spirits, draught, bottled, wine, soft drinks. I can see in one glance which line is drifting. If spirits variance is 6% but draught is 2% and bottles are 1%, I know my problem is behind the bar, not in the cellar. That’s actionable. A spreadsheet that just says “total variance 3.5%” tells you nothing.

Common Mistakes That Hide Variance

I see the same mistakes in every pub I’ve worked in. Here are the ones that cost the most:

• Counting closing stock without opening stock. You know what you have now. You don’t know what you started with. Unless you log opening stock, variance is meaningless. (And yes, opening stock is yesterday’s closing stock—that’s why the same-day reconciliation matters.)
• Forgetting to account for delivery day variance. A delivery on Wednesday changes your stock count. If you count on Monday and Thursday, Wednesday’s delivery throws off your numbers. Always note the day you count and what happened that day.
• Not separating theft from waste. They’re the same number on a spreadsheet. They’re not the same problem. Theft needs investigation. Waste needs training. Measure them separately.
• Relying on brewery stocktaker reconciliation. The brewery comes in once a quarter, counts everything, and tells you your variance. By then you’ve lost three months of money and forgotten what caused it. Weekly counts catch it the same week.
• Not logging comps and tastings in the till. The bartender comps a pint because the head was flat. That’s recorded nowhere. Your variance goes up by one pint. A proper till should have a “waste” or “comp” button. If it doesn’t, use a tally sheet and reconcile it to the count.

Tools You Need (And Don’t Need)

You don’t need expensive software or hardware. You need three things:

• A set of digital scales. £15 on Amazon. Accuracy to 1g. Use it to weigh every open spirit bottle. This single tool caught more losses in my pub than anything else.
• A dipstick. £8. Measure every cask and partial keg. No guessing at levels.
• A note of your till data. Pull your EPOS report for the same 24-hour window every week. If your EPOS doesn’t give you line-by-line sales, find one that does. SmartPubTools includes a cellar tracking screen that logs variance by line, but you don’t need software to start—you need discipline.

If your spreadsheet is clean, your routine is consistent, and you reconcile on the same day every week, you will see variance that matters. You’ll catch a 1% drift within a fortnight instead of waiting three months for the brewery stocktaker.

But if you’re running ten spreadsheets, counting on different days, and reconciling once a month, you’re not tracking variance—you’re hoping it stays small. Most pubs that move from a messy spreadsheet to a disciplined count claw back 1–2 GP points within a couple of months. That’s £2,000–£4,000 a year in a mid-sized pub. The scales and the dipstick pay for themselves in the first week.

If you want to move beyond spreadsheets without the complexity of full pub management software, the StockTap pub stock app is built specifically for this: weekly variance tracking, line-by-line logs, cellar dip records, and till reconciliation in one place. No subscription. £97 once. It works on any device and syncs across your phone, tablet, and laptop. But the routine matters more than the tool. If you’re not committing to a weekly count, no app will save you.