Osman Goni Ridwan
If you are searching for the 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.
Download Practice Workbook
You can download the practice workbook from here:
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.
Steps to Do Exponential Smoothing in Excel
Suppose, you have a dataset containing the actual sales quantity of 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 Windows operating system. You will find detailed explanations of methods and formulas here. I have used 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.
📌 Step 1: Enable 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: Go to Data Analysis Option and Select Exponential Smoothing
- Now, you have to go to the Data Tab
- Select the Data Analysis option in the Analysis portion.
- After clicking on the Data Analysis option, a window named Data Analysis will appear.
- Then, select the Exponential Smoothing from the Analysis tools.
- And, press OK.
📌 Step 3: Insert Proper Inputs in Exponential Smoothing Window and Apply
- After selecting Exponential Smoothing, a window will appear.
- Select the cells in the Actual 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 E5 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 Alpha value = 0.3. For this, just insert (1-0.3)= 0.7 in the Damping factor box.
📌 Step 4: Make a Graph to Compare
You can make charts to compare the actual and forecasted data with different smoothing factors. For this,
- Select the columns Months, Actual, and Forecasts.
- Then, go to Insert tab >> Select any Chart type
Read More: How to Calculate Trend Adjusted Exponential Smoothing in Excel
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.
Exponential Smoothing in Excel: Knowledge Hub
- Perform Holt-Winters Exponential Smoothing
- Calculate Trend Adjusted Exponential Smoothing
- Smooth Data in Excel
- Remove Noise from Data
Conclusion
In this article, you have found how to do Exponential smoothing in Excel. I hope you found this article helpful. You can visit our website ExcelDemy to learn more Excel-related content. Please, drop comments, suggestions, or queries if you have any in the comment section below.
