Arial fx Chapter 7: Applying Excel A 1 Chapter 7: Applying Excel 10 10 B C D E F G H 2 3 Data 4 Manufacturing overhead $500,000 5 Selling and administrative overhead $300,000 6. 7 Units 8 Manufacturing overhead 50% 9 Selling and administrative overhead 10% 10 Total activity 1,000 11 units 12 13 OfficeMart orders 14 Customers Assembling Processing Supporting Other Orders Customers 35% 717 45% 250 5% 10% 25% 20% 100 orders customers 1 customer 15 Orders 16 Number of filing cabinets ordered in total 17 Selling price 18 Direct materials 19 Direct labor 20 4 orders 80 units $595 $180 $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 25 26 Manufacturing overhead Assembling Processing Supporting Other Total Units Orders Customers 24 Perform the first stage allocations 25 26 Manufacturing overhead 27 Selling and administrative overhead 28 Total cost 29 30 Compute the activity rates 31 Activity Cost Pools 32 Assembling units Assembling Processing Supporting Other Total Units Orders Customers ? ? ? ? ? ? ? ? ? ? 2 2 2 Total Cost Total Activity ? ? units Activity Rate 2 per unit 33 Processing orders ? ? orders 2 per order 34 Supporting customers ? ? customers ? per customer 35 36 Compute the overhead cost attributable to the OfficeMart orders 37 Activity Cost Pools Activity Rate 38 Assembling units 7 per unit Activity 7 units 39 Processing orders ? per order ? orders ? customer ABC Cost ? ? ? 40 Supporting customers 7 per custome Determine the customer margin for the OfficeMart orders under Activity-Based Costing 41 42 43 Sales 44 Costs 45 Direct materials 46 Direct labor 47 Unit-related overhead 48 Order-related overhead A B C D E F G H 41 42 Determine the customer margin for the OfficeMart orders under Activity-Based Costing 43 Sales ? 44 Costs: 45 Direct materials 46 Direct labor 47 Unit-related overhead 48 Order-related overhead 49 Customer-related overhead 50 Customer margin ? ? ? ? ? ? 2 51 52 Determine the product margin for the OfficeMart orders under a traditional cost system 53 Manufacturing overhead 54 Total activity Manufacturing overhead per unit 55 56 57 Sales ? units 2 per unit ? 58 Costs: 59 Direct materials 60 Direct labor 61 Manufacturing overhead 62 Traditional costing product margin 63 64 65 ? ct Use the data found in the template + use the additional data found in the ebook section, "Applying Excel" (p. 330 print text). All calculations should be performed within the cell using cell referencing/formulas so that I can see how you arrived at your answers! Do not use your calculator and just type in numbers! You will submit four worksheets within your assignment file: sheet 1 = original sheet 2 requirement 1 quantitative + question responses 11 ces sheet 3 requirement 2 quantitative + question responses sheet 4 requirement 3 quantitative + question responses In scenario 1, 2, and 3.... copy the original worksheet after inputting your cell formulas and change the values as indicated for each requirement. Name each tab by the question #. When submitting your completed work, name the file as follows: C7Excel your first initial+lastname. 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. Page 330 2. Let's assume that OfficeMart 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: Page 331 Data Manufacturing overhead $500,000 Selling and administrative overhead $300,000 Assembling Processing Supporting Units Orders Customers Other Manufacturing overhead 50% 35% 5% 10% Selling and administrative overhead 10% 45% 25% 20% Total activity 1,000 250 100 units orders customers 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 FP 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: Assembling Processing Supporting Units Orders 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 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? 4 1 Chapter 7: Applying Excel 2 3 Data 4 Manufacturing overhead 5 Selling and administrative overhead $500,000 $300,000 6 Assembling Processing Supporting Other 7 Units Orders Customers 8 Manufacturing overhead 50% 35% 5% 10% 9 Selling and administrative overhead 10% 45% 25% 20% 10 Total activity 1,000 250 100 11 units orders customers 12 13 OfficeMart orders: 14 Customers 1 customer 15 Orders 4 orders 16 Number of filing cabinets ordered in total 80 units 17 Selling price $595 18 Direct materials $180 19 Direct labor $50 20 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 Cunning Other Total 23 24 Perform the first stage allocations 25 26 Manufacturing overhead 27 Selling and administrative overhead Assembling Processing Supporting Other Total Units Orders Customers ? ? 2 2 2 ? ? ? ? ? 2 2 2 ? 2 28 Total cost 29 30 Compute the activity rates 31 Activity Cost Pools: Total Cost 32 Assembling units ? Total Activity 7 units Activity Rate ? per unit 33 Processing orders ? 7 orders ? per order 34 Supporting customers 2 customers 7 per customer 35 36 Compute the overhead cost attributable to the OfficeMart orders 37 Activity Cost Pools 38 Assembling units 39 Processing orders 40 Supporting customers 41 Activity Rate 2 per unit 7 per order ? per custome ? units 2 customer 42 Determine the customer margin for the OfficeMart orders under Activity-Based Costing Activity ABC Cost ? ? orders ? 2 43 Sales 44 Costs 45 Direct materials 46 Direct labor 47 Unit-related overhead 48 Order-related overhead 2 B C D E 42 Determine the customer margin for the OfficeMart orders under Activity-Based Costing 43 Sales 14445 46 47 Costs: Direct materials Direct labor Unit-related overhead 48 Order-related overhead 49 Customer-related overhead 50 Customer margin 51 52 ? ? ? ? ? ? 2 Determine the product margin for the OfficeMart orders under a traditional cost system 53 Manufacturing overhead 54 Total activity 55 Manufacturing overhead per unit Sales 56 57 58 Costs ? 7 units ? per unit ? 60 Direct labor 59. Direct materials 61 Manufacturing overhead ? ? ? 2 62 Traditional costing product margin 2 63 64 65 66 67 LL F G H ect New: A short instructional video will be available in the Chapter 7 module. Use the template attached below to complete this activity: (Videos are available in the C4 module on using the ebook+copying worksheets). Chapter 7 Applying Excel template-1.xlsx Use the data found in the template + use the additional data found in the ebook section, "Applying Excel" (p. 330 print text). All calculations should be performed within the cell using cell referencing/formulas so that I can see how you arrived at your answers! Do not use your calculator and just type in numbers