X i - Data Table Overhead Cost Hospital Nursing Number of Overhead Cost Patient Days Month Overhead Costs Hours per Patient Day per Nursing Hour 19.83 $ 127.96 July. 476,000 24,000 3,720 $ $ August $ September $ 26,000 19.69 $ 512,000 4,320 $ 118.52 4,220 $ 424,000 20,000 21.20 $ 100.47 $ 19.91 $ October 448,000 22,500 3,470 $ 129.11 5,690 $ 18.50 $ November 555,000 30,000 97.54 22,000 3,210 $ 19.59 $ December. 431,000 134.27 Print Done EA Requirements to 1. Are the hospital's overhead costs fixed, variable, or mixed? Explain gr 2. Graph the hospital's overhead costs against nursing hours. 3. Graph the hospital's overhead costs against the number of patient days. 4. Do the data appear to be sound or do you see any potential data problems? Explain 5. Use the high-low method to determine the hospital's cost equation using nursing hours as the cost driver. Predict total overhead costs if 25,000 nursing hours are predicted for the month 6. Hoffman runs a regression analysis using nursing hours as the cost driver to predict total hospital overhead costs. The Excel output from the regression analysis is as follows: (Click the icon to view the regression analysis.) If 25,000 nursing hours are predicted for the month, what is the total predicted hospital overhead? 7. Hoffman then ran the regression analysis using number of patient days as the cost driver. The Excel output from the regression is shown here: Click the icon to view the regression analysis.) If 3,640 patient days are predicted for the month, what is the total predicted hospital overhead? 8. Which regression analysis (using nursing hours or using number of patient days as the cost driver) produces the best cost equation? Explain your answer. Print Done - X Regression analysis using nursing hours SUMMARY OUTPUT - Nursing hours as cost driver Regression Statistics Multiple R 0.984895 R Square 0.970018 Adjusted R Square 0.962523 Standard Error 9,883.836352 Observations 6 ANOVA df SS MS F Significance F Regression 1 12,642,572,449 12,642,572,449 129.414926 0.000341 390,760,884 4 Residual 97,690,221 5 13,033,333,333 Total Standard Lower Upper Coefficients 95% Error tStat P-value 95% 46,466.228 Intercept 131,004.69 30,448.454 4.303 0.013 215,543.149 X Variable 1 14.26 1.253 11.376 0.000 10.777 17.735 Print Done Regression analysis using number of patient days SUMMARY OUTPUT - Using number of patient days as cost driver Regression Statistics Multiple R 0.818166 R Square 0.669396 Adjusted R Square 0.586745 32,820.99359 Standard Error Observations 6 ANOVA MS df SS Significance F 0.046589 Regression 8,724,462,852 8,724,462,852 8.099072 4,308,870,482 Residual 1,077,217,620 13,033,333,334 Total Standard Lower Upper 95% Coefficients Error t Stat P-value 95% 280,775.96 4.05 Intercept 69,320.327 0.015 88,311.882 473,240.047 X Variable 1 93.153 47.15 16.568 2.846 0.047 1.151 Print Done X i - Data Table Overhead Cost Hospital Nursing Number of Overhead Cost Patient Days Month Overhead Costs Hours per Patient Day per Nursing Hour 19.83 $ 127.96 July. 476,000 24,000 3,720 $ $ August $ September $ 26,000 19.69 $ 512,000 4,320 $ 118.52 4,220 $ 424,000 20,000 21.20 $ 100.47 $ 19.91 $ October 448,000 22,500 3,470 $ 129.11 5,690 $ 18.50 $ November 555,000 30,000 97.54 22,000 3,210 $ 19.59 $ December. 431,000 134.27 Print Done EA Requirements to 1. Are the hospital's overhead costs fixed, variable, or mixed? Explain gr 2. Graph the hospital's overhead costs against nursing hours. 3. Graph the hospital's overhead costs against the number of patient days. 4. Do the data appear to be sound or do you see any potential data problems? Explain 5. Use the high-low method to determine the hospital's cost equation using nursing hours as the cost driver. Predict total overhead costs if 25,000 nursing hours are predicted for the month 6. Hoffman runs a regression analysis using nursing hours as the cost driver to predict total hospital overhead costs. The Excel output from the regression analysis is as follows: (Click the icon to view the regression analysis.) If 25,000 nursing hours are predicted for the month, what is the total predicted hospital overhead? 7. Hoffman then ran the regression analysis using number of patient days as the cost driver. The Excel output from the regression is shown here: Click the icon to view the regression analysis.) If 3,640 patient days are predicted for the month, what is the total predicted hospital overhead? 8. Which regression analysis (using nursing hours or using number of patient days as the cost driver) produces the best cost equation? Explain your answer. Print Done - X Regression analysis using nursing hours SUMMARY OUTPUT - Nursing hours as cost driver Regression Statistics Multiple R 0.984895 R Square 0.970018 Adjusted R Square 0.962523 Standard Error 9,883.836352 Observations 6 ANOVA df SS MS F Significance F Regression 1 12,642,572,449 12,642,572,449 129.414926 0.000341 390,760,884 4 Residual 97,690,221 5 13,033,333,333 Total Standard Lower Upper Coefficients 95% Error tStat P-value 95% 46,466.228 Intercept 131,004.69 30,448.454 4.303 0.013 215,543.149 X Variable 1 14.26 1.253 11.376 0.000 10.777 17.735 Print Done Regression analysis using number of patient days SUMMARY OUTPUT - Using number of patient days as cost driver Regression Statistics Multiple R 0.818166 R Square 0.669396 Adjusted R Square 0.586745 32,820.99359 Standard Error Observations 6 ANOVA MS df SS Significance F 0.046589 Regression 8,724,462,852 8,724,462,852 8.099072 4,308,870,482 Residual 1,077,217,620 13,033,333,334 Total Standard Lower Upper 95% Coefficients Error t Stat P-value 95% 280,775.96 4.05 Intercept 69,320.327 0.015 88,311.882 473,240.047 X Variable 1 93.153 47.15 16.568 2.846 0.047 1.151 Print Done