Warburton Health Centre provides medical imaging services: X-Rays, Ultrasound, CT Scan and MRI. The Health Centre would like to allocate costs using Activity Based Costing. Costs and cost drivers are identified as: Testing - Number of procedures Sanitation - Cleaning in minutes Preparation - Set up in minutes The excel spreadsheet provided with this assignment is reporting the following information: Column A reports the type of service Column B reports the number of tests performed for each service Column C and D report the minutes each test used for cleaning and set up. 1. Using the Sum If Formula, create a table that reports the total number of procedures, sanitation (in minutes) and the set up (in minutes) each service used for the year for each service. Ensure to provide a total for each activity and ensure each column is appropriately labelled. 2. Actual costs are as reported: Testing - $1,210,000, Sanitation - $836,700, Preparation $619,300. Create a second table that reports total costs. 3. Using the tables from requirement 1 and 2, assign costs to each service area. Ensure to report total costs for each type of service (for example, report total costs assigned to X-ray). All columns should be appropriately labelled. 4. Under simple costing, each service area was assigned the following cost: X-ray, $715,000 Ultrasound, $780,000 CT Scan, $642,000 MRI, S529,000 Create a fourth table, that reports the costs using the simple costing method and the one from the third table. Using the If formula, identify which service area was under costed and which was over costed. Set-up - Minutes Activity Xray Number of Procedures Cleaning - Minutes 4 24 20 Xray 48 40 CT Scan 28 420 Uhrasound Xray MRI 11 55 165 25 13 65 130 Ultrasound 20 100 300 Xray 54 45 Xray CT Scan 30 25 110 165 Ultrasound 13 65 195 MRI 12 120 MRI 10 100 Xray 42 35 MRI 25 50 MRI 40 80 Xray 30 25 Xray Xray CT Sem Utrasound Xray Xruy Xray CT Scan 24 20 6. 60 90 10 150 450 42 35 12 10 20 30 Utrasound 55 165 Xray 25 MRI 13 65 130 Ultrasound 10 150 Xray 18 15 Xray 6 36 30 CT Scan 70 105 Ultrasound 120 MRI 12 60 120 MRI 10 50 100 Xruy 42 35 MRI 35 175 350 MRI 18 190 380 Xruy Xray 25 10 60 50 Xray 36 30 CT Scan 6 60 90 Ultrasound 10 30 Xray MRI 48 40 38 190 380 Xray 30 25 Xray 12 Xray CT Scan 18 15 30 450 Utrasound 65 325 975 Xrny Xray Xruy CT Sem 54 45 36 30 42 35 33 330 495 Ultrasound 55 165 Xray 25 MRI 13 130 Utrasound 20 100 300 Xray Xray CT Scan 54 45 18 15 35 350 325 Utrasound 65 325 975 MRI 7 35 70 MRI 10 50 100 Xray 3 18 15 MRI 11 55 110 Xray 2 12 10 Xray 9. 54 45 CT Scan 37 370 555 Ultrasound 20 100 300 Xray 39 234 195 MRI 8 40 80 Xray ray 5 30 25 11 66 55 Xray 13 78 65 CT Scan 36 360 540 Ultrasound 12 60 180 Xray Xray 9. 54 45 36 30 Ultrasound 65 325 975 Xray 39 234 195 MRI 3. 15 30 ray Xray Xray 5 30 25 22 132 110 13 78 65 CT Scan 40 400 600 Ultrasound 65 325 975 Xray 10 60 50 Xray Xray Xray 6. 36 30 6 36 30 17 102 85 CT Scan 29 290 435 Ultrasound 7 35 105 Xray 30 25 MRI 20 40 Ultrasound 5 25 75 Xray 7 42 35