Pub stocktaking spreadsheet problems

Last updated: 26 June 2026

Most pub stock loss isn’t theft—it’s measurement error buried inside your spreadsheet and cellars. I spent three years running stock on a tangle of half-updated worksheets, losing track of partial kegs and spirit measures, before I realised the variance number I was reporting to the brewery was basically guesswork. Once I started a proper weekly count routine with a dipstick and a set of scales, the number became trustworthy in a fortnight. The same gap that’s costing you money is probably hiding in your spreadsheet right now, not in your till.

A typical pub that loses just 1% on wet sales is haemorrhaging £3,000–£5,000 a year in silent leakage. You won’t see it as one number; you’ll see it as “variance” or a slightly soft GP margin. Most pubs that move from a messy spreadsheet to a disciplined, weekly count routine claw back 1–2 GP points within a couple of months. This article shows you exactly where spreadsheets fail, what actually causes stock loss in a pub, and how to set up a counting system you can trust.

Why Spreadsheets Fail at Pub Stocktaking

Spreadsheets fail because they separate data entry from the physical act of counting, which creates lag, forgetfulness and measurement inconsistency. You count a cask on Tuesday, you enter it on Thursday, you update the formula a week later, and by then you’ve forgotten how full it actually was. The delay between the physical reality and the number on screen is where the variance lives.

I’ve watched dozens of operators manage stock on a single spreadsheet that gets updated whenever someone remembers to. One column has last month’s partial kegs, another has yesterday’s deliveries that nobody’s reconciled yet, and the opening stock line uses an old closing figure. The formula calculates a variance number that looks scientific but is actually built on foundations of approximation. It looks like you’re in control; actually, you’re just generating a report that hides uncertainty.

The second failure mode is that spreadsheets don’t force a consistent measurement method. You dip a cask one week by eye, the next week with a dipstick, the third week you estimate because you’re in a hurry. Measurement variance compounds quickly. A keg you think is half full might be 42% or 58%, depending on who’s counting and what tool they’re using. Over four weeks, that’s not a rounding error—that’s a stock loss signal you’ll misread as a real problem.

The most effective way to fix spreadsheet variance is to separate weekly operational counts from monthly financial reconciliation, and to use the same physical measurement method every single time. Weekly counts should be quick, consistent and tied to till data on the same day. Monthly variance analysis should then be a forensic review of those weekly numbers against sales, not a new entry in a master spreadsheet.

Where Pub Stock Loss Really Comes From

Stock loss in pubs breaks down into three categories, and theft is usually the smallest. Most variance is actually measurement error, followed by forgotten wastage, then till reconciliation lag.

Spirits hide losses in over-pouring. A free-poured 25ml measure is often 32–35ml on the scales. That’s not dishonesty; it’s gravity and the way the bottle tilts. Over 100 pours a week per spirit line, that’s 700–1,000ml of unrecorded giveaway. Over a month, that’s a full bottle—and your spreadsheet will flag it as variance, not as a built-in cost of the free-pouring method. You need to weigh open bottles on scales every week, not estimate the level by eye.

Draught loss hides in cellar temperature, line cleaning and pressure tuning. A cold cellar can add 10–15% creep to waste. A dirty line wastes a pint every clean-down. Bad pressure tuning causes cavitation and foam loss. These aren’t spreadsheet problems; they’re physical operational problems that show up in your spreadsheet variance. If you’re not monitoring cellar temperature daily and recording line cleans, your stock variance is telling you absolutely nothing.

Most stock ‘theft’ is actually measurement error and forgotten wastage that never gets written down. You use a bottle of gin for a promotion that didn’t go into till. You bin three pints of flat bitter. You have a spillage while restocking. These aren’t entered in the spreadsheet because nobody thought to record them. They appear as variance instead.

Till reconciliation lag is the final culprit. You count stock on Friday evening and reconcile till that evening too. But some card transactions settle on Monday, some pints from Friday evening go into Saturday’s till, and some pours don’t get rung up at all (human error, not theft). Your opening position for next week’s count is already wrong before you start. SmartPubTools solves this by forcing same-day reconciliation: you dip, you count, you pull till data, you reconcile, all in the same session.

The Weekly Line Check That Actually Works

A proper weekly count takes 30–45 minutes and requires one person with a dipstick, a set of scales, a till report and a notebook (or a simple form on your phone). Here’s the routine I built and still use.

Monday morning count (after the weekend rush settles)

• Pull the till report for Friday and Saturday service—actual pints and bottles sold, split by product line.
• Dip every cask and partial keg in the cellar. Write down the depth in millimetres or use a dipstick scale if you have one.
• Weigh every open spirit bottle on scales. Record the weight.
• Check cellar temperature and log it on a simple wall chart.
• Note any line cleans done, any products removed, any spillage or wastage.

This takes 20–30 minutes. The data goes into a simple spreadsheet or into StockTap pub stock app, which does the maths for you and flags anything that deviates from the expected range based on sales.

The number that actually matters is wet GP by line, not a single headline stock figure. Your bitter line should sell 40 pints a day and lose 1 pint to waste. If you’re selling 40 but variance is 5, something’s wrong—over-pouring, a leak, line cleaning waste or measurement error. If you’re selling 40 and variance is 1, you’re efficient. A lager line with 3% variance is different to a bitter line with 3% variance because of the cost and the expected waste profile.

Friday reconciliation (once a week)

At the end of the week, reconcile that week’s opening stock, purchases, sales and closing stock. The variance should sit between 0.5% and 2% for most pubs. If it’s higher, you have a specific problem to investigate. If it’s lower, you’re running tight and that’s good—but check you’re not undercounting waste.

This weekly rhythm means you catch problems in days, not in the monthly stock-take that happens three weeks later when the trail is cold and you’ve already lost money you can’t explain.

Equipment You Actually Need

You do not need special equipment. You need:

• A simple metal dipstick (costs £8–15) to measure cask depth consistently.
• A set of kitchen scales accurate to 0.1kg (£12–30).
• A thermometer for the cellar (£5–10).
• A notebook or a form on your phone.
• Access to your till data exported daily or weekly.

That’s £40 in total equipment, and it works just as well as an expensive cellar management system. The difference between cheap and expensive is not the equipment—it’s the discipline. If you use the dipstick the same way every week, the number becomes reliable. If you use it sometimes and estimate other times, you’ve wasted your money.

Some operators ask: “Won’t the brewery stocktaker just do this?” The answer is no. The brewery stocktaker is there to verify your account once a month or once a quarter, not to run your business. If you rely on the brewery count as your primary control, you’re flying blind for 30 days at a time, losing money you’ll never recover. Your weekly count is your early warning system. The brewery count is your sanity check.

Spreadsheet vs. Digital: What You Really Need

Should you stay on spreadsheet or move to a digital tool? The honest answer: it depends on your discipline, not the technology.

A spreadsheet works fine if you:

• Update it the same day every time (no exception).
• Use the same measurement method every week (no estimation).
• Reconcile till data on the same day you count (no lag).
• Review variance by product line, not as a headline number (do the analysis).

Most operators don’t do this. Most spreadsheets get updated sporadically, use mixed measurement methods, and sit orphaned after a few weeks. Then the operator asks why variance is uncontrollable, and the answer is usually: “Because the spreadsheet is broken,” when actually it means “I’m not using it consistently.”

A digital tool like StockTap forces consistency. You can’t submit a count without entering a dip depth or a weight. You can’t submit without matching it to till data. The variance flags automatically. It works on any device, so you can do the count on your phone and sync it back to the office. But again—the tool doesn’t fix a broken counting routine. It just makes a good routine harder to skip.

A digital tool is safer than a spreadsheet for records because it timestamps every entry, prevents overwriting historical data and creates an audit trail that a spreadsheet can’t match. If the brewery audits you, they want to see a consistent method and a clear record. A spreadsheet with formulas you’ve edited and columns you’ve deleted won’t satisfy them. A tool with timestamped entries will. But if your concern is purely local control and you’ve got the discipline to run it properly, a good spreadsheet is fine.

Common Stocktaking Mistakes Operators Make

I’ve made all of these, and I’ve watched other licensees make them too.

Mistake 1: Counting monthly instead of weekly. A month is too long. Variance accumulates. A line that’s drifting 0.5% per week will show as 2% at month-end, and by then you’ve lost money across four weeks that you can’t trace back. Weekly counting lets you spot the drift early.

Mistake 2: Counting on the same day the brewery stocktaker visits. You panic and do a crash count the morning of the brewery visit, using different measurement methods than usual. Then your internal numbers don’t match the brewery numbers, and you look unreliable. Count on your schedule, not theirs. The brewery count will be close—it won’t be exact, and that’s normal.

Mistake 3: Not reconciling till data at the same time as the physical count. You count stock on Monday, you reconcile till on Wednesday. By then, Tuesday’s sales have landed, discrepancies have compounded, and you don’t know where the problem started. Count and till reconciliation must happen in the same session, the same day.

Mistake 4: Using weight for casks and eye estimation for partials. You scale your sealed bottles (good) but then guess the level on a half-full keg by looking at the bung. That works until it doesn’t—and when it doesn’t, you’ll have been drifting for weeks. Use a dipstick or a scale on every cask, every time. Consistency over speed.

Mistake 5: Not writing down wastage as it happens. You clean the beer line, you bin two pints. You do a promotion, you use a bottle of gin. You have a spillage. These get entered in a mental note, not in the log. At month-end, you’ve forgotten half of them. Keep a simple waste log in the cellar. Five minutes a day beats an hour of confusion later.