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. Part C: Ethical Dilemma After receiving Megan's memo, James called Megan to discuss the matter. The following exchange occurred James: 'As you know Megan, I was never in favor of this clinic. It's going to be a drag on our administrative staff, and we'd have been far better off keeping the pediatrics services here in the hospital. Megan: I was aware that you felt the clinic was a mistake. Of course, the Board of Trustees had other issues to consider. I believe the Board felt the clinic should be built to make pediatric care more accessible to the economically depressed area on the other side of the city." James: That's true, but the Board doesn't realize how difficult it's going to make life for us here in the hospital. In any case, I called to tell you that when you and I report to the Board next week, I am going to recommend that the clinic be shut down. I want you to support my recommendation with one of your cost estimates showing that administrative costs will soar at high activity levels. Megan: We've already decided that the likely range of patients will not include those levels, so it seems like that would not be the most useful information to present to the Board. James: 'm counting on you to be a team player Megan. I'll see you at the meeting. Feeling defeated, Megan shares the situation with a trusted friend that evening who is also an accountant. Required: Putting yourself in the shoes of Megan's friend, what would you advise her to do? As this is a professional accounting ethical dilemma, include in your response references to any particular tenets of the Institute of Management Accountants (IMA) Statement of Ethical Professional Practice that may apply to Megan's situation 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. Part C: Ethical Dilemma After receiving Megan's memo, James called Megan to discuss the matter. The following exchange occurred James: 'As you know Megan, I was never in favor of this clinic. It's going to be a drag on our administrative staff, and we'd have been far better off keeping the pediatrics services here in the hospital. Megan: I was aware that you felt the clinic was a mistake. Of course, the Board of Trustees had other issues to consider. I believe the Board felt the clinic should be built to make pediatric care more accessible to the economically depressed area on the other side of the city." James: That's true, but the Board doesn't realize how difficult it's going to make life for us here in the hospital. In any case, I called to tell you that when you and I report to the Board next week, I am going to recommend that the clinic be shut down. I want you to support my recommendation with one of your cost estimates showing that administrative costs will soar at high activity levels. Megan: We've already decided that the likely range of patients will not include those levels, so it seems like that would not be the most useful information to present to the Board. James: 'm counting on you to be a team player Megan. I'll see you at the meeting. Feeling defeated, Megan shares the situation with a trusted friend that evening who is also an accountant. Required: Putting yourself in the shoes of Megan's friend, what would you advise her to do? As this is a professional accounting ethical dilemma, include in your response references to any particular tenets of the Institute of Management Accountants (IMA) Statement of Ethical Professional Practice that may apply to Megan's situation