Free liquor inventory spreadsheet

Type the counts, deliveries and sales; the sheet does the rest. The worked example follows one house gin through a week.

Fill in the count sheet

Set the sheet up once, in the order you walk the bar: back bar shelf 1, shelf 2, speed rail, fridge, freezer, walk-in, stockroom. One row is one product in one storage spot, so a product in two places or two sizes gets two rows.

Count when the bar is closed, working down the sheet. Full bottles go in one column, the open bottle in the next, in tenths (how to count open bottles).

Take house gin in 1 liter (1 L) bottles at $30 a bottle. Shelf 2 has an open bottle at 0.5, and the stockroom has 2 full bottles. The gin count is 0.5 + 2 = 2.5 bottles, worth 2.5 × $30 = $75.

Count = full bottles + open bottle
Value = count × cost per bottle

Cost per bottle is the price on the last invoice, before tax. Update it from each new invoice.

Turn drink sales into bottles

You sell drinks, but the usage tab needs bottles. Across the top, put each product, its size as written on the count tab ("1 L"), and its bottle size in ml (1,000). Down the side, put each drink and the number sold. In each cell, enter the ml that drink pours of that product.

You sold 150 Gin & Tonics at 2 oz (60 ml) of gin: 9,000 ml. You sold 50 Negronis at 1 oz (30 ml) of gin: 1,500 ml. The bottom row gives 10,500 ml ÷ 1,000 ml = 10.5 bottles sold.

Take the numbers sold from your POS item sales report (often called product mix), from the morning after the first count to the night of the second.

A bottled house batch, such as a freezer martini, has its own column. For a batch that is not bottled, enter the spirit inside it: the house Old Fashioned's 2 oz (60 ml) of bourbon, not its 2½ oz (75 ml) of batch. Batch left in the fridge at the count is bourbon on hand. 750 ml of batch holds 750 × 60 ÷ 75 = 600 ml of bourbon, or 0.8 of a 750 ml bottle. Count it on a bourbon row, or it shows as unaccounted for.

Read the usage tab

At the last count you had 4.5 bottles of gin. You received 18, and now count 2.5. You made no gin in house, so Made in house is 0. You used 4.5 + 18 + 0 − 2.5 = 20 bottles.

Used = last count + received + made in house − this count
Unaccounted for = used − sold − into house batches − transfers out − waste and comps logged

Of those 20, you sold 10.5, put 8 into the freezer martini batch, and logged 0.5 as waste and comps. That leaves 20 − 10.5 − 8 − 0.5 = 1 bottle unaccounted for: 1 × $30 = $30, or 1 ÷ 20 = 5% of what you used.

The usage tab finds each product on the count tab by name and size, so write both the same on both tabs: "750 ml", not "750ml". If "Matching inventory rows" shows 0, they differ, and This count shows 0. Unaccounted for stays blank until you enter your sales.

The last column, Cost of stock used, counts the freezer martini batch but not the 8 bottles of gin inside it: (20 − 8) × $30 = $360.

In Unaccounted for, −0.02 bottles comes from counting in tenths and means nothing. A figure of −0.5 or lower means a count, a delivery or a sales figure is wrong: check those first. To find where a gap went, work through the liquor variance checks, from a recount to a pour test.

Before the next count. Save a copy of the file with the count date in its name. On My usage, copy This count and paste it onto Last count, the column next to it, as values (Paste Special > Values). Then clear the yellow cells of the period: Full and Open on My count, Received to Waste and comps logged on My usage, and Sold on the sales tab. Formulas are locked, so nothing else can be typed over.

From usage to your order

The sheet stops at what you have and what you used. The gin counts were a week apart, so you used 20 bottles in the week. If your counts are further apart, divide by the weeks between them.

Average week = used ÷ weeks between counts

Take that and your count to the free par sheet. It sets a par for each product from usage and delivery days, and gives the order in whole bottles.