Purpose: Learn to work with financial calculations for decision making and create a weighted scoring model. Be sure to make your output look professional.
Assignment: review the following case study and answer the questions at the end.
You work for a large successful Fortune 500 organization, Brewer Enterprises (BE) but lately many of the IT projects have experienced disappointing results. You have been put on the steering committee to improve the project selection process. BE has decided that any new projects selected for implementation must first meet strict financial measures as well as several other criteria. They have set minimum values for ROI, NPV, IRR, and the payback period. The following information was generated by the project manager for the current set of proposed new projects.
Project One Costs Year 0 - Year 4: $120,000; $15,000; $12,000; $7500; $7500 Project One Revenue Year 0 - Year 4: $0; $5,000; $70,000; $100,000; $110,000 Project One Risk determined to be high
The project directly supports several line items on the Strategic Plan Current Competitors have not yet completed a similar project
Resource Availability is good Project Two Costs Year 0 - Year 4: $51,000; $50,000; $25,000; $6,000; $6,000 Project Two Revenue year 0 - Year 4: $0; $4,000; $77,000; $80,000; $100,000 Project Two Risk determined to be Medium
The project is something entirely new, not on the strategic plan, but being mandated by the U.S. government.
One current competitor has completed a similar project
Resource Availability is good
Project Three Costs Year 0 - Year 4: $120,000; $15,000; $5,000; $5,000; $5,000 Project Three Revenue Year 0 - Year 4: $10,000; $50,000; $100,000; $150,000; $100,000
Project Three Risk determined to be Low
The project indirectly supports several line items on the Strategic Plan Current Competitors have not yet completed a similar project
Resource Availability is average
Project Four Costs Year 0 - Year 4: $60,000; $55,000; $50,000; $20,000; $7500 Project Four Revenue Year 0 - Year 4: 0; $7,000; $71,000; $85,000; $90,000 Project Four Risk determined to be Medium to High
The project directly supports several line items on the Strategic Plan Current Competitors have not yet completed a similar project
Resource Availability is poor
Questions: use Microsoft Excel or equivalent to calculate
1. Calculate NPV for each project using an 8% discount rate.
2. Calculate IRR for each project.
3. Calculate ROI for each project using an 8% discount rate.
4. Calculate payback period for each project.
5. Create a weighted scoring model using the following criteria: Strategic Plan Alignment, NPV, ROI, Risk, Competition, and Resource Availability. The organization has decided to apply the following weights to each of the criteria as: 25%, 15%, 15%, 20%, 15%, and 10% respectively.
6. If the steering committee can pick only one project to work on, which would it be?