Inventory Management Review: Notes From an Inventory Management Consulting Job: Part III of IV

August 05, 2005

Notes From an Inventory Management Consulting Job: Part III of IV

3 of 4: Building the decision support system

Now that I have determined the Reorder Points, I needed to figure out how to help the managers determine that their inventory was at this level without having to count their inventory each day. This requires the following steps:

Step 1: Count the current inventory levels. This needs to be performed as a starting point. In this case it only took about 30 minutes to count it and enter the values into the database I built for them.

Step 2: I then entered these values into a column that was labeled as Most Recent Inventory Levels in an excel spreadsheet.

Step 3: I setup up a database in excel that keeps track of how many and of what type of kits are sent out on a daily basis. Data for this database needs to be entered daily.

Step 4: I took the daily data and summed it up so I now had total data for each type of kit.

Step 5: I determined which materials were associated with each type of kit and I set up Excel so that it automatically subtracts each days raw material usage out of the current inventory levels.

Now I had setup a system that automatically subtracts inventory as it is used up. This constantly accurate inventory level is compared to the Reorder Points and when the actual inventory level is less than the Reorder Point, the inventory manager is notified that it is time to reorder. This is all achieved by the inventory manager spending approximately 1 minute a day to enter in the day’s demand into the system.

While the system is setup to notify the manager, it is important that the data is as accurate as possible. I mentioned in the previous post that the demand data only contained 19 points. This is not exactly a huge sample size. To account for that, I have set up the system so that it constantly reevaluates the demand data.

This is achieved in a very simple fashion. The daily demand is kept track of as the inventory manager inputs it into the system. This demand is broken up into the two different types of kits. That data is then taken to a different spreadsheet which uses the recently entered demand data along with historical data and calculates the average demand, the standard deviation of demand, and the average ratios of the two types of kits.

The only thing left to consider now was how returned items affect inventory levels. Because of bad addresses, a small percent of the mail comes back to the mailing room. Lucky for them the more expensive parts of the kit can be re-used. Realizing this could throw off inventory levels, I altered the spreadsheet so that there was a sheet into which daily returns are to be entered. This data is used to add in additional inventory to the parts that can be reused.

On a normal consulting job I would now evaluate the Economic Order Quantity to really help keep inventories as low as possible, but quite frankly, I didn’t feel like it in this case. Ok, the truth is it wasn’t necessary. As stated earlier, the quantity discounts are too great to pass up. The ordering costs are high and the space used for inventory is going to waste otherwise. Finally, the inventory isn’t particularly expensive, so the money invested in them isn’t even really a waste of funds. The economic order quantity for them is as many as they can fit.

This post concludes the step by step discussion of how the system was built. The next post will discuss the usefulness of Excel as the decision support system.

--
Did you enjoy this post?

August 5, 2005 in Case Studies, Implementation, Software | Permalink