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
Step 1: First, let’s plot our time series, which is shown below.
Step 2: On the top right corner of the data tab click data analysis.
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.
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.
So the resultant table will be
When we plot the time series the resultant time series chart will be
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
The resultant time series chart will be
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.
