Backbar Academy | Free Guides & Resources for Bar & Restaurant Professionals

How to Create a Bar Inventory Spreadsheet

Written by Kyle Thacker | Mar 11, 2019 2:05:29 PM

Taking bar inventory at your bar or restaurant can be a pain. But the best way to minimize the pain is to create a liquor inventory spreadsheet that is organized and allows you to keep important information all in one document. The mantra for creating an inventory template is Organization = Efficiency. So let's explore how to stay organized and on top of your bar inventory.


Step 1: Setting Up Your Bar Inventory List

 

Organization is essential for an building efficient inventory system that strengthens your business. Value for a business is measured in dollars. So inventory should be more than tallying up bottle counts. A valuable inventory strategy should give you detailed information that you can make decisions with to do things like lowering your liquor cost, sticking to a purchasing budget, and keeping your counts in check.

 

Another big benefit of inventory is making sure you have the products on hand that you need so service runs smoothly. 

 

Staying organized will also save you significant time and reduce the frustrations of performing inventory.

 

Inventory Locations

 

Whether you use a spreadsheet or an inventory solution like Backbar, you should set up separate locations or areas for your items. Locations are the physical places that inventory is stored in your restaurant or bar.

 

For example:

 

Front Bar | Back Bar | Beer Cooler | Liquor Storage | Wine Room

 

The Backbar bar inventory software allows users to create multiple locations and sub-locations so different bar operators can track inventory performance by location, in addition to seeing the overall performance of inventory products. 

 

Doing this helps operators make better decisions about which products to keep in stock and where.

 

 

This will help you maintain accurate counts and keep things streamlined when you're taking inventory. It also allows you to divide up the work of counting inventory at the end of the month more easily by having different staff counting in different areas at the same time. You can total the counts for each item in each location when you've completed counts for all locations.

 

 

Making inventory as stress-free as possible is what Backbar is here for! That's why Backbar now integrates with select POS systems to make tracking inventory easier than ever. 

 

Putting Your Inventory List in Order

 

Once you've established your inventory locations, it's time to add and organize the products listed in each section.

 

The best way to do so is not necessarily alphabetically, but by matching the order of your inventory list to the order in which they are physically organized behind the bar and in storage.

 

This allows you to quickly enter counts. 

 

Recording Costs Helps Track Performance

 

In order to calculate important information like Pour Costs, Variance, and Margins, you need to know the cost of each product you've purchased.

 

To record your costs, you will have to copy pricing information from your invoices into a spreadsheet. If you're using Backbar, you can upload your invoices to our system to have your costs recorded for you automatically.

 

The two most important data points are the cost per bottle and cost per ounce. So if you order cases, divide the case price (including discounts and taxes if possible) by the number of bottles in the case to find the bottle cost. It's best to keep as much information centralized as possible.

 

If you use a spreadsheet for inventory, you should add a separate column for item cost.

 

 

Establish Pour Sizes and Pricing

 

Your pour sizes and pricing for products like wine and spirits will be influenced by your costs.

A general guideline for pricing is to sell items at a 20% liquor cost. That is, if an item costs you $1, you would sell it for $5. So, 1/5 =.2 or 20%.

 

 

Standard wine pours are typically 5 oz. or 6 oz. per glass

 

Standard pours for spirits are 1 - 2 oz. for neat pours, or 1.5 to 2 oz. for mixed drinks.

 

20% isn't always possible with premium items like a rare whiskey or an expensive bottle of wine because you may price out your customers by charging too much to hit a 20% pour cost. It's important to know your client base when setting pricing. In general, the lower the cost of an item, the higher the pour cost should be; and the more expensive the cost of an item, the lower the pour cost should be.

 

Your cheapest wines by the glass may have a pour cost of 25%, while your most expensive wines by the bottle may have a pour cost of 60%. Despite a much higher pour cost, you'd prefer to sell a $200 bottle of wine with a 60% pour cost ($80 of gross profit) compared to a $50 bottle of wine with a 30% pour cost ($35 of gross profit).

 

Establishing the liquor cost percentage is easy once you know the cost per bottle. For spirits, wine, beer, and other liquors sold by the ounce (as opposed to selling by the bottle), you will need to calculate the cost per ounce. Record your sale price for each item in a column next to your cost column.

 

To calculate cost per ounce, divide the bottle cost by the number of ounces.

 

Identifying the cost per ounce will also help you decide on your pour sizes by determining the margin you want per bottle for items sold by the ounce.

For wine, where a standard bottle is 750ml or 25.3 ounces, industry standard is 5 oz. pours (yields 5 glasses per bottle) or 6 oz. (4 glasses per bottle).

 

Plan Ahead with Your Spreadsheet

 

One of the points we've hammered on in this inventory guide is that inventory is about much more than just counting bottles. The information you capture from a mature inventory system can be the difference between failure and success for your business. And although we've thrown at you a lot of complex formulas and calculations, running an efficient and profitable inventory system is not as difficult as it seems, it just requires some upfront planning.

 

When putting together your liquor inventory spreadsheet you will want to decide what type of information you want to have available for analyzing your bar program.

 

For example, if you want to know how much money you have tied up in wines vs. beer, then you will need to add a spreadsheet column to notate the type of beverage it is. Uncorkd and other inventory programs are so useful because that information is already associated with the items you enter into the system.

 

Here are some categories you may want to capture information on for inventory analysis

 

Product Types

 

Wine, Beer, Spirits, etc.

 

Subtypes

 

Red wine, White wine, IPAs, Lagers, Tequila, Vodka, Whiskey, etc.

 

Vendors

 

The distributors you purchase alcohol and beverages from.

 

Size

 

Determines pour costs, pour sizes, and price point

 

Varietal or Style

 

Chardonnay, Merlot, Single Malt Scotch, Blended Scotch, Blanco Tequila, Anejo Tequila

 

Why this information is useful

Capturing data by additional categories like those listed above will help you determine which areas of your business are most profitable. It will also help ensure you don't have too much money tied up in inventory of any specific type of product. For example, if Cabernet Sauvignon accounts for 15% of your wine sales, it would be useful to know if your on-hand inventory value of Cabernet deviated drastically from 15%.

 

We noted above that different types of alcohol have different average pour costs. By capturing more data to analyze your beverage program you can approach your inventory data from different perspectives to determine how you can become most successful.