Exponential smoothing in Excel

In this tutorial we will learn how to implement Exponential smoothing in Excel for a time series data.

What is Exponential smoothing?
Statistical smoothing technique for detecting significant changes in data by ignoring the fluctuations irrelevant to the purpose.
Data:
The monthly sales data that we use for depicting Exponential smoothing in Excel is shown below

Exponential smoothing in Excel 1

 

Step 1: First, let’s plot our time series, which is shown below.

Exponential smoothing in Excel 2

 

Step 2: On the top right corner of the data tab click data analysis.

Exponential smoothing in Excel 3

Note: if you can’t able to find the Data Analysis button? Click here to load the Analysis ToolPak add-in.

 

Step 3: Select Exponential smoothing and click ok.

Exponential smoothing in Excel 4

 

Step 4: select the input range and output range, then mention the damping factor. More the damping factor, the smoother the data will be.

Damping factor = 1-alpha.

Exponential smoothing in Excel 5

So the resultant table will be

Exponential smoothing in Excel 6a

When we plot the time series the resultant time series chart will be

Exponential smoothing in Excel 6b

 

Step 5: Now add two more smoothing in the same way as we seen above, with damping factor =0.5 (alpha=0.5) and damping factor=0.9 (alpha=0.1).

So the resultant table will be

Exponential smoothing in Excel 7

The resultant time series chart will be

Exponential smoothing in Excel 8

As you could clearly see when the damping factor increases (alpha value decreases) more smooth the data becomes. Alpha =0.1 have the least Fluctuation, whereas alpha=0.9 have most fluctuation.

previous-small-11                                                                                                                next_small-11