[SOLVED] The Excel worksheet form that appears below

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 OfficeMart 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:

Data Manufacturing overhead Seling and admiristrative averhead . ss00,000 $300,000 Assembling Processing Supporting Units Orders Customers Other Manufacturing overhead .. Seling and administrative overhead - Tatal activity .. SON 35% 10% 10% 45% 25% 20% 250 1,000 units 100 orders customers OfficeMart orders Customers 1 customer Orders... 20 arders Total number of filing cabinets ordered Seling price 80 units $795 Direct materials $185 $90 Direct labor 1 Chapter Applying Excel Data 4 Manufacturing everhead 5 Seling and administratve oerhead S500, 000 Assembing Proceaing Supponting Ordere Cuotomers Units Oher Marutactanng overhead Seling and adninistratve overhead 10 Total actvity 1 20% 10% 100 45% 25% 250 Drders customers 100 unt 13 Offcalat orders 14 Customers 15 Orders 16 Number of fing cabirets erdered in total 17 Seling price 18 Drect materals 19 Direct laba 20 Endera formuia into sach of the cela marked ith a ? belav 22 Review Problem Activity Based Costing 23 24 Perform the fest stage allecarions 1 customer 4 orders t0 units $100 550 Assembling Proceaning Supporting Ordere Unte Cuotomers Oher Tota 24 Marufactaing vertead 27 Soling and administrative ovorhoad 20 Total cont 3 Campute the activity rate 31 Actvity Cost Pools 12 Assembing units 33 Processing anders 34 Suppating cutome 35 26 Campute the overhead cont atributable to she Officelart arders 37 Activity Cost Poole an Asembing units 9 Processing anders a Suppating cutomes 41 42 Determine the customer margin for she Oficelfart orders under Activitylared Costing Total Cost Total Activity unts 1 orders 7 cuntomers Activity Rate per unit 7 per order 1 per cuntomer Activity Rate 7 per unt * per erder 1 per cuntomer Actvity 7 units 7 orders 7 customer ABC Cost 4a Conts 45 Direct materat 4s Drect labor 47 Unt-elated ovehead 43 Order-related ouerhead a Customer-latad overhead 50 Customer magie 52 Determine the product margin for she Oficelfart orders under a traditional cost system 53 Manfactuing erhead 54 Total activity 5 Marufactaing averhead per unit 7units 1 per unt 57 Sales 58 Costs 9 Direct matetiata 60 Direct labor a1 Marufactuing svertead 62 Tradtional costing product margin HChepter Assembling Processing Orders Supporting Units Customers Other Manufacturing overhead .... 50% 35% 5% 10% Selling and administrative overhead 10% 30% 40% 20% Total activity 1,000 250 100 units orders customers

General Accounting Definitions

Accelerated depreciation

Question: The Excel worksheet form that appears below

Transcribed Image Text:

General Accounting Definitions

View More General Accounting Definitions