4211 Lab 3 Sample Procedure

Topics: Month, Moving average, Exponential smoothing Pages: 2 (253 words) Published: February 1, 2013
BUS ML 4211
Sample Problem
Electric Can Opener shipments are as shown below:
MONTHTIME PERIODACTUAL VALUES3 Month MA5 Month MA
JAN1200
Feb2135
Mar3195
April4197.5
May5310
June6175
July7155
Aug8130
Sept9220
Oct10277.5
Nov11235
Dec12

1. 3 Month Moving Averages: The forecast for April is average of Jan, Feb and Mar shipments, =(200+135+195)/3, enter D5=SUM(C2:C4)/3 in EXCEL file. Copy and paste this column. So, forecast for Dec. shipments is 244.17

2. Similarly, for Five Month Average will be E7=SUM(C2:C6)/5= 207.4; copy and paste the formula till the end. So, forecast for Dec is 203.50

3. EXPONENTIAL SMOOTHING: α=0.1
Ft+1 = α Xt + (1 - α ) Ft
Let the starting point forecast be Jan sales, F1=200. And X1=200 Forecast for Feb, F2=200. Actaul Sales, X2=135
F3 (forecast for March)= α *X2+(1- α)*F2=0.1*135+0.9*200
In EXCEL: For the cell, F3 enter =\$E\$16*C2+(1-\$E\$16)*F2 where \$E\$16 is value of 0.1, which is the smoothing constant, α.

So, the forecast for Dec is 205.56

The following shows the plot for Forecast by 3 MA, 5MA and Exponential Smoothing, 0.1

In last three columns in EXCEL file, you can see the computation for MSE and RMSE.