Inventory Management Review

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

This is the final post of a series detailing a consulting job I recently completed.  This post discusses the pros and cons of employing Excel as the decision support system.

The spreadsheets built for this job are a very good example of an automated inventory tracking system that can be built for much less than the price of purchasing a “real” inventory management software package.  There are however some definite pros and cons to look at when building your own software package using excel.

The main pro is the price. Excel is a software package that just about every office already owns and with a little bit of know how, you can build whatever you want. In some cases you might now even need to have any clue in terms of writing code to use excel to get it to do what you want it to do. It’s also a nice package to use because everyone in the office should already know how to use it. 

The downside of using excel is that it might not be able to do everything you want it to do, nor may it do everything it should do. For example, in this project I encountered a problem with the inventory counting. The counts should really be updated every 6-8 weeks just to be safe. But when they are, the used materials that are subtracted out of them need to be cleared. Unfortunately this involves the inventory manager at the mailing room to update the values in excel. Really this isn’t a huge deal but ultimately it cuts down on the automation of the system. (As it turns out, I found a way to get around this about 2 weeks after I wrote this, but I can assure you, it was a serious hassle).

Another serious hassle is getting these spreadsheets onto the internet. After completing this consulting job, I was recommended by the firm I consulted to do a job almost identical to this job. The only notable difference is that I had to put the spreadsheets onto a webpage that gets updated daily. Although possible with excel, the interface is not as friendly as I have seen with other software packages. 

Another problem with excel for this project is that I ended up using it as a daily and weekly demand database when really, Excel is a terrible database system. What makes it so terrible is that it does close to nothing to verify that the data is complete. Ensuring the integrity of the demand data is very using Excel. 

Overall, considering I’m not a information system specialist and yet I did manage to build this system using excel, I would have to say that for low level jobs such as this one, excel is an excellent tool that is already at hand and ready to be utilized by those who know what they are doing with it. As for the integrity of the data, had I teamed up Access with Excel, I could have ensured the data’s integrity to a better degree. In the end, the company got a software package that meets their needs for a sliver of the price that it would cost to buy a “real” inventory management system. 

If you would like to see the spreadsheets I have made for the firm discussed in this series of articles, you can now download them.  Please leave a comment letting me know your thoughts.  I haven't received any from any users yet (thanks a lot guys), so don't be a jerk.  I'll be posting user instructions very soon.  I promise.  Sorry to all who left comments any haven't received them yet.

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.