Answer part B. https://www.chegg.com/homework-help/questions-and-answers/-q35922938 (answer to part A)
Situation I don't understand this cost report at all' stated James McCormick, the newly appointed hospital administrator for Mountainview General Hospital. 'Our administrative costs in the new pediatrics clinic are all over the map. One month the report shows $8,300 and the next month it's $16,100. What's going on?' McCormick's question was posed to Megan Stuart, the hospital's director of Cost Management who reports directly to McCormick who in turn reports directly to the Board of Trustees. 'The main problem is that the clinic has experienced some widely varying patient loads in its first year of operation. There seems to be some confusion in the public's mind about what services we offer in the clinic. When should patients use the clinic? When should they go to the emergency room? That sort of thing. As the patient load has varied, we've frequently changed our clinic administrative staffing McCormick continued to puzzle over the report. 'Could you put together an analysis Megan, so we can see how this cost behaves over a range of patient loads? Megan proceeded to gather the raw data on patient load and administrative costs for the clinic: Administrative Cost Patient Load January Februrary March April May June July August September October November December 1,540 $ 13,900 7,000 6,000 9,800 1,430$ 11,900 9,600 1,210 $ 10,200 4,100 9,300 1,320 $11,100 8,300 1,980 $ 16,100 550 $ 440 $ 1,100$ 990 $ 220 $ 770 $ 660 $ Megan and James do not believe the first year's widely fluctuating patient load will be experienced again in the future. Megan has estimated the clinic's relevant range of monthly activity in the future will be 750-1,500 patients and James concurs. Requirements Part A: Analyze the Data 1. Prepare a scatter diagram in Excel, then MANUALLY draw a 'best fit line' that you feel fits the data. Clearly present a cost equation based on your scatter diagram line. (Note-you may use the insert, then 'shapes' function in Excel to manually draw a line, or you can print your diagram and draw the line in pen. HOWEVER-if you have Excel insert a line, the program will run regression on the data to put in the line and thus you will NOT have fulfilled the requirements.) 2. Use the high-low method to estimate the cost behavior for the clinic's administrative costs. Clearly label the final cost formula. 3. Perform a least squares regression analysis on the data to estimate the cost behavior. Clearly present the final cost formula. Include a brief analysis of the R2 factor for the regression on your Excel sheet. Part B: Provide Useful Information Prepare a to 1 page memo to James McCormick that communicates the results of your analysis. You should include in your memo observations about the likely cost behavior pattern, the cost equations developed under each of the methods from Part A and how a cost equation is to be used (i.e. what are the variables involved and how is the cost equation used to estimate costs). Include a brief description of each method used that includes the advantage and disadvantage of each method presented. Finally, provide a recommendation of which equation you suggest should be used for decision-making and why it is the best in this specific scenario. Situation I don't understand this cost report at all' stated James McCormick, the newly appointed hospital administrator for Mountainview General Hospital. 'Our administrative costs in the new pediatrics clinic are all over the map. One month the report shows $8,300 and the next month it's $16,100. What's going on?' McCormick's question was posed to Megan Stuart, the hospital's director of Cost Management who reports directly to McCormick who in turn reports directly to the Board of Trustees. 'The main problem is that the clinic has experienced some widely varying patient loads in its first year of operation. There seems to be some confusion in the public's mind about what services we offer in the clinic. When should patients use the clinic? When should they go to the emergency room? That sort of thing. As the patient load has varied, we've frequently changed our clinic administrative staffing McCormick continued to puzzle over the report. 'Could you put together an analysis Megan, so we can see how this cost behaves over a range of patient loads? Megan proceeded to gather the raw data on patient load and administrative costs for the clinic: Administrative Cost Patient Load January Februrary March April May June July August September October November December 1,540 $ 13,900 7,000 6,000 9,800 1,430$ 11,900 9,600 1,210 $ 10,200 4,100 9,300 1,320 $11,100 8,300 1,980 $ 16,100 550 $ 440 $ 1,100$ 990 $ 220 $ 770 $ 660 $ Megan and James do not believe the first year's widely fluctuating patient load will be experienced again in the future. Megan has estimated the clinic's relevant range of monthly activity in the future will be 750-1,500 patients and James concurs. Requirements Part A: Analyze the Data 1. Prepare a scatter diagram in Excel, then MANUALLY draw a 'best fit line' that you feel fits the data. Clearly present a cost equation based on your scatter diagram line. (Note-you may use the insert, then 'shapes' function in Excel to manually draw a line, or you can print your diagram and draw the line in pen. HOWEVER-if you have Excel insert a line, the program will run regression on the data to put in the line and thus you will NOT have fulfilled the requirements.) 2. Use the high-low method to estimate the cost behavior for the clinic's administrative costs. Clearly label the final cost formula. 3. Perform a least squares regression analysis on the data to estimate the cost behavior. Clearly present the final cost formula. Include a brief analysis of the R2 factor for the regression on your Excel sheet. Part B: Provide Useful Information Prepare a to 1 page memo to James McCormick that communicates the results of your analysis. You should include in your memo observations about the likely cost behavior pattern, the cost equations developed under each of the methods from Part A and how a cost equation is to be used (i.e. what are the variables involved and how is the cost equation used to estimate costs). Include a brief description of each method used that includes the advantage and disadvantage of each method presented. Finally, provide a recommendation of which equation you suggest should be used for decision-making and why it is the best in this specific scenario
