Excel Exponential Smoothing (Table of Contents)
- Exponential Smoothing in Excel
- Where is the Exponential Smoothing 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 is the Exponential Smoothing 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 the below steps:
- Go to the FILE tab. Click on the OPTIONS tab in the left pane window. Refer to the below screenshot.
- It will open a dialog box for Excel Options. Click on the Add-Ins tab, as shown in the below screenshot.
- It will again display some options.
- Select the Excel Add-Ins options under Manage Box and click on the 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 the Analysis Toolpak checkbox and then click on OK, as shown in the below screenshot.
- The 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 a 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 the Data Analysis option under the Analysis section, as shown in the below screenshot.
- It will open a dialog box Data Analysis options.
- Click on the Exponential Smoothing option from the list of options and then click on OK as shown below.
- A dialog box appears for the Exponential Smoothing method.
- Under the Input Range box select, the Price values range from C4:C15.
- In the Damping Factor box, enter the value 0.9. This 0.9 is called the damping factor, which is equal to the 1- α. Here α (alpha) is the smoothing factor.
- Under the Output Range box, select the cell where you want to see the result. Refer to the 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 the Exponential Smoothing chart, as shown in the 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 the 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 the 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 the 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 the 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 Exponential Smoothing in Excel along with excel examples and a downloadable excel template. You may also look at these useful charts in excel –
