Activity Based Costing in Repair/Rework Activities for Legacy Systems Naval Surface Warfare Center Crane, Indiana Gail W. DePuy, John S. Usher, G. Don Taylor, Cindy Edlin University of Louisville
Company Overview • NSWC-Crane serves the Navy as a recognized leader in diverse and highly technical products: • microwave devices • acoustic sensors • small arms • microelectronic technology • Crane stands as a leader in methods, technology, production and maintenance of modern naval combat weapons systems.
Problem Definition • Deliver an Activity-based Costing (ABC) spreadsheet to assist in Crane’s desire to transition to a more responsive manufacturing environment • Research activities: • Evaluate current costing methodologies in place for various overhead activities • Develop an ABC software tool (MS-Excel) that more accurately apportions indirect costs to activity centers and then to specific products and services
Various Spreadsheet Uses • Cost Accounting: • Costs can be more accurately charged to Crane's various customers, managers of different functional groups can become more accountable, and problem areas can be more easily identified • Cost Budgeting: • Since this occurs before a project is undertaken the spreadsheet can be used in a proactive way to establish performance goals for various programs or departments in the future • Cost Estimation: • The spreadsheet offers the user the ability to input cost information at detailed activity or driver levels in order to more accurately estimate indirect activity costs
Spreadsheet Layout & Functions • Top Level (Cost/Activity Center): • Highest breakdown level • Currently only level used in the existing Crane costing spreadsheet • Activity Level: • Intermediate level of detail • Activity level items consist of primary activities that consume a cost center’s resources • Driver Level: • Most specific level of detail • Driver level items consist of the major ‘activity cost drivers’ that cause activities to be performed or drive the need for their development • Specific cost drivers are used to assign overhead costs to activities within each cost center
Spreadsheet Layout & Functions • The spreadsheet uses various calculations that allow for driver level data to roll over to activity level data then further roll up to cost center level data
Example • Cost center 8091 Support • The planning level is AEGIS (MK-20). • 8091 Support has the following four primary activities and associated cost drivers: • Driver level detail is known for the financial, timekeeping, project analysis, and logistics activities. • Only activity level detail is known for procurement.
In the drivers worksheet the user would enter in volumes for the AEGIS (MK-20) system (i.e. row 12) for columns I-O. • Then in the activities worksheet the user would enter in known activity level data into cell E12 for the procurement activity.
The drivers calculations worksheet displays work hours for each driver separated out into gvmt. and contract. • The activity calculations worksheet displays government and contract work hours at the activity level.
The main calculations worksheet sums the total government and contract work hours from the activity calculations worksheet.
The AEGIS (MK-20) is made up of a group of 6 part numbers or NSNs; therefore, the number of items (6) was entered in column F for 8091 Support. The six part numbers are shown in column D of the main worksheet. The hours per item columns for all the cost centers are rolled over to the main worksheet.
Summary • The ABC spreadsheet offers flexibility in terms of: • Level of detail desired (top level, activity level, and driver level) • Desired use (cost estimation, cost accounting or cost budgeting) • Desired planning level (part number, customer, functional department, etc.) • The overall result is a better tool that will enable Crane to more accurately estimate, collect, and control costs • The spreadsheet is equally useful for the following tasks: • Estimating new costs for individual products and new business • Pricing new work or more accurately charging existing customers • Understanding and controlling costs by functional area within Crane