• A longtime client that is a distributor 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 their business with 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 and the only manufacturers left are in China (even the Tool & Die Companies that make the machinery used by our client’s manufacturers only exist in China).  Instead of receiving an order within days, it now takes over a month.  And to insure that the manufacturers have enough parts on hand, our client has to provide their manufacturers with a forecast of their inventory demand several months out.  Back in 1987, the term Supply Chain Management did not exist.  Now it had grown into a rather 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 the forecast, it was obsolete.

    We had talked about their problem as it grew offering possible solutions along the way.  We suggested implementing the Inventory Replenishment and Purchasing Modules or 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 effort 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 intuitively know but do not embrace.  In the final analysis, this implementation would tell them what they need 150 days from now, but they would not understand how the numbers were created – with a level of complexity that they did not want.So they asked if we could improve their Excel based process and we had some simple ideas that would help.

    The first step was a way 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 workbook via a program called a SQL Stored Procedure that is executed in a VBA Macro.  In one second, they had all of the historical sales information that they needed.  I thought, “Done deal, next project.”  But they wanted more.  They wanted a moving average of sales.  And they wanted to be able to select the time range for the 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.  And why not include the Inventory Turn ratio and the Days of Inventory (the reciprocal of Inventory Turn)?  When the vice president saw the “finished” product, a light went off.  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.

    But they still needed a way to include the shipments of inventory that were “on the water” and to predict where their inventory would be months from now, including the draw down from sales.  The sales draw down was easy, because we already had the raw data in the worksheet.  The quantities in the water were available in another workbook that they maintained and could be linked via the Excel V Look up 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 then updated the Days of Inventory.  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 past these four shipments so they could tell the supplier what to produce months down the road.

    The workbook was further refined to filter by Movement Classes, Product Lines, and Vendors.  This allowed them to concentrate on their key inventory items.  The data that had taken months to create 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 on hand.  During this process, it became apparent that they typically had twice the inventory that they needed.  As they adjust their orders to meet their targeted Days of Inventory, their inventory isdropping but will still be safe.  This inventory “tuning” willsave them seven figures in inventory holding costs.  A huge savings realized bycombining all the data that they currently have in Dynamics SL and Excel into one easy to understand Excel Workbook.