Perform Holt-Winters Exponential Smoothing in Excel - 11 Steps - ExcelDemy (2024)

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.

Perform Holt-Winters Exponential Smoothing in Excel - 11 Steps - ExcelDemy (1)

you want to calculate the forecast values for 2023.

Perform Holt-Winters Exponential Smoothing in Excel - 11 Steps - ExcelDemy (2)

Step 1 – Assign Random Alpha, Beta & Gamma Values

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

Perform Holt-Winters Exponential Smoothing in Excel - 11 Steps - ExcelDemy (3)

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)

Perform Holt-Winters Exponential Smoothing in Excel - 11 Steps - ExcelDemy (4)

  • Press ENTER.
  • This is the output.

Perform Holt-Winters Exponential Smoothing in Excel - 11 Steps - ExcelDemy (5)

  • Drag down the Fill Handleto F14.

Perform Holt-Winters Exponential Smoothing in Excel - 11 Steps - ExcelDemy (6)

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

Perform Holt-Winters Exponential Smoothing in Excel - 11 Steps - ExcelDemy (7)

  • Press ENTER.
  • This is the output.

Perform Holt-Winters Exponential Smoothing in Excel - 11 Steps - ExcelDemy (8)

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

Perform Holt-Winters Exponential Smoothing in Excel - 11 Steps - ExcelDemy (9)

  • Press ENTER.
  • This is the output.

Perform Holt-Winters Exponential Smoothing in Excel - 11 Steps - ExcelDemy (10)

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

Perform Holt-Winters Exponential Smoothing in Excel - 11 Steps - ExcelDemy (11)

  • Press ENTER.

Perform Holt-Winters Exponential Smoothing in Excel - 11 Steps - ExcelDemy (12)

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

Perform Holt-Winters Exponential Smoothing in Excel - 11 Steps - ExcelDemy (13)

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)

Perform Holt-Winters Exponential Smoothing in Excel - 11 Steps - ExcelDemy (14)

  • Press ENTER.

Perform Holt-Winters Exponential Smoothing in Excel - 11 Steps - ExcelDemy (15)

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

Perform Holt-Winters Exponential Smoothing in Excel - 11 Steps - ExcelDemy (16)

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

Perform Holt-Winters Exponential Smoothing in Excel - 11 Steps - ExcelDemy (17)

  • Press ENTER.

Perform Holt-Winters Exponential Smoothing in Excel - 11 Steps - ExcelDemy (18)

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

Perform Holt-Winters Exponential Smoothing in Excel - 11 Steps - ExcelDemy (19)

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.

Perform Holt-Winters Exponential Smoothing in Excel - 11 Steps - ExcelDemy (20)

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

Perform Holt-Winters Exponential Smoothing in Excel - 11 Steps - ExcelDemy (21)

Step 8 – Calculate Forecasting Errors

  • Go to H16 and enter the formula.

=C16-G16

  • Press ENTER.

Perform Holt-Winters Exponential Smoothing in Excel - 11 Steps - ExcelDemy (22)

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

Perform Holt-Winters Exponential Smoothing in Excel - 11 Steps - ExcelDemy (23)

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.

Perform Holt-Winters Exponential Smoothing in Excel - 11 Steps - ExcelDemy (24)

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

Perform Holt-Winters Exponential Smoothing in Excel - 11 Steps - ExcelDemy (25)

  • Press ENTER to see the output.

Perform Holt-Winters Exponential Smoothing in Excel - 11 Steps - ExcelDemy (26)

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

Perform Holt-Winters Exponential Smoothing in Excel - 11 Steps - ExcelDemy (27)

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))

Perform Holt-Winters Exponential Smoothing in Excel - 11 Steps - ExcelDemy (28)

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.

Perform Holt-Winters Exponential Smoothing in Excel - 11 Steps - ExcelDemy (29)

  • Go to the Data tab >> select Solver.

Perform Holt-Winters Exponential Smoothing in Excel - 11 Steps - ExcelDemy (30)

  • In the Solver Parameters window, set RMSE to minimum by changing the values of the coefficients.
  • To add constraints, click Add.

Perform Holt-Winters Exponential Smoothing in Excel - 11 Steps - ExcelDemy (31)

  • In the Add Constraint window,set the cell reference and value. The constraints are 0<=α,४,ß<=1

Perform Holt-Winters Exponential Smoothing in Excel - 11 Steps - ExcelDemy (32)

  • Add the second constraint. Click Solve.

Perform Holt-Winters Exponential Smoothing in Excel - 11 Steps - ExcelDemy (33)

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

Perform Holt-Winters Exponential Smoothing in Excel - 11 Steps - ExcelDemy (34)

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!
Perform Holt-Winters Exponential Smoothing in Excel - 11 Steps - ExcelDemy (2024)

FAQs

What is the formula for Holt-Winters exponential smoothing? ›

Holt and Winters extended Holt's method to capture seasonality. st = γ(yt − `t−1 − bt−1)+(1 − γ)st−m, k = integer part of (h − 1)/m. Ensures estimates from the final year are used for forecasting.

What is the additive Holt winter method in Excel? ›

Basic Concepts
  1. The additive Holt-Winters model is identical to the multiplicative model, except that seasonality is considered to be additive. ...
  2. The recursive approach to the additive model is.
  3. where 0 ≤ α ≤ 1, 0 ≤ β ≤ 1 and 0 ≤ γ ≤ 1.
  4. The predictions for the data elements yi is given by.

What is Holt-Winters vs exponential smoothing? ›

The Holt-Winters method uses exponential smoothing to encode lots of values from the past and use them to predict “typical” values for the present and future. Exponential smoothing refers to the use of an exponentially weighted moving average (EWMA) to “smooth” a time series.

How do you do exponential smoothing? ›

The exponential smoothing calculation is as follows: The most recent period's demand multiplied by the smoothing factor. The most recent period's forecast multiplied by (one minus the smoothing factor). S = the smoothing factor represented in decimal form (so 35% would be represented as 0.35).

How to do an exponential in Excel? ›

Enter your numbers

Inside the parentheses, type your base number, followed by a comma, then the exponent. For example, your formula may be:=POWER(10,2)Pressing "Enter" or moving to another cell runs the function and outputs the result in your cell.

What is Holt's winter method? ›

The Holt-Winters algorithm is a time-series forecasting method that uses exponential smoothing to make predictions based on past observations. The method considers three components of a time series: level, trend, and seasonality, and uses them to make forecasts for future periods.

Is arima better than Holt-Winters? ›

Thus, on the basis of these results, the Holt-Winters additive model is more efficient in predicting the prices of agricultural products than the ARIMA model.

How many smoothing constants does Holt-Winters method contain? ›

The Holt-Winters seasonal method comprises the forecast equation and three smoothing equations — one for the level ℓt , one for the trend bt , and one for the seasonal component st , with corresponding smoothing parameters α , β∗ and γ .

What is the formula for the Winters model? ›

Formula. The additive model is: L t = α (Y t – S tp ) + (1 – α) [L t1 + T t1 ] T t = γ [L t – L t1 ] + (1 – γ) T.

What is Holt's Linear exponential smoothing method? ›

Holt's Linear smoothing

Holt (1957) extended simple exponential smoothing to allow the forecasting of data with a trend. This method involves a forecast equation and two smoothing equations (one for the level and one for the trend).

What is the additive Holt-Winters model? ›

Is an extension of Holt's exponential smoothing that captures seasonality. This method produces exponentially smoothed values for the level of the forecast, the trend of the forecast, and the seasonal adjustment to the forecast.

Which is an annoying feature of Excel's exponential smoothing tool? ›

Which is an annoying feature of Excel's Exponential Smoothing tool? It does not provide the one-step-ahead forecast.

How do you create an exponential trend curve in Excel? ›

On the Chart Design tab, click Add Chart Element, and then click Trendline. Choose a trendline option or click More Trendline Options. You can choose from the following: Exponential.

What is the formula for mad in Excel? ›

Calculate MAD by selecting a cell and entering =AVEDEV(), followed by the range of data points within parentheses. For example, =AVEDEV(A1:A10) calculates the MAD for data in cells A1 through A10.

Top Articles
Latest Posts
Recommended Articles
Article information

Author: Prof. Nancy Dach

Last Updated:

Views: 6176

Rating: 4.7 / 5 (57 voted)

Reviews: 80% of readers found this page helpful

Author information

Name: Prof. Nancy Dach

Birthday: 1993-08-23

Address: 569 Waelchi Ports, South Blainebury, LA 11589

Phone: +9958996486049

Job: Sales Manager

Hobby: Web surfing, Scuba diving, Mountaineering, Writing, Sailing, Dance, Blacksmithing

Introduction: My name is Prof. Nancy Dach, I am a lively, joyous, courageous, lovely, tender, charming, open person who loves writing and wants to share my knowledge and understanding with you.