St. Marie's hospital has been faced with financial challenges over the past ten years. Management is interested in restructuring the hospital's operations and has hired you as a consultant to address some issues that have arisen. An Excel spreadsheet was prepared with the following variable headings: Name Description ID: Identification Number (1-113) Length_stay: Average length of stay for all patients in hospital (days) Age_years: Average age of patients (years) Infection_pct: Average estimated probability of acquiring infection in hospital Culture_ratio: Number of cultures performed to number of patients without signs or symptoms of hospital-acquired infection in hospital X_ray_ratio: Number of X-rays performed to number of patients without signs or symptoms of pneumonia Num_Beds: Average number of beds in hospital during study period Region_num: Geographic region : 1 = North East, 2 = North Central, 3 = South, 4 = West Num_Patients: Average number of patients in hospital during study period Num_Nurses: Number of full-time nurses during study period Num_Services: Number of potential facilities and services that are provided by the hospital Use Excel where necessary to assist you in answering the following questions: Question 1 Construct an appropriate table showing the number of full-time nurses across each region. [4 marks] Question 2 Derive a linear regression model for estimating the number of cultures performed to number of patients without signs or symptoms of hospital-acquired infection in hospital based on the number of X-rays performed to number of patients without signs or symptoms of pneumonia. [6 marks] Question 3 Develop a multiple regression model to determine if the average estimated probability of acquiring an infection is dependent on the average length of stay, average age of patients and the average number of patients in hospital during study period. By performing an appropriate test of hypothesis at the 5% level of significance, use the results to answer each of the following questions giving the reasons for your answers. (i) Is the model useful? (ii) Should any of the variables be removed from the model? (iii) Would the addition of the average number of beds in the hospital improve the predictive power of the model? [12 marks] Question 4 Carry out the ANOVA test at the 5% level of significance to provide answers for each of the following: i. ii. Determine if there is a difference in the average number of potential facilities and services that are provided by the hospital by region. [5 marks] Which region(s) showed difference in the average number of potential facilities and services that are provided by the hospital by region? Explain. [3 marks]