If you are searching for a solution or some special tricks to do Exponential Smoothing in Excel. Then you have landed in the right place. There is a quick way to do Exponential Smoothing in Excel. This article will show you each and every step with proper illustrations, so you can easily apply them for your purpose. Let’s get into the main part of the article.
Have a look at the overview image of this article.
What Is Exponential Smoothing?
Exponential smoothing is a time series forecasting method where newer data is given higher priority and old data is given low priority. Priority is determined by a weight factor. The past observations are weighted by an exponential decrease. There are three types of exponential smoothing:
- Single Exponential Smoothing: There is no trend or seasonality. And only one parameter which is the smoothing factor (α).
- Double Exponential Smoothing: It includes trends in the time series forecasting.
- Triple Exponential Smoothing: It includes both trends and seasonality. It is also named as Holt-Winters Exponential Smoothing.
What Is Damping Factor?
You have to use a smoothing coefficient between 0 and 1 which is known as a smoothing coefficient (α). And damping coefficient is the value of 1 minus alpha (α). So, the smaller the damping factor is, the higher the alpha level. A lower alpha level smooths the peak and valley points of the graph and a lower damping factor smooths the values close to the actual points.
Suppose, you have a dataset containing the actual sales for months and you want to forecast sales for the next months using the exponential smoothing method. In this section, I will show you the quick steps to do Exponential Smoothing in Excel on the Windows operating system. You will find detailed explanations of methods and formulas here. I have used the Microsoft 365 version here. But you can use any other versions as of your availability. If any methods won’t work in your version then leave us a comment.
1. Using Data Analysis Tool to Do Exponential Smoothing in Excel
Step 1: Enabling Data Analysis ToolPak Add-In
In Excel, Data Analysis ToolPak is not enabled by default so you have to enable it manually. Follow the steps below to enable the Data Analysis ToolPak.
- At first, open any workbook in Excel and Go to File Tab >> Options.
- Then, you will see a window named Excel Options will open.
- Go to the Add-ins options.
- Here, select Excel Add-ins in the Manage tools and press Go.
- Now, mark the Analysis ToolPak box and press OK.
Step 2: Selecting Exponential Smoothing in the Data Analysis Dialog
- Now, you have to go to the Data Tab.
- Select the Data Analysis option in the Analyze portion.
- After clicking on the Data Analysis option, a window named Data Analysis will appear.
- Then, select Exponential Smoothing from the Analysis tools.
- And, press OK.
Step 3: Inserting Proper Inputs in Exponential Smoothing Window and Apply
- After selecting Exponential Smoothing, a window will appear.
- Select the cells in the Actual Sales column as the Input Range.
- Insert the Damping Factor as 0.9 if the Alpha value is 0.1.
- Then, select the first cell of Forecast column F5 as Output Range.
- Finally, press OK.
- As a result, after clicking the OK, you will see the Forecast column will be filled.
- You can follow a similar way to create Time Series Forecasting with an Alpha value = 0.3. For this, just insert (1-0.3)= 0.7 in the Damping factor box.
Step 4: Inserting a Graph to Compare
You can make charts to compare the actual and forecasted data with different smoothing factors. For this,
- Select range D4:D16Â and F4:G16 simultaneously >> go to Insert tab >> Select Line or Area Chart.
- Tap on the Line chart in 2-D Line.
- Thus, we obtain the chart showing the differences.
2. Using Excel Solver Add-in to Do Exponential Smoothing
Initially, the first forecast value will be equal to the actual value. So, type the following formula in D6,
=C5Afterward, input the following formula in D7,
=$C$14*C6+$C$15
Write the ABS function in E6,
=ABS(C6-D6)
Later, type the basic formula in F6,
=E6^2
Finally, type the following formula in G6 to find the Absolute Percent Errors.
=E6/C6*100
Lastly, in E14, enter the following formula >> copy the cell by dragging it to G14.
=AVERAGE(E6:E13)Hence, we obtain the Mean Absolute Deviation, Mean Squared Error, and Mean Absolute Percent Error in E14, F14, and G14 respectively.
- Mean Absolute Deviation is the sum of Absolute Forecast Errors per n. Mean Square Error is the sum of the Square of absolute forecast per (n-1).
- The Mean Absolute Percentage Error (MAPE) is a statistical metric used to assess the accuracy of a forecasting method in terms of predictions.
Go to File >> Options.
Click on Add-ins >> Go.
Tap on Solver Add-in in Add-ins dialog >> OK.
Thus, we add Solver Add-in. Go to Data tab >> Solver.
In Solver Parameters, put E14 as the value of MAD in Set Objective >> Check Min option >> put C14 in By Changing Variable Cells >> add C14 <=1 in Add box >> Solve.
Thus, the value of alpha changes to 1 in C14. That means, that to get the smallest MAD possible, we should set the alpha to 1.
Next, put F14 as the value of MSE in Set Objective >> Check Min option >> put C14 in By Changing Variable Cells >> add C14 <=1 in Add box >> Solve.
Hence, we will find the changes in C14. Therefore, we should set the alpha value to 0.94136709 to get the smallest value possible for MSE.
Finally, click the Solver tool again, and in the Solver Parameters, put G14 in Set Objective >> check Min box >> C14 in By Changing Variable Cells >> tap Add and write C14 <= 1 >> Solve.
Thus, the value of alpha changes to 1. This shows, that to obtain the minimum value for MAPE, set the alpha value to 1.
Mean Absolute Percent Error gives all errors equal weightage while Mean Square Error weights errors according to their squares.
How to Forecast with Exponential Smoothing in Excel
1. Applying FORECAST Function
To predict the sales value, type the following the FORECAST function in D10,
=FORECAST.ETS(C10,$D$5:$D$9,$C$5:$C$9,1,1)
Later, drag the cell down to D16 to get the other forecasts.
2. Using Forecast Sheet Feature
To use the Forecast Sheet feature, select the range of data table >> go to Data tab >> Forecast Sheet.
Put 12 in Forecast End >> Create.
Thus, the predicted value appears with a forecast chart.
Things to Remember
- The damping factor is the subtraction of the Alpha value from 1.
- With the higher value of alpha, the curve fluctuates highly with data.
- When you take input data with headings, you have to tick the Label in the Exponential Smoothing.
Frequently Asked Questions
- In Simple Exponential Smoothing can we take alpha > 1? If not then why?
If alpha > 1 then 1-alpha < 0 and so the predicted value at time t has a negative impact on the predicted value at time t+1, which is counter-intuitive.
- Can I get a negative result in the forecast by using Holts linear method?
It depends on the data values, but yes. You can obtain a negative result.
Download Practice Workbook
You can download the practice workbook from here:
Conclusion
In this article, you have learned how to do Exponential smoothing in Excel. I hope you found this article helpful. Please, drop comments, suggestions, or queries if you have any in the comment section below.
