My friend in Houston, TX, Kurt Bradley, a successful Dynamics SL consultant for the past 25 years, shared this story with me. Other than worksheets and “inventory by walking around” there are few lower cost options for distributors suffering through the challenges of trying to figure out what inventory to buy without overstocking.
A distributor that is a longtime client has been using Dynamics SL and its predecessor, Solomon III, since 1987. They have kept their business simple and, in the past, had been able to adequately manage stock levels using Order Management, Inventory, and the core financial modules. But, over the course of the past decade, there has been a dramatic shift in their supply chain as domestic manufacturers have disappeared. The only manufacturers left are in China (even the Tool & Die Companies that make the machinery used by our client’s manufacturers now only exist in China).
Instead of receiving an order within days, it now takes over a month. To insure that the manufacturers have enough parts on hand, our client has to provide them with a forecast of their inventory demand several months out. Back in 1987, the term Supply Chain Management did not exist (the rapid response didn’t demand significant amounts of long range planning). Now it has grown into a complex monster. It got to the point where the vice president and another person were spending “months” trying to forecast demand. By the time they completed a forecast, it was already obsolete.
We had discussed their problem as it grew, offering possible solutions along the way. We suggested implementing the Inventory Replenishment and Purchasing Modules and various third party products. The cost of the modules (and a required upgrade to the Professional or Advanced Edition) would have been at least $20K. This did not include the labor to implement these products, nor the effort to derive a lot of parameters to which they could not relate. Reorder Point, Economic Order Quantity, Safety Stock, and Purchase Order Lead Time are concepts that they know intuitively but do not embrace.
In the final analysis, those implementations would have told them what they need 150 days in the future, with a level of complexity that they did not want, and they would not understand how the numbers were created. So they asked if we could improve their Excel based process, and we had some simple ideas (famous last words) that would help.
The first step was a method to quickly and accurately provide historical sales by item, in units. So we built an Excel Workbook that pulls historical sales for all items into the worksheet via a program-called SQL Stored Procedure that is executed in a VBA Macro. In one second, they have all of the historical sales information that they needed. I thought, “Done deal, next project.” But they wanted more (I’m shocked, shocked I tell you).
They wanted a moving average of sales. And they wanted to be able to select the time range for that moving average at the time of populating the workbook. While we were incorporating the moving average monthly sales, it seemed natural to pull in the real time Quantity on Hand and Quantity Available at the same time. And why not calculate and include the Inventory Turn ratio and the Days of Inventory (the reciprocal of the Inventory Turn)? When the vice president saw the “finished” product, a light went on. They could relate to the Days of Inventory and saw it as their key ratio/metric/KPI. They had been using “Weeks of Inventory”, so we were all on the same page (see, miracles do happen).
Double click the above image to be able to read it better.
But they still needed a way to include the shipments of inventory that were “in the water”, and to predict where their inventory would be months from now, including the drawdown from sales. The sales drawdown was easy, because we already had that raw data in the worksheet. The quantities in transit were available from another workbook that they maintained and could be linked into this one via the Excel VLookup function.
Since there are generally up to three shipments in the water at any one time, we created three groups of columns to predict the quantities on hand after receipt of each shipment and updated the Days of Inventory based on those values. We also created a fourth group of columns, so, when they filled the other worksheet with a new order from the supplier, the new worksheet was updated and new values calculated. A fifth group of columns was added that allowed them to forecast out beyond these three shipments, so they could tell the supplier what to produce months down the road.
Double click the above image to be able to read it better.
The workbook was further refined by adding the ability to filter by Movement Classes, Product Lines, and Vendors. This allowed them to concentrate on their key inventory items. The data that had previously taken months to generate was now taking 3 seconds.
Not only do they now feel comfortable that they will have enough of the right inventory on hand, but they can avoid having too much inventory. It became apparent during this process that they typically had double the inventory that was truly needed. As they adjust their orders to meet their targeted Days of Inventory, their on hand inventory is dropping, but they are still safe. This inventory “tuning” will save them seven figures in inventory holding costs annually. A huge savings realized by combining the data that they already had in Dynamics SL and Excel into one easy to understand Excel Workbook.
