Data Table Hospital Month Overhead Costs July ........ $ 476,000 August ..... $ 512,000 September .. $ 424,000 October ..... $ 448,000 November ... $ 555,000 December ... $ 431,000 Nursing Hours 24,000 26,000 20,000 22,500 30,000 22,000 Number of Overhead Cost Overhead Cost Patient Days per Nursing Hour per Patient Day 3,720 $ 19.83 $ 127.96 4,320 $ 19.69 $ 118.52 4,220 $ 21.20 $ 3,470 $ 19.91 $ 129.11 5,690 $ 18.50 $ 97.54 3,210 $ 19.59 $ 134.27 100.47 Print Done 0 Requirements Y 1. Are the hospital's overhead costs fixed, variable, or mixed? Explain. 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,500 nursing hours are predicted for the month. 6. Becker 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,500 nursing hours are ptadicted for the month, what is the total predicted hospital overhead? 7. Becker 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,680 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 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 ANOVA MS F Significance F Regression 12,642,572,449 12,642,572,449 129.414926 0.000341 Residual 390,760,884 97,690,221 Total 13,033,333,333 Standard Lower Upper Coefficients Error Stat P-value 95% 95% Intercept 131,004.69 30,448.454 4.303 0.01346,466.228 215,543.149 X Variable 1 14.26 11.376 0.000 10.777 17.735 1.253 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 Standard Error 32,820.99359 Observations ANOVA df SS MS Regression 8,724,462,852 8,724,462,852 Residual 4,308,870,482 1,077,217,620 Total 5 13,033,333,334 F 8.099072 Significance F 0.046589 Stat P-value Standard Error 69,320.327 16.568 Coefficients 280,775.96 47.15 Lower 95% 88,311.882 1.151 Upper 95% 473,240.047 93.153 Intercept X Variable 1 4.05 2.846 0.015 0.047 Print Done Los Becker the Chief Operating O wUnion Ho w eveyshe ng the honovads uning hours we er of patient day would be the best couverte for predicting the opta Requirement. The hous e s and plan The powe r on the and eve r y way we Afer you to the so n you become begeerte r to the top of the h e r e the ground the point to be played below to draw the graph) soft the charged to be P o DE There in the producing t a g o n www . charter . The point Requested to dem o nstrateurs as me 25.500 to the Data Table Hospital Month Overhead Costs July ........ $ 476,000 August ..... $ 512,000 September .. $ 424,000 October ..... $ 448,000 November ... $ 555,000 December ... $ 431,000 Nursing Hours 24,000 26,000 20,000 22,500 30,000 22,000 Number of Overhead Cost Overhead Cost Patient Days per Nursing Hour per Patient Day 3,720 $ 19.83 $ 127.96 4,320 $ 19.69 $ 118.52 4,220 $ 21.20 $ 3,470 $ 19.91 $ 129.11 5,690 $ 18.50 $ 97.54 3,210 $ 19.59 $ 134.27 100.47 Print Done 0 Requirements Y 1. Are the hospital's overhead costs fixed, variable, or mixed? Explain. 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,500 nursing hours are predicted for the month. 6. Becker 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,500 nursing hours are ptadicted for the month, what is the total predicted hospital overhead? 7. Becker 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,680 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 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 ANOVA MS F Significance F Regression 12,642,572,449 12,642,572,449 129.414926 0.000341 Residual 390,760,884 97,690,221 Total 13,033,333,333 Standard Lower Upper Coefficients Error Stat P-value 95% 95% Intercept 131,004.69 30,448.454 4.303 0.01346,466.228 215,543.149 X Variable 1 14.26 11.376 0.000 10.777 17.735 1.253 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 Standard Error 32,820.99359 Observations ANOVA df SS MS Regression 8,724,462,852 8,724,462,852 Residual 4,308,870,482 1,077,217,620 Total 5 13,033,333,334 F 8.099072 Significance F 0.046589 Stat P-value Standard Error 69,320.327 16.568 Coefficients 280,775.96 47.15 Lower 95% 88,311.882 1.151 Upper 95% 473,240.047 93.153 Intercept X Variable 1 4.05 2.846 0.015 0.047 Print Done Los Becker the Chief Operating O wUnion Ho w eveyshe ng the honovads uning hours we er of patient day would be the best couverte for predicting the opta Requirement. The hous e s and plan The powe r on the and eve r y way we Afer you to the so n you become begeerte r to the top of the h e r e the ground the point to be played below to draw the graph) soft the charged to be P o DE There in the producing t a g o n www . charter . The point Requested to dem o nstrateurs as me 25.500 to the