Can you put this in excel and show the formulas of how you\'re getting the answe

Question

Can you put this in excel and show the formulas of how you're getting the answers please!

Hi-Tek Manufacturing Inc. makes two types of industrial component parts—the B300 and the T500. An absorption costing income statement for the most recent period is shown below:

Hi-Tek produced and sold 60,300 units of B300 at a price of $20 per unit and 12,800 units of T500 at a price of $40 per unit. The company’s traditional cost system allocates manufacturing overhead to products using a plantwide overhead rate and direct labor dollars as the allocation base. Additional information relating to the company’s two product lines is shown below:

The company has created an activity-based costing system to evaluate the profitability of its products. Hi-Tek’s ABC implementation team concluded that $53,000 and $107,000 of the company’s advertising expenses could be directly traced to B300 and T500, respectively. The remainder of the selling and administrative expenses was organization-sustaining in nature. The ABC team also distributed the company’s manufacturing overhead to four activities as shown below:

Required

1. Compute the product margins for the B300 and T500 under the company’s traditional costing system. (Do not round your overhead rate. Round your other intermediate and final answers to the nearest whole number.)

2. Compute the product margins for B300 and T500 under the activity-based costing system. (Negative product margins should be indicated by a minus sign. Round your intermediate calculations to 2 decimal places.)

3. Prepare a quantitative comparison of the traditional and activity-based cost assignments. (Do not round your overhead rate. Round your other intermediate calculations and final answers to the nearest whole number. Round your "Percentage" answer to 1 decimal place. (i.e. .1234 should be entered as 12.3))

Hi-Tek Manufacturing Inc.
Income Statement Sales $ 1,718,000 Cost of goods sold 1,220,369 Gross margin 497,631 Selling and administrative expenses 650,000 Net operating loss $ (152,369)

Explanation / Answer

B300 T500 Total Units 60300 12800 Sales Price 20.00 40.00 Direct Material 400700 162500 563200 Total Manufacturing OH 494269 Direct Labor 120200 42700 162900 Total Direct Labor Dollars 162900 Manufacturing OH 494269 Overhead Per Dollars 3.03 Cost of Goods Sold 1220369 1 B300 T500 Sales Price 20.00 40.00 Less: Direct Material 6.65 12.70 Less: Direct Labor 1.99 3.34 Less: Maufacturing OH 6.05 10.12 (DL Dollars* OHRate) Product Margin 5.31 13.85 19.16 2 B300 T500 Activity Pools Amt B300 T500 Total Per Driver Sales Price 20.00 40.00 Machining 200299 90700 62200 152900 1.31 Less: Direct Material 6.65 12.70 Setups 132870 79 230 309 430 Less: Direct Labor 1.99 3.34 Product Sustaining 100200 1 1 2 50100 Less: Maufacturing OH 3.36 18.01 From Right Side Other 60900 NA NA NA Product Margin 8.00 5.96 Total M OH 494269 Allocation: B300 T500 Machining 118817 81482 (Activity Driver* Rate) Setups 33970 98900 (Activity Driver* Rate) Product Sustaining 50100 50100 (Activity Driver* Rate) Total M OH 202887 230482 Units 60300 12800 B300 T500 Manufacturing OH 3.36 18.01 60300 12800 3 B300 T500 Total Traditional Cost System Amount % Amount % Amount Direct Material 4,00,700 45.2% 1,62,500 48.5% 5,63,200 Direct Labor 1,20,200 13.6% 42,700 12.8% 1,62,900 Manufacturing OH 3,64,709 41.2% 1,29,560 38.7% 4,94,269 Total Cost assigned to products 8,85,609 100.0% 3,34,760 100.0% 12,20,369 Traditional Cost System Amount % Amount % Amount Direct Costs: Direct Material 4,00,700 55.4% 1,62,500 37.3% 5,63,200 Direct Labor 1,20,200 16.6% 42,700 9.8% 1,62,900 Indirect Costs: Machining 1,18,817 16.4% 81,482 18.7% 2,00,299 Setups 33,970 4.7% 98,900 22.7% 1,32,870 Product Sustaining 50,100 6.9% 50,100 11.5% 1,00,200 Total Cost assigned to products 7,23,787 100.0% 4,35,682 100.0% 11,59,469 Costs not assigned to products: Others 60900 Total Cost 12,20,369 Note: Rounding off has not been done

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.