Unit 2 - Overhead Allocation and Activity-Based Costing Problem 4 - Activity Based Costing Orange Computer Company produces two types of laptop computers. Model J3MP has a 15" screen and a 8MB hard disk. Model P5NX has a 20" screen and a 20MB hard disk. The Model 13MP computer sells for $1,200.00 per unit and the Model P5NX sells for $2,250.00 per unit. Current planned production: Model J3MP - 5,000 units Model P5NX - 4,500 units Cost Pool Overhead Activity Driver Expected Application Amount Amount Rate Assembly Department Assembling 1,480,000 Machine Hours 32,000 hours Machine Setups 398,275 Number of Setups 445 setups Material Handling 76,800 No. of Production Runs 96 Packaging Department Inspecting and Packaging $324,700 Labor Hours 95,500 Shipping $105,000 No. of Shipments 600 Total $2,384,775 Job or Assembling Number of Production Labor No. of Product Setups Runs Hours Shipments Product 6,000 45 46 60,000 200 J3MP Product 26,000 400 50 35,500 400 P5NX Total 32,000 445 96 95,500 600 Required: Using Excel, prepare worksheet to compute the cost per unit for each product using Activity Based Costing. Compute the Profit Margin per unit and Profit Margin Percentage for each product. Use the table provided as a guideline to organize your worksheet. . Save your completed file using the following filename: MacBook AirAutoSave OFF Activity Based Costing - Template (1) Home Insert Draw Page Layout Formulas Data Review View Tell me Arial v 10 A A ab Wrap Text v General v Merge & Center $ ~ % " 00 -8 Conditional Format Cell Insert Delete Paste DVAv Forma B I U V V = Formatting as Table Styles F31 X V fx B N O C D E F G H K L M 2 Computation of Rates Cost Pool Activity Activity Cost Pool Amount Base Amount Rate 9 10 11 Cost - Product ABC 12 13 14 15 16 17 18 19 20 21 22 23 24 5 Cost of Product XYZ 26 27 28 29 30 31 32 33 34 35 36 37 39 40 Assumptions 41 ABC XYZ 42 Direct Materials 35,200 28,700 43 Direct Labor 62, 150 50,500 44 45 46 47 48 Activity Based Costing Template + Ready