To download the Perpetual Beverage Inventory Template in Excel Format, click here.
Instructions for using the TrueGuest Perpetual Beverage Inventory
To set up the file:
- 1. Create a new file for each month. You can either create a new file for each month or add each month to a separate tab within this worksheet.
- 2. Input your ‘Opening Liquor Room’ and ‘Opening Bar Count’ inventories in columns B and C. If you used this file last month, it is easy to copy and paste your totals in from the prior month’s spreadsheet.
- a. If you have more than 1 liquor room or more than 1 bar inventory, you can add new columns for the other inventories. Be sure to adjust the formulas in the ‘liquor room total’ and ‘bar total’ columns if you add other inventories.
- 3. Adjust your ‘Price Bottle’ in column F. It is easier if you adjust these prices every time you receive an invoice rather than waiting until the end of the month.
- 4. The total inventory values will calculate automatically. The total is at the very bottom.
To add your purchases:
- 1. Every time you receive a delivery, just add the number of bottles you purchased into the ‘wk1′ or ‘wk2′ column.
- 2. The total purchases for the month will calculate at the right.
To remove the items you requisition from inventory:
- 1. As you requisition items from the inventory, put the number that you requisitioned in the column.
- 2. If you requisition items to banquets, put that number in the banquet column.
- 3. The total requisitions will total up at the right
When you take the physical inventory at the end of the month:
- 1. Input the amount in the ‘ending liquor room count’ column.
- 2. Your variance between the actual inventory and the perpetual inventory will calculate to the right.
To see what your beverage cost is during the month:
- 1. Click on the ‘costs’ tab.
- 2. Input your month to date revenues.
- 3. Your total costs to date will display below.
To use pars for easy ordering:
- 1. Input your par in the ‘par’ column. Your par is how much inventory you should have on hand for each item. We recommend the bar to be a two week supply if you order once per week.
- 2. Input the number of bottles you receive in a case in the ‘case’ column.
- 3. Anytime you are under par, the number that you should order will appear in yellow in the ‘order’ column. That is the amount that you should order to get back up to par.