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 Handleto 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 theExcel 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.
- To add constraints, click Add.
- 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.
Things to Remember
- Activate the solver add-in.
Download Workbook
Download the workbook and practice.
Holt-Winters Method.xlsx
Related Articles
- How to Calculate Trend Adjusted Exponential Smoothing in Excel
- How to Remove Noise from Data in Excel
- How to Smooth Data in Excel
<< Go Back toExponential Smoothing in Excel | Solver in Excel|Learn Excel
Get FREE Advanced Excel Exercises with Solutions!