.png)
A bar or restaurant relies heavily on its inventory to meet customer demand, make sales, and grow. That's why so many business owners invest in a perpetual inventory system, wine tracker, or bar inventory app.
If you're not yet ready to invest in a slew of tools, or you have a smaller inventory, you can start with an inventory spreadsheet. This sheet will let you update par level, note missing products, and even separate bottles by type of alcohol and location.
Keep reading to learn how to do inventory, create a spreadsheet, and access a few free templates to help make it easier for you.
How to Create an Inventory Spreadsheet
Creating an inventory spreadsheet can feel like a daunting task, particularly when you sell a range of products or store them in various locations. But, it's a vital step in inventory control and will pay dividends down the line. To make things easier, you should follow a step-by-step approach to building a sheet.
Here are the steps to follow to create a inventory spreadsheet:
1. Pick Your Platform
You can track your inventory levels in Microsoft Excel, Google Sheets, Apple Numbers, and more. Pick whatever spreadsheet tool you feel most comfortable with, and you know how to use. You'll also want to make sure any other employee that will need to access the numbers has the same program and knowledge.
2. Determine What You'll Be Tracking
You'll want to have separate sections or tabs for different types of products, including your wholesale products. Tracking food and beverages in one tab can get really messy and make it a lot more difficult to keep everything up-to-date and useful. This will also be very helpful when you start calculating your prime cost, food cost, inventory variance, and more. The last thing you want your inventory spreadsheet to be is more of a hindrance than a help.
3. Create Pertinent Columns and Headers
The most important thing you'll want to track are product brand or name; format (liquor bottle sizes, beer keg sizes, wine bottle sizes, case of wine, etc.); starting inventory, received inventory, ending inventory; and cost of Goods Sold for both by individual unit and total. With this information in hand, you can more easily calculate the numbers above. Note that these columns should be in all inventory sheets, but your business may have unique needs. Anything you can think of to track can be added at this point. If they don't work out, you can just remove the columns later and move on.
4. Perform a Physical Count of Inventory
Once the sheet is set up, you'll need to do inventory at least once to get your starting inventory numbers in there. Otherwise, you'll just have a fancy spreadsheet with no value. Without this first count, you won't be able to figure out your inventory turnover at the end of the period or know how much to order for the next period. You may even want to look into a barcode scanner app at this stage to help automatically populate your new spreadsheet with data. This can save you hours of labor.
If you're a BinWise Pro customer, you can get access to our own BinScan inventory scanning app for free. It can take your inventory to the next level and help you keep a closer eye on your numbers, so you can grow your bottom line with ease. Even better, it can be seamlessly paired with the Socket Scan S740 handheld scanner for increased efficiency. This scanner is easy-to-use and can take your scanning from 10 bottles per minute to more than 150 bottles per minute. That's immense time savings when you're counting inventory in any warehousing environment.
5. Save the Sheet and Share it With the Team
Now that you've built your sheet and put in your starting numbers, everyone can make use of it going forward. Give people some time to play around with the sheet and offer suggestions. Your bartender or back-of-house staff may think of a field or information that you missed. Update the sheet, and add in more data each time you do another inventory count. Then, you can start building out formulas that pull this information in to get you greater insight into what inventory is selling, how quickly, and if there are any issues with shrinkage.
Excel Inventory Template: Printable Spreadsheet
To make all of that work a little easier, you can just download our Excel inventory template for free as part of our full inventory template pack. It’s a CSV file, so you can open and edit it in Excel without any issues.
If you have a smaller bar without food, there’s also a bar inventory spreadsheet in the pack you can use to keep it simple. We have a robust library of inventory templates that can help you get on top of your inventory control.
Here’s just one inventory spreadsheet example from our download library:
Google Sheets Inventory Template
If you're more of a cloud-based spreadsheet person, we've got you covered there as well. Just access our free Google Sheets inventory template.
Once there, you'll see the same fields as the Excel version above with a few changes to fit the new medium. To start using the template, just click on File and Make a Copy, then save your own version to your Google Drive. This way you can edit all the information and leave the original for other business owners to copy as well.
Remember, this is just a general template suited for most bars and restaurants. You may have unique fields you track or want another tab for important calculations. Feel free to add, remove, or edit anything to help make the template your own. It's your business, so you need to do what's best for growing it.
Frequently Asked Questions about Making an Inventory Sheet
Do you have more questions related to how to make an inventory sheet? If so, here are some of the most frequently asked ones and our answers!
How Should I Organize My Inventory Sheet?
You can organize your inventory sheet by grouping items by category, location, or other relevant criteria to make it easier to navigate and manage. Consider using separate tabs or sections for different departments or product types.
How Often Should I Update My Inventory Sheet?
It's advisable to update your inventory sheet regularly, ideally after every transaction or on a scheduled basis, to ensure accuracy and reliability. This helps prevent discrepancies and provides up-to-date information for decision-making.
How Can I Ensure Data Accuracy on My Inventory Sheet?
To ensure data accuracy, establish clear procedures for recording inventory transactions, train staff on proper inventory management practices, conduct regular audits and reconciliations, and implement controls to minimize errors and discrepancies.
Ah Sheet, Here We Go Again
Tracking inventory doesn't have to be a miserable experience. Creating a spreadsheet or downloading a template can save you hours of labor in the future and help you avoid a lot of human error. Just stick with regular inventory counts or cycle count and update your sheet each time to ensure accuracy.