Please answer those green box questions.

A B D E F G H 1 J L M N Req. 1: Enter 160 for the number of filing cabinets ordered in the green cell on the left. The customer margin under activity based costing should now be $7,640 and the traditional costing product margin should be ($21,600). If you don't get these answers, correct your formulas on the previous page and copy the corrections the other pages using the copy formulas button. 1 Chapter 7: Applying Excel 2 3 Data 4 5 Manufacturing overhead 6 Selling and administrative overhead 7 $500,000 $300,000 Why has the customer margin under activity-based costing more than doubled when the units ordered doubled? Other Assembling Units 50% 10% 1,000 units Processing Orders 35% 45% 250 orders Supporting Customers 5% 25% 100 customers 10% 20% Why has the traditional costing product margin exactly doubled from ($10,800) to ($21,600)? 8 9 Manufacturing overhead 10 Selling and administrative overhead 11 Total activity 12 13 14 OfficeMart orders: 15 Customers 16 Orders 17 Number of filing cabinets ordered in total 18 Selling price 19 Direct materials 20 Direct labor 21 22 23 24 Review Problem: Activity-Based Costing 25 Perform the first stage allocations 1 customer 4 orders 160 units $595 $180 $50 Which costing system provides a more accurate picture of what happens to profits as the number of units ordered increases? $ Processing Orders $ 175,000 135,000 $ 310,000 26 27 Manufacturing overhead 28 Selling and administrative overhead 29 Total cost $ Supporting Customers 25,000 75,000 100,000 Assembling Units $ 250,000 30,000 $ 280,000 $ Other 50,000 60,000 110,000 Total 500,000 300,000 800,000 $ $ $ 30 Total Cost $280,000 $310,000 $100,000 Total Activity 1,000 units 250 orders 100 customers Activity Rate $280 per unit $1,240 per order $1,000 per customer Activity Rate $280 per unit $1,240 per order $1,000 per customer Activity 160 units 4 orders 1 customer ABC Cost $44,800 $4,960 $1,000 1 Compute the activity rates 2 Activity Cost Pools 3 Assembling units 4 Processing orders 5 Supporting customers 6 7 Compute the overhead cost attributable to the OfficeMart orders 8 Activity Cost Pools 9 Assembling units Processing orders 1 Supporting customers 2 3 Determine the customer margin for the OfficeMart orders under Activity-Based Costing 4 Sales 5 Costs: 6 Direct materials 7 Direct labor 8 Unit-related overhead 9 Order-related overhead o Customer-related overhead 1 Customer margin 2 3 Determine the product margin for the Office Mart orders under a traditional cost system 4 Manufacturing overhead 5 Total activity 6 Manufacturing overhead per unit $ 95,200 $ 28,800 8,000 44,800 4,960 1,000 87,560 7,640 $ $500,000 1,000 units $500 per unit $ 95,200 $ 8 Sales 9 Costs: Direct materials 1 Direct labor 2 Manufacturing overhead 3 Traditional costing product margin 4 5 6 28,800 8,000 80,000 116,800 (21,600) $ A B D E F G H 1 J K L M N 1 Chapter 7: Applying Excel Req. 3: Enter the percentage of selling and administrative overhead attributable to processing orders of 30% and the percentage attributable to supporting customers to 40% in the green cells to the left. 2 3 Data Relative to req. 2, what has happened to the customer margin under activity-based costing? Why? 4 5 Manufacturing overhead 6 Selling and administrative overhead 7 $500,000 $300,000 Supporting Customers 5% Other 8 9 Manufacturing overhead 10 Selling and administrative overhead 11 Total activity 12 Assembling Units 50% 10% 1,000 units Processing Orders 35% 30% 250 orders 10% 20% 40% 100 customers Relative to req. 2, what has happened to the product margin under the traditional cost system? Why? 1 customer 20 orders 13 14 OfficeMart orders: 15 Customers 16 Orders 17 Number of filing cabinets ordered in total 18 Selling price 19 Direct materials 20 Direct labor 80 units $795 $185 $90 21 22 23 23 24 Review Problem: Activity-Based Costing 25 Perform the first stage allocationis Processing Supporting Assembling Units Orders Customers $ 250,000 $ 175.000 $ 25,000 $ 30,000 90.000 120.000 $ 280,000 S 265.000 $ 145,000 $ Other 50,000 $ 60,000 110,000 S Total 500.000 300,000 800,000 Total Activity Total Cost $280,000 $265,000 $145,000 1,000 units 250 orders 100 customers Activity Rate $280 per unit $1,060 per order $1,450 per customer Activity Rate $280 per unit $1,000 per order $1,450 per customer Activity 80 units 20 orders 1 customer ABC Cas $22.400 $21,200 $1,450 $ 63.600 26 27 Manufacturing overhead 28 Selling and administrative overhead 29 Total cost 30 31 Compute the activity rates 32 Activity Cost Pools 33 Assembling units 34 Processing orders 35 Supporting customers 36 37 Compute the overhead cost attributable to the Office Mart orders 38 Activity Cost Pools 39 Assembling units 40 Processing orders 41 Supporting customers 42 43 Determine the customer margin for the Office Mart orders under Activity-Based Costing 44 Salas 45 Costs: 46 Direct materials 47 Direct labor 48 Unit-related overhead 49 Order-related overhead 50 Customer-related overhead 51 Customer margin 52 53 Determine the product margin for the OfficeMart orders under a traditional cost system 54 Manufacturing overhead 55 Total activity 56 Manufacturing overhead per unit 57 58 Sales 59 Costs: 60 Direct materials 61 Direct labor 62 Manufacturing overhead 63 Traditional costing product margin 64 65 $ 14,800 7,200 22,400 21,200 1.450 67,050 (3 450) $ $500,000 1,000 units $500 per unit S 63,600 $ 14,BOO 7,200 40,000 62.000 $ 1.600 A B D E F G H 1 J L M N Req. 1: Enter 160 for the number of filing cabinets ordered in the green cell on the left. The customer margin under activity based costing should now be $7,640 and the traditional costing product margin should be ($21,600). If you don't get these answers, correct your formulas on the previous page and copy the corrections the other pages using the copy formulas button. 1 Chapter 7: Applying Excel 2 3 Data 4 5 Manufacturing overhead 6 Selling and administrative overhead 7 $500,000 $300,000 Why has the customer margin under activity-based costing more than doubled when the units ordered doubled? Other Assembling Units 50% 10% 1,000 units Processing Orders 35% 45% 250 orders Supporting Customers 5% 25% 100 customers 10% 20% Why has the traditional costing product margin exactly doubled from ($10,800) to ($21,600)? 8 9 Manufacturing overhead 10 Selling and administrative overhead 11 Total activity 12 13 14 OfficeMart orders: 15 Customers 16 Orders 17 Number of filing cabinets ordered in total 18 Selling price 19 Direct materials 20 Direct labor 21 22 23 24 Review Problem: Activity-Based Costing 25 Perform the first stage allocations 1 customer 4 orders 160 units $595 $180 $50 Which costing system provides a more accurate picture of what happens to profits as the number of units ordered increases? $ Processing Orders $ 175,000 135,000 $ 310,000 26 27 Manufacturing overhead 28 Selling and administrative overhead 29 Total cost $ Supporting Customers 25,000 75,000 100,000 Assembling Units $ 250,000 30,000 $ 280,000 $ Other 50,000 60,000 110,000 Total 500,000 300,000 800,000 $ $ $ 30 Total Cost $280,000 $310,000 $100,000 Total Activity 1,000 units 250 orders 100 customers Activity Rate $280 per unit $1,240 per order $1,000 per customer Activity Rate $280 per unit $1,240 per order $1,000 per customer Activity 160 units 4 orders 1 customer ABC Cost $44,800 $4,960 $1,000 1 Compute the activity rates 2 Activity Cost Pools 3 Assembling units 4 Processing orders 5 Supporting customers 6 7 Compute the overhead cost attributable to the OfficeMart orders 8 Activity Cost Pools 9 Assembling units Processing orders 1 Supporting customers 2 3 Determine the customer margin for the OfficeMart orders under Activity-Based Costing 4 Sales 5 Costs: 6 Direct materials 7 Direct labor 8 Unit-related overhead 9 Order-related overhead o Customer-related overhead 1 Customer margin 2 3 Determine the product margin for the Office Mart orders under a traditional cost system 4 Manufacturing overhead 5 Total activity 6 Manufacturing overhead per unit $ 95,200 $ 28,800 8,000 44,800 4,960 1,000 87,560 7,640 $ $500,000 1,000 units $500 per unit $ 95,200 $ 8 Sales 9 Costs: Direct materials 1 Direct labor 2 Manufacturing overhead 3 Traditional costing product margin 4 5 6 28,800 8,000 80,000 116,800 (21,600) $ A B D E F G H 1 J K L M N 1 Chapter 7: Applying Excel Req. 3: Enter the percentage of selling and administrative overhead attributable to processing orders of 30% and the percentage attributable to supporting customers to 40% in the green cells to the left. 2 3 Data Relative to req. 2, what has happened to the customer margin under activity-based costing? Why? 4 5 Manufacturing overhead 6 Selling and administrative overhead 7 $500,000 $300,000 Supporting Customers 5% Other 8 9 Manufacturing overhead 10 Selling and administrative overhead 11 Total activity 12 Assembling Units 50% 10% 1,000 units Processing Orders 35% 30% 250 orders 10% 20% 40% 100 customers Relative to req. 2, what has happened to the product margin under the traditional cost system? Why? 1 customer 20 orders 13 14 OfficeMart orders: 15 Customers 16 Orders 17 Number of filing cabinets ordered in total 18 Selling price 19 Direct materials 20 Direct labor 80 units $795 $185 $90 21 22 23 23 24 Review Problem: Activity-Based Costing 25 Perform the first stage allocationis Processing Supporting Assembling Units Orders Customers $ 250,000 $ 175.000 $ 25,000 $ 30,000 90.000 120.000 $ 280,000 S 265.000 $ 145,000 $ Other 50,000 $ 60,000 110,000 S Total 500.000 300,000 800,000 Total Activity Total Cost $280,000 $265,000 $145,000 1,000 units 250 orders 100 customers Activity Rate $280 per unit $1,060 per order $1,450 per customer Activity Rate $280 per unit $1,000 per order $1,450 per customer Activity 80 units 20 orders 1 customer ABC Cas $22.400 $21,200 $1,450 $ 63.600 26 27 Manufacturing overhead 28 Selling and administrative overhead 29 Total cost 30 31 Compute the activity rates 32 Activity Cost Pools 33 Assembling units 34 Processing orders 35 Supporting customers 36 37 Compute the overhead cost attributable to the Office Mart orders 38 Activity Cost Pools 39 Assembling units 40 Processing orders 41 Supporting customers 42 43 Determine the customer margin for the Office Mart orders under Activity-Based Costing 44 Salas 45 Costs: 46 Direct materials 47 Direct labor 48 Unit-related overhead 49 Order-related overhead 50 Customer-related overhead 51 Customer margin 52 53 Determine the product margin for the OfficeMart orders under a traditional cost system 54 Manufacturing overhead 55 Total activity 56 Manufacturing overhead per unit 57 58 Sales 59 Costs: 60 Direct materials 61 Direct labor 62 Manufacturing overhead 63 Traditional costing product margin 64 65 $ 14,800 7,200 22,400 21,200 1.450 67,050 (3 450) $ $500,000 1,000 units $500 per unit S 63,600 $ 14,BOO 7,200 40,000 62.000 $ 1.600