REQUIRED:

1. Update this spreadsheet with the appropriate formulas in the cells below with question marks.

2a. What is the customer margin under activity-based costing?

2b. What is the product margin under the traditional cost system?

2c. Explain why the profitability picture looks much different now than it did when OfficeMart was ordering less expensive filing cabinets less frequently, but in larger quantities per order.

B D E F G H 1 K L A Chapter 7: Applying Excel Page 339 Data Manufacturing overhead Selling and administrative overhead Instructions: 1. Update this spreadsheet with the appropriate formulas in the cells below with question marks. $500,000 $300,000 2. Then, make a copy of the sheet in this same workbook and prepare Requirement 2 on page 340. Manufacturing overhead Selling and administrative overhead Total activity Assembling Processing Units Orders 50% 35% 10% 45% 1.000 250 units orders Supporting Customers 5% 25% 100 customers Other 10% 20% Note: You can also make the changes suggested in Requirement 1, if you want, to check your worksheet the first time to make sure it works properly. OfficeMart orders: Customers Orders Number of filing cabinets ordered in total Selling price Direct materials Direct labor 1 customer 4 orders 80 units $595 $180 $50 Enter a formula into each of the cells marked with a ? below Review Problem: Activity-Based Costing Perform the first stage allocations Other Total Manufacturing overhead Selling and administrative overhead Total cost Assembling Processing Units Orders ? ? ? ? ? ? Supporting Customers ? ? ? ? ? ? ? ? ? Total Cost Compute the activity rates Activity Cost Pools Assembling units Processing orders Supporting customers ? ? ? Total Activity ? units ? orders ? customers Activity Rate ? per unit ? per order ? per customer ABC Cost Compute the overhead cost attributable to the OfficeMart orders Activity Cost Pools Activity Rate Assembling units ? per unit Processing orders ? per order Supporting customers ? per customer Activity ? units ? orders ? customer ? ? ? Determine the customer margin for the OfficeMart orders under Activity-Based Costing Sales ? Costs: Direct materials ? Direct labor ? Unit-related overhead ? Order-related overhead ? Customer-related overhead ? ? Customer margin 2 "Check My Work": Customer margin should be $840 Determine the product margin for the OfficeMart orders under a traditional cost system Manufacturing overhead ? Total activity ? units Manufacturing overhead per unit ? per unit ? Sales Costs: Direct materials Direct labor Manufacturing overhead Traditional costing product margin ? ? ? ? ? "Check My Work" Customer margin should be ($10, 800) B D E F G H 1 K L A Chapter 7: Applying Excel Page 339 Data Manufacturing overhead Selling and administrative overhead Instructions: 1. Update this spreadsheet with the appropriate formulas in the cells below with question marks. $500,000 $300,000 2. Then, make a copy of the sheet in this same workbook and prepare Requirement 2 on page 340. Manufacturing overhead Selling and administrative overhead Total activity Assembling Processing Units Orders 50% 35% 10% 45% 1.000 250 units orders Supporting Customers 5% 25% 100 customers Other 10% 20% Note: You can also make the changes suggested in Requirement 1, if you want, to check your worksheet the first time to make sure it works properly. OfficeMart orders: Customers Orders Number of filing cabinets ordered in total Selling price Direct materials Direct labor 1 customer 4 orders 80 units $595 $180 $50 Enter a formula into each of the cells marked with a ? below Review Problem: Activity-Based Costing Perform the first stage allocations Other Total Manufacturing overhead Selling and administrative overhead Total cost Assembling Processing Units Orders ? ? ? ? ? ? Supporting Customers ? ? ? ? ? ? ? ? ? Total Cost Compute the activity rates Activity Cost Pools Assembling units Processing orders Supporting customers ? ? ? Total Activity ? units ? orders ? customers Activity Rate ? per unit ? per order ? per customer ABC Cost Compute the overhead cost attributable to the OfficeMart orders Activity Cost Pools Activity Rate Assembling units ? per unit Processing orders ? per order Supporting customers ? per customer Activity ? units ? orders ? customer ? ? ? Determine the customer margin for the OfficeMart orders under Activity-Based Costing Sales ? Costs: Direct materials ? Direct labor ? Unit-related overhead ? Order-related overhead ? Customer-related overhead ? ? Customer margin 2 "Check My Work": Customer margin should be $840 Determine the product margin for the OfficeMart orders under a traditional cost system Manufacturing overhead ? Total activity ? units Manufacturing overhead per unit ? per unit ? Sales Costs: Direct materials Direct labor Manufacturing overhead Traditional costing product margin ? ? ? ? ? "Check My Work" Customer margin should be ($10, 800)