A free pub stocktake template that actually works, and how to use it

Here's the template: download the Excel version or the CSV for Google Sheets. No email address, no "book a demo" button, no watermark. It's the sheet I'd hand a mate taking on his first pub. The rest of this page is how to use it without it lying to you.

I looked at what ranks for "pub stocktake template" before writing this. Most of the pages have no template on them. They describe a spreadsheet you could build yourself, column by column, then pitch their software at the bottom. The ones that do have a file are American, so everything is in ounces and liquor bottles by the case, or they're restaurant inventory sheets with nowhere to put a cask. This one is built for a UK bar: pints, 25ml measures, ex-VAT prices, and GP% the way your accountant and your area manager mean it.

What's in it

Three sheets. A count sheet, a summary, and a page of instructions you can delete once you've done it twice.

The count sheet has eight columns you fill in and four that calculate themselves. Yours: category, product, unit size, cost price ex VAT, sell price inc VAT, opening count, delivered, closing count. The sheet's: consumed, cost of sales, expected revenue ex VAT, and GP% per line. The summary adds it all up, takes your till figure, and shows the variance in pounds.

One decision makes the whole thing work: you count in selling units. Pints for draught, 25ml measures for spirits, bottles for the fridge. Not kegs, not "about half a bottle of vodka". The moment you mix units, the arithmetic is fiction. The template has a converter block for exactly this, which brings us to the numbers most templates get wrong.

The container arithmetic

What each container actually holds, in sellable UK units. Practical yields are author's arithmetic; see the draught wastage guide for the workings.

Container	Holds on paper	What you'll actually sell
50L keg	88 pints	84 to 86
30L keg	52.8 pints	50 to 51
Firkin (9 gallon cask, 40.9L)	72 pints	mid-60s after sediment and stillage
70cl spirit bottle	28 x 25ml	28
1.5L spirit bottle	60 x 25ml	60
75cl wine bottle	4 x 175ml plus a splash	4

The gap between the two right-hand columns is not theft. It's froth, line cleaning, pull-through and sediment. British Beer and Pub Association guidance says a pint served with a head only has to be 95% liquid, so a slice of every keg legally never reaches a glass. If your template expects 88 sellable pints from every 50L keg, it will report a thief who doesn't exist, every single week. Mine expects 88 in and lets you judge the gap against the yields above. If your cellar consistently does worse than 84 from a 50, that's a real problem worth chasing, and the keg calibration guide is where I'd start.

How to run a count with it

Same day, same time, every week. Before open or after close, never mid-session. Walk the building in one direction: cellar, then back bar, then fridges, so you never count a shelf twice. Two people is twice as fast, one calling, one typing.

Draught: dip or weigh the part containers, count the full ones, convert to pints using the table. Spirits: full bottles times 28, part bottles in tenths if you're in a hurry, on the scales if you want the truth. An eyeballed tenth on a 70cl bottle is worth about £10 of revenue, and eyes are kind to themselves. The part bottles guide covers the weighing method. Fridges: count bottles, job done.

First count takes 60 to 90 minutes on a typical wet-led bar because you're building the product list as you go. After that, 40 minutes is normal. If it's taking two hours every week you're counting too many lines: start with your top thirty by value, because that's where the money leaks, and add lines as you go.

The formulas, written out so you can check my working

Never trust a spreadsheet you can't audit, including mine. Four formulas do everything:

• Consumed = opening + delivered − closing. What left the shelf, in units.
• Cost of sales = consumed × cost price ex VAT.
• Expected revenue ex VAT = consumed × (sell price ÷ 1.2). The ÷1.2 strips the 20% VAT out of your shelf price, because that slice was never yours.
• GP% = (expected revenue − cost of sales) ÷ expected revenue.

The ÷1.2 is where most home-made sheets go wrong. Compare an inc-VAT selling price against an ex-VAT cost price and your GP% comes out about 12 points too flattering, and every decision you make off it is wrong. I've written up the ex-VAT trap separately because it catches so many people. Once your GP% is real, check it against the UK pub GP benchmarks rather than a number someone said in a Facebook group.

The summary sheet then does the one comparison that matters: expected revenue against what the till says you took, both ex VAT. That difference, in pounds, is your variance. What counts as normal is its own subject, covered in the acceptable variance guide, but as a rule of thumb: under 1% of wet sales you're tidy, over 2% something specific is wrong and it has a name.

Five ways a spreadsheet will lie to you

Stale cost prices. Your supplier moved the keg price in February and your sheet still says last summer's number, so your GP% looks fine while your bank balance doesn't. Update the cost column from the delivery note, not from memory. I've written a separate guide on catching supplier price rises because it's the single most expensive lazy habit in the trade.

Mixed units. Kegs one week, pints the next, and the consumed column turns to noise. Pick the selling unit and never deviate.

Eyeballed part bottles. Forty optics, each guessed a tenth kindly, is a phantom loss that sends you accusing the rota instead of the guess.

Unlogged cleaning waste. Every line clean pulls two to three pints of saleable beer down the drain per line, and if it's not recorded the sheet books it as missing stock. The line cleaning guide has the weekly arithmetic.

A broken formula. One deleted row, one cell dragged the wrong way, and every number below it is quietly wrong with no error message. This is the one that eventually gets everyone, and you find out months later, usually during an argument you're losing.

When the spreadsheet stops being enough

Straight answer: for a micropub or a small bar with forty lines, this template plus discipline is a perfectly good stock system, and I'd rather you used it well than paid for software you won't open. I've said the same in the micropub guide.

It stops being enough at scale, or the first week you find a variance you can't explain. The sheet can tell you £180 is missing. It can't tell you whether that's froth, a miscoded till button, short deliveries or the Tuesday shift, because it doesn't see your till data or weigh your bottles. That's the point where the free tool's real cost shows up as hours and guesswork, and the switching guide covers how to move without losing your history. Until then, download the sheet, count on the same day every week, and argue with numbers instead of feelings.