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