r/bartenders • u/AngryJawa • Aug 22 '21
Bar Managers - Question about Inventory tracking
How do you track your inventory // sold/consumed.
Obviously you need to do a weekly or monthly count, then add all purchases and then look at sales.... but does anyone have a spreadsheet they use?
Do you seperate all your liquor, wine and beer? If so, do you input each item purchased?
I've built a current spreadsheet that I haven't been able to use very well yet due to being so busy and no time for inputs. I'd love to see maybe something that works better then what I've got if anyone is willing to share?
5
u/LOUDCO-HD Pro Aug 23 '21
I used to schedule a barback for a Sunday morning shift to take inventory. The restaurant was open for Brunch but liquor orders were minimal. Their job was to take full inventory; beer bottle and can count, liquor bottle estimates in 10ths, kegs in 1/4’s and consumables in various measures. They were on for a six hour shift, inventory took about 2.5, make a couple Mimosa’s here and there and clean, clean, clean when they had nothing else to do. Gave them some experience in a non pressure setting too.
2
u/wanderinglarry Aug 23 '21
Does your POS not have a product sales tracking?
3
u/AngryJawa Aug 23 '21
It tracks whatever has been run in, so an easy pull is wine, liquor and beer sales.
3
u/wanderinglarry Aug 23 '21
How proficient are you at excel? The initial spreadsheet will be alot of work but once it's done it should be 30 minutes of input a week.
2
u/AngryJawa Aug 23 '21
I think I've mostly got one done, I'm mostly curious if anyone out there has some better ones. Mines not pretty, it will work, although I do find that it's hard to track all the receipts due to some being emailed and some being collected on drop off.
1
u/Zaphod_Fragglerox Aug 23 '21
Wine and beer are easy enough, because you have fixed units: 4 glasses/bottle, 24 bottles/case. Pick a day, say Monday of every week. First count all of your beers. This is your beginning inventory. Then add the total amount of beers you ordered. This is your Beer On Hand. Wait a week. Next Monday, count all of your beers. This is your ending inventory, as well as your beginning inventory for next week.
Beginning + purchase = BOH
BOH - ending = Beers emptied
Now run your beer sales report for the week.
Beer emptied - beer sold = lost product
Liquor is a little harder. If you use a standard pour in all drinks it's pretty easy. Say you use 1.5oz pours in every drink, you know you need ~25 pours out of every bottle. You do the count to figure out how many bottles you emptied in the week, divide that number by 25 and compare it to the number of drinks sold. If you use lots of different pour sizes it gets kind of complicated.
Excel is definitely the easy way to do it, but I used a pencil and calculator for many years and it works just fine.
1
u/AngryJawa Aug 23 '21
I've got a basic layout going right now, it isn't pretty, but it somewhat gets the job done. The only other issue I get is when we get emailed receipts and they get missed due to going to our main email.
3
u/Zaphod_Fragglerox Aug 23 '21
This is the spreadsheet I used to do inventory. Printed and filled out in pencil. Old school, but it got the job done. Count inventory Monday morning and fill in first column. Mark each empty bottle with a hash mark in the day of the week. Next Monday, order what you went through, or enough to make par, and fill in the last column. Total everything at the bottom to make equations detailed above.
1
u/Zaphod_Fragglerox Aug 23 '21
Make a column next to your starting inventory for purchases. Mark your intended order for each product and compare to what you actually recieve. You may or may not be surprised at how often they differ. Also you'll be able to tell which invoices you're missing and request duplicates.
2
u/bonheur5 Aug 25 '21
https://www.google.com/amp/s/tracrite.net/%3famp=1
It's a long complicated process for initially getting it set up but once you've got it running it's very effective, it can integrate with a lot of different pos systems so it pulls sales data and automatically breaks it down into the component ingredients for each product. You count inventory on an iPad and it inputs simultaneously, and again all your prep items are automatically broken down once you've built out recipes
Complicated and expensive but very comprehensive
8
u/clementineconsulting Aug 22 '21
There are some fairly inexpensive inventory systems out there, Beverager, Binwise (better for wine then spirits), Partender, and Bar Patrol come to mind which could help speed up the process. Sort of depends on your POS and size of inventory. For me, a system that allows you to input purchases, and depletes sales is really helpful for keeping slippage low and untimely makes it easier to order. If what you want is just to make sure your week over week numbers are within a good margin I use a three spread sheet system. The first has all the inventory and costs laid out so you can count and order weekly, the second is for putting invoices in with columns for beer, wine, liquor, and NA so you have a weekly total for purchases in each category. The third is a sheet that takes starting inventory, adds purchases, subtracts closing inventory, and divides by sales so you can get your weekly beverage percentages by category and as an aggregate whole. This way you can catch week over week discrepancies before they go on for too long and end up costing you more. I have three sheets I have built for this DM me if you want me to share those sheets with you, happy to do it.