The Excel worksheet form that appears below is to be used to recreate the Review Problem pertaining to Ferris Corporation. Download the workbook containing this form from Connect, where 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 part (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 part (2), what has happened to the customer margin under activity-based costing? Why?
b. Relative to the results from part (2), what has happened to the product margin under the traditional cost system? Why?

Transcribed Image Text:

1 Chapter T: Applying Excel 3 Data 4 Manufacturing ovarhead 5 Seling and admnistrathe overheed 300,000 ntser tomers Other 8 Manufacturing overhead 9 Saling and administrative ovarhead 10 Total actvty 50% 10% 1,000 45% 250 unitsorders customeis 1% 25% 100 10% 20% 13 omceMart orndera 14 Customers 15 Orders 16 Number of fiing cabinets ordered in total 17 Seling price 18 Drect materials 19 Diract labor 20 1 Entor a fomua inlo each of the cella mankved with a2 below 22 Raviaw Problam: Activity-Based Costing custome arders B0 unts 5595 5180 $50 24 Perform the first stage allocations Teral 26 Manufacturing overhesd 27 Seling and admiristrative overhend 28 Total cost 29 0 Compute the activity rades 1 Achivity Coat Pools 32 Assembling units 33 Processing orders 4 Supparting customara 35 36 Compute the owerhesd cost attributable to the OfficeMart orders 7 Activity Coat Poals 8 Assembling units 9 Processing orders 40 Supporting customer Tatal Cost Total Aodwity Activity Rats ? unite ? orders per unit per order par cuatomar Activity Rats Aatiwity ABC Coat ? per unt ? per arder per customsr units orders custamer 42 Derermine the cusomer margin for she OficeMart orders umder Acnivity-Bssed Cossing 43 Sales 44 Costs 45 Direct materiale 6 Direct labor 47 Unt-relatad overhead 48 Orderelated overhead 49 Customer-related ơeertesd Custormer margin 51 2Deterine the product margin for the OfficeMart orders under a traditonal cost systemn 3 Manufacturing ovarhead 54 Total actvty 55 Manufacturing overhead per unit unite per unit 58 Coste 39 Direct materials 60 Direct labor 61 Manfacturing overhead 2 Traditial coating product margin Data Manufacturing overhead Selling and administrative overhead $500,000 $300,000 Assembling Processing Supporting Orders Customers Other 10% 20% Manufacturing overhead Selling and administrative overhead Total activity 5% 25% 100 customers 45% 10% 1,000 OfficeMart orders: Customers. Orders Total number of filing cabinets ordered Selling price Direct materials Direct labor 1 customer 20 orders 80 units $795 $185 $90 Assembling ProcessingSupporting nii Orders Customers Other 10% 20% Selling and administrative overhead lisd aclmiy ..__....- 50% 10 10xx) units 35% 30% 250 orders 5% 40% 100 customers