In Part 1, we discussed the shortfalls in the 7 compelling reasons why not to use a spreadsheet for your inventory planning In part 2, we look at what is expected from a spreadsheet from an Inventory Management perspective.
If you are planning to run your Inventory optimization using a spreadsheet, consider all the factors that should form part of your spreadsheet to give you what an inventory management system would provide.
Item classification
Before you get out of the starting blocks, you need to classify your stock items. It’s essential to do this so you can see which are your primary SKUs (stock-keeping units), the items that bring you the most turnover.
Classifying items makes it easier to identify your stock & safety stock criteria further down the line. Segmenting your data allows you to set rules based on groups of items that are similar from a planning perspective. Most companies that struggle to set appropriate planning parameters have felt this issue firsthand! It also really helps everyone to understand what the important items are to the business versus the “noise.”
The ABC analysis is a criterion often used to classify inventory according to the value of the items where A = highest value, B= medium value, and C= least value. However, using this analysis in isolation has its downfalls as a high-value, slow mover is potentially in the same group as a low value, fast mover. If you overlay this with another set of criteria as an example, the sales velocity of each item where H = high, M= medium, and L – low, your overall classification will be more precise. Preparing an ABC item classification using spreadsheet formulas may be pretty simple to do. You will probably need to do that external to your planning worksheet separately and link back the result as a vlookup. That does create a linked spreadsheet, so you will need to be careful not to lose that or break the link. As soon as you introduce the 2nd classification set, i.e., the HML criterion, it becomes evident that a spreadsheet is not the ideal choice as you now have yet another linked spreadsheet and vlookup to maintain. How do you email this, and what if you forget to include the other sheets and the user updates the links? Besides the complex formulas and links needed, the chance of human input error is extremely high, not to mention how time-intensive this becomes from a labor perspective.
Supplier history
You will need to analyze your supplier history data as it is directly related to your demand forecasting. This requires you to look at your order information against your goods received information. Data from your ERP will need to be exported and manually entered into your spreadsheet. But, this data should be ‘cleaned’ first before exported. There will be anomalies that should be stripped out, such as goods that were airfreighted in on special orders or orders where there was an unexpected delay. You’ll need to think about partial shipments and blanket orders too, and are they part of the metric or not? Taking these anomalies out of your ERP data is imperative for proper supplier performance measurement. In the process of getting this data into a spreadsheet, there is a high chance of data entry errors, AND the matter of wasted time once again rears its ugly head. This data will need to be continuously updated – every time a new order is received, and every order created will need to be dragged across manually. This probably needs to be in a separate workbook linked to your planning spreadsheet, or you’ll get to the limits of what Excel allows.
With all this work to get it right? Many companies just set an arbitrary amount as risk, maybe per vendor, but this will get horribly outdated quickly. And what about factors like COVID? This changes the results drastically and therefore changes the profile of buffer stock needed. If your competitor can deal with this better than you can, they will win the high ground!
Sales & previous forecast history
You need to take your sales history and map it to your previous forecasts to check your accuracy and predictability. Again, this involves loading sales and previous order information and using complex formulas to get the results, which are only as up to date as your last manual data input.
One crucial aspect not to lose sight of was when you placed the order; a forecast was used for that order. If that was, for example, three months ago, that forecast could have been changed three times by now. You CANNOT measure today’s forecast against that sales history. You have to measure the historical forecast that was used when placing the order. We call this window the cover forward period. The best option is to average out the forecast attempts for each of the months in the periods leading up to that point. As you can imagine, this can get complicated in spreadsheets and creates quite a calculation load on Excel. Again you’ll need to consider separate workbooks with links back to the primary datasheet to do that successfully.
The accuracy of that forecast versus sales, or forecast risk, is a critical input to calculating the buffers needed for the business to achieve the fill rates. It is also essential that you measure these accuracies and feed this back to the sales team. Remember the adage – you’ll see improvement in what you measure? So, measure forecast accuracy, get an improvement, and make the planning more accurate.
Target fill rate
Every product should have a target fill rate to control the amount of buffer stock needed. The target fill rate on your fast movers needs to be higher than your slow movers – you don’t want to be caught without stock on your popular items! The higher the target fill rate, the more safety stock is held to buffer the risk, but the same target fill rate does NOT create the same amount of buffer stock being needed since product risks are different across items.
This adds another complexity to your spreadsheet. Lots of companies out there set the buffer as a simple period of cover expected. The next level of refinement is to make that buffer bigger or smaller based on the classification of the items. Some inventory planners go even further as far as linking that to lead times as well. That becomes more complicated but is still not good enough. You need to factor in desired target service levels, lead times, order cycles, supplier performance for a preferred vendor, forecast accuracy, and any minimum buy quantities or multiples, to start getting accurate buffer levels per product. When you get this right, the investment in inventory drops and service levels go through the roof.
Your spreadsheet now consists of hundreds, if not thousands of rows and columns, formulas, and links. All of which need to be manually updated and managed.
Demand forecasting
This is where it gets even more complicated. Consider trying to forecast demand in a seasonal business or forecast for an item that has zero units sold BUT only because there was no stock availability. When you have a new item – how would you treat that in terms of its forecast when it has no sales history? The number of mathematical equations involved in providing an accurate forecast with all these different scenarios makes this complicated quickly! How do you get the computer to decide which formula is best for each item? The chances of human error in these types of equations can be exceptionally high and dramatically affect your bottom line.
A spreadsheet may work well for companies that have a small inventory holding with few inventory items. But most companies plan to grow, and if growth is in your plans, your chance of failure if you are using a spreadsheet is high. Spreadsheets are simply not scalable. It’s very clear from the information provided that it’s nearly impossible to design a spreadsheet to aggregate all of the data required to forecast demand accurately, assess risk factors to calculate safety stock and produce optimal purchase order quantities.
There are inexpensive cloud-based solutions that represent the next generation of tools available. If you invest in an automated solution to replace your spreadsheets, you can expect to see benefits such as:
- Reduction in human error, along with its associated costs and inefficiencies.
- An improvement in the time taken to complete tasks and the ability to refocus your staff on other more critical tasks – get your team to manage the supply chain, not the spreadsheet!
- Full visibility of your inventory so you can track, monitor, report, and manage by exception.
- Drastically improve governance, compliance, and reliability.
- Facilitate collaboration and communication with your operations, sales, marketing, and finance teams.
- Establish a proactive and not reactive approach.
- Employees will be happier, more confident, and less stressed.
Spreadsheets pose a significant risk to the financial health of companies. Companies must look to reduce their exposure to these financial risks that are hidden away in spreadsheets that are not fit for purpose.
The investment of an inventory management tool will undoubtedly boost productivity by providing better forecast recommendations. This will ultimately free up your working capital so you can grow your business.
TruAudio, a USA Custom AV company, were battling to manage their inventory using spreadsheets. Download their case study and see how they were able to halve their excess inventory in only six months.
