How do I implement the exponential smoothing formula in Excel?

Conditions

• The smoothing constant must be stored in a fixed cell and referenced absolutely.
• The data is arranged in columns for Week, Unit Sales, and Forecast.

Reasoning, step by step

1. Identify the cell containing the smoothing constant (e.g., B4).
2. Enter the formula in the first forecast cell after initialization (e.g., C5).
3. Use relative references for the previous forecast (=C4) and previous actual (=B4).
4. Use an absolute reference for the smoothing constant (=$B$4).
5. Complete the formula: =C4+$B$$4*(B4-C4)$.
6. Double-click the fill handle to auto-fill the formula down the column.

Example

The video shows the formula =C4+$B$$4*(B4-C4)$ being entered into cell C5. The user then double-clicks the fill handle, causing the formula to fill down column C and updating the chart with a red forecast line.

Common misconceptions

• Forgetting to make the smoothing constant an absolute reference, leading to incorrect calculations when dragging the formula.
• Referencing the current row's actual value instead of the previous row's actual value.

Watch the explanation

Explore next

• What is the mathematical formula for exponential smoothing?
• How does the video visualize the smoothed forecasts?
• What is the purpose of the auto-fill feature in this context?