How is exponential smoothing implemented as an Excel formula?

Conditions

• Requires a table with actuals in one column and forecasts in another.
• Uses relative references for prior row values and an absolute reference for alpha.

Reasoning, step by step

1. Map $F(t-1)$ to the cell above in the Forecast column (e.g., C4).
2. Map $A(t-1)$ to the cell in the Actual column on the same row as the previous forecast (e.g., B4).
3. Map alpha to a fixed cell (e.g., B2) using an absolute reference ($B$2).
4. Construct the formula: =Previous_Forecast + Absolute_Alpha * (Previous_Actual - Previous_Forecast).
5. Drag or fill the formula down to apply the recursion to subsequent periods.

Example

The user enters =C4+$B$$2*(B4-C4)$ into cell C5. The speaker walks through the formula as previous forecast plus alpha times previous actual minus previous forecast and mentions locking alpha for dragging.

Common misconceptions

• Using a relative reference for alpha, which would change the smoothing constant when copying the formula down.
• Incorrectly referencing the current actual value instead of the previous actual value.

Watch the explanation

Explore next

• What is the exponential smoothing forecast formula shown in the video?
• What does alpha represent in this forecasting method?
• How do you initialize the first forecast when there is no previous period?