ECO 500 Excel Assignment Two Instructions
This sample problem shows the steps to evaluating two potential projects. In this problem, we are going to determine the expected value and the amount of risk for each project using Excel. To do so, we are going to find the net present value for each outcome, then find the weighted net present value. That will give us the expected value. Then, we are going to find the coefficient of variance which is used to determine the risk.
Start a new worksheet.
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 (%))
Step 5: Find the NPV of each of the outcomes for each project using Excel’s NPV formula.
NPV = (cost of capital (%), Year 1: Year 13).
Step 6: Find the weighted average of the NPVs of the three outcomes. This weighted average is the expected value! Note that the weighted average is the sum of the probability times the NPV for each outcome: Expected Value =∑ Probability (%) outcome* NPV outcome.
Now we have our expected value for each project! Now let us find the risk, using the coefficient of variation.
Step 7: Find the deviation of the NPV for each outcome from the weighted average NPV for the project, then square the deviation. Use Probability of each outcome = (NPV – Expected Value(NPV))^2.
Step 8: Now find the weighted average of the squared deviations. This is the variance. Take the square root of the variance to get the standard deviation.
Step 9: Find the coefficient of variation or the standard deviation divided by the expected value. Notice that the higher the coefficient of variation, the riskier the project.
Your spreadsheet is now complete!
Remember to answer the given questions!