Bar inventory spreadsheet: what actually works

Last updated: 26 June 2026

Most pubs are haemorrhaging money through stock they can’t account for, and they’ll never find it in a spreadsheet they update once a quarter. A 1% stock loss on wet sales quietly costs a typical pub £3,000–£5,000 a year — and that’s money that walks out the door on the same day the loss happens. You won’t see it by looking at a total figure; you’ll only catch it with a counting routine that’s so simple it becomes a habit, not a chore. This article shows you exactly how to build a bar inventory spreadsheet that works, why the spreadsheet itself is less important than what you do with it, and how to know whether you’re actually making money on each line.

Why Most Bar Inventory Spreadsheets Fail

I spent four years running stock on a tangle of spreadsheets — one for spirits, another for beer, another for mixers, and a fourth that no one quite understood. I’d update them once a month, or sometimes not until the brewery’s stocktaker came in. The figures never made sense. By the time I knew there was a problem, the problem was three weeks old and impossible to trace. That’s when I realised the spreadsheet wasn’t the problem; the routine was.

Most bar inventory spreadsheets fail because they’re updated too infrequently and measure the wrong things. A spreadsheet you look at once a month is a historical record, not a management tool. By then, you’ve lost a month’s worth of stock variance to overpouring, line waste, missed pours, or genuine loss. You can’t act on data you see three weeks after the fact.

The second reason spreadsheets fail is that they measure total stock value instead of GP by line. You might know you’ve got £4,200 of stock in the cellar, but that tells you nothing about whether your spirits are being poured at 25ml or 32ml, whether your draught lines are losing 2 pints a day to waste, or whether your till is ringing the right amount. A single headline number hides all the real problems.

Third, most spreadsheets are built in Excel by someone who doesn’t run a pub, so they’re beautiful but unusable. Too many fields. Too much data entry. Too much room for human error. By Thursday night, no one’s filled it in properly, and you’re working from incomplete guesses.

The Weekly Count Routine That Catches Loss

Here’s what works: one person, the same time every week, 45 minutes, measuring the same things the same way. I chose Tuesday morning before service. That’s when I’m not rushed, the till’s closed, and I can focus.

At my own pub, I built a simple count routine around a dipstick and a set of scales. I dip every cask and partial keg, I weigh every open spirit bottle, I note any spillage or waste from the previous week, and I reconcile against till data the same day. The weekly variance went from guesswork to a number I could trust within a fortnight. More importantly, when I saw a pattern — say, vodka variance creeping up by 2 bottles a week — I could act on it immediately instead of waiting for a quarterly stocktake.

The most effective way to run a bar inventory spreadsheet is to count the same way every week, measure only what matters, and reconcile till data on the same day as the count. This catches stock loss within seven days, not seven weeks.

Your weekly count should take no longer than 45 minutes for a standard pub, and should cover:

• Cask and partial keg depth (dipstick reading)
• Open spirit bottle weight (kitchen scales)
• Any recorded spillage or wastage from the previous week
• Till sales data for the same period (pulled at the same time)

That’s it. Four things. Same time every week. Enter it into your spreadsheet the same day.

What to Actually Measure (Not Just Total Stock)

I’ve seen pub managers obsess over total stock value — “We’ve got £5,600 in stock, which is perfect” — while they’re actually losing 3 GP points on draught because the lines aren’t being cleaned properly and 2 GP points on spirits because pours are drifting. The headline number is useless without a breakdown by line.

The number that actually matters is wet gross profit by line, not a single headline stock figure. Here’s why: spirits hide losses in over-pouring (a free-poured 25ml is often 32–35ml), draught hides losses in poor cellar temperature and bad line cleaning waste, and most stock ‘theft’ is actually measurement error and forgotten wastage. You catch all three only if you’re measuring each one separately.

When you count weekly, you should track variance as a percentage of cost price, not as an absolute value. A £2 variance on a £20 keg of lager is negligible. A £2 variance on a £40 bottle of premium spirit is significant and suggests either a measurement error or an over-pour. Your spreadsheet should flag this automatically, or at least make it obvious to spot.

This is why weighing open spirit bottles matters more than most people think. If a 70cl bottle of vodka should weigh 875g (including the bottle), and you’re weighing 820g, you’ve poured or lost 55g — roughly two 25ml measures. Do that three times a week, and it’s a full bottle of margin you’re bleeding. Most bar managers have no idea it’s happening because they’re not weighing bottles.

How to Structure Your Spreadsheet

You don’t need a complicated template. You need four columns: product name, opening stock, closing stock, till sales, and calculated variance. Then a second section that breaks variance down by category (spirits, draught, lager, cider, wine) so you can see patterns.

Here’s the structure that works at my pub:

• Column A: Product name (e.g., Stella Artois cask, Tanqueray bottle)
• Column B: Week-on-week opening stock (measured the same way as closing stock from the week before)
• Column C: Closing stock (dipstick reading, bottle weight, whatever your count method is)
• Column D: Till sales quantity (pulled from your EPOS the same morning)
• Column E: Calculated variance (a formula: opening + purchases – closing – till sales = variance)
• Column F: Variance as % of cost price (highlights which lines are bleeding hardest)

Don’t go beyond six columns. More than that, and people stop filling it in accurately. Your spreadsheet should take 10 minutes to fill in once you’ve done your physical count. If it takes longer, you’ve built something too clever.

One important detail: make sure your EPOS is ringing everything. Most stock loss isn’t actually loss — it’s a till error. You’ve poured the drink, but it didn’t ring, or it rang at the wrong price, or someone forgot to ring it at all. SmartPubTools users often find that reconciling till data against physical count reveals ring-miss before it becomes a pattern.

Common Objections — Answered

I don’t have time to stocktake every week

Yes, you do. 45 minutes. Once a week. This is not optional — it’s the only thing that prevents you losing £3,000–£5,000 a year. You spend that much time on paperwork you don’t need. This is the paperwork that saves money. Prioritise it, or accept the loss.

My spreadsheet works fine

If you’re counting once a month or less, it doesn’t. You might think it’s working because you’re not seeing dramatic variances, but that’s usually because you’re averaging out small losses across a longer period. A variance that’s invisible at the monthly level becomes obvious at the weekly level. If you’ve never tried weekly counting, you don’t know what you’re missing.

Do I really need special equipment?

No. A dipstick costs £4. Kitchen scales cost £15. Both last years. You probably already have the scales in your kitchen. The dipstick is the only real purchase. Everything else is discipline and routine.

Won’t the brewery stocktaker just do it?

No. The brewery stocktaker comes in every six weeks (if you’re lucky) and counts everything for billing purposes. They’re not trying to help you manage variance — they’re reconciling what you owe them. By the time they visit, your stock loss is a month old. You need to know today whether something’s wrong. The brewery won’t tell you.

Is an app safer than a spreadsheet for my records?

If your spreadsheet is on a shared drive or your laptop, yes, an app with cloud backup is safer. But “safer storage” isn’t the real issue — accuracy is. You can have the most secure spreadsheet in the world and still miss stock loss because you’re not counting weekly. If you are counting weekly, whether you record it in Excel or a dedicated tool doesn’t matter much. What matters is consistency.

When a Spreadsheet Stops Working

A spreadsheet works fine for one pub with one person doing the count. It gets messy when you’ve got two or three pubs, or when multiple staff members need to input data, or when you want to trend variances over 12 weeks and spot seasonal patterns. At that point, a spreadsheet becomes a liability because you’re either duplicating data entry or losing information when people don’t fill it in the same way.

The signal to move beyond a spreadsheet is usually: “I’m spending more time managing the spreadsheet than I am acting on the data it produces.” Or: “I’m not confident the numbers are consistent because different people fill it in differently.”

If you’re at that stage, StockTap pub stock app replaces the spreadsheet entirely and adds the things a spreadsheet can’t do — automated variance alerts, week-on-week trending, reconciliation with till data in real time, and a cellar log that tracks temperatures, line cleans, and cask rotation dates alongside your counts. It costs £97 once, no subscription, and works on any device, so your whole team is counting the same way whether they’re using a phone on the floor or a laptop in the office.

But honestly, if you’re not doing weekly counts yet, start with the spreadsheet. Build the habit first. The tool is secondary.