If you’ve ever had to do stock management for your bar, restaurant or hotel you’re likely to be very familiar with the humble Excel spreadsheet. After all, it’s how most teams keep track of food and beverage counts across the business.
Of course there are arguably better ways to manage inventory and keep those counts comin’, which often offer a more accurate, reliable or time-efficient way of counting stock (growyze being one of them!). But if you’re just starting out, or perhaps you don’t have the time right now to transfer over to a full system, we want to give you the run-down of how to use Excel for inventory management answering all of your questions along the way.
Can I use Excel for inventory management?
Absolutely! As mentioned above, using Excel can be quite time-consuming. You have to create the inventory management sheet setup, add formulas and manually move everything from open to close, or one month to the next, which means transferring data over. Which isn’t always that efficient, or accurate (hello human error). However, as a means to get started with and implement some kind of inventory management system, Excel is certainly a viable option.
How do you make a good inventory spreadsheet?
The key to a good inventory management spreadsheet is ensuring that you have everything set up to accurately log your inventory, sales and purchases. These are really the three pillars of data which are going to enable you to get to those all-important end figures, like COGS and gross profit.
How to set up an Excel spreadsheet for inventory management
Here, we’ll walk you through how to create your own Excel spreadsheet for inventory management.
1. Create a summary report
A summary report is usually the first tab of an inventory management spreadsheet. As the name suggests, it summarises all of the corresponding information and gives a top-line view of each area.
This captures in simple terms:
- How much stock you started with
- How much you purchased
- What you sold
- What you closed with
Within your summary report you would usually have a list of fields that compile information based on your latest stocktake. This may include calculations for the below areas:
- Open stock take and close stock date - this will show the dates you start and end this stocktake at. For example, you may open the stock take on 1st May and close it on 31st May if it’s a monthly report
- Opening stock (total) - the monetary value of stock you started with
- Purchases (total) - the monetary total of stock you purchased during this period
- Closing stock (total) - the monetary value of stock you ended with
- Net sales - how many sales you made during this period (usually split into categories - for example food sales, drink sales, merchandise sales, dry item sales depending on your venue)
The summary report usually reports actual COGS vs theoretical COGS. This differentiation is important. Theoretical COGS is based on the figures your stocktake has provided - for example if you had £1000 worth of stock and you sold £500 worth of stock, then your theoretical COGS would be £500. However, your actual COGS may be £300 and it’ll then be your job to work out where that £200 discrepancy has come from. For example, has some stock been wasted but not logged, is stock being stolen, were items not delivered by suppliers? Having these figures clearly listed on your summary report helps you to spot, and investigate, errors.
Top tip: Of course, investigating errors like this from a spreadsheet can be near-on impossible and leaves a lot of room for human error. That’s why we built growyze - to allow you to count, log and visualise your stock figures far more effectively. With in-built tools to flag discrepancies around stock counts, and reconciliation between what you order, what gets delivered and what’s invoiced, on every single supplier order. Try it for yourself today.
2. Create a count sheet
The count sheet displays the count from the stocktake and allows you to list all of your products, with the quantity of items you’ve counted for each one. It’s very common to have a different tab in your Excel sheet for each area: for example a Food Count Sheet, Drink Count Sheet and any other count sheets for different areas of the business (e.g. merchandise, dry items, cleaning products etc.).
Within these sheets you’ll likely have a list of headings relating to your stocktake process for example:
- Category or area of product
- Product
- Relevant information to that group of products, for example you may have case size or unit number for drinks and quantity or weight for food items
- Cost of product
- Opening quantity of stock for that item (usually transferred over from the previous stock take)
- Closing quantity of stock for that item (this is what you will have counted in the stocktake)
- Value or price of stock
- Sales on that stock
- Waste on that stock
- Actual vs theoretical number of stock items
If this seems like a lot of information that’s because it is! Logging every product or item can often take hours (for some venues, even days!) for a full stocktake. Some venues decide to complete a count every month, others less frequently depending on your business goals.
{{cta-ebook="/cta-components"}}
3. Enter your sales data
Next you’ll need a copy of your sales for the stocktake period. Depending on which POS system you use, you can often export the POS sales data and paste this directly into the spreadsheet.
Having the sales data within your inventory management sheet allows you to correctly identify exactly what’s been sold, so you can calculate your final gross profit effectively.
Top tip: growyze has ready-built integrations with many of the most popular POS systems which allow you to simply plug in your data without having to manually transfer this across. This means you get live sales data straight into growyze, to easily analyse stock levels and ensure you always have an accurate handle on where you’re making, and losing, money.
4. Add purchases / deliveries
The next tab of your spreadsheet shows what stock you’ve added during this period via purchases and deliveries. This is important because it adds context to your summary report. For example, if you started with 20 cases of coca cola, sold 10 and had 5 delivered, your closing stock count for that item should be 15. Without knowing how many items have been delivered you can’t effectively see what’s been sold (or misplaced/miscounted!).
For some venues you may also want to include a “transfers” section here, or even within a separate tab, to account for any stock transfers you make. This is helpful if you have a multi-site venue where you may borrow stock or loan stock from one location to another, so that each item can still be effectively accounted for.
Ready, get set… count!
Now that you have your inventory management sheet setup it’s time to complete your first stocktake. While this may be time-consuming it’s often the easy part compared to what comes after - working out why you have stock discrepancies in certain areas and reporting effectively on how your venue is operating.
For an easier way to manage your inventory end-to-end take a look at growyze. Our solution takes everything you do via Excel and makes it faster, easier and more automated. Keeping a single digital trail of every stock item, count, purchase, delivery and more with no copying over of spreadsheets from one month to the next. Plus powerful instant reporting dashboards for COG, gross-profit and more.
To get started with growyze today, book a demo with one of our friendly team.
