Bar inventory spreadsheet template

A free workbook for counting the bar: a stock count, pour cost for the period, par levels and a waste log, with the formulas already in. Download it, type over the example rows and count.

Published , updated

Bar inventory template, four sheets

  • Stock count: usage, stock value and what to order for up to 200 items
  • Pour cost for the period, worked out from the count
  • Par levels and reorder points, and a waste log with reasons
Download the template

Excel file, 36 KB. Opens in Excel, Google Sheets and LibreOffice. Free, and no email address needed.

Key takeaways

  • One workbook, four sheets: stock count, pour cost, par levels and a waste log.
  • Blue cells are yours to fill in. The grey cells are formulas.
  • Count part-full bottles as decimals, and count at the same time each period.
  • A spreadsheet suits one bar and one counter. It cannot hide the expected numbers, track batches or keep a history.

What is in the template

Stock count
One row per item, with its cost, opening count, deliveries and closing count. The sheet works out what was used, what the closing stock is worth, what the used stock cost and how much to order.
Pour cost
Takes the stock values from the count. You add your drink sales for the same period and it gives the pour cost as a percentage.
Par levels
Works out a par level and a reorder point for each item from your weekly usage, your delivery cycle, the supplier's lead time and your safety stock.
Waste log
One row per loss, with a reason from a list: spillage, breakage, over-pour, expired, free drink or other. The cost of each loss and the total are worked out for you.

Blue cells are the ones you fill in. Cells on a grey background are formulas. The filled rows are examples: type over them or delete them.

How to use it

  1. List your items on the stock count sheet, each with its category, unit and cost per unit without VAT.
  2. Enter the opening count. For your first period, that is a count you take today.
  3. Add every delivery in the period to the received column.
  4. Count after close and enter the closing count. Count a part-full bottle as a decimal: 0.4 is a bottle that is 40 per cent full.
  5. Read off what was used, what the remaining stock is worth and what the used stock cost.
  6. On the pour cost sheet, enter your drink sales without VAT for the same period.
  7. Set a par level and a reorder point for each item, and the order column tells you what to buy.
  8. For the next period, save a copy, move the closing counts into the opening column and clear the received and closing columns.

Count on the same day and at the same time each period, so that sales and stock cover the same nights. The routine is described step by step in how to do a bar stock take.

The formulas behind it

Used = opening count + received − closing count

Closing stock value = closing count × cost per unit

Pour cost = (opening stock + purchases − closing stock) ÷ drink sales without VAT

Order = par level − closing count, rounded up, once the shelf is at or below the reorder point

To check a single drink instead of the whole bar, use the pour cost calculator. To work out a par for one item without the spreadsheet, use the par level calculator.

Where a spreadsheet stops working

A spreadsheet is a good way to start counting. It has limits that show once the count matters:

  • The count is not blind. The opening count and the deliveries are on the same sheet, so whoever counts can work out the number they are expected to find.
  • One person, one device. Two people counting at once means two files to merge.
  • One cost per item. It cannot keep each delivery as its own batch with its own price and expiry date, so it cannot cost by FIFO or FEFO.
  • No history unless you save a copy every period and compare them by hand.
  • It is awkward on a phone, which is what you have in your hand in the stock room.

The same count in Firetable

In Firetable the bar is counted on a phone with blind counts, so the expected quantities stay hidden. After the count you review the variance and its estimated cost for every item before anything changes. Deliveries are received as batches with their cost and expiry date, stock is valued by FIFO, FEFO or weighted average, every loss is recorded with a reason, and the reorder suggestions are grouped by supplier. Your item list can be imported as a CSV file, so the list you built in this spreadsheet carries over.

Import your stock list this afternoon.Count the bar after close.

One month free with every feature, inventory included. Cancel any time.