CHAPTER 12: RISK, CAPITAL BUDGETING AND DIVERSIFICATION
Instructor’s Manual Problem Set
1. Tebow Endeavors Inc. is evaluating two mutually exclusive investment projects. The firm has estimated the following NPVs, IRRs, and PIs after performing scenario analysis using the following probabilities for each scenario:
Scenario
Probability NPVI PII IRRI NPVII PIII IRRII
1 5%$1,259,058.4
8 1.6836.30
%$1,009,622.3
3 1.3427.19
%
2 10% $959,508.26 1.5231.55
% $783,351.86 1.2624.51
%
3 15% $659,958.04 1.3626.64
% $557,081.39 1.1921.81
%
4 40% $360,407.82 1.1921.52
% $330,810.92 1.1119.07
%
5 15% $60,857.60 1.0316.14
% $104,540.45 1.0316.29
%
6 10%($238,692.62
) 0.8710.38
%($121,730.02
) 0.9613.48
%
7 5%($538,242.84
) 0.71 4.10%($348,000.49
) 0.8810.62
%
The financial staff is divided about which project is more convenient to accept: I or II. a. Determine the expected NPV, PI, and IRR for both projects. Which project should be accepted based only on these results?
Possible Answer: The expected NPV, PI, and IRR of project I are all higher than those of project II. Therefore, based exclusively on these results, project I should be accepted instead of II.
b. Determine the variance and standard deviation of the NPVs, PI, and IRR for both projects. Which project appears to be riskier?
Possible Answer: The variance and standard deviation of project I’s NPVs, PI, and IRR are all higher than those of project II. Therefore, based exclusively on these results, project I appears to be riskier than project II.
c. Determine the coefficient of variation of the NPVs PI, and IRR for both projects. Which project appears to be riskier based on these results only?
549
CHAPTER 12: RISK, CAPITAL BUDGETING AND DIVERSIFICATIONIM Problem Set & Solutions
Possible Answer: The coefficient of variation of project I’s NPVs, PI, and IRR are all higher than those of project II. Therefore, based exclusively on these results, project I appears to be riskier than project II.
d. Calculate the probability of a negative NPV, a PI less than one, and an IRR less than the firm’s WACC of 15% for both projects.
Possible Answer: Project I’s probabilities of a negative NPV, a PI less than one, and an IRR less than the firm’s WACC of 15% are 19.74%, 19.74%, and 20.38% respectively. On the other hand, project II’s probabilities of a negative NPV, a PI less than one, and an IRR less than the firm’s WACC of 15% are 15.06%; 15.06%; and 15.07% respectively.
e. Based on your results on parts a, b, c, and d, which project should be accepted?Possible Answer: The fact that project I has a higher expected NPV than II, or that project II has a lower probability of a negative NPV, a PI less than one, and an IRR less than the firm’s WACC should not constitute clear evidence of accepting one project over the other. The individual decision maker must decide.
Worksheet:
Formulas:
550
CHAPTER 12: RISK, CAPITAL BUDGETING AND DIVERSIFICATIONIM Problem Set & Solutions551
CHAPTER 12: RISK, CAPITAL BUDGETING AND DIVERSIFICATIONIM Problem Set & Solutions
2. Strong Structures Inc. is a manufacturer of track-works, steel sheet pilings and pipe coatings for rail, construction, energy and utility industries. The firm is considering the replacement of an existing machine. The new machine costs $1.3M and requires installation costs of $100,000. The existing machine currently has a salvage value of $200,000 before taxes. It was purchase three years ago at a price of $1M and has a remaining economic life of 5 years. It has been depreciated under the MACRS's 5-year recovery period. If the firm decides to keep this old machine will be worthless at the end of year 5. Over its 5-year life, the new machine should reduce operating costs by $400,000 per year. The new machine will be depreciated under the MACRS's 5-year recovery period and can be sold for $180,000 at the end of its economic life. Also, the new machine will require an investment in net working capital of $40,000. The firm WACC is 12% and is subject to a 40% tax rate.
a. Calculate the initial investment, annual after-tax cash flows, and the terminal cash flow of this investment project.
Worksheet:
552
CHAPTER 12: RISK, CAPITAL BUDGETING AND DIVERSIFICATIONIM Problem Set & Solutions
Formulas:
553
CHAPTER 12: RISK, CAPITAL BUDGETING AND DIVERSIFICATIONIM Problem Set & Solutions
554
CHAPTER 12: RISK, CAPITAL BUDGETING AND DIVERSIFICATIONIM Problem Set & Solutions555
CHAPTER 12: RISK, CAPITAL BUDGETING AND DIVERSIFICATIONIM Problem Set & Solutions
b. Determine the payback period, discounted payback period, NPV, PI, IRR, and MIRR of this project. Should this project be accepted?
Worksheet:
556
CHAPTER 12: RISK, CAPITAL BUDGETING AND DIVERSIFICATIONIM Problem Set & Solutions
Formulas:
Possible Answer: Based on a positive NPV of $65,343.55, a profitability index of 1.05, and a IRR of 14.06% and greater than the firm’s WACC, the machine should be replaced.
c. Perform the same sensitivity analysis as that on Exhibit 12-5, page 379 (except that you should use increments of 5% from -15% to 15%, instead of 10% like in the book) using the following uncertain variables: net working capital investment, price of the new machine, operating cost savings, salvage value of the old machine at the end of its economic life, current salvage value of the old machine. Create a Scatter char including all of these variables.
Worksheet:
557
CHAPTER 12: RISK, CAPITAL BUDGETING AND DIVERSIFICATIONIM Problem Set & Solutions
558
CHAPTER 12: RISK, CAPITAL BUDGETING AND DIVERSIFICATIONIM Problem Set & Solutions
-20% -10% 0% 10% 20%($100,000.00)($50,000.00)
$0.00 $50,000.00
$100,000.00 $150,000.00 $200,000.00 $250,000.00
Sensitivity Diagram
Net Working Capital In-vestmentPrice New MachineOperating Costs SavingsSalvage Value Old Machine @ end of economic lifeCurrent Salvage Value Old Machine
Percentage Change
Res
ultin
g N
PV
Formulas:
559
CHAPTER 12: RISK, CAPITAL BUDGETING AND DIVERSIFICATIONIM Problem Set & Solutions
560
CHAPTER 12: RISK, CAPITAL BUDGETING AND DIVERSIFICATIONIM Problem Set & Solutions
3. Consider the data of problem #2 and perform a scenario analysis using the percentages of change for the same uncertain variables and the probabilities for each scenario given in the following table:
Probability 5% 10% 20% 30% 20% 10% 5%Scenario #1 #2 #3 #4 #5 #6 #7Net Working Capital Investment +30% +20% +10% 0% -10% -20% -30%Price of the new machine +30% +20% +10% 0% -10% -20% -30%Operating cost savings -30% -20% -10% 0% +10% +20% +30%Salvage value of the old machine at the end of its economic life
+30% +20% +10% 0% -10% -20% -30%
Current salvage value of the old machine -30% -20% -10% 0% +10% +20% +30%
a. Determine the payback period, discounted payback period, NPV, PI, IRR, and MIRR of this project under each scenario.
Worksheet:
561
CHAPTER 12: RISK, CAPITAL BUDGETING AND DIVERSIFICATIONIM Problem Set & Solutions
a. Determine the expected NPV, IP, and IRR, and the corresponding variance, standard deviation, coefficient of variation, and the probability of a negative NPV, a PI equal to 1, and a IRR equal to the firm’s IRR.
562
CHAPTER 12: RISK, CAPITAL BUDGETING AND DIVERSIFICATIONIM Problem Set & Solutions
Worksheet:
Formulas:
b. Perform a Monte Carlo simulation with 1,000 trials to determine the expected NPV, IRR, and PI. Also determine the standard deviation of the expected NPV, PI, and IRR. The uncertain variables and their probability distributions are given in the following table:
563
CHAPTER 12: RISK, CAPITAL BUDGETING AND DIVERSIFICATIONIM Problem Set & Solutions
Uncertain Variable DistributionNet Working Capital Investment Uniform: min. of $30K and max. of $50K
Price of the new machineTriangular: min of $1.2M, most likely of $1.3M, and max. of $1.5M
Operating cost savings Normal: mean of $400K and standard deviation of $250KSalvage value of the old machine at the end of its economic life
Triangular: min of $150K, most likely of $180K, and max. of $200K
Current salvage value of the old machineTriangular: min of$180K, most likely of $200K, and max. of $220K
Worksheet:
564
CHAPTER 12: RISK, CAPITAL BUDGETING AND DIVERSIFICATIONIM Problem Set & Solutions
Formulas
565
CHAPTER 12: RISK, CAPITAL BUDGETING AND DIVERSIFICATIONIM Problem Set & Solutions
c. Create a histogram showing the probability distribution of NPV, IRR, and PI.
566
CHAPTER 12: RISK, CAPITAL BUDGETING AND DIVERSIFICATIONIM Problem Set & Solutions
-14176
15.76
-12342
01.39
8
-10507
87.03
6
-86737
2.674
2
-68395
8.312
3
-50054
3.950
3
-31712
9.588
4
-13371
5.226
5
49699
.1354
8
23311
3.497
4
41652
7.859
4
59994
2.221
3
78335
6.583
2
96677
0.945
2
11501
85.30
7
13335
99.66
90
20
40
60
80
NPV Histogram
Frequency
Bin
Freq
uenc
y
-0.154
1950
06
-0.005
2843
27
0.143
6263
52
0.292
5370
31
0.441
4477
1
0.590
3583
89
0.739
2690
68
0.888
1797
47
1.037
0904
26
1.186
0011
05
1.334
91178
4
1.483
8224
63
1.632
7331
42
1.781
6438
21
1.930
5545
2.079
4651
790
20
40
60
80
PI Histogram
Frequency
Bin
Freq
uenc
y567
CHAPTER 12: RISK, CAPITAL BUDGETING AND DIVERSIFICATIONIM Problem Set & Solutions
-0.600
5689
03
-0.528
7846
73
-0.457
0004
43
-0.385
2162
13
-0.313
4319
83
-0.241
6477
53
-0.169
8635
23
-0.098
0792
93
-0.026
2950
62
0.045
4891
68
0.11727
3398
0.189
0576
28
0.260
8418
58
0.332
6260
88
0.404
4103
18
0.476
1945
480
40
80
120
IRR Histogram
Frequency
Bin
Freq
uenc
y
d. Using the output of the simulation, determine the probability that the NPV will be less than or equal to zero, the IRR will be less than or equal to the firm’s WACC, and the PI will be less than or equal to 1. Compare your results with those of part a.
Worksheet:
Formulas:
4. Sophisticated Widgets Inc. is considering an investment project that will require an initial investment of $200K in net working capital over an estimated project's life of 5 years. The project's incremental sales are expected to be 300K units at a price of $15 per unit for the first
568
CHAPTER 12: RISK, CAPITAL BUDGETING AND DIVERSIFICATIONIM Problem Set & Solutions
year. The price per unit and the annual fixed costs are expected to grow at an average rate of inflation of 3% per year. The variable costs will represent 65% of total annual revenues and the fixed costs will be $800K annually. The capital spending associated with the project will cost $1.2M and will require an additional $150K of shipping and installation expenses. The fixed assets associated with the project will be depreciated using the MACRS 7-year class life. After five years the project's fixed assets can be sold for $350K. The firm's WACC is 15% and its marginal tax rate is 40%.
a. Calculate the initial investment, annual after-tax cash flows, and the terminal cash flow of this investment project.
Worksheet:
569
CHAPTER 12: RISK, CAPITAL BUDGETING AND DIVERSIFICATIONIM Problem Set & Solutions
Formulas:
570
CHAPTER 12: RISK, CAPITAL BUDGETING AND DIVERSIFICATIONIM Problem Set & Solutions571
CHAPTER 12: RISK, CAPITAL BUDGETING AND DIVERSIFICATIONIM Problem Set & Solutions
b. Determine the payback period, discounted payback period, NPV, PI, IRR, and MIRR of this project. Should this project be accepted?
Worksheet:
572
CHAPTER 12: RISK, CAPITAL BUDGETING AND DIVERSIFICATIONIM Problem Set & Solutions
Formulas:
Possible Answer: Based on the positive NPV of $647,058.77, the profitability index of 1.42, and the IRR of 29.83% and greater than the firm’s WACC, the project should be accepted.
c. Perform the same sensitivity analysis as that on Exhibit 12-5, page 379 (except that you should use increments of 5% from -15% to 15%, instead of 10% like in the book) using the following uncertain variables: number of units, variable cost, investment in net working capital, salvage value and inflation rate. Create a Scatter char including all of these variables.
Worksheet:
573
CHAPTER 12: RISK, CAPITAL BUDGETING AND DIVERSIFICATIONIM Problem Set & Solutions
574
CHAPTER 12: RISK, CAPITAL BUDGETING AND DIVERSIFICATIONIM Problem Set & Solutions
-20% -10% 0% 10% 20%($1,000,000.00)
$0.00
$1,000,000.00
$2,000,000.00
Sensitivity Diagram
Working Capital InvestmentNumber of UnitsInflation RateVariable CostSalvage Value
Percentage Change
Res
ultin
g N
PV
Formulas:
575
CHAPTER 12: RISK, CAPITAL BUDGETING AND DIVERSIFICATIONIM Problem Set & Solutions
5. Powerful Wind, Inc. is a wind turbine manufacturer that is considering three investment projects: I, II, and III, that will cost $2.5M, $2.3M, and $3.7M respectively. The projects have an expected life of three, five, and seven years correspondingly. The firm’s Vice President of Finance has estimated the probability distribution for each project’s first after-tax cash flow (ATCF1) as shown in the following table:
ScenarioProbabilit
y Project I's ATCF1 Project II's ATCF1 Project III's ATCF1
1 5% $1,000,000 $550,000 $350,0002 15% $1,150,000 $750,000 $600,0003 60% $1,250,000 $950,000 $850,0004 15% $1,350,000 $1,150,000 $1,100,0005 5% $1,500,000 $1,350,000 $1,350,000
The Vice President of Finance uses the risk-adjusted discount technique to evaluate investment projects. He allocates risk premiums based on the coefficient of variation of each project’s after-tax cash flows according to the following table:
Coefficient of Variation Risk Premium0.0 -2.00%0.2 0.00%0.3 2.00%0.4 4.00%0.5 6.00%
576
CHAPTER 12: RISK, CAPITAL BUDGETING AND DIVERSIFICATIONIM Problem Set & Solutions
Each project’s after-tax cash flows are expected to grow at an annual rate of inflation of 3%.
a. Determine the expected cash flows, standard deviation, and coefficient of variation of each project.
Worksheet:
Formulas:
577
CHAPTER 12: RISK, CAPITAL BUDGETING AND DIVERSIFICATIONIM Problem Set & Solutions
b. If the firm’s WACC is 12%, what is the appropriate risk-adjusted required rate of return of each project?
Worksheet:
Formulas:
578
CHAPTER 12: RISK, CAPITAL BUDGETING AND DIVERSIFICATIONIM Problem Set & Solutions
c. Using the appropriate discount rates, determine the payback period, discounted payback period, NPV, PI, IRR, and MIRR for each project.
Worksheet:
Formulas:
579
CHAPTER 12: RISK, CAPITAL BUDGETING AND DIVERSIFICATIONIM Problem Set & Solutions
d. If the projects are mutually exclusive, which project should be accepted? Answer the same question assuming they are independent projects.
Possible Answer: Project II has the highest NPV ($1,053,784.21 compared to $533,567.19 of I and -$116,571.74 of III), has the highest IRR (32.64% compared to 25.08% of I and 15.94% of III), and has the highest PI (1.46 compared to 1.21 of I, and 0.97 of III.) Therefore, if these projects are mutually exclusive, project II should be accepted instead of I or III. On the other hand, if these projects were independent, I and II should be accepted since both have positive NPVs, IRRs greater than the appropriate risk-adjusted required rate of return of each project, and a PIs greater than 1. On the other hand, III should be rejected since it has a negative NPV, an IRR lower than the appropriate risk-adjusted required rate of return for this project, and a PI less than one.
6. Use the data of the previous problem to perform the following analysis:
a. Perform a sensitivity analysis using the tool Data Table. Determine the NPV, IRR, and PI as the ATCF1 takes the expected values under each scenario.
Worksheet:
580
CHAPTER 12: RISK, CAPITAL BUDGETING AND DIVERSIFICATIONIM Problem Set & Solutions
b. Determine the variance and standard deviation of the NPVs, PI, and IRR of all projects.
c. Determine the coefficient of variation of the NPVs PI, and IRR of all projects.
d. Calculate the probability of a negative NPV, a PI less than one, and an IRR less than the firm’s WACC of 15% for all projects.
Worksheet:
581
CHAPTER 12: RISK, CAPITAL BUDGETING AND DIVERSIFICATIONIM Problem Set & Solutions
Formulas:
582
CHAPTER 12: RISK, CAPITAL BUDGETING AND DIVERSIFICATIONIM Problem Set & Solutions
e. Based on your results on parts a, b, c, and d, which project should be accepted?
583
CHAPTER 12: RISK, CAPITAL BUDGETING AND DIVERSIFICATIONIM Problem Set & Solutions
Possible Answer: The fact that project II has a higher expected NPV than I or III (NPV II = $1,053,784.21 compared to NPVI = $533,567.19 or NPVIII = – $116,567.74), or that project I has the lowest probability of a negative NPV (4.35% compared to 8.65% for II and 54.02% for III), the lowest probability of a PI less than one, and the lowest probability of an IRR less than the appropriate risk-adjusted required rate of return should not constitute clear evidence of accepting one project over the other. The individual decision maker must decide.
584
CHAPTER 12: RISK, CAPITAL BUDGETING AND DIVERSIFICATIONIM Problem Set & Solutions
Test Bank
1. Which is the right formula on cell B5?a. {=SUM(C2:C4*B2:B4)}b. =B2*C2+B3*C3+B4*C4c. {=SUM(B2:B4*C2:C4)}d. All of the abovee. None of the above
Solution: d.
2. Which is the right formula on cell B5?
Remember that the basic syntax of the FAME_VAR function is:
FAME_VAR(VALUES, PROBABILITIES)
a. =FAME_Var(C2:C4,B2:B4)b. =B2*(C2-D7)^2+B3*(C3-D7)^2+B4*(C4-D7)^2c. {=SUM(B2:B4*(C2:C4-D7)^2)}d. {=SUM((C2:C4-D7)^2*B2:B4)}e. All of the aboveSolution: e.
3. Which is the right formula on cell B5?
Remember that the basic syntax of the FAME_STDDEV function is:FAME_STDDEV(VALUES,
PROBABILITIES)
a. =FAME_StdDev(C2:C4,B2:B4)b. =SQRT(B2*(C2-D7)^2+B3*(C3-D7)^2+B4*(C4-D7)^2)c. { =SQRT(SUM(B2:B4*(C2:C4-D7)^2))}d. { =SQRT(SUM((C2:C4-D7)^2*B2:B4))}e. All of the aboveSolution: c.
585
CHAPTER 12: RISK, CAPITAL BUDGETING AND DIVERSIFICATIONIM Problem Set & Solutions
4. What should be the correct formula on cell B6 to determine the probability of rejecting the project according to the profitability index’s decision?a. =NORMSDIST((0-B2)/B4)b. =NORMSDIST((1-B2)/B4)c. =NORMSDIST((0-B2)/B5)d. =NORMSDIST((1-B2)/B3)e. =NORMSDIST((0-B2)/B3)
Solution: b.
5. What should be the correct formula on cell B7 to determine the probability of rejecting the project according to the IRR’s decision?a. =NORMSDIST((B6-B2)/B4)b. =NORMSDIST((0-B2)/B4)c. =NORMSDIST((B6-B2)/B5)d. =NORMSDIST((0-B2)/B3)e. =NORMSDIST((B6-B2)/B3)
Solution: a.
6. What would be the outcome of cell B8?
Remember that the basic syntax of the COUNTIF function is:COUNTIF(range,criteria)
a. 20.00%b. 30.00%c. 33.33%d. 50.00%e. 66.67%
586
CHAPTER 12: RISK, CAPITAL BUDGETING AND DIVERSIFICATIONIM Problem Set & Solutions
Solution: c.7. What would be the outcome of cell B8?
Remember that the basic syntax of the COUNTIF function is:COUNTIF(range,criteria)
a. =COUNT(A2:A7)/COUNTIF(B2:B7,"A")b. =COUNTIF(A2:A7,"A")/COUNT(A2:A7)c. =COUNTIF(B2:B7,"A")/COUNT(B2:B7)d. =COUNTIF(B2:B7,"A")/COUNT(A2:A7)e. =COUNTIF(A2:A7,"A")/COUNT(B2:B7)Solution: d.
8. The table below contains the results of a Monte Carlo simulation with 1,000 trials to determine the expected NPV. What should be the correct formula on cell B1005 to determine the probability that NPV will be less than or equal to zero?
Remember that the basic syntax of the COUNTIF function is:
COUNTIF(range,criteria)
a. =NORMSDIST((0-B1003)/B1004)b. =COUNTIF(B3:B1002,"<=0")/1000c. =COUNTIF(B3:B1002,"<=0")/COUNT(A3:A1002)d. All of the abovee. None of the above
587
CHAPTER 12: RISK, CAPITAL BUDGETING AND DIVERSIFICATIONIM Problem Set & Solutions
Solution: d.
9. The table below contains the results of a Monte Carlo simulation with 1,000 trials to determine the expected Profitability Index (PI). What should be the correct formula on cell B1005 to determine the probability that the project will be rejected according to the PI’s decision rule?
Remember that the basic syntax of the COUNTIF function is:
COUNTIF(range,criteria)
a. =COUNTIF(B3:B1002,"<=1")/COUNT(B3:B1002)b. =COUNTIF(B3:B1002,"<=1")/COUNT(A3:A1002)c. =COUNTIF(B3:B1002,"<=0")/COUNT(A3:A1002)d. =COUNTIF(A3:A1002,"<=0")/COUNT(A3:A1002)e. =COUNTIF(B3:B1002,"<=0")/COUNT(B3:B1002)Solution: b.
588
CHAPTER 12: RISK, CAPITAL BUDGETING AND DIVERSIFICATIONIM Problem Set & Solutions
10. The table below contains the results of a Monte Carlo simulation with 1,000 trials to determine the expected IRR. What should be the correct formula on cell B1005 to determine the probability that the project will be rejected according to the IRR’s decision rule?
Remember that the basic syntax of the COUNTIF function is:
COUNTIF(range,criteria)
a. =COUNTIF(D3:D1002,"<=.17")/COUNT(A3:A1002)b. =COUNTIF(D3:D1002,"<=0 ")/COUNT(A3:A1002)c. =COUNTIF(D3:D1002,"<=0 ")/COUNT(B3:B1002)d. =COUNTIF(D3:D1002,"<=.17")/COUNT(B3:B1002)e. =COUNTIF(D3:D1002,"<=0 ")/1000Solution: a.
11. Where the _____ technique increases the discount rate to adjust for risk, the _________ decreases the cash flow.a. CE approach; IRRb. RADR; CE approach c. MIRR; CE approach d. MIRR; RADRe; RADR; MIRRSolution: b.
589
CHAPTER 12: RISK, CAPITAL BUDGETING AND DIVERSIFICATIONIM Problem Set & Solutions
12. Which should be the right formula for cell C4?a. =(B5-(B3/C3)-(B2/C2))/B4b. =(B5-(B3*C3)-(B2*C2))*B4c. =(B5-(B3*C3)-(B2*C2))/B4d. =(B5+(B3*C3)+(B2*C2))/B4e. =(B5-(B3*C3)+(B2*C2))/B4
Solution: c.
13. Which should be the right formula for cell B5?a. =(B2*C2)-(B3*C3)+(1-B2+B3)*C4b. =(B2*C2)-(B3*C3)-(1-B2-B3)*C4c. =(B2/C2)+(B3/C3)+(1-B2-B3)/C4d. =(B2*C2)+(B3*C3)+(1-B2-B3)*C4e. =(B2*C2)+(B3*C3)+(1+B2+B3)*C4
Solution: d.
14. Which value should be entered in the “Column input cell” window?a. B1b. B2c. B3d. B4e. B5
Solution: a.
15. Which should be the right formula for cell B4?a. =B1+B2+B3b. =B1+B2-B3c. =B1-B2+B3
590
CHAPTER 12: RISK, CAPITAL BUDGETING AND DIVERSIFICATIONIM Problem Set & Solutions
d. =B1-B2-B3e. =B1*B2/B3
Solution: c.
591