Case study - Constant Cable Company
Deliverables
1. Using the template provided as a starting point, create an Excel workbook that includes the following worksheets:
a. Assumptions
b. Economic Feasibility Summary
The Assumptions worksheet will include all your inputs such as Anticipated Yearly Benefits, One-Time Costs, Yearly Recurring Costs, along with the discount rate and any other assumptions you need to supply to your calculations. The purpose of this is to make it easy for your manager to trace back to where you got your numbers, and also to facilitate what-if analysis.
The Economic Feasibility Summary worksheet consolidates data from the Assumption worksheet. 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.
Observe the following instructions carefully:
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. Complete your calculations by determining the Break-even Point where the costs and the benefits are the same.
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 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 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 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 and the Break-even point.
7. On a separate worksheet create a table that compares the Overall Net Present Value and Break-even point for the four scenarios.
Attachment:- constant cable company.rar