4211 Lab 3 Sample Procedure
BUS ML 4211
Sample Problem
Electric Can Opener shipments are as shown below:
MONTH TIME PERIOD ACTUAL VALUES 3 Month MA 5 Month MA
JAN 1 200
Feb 2 135
Mar 3 195
April 4 197.5
May 5 310
June 6 175
July 7 155
Aug 8 130
Sept 9 220
Oct 10 277.5
Nov 11 235
Dec 12
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
Sample Problem
Electric Can Opener shipments are as shown below:
MONTH TIME PERIOD ACTUAL VALUES 3 Month MA 5 Month MA
JAN 1 200
Feb 2 135
Mar 3 195
April 4 197.5
May 5 310
June 6 175
July 7 155
Aug 8 130
Sept 9 220
Oct 10 277.5
Nov 11 235
Dec 12
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