When to use this
Run this workbook every time you take a full bar inventory — most operators do this monthly, higher-volume bars may run it weekly. Use one month tab at a time so usage and pour cost line up with your accounting period.
What the workbook tracks
- Beginning, purchases, and ending liquor inventory by category and brand.
- Full and partial bottle counts converted to “bottle equivalents.”
- Usage cost for the period, ready to drop into your pour cost and prime cost calculations.
Inputs — what goes where
- Category (A) — Vodka, Whiskey, Tequila, Rum, Gin, Cordials, Wine, Beer, etc.
- Brand / Item (B) — the label guests and staff know: “Tito’s 1L”, “Jameson 750ml”, etc.
- Bottle Size (C) — 750ml, 1L, 1.75L, keg size, etc.
- Begin Qty (D) — full-bottle equivalents at the start of the period (including partials as decimals).
- Purchases Qty (E) — how many full-bottle equivalents you received during the period.
- End Qty (F) — full-bottle equivalents on hand at the end of the period (again, partials as decimals).
- Bottle Cost (G) — your current landed cost per full bottle equivalent.
Partials: If you’re using tenths or quarters behind the bar, enter them as decimals (e.g. 0.25, 0.5, 0.75). The sheet does the rest.
Formulas — exactly what the sheet does
- Beginning Value = Begin Qty × Bottle Cost
- Purchases Value = Purchases Qty × Bottle Cost
- Ending Value = End Qty × Bottle Cost
- Usage Qty = Begin Qty + Purchases Qty − End Qty
- Usage Cost = Usage Qty × Bottle Cost (your liquor cost for that item for the period)
At the bottom-right of the tab, all items roll into period totals:
- Total Beginning Liquor Inventory
- Total Purchases
- Total Ending Liquor Inventory
- Liquor COGS (Period) = Beginning + Purchases − Ending
Tip: Use Liquor COGS ÷ Liquor Sales to get your pour cost %. Plug that into your prime cost calculator to see how the bar is actually performing.
Walk-through example
Say you’re tracking a 1L premium tequila:
- Begin Qty: 8.5 bottles (8 full + one half)
- Purchases Qty: 4 bottles
- End Qty: 5.25 bottles (5 full + a quarter)
- Bottle Cost: $32.00
- Usage Qty = 8.5 + 4 − 5.25 = 7.25 bottles
- Usage Cost = 7.25 × $32.00 = $232.00 in tequila cost for the period
Repeat this for every tracked product. The summary box at the bottom-right gives you total liquor COGS for the period, ready for pour cost and prime cost.
Common pitfalls
- Counting partials as “one bottle” instead of converting to a decimal.
- Using different partial systems (tenths vs quarters) on different shifts.
- Not updating bottle cost when vendors bump pricing.
- Including comps and spilled drinks in sales but not accounting for them in usage.