How to Calculate Trend Adjusted Exponential Smoothing in Excel

We know that using a unique weighting formula that prioritizes the most relevant time periods, exponential smoothing calculates a trend equation via the information. With Microsoft Excel, we can perform perfectly while computing exponential smoothing. Excel has some amazing tools and add-ins. In this article, we will demonstrate the procedures to calculate trend adjusted exponential smoothing in Excel.

What Is Exponential Smoothing in Excel?

Excel has an integrated smoothing approach which is Exponential Smoothing. We use this for trend projection, data smoothing, and forecasting. For the purpose of making informed judgments, exponential smoothing is utilized to anticipate the company volume. This method ‘Smooths’ the information by reducing the number of explanatory variables.

Where Can We Get Exponential Smoothing in Excel?

To access the exponential smoothing tool, we need to enable the Data Analysis tab on the Excel ribbon. The Data Analysis feature gives the ability to think critically regarding the information in normal language rather than needing to create laborious algorithms. To enable the Data Analysis tab, we have to follow the instructions down.

STEPS:

Firstly, go to the File tab from the ribbon.

This will take you to the backstage menu on the Excel application.
Secondly, scroll down a bit, and click on the Options menu.

Excel Options dialog box will appear.
Next, go to Add-ins and select Analysis ToolPak in the Add-ins group.
Further, choose the Excel Add-ins option from the Manage drop-down menu.
Then, click on the Go button.

Thus, the Add-ins dialog box will show up.
Consequently, check the box Analysis ToolPak.
And, then, click on OK to complete the procedure.

Finally, when you go to the Excel ribbon again and go to the Data tab you will see the Data Analysis feature is now enabled in the Analysis category.

Calculate Trend Adjusted Exponential Smoothing in Excel: Step-by-Step Procedures

An Adjusted Exponential Smoothing is a technique that creates predictions from quantifiable past information samples by computing a weighted average of the original value and projection for the recent period and adding a trend adjustment. Let’s follow the instructions to calculate trend-adjusted exponential smoothing in Excel.

Step 1: Insert Important Information in Excel

In the first place, we have to insert all the necessary information.

Firstly, we put the periods in column B. And we insert 10 periods of time to compute the exponential smoothing.
Next, insert the demand for each period.
Then, input the Smoothing Constant which is Alpha (α). In our case, the Alpha value is 20% or .02.
After that, put the Trend Factor which is Beta (β). In this instance, the Beta value is 30%, or .03.

Step 2: Calculate Exponential Smoothing Forecast

Excel’s exponential smoothing feature is quite intuitive and straightforward. We can easily perform with a tool and also with an equation or formula. First, we will see finding exponential smoothing with the Excel Data Analysis tool.

To begin with, go to the Data tab from the ribbon.
Then, click on the Data Analysis feature under the Analysis category.

As a consequence, this will display the Data Analysis dialog box.
Scroll down a bit and select Exponential Smoothing.
Afterward, click OK.

Hence, the Exponential Smoothing dialog will open up.
Now, place the Input Range and Damping factor in the Input part. In our case, we take the range of Demand ($C$5:$C$14) and put .02 as our damping factor.
Further, place the Output Range in Output options where we want to see the result of our exponential smoothing.
Furthermore, if you wish to see the output in a chart also, check the box of Chart Output.
Lastly, click on the OK button to finish the strategy.

Therefore, we can see that the Exponential Smoothing result is shown in our selected output range and the chart is visible.
However, there is a problem with using this as the first-period exponential smoothing shows the #N/A error. Therefore, while calculating the trend we will get a #N/A error in all the trend values.

Accordingly, it’s better to use the formula of exponential smoothing. But there is also a slight issue in that we didn’t get the accurate result of exponential smoothing, but the result will be quite similar to the accurate one. Let’s look at the instruction to calculate exponential smoothing with a formula.

First, we take the demand value as our first-period exponential smoothing. So, we select cell D5 and put the simple formula into that cell.

=C5

Press Enter.

Next, select the second cell D6, and put the formula into that selected cell.

=C5*$D$16+(1-$D$16)*D5

Hit the Enter key to complete the process.

Now, drag the Fill Handle down to duplicate the formula over the range. Or, to AutoFill the range, double-click on the plus (+) symbol.

Finally, you can see the result of Exponential Smoothing.

Further, we want to decrease the decimals. For this, go to the Home tab on the ribbon.
Then, click on Decrease Decimal on the Number group.

That’s it! We can see the exponential smoothing result.

Step 3: Compute Trend

Now, we will calculate the Trend. A Trend is a structure in the information that demonstrates that a sequence moves through periods to comparatively usually greater values.

Select cell E6 and insert the formula to calculate the trend in that cell.

=$D$17*(D6-D5)+(1-$D$17)*E5

Hit the Enter key on your keyboard.
The result will now display in the selected cell, along with the formula in the formula bar.

Further, to copy the formula over the range, drag the Fill Handle down. Or, double-click on the plus (+) sign to AutoFill the range.

We put 0 in the first-period trend. Other periods’ trend is shown by using the formula.

Step 4: Find Adjusted Exponential Smoothing

Now, we will calculate the adjusted exponential smoothing by adding the exponential smoothing and trend.

In the first place, select the cell where you want to see the result. We keep the first-period adjusted exponential smoothing cell blank. So we select the second-period cell.
Put the formula of addition into cell F6.

=D6+E6

Then, press the Enter key on your keyboard.
Now, you will be able to see the result in the selected cell and the formula will appear in the formula bar.

Further, to copy the formula over the range, drag the Fill Handle down. Alternatively, double-click on the plus (+) sign to AutoFill the range.

Lastly, we put the number as the same as the demand and exponential smoothing in our first period of adjusted exponential smoothing.
And, that’s it! By just following the above steps we can calculate the adjusted exponential smoothing in Excel.

Step 5: Insert Chart

Finally, to visualize the expense more often, we can insert a Chart. Charts are a popular tool for graphically illuminating data connections. We use charts to convey data that are too many or complex to be fully expressed in the text while taking up less space.

Firstly, select the data which we need while inserting the chart. In our case, we select the Period, Demand, Exponential Smoothing, and Adjusted Exponential Smoothing.

Next, go to the Insert tab from the ribbon.
After that, click on Insert Scatter (X, Y) or Bubble Chart drop-down menu under the Charts group.
Choose Scatter with Straight Lines and Markers which is the second-row first option of the Scatter.

This will create the chart to visualize the calculation of trend-adjusted exponential smoothing more often.

Final Output of Trend-Adjusted Exponential Smoothing

This is the final output of trend-adjusted exponential smoothing with the chart.

Things to Keep in Mind

Peaks and dips in the information are rounded out as the dumping factor increases in value.
Excel Exponential Smoothing is a really adaptable and simple-to-calculate approach.
The damping factor is greater; the Alpha value is less. As a consequence, the peaks and dips are rounded off most.
The damping factor is less and the Alpha value is larger. The smoothing results are hence more similar to the real sample points.

Download Practice Workbook

You can download the workbook and practice with them.

Calculate Trend Adjusted Exponential Smoothing.xlsx

Conclusion

The above procedures will assist you to calculate trend-adjusted exponential smoothing in Excel. Hope this will help you! Please let us know in the comment section if you have any questions, suggestions, or feedback.

<< Go Back to Exponential Smoothing in Excel | Solver in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!