How to set up your inventory control from scratch

If you’re a first time owner or manager, you might be unfamiliar with the basics of inventory management.

If that’s the case, you’re in the right place.

Below are the key steps to setting up your inventory control process.

1. Start with a simple spreadsheet

If you’re starting from scratch, we recommend opening up a new spreadsheet on your computer or downloading this template:

Email *

The idea here is to perform the process manually so you understand the ins and outs and clear up any issues before investing in a bar inventory system.

Once you have your spreadsheet, make sure you include the following columns on top:

Item description

Volume

Bottle cost

Sale price

Starting quantity

Received quantity

Ending quantity

Sold quantity

So you should end up with something like this:

As for the rows, you will list out each of your products as part of the inventory, including liquor type, brand and flavor. This is going to take some time to populate at first but you can re-use the spreadsheet as a template for future inventories.

2. Select a time frame

Once you have your spreadsheet prepared, make sure you note down the date on top and decide on a regular time frame to conduct inventories going forward.

This could be weekly, monthly or quarterly, depending on your resources. In general, the more often you do it, the more accurate results you will get. For our example, we’re going to use weekly inventories.

3. Take note of your starting inventory

Now it’s time to go through your stock and fill out your spreadsheet. You can do this on paper or digitally, whatever is more convenient for you.

Make sure you go through all possible places where you keep liquor and other materials like cocktail ingredients. It’s easy to miss something if you don’t have designated places and this could mess up your whole inventory.

For this example, we’re going to use a fictional product called “Dynamite Gin”. We’re going to add all of the available details in our spreadsheet so it looks like this:

Tip: Use the rule of 10ths to estimate quantities in open bottles. Imagine every bottle is split into 10 equal parts. Then estimate the amount remaining and note it down on your spreadsheet. For example, if the bottle is half full, you can record 0.5 in your spreadsheet. If you have about a third left, you can include 0.3 and so on.

4. Go back to your (almost normal) operations

The first step is now complete and it’s time to wait until your next inventory date, usually a week, month or quarter.

You can carry on with your normal operations but keep in mind the following if you want to make the best use of your inventory process:

Note down the quantity of product received in the same spreadsheet or separately. This will help you calculate your total usage over the period (more on that later).

Create a log with all spillages and recipe mistakes so you better understand sources of shrinkage.

Where possible, keep track of the quantity sold per product (this will be easier for some products but impractical for others, depending on your menu).

5. Take note of your ending inventory, deliveries and quantities sold

On the next inventory date, you can open the spreadsheet you created and add the ending inventory.

Basically, you will need to count the stock again using the exact same methodology so you can reduce inaccuracies.

In our example, let’s say we have 6 bottles of Dynamite Gin left. We also received one new case over the week containing 12 bottles. In addition, we sold 7 bottles according to our sales records. So the completed spreadsheet will look like this:

Once you repeat this process for all available stock, your first inventory period will be complete.

But how do we make this raw data useful? Well, now it’s time to calculate some key metrics to help you make better decisions.

Tip: If you don’t know the exact sale price or quantity sold for a product, you can leave them blank. These are useful for calculating variance and pour cost on an individual level but not essential. You can still find your pour cost as a whole using cumulative sales from your POS.

Calculating key metrics based on your inventory

Now that your first inventory is complete, you have the basis to calculate some fundamental bar metrics. Specifically, we’re going to look at:

Usage

Par levels

Variance

Pour cost

Let’s dive in.

Inventory Usage

Usage is the quantity of product you went through during the given period of time. In contrast to quantity sold, usage takes into account everything used, including lost product due to spillage, non-compliance or even theft.

To calculate usage, you just need to subtract your ending inventory from your starting inventory plus any quantities received. The formula is as follows:

Usage = Starting inventory + Received inventory – Ending inventory

In our example from above, we can calculate inventory usage as follows:

2.5 bottles + 12 bottles – 6 bottles = 8.5 bottles

Given the cost per bottle of $40, we can also calculate usage in terms of its dollar amount. In this situation, this would be 8.5 bottles X $40 = $340.

One benefit of understanding usage is you can estimate the minimum amount of product you need in stock without running out of it.

Par levels

Par levels represent the minimum quantity you should stock from a given product such that you don’t run out of it on busy nights.

This way, you can keep inventory to a minimum so your cash isn’t locked up. Not to mention you can use that valuable space on your shelfs and reduce the chance for spillage and waste in your bar.

In addition, low inventory levels will make your counting easier and faster due to having less products altogether.

So how do you calculate your par levels?

Once you take inventory for a few weeks, you’ll know your par levels over a given period of time. For example, let’s say we used 8.5 bottles in Week 1; 14 bottles in Week 2; and 13 bottles in Week 3. In this situation, our 3-week par level for the product would be:

Par level = (8.5 +14 + 13)/3 = 35.5/3= 11.8 bottles

So we’ll need to stock around 12 bottles per week in order to meet demand for the product and minimize idle inventory.

Product Variance

In a perfect world, you would be selling exactly as much as you are using. But sometimes bottles break, cocktails are not done properly and someone might just be running off with a case of beer every other night.

Variance, also known as shrinkage, is the difference between your Cost of Goods Sold (COGS) and your inventory usage (in $). A simple formula to calculate variance is this:

Variance = COGS – Usage ($)

We already know our usage is $340 but how do you calculate your COGS? All you need is the amount of product you’ve actually sold over the given period of time and the unit cost.

In our example, we sold 7 bottles of Dynamite Gin over the week. Each bottle costs $40 so our COGS = 7 x $40 = $280.

Therefore, we can calculate product variance as:

Variance = $280 – $340 = -$60

In order to compare your variance over time or between products, it’s good to convert it into a percentage. You can do this by dividing Variance ($) by Usage ($). In our example, this would be:

Variance (%) = -$60/$340 = -17.6%

Now we can see we’re losing close to 18% of our product due to spillages, mistakes and theft. This is not a bad percentage – you might see much higher variance rates, especially if you have never done proper inventory before.

In order to consistently improve this metric, we can implement more strict Standard Operating Procedures (SOPs) or investigate why a given product has a higher variance compared to the rest.

Pour cost

Lastly, we have one of the key measures of profitability in bars which is the pour cost.

The pour cost indicates how much your product costs are relative to your sales. So you’re taking into account the full usage including what you’ve lost to spillage, theft and non-compliance. An easy formula to calculate pour cost is as follows:

Pour cost = Usage ($) / Sales ($)

From our example above, we know the usage is $340. We also know that we’ve sold a total of 7 bottles over the week at $200 each (the combined price of all the units sold). This gives us total sales for Dynamite Gin of 7 x $200 = $1,400.

If we plug these numbers into the formula above, we’ll get a pour cost of:

Pour cost = $340 / $1,400 = 24.3%

For most bars, a pour cost of 15%-25% is manageable. High-end establishments may have more ambitious profit requirements but in general, a 20% pour cost is a good target to aim for. And when it comes to spirits, you should be aiming for the lower end of the spectrum.

However, our pour cost of 24.3% is towards the upper end. Why is that? And how can we influence it?

This is when everything we did so far ties in together. Considering that the variance is okay at 17.6%, the high pour cost is likely due to underpricing or paying too much per bottle. If we negotiate a deal with our supplier to reduce the cost from $40 to $33 per bottle, the pour cost would go down to about 20%.

This is just one example of how keeping track of your inventory can help you stay on top of metrics and make better business decisions. To make it easier to track all of this, you can use the same spreadsheet as before, just adding some additional fields and formulas as seen below:

You can get a copy of this spreadsheet and modify it to your needs below:

Email *

How to streamline your bar inventory management

Now you have a good basis to keep track of your inventory and calculate your key metrics. But the trick here is maintaining this consistently. To do that, you’ll need to streamline your inventory process because you will soon get tired of managing everything on a spreadsheet. How do you do that? There are 3 main components:

1. Choosing the right inventory management software

Once you understand the inventory process and doing it is a matter of consistency, you will likely find an inventory management software useful.

3. Training your staff and ensuring regular inventories

Even if you implement a robust inventory system, you’ll need to train your staff on using it.

To make sure the process is done consistently (which is the hardest part) select a few team members that will share responsibility. Ideally, they should be split by departments if you have a bigger establishment (e.g. kitchen, bar, cleaning) so everyone can feel involved and engaged in the process.

Also, if you have lots of items, you will need to split responsibility between several people so no one gets overwhelmed.

Conclusion

Proper inventory management might as well be the difference between a thriving, profitable bar and a failing one.

Simply doing it will get you ahead and then all the other benefits come into play – such as running your bar with clarity and making better business decisions.

In this guide, we introduced a step-by-step process for doing inventory for bars. We also looked at how to use that data to calculate key metrics like usage, variance, par levels and pour costs.

These are truly the backbone for sound bar management since they allow you to control your budget and avoid overstocking or understocking on a weekly basis. They also help with pricing and investigating any potential losses due to theft and non-compliance.

Lastly, we looked into a few tips to streamline inventory management using software and splitting duties among your team.

As a next step, we’ve prepared a simple spreadsheet to help you get started with inventory management in your bar: