Pub stocktake Excel - Free Template

1. Step 1 — Open the Price List and review the sample products. Replace the sample costs and selling prices with the figures on your latest supplier invoices and till price list.
2. Step 2 — Add each product you actually hold, keeping one row per item and a clear item code. Use the stated unit, such as Keg, Case, Bottle or Box, consistently.
3. Step 3 — Before counting, close or pause bar service and print or view the Stocktake sheet in a fixed counting order: cellar, stores, back bar, fridges and lounge stock.
4. Step 4 — Count unopened cases first, then loose bottles, kegs and other units. Enter the physical quantities carefully and investigate anything that looks unusually high or low.
5. Step 5 — Check the Dashboard after entering the count. Compare the stock value and product groups with the previous count or with your expected purchasing pattern.
6. Step 6 — Save a dated copy after each count, for example Pub Stocktake 31-07-2026.xlsx, and send the completed file to your bookkeeper with relevant delivery dockets and invoices.

A pub stocktake is most useful when it is performed at the same point in each accounting period. A VFI publican might count after closing on the last Sunday of the month, while a bookkeeper in a small Ltd company may need the final figures for the monthly management accounts. The timing matters because a delivery received on Monday can otherwise be included in purchases but left out of closing stock.

Cellar, bar and stores in one count

Count in a fixed route rather than walking around looking for products. Start with kegs in the cellar, move to bottled beer and soft drinks in fridges, then count spirits, wine, mixers and snacks behind the bar and in the storeroom. Image 2 shows the Stocktake sheet intended for entering this physical count.

The Price List in image 1 gives you a practical reference for the unit used by each product. A 50L keg is one Keg, Bulmers is held as a Case of 12, Coca-Cola is a Case of 24, and Tayto is a Box of 48 bags. That distinction prevents a count of 6 cases being mistaken for 6 individual units.

A worked month-end example

Suppose a rural pub has 8 Guinness kegs at €145.00 each, 10 Bulmers cases at €21.60, and 6 Jameson bottles at €22.50. The listed cost value is €1,160.00 + €216.00 + €135.00, giving €1,511.00 before adding the remaining drinks and snacks.

That figure gives the owner a useful sense check against the previous month. If stock has risen by €3,000 while takings were quiet, the first checks should be recent deliveries, an unposted invoice, a change in counting units or stock stored in a second shed.

That same month-end reconciliation becomes the starting point for a capital gains calculation when a disposal or write-off needs to be measured against the book value.

For Revenue, the stock figure is part of your accounting records rather than just a bar-management number. Keep stocktake sheets, supplier invoices, delivery dockets and purchase records for 6 years. A dated Excel copy is useful evidence of how the closing figure was built, but it should sit alongside the supporting documents.

VAT and selling prices

Most alcoholic drinks sold by a pub are subject to the Irish VAT standard rate of 23%. If a bottle is priced at €6.00 including VAT, the VAT element is €1.06 and the net sale is €4.94, using €6.00 × 23 ÷ 123. The workbook contains sales prices, but it should not be treated as a VAT return calculator unless you add and test a separate VAT analysis.

VAT-registered businesses generally file a VAT3 every two months through ROS, with an annual Return of Trading Details. The stocktake supports the figures in your accounts and helps explain purchases and gross margin; it does not replace the VAT3 records or till reports.

Cost evidence and year end

Use the latest valid purchase cost and keep the supplier invoice that supports it. For example, 20 cases bought at €21.60 give a cost of €432.00 before VAT treatment. Do not use the €42.00 sales price as stock cost: that would overstate closing stock and profit.

At year end, your accountant or bookkeeper will use the count when preparing the profit and loss and balance sheet. My practical preference is to value stock consistently at cost, subject to any required write-down, rather than changing between cost and selling price from one month to the next.

The expensive errors I see are usually counting errors, not difficult Excel errors. A publican counts 24 individual cans as 24 cases, or records a half-used keg as a full keg, and the closing stock becomes wrong by hundreds of euro. The problem then appears as a strange gross margin rather than an obvious stock mistake.

Case sizes create false stock

Take the Coca-Cola example in the Price List: a case contains 24 cans and costs €14.40. Recording 5 cases as 120 cases inflates stock cost from €72.00 to €1,728.00, a difference of €1,656.00. That can make a quiet month look unusually profitable and distort the next purchasing decision.

Transfers and deliveries are missed

Stock moved from the main bar to an event trailer or function room is often counted twice, while a delivery left in a locked storeroom is missed completely. A pub with €4,000 of weekly purchases can easily carry a timing difference of €1,000 or more if the invoice, delivery and count fall in different weeks.

Another common failure is changing the unit cost without retaining the old workbook. If Guinness rises from €145.00 to €152.00 per keg and the pub holds 30 kegs, the cost difference is €210.00. That is a legitimate price movement, but it should not be confused with shrinkage or unexplained usage.

Using sales prices as stock value

Sales price is useful for pricing decisions, but it is not the same as purchase cost. Valuing 10 Jameson bottles at €55.00 instead of €22.50 adds €325.00 to the apparent stock value. Keep the two columns separate, and investigate unusual differences between stock movement, till sales and purchasing rather than silently overwriting the count.

The spreadsheet only works if the count happens on a predictable night. Put it directly beside an existing task such as the month-end till close or the last supplier reconciliation. For a busy pub, counting on the final Sunday after closing is usually better than trying to remember it during Friday service.

A repeatable counting routine

• Give one person responsibility for the cellar and another for the bar, then swap roles occasionally so unusual results are challenged.
• Use the same route and count order every time: kegs, fridges, back bar, stores and function areas.
• Save a new dated workbook rather than overwriting the prior count. Keep the completed file with the invoices for that period.
• Review the Dashboard immediately and write a short note beside any large movement, such as a festival weekend or a new drinks promotion.

Do not alter the item code each month. If a supplier changes a case size, add a new product row or clearly update the Case Size and record the change in your notes. That preserves a usable comparison between a case of 12 and a case of 24.

When Excel has reached its limit

This template suits one pub with a manageable product list and a monthly count. If you are tracking 300 products across several bars, recording every wastage event, or reconciling stock to a till system daily, use dedicated hospitality stock software and retain this workbook as an export or checking tool.

My preferred approach is to keep Excel for the count and management review until the team cannot complete it accurately in under an hour. At that point, paying for integrated purchasing, recipes, wastage and sales reporting is more sensible than adding complicated formulas to a file nobody trusts.