Developing a system to prevent inventory stockouts.

[In a classic overview of forecasting demand,] the authors Chambers, Mullick, and Smith note that “in production and inventory control, increased accuracy is likely to lead to lower safety stocks. Here the manager and forecaster must weigh the cost of a more sophisticated and more expensive technique against potential savings in inventory costs.(https://hbr.org/1971/07/how-to-choose-the-right-forecasting-technique)Harvard Business Review

The researchers wrote this is 1971, and perhaps forecasting techniques are no longer carry such a hefty price tag. For this project we endeavor to provide Sigco, Inc. with an inventory management model that will help prevent stockouts of key products. Overall, Sigco uses 300 different items, but there are only a handful of ones which are utilized regularly.

To develop this model, we performed time series analysis with monthly demand data since January 2012 on thirty different SKUs that Sigco uses with such regularity that stock outs are common. We used a combination of forecasting and the EOQ. We exclusively rely on time series forecasting; because we are not looking into factors which may affect the SKU behavior, we do not do any regression that looks for correlation.

Decide on a horizon

*Initially we had 2 years of historical data, but research into time series analysis revealed that’s not enough to identify underlying trends and seasonality. Forecasting with this monthly data in POM consistently revealed that the lowest error, mainly MAPE, came from naive forecasting.

*Secured three more years of data. Once intitially plotted with the assistance of the forecasting package in R, we were able to patterns. We could call on special functions to decompose each time series for error, trend and seasonality and to return a forecast based utilizing the best model, mainly exponential smoothing. The forecasting model which returns the lowest error is automatically chosen.

*To apply these predictions to the EOQ model, we use a monthly forcast for the average demand and divide it by 21 (business days per month) to reach an average daily demand \[\bar{x}_d\].

*To arrive at the sigma of daily demand, \[\sigma_d\], we take the standard deviation of monthly demand for the hold out, divide it by 21 and then multiply it by the seasonal index, also returned as part of the decomposition function.

*Next, we calculate daily safety stock and reorder levels for eacj month. Because there has been a shift from month to month historically, we think it makes sense to apply an inventory system which takes this into account.

*Because Sigco would like to factor in a 10% increase in business, we can multiple the ROP and SS by 1.1

We did monthly and quarterly analyses. Quarterly returned lower errors, however in developing a test model where we “held out” about 20%, monthly analyses returned lower MAPEs.

Note to Chris…if Additive ts, [Seasonally Adjusting

If you have a seasonal time series that can be described using an additive model, you can seasonally adjust the time series by estimating the seasonal component, and subtracting the estimated seasonal component from the original time series. We can do this using the estimate of the seasonal component calculated by the “decompose()” function.

Preliminary Analysis

Graphing the data

Are there consistent patterns?

Is there a significant trend?

Is seasonality important?

Is there evidence of the presence of business cycles? Are there any outliers in the data that need to be explained by those with expert knowledge?

How strong are the relationships among the variables available for analysis?

Find the highest volume SKUs and do inventory management calculations based on historical data.

Equations based on the following assumptions:

  1. Continuous Review Policy

  2. consistent lead time of 6 days and a service level of 98%.

  3. Service level of 98%.

Average Monthly Demand:\[\bar{x}_d\]

Standard Deviation of Monthly Demand:\[\sigma_d\]

Lead Time:\[L = .214\]

Service Level:\[z = 2.05\]

Step by Step for Item_Code SS06CLR96130, 1/4 CLEAR 96X130 Demand Product Code

  1. Plot the time series.

  2. Plot the time series There appears to be trend and seasonality.

  1. A season plot can highlight patterns.

  2. Find an appropriate forecasting model with the lowest error. The summary below begins with “M, A, M”; this is the Error, Trend, Season taxonomy and for this item_code means Error = Mutiplicative, Trend = Additive, Seasonality = Multiplicative. Time series data which follows this pattern fits best using the “Multiplicative Holt-Winters’ method”. The MAPE error = 9.4%. Additionaly, the alpha, beta, and gamma values are given which are used in the smoothing equations.

## ETS(M,A,M) 
## 
## Call:
##  ets(y = WindowA) 
## 
##   Smoothing parameters:
##     alpha = 0.001 
##     beta  = 8e-04 
##     gamma = 1e-04 
## 
##   Initial states:
##     l = 1243.9225 
##     b = 24.0393 
##     s=1.1264 1.0676 1.215 1.0808 1.1453 1.1002
##            0.9214 0.9136 0.9221 0.8216 0.8366 0.8494
## 
##   sigma:  0.1157
## 
##      AIC     AICc      BIC 
## 893.6213 908.9213 928.6489 
## 
## Training set error measures:
##                     ME     RMSE      MAE       MPE   MAPE      MASE
## Training set -20.48308 219.9445 165.3238 -2.657917 9.3926 0.4353865
##                  ACF1
## Training set 0.071752
  1. Because we are interested in using the monthly forcasted demand as a proxy for average demand in the EOQ model, we forecast out 12 months; this seems reasonable for our purposes and the further out we forcast, the less reliable it becomes.
##          Point Forecast    Lo 80    Hi 80    Lo 95    Hi 95
## Nov 2016       2803.583 2387.803 3219.363 2167.702 3439.463
## Dec 2016       2983.791 2541.285 3426.297 2307.036 3660.545
## Jan 2017       2269.869 1933.238 2606.499 1755.037 2784.700
## Feb 2017       2254.887 1920.477 2589.298 1743.451 2766.324
## Mar 2017       2233.444 1902.211 2564.678 1726.867 2740.022
## Apr 2017       2527.982 2153.063 2902.902 1954.592 3101.372
## May 2017       2525.699 2151.112 2900.286 1952.818 3098.581
## Jun 2017       2568.585 2187.629 2949.540 1985.964 3151.206
## Jul 2017       3092.506 2633.834 3551.177 2391.028 3793.984
## Aug 2017       3245.865 2764.432 3727.297 2509.577 3982.152
## Sep 2017       3087.770 2629.769 3545.771 2387.318 3788.222
## Oct 2017       3499.452 2980.365 4018.540 2705.577 4293.328
  1. Below is a plot of the historical data, and the the 12 month forecast with 80 and 95% confidence intervals, in dark and light gray respectively

  1. To develop a test model based on the EOQ, we need a proxy for \[\bar{x}_d\]. Utilizing the monthly demand from 1/2012 - 12/2015, we project a 2016 monthly forecast. For the purposes of daily inventory management, this model has to be adjusted. Therefore, we divide the forecast by the number of business days in a month, \[\bar{x}_d=\frac{\hat{y}}{21}\].

The table below shows the monthly forecast for 2016.

Point.Forecast Lo.80 Hi.80 Lo.95 Hi.95
Jan 2016 2118 1789 2447 1615 2621
Feb 2016 2242 1893 2590 1709 2774
Mar 2016 1998 1688 2309 1524 2473
Apr 2016 2464 2081 2846 1878 3049
May 2016 2489 2102 2875 1897 3080
Jun 2016 2274 1920 2627 1733 2814
Jul 2016 2989 2525 3454 2279 3700
Aug 2016 2997 2531 3463 2285 3709
Sep 2016 2900 2449 3351 2211 3589
Oct 2016 3430 2897 3963 2615 4245
Nov 2016 2924 2469 3378 2229 3619
Dec 2016 2971 2509 3433 2265 3677
Jan 2017 2387 2016 2759 1820 2955
  1. To get the standard deviation of daily demand, we look at the summary data for the forecast generated from the 1/2012-12/2015 data. A monthly seasonal correction is given under “Initial States: s = …”. We use this correction as part of our \[\sigma_d\]. We multiply the standard deviation of 2016’s forecasted monthly demand/number of business days because this forecast was generated with a multiplicative model.

\[\sigma_d=\sigma_\frac{\hat{y_1...n}}{21}*s_i\]

## ETS(M,A,M) 
## 
## Call:
##  ets(y = variable1, model = "MAM") 
## 
##   Smoothing parameters:
##     alpha = 0.0131 
##     beta  = 1e-04 
##     gamma = 1e-04 
## 
##   Initial states:
##     l = 1211.3935 
##     b = 26.5752 
##     s=1.0666 1.0597 1.2552 1.0716 1.1183 1.1266
##            0.8655 0.957 0.9571 0.7844 0.8891 0.849
## 
##   sigma:  0.1211
## 
##      AIC     AICc      BIC 
## 560.1777 592.3882 587.5633 
## 
## Training set error measures:
##                     ME     RMSE      MAE       MPE     MAPE      MASE
## Training set -31.23064 200.1509 153.4345 -3.528352 10.14288 0.3832413
##                   ACF1
## Training set 0.2561221
  1. Finally, with these key variables for the EOQ model we can simply calculate the average demand during lead time, the Reorder level and the safety stock. We follow the formulas below.

Lead Time:

\[L = .214\]

Service Level:

\[z = 2.05\]

Average demand during lead time:

\[\bar{x}_L = \bar{x}_d * L\]

Safety Stock:

\[z*\sigma_d*\sqrt{L}\]

Reorder Level:

\[\bar{x}_L+z*\sigma_d*\sqrt{L}\]

Month.Yr Daily.Demand SigmaADemand Av.Demand.During.Lead.Time Safety.Stock Reorder.Point
January 2016 101 22 504 100 605
February 2016 107 22 534 100 633
March 2016 95 26 476 118 594
April 2016 117 22 587 101 687
May 2016 119 23 593 105 698
June 2016 108 23 541 106 647
July 2016 142 18 712 81 793
August 2016 143 20 714 90 804
September 2016 138 20 690 90 780
October 2016 163 16 817 74 890
November 2016 139 18 696 84 780
December 2016 141 17 707 80 787
January 2017 114 NA 568 NA NA

Product B

SS02CLR7284

We follow the same process for this product with one difference. The best forecast is given with an additive amoothing model which means that the seasonal factor is added to the forecast. Therefore for our purposes in adjusting standard deviation, \(\sigma_d\), we first divide the seasonal factor by the unadjusted forecast. Then to calculate the average monthly sigma of demand, we multiply the forecasted year’s standard deviation (which has been divided by the nuber of business days) by the quotient and add it back to to the demand sigma.

\[\sigma_d=\sigma_\frac{\hat{y_1...n}}{21}+s_i\]

## ETS(M,N,A) 
## 
## Call:
##  ets(y = variableB, model = "MNA") 
## 
##   Smoothing parameters:
##     alpha = 0.2467 
##     gamma = 2e-04 
## 
##   Initial states:
##     l = 873.9267 
##     s=-347.6457 -60.7358 366.6139 281.4915 302.0772 282.2595
##            209.1096 153.1011 -53.1247 -328.9994 -410.2999 -393.8475
## 
##   sigma:  0.1271
## 
##      AIC     AICc      BIC 
## 503.0580 525.9151 527.2217 
## 
## Training set error measures:
##                     ME     RMSE      MAE      MPE     MAPE      MASE
## Training set -4.858443 96.68681 73.33991 -2.29813 9.974028 0.5105814
##                    ACF1
## Training set 0.07885316

Below is the inventory management tool for product B

Month.Yr SeasonIndexB B.Actual Point.Forecast Daily.B.Demand SigmaBDemand Av.B.Demand.During.Lead.Time B.Safety.Stock B.Reorder.Point
January 2016 0 512 436 21 8 104 35 139
February 2016 -1 471 419 20 7 100 30 130
March 2016 0 744 501 24 9 119 39 158
April 2016 0 789 776 37 14 185 63 248
May 2016 0 943 983 47 17 234 78 312
June 2016 0 1341 1039 49 20 247 92 339
July 2016 0 973 1112 53 19 265 89 354
August 2016 0 1255 1132 54 20 269 92 361
September 2016 0 1146 1111 53 21 265 95 359
October 2016 0 1045 1196 57 21 285 95 380
November 2016 0 NA 769 37 13 183 59 242
December 2016 0 NA 482 23 9 115 43 158
January 2017 0 NA 436 21 8 104 35 139

Below is a comparison of the actual vs. the forecasted demand for 2016

Month.Yr B.Actual Point.Forecast B.Actual.vs.Forecast
January 2016 512 436 76
February 2016 471 419 52
March 2016 744 501 243
April 2016 789 776 13
May 2016 943 983 -40
June 2016 1341 1039 302
July 2016 973 1112 -139
August 2016 1255 1132 123
September 2016 1146 1111 35
October 2016 1045 1196 -151
November 2016 NA 769 NA
December 2016 NA 482 NA
January 2017 NA 436 NA

This table shows the forecast looking forward

Point.Forecast Lo.80 Hi.80 Lo.95 Hi.95
Nov 2016 754 631 877 566 942
Dec 2016 559 464 653 414 703
Jan 2017 420 345 495 305 534
Feb 2017 399 325 472 287 511
Mar 2017 551 454 648 403 699
Apr 2017 772 640 905 570 975
May 2017 977 811 1143 723 1231
Jun 2017 1154 957 1350 853 1454
Jul 2017 1119 924 1314 821 1417
Aug 2017 1137 936 1338 830 1445
Sep 2017 1182 971 1393 859 1505
Oct 2017 1185 970 1401 856 1514
Nov 2017 754 594 914 509 999
Dec 2017 559 419 698 345 772
Jan 2018 420 292 547 225 614
Feb 2018 399 272 525 206 592
Mar 2018 551 410 693 335 767
Apr 2018 772 605 940 516 1029
May 2018 977 781 1172 678 1276
Jun 2018 1154 932 1375 814 1493
Jul 2018 1119 898 1339 782 1456
Aug 2018 1137 911 1363 792 1483
Sep 2018 1182 947 1417 822 1541
Oct 2018 1185 947 1424 820 1550

\[\sigma_d=\sigma_\frac{\hat{y_1...n}}{21}+s_i\]

\[\sigma_d=\sigma_\frac{\hat{y_1...n}}{21}*s_i\]