How to use a bar inventory spreadsheet.
Spreadsheets are the "Old Faithful" of better bar inventory management.Kyle Odom Bar Cop chief inventory connoisseur
What can I say - I guess I'm just an old dinosaur and always will be.
I love the Internet and think it has propelled us leaps and bounds in the way we communicate, but when I think of managing bar inventory... I'll stick to using spreadsheets.
Bar inventory spreadsheets are simple to use, they organize your inventory efficiently, and using a spreadsheet doesn't store your private business data in the cloud (more on that later).
I started using spreadsheets for my own beverage and food inventory decades ago when I ran my own restaurant.
Now, I'm not talking about full-blown bar inventory management software powered by Excel like Bar Cop.
No, just a simple two page inventory spreadsheet that kept track of my stock pretty good.
And it worked well for my needs at the time, or at least I thought it did.
Over the past couple of decades working on the inventory management side of the industry, that two page spreadsheet has grown into what the Bar Cop software is today, a sophisticated workbook app with over 150 combined spreadsheets that break down every aspect of an establishment's product flow.
But I'll save Bar Cop's story for another day.
Bar inventory spreadsheets work...
Being in this industry for so long - I absolutely hate seeing bars and restaurants fail.
I never get used to it.
Yet, I see it again and again.
There are always going to be establishments that don't make it - maybe they were opened in the wrong location, or they didn't have enough capital to sustain operations long enough to grow at least to break-even. I can accept those restaurants and bars not making it, I don't like it, but I can accept it.
But when a restaurant or bar fails because of things they can easily control like prioritizing inventory management, well that hurts.
If you don't have good inventory management, you will have theft, and theft can put bars and restaurants out of business.
Inventory management does not have to be complicated.
A simple spreadsheet to track your bar inventory is easy to start.
You absolutely need to track your liquor, wine, beer, and food inventory - in some way.
There are different options when using spreadsheets.
You have Microsoft Excel, Google Sheets, OpenOffice, and a few others.
Bar Cop is built on Excel because it has important features and capabilities that the others don't.
But if you're just going to use a small one or two page spreadsheet for your inventory management then Google Sheets will work too.
I highly recommend that you go with either Excel or Google Sheets, because they will be around in the future for support and upgrades, where other options may not be.
Once you choose which type of spreadsheet that you want to use, you need to get it setup.
The point of inventory control is to track each product's actual usage - not what was rang into your register.
So when setting up your spreadsheet, you need to have a beginning and ending inventory count.
You can take inventory in one of two ways: eye-balling your products (also known as tenthing, weightless, or point counting) or by weighing your products.
We always want bar owners and managers to weigh their products because it is a lot more accurate than eye-balling.
However, any inventory management is better than no inventory management - so if you want to point count your products then that's how you should setup your spreadsheet.
So now your spreadsheet should have a beginning inventory count and an ending inventory count (with data entered as either point counts or weights).
Next you need to include purchases.
To track your inventory usage correctly, you have to know how much product you added to your stock between the beginning count and the ending count.
For example: If your beginning count of Absolute is 3 bottles and your ending count is 5.5 bottles, you would have had to added bottles to your stock.
That would be your purchases.
With just those three numbers - your beginning count, purchases, and ending count, you can track your product usage from the start of an inventory period until the end of an inventory period.
You'll need to do total calculation that takes your beginning + purchases - ending to get a final usage number.
You can then cross compare product usage to your POS sales to see if there was product that was poured (or served) and not rang into your register.
By creating a simple bar or restaurant inventory spreadsheet tracking this data, you are taking a great first step to managing your inventory and stopping theft from happening.
Since I mentioned checking your POS sales, we get this question a lot...
"My POS has inventory built-in, why do I need to track my products another way?"
It's a fair question with a pretty straight forward answer.
If a bartender or any employee is stealing, they are not ringing up what they steal into your POS system.
And that is how POS inventory tracks - based on what is being rang in, not on what is actually being used.
You cannot detect theft by tracking inventory based on sales, inventory has to be tracked by actual usage.
By using an inventory spreadsheet to track your products from a starting point to an ending point, you will have a much more detailed view on what is really happening to your liquor, wine, beer, and even food.
Once you have an inventory control process setup, you can add calculations to your spreadsheet to compare usage to your register sales, learn your real pour costs, find out what your variance numbers are, and much more.
The data you can calculate on spreadsheets for better bar inventory management are almost endless.
You just have to take that first easy step and get started.
Why not the cloud? It's awesome!
Everything is moving to the cloud these days.
You can access data from anywhere.
Sign me up!
Here's the deal - remember I said I must be an old dinosaur.
Is the Internet great? I love it.
Is cloud technology great? I think it has its place.
Would I want my restaurant or bars business data stored on the cloud?
And this is just another reason why I remain loyal to using spreadsheets for bar inventory management.
If you sign up for a service - any service, whether it's for inventory management or POS software that places your data into the cloud - you and the software company you are using really have zero control over your data.
Software companies that use the cloud - outsource that service.
And there is a good chance the company they outsource to - outsources it to another company.
You as the customer have no real clue of where your data is actually being stored.
And your data can be stolen at any time and you would never know it.
There is a great study that McAfee Security Solutions did and found 1 out of every 4 businesses with data stored in the cloud has had their date compromised... you can read about it here - McAfee cloud data study.
As a restaurant or bar owner this would concern me.
Especially if your POS software holds any of your customer's private data in the cloud.
So what about inventory usage data and why would that matter?
If that data was stolen, what could be done with it?
Not much by your everyday hacker.
The reason I don't like the idea of your establishment's inventory usage data being stored in the cloud (especially if your POS data already is) - is because of the high audit rate in the bar business.
In the case of an audit, you have zero control over your data stored in the cloud being obtained by another party (and you should know what other party I'm referring too) without your knowledge.
Not knowing if another party has all of your sales and usage data that could potentially be used against you in an audit - well that's a scary thought to me.
And this is just another reason why I love using spreadsheets for restaurant and bar inventory management.
All of your real usage data is stored securely on your own computer and can be easily saved on an external drive and removed as needed.
Maybe one day I'll change my mind, but I don't see that happening any time soon.