!1 il = Merged Cent .00 .0 Formatting as Table 1 N 0 P Q R s T U v P1 4 x fx B D E F H 1 K L M 1 Multiple Regression Case (2021) 2 As the cost of college tuition increases and families of college bound students are facing financial hardships, 3 selection of a college is an important decision. The Department of Education is publishing annual reports that 4 are used by parents, students and high school counselors when searching for the perfect fit". 5 You are provided with data for a group of 116 colleges and asked to develop a predictive model using multiple regression. 6 The model could then be used to determine the factors that are statistically significant in explaining post-college earnings. 7 The model could also be a valuable marketing and branding tool used by college recruiters. 8 For each of the colleges in the sample data, you are given the following: 9 the average post-college Earnings (5) 10 the average annual Cost ($) 11 Graduation rate(%) 12 the percentage of students paying down Debt (%) 13 You are asked to show your understanding of multiple regression models and predictive analytics 14 to complete the following tasks. 15 Tasks to complete Use step-wise regression to predict Average Post-college earnings (dependent variable) using cost, graduation rate and percentage of students 17 paying down debt. You will need to use Excel's Data Analysis, Regression tool to generate 4 summary outputs to address each of the following questions (10 points): What is the relationship between the Average Earnings after the graduation and the average Annual Cost? Write down the estimated regression equation. What is the relationship between the Average Earnings after the graduation and the Graduation Rate? Write down the estimated regression equation. What is the relationship between the Average Earnings after the graduation and the % of students paying Debt? Write down the estimated regression equation. What is the relationship between the Average Earnings after the graduation and all 3 predictor variables considered jointly? Write down the estimated regression equation. Discuss your findings. For each of summary outputs comment on the statistical significance of results (use alpha = .05), interpret value of R Square, 25 interpret the values of X coefficients (10 points) What conclusions and recommendations can you derive from your analysis? Be thorough and specific in your analysis. (4 points) 28 Would you recommend using all 3 predictor variables in the final model, if the level of statistical significance alpha is set to 05? If not, explain. (6 points) Develop the final model you are recommending to predict Average Earnings after graduation Provide the Residual Output and discuss the results. Make sure to interpret the value of Rsquare, as well as the values of X coefficients. Plot on a line chart the recorded and predicted Average Earnings after graduation. Customize the chart 32 by including your first and last names in the title. (8 points) What other independent (predictor) variables could be included in the model to predict the Average Earnings after graduation? 34 Elaborate briefly. (2 points) 16 1 18 19 20 21 22 23 24 2 3 26 27 4 5 29 30 31 33 6 !1 il = Merged Cent .00 .0 Formatting as Table 1 N 0 P Q R s T U v P1 4 x fx B D E F H 1 K L M 1 Multiple Regression Case (2021) 2 As the cost of college tuition increases and families of college bound students are facing financial hardships, 3 selection of a college is an important decision. The Department of Education is publishing annual reports that 4 are used by parents, students and high school counselors when searching for the perfect fit". 5 You are provided with data for a group of 116 colleges and asked to develop a predictive model using multiple regression. 6 The model could then be used to determine the factors that are statistically significant in explaining post-college earnings. 7 The model could also be a valuable marketing and branding tool used by college recruiters. 8 For each of the colleges in the sample data, you are given the following: 9 the average post-college Earnings (5) 10 the average annual Cost ($) 11 Graduation rate(%) 12 the percentage of students paying down Debt (%) 13 You are asked to show your understanding of multiple regression models and predictive analytics 14 to complete the following tasks. 15 Tasks to complete Use step-wise regression to predict Average Post-college earnings (dependent variable) using cost, graduation rate and percentage of students 17 paying down debt. You will need to use Excel's Data Analysis, Regression tool to generate 4 summary outputs to address each of the following questions (10 points): What is the relationship between the Average Earnings after the graduation and the average Annual Cost? Write down the estimated regression equation. What is the relationship between the Average Earnings after the graduation and the Graduation Rate? Write down the estimated regression equation. What is the relationship between the Average Earnings after the graduation and the % of students paying Debt? Write down the estimated regression equation. What is the relationship between the Average Earnings after the graduation and all 3 predictor variables considered jointly? Write down the estimated regression equation. Discuss your findings. For each of summary outputs comment on the statistical significance of results (use alpha = .05), interpret value of R Square, 25 interpret the values of X coefficients (10 points) What conclusions and recommendations can you derive from your analysis? Be thorough and specific in your analysis. (4 points) 28 Would you recommend using all 3 predictor variables in the final model, if the level of statistical significance alpha is set to 05? If not, explain. (6 points) Develop the final model you are recommending to predict Average Earnings after graduation Provide the Residual Output and discuss the results. Make sure to interpret the value of Rsquare, as well as the values of X coefficients. Plot on a line chart the recorded and predicted Average Earnings after graduation. Customize the chart 32 by including your first and last names in the title. (8 points) What other independent (predictor) variables could be included in the model to predict the Average Earnings after graduation? 34 Elaborate briefly. (2 points) 16 1 18 19 20 21 22 23 24 2 3 26 27 4 5 29 30 31 33 6