In any wholesale distribution business, inventory is the byproduct of fluctuations in supply and demand and the mismatch between the two. A way to think of inventory is as the shock absorber that allows us to have a smoother ride on a bumpy road. Each change in demand and/or supply represents a bump, and some of that ends in increasing the inventory and some of it ends in decreasing the inventory. If the inventory did not exist, one would feel every bump in the road by way of customer complaints.
In any business, the inventory in any period can be simply defined as inventory in the previous period plus any supplies coming in minus any demand going out. This can be represented as a simple equation:
Most businesses have two types of inventories: planned and unplanned. Planned Inventories include:
The desire to have low inventory does not always result in perfect results. For example, when practitioners on LinkedIn were asked about the percentage of inventory represented by unplanned inventory in their suppychains, the response ranged from 15% to 70%. (These group discussions can be viewed here and here on LinkedIn). A variety of factors would influence such results. These could include industry sectors, skills of the business in inventory planning, ability or desire to get rid of excess inventories, etc. It could also be because of a difference in opinion in what constitutes excess inventory. After all, what seems excess to some might look like a bag of gold waiting for the right opportunity to others.
To illustrate the last point above, look at the results of a survey conducted by Cutwater Solutions. (You can participate in the anonymous survey here.) As you can see in Figure 1, while most confirm there is an inventory problem, 60% state it is a mixed bag of inventory being too high or too low. And about 25% said that it also depends on who you ask.
Figure 1: Percentage response to the question: What is the typical inventory problem at your firm?
When a wholesale distribution company finds itself in a position where the inventory is not in the right place, the typical response is to create a quick spreadsheet to calculate which inventories are in excess, allowing the business to do something about the problem quickly. For example, a report can easily be created which shows the items that have not sold in the last 12 months, but still have inventory. Depending on the business, these can be included in a list for quick disposal via sales or other promotions. Spreadsheets have the obvious advantage of being ubiquitous and a high degree of user acceptance. This approach, while effective in a piecemeal way, has the following potential disadvantages:
Other factors that make spreadsheets unproductive are the complexities created by amount of data, multiple users and need for connectivity to ERP systems.
If you decide your firm has outgrown the Excel-based inventory analysis, what is the next step? One option is to look at inventory analysis software based in a database management system (DBMS). Examples of DBMS include Microsoft SQL server, Oracle and DB2. Ideally, a practitioner chooses an inventory analysis system that runs on the same DBMS as the company’s ERP system (or integrates well with it). At the same time, these solutions should still be easy to use and integrate with the users’ desktop. Practitioners should also look for successes of the software with companies of similar size which are using same or similar ERPs. Functionality wise, these systems should be able to do the analysis using a nuanced approach, wherein they do not try and apply the same rule to all material-location combinations. Instead, the system should do a ‘structural’ analysis to identify areas of improvement with maximum impact while minimizing any downside.
These applications gather data automatically from an organization’s ERP not only at a preset frequency, but also on demand, and they hold it on a centralized server where it may be accessed by many users. More importantly, they offer true business functionality, including specialized support for a wide range of inventory analysis such as Pareto analysis, customer and order patterns, dead and excess inventory, too much or too little inventory, candidates for rebalancing, etc. This enables a company to truly understand the difference in the ‘DNA’ of different products and/or product families. They also provide multiple security levels, allowing access based on roles.
DBMS-based applications are not included with the purchase of a laptop; at the same time, most distributors have a few SQL servers running as part of their enterprise systems. These applications can provide business value far exceeding their cost. Compared with Excel, for example, solutions based on database management systems offer significant advantages:
Finally, they can detect patterns within the data itself, to make better-informed decisions about overall processes and individual projects. Deciding to switch from a homegrown Excel application to a vendor-provided and DBMS-based tool is not easy. Depending on the size of the company, many people and departments (including planners and other users, IT personnel and management) need to be convinced. Next, the timing also needs to be right. A manager who thinks the business has outgrown Excel-based tools might still need to wait for the right political and economic climate. For example, the disruptions caused by a natural or a market event might make the case for a change, or the arrival of a senior executive with experience in these types of applications might also tip the scale. The practitioner should keep the end-goal (of right sized inventory) in mind as they navigate this obstacle course.