First year retention in a college is affected by many variables including average SAT, high school GPA, % of students in the top 10% of their graduating class, % of students in the top 20% of their graduating class, and the average ACT score. Data was collected from 35 colleges in the southeast and a researcher in the Department of Education is trying to build the best regression model to predict first year retention. a) Using Excel’s Correlation procedure, determine which of these variables has the highest correlation with first year retention. Which variable has the highest correlation with first year retention?
b) Prepare a scatter plot showing the relationship between the first year retention and variable you identified in part (a Properly format (trend line with equation, modify equation with variable names rather than x & y, axes labels, etc.) this scatterplot and paste into a Word document with a Figure number. Write an interpretation which discusses the model fit and write the regression equation.
c) Perform the backward stepwise procedure, which is the first variable that is removed in the procedure?
d) Perform the backward stepwise procedure, which is the second variable that is removed in the procedure?
e) Perform the backward stepwise procedure, which is the third variable that is removed in the procedure?
f) After completing the backward stepwise procedure, which independent variables remain in the model?
g) Create an ANOVA table for your best model (based on your answer to part (f) (using Excel’s Regression procedure), format it to show no more than 3 decimal places and remove duplicate columns. Copy and paste it at of the end of the Word document you created in part (b).
h) Based on your best model (part g), how should the researcher estimate the first year retention for a college with an average SAT of 1100, a high school GPA of 3.4, 30 % of students in the top 10% of their graduating class, 45% of students in the top 20% of their graduating class, and an average ACT score of 22.
i) Considering all five independent variables, does there appear to be any multicollinearity among the independent variables?
j) Which of the following pairs of independent variables (if any) indicate multicollinearity?
Submit the Word document you created in parts a) and d)
1st year retention rateAvg SATHS GPA% top 10%% top 20%Avg ACT
82.70%1200.503.56533.70%60.50%26.30
80.00%1003.403.40220.40%37.00%22.70
78.80%1135.673.40117.58%41.76%24.03
89.90%1193.113.60140.46%65.32%26.30
67.65%1041.003.07510.70%19.70%21.90
67.10%1036.443.17013.45%30.57%21.91
73.50%1019.423.34717.58%41.76%22.12
91.45%1211.503.57436.00%68.40%26.10
79.10%1075.423.34615.90%33.47%22.94
59.88%932.002.7182.50%15.00%19.50 88.50%1194.603.61736.30%67.40%26.00
67.00%936.062.5271.55%5.17%18.99
72.00%1061.373.19014.43%29.49%22.01
59.88%901.002.6232.90%6.69%19.00
63.20%920.232.6713.17%10.32%19.22 84.90%1206.093.53531.45%56.40%26.23
70.18%993.002.9971.80%17.50%20.70 65.70%924.002.6031.80%6.00%19.00
66.90%924.802.7385.20%14.20%19.00 76.40%1010.683.32914.63%21.95%21.90
87.70%1148.903.47732.60%53.90%24.10
68.80%1037.903.17913.70%27.50%21.80
67.70%1013.242.9055.44%16.33%21.06< br /> 86.60%1162.253.49431.53%53.15%25.54
67.00%973.283.18410.96%23.29%21.27< br /> 85.40%1195.523.60342.07%63.79%26.47
66.30%1014.902.8566.70%16.30%21.00< br /> 72.50%980.873.3397.79%31.17%21.61 73.10%1013.302.8395.90%14.10%21.10< br /> 76.43%1133.203.28118.20%37.70%23.80
75.40%996.203.2118.60%20.70%22.00 76.90%1065.903.1999.60%22.90%22.70< br /> 52.50%970.003.18823.33%43.33%20.97< br /> 72.55%1025.403.27821.10%36.80%21.60
76.70%1077.933.23711.65%24.92%22.94