Expected NPV Value and Risk (standard deviation and coefficient of variation) Analysis

Your company asked you to evaluate two potential projects. These projects are active for 10 years and have no salvage life. Both have the same upfront costs, but the revenue stream from each of the projects is subject to variation, so risk is involved.

You are given the following information:
Firm's cost of capital: 10%
Each project will require three years of investment before revenues are generated.
The following cost distribution is given:

Probability of Outcome Year 1 Investment Costs Year 2 Investment Costs Year 3 Investment Costs Expected Annual Revenues in Year 4 Expected Rate of Increase in Annual Revenues
Project 1
Outcome A 20% $1,000 $2,000 $1,000 500 2%
Outcome B 40% $1,000 $2,000 $1,000 650 3%
Outcome C 40% $1,000 $2,000 $1,000 850 4%
Project 2
Outcome A 10% $1,000 $2,000 $1,000 675 2%
Outcome B 50% $1,000 $2,000 $1,000 700 2.40%
Outcome C 40% $1,000 $2,000 $1,000 725 2.80%

In a new worksheet in Excel, answer the following:
1.       What is the expected value of the NPV for each of the projects?
2.       What is the standard deviation of the NPV for each of the projects?
3.       What is the coefficient of variation of the NPV for each of the projects?
4.       Which project has a higher expected return? Which has more risk?
5.       Which one would you recommend to your company? How does its attitude toward risk affect your answer?

Instructions:

Step 1: Enter the cost of capital in the worksheet.

Step 2: Enter information about probability of each possible outcome and the expected annual rate of increase in revenues. Take these values from the given data.

Step 3: Enter the cash flow data for the 3 years of investment costs and the first year of revenues from the information given. Notice that the first 3 values are negative because it costs you money to invest in these projects. Year 4 is positive because you are expected to generate revenue in year 4.

 
Step 4: Use the following formula for calculating growth rates to find expected revenues for the remaining 9 years (for a total of 13 years; 3 years of cost and 10 years of revenue).
Use Previous Year + (1 + Probability (%))