chapter 1 spreadsheet basics - leeds school of businessleeds-faculty.colorado.edu/donchez/fnce...
TRANSCRIPT
![Page 1: Chapter 1 Spreadsheet Basics - Leeds School of Businessleeds-faculty.colorado.edu/Donchez/FNCE 4050/4050... · Web viewIM Problem Set & Solutions 587 CHAPTER 12: RISK, CAPITAL BUDGETING](https://reader034.vdocuments.us/reader034/viewer/2022052119/5afee1d67f8b9a994d8f9a5c/html5/thumbnails/1.jpg)
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
![Page 2: Chapter 1 Spreadsheet Basics - Leeds School of Businessleeds-faculty.colorado.edu/Donchez/FNCE 4050/4050... · Web viewIM Problem Set & Solutions 587 CHAPTER 12: RISK, CAPITAL BUDGETING](https://reader034.vdocuments.us/reader034/viewer/2022052119/5afee1d67f8b9a994d8f9a5c/html5/thumbnails/2.jpg)
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
![Page 3: Chapter 1 Spreadsheet Basics - Leeds School of Businessleeds-faculty.colorado.edu/Donchez/FNCE 4050/4050... · Web viewIM Problem Set & Solutions 587 CHAPTER 12: RISK, CAPITAL BUDGETING](https://reader034.vdocuments.us/reader034/viewer/2022052119/5afee1d67f8b9a994d8f9a5c/html5/thumbnails/3.jpg)
CHAPTER 12: RISK, CAPITAL BUDGETING AND DIVERSIFICATIONIM Problem Set & Solutions551
![Page 4: Chapter 1 Spreadsheet Basics - Leeds School of Businessleeds-faculty.colorado.edu/Donchez/FNCE 4050/4050... · Web viewIM Problem Set & Solutions 587 CHAPTER 12: RISK, CAPITAL BUDGETING](https://reader034.vdocuments.us/reader034/viewer/2022052119/5afee1d67f8b9a994d8f9a5c/html5/thumbnails/4.jpg)
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
![Page 5: Chapter 1 Spreadsheet Basics - Leeds School of Businessleeds-faculty.colorado.edu/Donchez/FNCE 4050/4050... · Web viewIM Problem Set & Solutions 587 CHAPTER 12: RISK, CAPITAL BUDGETING](https://reader034.vdocuments.us/reader034/viewer/2022052119/5afee1d67f8b9a994d8f9a5c/html5/thumbnails/5.jpg)
CHAPTER 12: RISK, CAPITAL BUDGETING AND DIVERSIFICATIONIM Problem Set & Solutions
Formulas:
553
![Page 6: Chapter 1 Spreadsheet Basics - Leeds School of Businessleeds-faculty.colorado.edu/Donchez/FNCE 4050/4050... · Web viewIM Problem Set & Solutions 587 CHAPTER 12: RISK, CAPITAL BUDGETING](https://reader034.vdocuments.us/reader034/viewer/2022052119/5afee1d67f8b9a994d8f9a5c/html5/thumbnails/6.jpg)
CHAPTER 12: RISK, CAPITAL BUDGETING AND DIVERSIFICATIONIM Problem Set & Solutions
554
![Page 7: Chapter 1 Spreadsheet Basics - Leeds School of Businessleeds-faculty.colorado.edu/Donchez/FNCE 4050/4050... · Web viewIM Problem Set & Solutions 587 CHAPTER 12: RISK, CAPITAL BUDGETING](https://reader034.vdocuments.us/reader034/viewer/2022052119/5afee1d67f8b9a994d8f9a5c/html5/thumbnails/7.jpg)
CHAPTER 12: RISK, CAPITAL BUDGETING AND DIVERSIFICATIONIM Problem Set & Solutions555
![Page 8: Chapter 1 Spreadsheet Basics - Leeds School of Businessleeds-faculty.colorado.edu/Donchez/FNCE 4050/4050... · Web viewIM Problem Set & Solutions 587 CHAPTER 12: RISK, CAPITAL BUDGETING](https://reader034.vdocuments.us/reader034/viewer/2022052119/5afee1d67f8b9a994d8f9a5c/html5/thumbnails/8.jpg)
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
![Page 9: Chapter 1 Spreadsheet Basics - Leeds School of Businessleeds-faculty.colorado.edu/Donchez/FNCE 4050/4050... · Web viewIM Problem Set & Solutions 587 CHAPTER 12: RISK, CAPITAL BUDGETING](https://reader034.vdocuments.us/reader034/viewer/2022052119/5afee1d67f8b9a994d8f9a5c/html5/thumbnails/9.jpg)
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
![Page 10: Chapter 1 Spreadsheet Basics - Leeds School of Businessleeds-faculty.colorado.edu/Donchez/FNCE 4050/4050... · Web viewIM Problem Set & Solutions 587 CHAPTER 12: RISK, CAPITAL BUDGETING](https://reader034.vdocuments.us/reader034/viewer/2022052119/5afee1d67f8b9a994d8f9a5c/html5/thumbnails/10.jpg)
CHAPTER 12: RISK, CAPITAL BUDGETING AND DIVERSIFICATIONIM Problem Set & Solutions
558
![Page 11: Chapter 1 Spreadsheet Basics - Leeds School of Businessleeds-faculty.colorado.edu/Donchez/FNCE 4050/4050... · Web viewIM Problem Set & Solutions 587 CHAPTER 12: RISK, CAPITAL BUDGETING](https://reader034.vdocuments.us/reader034/viewer/2022052119/5afee1d67f8b9a994d8f9a5c/html5/thumbnails/11.jpg)
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
![Page 12: Chapter 1 Spreadsheet Basics - Leeds School of Businessleeds-faculty.colorado.edu/Donchez/FNCE 4050/4050... · Web viewIM Problem Set & Solutions 587 CHAPTER 12: RISK, CAPITAL BUDGETING](https://reader034.vdocuments.us/reader034/viewer/2022052119/5afee1d67f8b9a994d8f9a5c/html5/thumbnails/12.jpg)
CHAPTER 12: RISK, CAPITAL BUDGETING AND DIVERSIFICATIONIM Problem Set & Solutions
560
![Page 13: Chapter 1 Spreadsheet Basics - Leeds School of Businessleeds-faculty.colorado.edu/Donchez/FNCE 4050/4050... · Web viewIM Problem Set & Solutions 587 CHAPTER 12: RISK, CAPITAL BUDGETING](https://reader034.vdocuments.us/reader034/viewer/2022052119/5afee1d67f8b9a994d8f9a5c/html5/thumbnails/13.jpg)
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
![Page 14: Chapter 1 Spreadsheet Basics - Leeds School of Businessleeds-faculty.colorado.edu/Donchez/FNCE 4050/4050... · Web viewIM Problem Set & Solutions 587 CHAPTER 12: RISK, CAPITAL BUDGETING](https://reader034.vdocuments.us/reader034/viewer/2022052119/5afee1d67f8b9a994d8f9a5c/html5/thumbnails/14.jpg)
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
![Page 15: Chapter 1 Spreadsheet Basics - Leeds School of Businessleeds-faculty.colorado.edu/Donchez/FNCE 4050/4050... · Web viewIM Problem Set & Solutions 587 CHAPTER 12: RISK, CAPITAL BUDGETING](https://reader034.vdocuments.us/reader034/viewer/2022052119/5afee1d67f8b9a994d8f9a5c/html5/thumbnails/15.jpg)
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
![Page 16: Chapter 1 Spreadsheet Basics - Leeds School of Businessleeds-faculty.colorado.edu/Donchez/FNCE 4050/4050... · Web viewIM Problem Set & Solutions 587 CHAPTER 12: RISK, CAPITAL BUDGETING](https://reader034.vdocuments.us/reader034/viewer/2022052119/5afee1d67f8b9a994d8f9a5c/html5/thumbnails/16.jpg)
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
![Page 17: Chapter 1 Spreadsheet Basics - Leeds School of Businessleeds-faculty.colorado.edu/Donchez/FNCE 4050/4050... · Web viewIM Problem Set & Solutions 587 CHAPTER 12: RISK, CAPITAL BUDGETING](https://reader034.vdocuments.us/reader034/viewer/2022052119/5afee1d67f8b9a994d8f9a5c/html5/thumbnails/17.jpg)
CHAPTER 12: RISK, CAPITAL BUDGETING AND DIVERSIFICATIONIM Problem Set & Solutions
Formulas
565
![Page 18: Chapter 1 Spreadsheet Basics - Leeds School of Businessleeds-faculty.colorado.edu/Donchez/FNCE 4050/4050... · Web viewIM Problem Set & Solutions 587 CHAPTER 12: RISK, CAPITAL BUDGETING](https://reader034.vdocuments.us/reader034/viewer/2022052119/5afee1d67f8b9a994d8f9a5c/html5/thumbnails/18.jpg)
CHAPTER 12: RISK, CAPITAL BUDGETING AND DIVERSIFICATIONIM Problem Set & Solutions
c. Create a histogram showing the probability distribution of NPV, IRR, and PI.
566
![Page 19: Chapter 1 Spreadsheet Basics - Leeds School of Businessleeds-faculty.colorado.edu/Donchez/FNCE 4050/4050... · Web viewIM Problem Set & Solutions 587 CHAPTER 12: RISK, CAPITAL BUDGETING](https://reader034.vdocuments.us/reader034/viewer/2022052119/5afee1d67f8b9a994d8f9a5c/html5/thumbnails/19.jpg)
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
![Page 20: Chapter 1 Spreadsheet Basics - Leeds School of Businessleeds-faculty.colorado.edu/Donchez/FNCE 4050/4050... · Web viewIM Problem Set & Solutions 587 CHAPTER 12: RISK, CAPITAL BUDGETING](https://reader034.vdocuments.us/reader034/viewer/2022052119/5afee1d67f8b9a994d8f9a5c/html5/thumbnails/20.jpg)
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
![Page 21: Chapter 1 Spreadsheet Basics - Leeds School of Businessleeds-faculty.colorado.edu/Donchez/FNCE 4050/4050... · Web viewIM Problem Set & Solutions 587 CHAPTER 12: RISK, CAPITAL BUDGETING](https://reader034.vdocuments.us/reader034/viewer/2022052119/5afee1d67f8b9a994d8f9a5c/html5/thumbnails/21.jpg)
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
![Page 22: Chapter 1 Spreadsheet Basics - Leeds School of Businessleeds-faculty.colorado.edu/Donchez/FNCE 4050/4050... · Web viewIM Problem Set & Solutions 587 CHAPTER 12: RISK, CAPITAL BUDGETING](https://reader034.vdocuments.us/reader034/viewer/2022052119/5afee1d67f8b9a994d8f9a5c/html5/thumbnails/22.jpg)
CHAPTER 12: RISK, CAPITAL BUDGETING AND DIVERSIFICATIONIM Problem Set & Solutions
Formulas:
570
![Page 23: Chapter 1 Spreadsheet Basics - Leeds School of Businessleeds-faculty.colorado.edu/Donchez/FNCE 4050/4050... · Web viewIM Problem Set & Solutions 587 CHAPTER 12: RISK, CAPITAL BUDGETING](https://reader034.vdocuments.us/reader034/viewer/2022052119/5afee1d67f8b9a994d8f9a5c/html5/thumbnails/23.jpg)
CHAPTER 12: RISK, CAPITAL BUDGETING AND DIVERSIFICATIONIM Problem Set & Solutions571
![Page 24: Chapter 1 Spreadsheet Basics - Leeds School of Businessleeds-faculty.colorado.edu/Donchez/FNCE 4050/4050... · Web viewIM Problem Set & Solutions 587 CHAPTER 12: RISK, CAPITAL BUDGETING](https://reader034.vdocuments.us/reader034/viewer/2022052119/5afee1d67f8b9a994d8f9a5c/html5/thumbnails/24.jpg)
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
![Page 25: Chapter 1 Spreadsheet Basics - Leeds School of Businessleeds-faculty.colorado.edu/Donchez/FNCE 4050/4050... · Web viewIM Problem Set & Solutions 587 CHAPTER 12: RISK, CAPITAL BUDGETING](https://reader034.vdocuments.us/reader034/viewer/2022052119/5afee1d67f8b9a994d8f9a5c/html5/thumbnails/25.jpg)
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
![Page 26: Chapter 1 Spreadsheet Basics - Leeds School of Businessleeds-faculty.colorado.edu/Donchez/FNCE 4050/4050... · Web viewIM Problem Set & Solutions 587 CHAPTER 12: RISK, CAPITAL BUDGETING](https://reader034.vdocuments.us/reader034/viewer/2022052119/5afee1d67f8b9a994d8f9a5c/html5/thumbnails/26.jpg)
CHAPTER 12: RISK, CAPITAL BUDGETING AND DIVERSIFICATIONIM Problem Set & Solutions
574
![Page 27: Chapter 1 Spreadsheet Basics - Leeds School of Businessleeds-faculty.colorado.edu/Donchez/FNCE 4050/4050... · Web viewIM Problem Set & Solutions 587 CHAPTER 12: RISK, CAPITAL BUDGETING](https://reader034.vdocuments.us/reader034/viewer/2022052119/5afee1d67f8b9a994d8f9a5c/html5/thumbnails/27.jpg)
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
![Page 28: Chapter 1 Spreadsheet Basics - Leeds School of Businessleeds-faculty.colorado.edu/Donchez/FNCE 4050/4050... · Web viewIM Problem Set & Solutions 587 CHAPTER 12: RISK, CAPITAL BUDGETING](https://reader034.vdocuments.us/reader034/viewer/2022052119/5afee1d67f8b9a994d8f9a5c/html5/thumbnails/28.jpg)
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
![Page 29: Chapter 1 Spreadsheet Basics - Leeds School of Businessleeds-faculty.colorado.edu/Donchez/FNCE 4050/4050... · Web viewIM Problem Set & Solutions 587 CHAPTER 12: RISK, CAPITAL BUDGETING](https://reader034.vdocuments.us/reader034/viewer/2022052119/5afee1d67f8b9a994d8f9a5c/html5/thumbnails/29.jpg)
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
![Page 30: Chapter 1 Spreadsheet Basics - Leeds School of Businessleeds-faculty.colorado.edu/Donchez/FNCE 4050/4050... · Web viewIM Problem Set & Solutions 587 CHAPTER 12: RISK, CAPITAL BUDGETING](https://reader034.vdocuments.us/reader034/viewer/2022052119/5afee1d67f8b9a994d8f9a5c/html5/thumbnails/30.jpg)
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
![Page 31: Chapter 1 Spreadsheet Basics - Leeds School of Businessleeds-faculty.colorado.edu/Donchez/FNCE 4050/4050... · Web viewIM Problem Set & Solutions 587 CHAPTER 12: RISK, CAPITAL BUDGETING](https://reader034.vdocuments.us/reader034/viewer/2022052119/5afee1d67f8b9a994d8f9a5c/html5/thumbnails/31.jpg)
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
![Page 32: Chapter 1 Spreadsheet Basics - Leeds School of Businessleeds-faculty.colorado.edu/Donchez/FNCE 4050/4050... · Web viewIM Problem Set & Solutions 587 CHAPTER 12: RISK, CAPITAL BUDGETING](https://reader034.vdocuments.us/reader034/viewer/2022052119/5afee1d67f8b9a994d8f9a5c/html5/thumbnails/32.jpg)
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
![Page 33: Chapter 1 Spreadsheet Basics - Leeds School of Businessleeds-faculty.colorado.edu/Donchez/FNCE 4050/4050... · Web viewIM Problem Set & Solutions 587 CHAPTER 12: RISK, CAPITAL BUDGETING](https://reader034.vdocuments.us/reader034/viewer/2022052119/5afee1d67f8b9a994d8f9a5c/html5/thumbnails/33.jpg)
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
![Page 34: Chapter 1 Spreadsheet Basics - Leeds School of Businessleeds-faculty.colorado.edu/Donchez/FNCE 4050/4050... · Web viewIM Problem Set & Solutions 587 CHAPTER 12: RISK, CAPITAL BUDGETING](https://reader034.vdocuments.us/reader034/viewer/2022052119/5afee1d67f8b9a994d8f9a5c/html5/thumbnails/34.jpg)
CHAPTER 12: RISK, CAPITAL BUDGETING AND DIVERSIFICATIONIM Problem Set & Solutions
Formulas:
582
![Page 35: Chapter 1 Spreadsheet Basics - Leeds School of Businessleeds-faculty.colorado.edu/Donchez/FNCE 4050/4050... · Web viewIM Problem Set & Solutions 587 CHAPTER 12: RISK, CAPITAL BUDGETING](https://reader034.vdocuments.us/reader034/viewer/2022052119/5afee1d67f8b9a994d8f9a5c/html5/thumbnails/35.jpg)
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
![Page 36: Chapter 1 Spreadsheet Basics - Leeds School of Businessleeds-faculty.colorado.edu/Donchez/FNCE 4050/4050... · Web viewIM Problem Set & Solutions 587 CHAPTER 12: RISK, CAPITAL BUDGETING](https://reader034.vdocuments.us/reader034/viewer/2022052119/5afee1d67f8b9a994d8f9a5c/html5/thumbnails/36.jpg)
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
![Page 37: Chapter 1 Spreadsheet Basics - Leeds School of Businessleeds-faculty.colorado.edu/Donchez/FNCE 4050/4050... · Web viewIM Problem Set & Solutions 587 CHAPTER 12: RISK, CAPITAL BUDGETING](https://reader034.vdocuments.us/reader034/viewer/2022052119/5afee1d67f8b9a994d8f9a5c/html5/thumbnails/37.jpg)
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
![Page 38: Chapter 1 Spreadsheet Basics - Leeds School of Businessleeds-faculty.colorado.edu/Donchez/FNCE 4050/4050... · Web viewIM Problem Set & Solutions 587 CHAPTER 12: RISK, CAPITAL BUDGETING](https://reader034.vdocuments.us/reader034/viewer/2022052119/5afee1d67f8b9a994d8f9a5c/html5/thumbnails/38.jpg)
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
![Page 39: Chapter 1 Spreadsheet Basics - Leeds School of Businessleeds-faculty.colorado.edu/Donchez/FNCE 4050/4050... · Web viewIM Problem Set & Solutions 587 CHAPTER 12: RISK, CAPITAL BUDGETING](https://reader034.vdocuments.us/reader034/viewer/2022052119/5afee1d67f8b9a994d8f9a5c/html5/thumbnails/39.jpg)
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
![Page 40: Chapter 1 Spreadsheet Basics - Leeds School of Businessleeds-faculty.colorado.edu/Donchez/FNCE 4050/4050... · Web viewIM Problem Set & Solutions 587 CHAPTER 12: RISK, CAPITAL BUDGETING](https://reader034.vdocuments.us/reader034/viewer/2022052119/5afee1d67f8b9a994d8f9a5c/html5/thumbnails/40.jpg)
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
![Page 41: Chapter 1 Spreadsheet Basics - Leeds School of Businessleeds-faculty.colorado.edu/Donchez/FNCE 4050/4050... · Web viewIM Problem Set & Solutions 587 CHAPTER 12: RISK, CAPITAL BUDGETING](https://reader034.vdocuments.us/reader034/viewer/2022052119/5afee1d67f8b9a994d8f9a5c/html5/thumbnails/41.jpg)
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
![Page 42: Chapter 1 Spreadsheet Basics - Leeds School of Businessleeds-faculty.colorado.edu/Donchez/FNCE 4050/4050... · Web viewIM Problem Set & Solutions 587 CHAPTER 12: RISK, CAPITAL BUDGETING](https://reader034.vdocuments.us/reader034/viewer/2022052119/5afee1d67f8b9a994d8f9a5c/html5/thumbnails/42.jpg)
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
![Page 43: Chapter 1 Spreadsheet Basics - Leeds School of Businessleeds-faculty.colorado.edu/Donchez/FNCE 4050/4050... · Web viewIM Problem Set & Solutions 587 CHAPTER 12: RISK, CAPITAL BUDGETING](https://reader034.vdocuments.us/reader034/viewer/2022052119/5afee1d67f8b9a994d8f9a5c/html5/thumbnails/43.jpg)
CHAPTER 12: RISK, CAPITAL BUDGETING AND DIVERSIFICATIONIM Problem Set & Solutions
d. =B1-B2-B3e. =B1*B2/B3
Solution: c.
591