Development of a Dashboard for the Consolidation of Forecasts Microsoft Excel was selected as the platform for the development of a dashboard

providing a user-friendly interface for the consolidation of forecasts. It was designed to have the following primary functions:

 Import data – Read historical data consisting of the actual values of the variable being forecasted and the corresponding sets of forecasts that were made.

31

 Select Samples - Manual and random selection of analysis and holdout samples of varying sample sizes and number of forecasts from the input data;

 Transform the input data – Apply two transformations to the data to:

o Remove the inherent growth of the variable being forecasted from the time series to make it stationary, then remove the effect of time-periods such the month of the year, the day of the week and the hour of the day from the transformed data (growth removed);

o Subtract the mean of the forecasts for an input record from the relevant variable being forecasted and all the forecasts, i.e. to transform the variable being forecasted Yi and its k forecasts X1.i, X2.i, … , Xk.i to (Yi - ̅i) and the predictors (X1.i - ̅i), (X2.i - ̅i), … , (Xk.i - ̅i).

 Perform regression analysis for an ad-hoc and pre-defined set of linear regression models (LRM’s):

o The ad hoc user specified model in terms of the forecasts and the predictor variables generated by the dashboard for the pre-defined models, e.g.

Yi =  + 1X1.i + 2X3.i + 3X2.iX4.i + i

o A set of k simple LRM’s, one for each of the k forecasts:

Yi = j + jXj.i +  j.i for j = 1 to k;

o A multiple LRM that includes all the forecasts:

Yi =  + 1X1.i + 2X2.i + … + kXk.i + i

o A multiple LRM that includes all the forecasts and as many of the first-order interactions between the k input forecasts as allowed by the sample size and the maximum number of predictors provided by the dashboard, with the actual interactions selected for inclusion in the model being those with the highest individual correlation with the variable being forecasted. Assuming that a maximum of h interactions are selected then the model is defined as:

Yi =  + ∑ + ∑ +i

32 where:

sm is the index of the first forecast in the m-th selected interaction;

tm is the index of the second forecast in the m-th selected interaction.

For example, if the interaction between x2 and x3 and the interaction between x5 and x7 are selected for inclusion in the model with k forecasts, then the interaction term in the regression model is given by:

.

o A multiple LRM that includes as many of the forecasts and first-order interactions between the input forecasts as allowed by the sample size and the maximum number of predictors provided by the dashboard, with the forecasts and interactions included in the model being those with the highest individual correlation with the variable being forecasted. Assuming that the identified LRM consists of g individual forecasts and h first-order

interactions then the model is defined as:

Yi =  + ∑ + ∑ +i

where:

ra is the index of the a-th individual forecast to be included in the model;

sm is the index of the first forecast in the m-th selected interaction;

tm is the index of the second forecast in the m-th selected interaction.

o A simple LRM with the predictor variable being the mean of the k forecasts:

Yi =  +  ̅i + i

o A set of simple LRM’s with the predictor variable in each case being the weighted mean of the forecasts with the general formula:

Yi =  +  ∑ +i

with the weights for each LRM defined as:

∑ with r the correlation between Y and forecast Xj

33 by Elliott and Timmermann (2004). Given the

variance-covariance matrix for the forecasted variable y and its set of k forecasts f:

Var( ) = ( *

the weights for the forecasts to be consolidated are given by _ET

 Provision of forward entry solutions where starting from step 0 (the null-model:

Y = mean(Y)) the forecast that minimises a user specified loss function is iteratively added into the model. Only forecasts with statistically significant regression coefficients (t-test p-value < .05) are candidates for entry. The stepping terminates if there are no further candidates or when the maximum number of forecasts as dictated by either the capacity of the dashboard or the sample size is reached;

 Provision of backward removal solutions where starting with all predictors in the model, the predictor with the largest regression p-value is iteratively removed from the model. The stepping terminates if there are no predictors with regression p-value greater than the statistical significance level α or if all the predictors have been removed.

 Provision of an option to perform weighted least squares (WLS) regression with the weight wi for observation i calculated as:

( ) where:

Di = the date of observation i

h = the weight increment per day, a user specified non-negative decimal value.

34

 For each regression model, the calculation of a set of loss functions which are all defined to ensure that lower values indicate more accurate forecasts. In the formulas below:

dfE = degrees of freedom error = n – k - 1

ei = observed regression error for case i = ( ̂i - yi) k = the number of predictors in the model

MSE = mean square error = n = the sample size;

R² = coefficient of determination

se = standard deviation of the regression errors SSE = sum of squares error = ( ̂i - yi)²

y = observed value of variable being predicted ̂= estimated y value

The loss functions to be included in the dashboard are:

o L.Adj.R² = 1 – Adjusted R²

= _{( )} ( )

o Std.Err = the regression standard error

= { if = 0

o The mean of each of three asymmetric loss functions recommended by Elliott & Timmermann (2004):

 Linex loss function ( ) exp(aei) - aei – 1

 Lin-Lin loss function ( ) {( )| | if | | if

 Asym.L² the asymmetric squared loss functions:

35

( ) {( ) if if

Note that AIC do not meet all the requirements for a loss function as defined by Granger (1999) in that its value is not zero when the error is zero and smaller errors do not necessarily imply smaller AIC values. Because smaller AIC values are superior to larger values and for the purpose of simplifying the user interface in the various sheets where reference is made to AIC, it is listed under the loss function heading in the dashboard.

 A single overall score of a model’s performance based on the aggregate of all its loss function values. Various alternatives were considered. An absolute score was deemed inappropriate given that the loss functions are on different scales and unbounded in at least one direction. Instead the following relative loss function is proposed:

l = the number of loss functions to be aggregated

Li.j = the j’th regression model’s score for loss function i wi = the weight for loss function i

The weights are at the discretion of the user to control the importance of each loss function in the aggregate score - for example, assigning small weights to the asymmetrical loss functions would be appropriate if asymmetry of loss is of little relevance for the assessment of the regression models.

RAWL values are in the range zero (model obtains best – lowest – scores for all loss functions) to one (model obtains worst – largest – scores for all loss functions).

36

Given that RAWL is a relative loss function, its sole but important purpose is to assist in the ranking of models based on the scores for all loss functions deemed appropriate by the user.

 Detailed and summary statistics for the regression models being considered are provided for the assessment of their performance in terms of the various loss functions.

 Perform holdout validation applying the derived regression models to a user specified number of input data records directly subsequent to the analysis sample that was used for the regression analysis. The regression models are ranked in terms of their values for user specified loss function.

 Perform post-holdout testing, in terms of a user specified loss function, to evaluate the performance of regression models in comparison to that of the following predictors:

o The single forecast with the smallest loss function value for the holdout sample;

o The weighted average of the forecasts for a post-holdout record with the weights for each forecast derived from the inverse of its user specified loss function value Li for the holdout sample as:

∑

o The average of the forecasts for a post-holdout record.

Records directly following the holdout sample are used for post-holdout testing, with the sample size specified by the user.

 Consolidate a set of new forecasts providing both point estimates and confidence and prediction intervals. The top 50 regression models, ranked according to their performance in terms of the holdout sample, are utilised for consolidation.

 Several sets of graphs are provided to give a summarised graphical depiction of what appears elsewhere in tables.

The flow between the main dashboard worksheets together with an indication of their primary functionality is depicted as a flowchart in Figure 3.3.

37

Figure 3.3: Flowchart depicting the flow between and the primary functionality of the main dashboard worksheets

3.3.3 Application of the Dashboard to Consolidate Simulated and Real Forecasts