What a bar inventory sheet needs
A useful bar inventory template does more than list bottles. For each item it should have:
- Item name, category, bottle or pack size, and the price you pay
- A count column for each storage area (back bar, well, storeroom), listed in shelf order so the count goes in one pass
- Opening count, amount received, and closing count
- Usage and its dollar value
- Expected usage from what you sold, and the variance between the two
The formulas
Usage = opening count + received − closing countExpected usage = units sold × mL per drink, added up across every drink that uses the itemVariance = usage − expected usage, andvariance % = variance ÷ expected usage × 100Cost of goods = opening value + purchases − closing valuePour cost % = cost of goods ÷ net sales × 100(see the pour cost formula)
Last week's closing count becomes this week's opening count.
Example (made-up figures)
Worked example: one week of vodka
| Line | Value |
|---|---|
| Opening count | 6.0 bottles |
| Received | 12 bottles |
| Closing count | 7.4 bottles |
| Usage: 6.0 + 12 − 7.4 | 10.6 bottles = 7,950 mL |
| Expected: 170 drinks × 44 mL | 7,480 mL |
| Variance: 7,950 − 7,480 | 470 mL (6.3%) |
| Value at $30 / 750 mL ($0.04 per mL) | $18.80 |
470 mL is about 16 over-poured or unrung 29 mL shots in a week. Whether that's worth chasing depends on your tolerance. A good rule is to flag an item only when it's over both a percentage and a dollar threshold, so slow-moving bottles don't bury real problems.
Example (made-up figures)
Counting an open bottle by weight
Counting in tenths by eye is quick but rough. Weighing is more consistent:
Density (g/mL) = (full weight − empty weight) ÷ bottle size (mL)mL left = (current weight − empty weight) ÷ density
A 750 mL bottle weighs 1,250 g full and 540 g empty, so density = 710 ÷ 750 = 0.947 g/mL. The open bottle weighs 900 g: (900 − 540) ÷ 0.947 = 380 mL, or about 0.51 of a bottle. Weigh one full and one empty bottle of each product once and reuse the numbers.
Common mistakes
- Counting during service. Count before opening or after close, at the same point each week, or usage won't line up with sales.
- Missing deliveries or transfers. A case that arrived but wasn't entered looks like a big gain; a transfer to the kitchen looks like loss.
- Counting in a different order each time. Bottles get skipped or counted twice. Match the sheet to the shelf.
- Using sales that include tax. Pour cost should use net sales before tax.
- Not ringing in comps and spills. If they aren't in the POS, they show up as unexplained variance.
- Counting too rarely. A monthly count tells you there was a problem; a weekly count tells you when.
Once usage is reliable, use it to set reorder levels with the par level guide.
A ready-made bar inventory spreadsheet
The Bar Inventory & Par Level Tracker takes counts by location in tenths or by weight, compares usage with your POS sales, flags variance in mL, bottles, dollars and %, shows weekly and monthly pour cost, and builds an order rounded to full cases. Excel, also opens in Google Sheets and LibreOffice. US$12.
Get the Bar Inventory & Par Level TrackerFAQ
How often should a bar do inventory?
Many bars count weekly, because it lets you tie losses to a specific week and staff schedule. A monthly count is the minimum for a cost-of-goods figure. Whatever you choose, count at the same time and in the same order each time.
How do I count partial bottles?
Either estimate in tenths of a bottle by eye, or weigh the bottle and subtract the empty weight. Weighing is more consistent between counters.
What is bar inventory variance?
It's the difference between what left the shelf and what your sales say should have left it. A loss means more product was used than sold; a gain usually points to a counting or data-entry error.
Can I do bar inventory in Excel or Google Sheets?
Yes. A spreadsheet with opening, received and closing columns, a price per item and the formulas on this page covers the basics. Dedicated software adds things like scanning and supplier integrations.
What's the difference between usage and cost of goods?
Usage is measured in bottles or mL for each item. Cost of goods is the dollar value of everything used in the period, which you divide by sales to get pour cost.
About this page
Written by Jigger & Ledger, built by a Toronto hospitality operator. Example figures are made up for illustration and are not real supplier prices. This is general guidance for planning, not financial, tax or legal advice.