A useful pub stocktake records physical quantities at a defined time and compares them with an expected closing balance in the same units. A difference is an investigation point, not proof of theft or a cash saving.
The bar has been counted. The spreadsheet shows ten fewer bottles than expected. What happened?
It could be a missed transfer, a case recorded as a bottle, an incorrect opening balance, a sales-mapping error or stock that was not counted. A useful stocktake does more than produce a total: it gives you a controlled way to find the explanation. The workbook in this guide separates the physical count from the expected balance and keeps the arithmetic visible.
What the free workbook includes
The workbook contains an instructions sheet, a count sheet, a stock-movement reconciliation and a small summary. Fictional examples show how whole cases, loose units and partial bottles feed into the closing count. Additional rows are ready for your products.
The Count sheet records where the product is held and how it is counted. The Reconciliation sheet uses opening stock, receipts, transfers, recorded consumption and wastage to calculate what should remain. You enter approved unit costs to obtain a management value for the difference. This is a working stock-control tool, not an accounting valuation policy or an automatic fraud detector.
Example rows, blank input rows, formulas, a summary and instructions. No registration required.
Download the free pub stocktake workbookSet a count time and a physical route
Choose a repeatable cut-off, ideally when movement is limited. Label the count with the venue, area, date, time and person responsible. Map the route: main bar, back bar, cellar, fridge, store room and any event stock. Assign each area once so that the same case is not counted twice.
Where practical, pause transfers and deliveries while an area is being counted. Where that is not possible, record movements after the cut-off separately and reconcile them. A count taken at 08:00 cannot be fairly compared with a stock balance that includes a delivery at 10:00 unless the timing difference is adjusted.
Record an actual zero for an item checked and found absent. Leave an item uncounted when it has not been checked. Those are different states, and the workbook leaves the calculated count blank when required count inputs are missing.
Use one base unit for each product
For a packaged mixer, the base unit might be one bottle. For spirits and wine, you can count bottle equivalents or volume, provided the same unit is used throughout. For draught stock, litres can be more practical than treating every partly used keg as a full keg.
Write the pack size next to the product. A case and its individual contents are different packaging levels; GS1 Ireland specifically distinguishes those identifiers. A scanner or spreadsheet still needs to know which level you are recording. [1]
For the workbook, the physical quantity is:
Two cases of 24 bottles, six loose bottles and no partial units give 54 bottles. For a spirit, two full bottles and another holding an estimated 350 ml of a 700 ml bottle give 2.5 bottle equivalents. The partial value is an estimate unless you use an appropriate measurement method; label the method and use it consistently.
Do not assume half the visible height of an irregular bottle means half its volume. Do not mix litres with bottle equivalents in the same line. Keep damaged, quarantined or unsaleable stock identifiable instead of hiding it inside the usable balance.
Calculate the expected closing balance
Build the expected quantity from the previous verified count and the movements since then. The formula used in the workbook is:
“Recorded sales usage” means stock consumed according to sales and recipe mappings. “Other recorded usage” may include approved complimentary drinks or staff consumption where applicable. Make sure these categories do not duplicate each other. If a staff drink is already included in the sales-usage figure, do not subtract it again.
Use quantities actually received, not merely quantities ordered or invoiced. Keep supplier credits separate from physical movement: a financial credit does not necessarily mean goods left the cellar. For transfers, record both the dispatch and receipt so the two venues or areas can reconcile the movement.
A worked example: ten bottles to investigate
The fictional product is a bottled mixer counted in individual bottles. All movements below belong to the same period and cut-off.
| Movement | Bottles |
|---|---|
| Opening verified stock | 60 |
| Delivered and accepted | +48 |
| Transfers in | +12 |
| Transfers out | −6 |
| Recorded sales usage | −66 |
| Other recorded usage | −2 |
| Recorded wastage | −2 |
| Supplier returns | 0 |
| Expected closing stock | 44 |
| Physical count | 34 |
| Physical minus expected | −10 |
At an illustrative cost of €0.80 per bottle, the difference has a management cost value of −€8. This is not a retail-sales loss calculation and it does not prove theft. It identifies a quantity gap that needs an explanation. The workbook preserves that distinction by labelling the result a variance, not a recovery or saving.
Investigate in a useful order
First, recount the physical area and check overlooked shelves, overflow storage and event stock. Confirm the product and pack size. Then check the cut-off: were deliveries, sales and transfers recorded in the correct period?
Next, review the records behind the expected balance. A transfer may be missing, a wastage entry may be duplicated or a recipe mapping may use the wrong portion size. When a till item maps to two ingredients instead of one, the arithmetic can be correct while the expected stock is wrong.
Only after those checks should you examine a persistent operational loss. Keep the review factual and involve the responsible manager. A short explanation such as “six bottles transferred to function bar after cut-off; receiving entry corrected” is more useful than overwriting the count until the difference disappears.
Close the count without erasing the evidence
Retain the original count, any recount, the reason for an adjustment and who approved it. Agree which value becomes the next opening balance. The count and the adjustment are related records, not the same event.
Review repeated differences by product and process, not just the largest euro total. An item with a small but repeated unit mismatch may reveal a pack conversion problem affecting many orders. A single large positive variance can also matter: it may indicate a missing receipt or understated opening stock rather than a favourable result.
Stock records also support a wider receiving process, but a simple count sheet does not cover food traceability on its own. FSAI explains the supplier, product and transaction details required for traceability, including extra requirements for some foods. Keep those records alongside—not inside an improvised replacement for—your food-safety system. [2]
Choose a counting rhythm your team can maintain
There is no universal count frequency that suits every pub. A sensible starting approach is to review high-value or fast-moving lines more often and schedule a consistent full count. Match the frequency to stock risk, turnover and your accountant’s period-end requirements.
Measure the whole job: preparation, counting, recounts, investigating and approval. A faster count is not an improvement if it leaves more uncertainty. Compare a few cycles before deciding whether a new process or tool has helped.
For a software demonstration, bring a partial bottle, a case conversion, a stock transfer and a late delivery. Ask how each is handled and how the original count survives an adjustment. Those examples tell you more than a dashboard with perfect sample data.
Questions about pub stocktakes
Does this workbook calculate profit?
No. It compares physical and expected stock and applies the unit cost you enter. Profit and formal stock valuation depend on wider accounting records and policies. Agree those with your accountant.
Can I count an open bottle as a fraction?
Yes, when the base unit is a bottle and your method is consistent. Record a 350 ml remainder from a 700 ml bottle as 0.5 bottle equivalents. Label visual estimates and avoid treating them as exact measurements.
What does a negative variance mean?
It means the recorded physical quantity is below the calculated expected quantity. Recount and check units, timing and movement records before deciding why.
Can this work for several venues?
Use one controlled count per venue and cut-off, with transfers recorded on both sides. Do not merge locations before checking that stock moved between them has not been omitted or counted twice.
Sources and editorial notes
Primary guidance checked on 26 October 2026. Operational routines and worked examples are Inntally’s editorial recommendations; adapt them to your venue.
All numerical examples are fictional and explain the method; they are not customer results or savings claims. Images are illustrations, not photographs of identified customers or evidence of product outcomes.
Read next
- How to compare restaurant supplier prices — and find the real cost
- Three-way invoice matching for restaurants: ordered, received, invoiced
- How to reduce restaurant no-shows without losing the welcome
- Restaurant bookkeeping in Ireland: a weekly routine that keeps the records together
- Stock counting in 2026 — the phone-first count the template feeds.
- Inntally Stock Count — the product page.