Solve problem number 12 and 14 using excel.

Auto REVIEW QUESTIONS AND PROBLEMS 577 PROBLEM 14 EXHIBIT 12.14 DATA FOR TRADITIONAL AND ABC COST ALLOCATION, FOR Total Cost by Visit Type Basic Data A Initial C Regular D Intensive Total approach (Given) unit costs using a conventional 1 Number of visits (Given) Basic data and calculation of (Given) (A + B + C) Direct materials (etc.) 3,000 26,000 8,600 42,600 AWN Direct labor $40,000 $50,000 $65,000 $155,000 Estimated overhead (total is a given) $100,000 $120,000 $280,000 $500,000 UT Cost per visit: conventional method $229,000 (1) Basic data: annual projections of overhead costs by activity Cost Driver Information K Cost Driver Units Activity (Given) (Given) Given) 6 Intake $34,000 New visits 3,550 new visits 7 Medical records $35,000 Time spent 2,390 hours 8 Billing $65,000 Time spent 5,400 hours 9 Other $95,000 Visits 42,600 visits 10 Total $229,000 Additional basic data (2) Basic data: actual annual operating results of cost drivers to be used to assign ABC overhead cost Visit Type P O M N Total Initial Regular Intensive (Given) (M + N + 0) (Given) (Given) 3,550 350 700 2,500 2,390 1,150 11 New visits 640 600 5,400 1,200 12 Medical records 1,000 42,600 3,200 8,600 26,000 13 Billing 8,000 14 OtherREVIEW QUESTIONS AND PROBLEMS 575 EXHIBIT 12.12 BASIC STATISTICS FOR COST ALLOCATION IN A SURGERY CLINIC, FOR PROBLEM 12 A B Square Feet Dept. Costs Lab Tests Utilities $300,000 Administration 3,000 $700,000 Laboratory 4,000 $800,000 Day-op suite 5,000 $1,800,000 6.000 Cystoscopy $600,000 2,000 2,000 2,000 Endoscopy 2,500 $650,000 10,000 Total $4,850,000 16,500 fewer staff would be needed, and direct costs could be reduced by $40,000 ($20,000 feels that the day-op area is underutilized and that 200 square it when excess capacity was needed. Assuming equally between the endoscopy and ribed in part b, what would his scenario?C. Bobbie Jones, the manager of the endoscopy suite, is concerned about adding more space. She contends that if the cystoscopy and endoscopy units were combined, fewer staff would be needed, and direct costs could be reduced by $50,000 ($25,000 in each unit). She also feels that the day-op area is underutilized, and that 500 square feet could be used by a combined unit when excess capacity was needed. Assuming that the 500 square feet were to be allocated equally between the endoscopy and cystoscopy suites, in addition to the renovations as described in part b, what would the total allocated costs for each of these two services be under this scenario? 12. Cost allocation. Use the information in Exhibit 12.12 to answer these questions. a. Dick Clark, the new administrator for the surgical clinic, was trying to figure out how to allocate his indirect expenses. He decided to try to allocate utilities based on square footage of each department, to allocate administration based on direct costs, and to allocate laboratory based on tests. How would indirect costs be distributed as a result? b. Joe Ryan, the director of laboratories, was given approval to add 250 square feet of space to the lab by expanding into the day-op suite, which lost the space. What will his new fully allocated expenses be? (Assume that there are no new additional costs incurred by adding the 250 square feet.) c. Mary Jones, the manager of the endoscopy suite, is concerned about adding more space. She contends that if the cystoscopy and endoscopy units were combined,IONS AND PROBLEMS R PROBLEM 12 ATISTICS FOR COST ALLOCATION IN A SURGERY CLINIC. Utilities Square Feet Dept. Costs Lab Tests Administration Laboratory $300,000 3,000 Day-op suite 4,000 $700,000 Cystoscopy 5,000 $800,000 Endoscopy 2,000 $1,800,000 6.000 2,500 $600,000 2,000 Total 16,500 $650,000 2.000 $4,850,000 10,000 fewer staff would be needed, and direct costs could be reduced by $40,000 ($20,000 in each unit). She also feels that the day-op area is underutilized and that 200 square feet could be used by a combined unit when excess capacity was needed. Assuming that the 200 square feet were to be allocated equally between the endoscopy and cystoscopy suites, in addition to the renovations as described in part b, what would the total allocated costs for each of these two services be under this scenario? 13. Traditional and activity-based costing. Use the information in Exhibit 12.13 to answer these questions. a. What is the per unit cost of an initial, regular, and intensive visit using the conven- tional and ABC approaches? b. What is the total cost of initial, regular, and intensive visits using the conventional and ABC approaches? 14. Traditional and activity-based costing. Use the information in Exhibit 12.14 to answer these questions. a. What is the per unit cost of an initial, regular, and intensive visit using the conven- tional and ABC approaches? b. What is the total cost of initial, regular, and intensive visits using the conventional and ABC approaches