The Problem
John recognized that the success of SSB would depend on the ability of his employees to work seamlessly together in an integrated fashion providing exceptional customer service. In the beginning when SSB was a small company with fewer than 10 employees, it was easy to provide outstanding customer service. However, as SSB grew during the next decade expanding its operations to 50 full-time employees and $5,000,000 in revenue, customer service declined, customer complaints increased, and cash flow suffered. Furthermore, the sales team was growing increasingly frustrated because they were promising products to customers they could not deliver.
When John first started the company, he invested in some computer equipment and basic office software to help track important information. He was a strong believer in using the computer to store and track information related to any of life’s worthwhile pursuits, especially if they were data-intensive. He purchased separate software applications to help keep track of data for each of the key functional areas in his organization - accounting, sales, and warehouse operations. While these software applications served each functional area well, they lacked integration and required significant manual intervention to perform routine business processes. For example, when the sales team submitted an order in its order tracking software application, the order had to be printed out and delivered to the warehouse and accounting departments for processing. The manual process was problematic and John felt it was one of the key reasons for the decline in customer service. Accordingly, he was considering buying an enterprise system like SAP to use throughout the organization.
Your Task
Before proceeding with the project, John has asked you to analyze the economic feasibility of replacing his existing suite of applications with SAP. John would like to know if the benefits realized from implementing the new system exceed the costs of buying, implementing and using the system. Therefore, you must carefully examine the cost and benefits of replacing the legacy system with SAP. To facilitate your analysis, you will create a Microsoft Excel workbook detailing the costs of purchasing, configuring, implementing, and supporting the system. The workbook should also contain a list of the projected benefits and cost savings associated with implementing the new system. Your analysis should take into account the time value of money and calculate the ROI given a 20 year life expectancy for the new system. The present value of a future cash flow may be calculated using the following formula:
Present Value Factor =
Where,
r = discount rate
t = time period in years
The Costs
The costs of implementing the system may be categorized as either one-time costs or recurring costs. One-time costs occur only once, during system acquisition and implementation at the beginning of the project, while recurring costs continue every year as long as the system continues to be used. One-time costs are assumed to all occur in year 0 and do not need to be discounted using the present value formula. Recurring costs are assumed to begin in year 1 and end in year 20.
The following table summarizes the one-time and recurring costs associated with the project.
|
One-Time Costs
|
Dollar Amount
|
|
Software Licenses
|
$500,000
|
|
Hardware
|
$75,000
|
|
Network and Communications Upgrades
|
$25,000
|
|
Training
|
$25,000
|
|
Configuration and Implementation
|
$5,000,000
|
Table 1 - One-Time Costs
|
Recurring Costs
|
Dollar Amount
|
|
Software License Maintenance Fees
|
$80,000
|
|
New IT Employees (2 x 90,000)
|
$180,000
|
Table 2 - Recurring Costs
The Benefits
As a result of using the new system, SSB expects to reduce inventory holding costs of raw materials and finished goods as well as dramatically increase their sales yield, which is the number of sales quotes resulting in a sales order, due to increased customer satisfaction, faster response time, dynamic pricing, and more efficient procurement, production and fulfillment processes. Prior to using the system, 50% of sales quotes resulted in an order. Using the new system, it is expected that 80% of sales quotes will result in a sales order thereby increasing revenue 60%. Assuming average annual sales of $5,000,000 with an average profit margin of 25%, the new system is expected to contribute $750,500 annually to net income. In addition to increased sales and profits, SSB expects to eliminate the positions of four clerical workers who are responsible for the collection, storage and processing of the documents used to support the manual process.
The following table summarizes the recurring benefits associated with implementing the new system.
The Deliverables
Part 1 – The Excel Workbook
Use Excel to create a workbook to facilitate an analysis of the economic feasibility of the project. The workbook should use fixed and variable cell references where applicable to support rapid assessment of the business case under different assumptions for interest rates, benefits and costs. The workbook should contain the following six worksheets.
Table of Contents
This worksheet should list the contents of the workbook along with the purpose of the workbook, the date the workbook was last modified, and the name of the person who created the workbook.
One Time Costs
The one-time cost worksheet lists the one-time costs for the project contained in Table 1.
Recurring Costs
The recurring cost worksheet lists the recurring costs of the project contained in Table 2.
Recurring Benefits
The recurring benefits worksheet lists the recurring benefits of the project contained in Table 3.
Economic Feasibility
For each year of the project calculate the costs, the benefits, the annual cash flow, the present value factor, and the present value of the annual cash flow. You should also calculate the Overall Net Present Value (NPV) for the project using both the present value of annual cash flows and the built in Excel function. The two values you calculate should match. The following figure illustrates the layout of the “Economic Feasibility”.
Create a Sensitivity Analysis Table
Create a worksheet to calculate project NPVs for a range of interest rates and a range of annual benefits. Specifically, your sensitivity analysis should calculate NPV for interest rates between 0 and 20% in 1% increments and between annual benefits of $500,000 and $2,500,000. Year 0 costs and benefits remain the same. Auto format each cell so that each cell is green for positive NPV and red for negative. Hint: Use autofill and the NPV function built in Excel to help with your answer. See below for an example.
Part 2 – Questions to Answer using the Excel Workbook
Please answer the following questions using the workbook you created in Part 1. All questions should be answered in a separate Microsoft Word document.
- Assume a discount rate of 15%. What is the overall net present value for the project? Should SSB move forward with the project and proceed with implementing SAP? Explain your answer.
- Assume a discount rate of 30%. What is the overall net present value for the project? Should SSB move forward with the project and proceed with implementing SAP? Explain your answer.
- Assume the recurring value of the benefits due to increased sales was overly optimistic and net income due to increased sales is only $500,000 instead of $1,000,000. Assuming a discount rate of 15%, what is the overall net present value for the project? Should SSB move forward with the project and proceed with implementing SAP? Explain your answer.
4 Assume the recurring value of the benefits due to increased sales was overly optimistic and net income due to increased sales is only $500,000 instead of $1,000,000. At what discount rate is the project economically feasible? Include 6 decimal places of accuracy in your answer. Should SSB move forward with the project and proceed with implementing SAP?
