Chapter 12: Cash Flow Estimation and Risk Analysis
Comprehensive/Spreadsheet Problem
327
Comprehensive/Spreadsheet Problem
Note to Instructors:
The solution to this problem is not provided to students at the back of their text. Instructors
can access the
Excel
file on the textbook’s website.
12–22 a.
c. If the new project will reduce cash flows from the firm‘s other projects, then this is a negative
d. The project’s cash flows are likely to be positively correlated with returns on the firm’s other
e. 1.
NPV (at 10% ) $4,014
WACC: 10.0%
328
Comprehensive/Spreadsheet Problem
Chapter 12: Cash Flow Estimation and Risk Analysis
2.
f.
Key Output
NPV = $4,014
IRR = 11.11%
Machine X:
Chapter 12: Cash Flow Estimation and Risk Analysis
Comprehensive/Spreadsheet Problem
329
Part 5. Key Output: Appraisal of the Proposed Project
g.
Part 4. Project Cash Flows (Time Line of Annual Cash Flows)
0 1 2 3
Investment Outlays at Time = 0:
Operating Cash Flows over the Project’s Life:
Units sold 115,000 115,000 115,000
Sales price $3.25 $3.25 $3.25
WACC
UNIT SALES
Deviation
from Sales Variable Unit Fixed Equipment
Base Case Price Costs Sales Costs WACC Cost
-20% ($107,521) $70,935 ($40,600) $24,904 $11,569 $27,294
NPV at Different Deviations from Base
$30,000
$50,000
$70,000
NPV
Sensitivity Analysis
Sales Price
Unit Sales
VARIABLE COSTS
SALES PRICE
FIXED COSTS
EQUIPMENT COST
Chapter 12: Cash Flow Estimation and Risk Analysis
Comprehensive/Spreadsheet Problem
331
h. Note that “best-case” values for variable costs, fixed costs, WACC, and equipment cost are
20% less than base-case values, while the “worst-case” values for variable costs, fixed costs,
WACC, and equipment cost are 20% higher than base-case values.
Probability Graph
Probability
50%
Continuous Approximation
Probability Density
(227,902) 0 26,093 324,244
Squared
Deviation
Sales Variable Unit Fixed Equipment Times
Scenario Probability Price Costs Sales Costs WACC Cost NPV Probability
332
Integrated Case
Chapter 12: Cash Flow Estimation and Risk Analysis
Integrated Case
12-23
Allied Food Products
Capital Budgeting and Cash Flow Estimation
Allied Food Products is considering expanding into the fruit juice business with a
new fresh lemon juice product. Assume that you were recently hired as assistant
The project is expected to operate for 4 years, at which time it will be
terminated. The cash inflows are assumed to begin 1 year after the project is
Chapter 12: Cash Flow Estimation and Risk Analysis
Integrated Case
333
Table IC 12.1. Allied’s Lemon Juice Project (in Thousands)
End of Year: 0 1 2 3 4
I. Investment Outlays
Equipment cost
Installation
CAPEX
Increase in inventory
Increase in accounts payable
NOWC
III. Project Termination Cash Flows
Salvage value (taxed as ord. income)
Tax on salvage value
After-tax salvage value
NOWC = Recovery of NOWC
334
Integrated Case
Chapter 12: Cash Flow Estimation and Risk Analysis
A. Allied has a standard form that is used in the capital budgeting
process. (See Table IC 12.1.) Part of the table has been completed,
but you must replace the blanks with the missing numbers.
Complete the table using the following steps:
(1) Fill in the blanks under Year 0 for the initial investment outlays:
CAPEX and NOWC.
Answer: [Show S12-1 through S12-5 here.] This answer is straightforward.
Note that accounts payable is an offset to the inventory buildup, so
A. (2) Complete the table for unit sales, sales price, total revenues, and
operating costs excluding depreciation.
Answer: This answer requires no explanation. Students may note, though,
A. (3) Complete the depreciation data.
Answer: [Show S12-6 here.] The only item that requires explanation here is
the use of the depreciation tables in Appendix 12A. Here are the
Chapter 12: Cash Flow Estimation and Risk Analysis
Integrated Case
335
A. (4) Complete the table down to after-tax operating income and then
down to the project’s operating cash flows, EBIT(1 – T) + DEP.
Answer: [Show S12-7 here.] This is straightforward. The only even slightly
A. (5) Fill in the blanks under Year 4 for the terminal cash flows, and
complete the project free cash flow line. Discuss the recovery of
net operating working capital. What would have happened if the
machinery were sold for less than its book value?
Answer: [Show S12-8 and S12-9 here.] These are all straightforward. Note
336
Integrated Case
Chapter 12: Cash Flow Estimation and Risk Analysis
Table IC 12.1. Allied’s Lemon Juice Project (in Thousands)
Inputs: Price: $2.00 WACC: 10% Infl: 0.0%
VC rate: 60.0% T-rate: 40%
End of Year: 0 1 2 3 4
II. Project Operating Cash Flows
Unit sales (thousands) 100 100 100 100
Price/unit $ 2.00 $ 2.00 $ 2.00 $ 2.00
IV. Results
Chapter 12: Cash Flow Estimation and Risk Analysis
Integrated Case
337
B. (1) Allied uses debt in its capital structure, so some of the money used
to finance the project will be debt. Given this fact, should the
projected cash flows be revised to show projected interest charges?
Explain.
Answer: [Show S12-10 here.] The projected cash flows in the table should
B. (2) Suppose you learned that Allied had spent $50,000 to renovate the
building last year, expensing these costs. Should this cost be
reflected in the analysis? Explain.
Answer: [Show S12-11 here.] This expenditure is a sunk cost, hence it
B. (3) Suppose you learned that Allied could lease its building to another
party and earn $25,000 per year. Should that fact be reflected in
the analysis? If so, how?
Answer: [Show S12–12 here.] The rental payment represents an opportunity
B. (4) Assume that the lemon juice project would take profitable sales
away from Allied’s fresh orange juice business. Should that fact be
reflected in your analysis? If so, how?
338
Integrated Case
Chapter 12: Cash Flow Estimation and Risk Analysis
Answer: [Show S12-13 here.] The decreased sales from Allied’s fresh
orange juice business should be accounted for in the analysis. This
C. Disregard all the assumptions made in part B and assume there is
no alternative use for the building over the next 4 years. Now
calculate the project’s NPV, IRR, MIRR, and payback. Do these
indicators suggest that the project should be accepted? Explain.
Answer: [Show S12-14 here.] We refer to the completed time line and
explain how each of the indicators is calculated. We base our
Chapter 12: Cash Flow Estimation and Risk Analysis
Integrated Case
339
MIRR: 0 1 2 3 4
D. If this project had been a replacement rather than an expansion
project, how would the analysis have changed? Think about the
changes that would have to occur in the cash flow table.
Answer: [Show S12-15 here.] In a replacement analysis, we must find
10%