Excel Exponential Smoothing (Table of Contents)
What is Exponential Smoothing in Excel?
Exponential Smoothing is one of the top 3 sales forecasting methods used in statistics filed. Exponential smoothing is more realistic forecasting method to get the better picture of the business.
- Exponential Smoothing logic will be the same as other forecasting methods but this method works on the basis of weighted averaging factors. The older the data the less weight or less priority it has and for fresher data or for relevant data it given more priority or weight.
- Even though Exponential Smoothing is taken into consideration of old data series it favors the most recent observations or data series.
Types of Exponential Smoothing in Excel
There are mainly 3 types of Exponential Smoothing available in Excel.
- Simple / Single Exponential Smoothing: In this type α (alpha) is close to the zero value. When α (alpha) is close to the zero means smoothing happening rate is very slow.
- Double Exponential Smoothing: This method is suitable for analyzing the data which shows more trend indicator.
- Triple Exponential Smoothing: This method is suitable for the data which shows more trend and also seasonality in the series.
Where to Find Exponential Smoothing in Excel?
Exponential Smoothing is part of many Data Analysis tool in excel. By default, it is not visible in excel. If your excel is not showing Data Analysis tool then follow our old articles to unhide Data Analysis Toolpak.
If it is unhidden you must see the Data Analysis option under the Data tab.
Click on the Data Analysis you will see many statistical techniques. In this article, we are going to concentrate on Exponential Smoothing.
How to do Exponential Smoothing in Excel?
Below are the examples to do Exponential Smoothing in Excel.
Exponential Smoothing Example #1 – Sales Forecast
We will see one simple data to do the sales forecast for next year. I have 10 years revenue data.
Using Exponential Smoothing we need to forecast the revenue.
Step 1: Click on the Data tab and Data Analysis.
Step 2: Select the Exponential Smoothing option.
Step 3: For Input Range specify the available data points. Our data range is B1:B11.
Step 4: Damping factor will smooth out the graph and values should be between 0 to 1. Technically it is 1 – α (alpha). I have mentioned 0.3 as the damping factor.
Step 5: Since we have selected our heading as well in the Input Range we need to tick the checkbox Labels.
Step 6: Now select the range where to display the output range. I have selected next column of the existing data i.e. C2.
Step 7: Now we need to tell whether we need a chart to represent the data graphically or not. It is always a good practice to represent the data graphically. So select Chart Output.
Step 8: We have completed all the fields. Click on Ok to get the results.
Explanation: We have set the Damping Factor to 0.3 and alpha becomes 0.7. This shows for recent values (recent years revenue values) has given a weight of 70% and for relatively old values has a weight of 30%.
A graph shows more or less the same trend in this method. Since there is no previous value for the year 2007 excel cannot calculate the smoothed value and smoothed value of the second data series is always equal to the first data point.
Exponential Smoothing Example #2 – Forecast Trend at Different Dumping Factors
We will the forecast trend at different dumping factors. For this example, I am using monthly sales trend data.
Enter these numbers to excel sheet vertically.
Step 1: Click on the Data tab and Data Analysis.
Step 2: Select the Exponential Smoothing option.
Step 3: For Input Range select the available previous revenue details. Damping factor is 0.1.
Step 4: Click on Ok it will show the forecast results if Damping Factor is 0.1
Now run Exponential Smoothing two more time by changing the damping factor to 0.5 and 0.9.
Dumping Factor @ 0.5
Dumping Factor @ 0.9
We have got all three different results.
Now we will draw a graph for it. Select the data and go to insert tab select line chart.
The chart should look like the below one. You can modify your chart as per your requirement, here in this graph I have changed the Chart Title and Line color.
Explanation
- Now carefully observe the graph “as the damping factor increases the line or data becomes smooth in the graph”.
- Damping Factor is 0.1 means Alpha is 0.9 so we have more volatility in the data.
- Damping Factor is 0.9 means Alpha is 0.1 so we have less volatility in the data.
Things to Remember About Exponential Smoothing in Excel
- Alpha value will be 1 – Dumping value and vice versa.
- As alpha value increases we can see more fluctuations in the data points.
- Labels need to be ticked if the input range includes headings.
- Ideal dumping values should be 0.2 to 0.3.
You can download this Exponential Smoothing Excel template here –Exponential Smoothing Excel Template
Recommended Articles
This has been a guide to Exponential Smoothing in Excel. Here we discuss how to do Exponential Smoothing in excel along with practical examples and downloadable excel template. You may learn more about excel from the following articles –
Leave a Reply