Data for 137 mutual funds are provided in the aaaaa.xlsx (https://drive.google.com/file/d/1t5SW4SFmXd76LU662zJA9a3FSYLghe2m/view?usp=sharing) spreadsheet. This file contains the fields described below:

Fund Name of Fund

Ret00Q1 % Return in Quarter 1 of 2000 (1Q2000)

Ret99 % Return in 1999 (%)

ExpRatio % Fund Expense Ratio (%)

Objective

GI '1' if Growth-and-Income fund, '0' otherwise

MC '1' if Midsize Companies fund, '0' otherwise

SC '1' if Small Companies fund, '0' otherwise

TK '1' if High-tech fund, '0' otherwise

SalesChg '1' if fund has sales charge, '0' otherwise

Risk

AvgRisk '1' if Average Risk fund, '0' otherwise

HiRisk '1' if High Risk fund, '0' otherwise

develop a model for Ret00Q1 returns as a function of the other variables. In the following questions the term 'variables' refers to all variables in this data set except the 'Fund' name variable.

Use the Excel Tools/Data Analysis/Regression tool to carry out a multiple regression of Ret00Q1 versus all of the other variables. The resulting model will be referred to as the 'full model' below. [Note that the built-in Excel tool requires all 'X'-variables to be in adjacent columns

a)Calculate the sample correlations for all pairs of variables.Briefly comment on any correlations that may be relevant to the regression analysis?

b)What percentage of the variation in Ret00Q1 is explained by the full model.

c)Write out a clear interpretation of each significant slope coefficient that you obtain.

d)When questioned by an investor about a seemingly high expense ratio for a mutual fund, an investment advisor at a bank responded that "this fund is intensively managed to maximize returns, and the management costs are accordingly higher." Comment on this advisor's statement, using the regression results to support your statements.

e)Predict the return in the first quarter of 2000 on a single average-risk, TK fund with a sales charge, an expense ratio of 1.30, and a 10% return in 1999.It is not necessary to generate a prediction interval for this estimate. Examine the original data and comment on any concerns you might have about this prediction.

f)Run a multiple regression of Ret00Q1 versus only the two Risk indicator variables. Compare the results from this regression with those from the full model. Can you explain any differences?

g)Conduct additional regressions at your discretion with the objective of obtaining a 'good' model in the terms discussed in class. You may choose to use the adjusted R-square as a rough measure of the 'goodness' of a model. brief summary of your findings.

h)Graphically examine the residuals from the final model that you select and comment on any concerns you might have. (A plot of standard residuals vs. predicted values is useful for this purpose.)