1. Overhead Allocation and Primary Assumptions ? Complete Sheet 1 (4 points) a. Use the resource consumption percentages and budgeted overhead costs to perform ?Stage-One? allocations from functional to activity cost pools. Use formulas. 2. Product Costing and Profitability Computations ? Complete Sheet 2 (5 points) a. Compute CDRs (activity rates) for the facility-wide and activity-based cost pools. Using the CDRs, allocate overhead from ABC cost pools to product lines in the ?Stage-two? overhead allocation. Use reference links to CDRs in Sheet 2 and appropriate budget cells from Sheet 1 to complete the stage-two allocation. b. Determine product line and average unit costs under both traditional and ABC costing systems. Note: Remember that ONLY production-related overhead costs are to be used for the traditional costing system. c. Compute total and unit product margins under the two costing systems. 3. Customer Profitability Computations ? Complete Sheet 3 (5 points) a. Reference CDRs from Sheet 2 and use them to compute customer profitability under both the current and ABC costing systems. 4. Profitability Analysis (6 points) Analyze product profitability under traditional costing and ABC. a. Does PWC appear to be losing money on either or both products? Explain. b. If the profitability is different across the costing systems, which system do you believe? Why? c. Explain the difference in total margins across traditional and ABC costing systems. Be specific. Analyze customer profitability under traditional costing and ABC. d. Does PWC appear to be losing money on either or both customers? Explain. e. What recommendation(s) can you provide to PWC management to better manage customer profitability? Be specific. 5. What-if Analysis (4 points) Open your previously saved spreadsheet file. You should only need to change selected numbers on the data input sheet to complete this item. a. Mr. Peach is considering an investment to further automate the factory by installing a computer integrated manufacturing (CIM) system. This system would increase the annual fixed costs of factory equipment depreciation, engineering and computer programmer salaries. However, improved efficiency is expected to yield revenue enhancements, and some cost reductions as shown below. Adjust the following input data on the source sheet (Sheet 1 ? highlighted yellow cells), as indicated below (it may be helpful to save your adjusted spreadsheet under a different filename). ? Increase engineers' costs to $78,200; computer programmers? costs to $73,600; and equipment depreciation costs to $205,000. Decrease inspector cost to $23,500. ? Increase unit production of standard windows by 37% (to 16,440). ? For standard windows Increase machine hours (to 23,700) and decrease inspections (to 52). For custom windows: Decrease machine hours (to 2,600) and inspections (to 92). b. Explicitly identify costs and benefits (include qualitative considerations) of the proposed upgrade. (Hint: What is the differential margin or operating income associated with the upgrade)? Should management complete the investment? What other information would you want to make your recommendation? Explain and support your answers. 6. Cost System Assessment (6 points) a. Succinctly, but completely, describe the existing cost system used by PWC. Be specific and thorough by considering: cost accumulation, allocation, measurement, and reporting format. b. What alternatives exist to improve the accuracy of traditional costing systems? Explain and be specific. c. Is an ABC system always the best solution for an organization? State relevant factors and tradeoffs PWC should consider in determining whether or not an ABC system is appropriate. Explain and be specific.

AWC Overhead Allocation and Primary Assumptions NAME: Mayank Chauhan Plumtree Window, Co. Budgeted Overhead Costs (Manufacturing and Nonmanufacturing) Production Department: ACTG 352 Machine Setups SPRING 2012 Distribution of Resource Consumption Across Activities Quality Customer Product Customer Control Orders Designs Relations Other Total Indirect wages and benefits: $70,000 $65,800 $42,000 $48,000 $35,000 Inspectors Computer programmers Shipping & materials clerks 20% 15% 15% 25% 10% 10% 10% 5% 15% 40% 20% 30% 10% 5% 10% 25% 100% 100% 100% 100% 100% 30% 35% 25% 15% 25% 20% 20% 25% 25% 5% 5% 5% 5% 0% 0% 5% 0% 5% 15% 0% 0% 0% 25% 45% 50% 50% 50% 100% 100% 100% 100% 100% 35,000 12,000 47,000 Engineers 35% 20% $145,000 $42,000 24,500 32,000 25,000 529,300 Factory supervisors 15% 10% 25% 100% 0% 0% 5% 0% 30% 60% 15% 15% 45% 10% 5% 15% 100% 100% 43,000 7,000 13,500 63,500 $639,800 30% 0% 0% 25% 15% 10% 15% 20% 15% 10% 35% 50% 10% 30% 25% 10% 0% 0% 100% 100% 100% Machine Setups Quality Control Customer Orders Product Designs Customer Relations Other $24,500 $13,160 $$21,600 $- $7,000 $16,450 $42,000 $$- $14,000 $9,870 $$7,200 $14,000 $10,500 $16,450 $$9,600 $10,500 $7,000 $6,580 $$4,800 $1,750 $7,000 $3,290 $$4,800 $8,750 $70,000 $65,800 $42,000 $48,000 $35,000 $21,750 $$4,900 $8,000 $6,250 $7,250 $2,100 $1,225 $1,600 $- $43,500 $14,700 $6,125 $4,800 $6,250 $29,000 $$$1,600 $- $7,250 $6,300 $$$- $36,250 $18,900 $12,250 $16,000 $12,500 $145,000 $42,000 $24,500 $32,000 $25,000 $$- $1,750 $- $10,500 $7,200 $5,250 $1,800 $15,750 $1,200 $1,750 $1,800 $35,000 $12,000 $10,750 $1,050 $1,350 $92,525 $6,450 $1,400 $2,025 $148,020 $4,300 $2,450 $6,750 $98,200 $4,300 $2,100 $3,375 $60,405 $4,300 $$$127,590 45% Depreciation: Equipment Plant Indirect materials Utilities Repair and maintenance Marketing Department: Marketing salaries & wages Selling expenses General & Administration: Administrative salaries & wages Office equipment depreciation Administrative building lease Total overhead cost Stage One Allocations Production Department: Indirect wages and benefits: Factory supervisors Engineers Inspectors Computer programmers Shipping & materials clerks Depreciation: Equipment Plant Indirect materials Utilities Repair and maintenance Marketing Department: Marketing salaries & wages Selling expenses General & Administration: Administrative salaries & wages Office equipment depreciation Administrative building lease Total Budgeted Activity Activity Cost Pool Production quantity (units) Machine Setups Quality Control Customer Orders Product Designs Customer Relations Other Sheet 1 $12,900 $$$113,060 Cost Driver Activity Level Setups Inspections Orders Designs Customers Machine Hours Product Standard Custom 12,000 2,000 30 50 70 125 140 95 15 62 10 40 21,000 3,200 Total Allocated $43,000 $7,000 $13,500 $639,800 Budgeted Revenue & Costs Total 14,000 80 195 235 77 50 24,200 Standard Revenue 1,140,000 Direct Costs: Direct materials468,000 Direct labor 180,000 Shipping 24,000 Custom 308,000 Total 1,448,000 108,000 43,000 6,000 576,000 223,000 30,000 Product Costing Project AWC Cost and Product Line Profitability Analysis NAME: Mayank Chauhan ACTG 352 SPRING 2012 Plumtree Window, Co. Traditional Costing Overhead Rate Computation Total budgeted overhead Budgeted machine hours ABC Overhead Rate Calculations Machine Setups Quality Control Customer Orders Product Designs Customer Relations Other Total Product Costing Calculations Standard: Overhead applied: Machine Setups Quality Control Customer Orders Product Designs Customer Relations Other Total overhead applied Custom: Overhead applied: Machine Setups Quality Control Customer Orders Product Designs Customer Relations Other Total overhead applied Product Line Profitability Analysis Standard: Revenue Direct materials Direct labor Manufacturing overhead Shipping Machine Setups Quality Control Customer Orders Product Designs Customer Relations Other Product Margin $639,800 Overhead rate (CDR) = Cost Pool $113,060 92,525 148,020 98,200 60,405 127,590 $639,800 Cost Driver Setups Inspections Orders Designs Customers Machine Hours Budgeted Level 80 195 235 77 50 24,200 CDR ABC Cost Activity 30 70 140 15 10 21,000 $1,413.25 $474.49 $629.87 $1,275.32 $1,208.10 $5.27 50 125 95 62 40 3,200 $1,413.25 $474.49 $629.87 $1,275.32 $1,208.10 $5.27 70662.50 59310.90 59837.87 79070.13 48324.00 16871.40 $334,077 Traditional ABC $1,140,000 $468,000 180,000 555,198 N/A N/A N/A N/A N/A N/A N/A 1,203,198 $(63,198.35) -5.5% 2.9% $#DIV/0! 14.2% $308,000 $108,000 $43,000 $84,602 N/A N/A N/A N/A N/A N/A N/A Check figures: Unit Profitability Analysis Average Sales Price per unit Average Unit Cost CDR $1,413.25 $474.49 $629.87 $1,275.32 $1,208.10 $5.27 42397.50 33214.10 88182.13 19129.87 12081.00 110718.60 $305,723 Check figures: Custom: Revenue Direct materials Direct labor Manufacturing overhead Shipping Machine Setups Quality Control Customer Orders Product Designs Customer Relations Other Product Margin 26.44 Per Machine HR 24,200 Traditional Costing Standard $95.00 $235,602 $72,398 23.5% 28.2% $#DIV/0! -59.4% Custom $154.00 ABC Costing Standard Custom $95.00 $154.00 Average product margin per unit Percentage Check figures: Unit sales Total margins Sheet 2 0.0% 2.9% 0.0% 28.2% 0.0% 14.2% 0.0% -59.4% 12,000 $- 2,000 $$- 12,000 $- 2,000 $$- Product Costing Project AWC Customer Profitability Analysis NAME: Mayank Chauhan ACTG 352 SPRING 2012 Plumtree Window, Co. Customer Profitability Analysis: Traditional vs. ABC Costing Columbus Twin Lakes Construction Builders Revenue 17,250 18,500 Direct materials cost 5,400 4,750 Direct labor cost 2,150 1,850 Shipping cost 205 390 Units shipped 90 137 Setups 3 1 Inspections 3 2 Orders 4 2 Product designs 4 2 Machine hours 140 215 Cost Pool CDR Customer Information Cost Driver Total budgeted overhead Machine Hours Machine Setups Setups Quality Control Inspections Customer Orders Orders Product Designs Designs Customer Relations Customers Other Machine Hours TRADITIONAL COSTING METHOD Margin Calculation Columbus Twin Lakes Construction Builders Sales Direct materials Direct labor Overhead applied Gross Margin $0 #DIV/0! 38.5% Check figures: ABC MARGIN ANALYSIS Sales Direct materials Direct labor Shipping Machine Setups Quality Control Customer Orders Product Designs Customer Relations Other ABC Customer Margin Check figures: Sheet 3 $0 #DIV/0! 38.9% Columbus Construction Twin Lakes Builders #DIV/0! -33.2% #DIV/0! 16.2% Product Costing Project