FORECAST_EXPAVE: Using Single Exponential Smoothing

How to:

Calculate a Single Exponential Smoothing Column

The single exponential smoothing method calculates an average that allows you to choose weights to apply to newer and older values.

The following formula determines the weight given to the newest value.

k = 2/(1+n)

where:

k: Is the newest value.
n: Is an integer greater than one. Increasing n increases the weight assigned to the earlier observations (or data instances), as compared to the later ones.

The next calculation of the exponential moving average (EMA) value is derived by the following formula:

EMA = (EMA * (1-k)) + (datavalue * k)

This means that the newest value from the data source is multiplied by the factor k and the current moving average is multiplied by the factor (1-k). These quantities are then summed to generate the new EMA.

Note: When the data values are exhausted, the last data value in the sort group is used as the next data value.

Syntax: How to Calculate a Single Exponential Smoothing Column

FORECAST_EXPAVE(display, infield, interval,
 npredict, npoint1)

where:

display

Keyword

Specifies which values to display for rows of output that represent existing data. Valid values are:

INPUT_FIELD. This displays the original field values for rows that represent existing data.
MODEL_DATA. This displays the calculated values for rows that represent existing data.

Note: You can show both types of output for any field by creating two independent COMPUTE commands in the same request, each with a different display option.

infield

Is any numeric field. It can be the same field as the result field, or a different field. It cannot be a date-time field or a numeric field with date display options.

interval

Is the increment to add to each sort field value (after the last data point) to create the next value. This must be a positive integer. To sort in descending order, use the BY HIGHEST phrase. The result of adding this number to the sort field values is converted to the same format as the sort field.

For date fields, the minimal component in the format determines how the number is interpreted. For example, if the format is YMD, MDY, or DMY, an interval value of 2 is interpreted as meaning two days. If the format is YM, the 2 is interpreted as meaning two months.

npredict

Is the number of predictions for FORECAST to calculate. It must be an integer greater than or equal to zero. Zero indicates that you do not want predictions, and is only supported with a non-recursive FORECAST.

npoint1

For EXPAVE, this number is used to calculate the weights for each component in the average. This value must be a positive whole number. The weight, k, is calculated by the following formula:

k=2/(1+npoint1)

Example: Calculating a Single Exponential Smoothing Column

The following defines an integer value named PERIOD to use as the independent variable for the moving average. It predicts three periods of values beyond the range of retrieved data.

DEFINE FILE GGSALES
SDATE/YYM = DATE;
SYEAR/Y = SDATE;
SMONTH/M = SDATE;
PERIOD/I2 = SMONTH;
END
TABLE FILE GGSALES
SUM UNITS DOLLARS
COMPUTE EXPAVE/D10.1= FORECAST_EXPAVE(MODEL_DATA,DOLLARS,1,3,3);
BY CATEGORY BY PERIOD
WHERE SYEAR EQ 97 AND CATEGORY NE 'Gifts'
ON TABLE SET STYLE *
GRID=OFF,$
ENDSTYLE
END

The output is shown in the following image:

Category     PERIOD  Unit Sales  Dollar Sales        EXPAVE
--------     ------  ----------  ------------        ------
Coffee            1       61666        801123     801,123.0
                  2       54870        682340     741,731.5
                  3       61608        765078     753,404.8
                  4       57050        691274     722,339.4
                  5       59229        720444     721,391.7
                  6       58466        742457     731,924.3
                  7       60771        747253     739,588.7
                  8       54633        655896     697,742.3
                  9       57829        730317     714,029.7
                 10       57012        724412     719,220.8
                 11       51110        620264     669,742.4
                 12       58981        762328     716,035.2
                 13           0             0     739,181.6
                 14           0             0     750,754.8
                 15           0             0     756,541.4
Food              1       54394        672727     672,727.0
                  2       54894        699073     685,900.0
                  3       52713        642802     664,351.0
                  4       58026        718514     691,432.5
                  5       53289        660740     676,086.3
                  6       58742        734705     705,395.6
                  7       60127        760586     732,990.8
                  8       55622        695235     714,112.9
                  9       55787        683140     698,626.5
                 10       57340        713768     706,197.2
                 11       57459        710138     708,167.6
                 12       57290        705315     706,741.3
                 13           0             0     706,028.2
                 14           0             0     705,671.6
                 15           0             0     705,493.3

In the report, three predicted values of EXPAVE are calculated within each value of CATEGORY. For values outside the range of the data, new PERIOD values are generated by adding the interval value (1) to the prior PERIOD value.

Each average (EXPAVE value) is computed using DOLLARS values where they exist. The calculation of the moving average begins in the following way:

The first EXPAVE value (801,123.0) is the same as the first DOLLARS value.
The second EXPAVE value (741,731.5) is calculated as follows. Note that because of rounding and the number of decimal places used, the value derived in this sample calculation varies slightly from the one displayed in the report output:
```
n=3 (number used to calculate weights)
```
```
k = 2/(1+n) = 2/4 = 0.5
```
```
EXPAVE = (EXPAVE*(1-k))+(new-DOLLARS*k) = (801123*0.5) + (682340*0.50) = 400561.5 + 341170 = 741731.5
```

The third EXPAVE value (753,404.8) is calculated as follows:

EXPAVE = (EXPAVE*(1-k))+(new-DOLLARS*k) = (741731.5*0.5)+(765078*0.50) = 370865.75 + 382539 = 753404.75