Question: Show in detail MODEL 1 Develop an Excel model that computes the NPV of each proposed project using an annual discount rate (interest rate) of
Show in detail MODEL 1
Develop an Excel model that computes the NPV of each proposed project using an annual discount rate (interest rate) of22%and the cumulative cash flow. Based on the cumulative cash flow, determine the year in which the (simple)investment paybackoccurs. This is also known as the simple investment recovery period. [Suggest using NPV function, but exercise caution regarding Y0 cash flow. Y0 is the beginning of Y1. The functions default to end-of-period cash flows.] Then, determine what happens if the annual discount rate drops to 10%. Rename this worksheet tab as "NPV".
| YEAR | ||||||
| Proposed Project | 0 | 1 | 2 | 3 | 4 | 5 |
| Dust Devils | ($450,000.00) | $100,000.00 | $250,000.00 | $350,000.00 | ||
| Osprey | ($250,000.00) | $80,000.00 | $100,000.00 | $150,000.00 | $100,000.00 | |
| Voyagers | ($175,000.00) | $25,000.00 | $50,000.00 | $75,000.00 | $75,000.00 | $150,000.00 |
MODEL 2 Then, create another worksheet within the same workbook.Relabel this worksheet tab as "PSM" for Project Screening Matrix. Using the information in the table below, develop a Project Screening Matrix in Excel and compute the weighted score for each project proposal. Use Excel for the computation. [Suggest using the SumProduct function.]
| Criteria | Sponsor | Strategic Alignment | Urgency | Sales from NPI | Competitive Position | Market Niche | |
| Weight | 2 | 4 | 5 | 1 | 4 | 3 | |
| Project A | 9 | 5 | 2 | 2 | 0 | 4 | |
| Project B | 3 | 7 | 2 | 0 | 1 | 5 | |
| Project C | 1 | 2 | 5 | 10 | 6 | 8 | |
| Project D | 8 | 7 | 5 | 6 | 4 | 9 |
Step by Step Solution
There are 3 Steps involved in it
Get step-by-step solutions from verified subject matter experts
Study Help