Due in eight hours.
Read the attachment, it is cut and paste below, but not formatted correctly.
1
Constant Cable Company
Scheduling and Dispatching System
Economic Feasibility Analysis
Scenario
Constant Cable Company has been in business since 2002. When they started the demand for Internet access in residential homes
was minimal; the majority of home owners obtained their Internet access through dial-up modems or through their telephone providers. Even though the combined availability in the United States was estimated to be almost 85 percent, only 12 percent of those households had chosen to subscribe. CCC was able to keep up with the demand for cable installation, maintenance and repair with one full-time technician, one part-time installer and a simple scheduling application.
By the end of 2004, new technologies and infrastructure were emerging – available to both cable companies and telephone companies. By early 2005 the penetration of broadband had improved to the point where around half of residential users accessed the Internet via broadband. Although CCC was able to fulfill orders they were unable to deliver the quality of service they desired in a timely manner. Service calls were taking more than a day to be resolved; new installs were in the queue for too many days and customers were complaining. Service requests were handled in the order they were received. This often resulted in the service van being in the same neighborhood three or four different times in a single day. CCC management senses the need is imminent. The current scheduling and dispatch system cannot adequately handle the number of calls in an efficient manner and must be replaced. You have been hired to perform an economic feasibility analysis for the creation of a new scheduling and dispatching system that CCC is considering. Your findings will be presented to CCC’s Board of Directors for approval.
Meetings with various departments such as customer service, logistics, IT and accounting identified several tangible benefits and costs. The new custom order scheduling and dispatch system will save the company money by decreasing the amount of fuel consumed, reducing the fleet maintenance and rework costs and by increasing savings from greater efficiency from the dispatchers, technicians and the CRM application. Table 1 summarizes these anticipated benefits and their respective savings from implementing the new system:
Table 1: Anticipated Yearly Benefits
Benefit
Savings
Fuel Savings
$59,400
Increased technician efficiency
$336,000
Improved inventory control
$64,500
Fleet maintenance cost reduction
$4,480
Rework reduction
$160,000
Dispatch savings
$140,000
CRM Savings
$64,000
2
The proposed custom order scheduling and dispatch system will incur both one-time and recurring costs. The
one-time costs for this project currently include implementation consultants, customization, additions to the
technical infrastructure, mobile hardware purchases, software licensing and training for new technicians and
dispatchers. These costs are summarized in Table 2.
Table 2: One-Time Costs
Item Cost
Implementation Consultants $345,000
Customization $289,000
Technical Infrastructure $33,000
Software License $256,000
Mobile Hardware $64,000
Training new technicians $60,000
Training new dispatchers $18,000
The recurring maintenance costs include communication, annual software license fees, mobile hardware
replacement, ongoing training, hosting fees and salaries for one business analyst (BA) and one developer (Dev).
These are summarized in Table 3:
Table 3: Yearly Recurring Costs
Item Cost
Mobile Communication $23,040
Software License $64,000
Mobile Hardware Replacement $12,000
Training new technicians $18,750
Training new dispatchers $4,500
Hosting Fees $68,000
IT Staff (1 BA, 1 Dev) $196,000
Deliverables
1. Create an excel workbook that includes the following worksheets:
a. Anticipated Yearly Benefits
b. One-Time Costs
c. Yearly Recurring Costs
d. Economic Feasibility Summary
The Economic Feasibility Summary worksheet consolidates data from the Anticipated Yearly Benefits,
One-Time Costs, and Yearly Recurring Costs worksheets. Completion of the Economic Feasibility
Summary requires you to discount the recurring benefits and costs to their present values. Since each
year requires a different present value factor, the worksheet must compute the present value factor for
each year.
3
i. Use the following formula to calculate the Present Value Factor:
PVF = 1/(1 + i)n
where i represents the discount rate and n refers to the year. Use a discount rate of 10%.
ii. Determine the present value of a benefit or cost for a particular year by multiplying the recurring value of the benefit or cost for that year by the Present Value Factor.
iii. Calculate the Net Present Value of All Benefits (or Costs) by summing the benefits (or costs) up to and including the current year. Keep in mind that all the cost values should be negative values.
iv. Calculate the Overall Net Present Value (NPV) as the difference between the Net Present Value of all Benefits and the Net Present Value of all Costs.
v. In the Cash Flow Analysis Section calculate the difference between the Net Present Value of all Benefits and the Net Present Value of All Costs for all years and an Overall Net Present Value cash flow.
vi. Using these values and the built-in Excel Internal Rate of Return formula, calculate the IRR using the recurring costs and benefits for each year
vii. Complete your calculations by determining the Actual Break-even Point where the costs and the benefits are the same.
Use the following template to record your calculations:
4
2. Create a chart that displays the Net Present Value of All Benefits and the Net Present Value of All Costs and place it on a separate worksheet. Label the worksheet, “BEP Chart”. On the chart indicate where the break-even point for the Scheduling and Dispatch system occurs.
3. What is the impact if the operational IT staff is doubled? Create a new worksheet that displays this scenario and label it “Case 1: Increased IT Staff”. Be sure to highlight the Overall Net Present Value, IRR and the Break-even point.
4. What is the impact if Customization and Consulting Costs double? Create a new worksheet that displays this scenario and label it “Case 2: Increased One-Time Costs”. Be sure to highlight the Overall Net Present Value, IRR and the Break-even point.
5. What is the impact if rework reduction and technician efficiency decrease by 50%? Create a new worksheet that displays this scenario and label it “Case 3: Decreased Rework and Efficiency”. Be sure to highlight the Overall Net Present Value, IRR and the Break-even point.
6. What is the impact if Rework Reduction and Technician Efficiency increase by 50%? Create a new worksheet that displays this scenario and label it “Case 4: Increased Rework and Efficiency”. Be sure to highlight the Overall Net Present Value, IRR and the Break-even point.
7. On a separate worksheet create a table that compares the Overall Net Present Value, IRR and Break-even point for the four scenarios.
8. Assume that management has enough money to fund two development projects. Compare the Internal Rate of Return of this project to the internal rate of returns for the proposed development projects listed in the following table. Based on these values, which projects should management fund? Justify your response. What factors should be considered when making this decision? Be specific. Place your response on a separate worksheet in the workbook.
9. Name your workbook, “LastnameCCCEconomicFeasibility.xlsx” and submit it through the Attach File feature in Blackboard.
Proposed Project
IRR
Delivery System
15.7 %
Human Resources System
27.8 %
Inventory Tracking System
18.9%
Forecasting System
23.7%
0 comments