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.

• 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:

• 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,

• 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

• 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.

• 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

• 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

• 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.

• 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.

• 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.

• 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.

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 to Exponential Smoothing in Excel | Solver in Excel | Learn Excel