Inventory Management Review

A Case Study in Software Implementation

I recently had the pleasure of attending a guest lecture delivered by Optiant director of research, Ph.D. John Neale on the topic of multi-echelon inventory management software implementation.  Optiant is a supply chain software solutions provider for companies including Gillette and HP.  His discussion described how the implementation of Optiant's PowerChain® software suite helped to dramatically decrease inventory and increase service for a well-known manufacturer of consumer adhesives.  This post will give a history of the adhesive manufacturer's supply chain problems and the solution they chose.  The results of that solution will be explained with a brief lesson on multi-echelon inventory.

Supply Chain
The manufacturer being discussed operates primarily in North America.  Their supply chain has the following characteristics:

Raw Materials
First, they procure over 3000 raw materials from multiple sources and hold the raw materials at one of their two manufacturing sites until they are processed.  This is the first stage (echelon) where inventory is held.  After the raw materials are turned into finished goods, they are immediately shipped to a distribution center (DC).

Finished Goods
The firm has two DCs and over 1800 SKUs (stock keeping units.  This is basically how many different products and package variations they have on those products).  The DCs are the final stop for finished goods inventory before they are shipped to a myriad of retailers.  Of those retailers, Wal-Mart, Staples, Home Depot, and other mass market stores constituted one third of their overall volume.  The DCs also receive some finished goods which come from other manufacturers in the form of finished goods.  The DCs are the second stage (second echelon, thus making more than one echelon, hence the name, multi-echelon) where safety stock is held.

Old Inventory Policy
Prior to Optiant's consulting work and software implementation, the manufacturer had no real method for determining their safety stock. Essentially, they used a trial and error method where they would set a level and if they were stocking out too often, they would increase inventory.  When they stopped stocking out, they would scale back inventory.  Their inventory was also high because of expansion in their product line and high service levels demanded by stores like Wal-Mart.

The manufacturer lacked the expertise regarding how to set a safety stock level that optimized each local inventory stage, and additionally, they were without the experience necessary regarding how to optimize the overall system.  As John Neale put it, this is a problem because while safety stock formulas can be useful for local optimization, one point he made was,

"Don't just optimize things in isolation."

Upon realizing that there were better ways of doing things, they contacted Optiant.

Optiant
What Optiant did for them was more than just selling them a software package. Optiant spent many weeks learning about their supply chain constraints and gathering data, and then used PowerChain®  to optimize the safety stocks for each of the 1500 SKUs and each of their 3000 raw materials.

Data Requirements
Much of this data is demand data. In order to get a feel for demand, Optiant uses historical demand projections.  In order to figure out what kind of deviation there is on these projections, they look at historical projections and compare them with historical demand realizations.  As you can imagine, a lot of companies don't keep accurate records regarding this data.  The less a company has in the way of records, the less effective software initially is.  Keep this in mind before you bring in any consultants: start collecting data before they get there, so you're ready to roll once they're on the clock.

Optiant also required supplier data, including lead times, costs, and a bill of materials (list of parts required for each SKU).

Software
Once they have the data, they can start to use their software model.  I'm not clear on the math that runs the program other than that it uses algorithms to minimize holding costs while maintaining service level requirements.  I didn't bother asking for more detail, because what I understand is this: the software they have works and is based on the kind of framework that you would expect to come from someone with a Ph.D. from MIT, which is precisely what Optiant co-founder, Sean Willems, has.  The point is, the program is complex, but it is not baseless, and it is not a hoax.  It is exactly the kind of complex software I was referring to when I wrote about the kind of advantage that professional software can offer that Excel can't even come close to providing.

Results
Before you refuse to believe the software works without understanding every detail behind it, consider that Optiant's solution allowed this adhesive manufacturer to raise their service level while lowering safety stock value by over 20%.  First of all, 20% is a very large reduction in safety stock value on its own.  In addition to this, they were able to raise their service level while lowering safety stock.  At first glance, this seems too good to be true.  Normally, the way to raise a service level is by raising safety stock, not by lowering it.  Why is this case any different?

Multi-Echelon Inventory Management
This case is different because it is a multi-echelon inventory model.  What this means in this case is that they had the opportunity to hold inventory at two stages: the raw materials stage and the finished goods stages.

Balancing Raw Materials and Finished Goods
Remember, holding costs are a function that involves the value of the inventory and at the raw materials stage the value is considerably less expensive.  This means that if the adhesive manufacturer has short production lead times from raw materials to finished goods, which they do, then they can afford to hold large amounts of raw materials, small amounts of finished goods, and still be in a position to meet demand.  Thus, by reducing finished goods inventory and increasing raw materials inventory, they can increase service level because of their ability to quickly turn raw materials into finished goods, and they can reduce inventory costs because they are holding less finished goods.

Risk Pooling
The other reason they are able to reduce safety stock value while increasing service is because they have so many SKUs that all use the same basic raw materials.  The importance here is the inherent flexibility that raw materials when they can become a variety of different finished goods.  This allows them to keep materials raw for as long as possible, which reduces their vulnerability to fluctuations in demand.  The vulnerability to these fluctuations is limited because many of their glue product SKUs are essentially pooled as one product with an overall demand that is less likely to fluctuate as long as products are kept as raw materials that can be turned into any product once demand projections are closer to demand realizations.  To further illustrate this is an example from the MITSloan Management Review about apparel manufacturer Bennetton Group SpA and how they delay final goods production by keeping raw materials in a position ready to be turned into finished goods:

An inventory of undyed sweaters gets stockpiled in one location; coloring takes place only after specific orders have been received. This pooling of demand across geographical areas, and across colors, helps Benneton greatly reduce inventory risk while more effectively meeting customer demand.1

Another example cited in the article is how the house paint industry holds only base paints which colors are added into instead of holding onto hundreds of different colors at each retail location.

The effects of this are incredible because for the adhesive manufacturer, paint companies, and Benneton, the risk of each individual product in the product line can be vastly reduced by simply keeping finished goods as raw materials for as long as possible.

Paint companies no longer have to worry about having too much blue paint and not enough red paint.  Statistically, the variations in each type of paint will even out.  So if yellow doesn't sell as much as expected and green sells twice as much as expected, paint companies are still ok as long as they have the right amount of base paint.  Unfortunately for the consumer this makes it difficult to return paint.

Luckily for the adhesive manufacturer, risk pooling works.  So does the software Optiant creates.

Final Notes on PowerChain®
Originally, the adhesive manufacturer only hired Optiant so that Optiant could use to use their software to tell provide a report detailing how to optimize each of their 1800 SKUs and 3000 raw materials.  The CFO of the adhesive firm was so impressed with the forecasting abilities of the software that he eventually invested in a license of the software.  I'm not sure whether or not they worked with Optiant to adapt the PowerChain®  software to their other computer programs for automated entry of optimal safety stock into their other systems, although this is something Optiant does.

I'm not trying to suggest that you dive right into the investment of such software, although I can't imagine Optiant would mind, but hopefully this post has given you a better understanding of what inventory management software packages can do for you and what the implementation process entails.

1 Sunil Chopra & ManMohan Sodhi, "Avoiding Supply Chain Breakdown", MITSloan Management Review, Fall 2004, Vol. 46, No. 1

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.

Decision Support Systems (DSS)

In my last article, I discussed some of the many changes inventory management has undergone in recent decades. A major reason this has become possible is because of the software that is currently in place to help firms make decisions. Decision support software (DSS) is capable of instantly analyzing data that once was nearly impossible to analyze without the aid of computers. What does this result in for us as managers?  Better decisions.

Take for example the issue of economic order quantity (EOQ). I mentioned in my first article that this is the quantity that should be ordered to keep the combination of holding costs and ordering costs as low as possible. As an inventory manager, if you are not attempting to optimize your EOQ, then you really are not doing your job. The formula for this is:

EOQ = SqRt [(2 * Demand * Ordering Costs) / Holding Costs]

This is simple enough if demand, ordering costs, and holding costs are all constant, but in reality, this is rarely the case. A typical manager needs to account for fluctuations in all of these variables. Demand typically is the variable that fluctuates the most. Unfortunately, even without fluctuations, determining EOQ for an entire firm can be incredibly time consuming. So how is a manager supposed to have time for anything else?  Decision support systems are the most viable option.

Today, software packages are available that integrate a company’s DSS needs. So, for the EOQ example, here is what can be performed with the software package. Bill over in marketing can come up with his most recent sales forecasts. Bill then enters the numbers onto the company’s network. The inventory manager now has an updated demand (one of the variables in EOQ). The inventory manager will then determine the ordering costs and the holding costs and enter those into the system. The amount that eventually gets ordered will transfer over to the finance department where Ted over in accounting will be satisfied because he can instantly get the numbers he needs. Overall, the entire firm is transferring essential information all through the firm’s software network.

Software packages today are capable of finding the EOQ using the information entered. In addition to this DSS will take into account additional variables such as how much warehouse space you have, or how much a supplier will allow you to order based on your line of credit and the software will help you make a more advanced decision based on this information. Still, you might be asking yourself whether you want to spend any of your department’s budget on software that solves the EOQ formula. No should be the answer to this, but DSS is capable of much more.

On its own, EOQ is simple. Mix it with the Re-Order Point (ROP) formulas and aggregate planning and it can become very tricky. Take my word for it when I say that advanced DSS is capable of saving you time. Prior to DSS, I had spent countless hours modeling the best results I could attain using nothing but Microsoft Excel. I once spent 60 hours in a week tweaking an aggregate plan to help minimize the projected costs of a project over its entire life. While I eventually built some pretty powerful spreadsheets, they were never as accurate or time-efficient as the software that had already been written by professionals. That same project I had spent 60 hours on, I might only need to spend 5 hours on with DSS. DSS is very powerful software that is essential to any business that wants to compete.