Please use excel and show formulas. thanks
CMI Number of Falls 26 1.392 23 1.392 9 0.889 12 0.889 18 1.38 16 1.38 32 1.628 28 1.628 16 1.08 18 1.08 35 1.72 26 1.72 18 1.22 23 1.334 31 1.689 31 1.689 8 1.12 16 1.12 29 1.505 26 1.505 The provided data set for this assignment on the Excel Worksheet shows the number of patient falls in a medical/surgical unit of different hospitals and case-mix index of the patients during a year. As you know, case mix index (CMI) is a relative value assigned to a diagnosis-related group of patients in a medical care environment. The CMI value is used in determining the allocation of resources to care for and/or treat the patients in the group. For the given dataset: a) Identify the independent (x) and dependent (y) variables. b) Plot the data on a scatter diagram. Don't forget to label your graph. c) Develop a linear regression model by calculating the line slope and intercept using excel formulas. Write down the line equation. d) Using the provided values for the independent variable and your linear regression model, calculate the predicted values for the dependent variables. e) Calculate Absolute Error for the predicted values and Mean Absolute Error (MAE) for this model f) Calculate the coefficient of determination (R2). What's your interpretation of this value and how do you evaluate the quality of your linear regression model as a predictive model? g) Predict the number of patient falls for three new hospitals with the following CMIS: Hospital A: 1.47 Hospital B: 0.73 Hospital C: 2.01 h) Conduct the Excel regression analysis. Do the models match? CMI Number of Falls 26 1.392 23 1.392 9 0.889 12 0.889 18 1.38 16 1.38 32 1.628 28 1.628 16 1.08 18 1.08 35 1.72 26 1.72 18 1.22 23 1.334 31 1.689 31 1.689 8 1.12 16 1.12 29 1.505 26 1.505 The provided data set for this assignment on the Excel Worksheet shows the number of patient falls in a medical/surgical unit of different hospitals and case-mix index of the patients during a year. As you know, case mix index (CMI) is a relative value assigned to a diagnosis-related group of patients in a medical care environment. The CMI value is used in determining the allocation of resources to care for and/or treat the patients in the group. For the given dataset: a) Identify the independent (x) and dependent (y) variables. b) Plot the data on a scatter diagram. Don't forget to label your graph. c) Develop a linear regression model by calculating the line slope and intercept using excel formulas. Write down the line equation. d) Using the provided values for the independent variable and your linear regression model, calculate the predicted values for the dependent variables. e) Calculate Absolute Error for the predicted values and Mean Absolute Error (MAE) for this model f) Calculate the coefficient of determination (R2). What's your interpretation of this value and how do you evaluate the quality of your linear regression model as a predictive model? g) Predict the number of patient falls for three new hospitals with the following CMIS: Hospital A: 1.47 Hospital B: 0.73 Hospital C: 2.01 h) Conduct the Excel regression analysis. Do the models match
