Business Analytics Excel work

A study is being conducted to determine what factors have an effect on college and university graduation rates. Open the Colleges and Universities Data workbook on Moodle. The goal of this project is to construct a spreadsheet model that will allow an analyst to predict the graduation rate of a college or university depending upon several factors: • Whether the institution is a liberal arts college or university; • Median SAT score of students at the institution; • Acceptance rate of the college or university; • Expenditures per student; • Percentage of students in the top 10% of their high school class. Part 1: Perform regression analysis by constructing a model to predict the graduation rate of a college or university. Complete the following steps: a. Assess the data using descriptive statistics. Include various descriptive measures (mean, max, mode, median, min, standard deviation, etc.) Use histograms, if appropriate, and analyze any outliers you find in the data. Record what you learn. b. Plot the data. Analyze and record what you learn from the various scatter plots. c. Determine the correlation between the dependent variable and the various independent variables by creating a correlation matrix. Assess for multicollinearity between the independent variables. d. Assess the predictive capability of this data using both simple and multiple linear regression, including the use of a dummy (categorical independent) variable of whether the student attended a college or university. Run the regressions, perform statistical inference in each case, and record what you learn. Use a 0.05 level of significance. If you assess that there is not a relationship between any two variables of data, redo the regression equation and assessments showing only the data points with linear relationships. If you find multicollinearity, remove it by redoing your model without one of the variables causing multicollinearity, then try it with the other variable removed and pick the best option. e. Based on the prior steps, determine and record the best linear regression equation for this data. Discuss the meaning of the model fit and regression coefficients. f. Assess the possibility of a better curvilinear regression line. If your findings warrant this, run the curvilinear regression and discuss the meaning of the model fit and the regression coefficients. g. Pick the best regression equation and document it. h. Using your best regression equation, what is your graduation rate prediction for a university whose student median SAT score is 1210, acceptance rate is 23%, expenditures per student are $25,500, and the percentage of students in the top ten percent of their graduating HS class is 79%? i. What other independent variables (not cited in the data) may be important in improving this model?