The Bee Hive

I happen to look through the analytics of this blog and was amazed at the number of hits on my article of December 15 entitled Distribution Resource Planning (DRP). I had no idea an otherwise "dull" topic could generate so much interest out there. So I thought a few more offerings on this topic wouldn't do much harm since DRP is the place to start if you want to solve the inventory management woes of your company.

Many companies that I worked in usually attributed their inventory management problems to poor forecasting. And in all those companies that started DRP actually found that their problems were caused by a multitude of other issues, least of which was inaccurate forecasting! In this article, I would like to share a very important element of DRP - safety stock. Let's recap….

Distribution Resource Planning (DRP) is a method used for planning orders within a supply chain. DRP enables the user to set certain inventory control parameters (like safety stock) and calculate the time-phased inventory requirements. DRP uses several variables:

• the on-hand inventory at the end of a time period.
• the back-ordered demand at the end of a time period.
• the required quantity of product needed at the beginning of a time period.
• the constrained quantity of product available at the beginning of a time period.
• the recommended order quantity at the beginning of a period.

DRP needs the following information:

• the demand in a future time period.
• the scheduled receipts at the beginning of a time period.
• the safety stock requirement for a period.
• the on-hand inventory at the beginning of a period.

Most manufacturing facilities with multiple plants and distribution centers incorrectly plan production against a forecast of demand over a time period. The most common method is to use reorder points to replenish products from the supply source to the distribution centers (DCs). What is wrong with this? The sum of orders required to replenish the DCs rarely ever comes close to the total customer demand resulting in the plant making the wrong products at the wrong time. The correct thing to do is to forecast what the DC will ship to the customer and use DRP to calculate the DC's future replenishments from the plant. This invariably leads to the question of safety stock - how much, when, and how?

First of all, here's the formula:

Safety Stock:  {Z*SQRT(Avg. Lead Time*Standard Deviation of Demand^2 + Avg. Demand^2*Standard Deviation of Lead Time^2}

If that wasn't clear to you, I suggest reading on. This article will explain in detail what safety is used for, and how to use it.

Inventory management is about two things: not running out, and not having too much. Our desire to not run out, along with uncertainties in demand and supplier lead times are why we have inventory in the first place. Essentially, inventory is a reserve system to prevent a stock out. However, as important as it is to prevent such a stock out, we also don’t want to hold onto too much inventory because of holding costs.

So how do you balance the two and what is the right amount? More importantly, when should you re-order in order to prevent a stock out? The answer to this can be determined by obtaining and applying the following information about the inventory you wish to manage.

Re-order Point (ROP)

1. What is the average lead time for the part/finished good that you need?

2. What is the standard deviation of that lead time?

It is very important to track how long shipments take from you suppliers. If you are not doing this, start. It should be your top priority. Assuming you have tracked the data, excel can very easily help you determine your standard deviation. In excel, go to the toolbar and click on Insert, then click on Function, and choose STDEV and click ok. Then, enter in as much lead time data you have and presto, you have your standard deviation.

3. What is the expected demand you are working with?

4. What is the standard deviation on this demand?

Perhaps this is something you will be familiar with from experience, however, if not, this is something you should be able to squeeze out of the guy from the marketing department.  One way to find it is to look at historical demand and use the STDEV function in Excel to determine it.

5. How sure do you want to be that you aren’t going to run out?

90%, 95%, 98%, 99%? Whatever you decide, this will become your service level. Using this percentage, a statistical z-table should be used to get the corresponding “z-value.” A good z-value can be found  at:

http://www.inventoryops.com/safety_stock.htm

So, for example, if you want a 98% service level, you would use 2.05 as your z-value.

Ok, so you’ve gathered this data, now here’s what you do with it.

(Underlined section is safety stock)

Re-order point=Average Lead Time*Average Demand + Z*SQRT(Avg. Lead Time*Standard Deviation of Demand^2 + Avg. Demand^2*Standard Deviation of Lead Time^2)

In this formula, the first term (Average Lead Time*Average Demand) is the average demand.

The second term {Z*SQRT(Avg. Lead Time*Standard Deviation of Demand^2 + Avg. Demand^2*Standard Deviation of Lead Time^2} is the term that allows for the safety stock. In other words, the second term is the optimal safety stock level.

It is not simple to gather all the data that is needed for the calculations. For a product with multiple parts, each part needs to have its own re-order point calculations and its own safety stock calculation. This can all become very confusing if proper computer modeling is not employed.

Although I mentioned excel earlier, excel is probably not sufficient for your company’s software needs. If you have not already done so, it is very important to look into an integrated software package for these calculations and many others.

Good luck!