Capital Budgeting with Uncertainty- Chapter 13
How many units can we sell? How much will raw materials cost? Making a capital budgeting
decisions is dealing with tons of uncertainty. We will take more sensitivity analyses, scenario
analyses and Monte-Carlo simulations to determine the uncertainty surrounds our estimate of the
NPV.
We first develop a cash flow timeline for the Freshly Frozen Fish Company (FFF, p.419). Next
we use the DataTable (p.427) to create sensitivity analyses between NPVs and selected variables.
Then, given three scenarios and the probability of the occurrence of each scenario (p.434), we
estimate the expected value, variance and standard deviation of NPVs, and more importantly, we
compute the probability of a negative NPV. Last, we use Monte Carlo simulation (p.440), an
alternative approach to re-compute the probability of a negative NPV, checking the robustness of
our finding. Monte Carlo simulation is the computational algorithm that relies on repeated
random sampling to compute their results.
1
Table 1. Sensitivity Analysis
Frozen Catfish Fillet Project Inputs
Cost of Land
250,000
Cost of Buildings & Equipment
400,000
MACRS Class
20
Life of Project (Years)
5
Terminal Value of Land
350,000
Terminal Value of Buildings & Equipm
200,000
First Year Catfish Sales (lbs)
200,000
Price per Pound
2.50
Unit Sales Growth Rate
8%
Variable Costs as % of Sales
60%
Fixed Costs
80,000
Tax Rate
25%
WACC
10%
Initial Outlay
Sales
Variable Costs
Fixed Costs
Depreciation Expense
Taxable Cash Flows
Taxes
Add: Depreciation
Annual After-Tax Cash Flow
Terminal Cash Flow
Total Annual Cash Flows
Sensitivity %
0%
0%
0%
0%
0%
0%
Annual Cash Flows for Frozen Catfish Fillet Project
Year 0
Year 1
Year 2
Year 3
(650,000)
500,000
540,000
583,200
300,000
324,000
349,920
80,000
80,000
80,000
15,000
28,875
26,709
105,000
107,125
126,571
26,250
26,781
31,643
15,000
28,875
26,709
93,750
109,219
121,637
Net Present Value
Profitability Index
Internal Rate of Return
Modified Internal Rate of Return
Payback Period
Discounted Payback Period
(650,000)
93,750
109,219
121,637
Year 4
Year 5
629,856
377,914
80,000
24,706
147,236
36,809
24,706
135,133
680,244
408,147
80,000
22,853
169,245
42,311
22,853
149,787
545,464
695,251
135,133
$ 140,872.52
1.22
15.79%
14.40%
4.27
4.67
2
Slope
Importance
140,872.52
-30%
91,975
Sensitivity Tables
-20%
-10%
108,274
124,573
0%
140,873
10%
157,172
20%
173,471
30%
189,770
162,991.85
4
Value of Buildings & Equipment
140,872.52
-30%
112,931
-20%
122,245
-10%
131,559
0%
140,873
10%
150,186
20%
159,500
30%
168,814
93,138.20
6
First Year Catfish Sales (lbs)
140,872.52
-30%
(56,369)
-20%
9,378
-10%
75,125
0%
140,873
10%
206,620
20%
272,367
30%
338,114
657,471.50
2
Price per Pound
140,872.52
-30%
(56,369)
-20%
9,378
-10%
75,125
0%
140,873
10%
206,620
20%
272,367
30%
338,114
657,471.50
3
140,872.52
-30%
112,812
-20%
122,028
-10%
131,381
0%
140,873
10%
150,505
20%
160,279
30%
170,196
95,634.27
5
Variable Costs as % of Sales
140,872.52
-30%
436,735
-20%
338,114
-10%
239,493
0%
140,873
10%
42,252
20%
(56,369)
30%
(154,990)
986,207.25
1
Terminal Value of Land
Unit Sales Growth Rate
Sensitivity Diagram
NPV
300,000
200,000
Terminal Value of Land
100,000
Value of Buildings & Equipment
First Year Catfish Sales (lbs)
?
Price per Pound
(100,000)
(200,000)
?40%
Unit Sales Growth Rate
?30%
?20%
?10%
0%
10%
20%
30%
40%
Variable Costs as % of Sales
% Change in Variable
3
Table 2. Scenario Analysis
Scenario Summary
Worst Case
Base Case
Best Case
Changing Cells:
Catfish_Sales_Pounds
125,000
200,000
275,000
Price_Per_Pound
2.25
2.50
2.65
Var_Costs_Percent
65%
60%
55%
Result Cells:
Net_Present_Value
$
(192,999.73) $ 140,872.52 $ 561,448.82
Notes: Current Values column represents values of changing cells at
time Scenario Summary Report was created. Changing cells for each
scenario are highlighted in gray.
Probabilities
Expected NPV
Variance
Standard Deviation
Prob(NPV
Purchase answer to see full
attachment