Here's what the KPIs tell us:

• The MA(6) baseline typically forecasts values too low for the test dataset.

• On average, the MA(6) baseline is 4.08% too low.

• The typical MA(6) baseline is off by $74,530.

• On average, the MA(6) baseline is off by 6.58%.

With the baseline established, it's now time to see if the FORECAST.ETS() function can provide uplift (i.e., beat the baseline KPIs).

FORECAST.ETS() Intuition

The FORECAST.ETS() function was added to Microsoft Excel 2016 and empowers millions of professionals to build better data-driven forecasts.

The ETS portion of the function name stands for exponential triple smoothing, a battle-tested forecasting model in use for more than 60 years. For this tutorial series, an intuitive understanding of ETS is enough.

You can think of ETS as learning the following from time series data:

1. The average target of the time series, which is known as the level.

2. The average amount of fluctuation between two consecutive targets in the time series known as the trend.

3. Patterns that appear at regular intervals (e.g., weekly or monthly) that impact the level known as the seasonality.

Using ETS, each forecast can be thought of as being:

• Forecast = Level + Trend + Seasonality

Now for the intuition around exponential smoothing.

Consider 1 above. Conceptually, this is similar to a moving average forecasting model. However, a moving average model treats each data point equally in the calculation.

ETS brought the innovation of placing more importance to recent data points versus older data points. In some cases, ETS will severely penalize older data points to arrive at a better forecasting model (i.e., exponential smoothing).

If this seems overwhelming, then you'll probably find this refreshing.

The FORECAST.ETS() function does all this for you automagically.

​

Using FORECAST.ETS()

With the intuition established, it's time to apply ETS to the Sales time series dataset.

The FORECAST.ETS() function has some requirements that you need to be aware of. From Microsoft's website:

"This function requires the timeline to be organized with a constant step between the different points. For example, that could be a monthly timeline with values on the 1st of every month, a yearly timeline, or a timeline of numerical indices."

This is just a fancy of way of saying your dataset must be at a constant grain (e.g., monthly sales) and can't have any holes in the dates (e.g., January 2022 is missing).

The easiest way to get started with FORECAST.ETS() is to use only its three required arguments:

• The date for which the forecast is created.

• The historical targets.

• The historical dates.

First, a FORECAST.ETS() column will hold the forecasts. For the tutorial dataset, the first forecast for the test dataset is January 2024 (i.e., cell C51). Here's the first step of setting up the formula: