Free pub stock spreadsheet: build yours in 2026

Last updated: 11 April 2026

Most pub landlords managing stock manually spend Friday afternoon in the cellar with a clipboard and a calculator — and still get the numbers wrong by Monday. The real issue isn’t that spreadsheets are bad; it’s that a free pub stock spreadsheet only works if you actually use it consistently, and the moment your pub gets busy enough to need one, you’re too stretched to maintain it properly.

If you’re running a wet-led pub, managing draught lines, cask ale, bottled stock, and spirits across multiple shifts, you know how quickly a manual stocktake becomes a nightmare. You feel the pain of finding discrepancies three days after the count, having no way to trace where the loss happened, and wasting hours trying to work it out.

I’ve built and tested stock management systems across multiple pub formats — from quiet village locals to Teal Farm Pub in Washington, Tyne & Wear, which runs food service, quiz nights, and match day events simultaneously across 17 staff. And I can tell you: the difference between a spreadsheet that actually works and one that sits unused is structure, simplicity, and knowing exactly when to abandon the spreadsheet altogether.

In this guide, I’ll show you how to build a functional free pub stock spreadsheet that you’ll actually use, walk you through the real-world gaps it will have, and be honest about when you need to move beyond spreadsheets to integrated pub management templates or proper EPOS stock tracking.

What you’ll learn here is based on actual pub operations — not generic hospitality advice. You’ll get a spreadsheet structure that works, templates you can use immediately, and the operational insight that only comes from managing inventory across a busy bar.

Why a spreadsheet works (and when it doesn’t)

A free pub stock spreadsheet is a valid tool for tracking inventory in a small, quiet pub with consistent staff and simple product lines. It breaks down the moment you try to manage it across multiple staff, multiple shifts, or when trading patterns become unpredictable.

Here’s what I mean in practice: If you’re running a village pub open lunch and evenings, with two regular bar staff and a simple range of 6 draught lines and maybe 20 spirit SKUs, a spreadsheet can work. You do a stocktake every Friday, record the count, and calculate usage from known deliveries. The maths is simple, the variables are limited, and discrepancies are usually traceable because very little has changed.

Now scale that up. Add quiz nights on Wednesdays, match day events on Saturdays, three shifts instead of two, seasonal specials, and guest draught lines rotating monthly. Suddenly you’re trying to track stock across ten people over 70+ trading hours per week. Half your staff won’t remember to record sales, you’ll miss deliveries in the spreadsheet, and when the Friday count is £200 short, you have no idea which product lost the money or when.

The spreadsheet doesn’t fail because spreadsheets are weak tools — it fails because the work required to keep it accurate exceeds the time available to do it properly. That’s not a spreadsheet problem. That’s a capacity problem.

For most UK pubs trading more than 40 hours per week with more than 3 staff, a spreadsheet is a temporary solution or a backup to a proper system. But if you’re starting from scratch with limited budget, and you’re willing to be disciplined about it, a well-designed spreadsheet can buy you 6–12 months before you need to upgrade to proper pub management software.

Building your free pub stock spreadsheet from scratch

If you’re building a spreadsheet from zero, there are three approaches: download a template, adapt a generic hospitality spreadsheet, or build one specific to your pub’s product range.

The core columns every pub stock spreadsheet needs

Whether you’re tracking draught, bottled stock, or spirits, the structure is identical. Here’s what must be in your spreadsheet:

• Product name — exact product, not generic descriptions. “John Smith’s Bitter on draught” not “bitter”. Use the same naming every time or the spreadsheet becomes unreliable.
• Opening stock — the count from yesterday’s closing, or Friday’s full stocktake. Must be accurate.
• Deliveries — every delivery logged the day it arrives, with quantity. Include the invoice date to match ordering records.
• Stock usage — this is NOT sales. Usage is draught barrels emptied, cases opened, spirit bottles used. Measured, not estimated.
• Closing stock — the physical count at the end of the day or week. Must be counted, not calculated.
• Variance — the difference between expected stock (opening + deliveries — usage) and actual closing stock. This is your loss/theft/spillage figure.
• Notes — why is there a variance? Spillage on Sat night? Staff meal? Guest pour? Damaged goods? Without notes, variances are just numbers.

That’s it. Seven columns. Most pub landlords overcomplicate this with cost per unit, profit margin, and supplier information, which belong in a separate procurement spreadsheet. Keep the daily tracking spreadsheet simple or you won’t use it.

Product setup: draught, cask, spirits, and bottles

Different product types track differently. You need separate worksheets for each category:

Draught lines: Track by keg or barrel unit, not pints. When you receive a new keg of lager, you know exactly how many pints should come out of it (usually 50 pints from a standard UK keg, slightly less if you’re including headspace and spillage). When the keg is empty, that’s your usage. Partial kegs need to be measured — if you’re halfway through a keg, use a dipstick or calculate based on pour counts if you have till data.

Cask ale: Cask tracking is harder because opening a cask is a one-time event and usage is gradual. Best practice: record when the cask is tapped (opening stock = 0 for that line), track pints sold by till data if you have it, or measure manually each shift. Closing stock = pints remaining in the cask. When it’s empty, you count it as completely used. Variance here is usually low if the cask is managed well, but spillage during tap installation and pouring is typical.

Spirits and bottled stock: Count bottles in stock, not units sold. A bottle of Jägermeister in your stock level is one unit. When a bartender opens a new bottle because the old one ran out, you record usage of one bottle (or partial bottle if you track by percentage). Closing stock is the physical count of sealed bottles plus any opened bottle with estimated contents remaining. This gets messy quickly — most pubs don’t track spirits precisely enough until they spot theft or loss.

Packaged stock (cases, multipacks, individual bottles for retail sale): Track by case or by unit — be consistent. If you sell Peroni singles at the bar and also sell 4-packs as takeaway, track them separately or you’ll confuse the numbers. Opening stock in cases, deliveries in cases, usage measured as: cases opened + singles sold from existing stock.

Deciding on timing: daily, weekly, or hybrid tracking

Most pubs can’t do daily physical stocktakes. That’s 365 counts per year, and you’ll burn out after week two.

A realistic hybrid approach: Record deliveries and usage daily (takes 5 minutes), do physical counts weekly on one chosen day (usually Friday or Monday). Friday works if you’re closed or quiet on Mondays. Monday works if you want to verify the weekend. Pick one and stick with it.

On your chosen counting day, you physically count every product, record it in the closing stock column, and the spreadsheet calculates variance automatically. You’re only doing one full physical count per week, not daily, which is sustainable.

For fast-moving lines (your house lager, house bitter), consider a mid-week spot check on one draught line just to validate that the data you’re recording matches reality. Takes 3 minutes, catches problems early.

Real-world stock tracking for draught, cask, and spirits

Now let’s get practical. This is where most pub spreadsheets go wrong — the theory is sound but real-world execution differs significantly.

Draught line tracking: the hidden variables

When you receive a keg of draught lager, you expect 50 pints out of it. In reality, you’ll get 45–47 pints because of headspace, the pour when you first tap the keg, and spillage during busy service. Most pub landlords don’t account for this in their usage calculations, so they’re constantly finding variances they can’t explain.

The solution: build your actual yield per keg based on your pub’s real numbers, not the theoretical maximum. Run one keg and measure exactly how many pints you actually get. If it’s 46 pints instead of 50, use 46 in your spreadsheet. Now when you close a keg, your usage calculation is accurate and doesn’t create phantom losses.

At Teal Farm Pub, we found that our draught lines during peak Saturday trading (card-only payments, full house, three bar staff on simultaneously) produced slightly lower yields than our theoretical calculations because of increased spillage and pouring errors during last orders. We tracked the real number and adjusted our variance targets accordingly. That single data point eliminated hours of investigation into “missing” stock that was never actually missing.

Another issue: temperature and pressure affect pour consistency. If your cellar is too warm or your regulator is failing, pints become shorter and your expected yield drops. This isn’t loss — it’s a technical problem. If you don’t note this in your variance column, you’ll think you have theft when you actually have a £2 regulator failing.

Cask ale and the opening loss problem

Cask ale introduces a tracking problem that most spreadsheets ignore: the loss during tapping and first pour.

When you tap a cask, you lose roughly a pint during the process (sediment disturbance, air displacement, initial pour). Your spreadsheet should not expect a cask marked “9 gallons” to yield 40 pints — it’ll yield closer to 35 pints after opening loss. Build this into your notes so variances don’t look suspicious.

Cask ale is also the product line where licensees catch the most staff theft, because a partially-empty cask looks normal (it’s being used), but the usage numbers don’t match till records. If your till says you sold 30 pints of cask bitter on Wednesday but the cask lost 35 pints of stock, you have a 5-pint discrepancy. That’s not an error — that’s a conversation to have with your team.

Spirit tracking and the partial bottle problem

Most pubs don’t track spirits accurately because tracking partial bottles is tedious and imprecise.

Best practice approach: Assign each spirit a designated “pour glass” (usually a shot glass for standards, a jigger for measurements), and count by glass equivalents rather than estimating bottle percentages. An opened bottle of vodka is “3 glasses remaining”, not “approximately 60% full”. This is measurable and repeatable.

Record usage as: glasses poured from opened bottles + full bottles opened during the period. Closing stock = unopened bottles + glasses remaining in opened bottles. It’s still not perfect, but it’s dramatically more accurate than visual estimates and much easier to audit.

Track your most expensive spirits (premium bourbon, cognac, gin) separately from well spirits. Your well spirits will have higher variance (more spillage, more informal pouring) and you want to see that clearly rather than averaging it across all spirits.

Weekly and monthly stocktake routines that actually stick

The difference between a pub stock spreadsheet that works and one that fails is whether the person responsible actually executes the stocktake on schedule. That sounds obvious, but most licensees plan a Friday stocktake and then consistently do it Monday because something urgent happens every Friday.

Here’s a routine that works in real pubs:

Weekly stocktake (every Friday closing)

After service, before you leave. 30–45 minutes for a typical wet-led pub with 8–10 draught lines and 30 spirit SKUs. The routine:

• Count all draught kegs and casks (how many full, how many on tap, how many empty).
• Count all open draught lines and mark the approximate level (full, three-quarters, half, etc.).
• Count all unopened cases of bottled stock.
• Count all spirits bottles and partial bottles.
• Record everything in your closing stock column.
• The spreadsheet calculates variance automatically.
• If variance is more than 5% on any product, flag it for investigation.

This is a physical count, not a calculation. It takes discipline because you want to go home, but if you do it every week consistently, it takes 30 minutes and becomes automatic.

Mid-week spot check (Wednesday or Thursday)

Pick your two fastest-moving draught lines (usually lager and bitter). Count them. Compare against expected usage. Takes 3 minutes. If the numbers look off, you catch it before the full Friday count and can investigate during the week while staff memories are fresh.

This catches two problems: staff who are underpouring or overpouring (which affects till reconciliation), and slow leaks from keg seals or tap assemblies.

Monthly variance review

After four weeks of data, open your spreadsheet and review: Which products had the highest variance? Which weeks were the variance worse than others? Did a particular staff member’s shifts correlate with higher loss?

Look for patterns. A 2% variance on spirits is normal. A 7% variance on vodka specifically while other spirits are at 2% is a conversation with your team or a sign of a pouring technique problem.

Document your findings in a separate “Notes” sheet. Over time, this becomes your pub’s variance baseline — you know that cask ale typically runs 3% loss (opening loss + spillage), draught runs 2% (yield calculation + normal spillage), and spirits run 4% (pouring, spillage, staff drinks). Anything above that baseline is worth investigating.

Common mistakes that make pub stock spreadsheets fail

Mistake 1: Tracking sales instead of usage

This is the most common error. Your till tells you that you sold 47 pints of lager on Friday. That’s not the same as how much stock you used.

A sold pint doesn’t account for: spillage (bartender pours a pint, it hits the floor), waste (last 2 pints in a keg are cloudy and you pour them out), staff drinks (bar staff get free pints, especially on quiet days), and samples (you pour a taste for a customer deciding which bitter to have).

Your spreadsheet must track actual stock removed from the bar, not till records of what was sold. That’s why draught kegs are the most honest way to track usage — when the keg is empty, you know exactly how much was used, and the variance between expected and actual is your real loss figure.

If you’re tracking spirits by till, you’re missing spillage and staff drinks. If you’re tracking by bottle count, you’re catching everything. Bottle count is slower but more accurate.

Mistake 2: Not recording deliveries on the day they arrive

Deliveries get logged three days later, or not at all, or recorded in a different spreadsheet. Your opening stock on Friday includes deliveries you haven’t recorded yet, so the variance calculation is wrong, and now your numbers are meaningless.

Rule: Every delivery is recorded in the spreadsheet on the day it arrives, with the product name, quantity, and invoice date. Takes 2 minutes per delivery. Do it immediately when the driver leaves, not “later”.

This also helps with cash flow tracking because you can match deliveries to invoices and know what’s outstanding.

Mistake 3: Changing the product names mid-year

You start with “John Smith’s Bitter” in January and by March it’s “Smithy’s” and by July it’s just “Bitter”. Now your spreadsheet has the same product tracked under three different names and the year-end numbers are useless.

Create a product list once, at the start, and stick to it exactly. If a product name changes (supplier rebrand, new bottle design), update the name everywhere and note the change date — don’t create a duplicate line.

Mistake 4: Not accounting for stock adjustments

A bottle of spirits breaks. A keg arrives damaged and you reject it. You comp a customer a drink because service was slow. These are legitimate stock losses, but if you don’t record them, your variance looks like theft or loss and you waste time investigating a problem you already know about.

Create an “Adjustments” column for non-standard losses: damaged goods, comps, staff meals, events (if you donated 10 pints to the local football club charity event, record it). This separates legitimate adjustments from unexplained variance.

Mistake 5: Not involving the bar team

You create a beautiful spreadsheet, share it with your team, and no one fills it in. Spreadsheets only work if the people doing the daily work understand why it matters and how to use it.

Spend 15 minutes with your team explaining: this spreadsheet helps us find problems before they become big issues, it protects us (staff) from accusations of theft, and it helps the business know if we have a real problem or just normal variance. If your team understands the purpose, they’ll use it. If it feels like surveillance, they won’t.

Show them what a 5% variance looks like and what a 15% variance looks like. Help them see that variance isn’t an attack on them — it’s a management tool that’s supposed to help everyone.

When to move beyond spreadsheets

A free pub stock spreadsheet is a valid starting tool, but it has hard limits. At some point — usually when you’re managing more than 4 staff across more than 50 trading hours per week, or when you have more than 15 draught lines and 50+ spirit SKUs — a spreadsheet becomes more of a bottleneck than a help.

The real cost of a spreadsheet isn’t the monthly fee (which is zero), but the staff training time and the lost sales during the first two weeks of use when the system is unreliable and staff are still learning. This is true for any system — spreadsheet or EPOS. But with a spreadsheet, you also pay the ongoing cost of manual data entry and the management time to review and investigate variances.

Consider moving beyond spreadsheets when:

• Your weekly stocktake is taking more than 60 minutes and you’re still finding unexplained variances.
• You have more than one person responsible for stock tracking and they’re recording data differently, making the numbers unreliable.
• You’re trying to manage stock across multiple locations or multiple bar areas in one pub.
• You need to integrate stock with till data to understand whether variance is loss or pricing issues.
• You’re interested in profit margin analysis and need to know the cost of sales, not just usage quantities.

At that point, you move from a free pub stock spreadsheet to either a spreadsheet pub management template with more integrated features, or to a proper EPOS system with integrated stock management. The decision usually depends on your budget and the complexity of your trading pattern.

If you’re running a food-led pub or a gastropub with significant food sales and complex menu changes, a spreadsheet won’t work at all — you need a system that integrates draught stock with kitchen inventory and till data. If you’re a wet-led only pub with simple product lines and one location, a well-maintained spreadsheet can serve you for years.

Before you invest in paid software, be honest about whether the problem is the tool (spreadsheet) or the discipline (not using it consistently). Most pubs that fail with spreadsheets would also fail with entry-level EPOS because the underlying issue is staff buy-in and daily execution, not the technology.

For more information, visit pub profit margin calculator.

For more information, visit pub drink pricing calculator.

For more information, visit pub staffing cost calculator.

For more information, visit pub IT solutions guide.