# Perform Holt-Winters Exponential Smoothing in Excel – 11 Steps

## Introduction to Holt-Winters Exponential Smoothing

The Holt-Winters method is an advanced method to forecast values. It considers seasonality, and trend effects while predicting the forecast.

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

This is the sample dataset.

you want to calculate the forecast values for 2023.

### Step 1 – Assign Random Alpha, Beta & Gamma Values

Assign random values to the constants alpha, beta, and gamma.

These values will be later optimized.

### Step 2 – Calculate the Initial Seasonal Index

Determine the initial seasonal indexes by dividing the sales of each quarter by the average sales of the first 4 quarters. Use the AVERAGE function.

• Go to F11 and enter the following formula.
`=C11/AVERAGE(\$C\$11:\$C\$14)`

• Press ENTER.
• This is the output.

• Drag down the Fill HandleÂ to F14.

### Step 3 – Determine the Initial Level and Trend

The initial level is the level of the 5th quarter .

The formula is:

L5 = Y5/S1

Y5= Sales of the 5th Quarter.

S1= Seasonal Index of the 1st Quarter.

• Go to D15 and enter the formula:
`=C15/F11`

• Press ENTER.
• This is the output.

Calculate the initial trend:

T5 = L5-Y4/S4

Where, L5 = Level for 5th Quarter.

Y4 = Sales for the 4th Quarter.

S4 = Seasonal Index for 4th Quarter.

• Go to E15 and enter the formula,
`=D15-C14/F14`

• Press ENTER.
• This is the output.

### Step 4 – Calculate the Next Seasonal Indexes

The general formula to calculate the seasonal index is:

St = É£(Yt/Lt)+(1-É£)St-m

L = Level.
T = Trend.
M = 4 for the quarterly period, and 12 for the monthly period.
S = Seasonality Index.
Æ” = Coefficient.

To calculate the seasonal index:

• Go to F15 and enter the following formula
`=\$C\$6*(C15/D15)+(1-\$C\$6)*F11`

• Press ENTER.

• Drag down the Fill Handle to see the result in the rest of the cells.

Note: Ignore the error (it will disappear once you measure the next Levels and next Trends).

### Step 5 – Determine the Next Levels

This is the formula.

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

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

• Go to D16 and enter the following formula.
`=\$C\$4*(C16/F12)+(1-\$C\$4)*(D15+E15)`

• Press ENTER.

• Drag down the Fill Handle to see the result in the rest of the cells.

### Step 6 – Measure the Next Trends

The formula is:

Tt= Î²(Lt-Lt-1)+(1-Î²)Tt-1

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

To calculate the trend effect:

• Go to E6 and enter the following formula
`=\$C\$5*(D16-D15)+(1-\$C\$5)*E15`

• Press ENTER.

• Drag down the Fill Handle to see the result in the rest of the cells..

### Step 7 – Find the Forecast Values to Compare with Actual Sales

The formula to calculate the forecast values (for comparison) is:

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

• Go to G16 and enter the following formula
`=(D16+E16)*F12`

• Press ENTER.

• Drag down the Fill Handle to see the result in the rest of the cells.

### Step 8 – Calculate Forecasting Errors

• Go to H16 and enter the formula.
`=C16-G16`

• Press ENTER.

• Drag down the Fill Handle to see the result in the rest of the cells.

### Step 9 – Assign K Value to Quarters to Be Forecast

The co-efficient k represents the future time to forecast. Here, you will calculate the forecast for the 4 quarters of 2023 with the available data for 2022.

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

### Step 10 – Calculate the Forecast Value

Use the last available Level, Trend, and Seasonality to calculate it.

• Go to G23 and enter the following formula.
`=(\$D\$22+F23*\$E\$22)*F19`

• Press ENTER to see the output.

• Drag down the Fill Handle to see the result in the rest of the cells.

### Step 11 – Optimize Alpha, Beta, and Gamma

To minimize errors, optimize the values of alpha, beta, and gamma, using theÂ Excel solver.

• Calculate the root mean square error: go to C7 and enter the following formula.
`=SQRT(SUMSQ(H15:H21)/COUNT(H15:H21))`

Formula Breakdown:

• COUNT(H15:H21) â†’ Counts the number of cells.
• Output â†’ 7
• SUMSQ(H15:H21) â†’ Calculatesthe 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
• Press ENTER.

• Go to the Data tab >> select Solver.

• In the Solver Parameters window, set RMSE to minimum by changing the values of the coefficients.

• In the Add Constraint window,Â set the cell reference and value.Â  The constraints are 0<=Î±,à¥ª,ÃŸ<=1

• Add the second constraint. Click Solve.

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

## Related Articles

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

Get FREE Advanced Excel Exercises with Solutions!
Akib Bin Rashid

AKIB BIN RASHID, a materials and metallurgical engineer, is passionate about delving into Excel and VBA programming. To him, programming is a valuable time-saving tool for managing data, files, and internet-related tasks. Proficient in MS Office, AutoCAD, Excel, and VBA, he goes beyond the fundamentals. Holding a B.Sc in Materials and Metallurgical Engineering from Bangladesh University of Engineering and Technology, MD AKIB has transitioned into a content development role. Specializing in creating technical content centred around Excel and... Read Full Bio

1. Thanks for Holt’s Winter excel spreadsheet. It helped a Lot. Are there more examples. I enjoyed the one you sent.

2. Thank you for your kind words.
Have a good day!!

3. Hola! Estoy intentando usar el pronÃ³stico para kilogramos de un artÃ­culo en doce meses, pero no logro que la cantidad de kilogramos del aÃ±o 2023 tenga sentido. El aÃ±o 2022 son 3000 kg y el aÃ±o 2023 1200, algo tengo mal… Por favor, Â¿podrÃ­a pasarme un ejemplo del excel para 12 meses? Gracias!
English: Hello! I’m trying to use the forecast for kilograms of an item in twelve months, but I can’t get the number of kilograms for the year 2023 to make sense. The year 2022 is 3000 kg and the year 2023 is 1200, I have something wrong… Please, could you give me an example of excel for 12 months? Thank you!

• Hi JORGE,
Prior to that, check the following Excel file. We have added a 12-month example for you. Let us know if this helps.
Solution.xlsx
Note:
You must have data of 2021 and 2022 for forecasting 2023.

4. Hi Akib,

I think G16 should be: =(D15+E15)*F12 instead of =(D16+E16)*F12, right?

Thank you.

Fahim Shahriyar Dipto Dec 20, 2022 at 5:27 PM

Hi Quang,
Thanks for your feedback. You are right on this occasion. The formula will be `=(D15+E15)*F12` in G16. unfortunately, we have made a mistake here. Thanks to you that you corrected us. We have updated the article.
Best wishes to you.
Regards
Fahim Shahriyar Dipto
Excel & Content Developer.

Advanced Excel Exercises with Solutions PDF