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 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)

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:

	B300				T500				Total
Direct materials	$	400,700			$	162,500			$	563,200
Direct labor	$	120,200			$	42,700				162,900
Manufacturing overhead										494,269
Cost of goods sold									$	1,220,369

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:

	Manufacturing				Activity
Activity Cost Pool (and Activity Measure)	Overhead					B300			T500			Total
Machining (machine-hours)	$	200,299				90,700			62,200			152,900
Setups (setup hours)		132,870				79			230			309
Product-sustaining (number of products)		100,200				1			1			2
Other (organization-sustaining costs)		60,900				NA			NA			NA
Total manufacturing overhead cost	$	494,269

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))

Answer 1

		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