60.1K

Verified Solution

Link Copied!

Question 3 (66 marks) *Use excel file "Lab 3 Assignment Data File" to complete this question - this can be found on Moodle with the assignment 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, $529,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. 2 CTS 11 VE 1 5 CTS LI 13 13 7 ER 5 9 1 GT 1 VR 1 1 CTS MI FER 13 18 1 VR VE 5 CTS 2 ER 10 ST SP CE 7 CTS 1 23 M 11 13 135 MRI VR 14 MR 1 1 2 GT 11 VRE 5 5 LI . 15 V 13 a RUP ARRARO 3 VR nars Cleaning - Minutes 48 2001 SS Number of Procedures 4 8 28 11 5 13 20 9 5 II 13 12 10 65 100 54 30 110 65 60 SO 42 7 5 3 5 25 41 1 4 6 10 7 2 1 30 6 24 60 150 42 12 Set-up. Minutes 20 40 420 165 25 130 300 45 25 165 195 120 100 35 30 80 25 5 20 90 450 35 LO 3 30 165 25 130 ISO 15 30 105 120 120 100 35 350 380 25 50 30 90 30 2 11 $ 20 SS 30 65 SA Activity Xray Xray CT Scan Ultrasound Xray MRI Ultrasound Xny Xray CT Scam Ultrasound MRI MRI Xray MRI MRI Xray Xray CT SC Ultrasund Xray Xray Xray CTS Ultrasound Xray MRI Ultrasound Xray Xray CT Scan Ultrasound MRI MRI Xray MRI MRI Xray Xray Xray CTS Ultrasound Xray MRI Xray Xray Xray CT Scan Ultrasound Xmay Xray Xray CT Scan Ultra Xray MRI Ultrasound Xray Xray CT Scan Ultrasound 18 13 TO 3 6 7 8 70 40 12 10 7 15 175 190 MI 5 10 6 6 2 3 18 5 36 60 10 3 10 190 30 12 18 300 325 54 36 380 25 10 15 450 975 45 30 35 9 6 7 11 5 13 330 SS 90 65 100 54 165 25 130 300 45 20 9 35 18 350 325 13 525 975 65 70 100 15 110 MRI MRI Xray MRI Xray Xray CT Scan Ultrasound Xray MRI Xray Xray Xray CT Scan Ultrasound 7 10 3 11 2 9 37 20 39 8 5 11 Xray 13 36 12 9 6 65 39 35 50 18 55 12 54 370 100 234 40 30 66 78 360 60 54 36 325 234 15 30 132 78 400 325 60 36 36 102 290 35 30 20 25 42 Xray Ultrasound Xray MRI Xray Xray Xray CT Scan Ultrasound Xray Xray Xray Xray CT Scan Ultrasound Xray MRI Ultrasound Xray 3 10 45 555 300 195 80 25 55 65 540 180 45 30 975 195 30 25 110 65 600 975 50 30 30 85 435 105 25 40 75 35 5 22 13 40 65 10 6 6 17 29 7 5 4 5 7

Answer & Explanation Solved by verified expert

See Answer