Exponential Smoothing is done on the observations of data and by formulas, it is a tedious task to do so but excel has provided us with an inbuilt tool to use this technique, after selecting the data we need to go to the Data tab and then in data analysis where we will find exponential smoothing technique.
What is Exponential Smoothing in Excel?
Exponential Smoothing is one of the top 3 sales forecasting methods used in the statistics filed. Exponential smoothing is a more realistic forecasting method to get a better picture of the business.
- Exponential Smoothing logic will be the same as other forecasting methodsForecasting MethodsTop forecasting methods include qualitative forecasting (Delphi method, market survey, executive opinion, sales force composite) and quantitative forecasting (time series and associative models).read more, 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 zero, it means the smoothing happening rate is very slow.
- Double Exponential Smoothing: This method is suitable for analyzing the data, which shows more trend indicators.
- 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 the 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 of doing 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 of revenue data.
Using Exponential Smoothing, we need to forecast the revenue.
- Click on the Data tab and Data Analysis.
- Select the Exponential Smoothing option.
- For the Input Range, specify the available data points. Our data range is B1:B11.
- 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.
- Since we have selected our heading as well in the Input Range, we need to tick the checkbox Labels.
- Now select the range where to display the output range. I have selected the next column of the existing data, i.e., C2.
- 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.
- 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 the 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 see a forecast trend at different dumping factors. For this example, I am using monthly sales trend data.
Enter these numbers into the 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. The 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 times 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 the insert tab select line chart.
The chart should look like the below one. You can modify your chart as per your requirement; 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.โ
- The damping Factor is 0.1 means Alpha is 0.9, so we have more volatility in the data.
- The 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
- The alpha value will be 1 โ Dumping value and vice versa.
- As the 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.
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 a downloadable excel template. You may learn more about excel from the following articles –
- Analysis ToolPak in Excel
- Descriptive Statistics in Excel
- PERCENTILE Function in Excel
- Excel QUARTILE Function – Examples
- NETWORKDAYS Excel Function
- 35+ Courses
- 120+ Hours
- Full Lifetime Access
- Certificate of Completion
47.1K subscribers