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

**F****t ****= (L****t-1 ****+ T****t-1****)* S****t-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**

- Output â†’
**SUMSQ(H15:H21)**â†’ Calculatesthe sum of the squares of**H5:H11**.- Output â†’
**463493653301**

- Output â†’
**=SQRT(SUMSQ(H15:H21)/COUNT(H15:H21))**â†’ Calculates the**RMSE****=SQRT(992.463493653301/7)****=SQRT(141.780499093329)**- Output â†’
**9072**

- Output â†’

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

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

Thank you for your kind words.

Have a good day!!

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,

It’s a bit difficult to answer this, not seeing your spreadsheet. Can you please send a sample to

[email protected]?Prior to that, check the following Excel file. We have added a 12-month example for you. Let us know if this helps.

Download Link:Solution.xlsxNote:You must have data of 2021 and 2022 for forecasting 2023.

Hi Akib,

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

Thank you.

Hi Quang,

Thanks for your feedback. You are right on this occasion. The formula will be

in`=(D15+E15)*F12`

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.