Question

Excel Online Structured Activity: Project risk analysis

The Butler-Perkins Company (BPC) must decide between two mutually exclusive projects. Each costs $6,750 and has an expected life of 3 years. Annual project cash flows begin 1 year after the initial investment and are subject to the following probability distributions:

Project A			Project B
Probability	Cash Flows		Probability	Cash Flows
0.2	$6,000		0.2	$0
0.6	$6,750		0.6	$6,750
0.2	$7,500		0.2	$18,000

BPC has decided to evaluate the riskier project at 12% and the less-risky project at 10%. The data has been collected in the Microsoft Excel Online file below. Open the spreadsheet and perform the required analysis to answer the questions below.

Open spreadsheet

1. What is each project's expected annual cash flow? Round your answers to two decimal places.

   Project A: $  

   Project B: $  

   Project B's standard deviation (σ_B) is $5,797.84 and its coefficient of variation (CV_B) is 0.76. What are the values of (σ_A) and (CV_A)? Round your answers to two decimal places.

   σ_A = $  

   CV_A =

2. Based on the risk-adjusted NPVs, which project should BPC choose?

   _________Project AProject B

3. If you knew that Project B's cash flows were negatively correlated with the firm's other cash flow, but Project A's cash flows were positively correlated, how might this affect the decision?

   _________This would make Project B more appealing.This would make Project B less appealing.

   If Project B's cash flows were negatively correlated with gross domestic product (GDP), while A's cash flows were positively correlated, would that influence your risk assessment?

Answer 1

B8 X ESQRT(B7) B Square of Deviation from Expected Value =(C3-$B$6)^2 =(C4-$B$6)^2 =(C5-$B$6)^2 Square of Deviation from Expected Value =(F3-$E$6)^2 =(F4-$E$ 6)^2 =(F5-$E$6)^2 6 Expected Annual CF 7 Variance 8 Standard Deviation 9 Coefficient of Variation 10 Required return 11. NPV Project A Probability Cash Flows 0.2 6000 0.6 6750 0.2 7500 =SUMPRODUCT(B3:B5,C3 | =SUMPRODUCT(B3:B5,0:1 =SQRT(B7) =B8/B6 0.1 =NPV(B10,B6,B6,B6) Project B Probability Cash Flows 0.2 0.6 6750 0.2 18000 =SUMPRODUCT(E3:E5,F3 =SUMPRODUCT(E3:E5,63 =SQRT(E7) =E8/E6 0.12 =NPV(E10,E6, E6,E6)

B8 x fc =SQRT(B7) E F Project A Project B Probability Cash Flows Square of Deviation from Expected Value Probability Cash Flows Square of Deviation from Expected Value 0.2 6000 562500 0.2 58522500 0.6 6750 0.6 6750 810000 0.2 7500 562500 0.2 18000 107122500 6750 7650 225000 33615000 474.34! 5797.84 0.76 10% 12% *16,786.25 * 18,374.01 Expected Annual CF Variance Standard Deviation 9 Coefficient of Variation 10 Required return 11 NPV 0.07

b.

Based on Risk adjusted NPV, Project B should be selected as it has a higher rNPV.

c.

If Project B's cash flow were negatively correlated with another project, it would make the project more appealing as it would reduce the risk of the firm.

If Project is positively correlated to the GDP while Project B is negatively correlated to the GDP, the risk assessment would be influenced by the outlook on the GDP outlook .If the GDP outlook is positive, Project A is more favourable while if the outlook is negative Project B is more favourable.