Develop a six year NPV spreadsheet similar to the one shown in Figure 9-10. Use
ID: 655270 • Letter: D
Question
Develop a six year NPV spreadsheet similar to the one shown in Figure 9-10. Use the table of benefits, costs, and discount factors shown in figure 9-20. Development costs for system were $225,000.
combined to give a net value. The future stream of benefits and costs arc netted together and then discounted by a factor for each year in the future. The discount factor is the rate used to bring future values back to current values. Figure 9-10 shows a copy of the NPV calculation done for RMO's new CSMS. There are various techniques for calculating the NPV of a given investment. In this example. Year 0 represents the development period prior to the deployment of the system. The annual benefits for each year are extended across the top row. The development costs arc shown on the second row. Annual expenses arc shown on the third. Those three rows arc combined in the fourth row to give the net benefits and costs. The fifth row shows the discount value, given a 6 percent discount rate. The sixth row is the product of the fourth and fifth rows and represents the net value in terms of today's dollars (i.e., the NPV). The seventh row shows a cumulative total of annual NPVs. In Figure 9-10, the numbers in the seventh row eventually change from negative to positive. The point in time when that happens is called the break-even point. The length of time before the break-even point is reached is called the payback period. The payback period occurs in the year that the cumulative value goes positive. To calculate it, first take the last year that the cumulative value is negative-in this case. Year 2. Add to that year the number of days in the following year (in this case, Year 3) that it takes for the cumulative value to go positive. The method for doing that is to take absolute values of the ending value in Year 2 divided by the sum of the absolute values for the end of Year 2 and Year 3-in this ease, 226,865 divided by (226,865 + 430,743). Here, that calculation indicates that the cumulative value goes positive after 35 percent of the year has passed. Multiply .35 times the 365 days in the year to get 128 days into Year 3. Many companies require a payback period of two to three years on new software. The previous cost/bcnefit calculation depends on an organization's ability to quantify the costs and benefits. If it can indeed estimate a dollar value for a benefit or a cost, the organization treats that value as a tangible benefit orExplanation / Answer
The NPV function in Excel does not calculate the NPV correctly.
For this we have to do the below steps:
WACC 4% Rate gets created From the Cash Flow
ROIC 12% NPV Table Times Rate
Investment $(60,000) 1.00 $(60,000)
Year 1 $18,000 0.92583 $14,515
Year 2 $18,000 0.85754 $13,617
Year 3 $18,000 0.79343 $12,301
Year 4 $18,000 0.73703 $11,960
Year 5 $18,000 0.68858 $10,589
PV of Inflows $63,672 Sum of Years 1 - 6
NPV of Investment $13,882 PV of Inflows less Year Zero
IRR 19%
MIRR 13%
NPV using "NPV" Function
on ALL Cash Flows $12,847 = NPV of Investment, above
PV of Inflows using "NPV"
Function on Cash Inflows $63,863 See the PV of Inflows, that has been calculated above
The correct NPV is $13,856 not $12,845 as calculated using the Excel NPV function on all the cash flows.
And now to calculate the correct NPV using the Excel function spreadsheet, then you need to apply the NPV function to all cash flows except year zero.
Then, is present in an adjacent cell, that the net NPV calculated by Excel function with the Cash outflow in the year zero.
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.