Assignment:
Carter Electronics, a distributor of semiconductors and electrical components, is considering implementing an ERP system. This project will represent a huge investment for Carter and thus a business case must be developed in order to gain approval for the project. With the help of knowledgeable process owners, a group of individuals who will likely be on the project team, and IT staff, the managerial accountants put together the following total cost of ownership and benefits over a 5 year horizon (the time estimated that the system will be used until a major upgrade). Carter's cost of capital is 6%
|
|
ESTIMATED TCO
|
|
|
Item
|
One Time
|
Recurring
|
Comments
|
|
Technology
|
|
|
|
Hardware and infrastructure upgrades
|
$500,000
|
|
Costs for
servers/network/wiring/redundancy
|
|
ERP software contract value
|
$2,000,000
|
|
Expected negotiated net price
|
|
Recurring software maintenance -level 3 support
|
|
$360,000
|
18% of software contract value (low end of maintenance)
|
|
Database and Identity and
Access Management Software
|
$225,000
|
|
Estimated for MS SQL Server and any of the top 5 IAM systems, which are priced similarly
|
|
Data
|
|
Inventory master data accuracy and BOM master data
|
$350,000
|
|
This data is in 2 different systems currently. Deemed to have major data duplication issues. Need scrubbing and restructuring into new format.
|
|
All other master data scrubbing
|
$200,000
|
|
Scrubbing customer, vendor, employee
|
|
Writing interface programs
|
$275,000
|
|
Multiple legacy systems will remain (e.g., fixed assets)
|
|
Recurring data maintenance
|
|
$140,000
|
2 full time persons for master data maintenance (ongoing 5 years)
|
|
Writing data conversion programs
|
$300,000
|
|
Convert data to ERP format and migrate
|
|
People
|
|
Internal Project Team
|
$2,100,000
|
$180,000
|
Six full-time equivalent people; Center of Excellence - two full time people for 5 years
|
|
|
|
Change Management
(consultants)
|
$500,000
|
$100,000
|
Includes costs for communication, education, training; ongoing 2 years
|
|
Project management (consultants)
|
$1,600,000
|
|
Integrated project plan, budget etc.
|
|
Configuration/customization of ERP software (consultants)
|
$1,700,000
|
|
Adapting the software to use in the company/filling gaps
|
|
Contingency - 15%
|
15%
|
|
15% buffer on all one-time costs to avoid surprises.
|
|
ESTIMATED BENEFITS
|
|
|
Item
|
Current Level
|
% Improvement
|
Annual Benefits
|
Comments
|
|
Sales
|
$75,000,000
|
5%
|
$3,750,000
|
Improvement in gross sales due to improved product availability and better service.
|
|
Direct Labor
Productivity
|
$15,000,000
|
10%
|
$1,500,000
|
Reductions in idle time, overtime, and other HR- related issues related to inefficiency of workforce.
|
|
6 Fewer
Employees
Needed
|
Average loaded
$50,000 annual salary
|
|
$300,000
|
Reductions in
clerical/data entry
support and warehouse
staff
|
|
Purchasing costs
|
$1,000,000
|
20%
|
$200,000
|
Better planning and information = reduced total purchase costs.
|
|
Inventory
|
|
|
|
Item
|
Current Levels
|
% Improvement
|
Annual Benefits
|
Comments
|
|
Raw Material and
WIP
|
$17,000,000
|
4%
|
$680,000
|
Annual saving from better forecasting and also implementation of vendor managed inventory
|
|
Finished Goods
|
$9,000,000
|
17%
|
$1,530,000
|
Annual saving from better forecasting and also implementation of demand management module
|
|
Premium freight
|
$800,000
|
50%
|
$400,000
|
Produce and ship on-time reduces shipment cost
|
Required:
1) Set up an professionally formatted Excel spreadsheet for the data provided and perform the following calculations (assume that one-time costs occur now at time zero and assume that the initial investment is the sum of all the one time (initial) costs; also assume that the annual (recurring) costs and benefits occur at the end of each of Year's 1-5 (unless stated otherwise)):
a) Calculate the NPV of this project using 6% cost of capital.
b) Calculate the Return on Investment (ROI) for this project.
c) Calculate the Payback Period for the ERP system
d) If you used any Excel time value functions (e.g., NPV, PV, etc.) for (a), (b) or (c) above, "manually" test the results of the Excel functions by calculating the appropriate compounded discount rates and reperform the calculations that the Excel functions are calculating so that you have confidence in the Excel formula's.
e) Professionally format, then copy & paste relevant data/calculation results from this part (1) and use as an "Appendix A" in your memorandum in part 2. Reference relevant results from your analysis shown in the appendix as appropriate in your memo in part 2.