Please complete the attached question in the excel template provided.

The budgeted information for CMS Limited for the next period is as follows: Products A B C Sales and production units 12,000 units 8,000 units 3,000 units Unit sales price 190 $220 E175 Prime cost per unit (before overheads) $52 E 85 655 Machine dept. (machine hours per unit) 2 hours 4 hours 3 hours Assembly dept. (assembly labour hours per unit) 8 hours 3 hours 1 hour The overheads allocated and apportioned to the machinery and assembly departments (including service department costs) were recovered on the basis of a machine overhead recovery rate of $20 per hour (machine department) and f 12 per labour hour in the assembly department. The Managing Director of CMS Lid has requested that you compute budgeted profit (by product) for the next period on the basis of activity-based costing and then compare these profits with the profits calculated using the conventional absorption basis currently used by CMS Ltd. You have agreed with the Managing Director that five cost pools be created as follows: Machining department Assembly department Set-up costs Maintenance requests Material handling The following is an analysis of the overheads with their appropriate cost drivers: Cost Pool Cost Driver Machining department $390,000 Machine hours $5,000 Assembly department (246,000 Labour hours 23,000 Set-up costs $720,000 No set-ups 90 Maintenance requests 1962,000 No requests for maintenance 80 Material handling E458.000 No receipts of material 250 $2,776,000 The product cost driver analysis is as follows: A B C Total Number of set-ups 40 30 20 90 Number of requests for maintenance 20 35 25 30 Number of receipts of material 100 100 50 250Activity Based Costing Instructions Populate the cells in green which have a ? In them Lee menal information mumbers Product Sales and production units 12.000 3.090 Number ofact- Unitsales price Numberof 20 35 25 80 Prime costper unit (before overhed E8 5 $5 5 Numberof 100 100 50 250 Machine dentimachine Assembly dept. (assembly labourh Step one and two -identify overhead costs and allocate into cost pools Given in question Activity Cost Pool Machiningdept 390,000 Assembly dept 246,00 Setup costs 720.000 Maintenance requests 962,000 Material handling 158,000 Step three - allocate drivers Given in question Activity Cost Pool Cost driver Machiningdept 390,000 Machine hour Assembly dept 246,000 Labourhours Setup costs 720,000 Numberof setups Maintenance requests 962,000 No of requests for maintenance Material handling 158,000 No of receipts of material 2.376.800 Step four - calculate an overhead rate for each activity Activity Cost Pool Cost driver of times activity or erhead rate per Activity Machiningdept 390,000 Machine hours 65000 Assembly dept 246,000 Labourhours 123000 Setup costs 720,000 Numberof set 90 ? Maintenance requests 962,000 No of requests 80 ? Material handling 458,000 No of receipts 250 2 2.776800 Step five - allocate overheads to products Product Machiningdept Assembly dept Set up costs Maintenance request Material handling TotalOverhead cost NoOf Units Per Unit Overhead Cost A 12000 B 8000 C 3000 Calculate a profit statement Per Unit Total Per Unit Total Per Unit Total Sales Revenue Cost of Sales Prime Cost Overhead: Machiningdept Assembly dept Setup costs Maintenance requests Material handling Total Cost Profit