Decision science Excel report

Excel Sheet Summary

Predicting Mutual Funds Returns from Manager’s SAT, Education, Age and Tenure

Taking the data presented in the excel sheet provided on the return and details of each of the mutual fund managers, we seek to predict returns denoted as y from the x values which are, x1 SAT, x2 Degree, x3 Age and x4 Tenure.

The sample population regression model is: y = β1 + β2x2 + β3x3 + β4x4 + u

Assuming that the error u is independent with constant variance (homoscedastic), we estimate the regression line: y = b1 + b2x2 + b3x3 + b4x4

On excel, under the analysis groups, click on data analysis and then go to regression from the dialogues box that come up. In this you set the range for the dependent variable y and then set those of the independent variables x1, x2, x3 and x4. In doing this, note that excel only regress numerical data thus a need for different treatment of any non-numerical data used.

Treatment of the non-Numerical Data:

Working in excel, it is not possible to regress non-numerical data such as those describing the educational qualification of the mutual fund managers. Consequently, we allocate a numerical key to each of the educational qualifications represented in the profiles of the managers. In this case, we use number 1 to 5 in the place of each of the educational attainments as 1 – MBA , 2 – MS, 3 – BS, 4 – BA and 5 – PhD.

Upon completion of the above, excel then presents a summary of the outputs from regression in the cell marked as the output cell or on a new worksheet as defined in the regression dialogue box.