The Importance of a Shared Understanding of Data, Consistent Definitions and Data Credibility in Successfully Establishing a Data-driven Culture
Whether you’re a company that assembles parts to provide a finished product to the market or you’re a re-seller of finished goods, inventory is involved. While some might view inventory as just “items sitting on the shelf,” I invite you to think about it in a different way. How you manage your inventory can have a huge impact on your business because inventory management is very closely tied to cash flow management.
Precious cash was used to buy inventory, and until the inventory sells, the items on the shelf are cash locked away that a business can’t touch. Therefore, inventory management is very important and several key metrics will help you begin a journey into the successful management of inventory.
Just the Right Amount
Every company that carries inventory wants to have enough of it to be able to fill customers’ orders but have a minimum amount of cash tied up on the shelf. Unfortunately, one half of that statement wants you to stock more of each item and the other half wants you to stock less of each item. To balance out these conflicting goals, you need… just the right amount.
The challenge, of course, is in attempting to determine what “just the right amount” is because conditions constantly change. Inventory management is not a one-time activity. It’s an ongoing process of monitoring changes in sales volume patterns (trends, seasonality, changes in consumer preferences), price changes, supplier performance and sometimes commodity price indices.
Your First Few Metrics
If you’re an established, global business, there are probably at least 30-50 metrics that you follow in procurement and supply chain and you likely have dozens of procurement professionals on staff. If you’re a smaller company, you’re lucky if you have one full-time professional running your procurement and inventory management. For that reason, smaller companies need to start with a focus on just a few key metrics. Here are five metrics that you may find useful:
Sales Rate
This is a really simple metric that calculates, on average, how many of each item you sell each day. To calculate this metric, you’ll first need to decide how long of a time period you want to use for this measurement. For example, if you decide to use sales for the last 30 days, the sales rate for each item will be the quantity sold during those 30 days divided by 30. If I sell 600 bottles of orange juice in those 30 days, then my sales rate for orange juice is 600/30 or 20 bottles of juice per day.
Sales rates are never constant since the number of each item you sell will vary, so I like to have a dashboard calculate sales rates for the last 30 days. What I normally create is something like the image below which is a dashboard where the sales data is updated every day. I also like to see how this metric trends over time to gain insights into seasonality and changing customer preferences.
Inventory Management Tool: For each product, this dashboard view shows the metrics needed for supplier ordering decisions.
DSI (Days Sales Inventory)
How many days of inventory do you have for each product? When will you run out of inventory? How many days does it take to sell through your inventory? These three questions are essentially the same.
The calculation of DSI for each product is based on the sales rates that we calculated for our first metric. In the example used above, we know that we sell orange juice at a rate of 20 bottles per day. If we currently have 95 bottles available in our inventory, then we have 95/20 or 4.75 days of inventory. If we don’t add to our inventory stock, we anticipate running out of orange juice to sell in 4.75 days.
Cost of Inventory
Precious cash is tied up in inventory and the only way to free up that cash it to sell products to customers. For proper inventory management, we want to ensure that we are not tying up an increasing amount of cash in inventory. For this reason, I like to track the total dollars invested in inventory. To do this, we take the cost of each item and multiply that by the quantity of each item we have in stock. If you buy finished goods to resell, then the cost of each item is the price you paid to your supplier for these items. If you’re a manufacturer of products, then the cost of finished goods in your inventory is based on the cost of the raw materials used to make that product in addition to direct labour.
Percent of Line Items Shipped (or filled) Complete
This item measures a portion of the customer experience. How often do customers receive all of what they ordered when they expected it? This metric can be broken down per item to determine whether there is a subset of items that are hampering your inventory management success and impacting your customer satisfaction. To provide an example of this metric, suppose Customer #1 orders 6 pumps and 5 hoses. You have 2 pumps and 10 hoses in stock. You can meet the customers order for hoses but you can only ship 2 pumps to them. Of the two line items on the order, only 1 line item is shipped complete. Your percent of line items complete on this order is 50%. This calculation is expanded to include all line items on all orders within a chosen time period.
There’s just one thing to watch out for on this metric. Suppose customer #2 comes along and also orders pumps. When you have new pumps available to ship out, this metric will have an unintended consequence of making customer #1 wait even longer, because maximizing this metric means that employees being measured by this metric will want to send the pumps to customer #2. That creates a customer service issue since customer #1 will be kept waiting for the rest of his order even longer. You may wish to add a rule that whoever ordered first gets the first available product first.
A Histogram of Supplier Delivery Times
This isn’t a metric but rather a chart measuring supplier performance in terms of delivery variation. A histogram chart can be created for each supplier showing the range of delivery times you are experiencing with each one. How many times in the last year have they delivered product to you in 5 days, 10 days, 12 days… This chart counts the number of times the supplier delivered product to you in specific length of time. The overall chart lets you see what range of delivery times you can expect to see from your supplier.
A reference line should be drawn at the delivery time that was promised in a supplier’s contract to see how they perform against their promise.
In smaller companies, supplier contracts may not exist, but you can create reference lines based on verbal promises. The key point here is that you want to see whether the delivery performance you are receiving is working well enough for you to fill customer orders most of the time, i.e., within your defined customer service levels. You can view my YouTube video called, How to Measure Supplier Delivery Performance, for a full explanation of this measurement.
How to Construct a Useful Tool for Inventory Management
As with many impactful data projects, you may find that the data required for these metrics is stored in different data systems or different tables within a database. Much of the work to produce the suggested metrics, is in getting the data in a form that can be analyzed. That’s step 1.
Step 2 is in the creation of dashboards and data visualizations to make it easy for people to use the charts and tables created. Establishing a real-time data connection, or at least a frequent refresh of sales and inventory data to keep an inventory dashboard up-to-date is preferable. This has multiple benefits.
It eliminates having to repeatedly perform manual calculations which are prone to error.
It’s much easier to convince people to start using the data when current numbers are always at their fingertips.
Getting to Business Value
The final challenge for inventory management is in learning how to interpret the dashboard information for practical business use. It is valuable to have someone who has a broad understanding of business decisions and data analysis to help maximize the value of the dashboard tool created. They can help you determine how to make decisions based on the numbers and charts presented.
