Please see the attached file.
The following table shows the cost allocation bases used to distribute various costs among the hospital's divisions.
Cost Pool Cost Allocation Annual Cost
Building depreciation Square feet of space $190,000
Electricity Cubic feet of space 24,000
Water and sewer
Cable TV and phone
Administrator Budgeted number of employees 220,000
Public education Budgeted dollars of patient billings 40,000
Scholl physical exams
Shown below are the amounts of each allocation base associated with each division.
Square feet Cubic feet Number of Patient
General Medicine Division 15,000 135,000 30 2,000,000
Surgical Division 8,000 100,000 20 1,250,000
Medical Support Division 9,000 90,000 20 750,000
Administrative Division 8,000 75,000 30 0
TOTAL 40,000 400,000 100 4,000,000
1. Prepare a table that distributes each of the costs listed in the preceding table to the hospital's divisions.
2. Comment on the appropriateness of patient billings as the basis for distributing community outreach costs to the hospital's divisions. Can you suggest a better allocation base?
3. Is there any use in allocating utilities costs to the divisions? What purposes could such an allocation process serve?
4. Build a spreadsheet: Construct an Excel spreadsheet to solve requirement (1) above. Show how the solution will change if the following information changes: the costs incurred were $200,000, $25,000, $200,000, and $50,000 for facilities, utilities, general administration, and community outreach, respectively.
Excel file contains calculations of cost allocated to each division.