HOME

Time Series Analysis: Exponential Smoothing Forecasting

The following data (stored in Treasury) represent the three-month Treasury bill rates in the United States from 1991 to 2008:

Year Rate Year Rate
1991 5.38 2000 5.82
1992 3.43 2001 3.40
1993 3.00 2002 1.61
1994 4.25 2003 1.01
1995 5.49 2004 1.37
1996 5.01 2005 3.15
1997 5.06 2006 4.73
1998 4.78 2007 4.36
1999 4.64 2008 1.37

A. Plot the data

Step 1

Copy and paste data on excel

exponential smoothing forecasting image 1

Step 2

Plot the line chart of the data by clicking on insert and click on the line chart icon. Select cells A1 to A19 for x axis values and cells B1 to B19 for y axis values.

exponential smoothing forecasting image 2

B. Fit a three-year moving average to the data and plot the results

Step 3

Select points the data points on the graph and right click. Click on the option add trend line. A dialogue box will appear select moving average and change the period to 3. Click ok.

exponential smoothing forecasting image 3
exponential smoothing forecasting image 4

C. Using a smoothing coefficient of W = 0.50, exponentially smooth the series and plot the results

Step 4

Click on data and select data analysis tool pack. A dialogue box opens. Select exponential smoothing from the dialogue box.

exponential smoothing forecasting image 5

Step 5

Select cells B2 to B19 as input range and enter the damping factor as 0.5 (damping factor= 1-smoothing factor (W)) and cell C2 as the starting cell for the output of the smoothing.

exponential smoothing forecasting image 6

You will get the following data.

exponential smoothing forecasting image 7

Step 6

Right click on the graph. A dialogue box appears click on select data. And click on Add series.

exponential smoothing forecasting image 8

The following dialogue box appear. Name the series as 0.5 smooth and for x values select cells A2 to A19 and for y values select cells from C2 to C19. Click ok

exponential smoothing forecasting image 9

The following graphs appears

exponential smoothing forecasting image 10

D. What is your exponentially smoothed forecast for 2009?

Step 7

Click on the data points of the graph and right click. Select add trend line. A trend line will appear. Select the trend line and right click. Click on format trend line and select display equation andR2.

exponential smoothing forecasting image 11
exponential smoothing forecasting image 12
exponential smoothing forecasting image 13

The forecast equation is 
Y= -0.1318*x + 5.3128
For x=19 (2009)
Y= -0.1318*19 + 5.3128
= 2.8086

E. Repeat (c) and (d), using a smoothing coefficient of W = 0.25

Repeat steps 4-7. Take damping factor as 0.75

After completing the exponential smoothing steps as we did above the following output will be obtained

exponential smoothing forecasting image 14
exponential smoothing forecasting image 15
The exponentially smoothed Forecast equation when it is smoothed with W=0.25 is
Y = -0.1285x + 5.5173
The exponential smoothing forecast for 2009 is
Y = -0.1285x + 5.5173
= 3.0758

F. Compare the results of (d) and (e)

Comparing the results of D and E we can see that the results with exponential smoothing W=0.5 are closer to the actual data points. It can also be noted that the exponential smoothen trend with lesser W will have lesser peaks and valleys and will be farther from the actual data points when compared with exponential smoothened trend with higher W.