North East Window Co. (NEWC)
NEWC is a high-quality window manufacturer located near Erie, Pennsylvania. Customers may order standard windows or custom windows requiring unique designs. NEWC’s current costing system uses a facility-wide rate based on machine hours. Since the Company’s profitability analysis suggests that standard windows yield a smaller gross profit percentage, relative to custom window products, management is considering dropping the standard product line and increasing custom window production. You suggest to your boss, Ms. Plum, that a pilot ABC costing analysis should be considered before making a final decision to drop the standard window product line. You explain that ABC may improve the accuracy of overhead allocation and product cost information. Ms. Plum agrees to consider your ABC analysis prior to making a final decision. You and the factory supervisor, Mr. Al Good, have identified the company’s cost drivers and estimated activity levels for the coming year. Relevant information regarding the budget, window production and activity is provided in Sheet 1 of the accompanying Excel template.
Excel Template
Download and SAVE the Excel spreadsheet template to your flash drive (do not make changes to the Excel file within your browser because you may not be able to save and print the file). The Excel template already contains the required input data. Remember – do not hardcode numbers into spreadsheet cells. Rather, cells that you complete should contain only formulas (i.e., cell references and mathematical computations) that reference input data cells already contain values within the template. To complete Sheets 2 and 3 you will reference cells from Sheets 1 and/or 2. Do this by either typing in the required cell as follows: e.g., “=Sheet1!B2”, or highlighting the cell to be referenced by starting a formula (i.e., “=”) and then moving to the appropriate sheet and cell containing the data, then pressing ENTER. In addition, you may need to format cells for specific data (e.g., overhead rates and costs should be two-decimal currency format, but other items should be without decimal places. BE CONSISTENT AND PROFESSIONAL.
Required:
Complete the Excel spreadsheet template. Use formulas in all spreadsheet cells linking to data input cells and intermediate computations, as necessary. Up to 5 points will be deducted for hardcoded spreadsheet cells. Submit two separate files to the D2L digital drop box by the indicated due date: (1) A completed Excel template; and (2) A Word document with written response and supporting computations, if necessary.
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 Computations – Complete Sheet 2 (4 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.
3. Product Line and Unity Profitability Analysis – Complete Sheet 3 (4 points)
a. 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.
b. Compute total and unit product margins under the two costing systems.
c. Reconcile NOI for the two costing systems and briefly discuss differences.
4. Customer Profitability Analysis – Complete Sheet 4 (4 points)
a. Reference CDRs from Sheet 2 and use them to compute customer profitability under both the current and ABC costing systems.
5. Profitability Analysis (5 points)
Analyze product profitability under traditional costing and ABC.
a. Does NEWC 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?
Analyze customer profitability under traditional costing and ABC.
c. Does NEWC appear to be losing money on either or both customers? Explain.
d. What recommendation(s) can you provide to NEWC management to better manage customer profitability? Be specific.
6. 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. Al Good 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 $83,000; computer programmers’ costs to $75,400; and equipment depreciation costs to $230,000. Decrease inspector cost to $27,500.
• Increase unit production of standard windows by 20% (to 15,240).
• For standard windows Increase machine hours (to 26,400) and decrease inspections (to 49).
For custom windows: Decrease machine hours (to 2,850) and inspections (to 102).
PRINT and CLEARLY LABEL “What-if” on Sheets 1, 2 & 3.
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.
7. Cost System Assessment (5 points)
a. Succinctly, but completely, describe the existing cost system used by NEWC. 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 NEWC should consider in determining whether or not an ABC system is appropriate. Explain and be specific.
