FORECAST

Use the FORECAST command to forecast data by one of three methods: straight-line trend, exponential growth, or Holt-Winters extrapolation. FORECAST performs the calculation according to the method you specify and optionally stores the result in a variable in your analytic workspace.

You can then execute FORECAST.REPORT to produce a standard report of the forecast. You can also use the INFO function to obtain portions of the results for use in your own customized reports or for further analysis.

Syntax

FORECAST [LENGTH n] -

     [METHOD {TREND|EXPONENTIAL|WINTERS PERIODICITY p [argument...]}] -

     [TIME dimension] [FCNAME name] time-series

where argument is one or more of the following clauses that specify the characteristics of the forecast:

     ALPHA n

     BETA n

     GAMMA n

     STSMOOTHED n STSEASONAL n-series STTREND n

     FCSMOOTHED name

     FCSEASONAL name

     FCTREND name

Parameters

LENGTH n

Specifies the number of periods to forecast. The default is zero. When you supply a LENGTH, you must also supply the FCNAME option.

METHOD TREND

(Default) Specifies that the forecasting technique is a straight-line extrapolation of historical data.

METHOD EXPONENTIAL

Specifies that the forecasting technique is an extrapolation of historical data using a constant period-to-period percentage growth.

METHOD WINTERS

Specifies that the forecasting technique is the Holt-Winters method, an extrapolation method that allows for both a linear trend and seasonal fluctuations in the data. Oracle OLAP first constructs three statistically related series for each time period of the historical data. (See "Holt-Winters Constructed Series".) Then, Oracle OLAP produces a forecast from the three series for the specified number of periods into the future.

You can supply several arguments that affect the results of the Holt-Winters forecast. The only required one is PERIODICITY. For the others, Oracle OLAP chooses a reasonable value based on the data available.

PERIODICITY p

The length of the seasonal cycle, where p is an expression that specifies an INTEGER greater than or equal to 2. For example, when the data you are analyzing has monthly values, then p is 12.

PERIODICITY is required when you use the METHOD WINTERS keyword.

ALPHA n

BETA n

GAMMA n

Smoothing constants for the first three series calculated for the Holt-Winters forecast (See "Holt-Winters Constructed Series"). ALPHA is for the smoothed data series; BETA is for the seasonal index series; and GAMMA is for the trend series. The value n is a decimal expression greater than 0 and less than or equal to 1. Each value is optional. When you omit one, Oracle OLAP calculates an optimal smoothing constant for that series that minimizes the Mean Absolute Percent Error of the one-period-ahead forecasts in the historical time periods.

STSMOOTHED n STSEASONAL n-series STTREND n

STSMOOTHED specifies the starting value of the smoothed data series (See "Holt-Winters Constructed Series"). The value n is a decimal expression greater than 0. When you specify STSMOOTHED, you must also specify STSEASONAL and STTREND. When you omit it, Oracle OLAP calculates a starting value.

STSEASONAL specifies the starting values for the seasonal index series (See "Holt-Winters Constructed Series"). N-series is an array of decimal values, one for each period in a seasonal cycle. The number of values needed equals the number specified for PERIODICITY (See "Holt-Winters Starting Values"). When you specify STSEASONAL, you must also specify STSMOOTHED and STTREND. When you omit it, Oracle OLAP calculates the starting values.

STTREND specifies the starting value of the trend series (See "Holt-Winters Constructed Series"). N is a decimal value. When you specify STTREND, you must also specify STSMOOTHED and STSEASONAL. When you omit it, Oracle OLAP calculates a starting value.

FCSMOOTHED name

FCSEASONAL name

FCTREND name

Numeric variables in which Oracle OLAP can store the data calculated for the smoothed data series, the seasonal index series, and the trend series (See "Holt-Winters Constructed Series"). The variable specified by name must have the TIME dimension as one of its dimensions. The series calculations produce DECIMAL results, but Oracle OLAP converts the values to the data type of name before storing them. You can save any or all of the preliminary series. When you do not save a series, Oracle OLAP discards the values after completing the forecast.

TIME dimension

The name of the dimension considered to be the time dimension. The current status of dimension determines the number of periods of historical data used to calculate the forecast. The status of the time dimension must be an increasing, consecutive range of values. LENGTH specifies how many values immediately beyond this range is forecast.

When time-series has only one dimension, the time dimension defaults to that. When time-series has multiple dimensions and one dimension has a type of DAY, WEEK, MONTH, QUARTER, or YEAR, then the time dimension defaults to that type. Otherwise, you must specify the time dimension, even when the additional dimensions are limited to a single value. FORECAST only uses the first value in the status for dimensions other than the time dimension.

FCNAME name

The name of a numeric variable in which to store the values calculated by FORECAST. Name must be dimensioned by the time dimension; it can have other dimensions as well. When the data type of name is not decimal, FORECAST converts the values to the appropriate data type.

Fitted values, which correspond to the historical data, are stored in name for the current status of the time dimension. Forecasted values are stored in name for the number of periods specified by LENGTH. These forecasted periods immediately follow the current status of the time dimension.

For the Holt-Winters method, the fitted values are one-period-ahead forecasts calculated at the previous period. The final forecasted values are extrapolated from the fitted data.

For the TREND and EXPONENTIAL methods, FORECAST obtains the fitted values by evaluating the regression equation over the current status of the time dimension.

time-series

An expression that specifies the time series to be forecast. Time-series must be a numeric expression that is dimensioned by the time dimension. When time-series has other dimensions, FORECAST uses the first value only in their current status. The time-series is the historical data from which FORECAST calculates fitted and forecasted values. (See the explanation for FCNAME.)

Usage Notes

Forecasting Multidimensional Expressions

When you want to forecast all the values of a multidimensional expression, you can use a program that puts a FORECAST statement inside one or more FOR loops to loop over all the remaining dimensions of the expression.

Obtaining Portions of Results

YOu can obtain portions of the results of FORECAST for your own reports or further analysis, using an INFO statement.

Order of Arguments

You can specify the arguments for FORECAST in any order, except that time-series, the expression specifying the data to be forecast, must be last.

Time-series Data Handling

Each method has its own criteria for handling the input data specified in time-series.

• TREND -- Requires at least two values that are not NA; accepts zero and negative values; ignores NA values

• EXPONENTIAL -- Requires at least two positive values; ignores zero, negative, and NA values

• WINTERS -- Accepts zero and negative values; fills in NA values by calculating a weighted moving average

Zero Values

All methods allow zero values in the historical data, specified by time-series, but those time periods are excluded from the Mean Absolute Percent Error (MAPE) calculation.

Holt-Winters Constructed Series

The Holt-Winters forecasting method constructs three statistically related series, which are used to make the actual forecast. These series are:

1. The smoothed data series, which is the original data with seasonal effects and random error removed.

2. The seasonal index series, which is the seasonal effect for each period. A value greater than one represents a seasonal increase in the data for that period, and a value less than one is a seasonal decrease in the data. The Holt-Winters method allows seasonal effects to vary over time, so there is a seasonal index value for every historical period.

3. The trend series, which is the change in the data for each period with the seasonal effects and random error removed. The Holt-Winters method allows the trend effect to vary over time, so there is a trend value for every historical period.

Holt-Winters Omitted Arguments

For the Holt-Winters method, when you omit the STSMOOTHED, STTREND, and STSEASONAL phrases, Oracle OLAP calculates the necessary starting values using an algorithm from Statistical Methods for Forecasting by Abraham and Ledolter. Let Oracle OLAP calculate the starting values when you have little experience with Holt-Winters forecasting.

Holt-Winters Starting Values

When you specify starting values, Oracle OLAP obtains the STSEASONAL starting values by unraveling the values to make a list. The list must have at least the number of values as specified by PERIODICITY. Any more values are ignored; fewer values cause an error. The STSEASONAL expression can be multidimensional and does not have to have the same dimensions as the historical data. (For information about the order of the list when a dimensioned expression is unraveled, see the UNRAVEL function.)

Getting Calculated Values

You can find out the values that Oracle OLAP calculates for ALPHA, BETA, and GAMMA and for STSMOOTHED, STSEASONAL, and STTREND by using the INFO function.

Getting a Report of the Forecast

The FORECAST.REPORT program produces a standard report of a forecast created using the FORECAST command.

The report shows the parameters of the forecast, including the forecast formula and Mean Absolute Percent Error, followed by a display of the forecasted values. To produce this report, type the following.

FORECAST.REPORT

Examples

DEFINE fcst.sales DECIMAL <month>
LIMIT product TO 'Sportswear'
LIMIT district TO 'Chicago'
LIMIT month TO 'Jan95' TO 'Dec96'
FORECAST LENGTH 12 METHOD EXPONENTIAL FCNAME fcst.sales -
time month sales

Forecasting Analysis
                     ====================
 
                 Variable to Forecast: SALES
                   Forecast dimension: MONTH
                      Forecast method: EXPONENTIAL
          Mean absolute percent error: 16.64%
 
        Forecast Equation: SALES = 87718.0009541883 *
                           (1.00553383457899 ** MONTH)
 
MONTH                   Actual Value    Fitted Value
--------------------    ------------    ------------
Jan95                      72,123.47       88,203.42
Feb95                      80,071.75       88,691.52
Mar95                      78,812.69       89,182.33
Apr95                      97,413.26       89,675.85
May95                      94,406.65       90,172.10
  ...                        ...               ...
Dec96                      72,095.02      100,140.38
  ...                        ...               ...

DEFINE fcst.sales DECIMAL <montH>
LIMIT month TO year 'Yr95' 'Yr96'
FORECAST LENGTH 12 METHOD WINTERS -
PERIODICITY 12, ALPHA .5, BETA .5, GAMMA .5 -
time month, FCNAME fcst.sales, sales

Forecasting Analysis
                     ====================
 
                 Variable to Forecast: SALES
                   Forecast dimension: MONTH
                      Forecast method: WINTERS
                                Alpha: 0.50
                                 Beta: 0.50
                                Gamma: 0.50
                          Periodicity: 12
          Mean absolute percent error: 0.20%
 
MONTH                   Actual Value    Fitted Value
--------------------    ------------    ------------
Jan95                      72,123.47       72,154.67
Feb95                      80,071.75       80,027.51
Mar95                      78,812.69       79,171.08
Apr95                      97,413.26       97,200.81
May95                      94,406.65       94,464.71
 ....                         ...             ...
Dec97                                      77,867.23