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

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