I finished the Excel and I need these questions to be answered:1. Finish the Excel first. How much cost will be the Per Unit Cost allocation for product #1 at the end of ABC method of allocation cost per unit?2. Finish the Excel first. How much cost will be the Per Unit Cost allocation for product #2 at the end of your ABC method of allocation cost per unit?
Ace Company produces two products (#1 and #2); has two additional cost objects relating to customer service and warranty support (A and B); and engages in three activities related to design, production, and testing (X, Y, and Z). Follow the steps outlined below to apply activity-based costing to Ace Company. If any red boxes remain, you have not correctly completed the process. ALLOCATE COSTS: For the following four cost categories (indirect materials, maintenance, other, and SG&A), make assumptions about the percentages to be assigned to each of the activities (X, Y,Z, or unallocated). Use the available pick lists from within each boxed area and enter your selection. Your total allocation must equal 100% for each row. Activity Cost Pools X Y Z Unallocated Total Indirect materials 0.10 0.20 0.30 0.40 1.00 Maintenance 0.20 0.30 0.40 0.10 1.00 Other 0.30 0.30 0.10 1.00 SG&A 0.20 0.20 0.20 0.40 1.00 The percentages you selected above are next used in the table below to allocate the total costs (assumed) among the various activities (experiment with alternative percentages above and observe how this changes the cost allocations below): Activity Cost Pools Total Cost Unallocated Indirect materials 150,000 15,000 30,000 45,000 60,000 Maintenance 400,000 80,000 120,000 160,000 40,000 Other 75.000 22,500 22,500 22,500 7.500 SG&A 225,000 45,000 45,000 45,000 90,000 Totals 850,000 162.500 217.500 272.500 197.500 DETERMINE THE PER-ACTIVITY RATES: Each of the products and other cost objects consumed activities at the rate indicated in the table below. Use this information and cost data above to determine the per unit activity cost measures. Use the available pick lists from within each boxed area to select the correct values. Number of Activities Consumed X Z Product #1 1000 50 Product #2 2000 0 Cost Object A 0 0 10 Cost Object B 0 0 2 Totals 3000 75 12 Total Cost 162,500 217.500 272.500 Cost per Measure 54.167 2,900 22,708.333 APPLY COSTS TO OBJECTS: Given below are the costs of direct material and direct labor. Assume that Cost Object A is allocated half to Product #1 and half to Product #2. Assume that Cost Object B is fully allocated to Product #2. Use the pick lists from within the boxed areas below to apply all costs. The per unit amounts are automatically calculated. Study and review the entire process, considering the goals and objectives of ABC. Product #1 Product #2 Total Direct Materials Traces 200,000 300,000 500,000 Direct Labor Traces 90,000 410,000 500,000 Activity X $54.17 per measure 54,166.667 108,333.333 162,500 Activity Y $2,900.00 per measure 145,00 72,500 217,500 Activity Z $22,708.33 per measure 113,541.667 158,958.333 272,500 602,708.333 1,049,791.667 1,652,500 Unallocated 197,500 Total costs 1,850,000 Assumed Units 5,000 3,000 Per Unit Cost 120.542 349.931
