Pasta Maker Segment
Income Statement
Year Ended December 31, 2020
Sales		$1,000,000
Cost of goods sold
Direct materials	$200,000
Direct labor	50,000
Manufacturing overhead	320,000	570,000
Gross margin		430,000
Selling and administrative expenses
Variable Selling expenses	40,000
Fixed Selling expenses	180,000
Total administrative expenses	120,000	340,000
Net operating income		$90,000

My colleagues and I, with assistance of KPMG, have identified five activity cost pools and decided the distribution of overhead costs (manufacturing and nonmanufacturing) across the five pools below:

Activity Cost Pools	Machining	Purchase orders	Design alterations	Customer relations	Segment sustaining	Total
Manufacturing overhead	30%	10%	40%	0%	20%	100%
Fixed selling expenses	0%	25%	30%	35%	10%	100%
Total administrative expenses	25%	0%	0%	30%	45%	100%

Information on the costs associated with the three activity cost pools can found in the traditional income statement reported on the previous page.

Model-specific data:

	Standard	Advanced	Total
Sales	$600,000	$400,000	$1,000,000
Direct costs
Direct materials	125,000	75,000	200,000
Direct labor	30,000	20,000	50,000
Variable selling expenses	25,000	15,000	40,000
Units sold	5,000	2,000
Unit selling price	$120	$200
Machine hours per unit	0.4 MH	0.6 MH

Data from the activity-based costing system:

		Expected activity
Activity cost pool	Activity measure	Standard	Advanced	Total
Machining	number of machine hours	2,000	1,200	3,200
Purchase orders	number of purchase orders	200	300	500
Design alterations	Number of design alterations	0	400	400
Customer relations	Number of customers	20	80	100
Segment sustaining	Not applicable

Please follow Exhibit 7-7 (pp. 314 in the textbook) and make a chart showing the calculation of the four activity rates under the activity-based costing system. You must prepare your calculations in EXCEL. Show your work.