# 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

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.

## 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.

## 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.

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.

You will get the following data.

Step 6

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

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

The following graphs appears

## 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 andR^{2}.

{` 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

{` 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.

## Excel Econometrics Tutorials

**Multiple Regression Analysis**

**Estimation and Inference****Estimation, Inference & Prediction****Model Formation: Checking Significance of Independent Variables**

**Time Series Analysis**

**Exponential Smoothing Forecasting****Plotting Linear Trend and Forecasting****Monthly Data De-Trending, Seasonal Index & Forecasting****Linear, Exponential and Quadratic Trend****Excel Tutorial for Normal Distributions**

**Data Analysis**