How to Create a Liquor Inventory & Costing Spreadsheet

by Steve Brachmann; Updated September 26, 2017

Laid-back works well for a bar's atmosphere, but not for its liquor inventory accounting. A strict business sense and best practices in inventory accounting are necessary to protect the establishment's bottom line. Creating a comprehensive inventory and costing spreadsheet for your bar will help you keep track of how much money you should be making from beverage sales. Leaving space in the spreadsheet to account for complimentary drinks or wasted liquor can help you identify where your business can earn more money.

Items you will need

  • Clipboard with paper
  • Pen
  • Liquor distributor catalog
  • Computer with spreadsheet software
Step 1

Look through your current liquor inventory and write down what you currently own. If you have a large liquor inventory, you may want to break this list down among red wines, white wines, bourbon, gin, vodka or other. If you don't have a full bottle of the liquor, try to approximate what amount of the bottle you have left as best as you can.

Step 2

Look at your menu and find out how much you make per serving of liquor at your restaurant or bar establishment. Write down the drink prices and amount of liquor served per drink. For instance, if you sell a 6-oz. glass of a specific Chianti for $4, write down "$4" and "6 oz." onto the paper. For mixed drinks, like vodka and juice or whisky sours, break them down into their different parts and price each so that drink prices are uniform across the board. Figuring out the price per shot or drink of a liquor will tell you what you should ideally make on each bottle of liquor sold.

Step 3

Obtain a pricing catalog from your liquor distributor. Make sure it contains current prices for alcohol that your establishment purchases for resale. The price you pay per bottle is important to consider when finding out what your profit per bottle should be.

Step 4

Open your spreadsheet software. In one column, include every specific bottle of liquor being stocked at your store. In each row for a specific liquor, include the cost per bottle of liquor, the volume of the bottle bought, the serving size of liquor used in a drink and the cost per serving of liquor. Leave blank entries in each row for both servings of liquor sold and complimentary drinks, if applicable.

If you are familiar with spreadsheet formulas, you may insert a multiplication or sum formula to make calculations occur automatically. A bartender will need a hard copy of the spreadsheet to mark down drink orders, which may not be necessary.

Step 5

Print a copy of your liquor inventory and costing spreadsheet. Have your bartender or an assistant fill it out for all drink orders over the course of a manageable period of time for drink sales. Make sure to note any complimentary drinks as well as sales. Keep track of any new bottles that are purchased during the time period being analyzed on the spreadsheet.

When the time period ends, count your inventory to see the physical volume of liquor that you have left. Use this to figure out exactly how much of each type of liquor you sold in that time period. Divide the total volume of liquor sold by the serving size and multiply by the number of orders tallied to find out how much you should have made from your liquor inventory sold. Add your sales receipts and register reports to find out how much money you actually earned from sales.

Tips

  • Obtain a copy of the Restaurant Industry Operations Report (see Resources). This annual industry standard handbook contains best practices in restaurant accounting procedures from the National Restaurant Association. This will help you better compare your bottom line against competing restaurants. Online providers of liquor inventory and costing spreadsheet software are widely available. These programs have a wide array of options and are easier to utilize than a basic spreadsheet. Try to use a shareware version to test the software before spending money on the full version.

Photo Credits

  • Jochen Sand/Photodisc/Getty Images