4. Take the time to consolidate data
Taking the time to consolidate your data each month allows you to create charts that outline how much you’ve sold in each period. You should do this monthly and quarterly, paying special attention to holidays and occasions.
One of the biggest issues many people have with Excel is not being able to easily analyse your historical data – but taking the time to add your entire month's data into an easy-to-read pivot table will fix this. It is time consuming, but it allows you to track your sales based on period, and during different seasons, for better inventory optimisation in the future.
Here’s how to create a pivot table in Excel:
- Click “Insert”, Choose “Tables”, select “Pivot Table”.
- Click the arrow below “Pivot Table” to create a chart.
- Select the table range (e.g. 1A-21G) in the “Create Pivot Table” dialogue box that pops up.
- Choose the location of the new pivot table (new worksheet or existing one).
- Click ”OK”.
- Customise your pivot table as needed.
5. Review your data
Excel can be a valuable tool for tracking inventory, but it is up to you to make sure that you are tracking and calculating everything necessary. For example:
- If you're tracking time to reorder, have you included lead times (time from manufacturer to warehouse or 3PL) in your calculations?
- Are you calculating profits based on sales, or based on your actual per-item profit?
- Are you using your inventory to calculate GST immediately?
- Is your total inventory value based on resale or purchase value?
Going over the figures you use in your calculations to ensure that they are correct can save you expensive and time-consuming mistakes in the future.
6. Audit and review
Excel relies on people to input data, unless you have a barcode scanner connected to your system, and even then not everything is automated. This will lead to human error, which can throw off your entire supply and demand when you reorder too much or not enough, or under- or overestimate profits.
A policy of keeping track of everything on paper as well as in your Excel document allows you to go over your inventory at the end of each day to correct any mistakes.
7. Know when to upgrade
Excel simply does not work with medium to large inventory volume because hundreds of columns are too complex, time consuming and difficult to keep track of and keep up-to-date. At some point, you will have to upgrade to a dedicated inventory management system, and you should know when to do so.
A dedicated inventory management system helps you with everything from optimising your inventory for better sales and return on investment to growing your business and expanding to additional sales outlets as your consumer base grows, so your inventory management solution should grow with you.
The more sales you are making, the greater your need for effective inventory management. Using Excel will allow you to cut costs at first, but eventually, a dedicated inventory management system will be necessary to save time and money in the long run.
