Free Bar Inventory Spreadsheet Template (Excel)

What's inside the workbook?

This template is for operators who still manage inventory manually and want one structured workbook rather than disconnected count sheets.

ProductsKeep a master list with category, bottle or case size, supplier, unit cost, par level, reorder point and storage location.

Weekly StocktakeRecord opening stock, deliveries and closing stock. The sheet calculates actual usage and can compare it with expected usage.

OrderingCompare current closing stock with your par level and calculate a suggested order quantity and estimated cost.

SummarySee closing inventory value, actual usage value, variance, products counted and items currently below par.

How to use it for a bar stocktake

The spreadsheet is deliberately simple. The important part is using the same counting method each time.

Build the product list once

Replace the sample products with the items you actually carry. Keep product names, unit sizes and costs consistent between stocktakes.

Count in full-unit equivalents

For a 700 ml spirit bottle, one sealed bottle is 1.00. If you estimate an opened bottle at half full, enter 0.50. Three full bottles plus one half bottle becomes 3.50.

Record deliveries in the same unit

If the product is tracked by bottle, enter delivered bottles. If it is tracked by case, keep the product and counts consistently case-based.

Actual usage = Opening stock + Deliveries − Closing stock

Add expected usage only when you have a reliable source

If your POS, recipes or another system gives you theoretical usage, enter it in the Expected Usage column. The workbook then calculates the difference between actual and expected usage.

Investigate variance instead of guessing

A variance does not identify its cause. Recheck counts and consider receiving errors, transfers, breakages, waste, unrecorded comps, recipe/pour differences or other process gaps before drawing conclusions.

What this spreadsheet can — and can't — do

Useful forA structured manual stocktake, basic usage calculations, simple expected-vs-actual variance, par-based ordering and a snapshot of inventory value.

Still manualSomeone must maintain the product list, enter counts and deliveries correctly, keep costs current and archive completed stocktakes. Spreadsheet formulas cannot fix inconsistent physical counts.

Tip: save a clean master copy, then create a dated copy for each stocktake. This gives you a simple historical record without overwriting previous counts.

When a spreadsheet starts becoming the bottleneck

A spreadsheet can be perfectly adequate for a small operation. The case for dedicated inventory software becomes stronger when the manual administration around the sheet starts costing more time than the sheet saves.

Repeated manual entryYou are re-entering counts, deliveries or product data across several places.

Part-bottle counting is inconsistentDifferent staff estimate open bottles differently or counts are difficult to standardise.

Several people need to countMultiple counters create version-control or consolidation problems.

You need operational follow-throughLow stock, deliveries, variance and reordering need to become an ongoing workflow rather than a weekly spreadsheet exercise.

Outgrowing the spreadsheet?

BarSync is the inventory and stock-control software we built for bars, pubs and restaurants. If manual stocktakes are becoming too much work, see how the software handles the workflow.

Explore BarSync View the iPhone/iPad app