Dashboard Sheet 7 – Regression Analysis

This sheet performs the regression analysis for the models defined and selected by the user. Models can be analysed one at a time by specifying the relevant model number or all the models selected in sheet Regression Models by selecting “All” as the model number. The user can similarly select the data transformation(s) to apply and whether backward and forward stepping solutions should be generated in accordance with the methodology described in Chapter 3.

The sheet produces a comprehensive range of the statistics associated with regression analysis. These statistics are reported in the sheet if a single model is analysed and in sheets Regression Results Details and Regression Results Summary if all models are selected or on request if a single model is selected.

The Excel function LINEST was used to conduct the regression. Two other possibilities were also considered. The Excel data analysis tool Regression Analysis, although simple to use and with a wide range of numerical and graphical output, was rejected as the analysis tool because it was unacceptably slow. An implementation utilising Excel linear algebra functions was also tested but had to be rejected when it resulted in invalid statistics such as coefficients of determination greater than 1.00 for data with high levels of multiple collinearity. LINEST was tested as giving the same results as what are produced by the SPSS and Statistica packages for identical datasets. The function has the following syntax: LINEST(known dependent variable values; known predictor variables values; an optional Boolean value const to indicate whether the model has a non-zero intercept; an optional Boolean value stats to indicate whether more statistics than merely the regression coefficients should be reported). For this study both const

96

and stats were set to TRUE. Table 5.2 gives the list of additional statistics reported by LINEST as given by Microsoft.

Table 5.2: Additional statistics reported by the Excel function LINEST Statistic Description

se1,se2,...,sen The standard error values for the coefficients m1, m2,...,mn.

Seb The standard error value for the constant b (seb = #N/A when const is FALSE).

r² The coefficient of determination.

Sey The standard error for the y estimate.

F The F statistic.

Df The degrees of freedom.

Ssreg The regression sum of squares.

Ssresid The residual sum of squares.

Source: Extracted from Microsoft web site http://office.microsoft.com/en-za/excel- help/linest-function-HP010342653.aspx (Microsoft, 2013)

In addition to options to control the regression step type routine, the regression model(s) to analyse and the transformation type(s) the user are also provided the following options:

 The forecast age weight per day increment required for weighted linear

regression. If the value is zero then ordinary linear regression is performed with all the weights equal to 1.00. For a non-zero value iw the weight of the earliest forecast is set to 1.00 and the weight for a subsequent forecast calculated as j x iw where j is equal to the time difference in days between the subsequent forecast and the first forecast.

 The statistical significance level to be used for hypothesis testing with options = 0.01, 0.05 and 0.10.

 The maximum number of predictors allowed for forward stepping - Given the computational intensive nature of the forward stepping routine where at each step of the process a regression analysis is required for each remaining predictor in the model, the user is given the option to set an upper limit of the number of predictors in models for which forward stepping solutions should be produced.

97

This option is not required for backward stepping because in this instance only one regression analysis is required at each step.

 The loss function to be used in forward stepping. The predictor with the lowest value of the selected loss function is entered at each step.

 Parameters for the asymmetrical loss functions, i.e. a for the Linex function and separate theta values for the Lin-Lin and Asymmetrical Square function.

 Weights for the Relative Aggregate Weighted Loss (RAWL) function described in Chapter 3.

The two sections of the interface for this sheet are depicted in Figure 5.7 and Figure 5.8 with the model used illustrative purposes being Model 21 (all selected forecasts included in the model) with no stepping applied to the growth and time-periods effect

transformed data.

Figure 5.7: Dashboard Sheet 7 – Regression Analysis - Interface (section 1 excerpt)

98

Figure 5.8: Dashboard Sheet 7 – Regression Analysis - Interface (section 2 excerpt)

As can be seen in Figure 5.7, up to two regression models are displayed at a given time in this sheet. The first model labelled “Model for Analysis” is the model to be analysed if the Perform Regression Analysis button is clicked. The model’s dependent variable and its predictors are listed in this sheet so that the user can ensure that the correct model has been selected. This is useful given that numeric labels had to be used to identify models as finding short descriptive names for the range of current and possible future models is not feasible. The other model that may be displayed is the last model previously analysed. For this model detailed statistics are reported plus the data that were used in the analysis.