The Excel worksheet form that follows is to be used to recreate the Review Problem relating to Ferris Corporation that was presented earlier. Download the workbook containing this form from Connect. On the website you will also receive instructions about how to use this worksheet form. You should proceed to the requirements below only after completing your worksheet.

Required:

1. Check your worksheet by doubling the units ordered in cell B16 to 160. The customer margin under activity-based costing should now be $7,640 and the traditional costing product margin should be $(21,600). If you do not get these results, find the errors in your worksheet and correct them.

a. Why has the customer margin under activity-based costing more than doubled when the number of units ordered is doubled?

b. Why has the traditional costing product margin exactly doubled from a loss of $10,800 to a loss of $21,600?

c. Which costing system, activity-based costing or traditional costing, provides a more accurate picture of what happens to profits as the number of units ordered increases? Explain.

2. Let's assume that Office Mart places different orders next year, purchasing higher-end filing cabinets more frequently, but in smaller quantities per order. Enter the following data into your worksheet:

a. What is the customer margin under activity-based costing?
b. What is the product margin under the traditional cost system?
c. Explain why the profitability picture looks much different now than it did when Office Mart was ordering less expensive filing cabinets less frequently, but in larger quantities per order.
3. Using the data you entered in requirement 2, change the percentage of selling and administrative overhead attributable to processing orders from 45% to 30% and the percentage attributable to supporting customers from 25% to 40%. That portion of the worksheet should look like this:

a. Relative to the results from requirement 2, what has happened to the customer margin under activity-based costing? Why?
b. Relative to the results from requirement 2, what has happened to the product margin under the traditional cost system? Why?

Transcribed Image Text:

Data .$300,000 Assembling ProcessingSupporting Units 50% 10% 1,000 units Orders 35% 45% 250 orders Customers Other 5% 25% 100 customers 10% 20% OfficeMart orders: 1 customer 20 orders 80 units Total number of filing cablnets ordered... $795 $185 $90 1 Chapter 6: Applying Excel 3 Data 4 Manufacturing overhead $500,000 S300,000 Selling and administrative overhead Assembling Processing Supporting Units rdere Customers Other 8 Manufacturng overhead 9 Selling and administrative overhead 10 Total activity 50% 10% 25% 100 orders customers 35% 45% 250 20% 1,000 units 12 13 OfficeMart orders: 14 Customers 15 Orders 16 Number of filing cabinets ordered in total 17 Selling price 18 Direct materials 19 Direct labor 1 customer 4 orders 80 units $595 S180 $50 21 Enter a formula into each of the cells marked with a ? below 22 Review Problem: Activity Based Costing 23 24 Perform the first stage allocations Assembling Processing Supporting Units Customers OtherTota 26 Manufacturing overhead 27 Selling and administrative overhead 28 Total cost 29 30 Compute the activity rates 31 Activity Cost Pools 32 Assembling units 33 Processing orders 34 Supporting customers 35 36 Compute the overhead cost atuributable to the OfficeMart orders 37 Activity Cost Pools 33 Assembling units 39 Processing orders 40 Supporting custorners ? per customer Total Cost Total Activity ? units Activity Rate ? per unit ? per order ? per customer orders ? customers Activity Rate Activity ABC Cost ? per unit ? per order ? units ? orders ? customer 42 Defermine the customer margin for the OfficeMart orders under Activity-Based Casting 43 Salos 44 Costs 45 Direct materials 46 Direct labor 47 Unit-related overhead 48 Order-related ove rhead 49 Customer-related overhead 50 Customer margin 51 52 Determine the product margin for the OfficeMart orders under a traditional cost system 53 Manufacturing overhe ad 54 Total activity 55 Manufacturing overhead per unit 56 57 Sales 58 Costs 59 Direct materials 60 Direct labor 61 Manufacturing overhead 62 Traditional costing product margin ? units ? per unit 4 Chapter 6 Formled in Chapter 6 Form Assembling Processing Supporting Orders Units 50% 10% 1,000 units Customers Other 35% 30% 250 orders 5% 40% 100 customers 10% Selling and administrative overhead. 20%