fx Chapter 7: Applying Excel A1 B A C D E G H 1 Chaoter 7: Apolving Excel Data Manufacturing overhead 5 Selling and administrative overhead $500.000 $300.000 Processing Assombling Supporting 7 Lnits Odors Customars Other 8Manufactturing overhead 50% 35% 5% 10% 25 % Selling and administrative overhead 10% 45% 20% 10 Total activity 1,000 250 100 11 units orders customers 12 13 OmceMart orders: 14 Customers 15 Orders 16 Number of filing cabinets orderad in total 17 Selling price 18 Direct materials 19 Direct labor 1 customer 4 orders 80 units $595 $180 $50 20 21 Enter a fomula into each af the cells marked wdth 22 Review Problem: Activity-Based Costing 7 balow 24 Perform the first stage allocations Assombling Processing Supporting Customars 20 26 Manutactturing overhead 27 Selling and administrative overhead 28 Total cost Lnits Odars Othen Tatal 30 Compute me acivity rates 31 Activity Cost Popls 32 Assembling units 33 Processing orders 34 Supporting customers Total Cost Total Activty Activity Rate per unit 7 units 7 orders ? per order per customer 7 customers 36 Compute me overhead cost atributable to the OmoeMart arders 37 Activity Cost Poals 38 Assembling units 39 Processing onders 40 Supporting customers ARC Cost Activity Rat Activity 7 units ? per unit per orden orders 7 ? per customer customer 42 Determine the customer margin for the omceMart orders under Acivitry-Based Costing anSales 4 Costs Direct materials Direct labor 7 Unitrelated ovethead Order-related overhead Customer related overhead O Customer margin 52 Determine the product margin for the OmceMart arders under a traditional cost system 53 Manufacttuing overhead 4G4 Total acdivity 55 Manufacturing overhead per unit FA 57 Sales cA Costs ? units per unit Direct materials 7 Direct labor c 61 Manufacturing overhead 62 Traditional costing product margin Chapter 7 Form + 23456D 2. Assume that OfficeMart places orders more frequently, but everything else remains the same. On your worksheet increase the number of orders from 4 to 13. (Prior to completing this requirement, change all worksheet figures back to their original amounts.) C A B D E 1 Chapter 7: Applying Excel 2 3 Data 4 S Manufacturing overhead 500,000 Selling and adeministrative overhead 300,000 6 Assembling Units Processing Orders Supporting Customers 7 Other 5% 8 Manufacturing overhead 50% 35% 10% 9 Selling and administrative overhead 10% 45% 25% 20% 10 Total activity 100 250 1,000 11 units orders customers 12 13 OfficeMart orders: 1 customer 14 Customers 15 Orders 13 orders 16 Number of filing cabinets ordered in total 80 units Selling price 18 Direct materials 17 $ 595 180 19 Direct labor 50 (a) What is the customer margin under activity-based costing when the number of orders increases to 13? (Enter a loss as a negative amount.) Customer margin under activity-based costing (b) What is the product margin under the traditional costing system when the number of orders increases to 13? (Enter a loss as a negative amount.) Product margin under the traditional costing system 3. OfficeMart has found an error in its data. The percentage of selling and administrative overhead attributable to processing orders should be changed to 35% from 45% and the percentage attributable to supporting customers should be changed to 35% from 25%. P A C D E Chapter 7: Applying Excel 1 2 3 Data 4 Manufacturing overhead 500,000 5 Selling and administrative overhead 300,000 6 Assembling Units Processing Orders Supporting Customers 7 Other 8 Manufacturing overhead 50% 35% 5% 10% 9 Selling and administrative overhead 10% 35% 35% 20% Total activity 10 1,000 250 100 units 11 orders customers 12 13 OfficeMart orders: 14 Customers 1 customer 15 Orders 13 orders 16 Number of filing cabinets ordered in total 80 units 17 Selling price 18 Direct materials 595 180 (a) Based on new number of orders in Requirement 2, what is the customer margin under activity-based costing when the data are corrected? (Enter a loss as a negative amount.) Customer margin under activity-based costing (b) Based on new number of orders in Requirement 2, what is the product margin under the traditional costing system when the data are corrected? (Enter a loss as a negative amount.) Product margin under the traditional costing system fx Chapter 7: Applying Excel A1 B A C D E G H 1 Chaoter 7: Apolving Excel Data Manufacturing overhead 5 Selling and administrative overhead $500.000 $300.000 Processing Assombling Supporting 7 Lnits Odors Customars Other 8Manufactturing overhead 50% 35% 5% 10% 25 % Selling and administrative overhead 10% 45% 20% 10 Total activity 1,000 250 100 11 units orders customers 12 13 OmceMart orders: 14 Customers 15 Orders 16 Number of filing cabinets orderad in total 17 Selling price 18 Direct materials 19 Direct labor 1 customer 4 orders 80 units $595 $180 $50 20 21 Enter a fomula into each af the cells marked wdth 22 Review Problem: Activity-Based Costing 7 balow 24 Perform the first stage allocations Assombling Processing Supporting Customars 20 26 Manutactturing overhead 27 Selling and administrative overhead 28 Total cost Lnits Odars Othen Tatal 30 Compute me acivity rates 31 Activity Cost Popls 32 Assembling units 33 Processing orders 34 Supporting customers Total Cost Total Activty Activity Rate per unit 7 units 7 orders ? per order per customer 7 customers 36 Compute me overhead cost atributable to the OmoeMart arders 37 Activity Cost Poals 38 Assembling units 39 Processing onders 40 Supporting customers ARC Cost Activity Rat Activity 7 units ? per unit per orden orders 7 ? per customer customer 42 Determine the customer margin for the omceMart orders under Acivitry-Based Costing anSales 4 Costs Direct materials Direct labor 7 Unitrelated ovethead Order-related overhead Customer related overhead O Customer margin 52 Determine the product margin for the OmceMart arders under a traditional cost system 53 Manufacttuing overhead 4G4 Total acdivity 55 Manufacturing overhead per unit FA 57 Sales cA Costs ? units per unit Direct materials 7 Direct labor c 61 Manufacturing overhead 62 Traditional costing product margin Chapter 7 Form + 23456D 2. Assume that OfficeMart places orders more frequently, but everything else remains the same. On your worksheet increase the number of orders from 4 to 13. (Prior to completing this requirement, change all worksheet figures back to their original amounts.) C A B D E 1 Chapter 7: Applying Excel 2 3 Data 4 S Manufacturing overhead 500,000 Selling and adeministrative overhead 300,000 6 Assembling Units Processing Orders Supporting Customers 7 Other 5% 8 Manufacturing overhead 50% 35% 10% 9 Selling and administrative overhead 10% 45% 25% 20% 10 Total activity 100 250 1,000 11 units orders customers 12 13 OfficeMart orders: 1 customer 14 Customers 15 Orders 13 orders 16 Number of filing cabinets ordered in total 80 units Selling price 18 Direct materials 17 $ 595 180 19 Direct labor 50 (a) What is the customer margin under activity-based costing when the number of orders increases to 13? (Enter a loss as a negative amount.) Customer margin under activity-based costing (b) What is the product margin under the traditional costing system when the number of orders increases to 13? (Enter a loss as a negative amount.) Product margin under the traditional costing system 3. OfficeMart has found an error in its data. The percentage of selling and administrative overhead attributable to processing orders should be changed to 35% from 45% and the percentage attributable to supporting customers should be changed to 35% from 25%. P A C D E Chapter 7: Applying Excel 1 2 3 Data 4 Manufacturing overhead 500,000 5 Selling and administrative overhead 300,000 6 Assembling Units Processing Orders Supporting Customers 7 Other 8 Manufacturing overhead 50% 35% 5% 10% 9 Selling and administrative overhead 10% 35% 35% 20% Total activity 10 1,000 250 100 units 11 orders customers 12 13 OfficeMart orders: 14 Customers 1 customer 15 Orders 13 orders 16 Number of filing cabinets ordered in total 80 units 17 Selling price 18 Direct materials 595 180 (a) Based on new number of orders in Requirement 2, what is the customer margin under activity-based costing when the data are corrected? (Enter a loss as a negative amount.) Customer margin under activity-based costing (b) Based on new number of orders in Requirement 2, what is the product margin under the traditional costing system when the data are corrected? (Enter a loss as a negative amount.) Product margin under the traditional costing system