## Re-Order Point Calculation by using Data Trimming Methodology

When consumption of consumable materials are having lumpy or uncertain demand pattern in the past, then calculating the safety stock and re order point by applying the normal distribution function on raw data, you can get the desired result i.e. re order point with safety stock with desired service level. But there is still tremendous scope for reducing re-order point and safety stock without reducing the service level.

Let’s analyze the following material

Material Code: SKU001
Service Level: 95%

Below is the consumption pattern of SKU001. It clearly shows that the demand is of uncertain pattern with very few high demand peaks (i.e column No 2)

Average Consumption per month = 11.58, Standard Deviation = 6.9
Re order Point = demand during lead time + Safety Factor x Std Deviation of demand
Re –Order Point =11.58 + 1.64×6.9 = 22.89 ~23

These demand peaks (P1, P2 & P3) are causes of increase in value of standard deviation and average consumption.

If the average consumption or standard deviation are reduced then automatically re order point quantity and safety stock are also reduced i.e. inventory is also reduced.

Since these 3 nos of peaks (Highlighted in yellow color, P1,P2 & P3 in graph ) are the cause of higher value of average consumption and std deviation.

We can trim these peak data points, let’s by two times of the average consumption. (You can trim the data point from to 1xaverage consumption to 3 x Average consumption. It all depends on your data points)

After trimming the data (P1 to P’1, P2 to P’2 & P3 to P’3) the values come as under

Average Consumption per Month = 10.81
Std Deviation = 4.57
After calculating the Re-Order Point as earlier, it comes out 18, which is reduced by 21.7%.

This is not the end of the trimming; the data can be further trimmed. Here, you can trim these three demand peaks ( P’1, P’2 7 P’3) to next highest peak of demand (Issue Qty 17, Month Dec’05, Now P’’1, P’’2 & P’’3). After trimming the value comes as under

Average Consumption per month = 10.31
Std Deviation =3.34
Now Re-order Point shall be 15.78 ~ 16 (After rounding off)

Total percentage reduction in the re-order point qty = 100*(22-16)/22 = 22.27%

So here, it can be seen that by modifying the existing raw historical data there is enough scope for reducing the inventory
Related Words : Re-Order Point Planning, Consumption based planning, Material Requirement Planning (MRP), Safety Stock Calculation