ABC Air Conditioning Inc. (ABC Airis an air conditioner production company who fabricates the several models of air conditioners. ABC has been attempting to improve their cost efficiency and have heard that activity-based costing may provide some insight into the cost behaviour of their product line. To start the process of calculating the activity-based cost analysis,ABC Air compiled a summary of the following information list of the overhead costs (Table 1), production data (Table 2), proposed cost drivers (Table 3), and an activity matrix that describes the use of each overhead expense by each cost pool. (Table 4) You have been asked to complete this analysis of cost data and will need to complete the following calculations (these analyses should be shown in clear/logical spreadsheet tables): 1 Calculate the plant wide overhead allocation rate using direct labour hours as the driver. 2. Calculate the overhead allocation for each product, total cost for each product, and profit (in %#1) for each product using the plant wide rate. Calculate the overhead allocation for each product using activity-based costing. Calculate total cost ofeach product and profit (in %) for each product based on activity-based costing 3. 4. Table 1 Overhead Costs OH Catego Indirect Labour Computer & Software Product Transportation Ener Facility & Vehicle Rent Business & Training Travel Miscellaneous Maintenance Depreciation Advertisin Office & Utilities TOTAL Cost ($) 3065590 804546 351780 187660 182457 72600 72028 66000 53020 44000 4785 4904466 Table 2 Production Data Cost Parameter Production Volume Unit Selling Price Material cost/unit Total direct labour hours Total direct labour cost Total machine hours Products administered Setup time Production Runs Receipts Distance Customer Contacts 5-ton 3580 1200 665 59660 1020783 10023 6-ton 1193 1365 665 26520 453757 4455 7.5 ton 4402 1575 732 83840 1434502 14085 10-ton 4618 2214 2153 100752 1723867 20822 12.5 ton 1122 3420 2446 22440 383948 4638 15-ton 1264 4801 3263 27576 471825 7043 20-ton 1880 4905 3012 50136 857827 12806 rs 8 153 184 500000 150 8 600 700 900000 547 8 240 200 600000 245 12 120 150 500000 120 12 554 845 10000000 654 12 570 178 700000 347 12 451 602 584015 470 hrs Table 3 Cost Drivers Cost Pool Machines Data record maintenance Material handling Product changeover Scheduling and production preparation Raw material receiving & Handling Number of receipts Product Shipment Customer Service Cost Driver Machine hours Number of products administered Production volumee Setup time (hours) Number of production runs Distance (km) Number of customer contacts Table 4 Activity Matrix ㄩㄧ Cost Pool Machines Data record maintenance Material handlin Product changeover Scheduling and production preparation Raw material receiving & Handlin Product Shipment Customer Service Total 12% 12% 10% 2% 190 15% 20% 100%| 28% 100% | 100% 15% 7% 13% 7% 10% 13% 12% 23% 100% 100% 20% 690 3% 3% 3% 60% 3% 3% 3% 55% 25% 9% 8% 35% 65% 40% 60% 100% 100% | 100% 100% | 100% | 100% 100% | 100% | 100% | 100% | 100% | ABC Air Conditioning Inc. (ABC Airis an air conditioner production company who fabricates the several models of air conditioners. ABC has been attempting to improve their cost efficiency and have heard that activity-based costing may provide some insight into the cost behaviour of their product line. To start the process of calculating the activity-based cost analysis,ABC Air compiled a summary of the following information list of the overhead costs (Table 1), production data (Table 2), proposed cost drivers (Table 3), and an activity matrix that describes the use of each overhead expense by each cost pool. (Table 4) You have been asked to complete this analysis of cost data and will need to complete the following calculations (these analyses should be shown in clear/logical spreadsheet tables): 1 Calculate the plant wide overhead allocation rate using direct labour hours as the driver. 2. Calculate the overhead allocation for each product, total cost for each product, and profit (in %#1) for each product using the plant wide rate. Calculate the overhead allocation for each product using activity-based costing. Calculate total cost ofeach product and profit (in %) for each product based on activity-based costing 3. 4. Table 1 Overhead Costs OH Catego Indirect Labour Computer & Software Product Transportation Ener Facility & Vehicle Rent Business & Training Travel Miscellaneous Maintenance Depreciation Advertisin Office & Utilities TOTAL Cost ($) 3065590 804546 351780 187660 182457 72600 72028 66000 53020 44000 4785 4904466 Table 2 Production Data Cost Parameter Production Volume Unit Selling Price Material cost/unit Total direct labour hours Total direct labour cost Total machine hours Products administered Setup time Production Runs Receipts Distance Customer Contacts 5-ton 3580 1200 665 59660 1020783 10023 6-ton 1193 1365 665 26520 453757 4455 7.5 ton 4402 1575 732 83840 1434502 14085 10-ton 4618 2214 2153 100752 1723867 20822 12.5 ton 1122 3420 2446 22440 383948 4638 15-ton 1264 4801 3263 27576 471825 7043 20-ton 1880 4905 3012 50136 857827 12806 rs 8 153 184 500000 150 8 600 700 900000 547 8 240 200 600000 245 12 120 150 500000 120 12 554 845 10000000 654 12 570 178 700000 347 12 451 602 584015 470 hrs Table 3 Cost Drivers Cost Pool Machines Data record maintenance Material handling Product changeover Scheduling and production preparation Raw material receiving & Handling Number of receipts Product Shipment Customer Service Cost Driver Machine hours Number of products administered Production volumee Setup time (hours) Number of production runs Distance (km) Number of customer contacts Table 4 Activity Matrix ㄩㄧ Cost Pool Machines Data record maintenance Material handlin Product changeover Scheduling and production preparation Raw material receiving & Handlin Product Shipment Customer Service Total 12% 12% 10% 2% 190 15% 20% 100%| 28% 100% | 100% 15% 7% 13% 7% 10% 13% 12% 23% 100% 100% 20% 690 3% 3% 3% 60% 3% 3% 3% 55% 25% 9% 8% 35% 65% 40% 60% 100% 100% | 100% 100% | 100% | 100% 100% | 100% | 100% | 100% | 100% |