Perform Holt-Winters Exponential Smoothing in Excel (with Easy Steps)

Excel is the most widely used tool when it comes to dealing with huge datasets. We can perform myriads of tasks of multiple dimensions in Excel. In this article, I will explain how to apply Holt-Winters exponential smoothing in Excel. This method is useful for forecasting.

Download Workbook

Download this workbook and practice while going through this article.

Introduction to Holt-Winters Exponential Smoothing

Holt-Winters method is an advanced method to forecast values. It considers seasonality, and trend effects while predicting the forecast. That is why the values represent the actual values except for some randomness.

The formula to calculate the forecast value using Holt-Winters Exponential Smoothing in Excel is

Ft+k = (Lt+k*Tt)*St-m+k

Where, F = Forecasted Value
L = Level
T = Trend
M = 4 for the quarterly period, 12 for the monthly period
S = Seasonality Index

11 Easy Steps to Perform Holt Winters Exponential Smoothing in Excel

This is the dataset for today’s article. We have quarterly sales up to 2022.

We will calculate the forecasted values for 2023.

Step 1: Assign Random Alpha, Beta & Gamma Value

The first task is to assign some random values for the constants alpha, beta, and gamma.

We will later optimize these values.

Step 2: Calculate Initial Seasonal Index

After that, we will determine the initial seasonal index for the first 4 quarters. We will determine the initial seasonal indexes by dividing the sales of each quarter by the average sale of the first 4 quarters. We will use the AVERAGE function to do so.

• Go to F11 and write down the following formula.

• Now, press ENTER. Excel will return the output.

• After that, use the Fill Handle to AutoFill up to F14.

Step 3: Determine Initial Level and Trend

It’s time to calculate the initial level and trend for the dataset.

The initial level is the level for the 5th quarter since there are 4 quarters in a year.

The formula for the initial Level is,

L5 = Y5/S1

Where, Y5= Sales for the 5th Quarter.

S1= Seasonal Index for 1st Quarter.

To determine it,

• Go to D15 and write down the formula

• Now, press ENTER. Excel will return the output.

This time I will calculate the initial trend (which is also for the 5th quarter). The formula for the initial trend is,

T5 = L5-Y4/S4

Where, L5 = Level for 5th Quarter.

Y4 = Sales for the 4th Quarter.

S4 = Seasonal Index for 4th Quarter.

To calculate it,

• Go to E15 and write down the formula,

• Press ENTER to get the output.

Step 4: Calculate Next Seasonal Indexes

Now, we will calculate the next seasonal indexes using our general formula. The general formula to calculate the seasonal index is,

St = ɣ(Yt/Lt)+(1-ɣ)St-m

Where

L = Level.
T = Trend.
M = 4 for the quarterly period, 12 for a monthly period.
S = Seasonality Index.
Ɣ = Coefficient.
To calculate the seasonal index,

• Go to F15 and write down the following formula

• Press ENTER.

• AutoFill up to F22.

Note: Ignore the error for a while, it will be fine once you measure the next Levels and next Trends.

Step 5: Determine Next Levels

Now I will show how to determine the next levels using the formula,

Lt = α(Yt/St-m)+(1-α)(Lt-1+Tt-1)

Where

L = Level
T = Trend
M = 4 for quarterly period, 12 for a monthly period
S = Seasonality Index
α = Coefficient

• Go to D16 and write down the following formula

• Press ENTER.

• AutoFill up to D22.

Step 6: Measure Next Trends

Let’s get the idea of the trend effect now. The formula is

Tt= β(Lt-Lt-1)+(1-β)Tt-1

Where

L = Level
T = Trend
M = 4 for quarterly period, 12 for a monthly period
S = Seasonality Index
β= Coefficient

To calculate the trend effect,

• Go to E6 and write down the following formula

• Press ENTER to continue.

• AutoFill up to E22.

Step 7: Find Forecasted Values to Compare with Actual Sales

Now, we will calculate the forecasted values for comparing with actual sales. The first one will be for quarter 6. The formula to calculate the forecasted values (for comparison) is,

Ft = (Lt-1 + Tt-1)* St-M

So let’s do it.

• Go to G16 and write down the following formula

• Press ENTER to go.

• AutoFill up to G22.

Step 8: Calculate Forecasting Errors

Now, we will calculate the forecasting error by subtracting the forecasted value from the actual sales.

• Go to H16 and write down the formula

• Then press ENTER.

• AutoFill up to H22.

Step 9: Assign K Value for Quarters to Be Forecasted

It’s time to calculate the forecast. But before that, we must understand the co-efficient k. It represents the future time for forecasting. In our case, we will calculate the forecast for the 4 quarters of 2023. And we have data available for 2022.

So, for the first quarter of 2023, the value of k will be 1, for the 2nd quarter, it will be 2, and so on.

Step 10: Calculate Forecasted Value

Now, we are ready to calculate our forecasted values. We will use the last available Level, Trend, and Seasonality to calculate them.

• Go to G23 and write down the following formula

• Then press ENTER to get the output.

• Now, AutoFill up to G25.

Step 11: Optimize Alpha, Beta, and Gamma

Now, to minimize the error, we will optimize the value of alpha, beta, and gamma. We will take the help of an Excel solver to do so.

• First, we need to calculate the root mean square error. To do so, go to C7 and write down the following formula

Formula Breakdown:

• COUNT(H15:H21) → Count the number of cells.
  • Output → 7
• SUMSQ(H15:H21) → Calculate the sum of the squares of H5:H11.
  • Output → 463493653301
• =SQRT(SUMSQ(H15:H21)/COUNT(H15:H21)) → Calculates the RMSE
• =SQRT(992.463493653301/7)
• =SQRT(141.780499093329)
  • Output → 9072

• Then, press ENTER.

• Now, go to the Data tab >> select Solver.

• Solver Parameters window will pop up. Since we want to minimize the error, our objective is to set RMSE to be minimum by changing the values of the coefficients.
• After that, to add constraints, click Add.

• Add Constraint window will appear. The constraints are 0<=α,४,ß<=1. So to add the first constraint, set the cell reference and value. (see image)

• Similarly, after adding the second constraint, your output will be like this. Then click Solve.

• Excel will minimize the error by optimizing alpha, beta, and gamma.

Things to Remember

• You have to activate the solver add-in.
• We are not concerned about the k value for calculating forecasts that we are going to compare with the available actual sales.

Conclusion

In this article, I have explained how to apply Holt-Winters exponential smoothing in Excel. I hope it helps everyone. Please comment if you have any queries.