A B 1 Regression Analysis 2 3 D E F G H Free work cells Answer cells Gericho Hospital specializes in outpatient surgeries for relatively minor procedures. The management team wants to better understand the hospital's cost structure, particularly 4 whether the number of procedures or the number of patient-hours is a better predictor/driver of medical supply cost. 5 6 The hospital CFO provides the following data relating to the prior year's costs and activity. 7 Month 8 Medical supplies costs Number of procedures Number of patient-hours 9 1 $ 106,000 320 2,000 10 2 $ 230,000 500 3,900 11 3 $ 84,000 240 1,900 12 4 $ 238,000 520 4,100 13 5 $ 193,000 240 3,400 14 6 $ 180,000 340 3,700 15 7 $ 210,000 420 3,100 16 8 $ 92,000 360 1,200 17 9 $ 222,000 320 3,000 18 10 $ 78,000 180 1,300 19 20 11 12 $ 127,000 440 2,800 $ 225,000 380 3,800 21 22 NOTE 23 24 25 INOTE 26 27 28 You need to enable Excel's Data Analysis add-in to be able to perform regression. Check online for how to enable it for your specific version of Excel. One possible pathway is: Tools > Excel Add-ins > Check: Analysis ToolPak Check online for how to perform regression for your particular version of Excel. One possible pathway is: Tools > Data Analysis > Regression. 1. Perform a multivariate regression analysis using Excel in which Medical supplies costs is a function of both Number of procedures and Number of patient-hours. When selecting your Y & X ranges, include the labels in row 8. Make sure to tick the "Labels" indicator in the Input prompt. Make sure to choose your Output Range as a NEW WORKSHEET. 29 Provide the following pieces of info by linking to the Summary Output (i.e. use cell references/formulas; DO NOT not copy/paste the values over and DO NOT hard enter the numbers 30 31 Variable 32 Constant 33 Independent variable 1: No. of procedures 34 35 Independent variable 2: No. of patient-hours r-square 36 37 2. Evaluate the model: Coefficient Standard Error t-value 38 39 The model's goodness of fit is... because of the... 40 41 The statistical significance of the no. of procedures is... because of the... 42 43 The statistical significance of the no. of patient-hours is... because of the... 44 45 CU10 p-value