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

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

- 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 a K Value to Quarters to Be Forecast

The **co-efficient k **represents the future time to forecast. Calculate the forecast for the 4 quarters of 2023 based on the 2022 available data.

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