Applying Exponential Smoothing to Volatile Data in Excel

Exponential smoothing is a technique for “smoothing” out time series data and is often used for short-term forecasting. The basic idea is that time series data often has “random noise” associated with it, which leads to peaks and valleys in the data, but by applying exponential smoothing we can smooth out these peaks and valleys to see the true underlying trend of the data.

Procedure

To apply exponential smoothing to a time series in Excel, follow these steps:

1. Open your Excel spreadsheet and select the range of data values that you want to smooth.
2. Go to the Data tab, in the Analysis group, and click Data Analysis. If you don’t see this button, you need to first load the Excel Analysis ToolPak, which is a free add-in.
3. Select Exponential Smoothing and click OK.
4. In the Input Range box, enter or select the range of data values that you want to smooth.
5. In the Damping Factor box, enter or select the value that you want to use for the smoothing constant $\alpha$. The value (1 – $\alpha$) is called the damping factor. For example, if you want to use $\alpha =0.2$, then your damping factor will be 0.8.
6. In the Output Range box, enter or select the cell where you want the smoothed values to appear. It’s a good idea to choose this output range right next to your actual data values so you can easily compare them side by side.
7. If you want to see a chart displayed with the actual and the smoothed values, select the box that says Chart Output.
8. Click OK. A list of smoothed values and a chart (if selected) will automatically appear.

Example

Suppose we have the following dataset that shows the sales for a particular company for 10 sales periods:

We want to apply exponential smoothing to this time series data and see how the smoothed values compare to the actual values. We also want to use an alpha value of 0.3.

Perform the following steps to apply exponential smoothing to this time series data.

1. Open your Excel spreadsheet and select the range B2:B11 that contains the sales data.
2. Go to the Data tab, in the Analysis group, and click Data Analysis.
3. Select Exponential Smoothing and click OK.
4. In the Input Range box, enter or select B2:B11.
5. In the Damping Factor box, enter or select 0.7. This is equivalent to using $\alpha =0.3$.
6. In the Output Range box, enter or select C2. This will place the smoothed values next to the actual values.
7. Select the box that says Chart Output.
8. Click OK. A list of smoothed values and a chart will automatically appear.

The result is shown below:

Note that the first time period has a value of #N/A because there is no previous time period to use to calculate the smoothed value. The smoothed value for the second time period equals the previous data point.

We can see that the smoothed values are less volatile than the actual values and follow a general increasing trend. The smoothed values are also closer to the actual values when the alpha value is larger (0.3 in this case).

Other Approaches

Exponential smoothing is one of the simplest and most widely used methods for smoothing time series data and forecasting. However, it has some limitations and assumptions, such as:

• It assumes that the time series data has no trend or seasonality, which may not be realistic for some data sets.
• It only uses the previous data point and the previous smoothed value to calculate the current smoothed value, which may not capture the long-term patterns or cycles in the data.
• It requires choosing an appropriate value for the smoothing constant $\alpha$, which may not be easy or optimal for some data sets.

Therefore, depending on the characteristics and objectives of your data, you may want to consider other approaches for smoothing and forecasting, such as:

• Moving average: This method calculates the average of the last $n$ data points and uses it as the smoothed value for the current time period. It is simple and easy to implement, but it also assumes no trend or seasonality and gives equal weight to all data points, which may not be desirable for some data sets.
• Holt’s linear trend method: This method extends the exponential smoothing method by adding a second equation to capture the trend component of the data. It uses two smoothing constants, one for the level and one for the trend, and produces a linear forecast. It is more flexible and accurate than the simple exponential smoothing method, but it also assumes no seasonality and requires choosing two optimal values for the smoothing constants.
• Holt-Winters method: This method further extends the exponential smoothing method by adding a third equation to capture the seasonality component of the data. It uses three smoothing constants, one for the level, one for the trend, and one for the seasonality, and produces a seasonal forecast. It is the most comprehensive and sophisticated method among the three, but it also requires choosing three optimal values for the smoothing constants and knowing the length of the seasonal cycle.