Steco Invetory - Case Study

STECO is IT equipment devices provider. One of his main products is stainless steel struts. Recently, the stock of struts in STECO is very high that leads to the inventory cost increased. To effectively reduce the inventory stock, one person who is deeply concerned about the use of some simple forecasting models to predict the inventory in the next month demand is Victor Kowalski, the new vice president of operations of STECO. Since he is responsible for the inventory of thousands of items, simple (i.e., inexpensive) forecasting models are important to him. In order to become familiar with the various models, he decides to “try out” different models on some historical data. In particular he decides to use last year’s monthly sales data (STRUT.xls) as the monthly demand for stainless steel struts to learn about the different models and to see how well they would have worked if STECO had been using the models last year. He is performing what is called a validation study.

1. Simple n-Period Moving Average. Using a 3-period and a 4-period moving average to STECO’s strut sales data forecast the sale quantity with the MAD and MAPE to measure the forecasting performance.

Month Actual Sales 3month MA Fcst Absolute Error 4month MA Fcst Absolute Error

January 20

February 24

March 27

April 31 23,667 7,333

May 37 27,333 9,667 25,5 11,5

June 47 31,667 15,333 29,75 17,25

July 53 38,333 14,667 35,5 17,5

August 62 45,667 16,333 42 20

September 54 54,000 0,000 49,75 4,25

October 36 56,333 20,333 54 18

November 32 50,667 18,667 51,25 19,25

December 29 40,667 11,667 46 17

Sum = 114 Sum = 124,75

MAD = 12,667 MAD = 15,594

2. Weighted n-Period Moving Average. Using the 3-month weighted moving average with initial weights 3/6, 2/6, 1/6 to the historical stainless strut data forecasts the monthly demand with the MAD as the performance measure. Comparing MAD with the results of optimal three-month weighted moving average (use Solver).

a. Before using Solver

Alpha2 = 0,167 Month Actual Sales 3month WMA Fcst Absolute Error

Alpha1 = 0,333 January 20

Alpha0 = 0,500 February 24

SUM OF WTS = 1 March 27

April 31 24,833 6,167

May 37 28,500 8,500

June 47 33,333 13,667

July 53 41,000 12,000

August 62 48,333 13,667

September 54 56,5 2,5

October 36 56,500 20,500

November 32 46,333 14,333

December 29 37,000 8,000

Sum = 99,33

MAD = 11,04

b. After using Solver

Alpha2 = 0 Month Actual Sales 3month WMA Fcst Absolute Error

Alpha1 = 0 January 20

Alpha0 = 1,000 February 24

SUM OF WTS= 1 March 27

April 31 27,000 4,000

May 37 31,000 6,000

June 47 37,000 10,000

July 53 47,000 6,000

August 62 53,000 9,000

September 54 62,000 8,000

October 36 54,000 18,000

November 32 36,000 4,000

December 29 32,000 3,000

Sum = 68,00

MAD = 7,56

3. Exponential Smoothing: using an initial value for alpha = 0.5 to forecast the monthly sale with MAD to measure the forecasting performance. Comparing MAD with the results