Inventory Management Review

Implementing Reordering

Actually reordering and knowing when to hypothetically reorder based on ROP formulas are not the same thing.  Of course, in order to actually reorder, you should know when to do it.  That is, you shouldn't reorder without ROP.  You should determine the ROP and order accordingly, which requires constant inventory level tracking.  My last post, Different Inventory Systems, detailed ways to track inventory levels.  So if you track inventory levels, and know ROP, you should be good, right?  Well, not really.  You still need two things: someone to actually reorder and the knowledge that it's time reorder conveyed to the person in charge of reordering.

First, let me give an example that should help demonstrate the issue at hand.  The firm that I wrote about in Notes from an Inventory Management Consulting Job did not have a perfectly smooth transition with the new system.  While it is working to their advantage very well now, there were some bumps in the road.

Primarily, there was a bump in the road of not reordering materials on time.  Now, this is a pretty big problem considering that the system was commissioned so they would know when to reorder.  Apparently people don't like spending money on a system that doesn't help them.  Now, I don't want to say it wasn't my fault that it wasn't working, BUT, luckily it wasn't my fault and the issues have since been fixed.

You see, the system was designed so that when materials hit the safety stock level, it was time to reorder.  Despite information in the system that very clearly alerts users that it is time to reorder by saying "NOW!" in big red letters when it is time to reorder, there were still issues regarding the actual reordering.  There was even a countdown by the system that allows users to know how many days they have left until they have to reorder.  It was really quite clear.

The problem, as I discovered, wasn't so much that they didn't know when to reorder, or how much to reorder, or what their inventory levels were at so they could decide to reorder or not.  The problem was they didn't reorder.

There are two solutions to this.  First, I could have manipulated the system so that it told them reorder a couple of weeks before they really needed to.  Basically, I could have implemented a safety net into the system that would account for their inability to reorder on time.  For some firms, this could be a viable solution.  A good example would be if there is a redesign that needs to performed each time materials are reordered.

The second solution is to reorder the damn materials when the system tells you to.  This, although not in so many words, is what I suggested to them.  Unlike the first solution, this solution reorders when it makes economical sense.  The other solution dumps in excessive inventory into the system.  It has you reorder before you should which raises average inventory, which needlessly ties up cash into the system.

Back to what I was saying earlier, you need someone to reorder and you need that someone to have the knowledge that it's time to reorder.  Here, the firm needed someone to actually do it.  People had the knowledge, but there wasn't someone who got the job done.

So how do I implement this?
There are a number of different ways to implement this.  While I won't go into detail regarding building a system, I will give the basic requirements on managing the implementation.

Most importantly, knowledge needs to be conveyed.  When your inventory tracking system recognizes that levels have reached the ROP, this information needs to be made available to people that can do something about it.  I'm not saying sirens need to go off, but something needs to occur that results in a materials orderer discovering that he has an order to place.

As I discussed eariler, someone needs to have the job of actually reordering.  I see it too often that people don't reorder when they know they should.  Don't let this be you.  Don't be the manager who invests money into a system but doesn't invest time into ensuring his employees are properly using it.

Before I conclude, I will mention that this entire process can be automated.  EDI, or electronic data interchange, used alongside RFID can accomplish such.  RFID is used to track inventory and when the levels drop below safety stock levels, EDI automatically places orders with suppliers.  Wal-Mart is a great success story regarding these two technologies.

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.

 

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

2 of 4: Collecting the remaining data needed to determine Reorder Points 

To recap, at the end of the first day of consulting I had found out what went into the kits and the lead times for the materials. On day two it was my job to determine what the demand was and how much standard deviation was in that demand. Lucky for me the mail room manager keeps excellent records of this data. Not so lucky for me was that their demand recently increased by quite a sizeable amount due to a permanent increase in advertisement and that this demand was projected to remain high. This cut my useable demand data drastically.

Fortunately the manager was still able to supply me with 19 pieces of demand data. This data was enough for me to determine the average demand the mail room faces as well as the standard deviation in that demand. I then took that data and turned it into weekly figures and determined the average weekly demand and average weekly standard deviation. Although this weekly demand data is only based on 3 complete weeks worth of data, the data will be better in the future, as I’ll explain later.

As I mentioned earlier however, there are certain types of kits that require an additional booklet as well as an additional 30 pieces of paper. In order to determine the approximate demand for these materials, I determined the approximate percentage of kits that require these extra pieces and then I counted those pieces into the use of raw materials only as often as I projected they would be used. 

Now I had the information I needed to determine the Reorder Point. I had the average lead time, the average demand, and the standard deviation of the demand. The missing piece of this formula is the z value which comes from the service level. Service level and z values are explained in greater detail in a previous post which can still be found on this blog.

I multiplied the average lead time by the average demand together in order to get the point at which they should reorder assuming there is no deviation in demand. However, since there is always deviation from the average demand I also determined the safety stock.

To get the safety stock, I used the following formula:
 

z*Square Root of (Average Demand^2*Standard Deviation of Lead Time^2 + Average Lead Time*Standard Deviation of Demand^2)

I Then added the safety stock to the result  determined from multpiplying demand with average lead time and together they make the reorder point.  When current inventories reach this level, the managers need to reorder.

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

This post is the 1st of 4 posts that provide detailed notes of an inventory management system design job and the building of a decision support system that helps managers determine when to reorder materials. 

I was recently hired to setup an entirely new inventory management system at a small mailing room in Los Angeles. Rather than keep what I learned to myself, I thought I’d share it with all of you. First I’ll give you some background information on the mailing room. 

The mailing room processes leads from a variety of different sources and sends each lead a mailing kit that has a few pieces of paper, a business reply envelope and a couple of booklets in it. This fairly small operation handles approximately 1500 leads a month and is run out of a vacant room in the office. The booklets, the business reply envelopes, and the large envelopes are all printed by another firm. The loose sheets of paper are printed in house and are printed on blank paper shipped from Staples. 

When I arrived at the mailing room I talked to the man in charge. While he had set up a very good system for keeping track of leads, when and where they came from, whether or not they were delivered successfully, and while he had setup a fairly automated mailing kit production process, his inventory tracking system was as archaic as his lead tracking was detailed. The ordering of his supplies was very simple. He would order as much as he had room for because the quantity discounts at the printing house were so great that ordering small amounts was not even an option. 

For him, the real question was when to order. Previously his policy regarding when to reorder was, “I dunno I reorder, when it looks like I’m running out.” As funny as this may seem, it is not the only company I have encountered that has this policy, and I can assure you, that is not a good thing. In this case, when I say it is not a good thing, I mean that in the sense that they ran out of supplies and now are facing two weeks of production with a missing part of their kit. I guess it looked like they had more inventory than they really did. 

Now I’ll discuss step by step exactly how I set up their system. 

The first step I took was to determine all of the individual components that are associated with the final product. It is a little bit more complex than what I mentioned earlier, so I’ll break it down in more detail. Each mailing kit involves the following raw materials: 

1 letterhead

Black Toner

2 blank pieces of paper

Color Toner

1 Booklet A

1 Booklet B

1 Booklet C

And sometimes the kits require an additional 30 pieces of blank paper AND

1 Booklet D 

The second step I took was to determine the lead times of each of these raw materials. Right now these are estimates. The manager in charge of ordering the materials has informed me that he does not know how long it takes for him to order and receive shipments on any of his supplies except paper and toner. His best estimate for the other supplies was that the lead time was approximately a week and a half.

That's about all the information they were able to give to me on the first day, so I think that's all I give you.  Part II, which will be published on July 28th will discuss the collection and analysis of the demand data.