Chapter 9

PROJECTIONS II - MULTIPLE CORRELATION

1. APPLICATION TO SALES. USE OF THE COMPUTER

STEP 1: enter the Excel program and prepare the matrix with the information available:

STEP 2: slow down the independent variables.
Multiple regressions must be made with the values of the previous period.

Note: observe that for a matrix of n rows, (n+1) observations are needed.

STEP 3: line of regression calculation.
Enter the menu "tools" and then to the submenu” Analysis of Data".

If Excel does not have the Analysis of data option, enter the menu "tools" and then to the sub menu complements, there choose the option tools for Analysis, and then click on accept.
Click on the Analysis of data option, this will show you a window of dialogue; select the option” Regression”.

Click on Accept, and you will observe the following window:

On the icon "Range Y of entrance" enter the data of the dependent variable, that for our example would be sales, (is not necessary to close the window of Regression, it is enough to indicate the data of sales, using the left key of the mouse):

Thereafter, in the square "Range X of entrance", enter the data of the independent variables  (see box), using the mouse and the red guides on the right:

As it can be observed, in the superior circle, the confidence level that has been requested is 95%; the maximum projection error of data accepted is 5%. In addition to this, notice that the option "on a new sheet" is selected; you can see this in the circle on the left-hand side at the key of the page and the result of the data will be shown in a new sheet of Excel clicking on Accept.

The confidence level depends on the willingness of the analyst. It is recommended to work with levels of 95% or superior to that.
The result is the following:

STEP 4: Interpretation of the data.
The multiple coefficients of correlation indicate the percentage of the total sales of the company that is explained by a linear relation of the independent variables (salary, interest rate, and level of employment); in our example is 99.8%
The coefficient of determination of 99,6%, indicates the level of adjustment of the model; the closer the value of R2 is to 1 the greater the amount of total variation that can be explained by the terms that appear in the model.
The adjusted R2 is a measure that includes in the most accurate way the variations that the model calculates in a direct way, adjusting or correcting the number of explanatory variables.
A standard mistake is equivalent to a typical deviation, which indicates that in 95% of the cases each of the observations stands between the expected value, more or less: 51.8.
Observations are the number of analyzed periods, (12).   Remember that for practical effects one should work with (n+1) observations, (13).

STEP 5: preparation of the equation.
Taking the intercept (-5282,68), and the coefficients of  X1 (772,86); X2 (-10,47) and X3 (68,31) we now process the multiple regression equation:

Y = - 5282,687201 + 772.8693637 X1 - 10,47165995 X2 + 68.31834232 X3

Note: The reader can extend the knowledge of statistics in a book specialized on the subject.

STEP 6: Test of the Model.
It is done by the verification of the goodness of the adjustment, as it is indicated next.

Observed that the model adjusts perfectly, and therefore it is applicable to the prediction of future values.

We now apply the multiple regression equation that we had already found and that we indicate here:

Y₁₃= 4288,90; Y₁₄= 4267,70; Y₁₅= 4270,14; Y₁₆= 4220,07; Y₁₇= 4040,20