General Exponential Smoothing (GESMTH) with trend in Excel

General Exponential Smoothing with Trend in NumXL: In this video, we show you how to use the general exponential smoothing function in NumXL with trend.

 

In this tutorial, we’ll introduce the latest addition to the exponential smoothing functions family – The General Exponential Smoothing Function (GESMTH).

The General Exponential Smoothing Function not only supports prior smoothing models covered by the Simple, double and triple exponential smoothing functions, but it implements additional types for trend and seasonal components.

Furthermore, the General Exponential Smoothing Function supports data prior-transformation and Chatfield correction/adjustment for 1st order autocorrelation in the forecast errors. In short, the general exponential smoothing function is the ultimate tool in our arsenal to smooth your data, and project forecast.

In this video, we will cover the smoothing models of non-seasonal trending data set using the different trend types and built-in automatic optimization for the smoothing parameters.

We will use the annual net power generation in US using natural gas as a fuel between 1949-2016. The data was sourced from US Energy Information Agency, and units are expressed in millions of Mega Watt Hours.

Once you find the General Exponential Smoothing function, click on the “fx” button located on the left of the equation toolbar. This will invoke the “function arguments dialog” for the General Exponential Smoothing Function.

First, let’s specify the input cells range.

In the order field, specify 1 (or True) for the time order in the series. This designates that the 1st observation in the input series corresponds to the earliest date.

For the level-smoothing parameter alpha, you can type in a value between 0 and 1, leave blank, or reference an existing cell.

In this example, let’s reference the value in D1.

For the trend-smoothing parameter (beta), you can also type in a value between 0 and 1, leave blank, or reference an existing cell.

In this example, let’s reference the value in D2.

For Gamma or seasonality indices smoothing parameters, you may type a value between 0 and 1, leave blank, or reference the existing cell in your worksheet.

Now, for the trend damping coefficient (Phi), you may type a value between 0 and 1, leave blank, or reference a cell in your worksheet.

For the parameter value of Chatfield’s autocorrelation correction term (aka Lambda), you may type a value between -1 and 1, leave blank, or reference a cell in your worksheet.

For trend-type, you need to choose a value from 0 (no trend) to 4 (damped-multiplicative), leave blank (aka no trend), or reference a cell in your worksheet.

For seasonality type argument, you may leave blank indicating nonseasonal, enter 0, or reference a cell in the spreadsheet.

For seasonal length, you may leave blank (no seasonality), type in a value (e.g. 1), or reference a cell.

For enabling/disabling the built-in optimizer, you may type in True/False, leave blank (disabled), or reference the value in D9

For enabling/disabling Chatfield’s autocorrelation correction, enter a True/False, leave blank, or reference the value in D10

For prior-data log transform, reference the value in cell D11

For the forecast time, we will be forecasting the value at the end of the input data. So, set T to zero, or better yet, reference a cell in column A, so we can change it after the end of the input data.

For return type, leave blank or type in zero (0) for forecast.

The function returns #N/A since we don’t have enough data.

Copy the formulas to the cells below it, even after the end of the sample data.

In Exponential smoothing, using the most relevant data is key for a good model fitting, parameters calibration, and, eventually, robust forecasts. Looking again to the data, we see three separate regimes (roughly):

  • 1949 – 1969 (Growth)
  • 1970 – 1988 (Plateau)
  • 1989 – today (Growth)

Damped-multiplicative trend-type for the log data (MAPE is 5% and MASE is 67%)

Does it matter which model to use?

For one-step forecast, the two models give very comparable results. How about multiple-steps forecast beyond our data sets?

The damped additive trend-type (for log-data) is relatively simpler model, and materializes in a damped exponential growth, something we can agree on, maybe!

The damped-multiplicative trend (for the log-data) assumes the log-data itself exhibits exponential growth, so the pace of growth for the forecast is much faster that prior model.

We would like to use a simple intuitive model, so we’d go with the damped-additive model.

In this tutorial, we only looked at trend-type component (no seasonality), and only concerned with the forecast values. The General exponential smoothing function has many outputs that we believe are key to build your next killer-model.

Have more questions? Submit a request

0 Comments