Pub Stocktaking Excel Template — Build It Right

Last updated: 26 June 2026

A 1% stock loss on wet sales quietly costs a typical pub £3,000–£5,000 a year — and most operators never see it coming because they’re not measuring the right thing. You’re probably looking at a headline stock figure once a month and thinking you’re in control. You’re not. The real damage happens weekly, in the detail: a draught line that’s running warm, spirits being over-poured, a partial keg you forgot to log, wastage that never made it to the bin sheet.

The good news: a proper pub stocktaking Excel template takes the guesswork out. But not the generic spreadsheet your pubco gave you three years ago. I’m talking about a working template built around what actually happens in a busy bar — dips, weights, till reconciliation, variance by line — checked every single week so variance becomes a number you trust, not a mystery.

This guide shows you exactly how to build one, why it matters more than you think, and what to measure so you actually claw back those hidden losses.

Why a Proper Excel Template Beats a Spreadsheet Mess

When I took on my Marston’s pub, I was running stock on a tangle of spreadsheets and still losing track of partial kegs and spirit measures. Every month the stock figure came out different, and I had no way of knowing if it was loss, measurement error, or just bad record-keeping. Most pubs are in that same place right now.

The most effective way to stop stock loss is to measure it weekly by product line with the same method every time. Not a monthly recount. Not a brewery stocktaker doing a spot-check. Your own routine, your own numbers, your own accountability. A template forces consistency.

Excel works because it’s:

• On every device you own (phone, laptop, till office)
• Editable without IT support or software updates
• Already familiar to your staff
• Capable of simple formulas that highlight variance instantly
• Backed up by your cloud service (if you use one)

What it’s not is a replacement for discipline. A spreadsheet works only if you fill it the same way every week, measure the same lines, and reconcile against till data on the same day. If you’re too busy, too tired, or too chaotic, neither Excel nor any other tool will save you.

What to Actually Measure (And Why Most Pubs Get This Wrong)

This is where most pub operators trip up. They measure the headline stock value — total value of stock on hand at a given date — and wonder why it changes so much. That number is almost useless for day-to-day control.

Wet gross profit by product line is what tells you whether you’re making money on beer, spirits, wine and soft drinks. Spirits hide losses in over-pouring (a free-poured 25ml is often 32–35ml in a busy service). Draught hides it in poor cellar temperature, bad line cleaning waste, and leaks you can’t see. Most stock ‘theft’ is actually measurement error and forgotten wastage. The moment you start tracking loss by line, you see where the damage is.

Your template needs to track:

• Draught: Cask and keg dips (in gallons or litres), cask weight if you use scale measurement, line waste recorded daily
• Spirits: Open bottle weights (in grams), opening and closing measures, till count reconciliation
• Wine: Opening bottle count, closing bottle count, bottles opened and wasted, till count
• Soft drinks: Opening and closing stock level (by shelf or bin), waste record

Then you calculate: Opening stock (at cost) + Purchases (at cost) − Closing stock (at cost) − Till revenue (at cost of goods) = Variance. A positive variance is loss (either actual or measurement error). A negative variance usually means you under-measured something.

How to Build a Working Stocktaking Template in Excel

You don’t need fancy formatting. You need columns that force the right questions.

Step 1: Set Up Your Product Lines

Create a worksheet for each week, or one master sheet with tabs for each product category (Draught, Spirits, Wine, Soft Drinks). In the draught section, list every cask, keg and line by name:

• Cask or keg name (e.g. “Guinness Keg A”)
• Opening dip (in litres or gallons)
• Date opened
• Closing dip (same measurement type)
• Litres/gallons sold (calculated)
• Till revenue for that line (from your EPOS)
• Expected cost of goods sold (litres sold × cost per litre)
• Variance (till revenue at cost − expected COGS)

Step 2: Draught Section — Dips and Temperature

For every cask and keg, record:

• Opening dip in litres (use a proper dip stick — not guesswork)
• Cellar temperature (you need one of those wireless thermometer units for about £15)
• Any line cleaning waste or spoilage recorded that week
• Closing dip on the same day every week (Friday or Monday work best)

The dip is your most important number. A cask sitting at 18°C will pour differently than one at 12°C. If your variance is wild, temperature is often the culprit.

Step 3: Spirits Section — Weights and Measures

For every open spirit bottle, record:

• Spirit name and bottle size (e.g. “Absolut Vodka 70cl”)
• Opening weight in grams (use a kitchen scale — £8 from Argos)
• Closing weight in grams, recorded on the same day every week
• Grams consumed (opening − closing)
• Predicted measures (grams consumed ÷ 27.5 grams per 25ml measure, if using imperial)
• Actual measures from your till (or from manual count if you hand-pour)
• Variance in measures (predicted − actual)

If you’re over-pouring by 5–7 measures per bottle per week, that’s the leak. That’s where you catch it.

Step 4: Till Reconciliation Column

Pull a report from your EPOS for that week showing:

• Total revenue by product category (draught, spirits, wine, soft drinks)
• Total revenue by specific line (if your till tracks it)
• Revenue at menu price, then converted to cost using your pour cost % or a column for cost per unit

This is where measurement error shows up. If your till says you sold 40 measures of vodka but your bottle weight only accounts for 32 measures, something is wrong. It’s usually the till count, but sometimes it’s under-measurement.

Step 5: Variance Calculation

Add a final column: Variance in £. Use a simple formula: (Expected COGS − Actual COGS from till) × your bottle cost. A positive number means loss. A negative number usually means measurement error (you under-recorded opening stock, or the till is under-counting).

Setting Up Your Weekly Stocktaking Routine

The template only works if you use it. Build a rhythm. I recommend:

Every Friday at Close (or Monday at Open)

Pick one day. The same day. Every week. Consistency is what makes the numbers trustworthy. On that day:

• Dip every cask and partial keg in the cellar (takes 10 minutes if you’re organised)
• Weigh every open spirit bottle (takes 5 minutes)
• Count wine bottles on display (takes 3 minutes)
• Record cellar temperature
• Hand this data to whoever updates the Excel sheet — ideally you, not a staff member

Reconcile against till data the same day. Don’t wait until Monday. If there’s a discrepancy, you can still trace what happened on Friday service.

Total time: 20 minutes, once a week. Not 20 minutes a day. One of the biggest objections I hear is “I don’t have time to stocktake every week,” but a proper weekly check takes less time than a monthly monthly deep-dive recount, and catches losses before they pile up.

Tracking Variance and Spotting Trends

After four weeks, you’ll start seeing patterns. If spirits variance is consistently +£12 per week (you’re losing money), start investigating:

• Are you over-pouring? Measure a few pours with a jigger next service and compare to what your staff pours free-hand
• Are you forgetting to ring certain drinks? Run a bar audit with your manager
• Is one member of staff responsible? This is rare, but check till transactions by user if your EPOS allows it

If draught variance is wild (swinging between −£20 and +£20 per week), cellar temperature is almost certainly the issue. A cask at 18°C pours different volumes than one at 12°C, and that difference shows up in variance. Get a thermometer. Keep it between 12–14°C.

Add a Trend column to your Excel sheet. Calculate a four-week rolling average of variance by line. That’s your real number. One week of +£15 variance is noise. Four weeks of +£15 variance is a problem that needs fixing.

The reason this works is simple: Weekly variance tracking by product line reveals stock loss patterns that monthly snapshots miss completely. By the time you do a monthly recount, you’ve forgotten where the loss came from. By week two of weekly checks, you know exactly which line is bleeding money.

Common Template Mistakes (And How to Avoid Them)

Mistake 1: Mixing Measurement Methods

If you dip one cask in litres and another in gallons, your variance calculation breaks. Pick one. Stick with it. If you’re in the UK, litres are easier (most British draught is measured in litres). If you’re in the US, gallons are standard. Don’t mix them.

Mistake 2: Forgetting to Include Waste

A cask you had to empty because it was infected, a bottle of wine you dropped, measures you poured but didn’t sell — these all matter. Create a Recorded Waste column in your template. When a bottle is damaged, write it down immediately. When a cask has to be pulled, note it. When staff make mistakes and you comp a drink, record it. Waste that isn’t recorded looks like loss.

Mistake 3: Not Reconciling Till Data

Your Excel template is only as good as your till data. If your till is under-counting drinks (common in pubs with older EPOS systems), your variance will always show loss even if you’re not actually losing stock. Pull a weekly EPOS report. Check it against your physical counts. If they don’t match, investigate the till before you blame staff or suppliers.

Mistake 4: Changing the Template Every Few Weeks

Once you’ve built it, leave it alone for at least two months. If you keep moving columns around, adding new fields, or changing how you calculate variance, the numbers won’t be comparable week-to-week. Build it right the first time. Use it exactly the same way for eight weeks. Then evaluate whether you need to change anything.

Mistake 5: Only Looking at Spirits, Ignoring Draught

Most pub operators obsess over spirits (because it’s easy to measure and feels tangible) and ignore draught. But draught is usually where the bigger loss hides, because temperature variation and line cleaning waste are so easy to forget about. Track both equally. Draught is typically 60% of wet sales — don’t leave it unmeasured.

The other common mistake: thinking an Excel template is a substitute for actual change. I built my first template, ran it for two weeks, and realised that without a cellar thermometer and a proper dip stick, the numbers were still guesswork. You need the right equipment: a dip stick (£6), a kitchen scale (£8), and a cellar thermometer (£15). Total investment: under £30. Without those, your template is just a pretty spreadsheet.

Moving Beyond Excel: When a Template Isn’t Enough

Here’s what I’ve learned running my pub for 15 years: an Excel template is brilliant for starting out. It forces discipline, it reveals patterns, and it costs nothing. But if you’re running multiple lines, or your staff turnover is high, or you’re trying to track GP by shift or by staff member, Excel becomes unwieldy. You start needing vlookups and pivot tables, and suddenly you’re spending more time managing the spreadsheet than actually using it.

That’s when a purpose-built tool becomes worth considering. The StockTap pub stock app was built by a working pub landlord (not a software company) around the exact problem this article solves — weekly variance tracking, cask dips, spirit weights, till reconciliation, all in one place, on any device, without any of the Excel drama. It’s £97 one-off, no subscription, and it does the calculation and variance tracking automatically. But you don’t need it to start. A disciplined Excel template will catch 80% of the loss that’s hiding in your pub right now.

Why Weekly, Not Monthly?

I hear this objection constantly: “My spreadsheet works fine” and “Do I really need special equipment?” Let me be direct. Your spreadsheet probably does work if you’re tracking something. But most spreadsheets track headline stock value, not variance by line. And most operators don’t have the discipline to measure the same way twice. Weekly checking — same day, same method, same reconciliation — is what separates a number you can trust from a number that’s just a guess.

Another objection: “Won’t the brewery stocktaker just do it?” The answer is no. Your brewery’s stocktaker comes quarterly or annually. By then, months of loss have stacked up. They’re also checking the brewery’s liability, not your profitability. You need to know your variance in real time so you can fix it immediately.

Last one: “Is an app safer than a spreadsheet for my records?” Yes. A cloud-based system (whether Excel on OneDrive or a dedicated app) is more secure than a spreadsheet sitting on a till computer with no backup. But the safety isn’t the point. The point is the method. Weekly, consistent, by-line, reconciled to till data. That method works in Excel or in an app. The tool is less important than the discipline.

For more information, visit SmartPubTools.