Excel Exponential Smoothing (Table of Contents)
- Exponential Smoothing in Excel
- Where the Exponential Smoothing is found in Excel?
- How to Use Exponential Smoothing in Excel?
Exponential Smoothing in Excel
Exponential Smoothing in Excel is an inbuilt smoothing method used for Forecasting, Smoothing the data, trend projection. To access, Exponential Smoothing in Excel, go to the Data menu tab and from the Data Analysis option choose Exponential Smoothing. Select the input range which we want to smooth and then choose the dumping factor which should be between 0 and 1 (1 – α) and then select the output range cell. This will smoothen the select input range number by the percentage of dumping factor we choose.
Where the Exponential Smoothing is found in Excel?
It is found under Analysis ToolPak in Excel. The Analysis ToolPak is a Microsoft Excel data analysis add-in. This add-in is not loaded automatically on excel. Before using this first, we need to load it.
Steps to load the Analysis ToolPak add-in:
We need to add this feature in Excel for analyzing business by using Excel Add-Ins. To add this feature in Excel follow below steps:
- Go to the FILE tab. Click on the OPTIONS tab in the left pane window. Refer below screenshot.
- It will open a dialog box for Excel Options. Click on Add-Ins tab as shown in below screenshot.
- It will again display some options.
- Select the Excel Add-Ins options under Manage Box and click on Go button as shown in the below screenshot. (However, Excel Add-Ins is by default selected)
- It will open an Add-Ins dialog box.
- Tick on Analysis Toolpak checkbox and then click on OK as shown in below screenshot.
- These above steps will add the Data Analysis section for statistical analysis under the DATA tab.
How to Use Exponential Smoothing in Excel?
Exponential Smoothing in Excel is very simple and easy to use. Let’s understand the working of forecasting Exponential Smoothing in Excel with some examples.
Exponential Smoothing Forecasting – Example #1
Below we have given month wise price list.
We have assigned the number to the month period. For Exponential Smoothing to this time series data, follow the below steps:
- Go to the Data tab. Click on Data Analysis option under Analysis section as shown in the below screenshot.
- It will open a dialog box Data Analysis options.
- Click on Exponential Smoothing option from the list of options and then click on OK as shown below.
- A dialog box appears for Exponential Smoothing method.
- Under Input Range box select the Price values range from C4:C15.
- In Damping Factor box enter the value 0.9. This 0.9 is called damping factor which is equal to the 1- α. Here α (alpha) is the smoothing factor.
- Under Output Range box, select the cell where you want to see the result. Refer below screenshot.
- Tick on Chart Output box for displaying the values in the chart and then Click on OK.
- It will insert the damping values in the E column with Exponential Smoothing chart as shown in below screenshot.
Explanation:
- Here α=0.1, the previous data point is given a relatively small weight whereas the previous smoothed value is given a large weight (0.9).
- The above graph is showing an increasing trend in the data points.
- The graph doesn’t calculate the smoothed value for the first data point because there is no data point before that.
Exponential Smoothing Forecasting – Example #2
Let’s consider α=0.2 for the above-given data values so enter the value 0.8 in Damping Factor box and again repeat the Exponential Smoothing method.
The result is shown below:
Exponential Smoothing Forecasting – Example #3
Let’s consider α=0.8 for the above-given data values so enter the value 0.2 in Damping Factor box and again repeat the Exponential Smoothing method.
The result is shown below:
Now if we compare the results of all the above 3 Excel Exponential Smoothing examples, then we can come up with below conclusion:
- The Alpha α value is smaller, the damping factor is higher. Resultant the more the peaks and valleys are smoothed out.
- The Alpha α value is higher, the damping factor is smaller. Resultant the smoothed values are closer to the actual data points.
Things to Remember
- The more value of dumping factor smooths out the peak and valleys in the dataset.
- Excel Exponential Smoothing is a very flexible method to use and easy in the calculation.
Recommended Articles
This has been a guide to Exponential Smoothing in Excel. Here we discuss how to use an Exponential Smoothing in Excel along with excel examples and downloadable excel template. You may also look at these useful charts in excel –
