P12-5 Scenario analysis (LG 2; Basic)
a. and b.
c. Both projects offer the same NPV in the most likely scenario. But the range of NPVs for the
snack food machines is 1.6 times larger than the range for soft drink machines. Moreover,
snack food machines have a much larger downside—in the pessimistic scenario the negative
NPV is 1.8 times larger than the negative NPV for soft drink machines.
d. The risk averse financial manager is, therefore, likely to opt for soft-drink machines.
P12-6 Scenario Analysis (LG 2; Intermediate)
a., b., c., and d.
To find NPVs for all gold prices, first calculate the present value of operating cash flows
associated with each price over five years at a discount rate of 11.2%. Then subtract the $67.8
project cost. Operating cash flows and project NPV are highlighted in bold font in the table
below. Internal rate of return (IRR) is the discount rate that makes project NPV equal zero for
a given discount rate, number of periods, and set of operating cash flows. IRR may be found in
Excel with the IRR command. Begin by arranging the cash outflow and inflows in adjacent
cells in a row or column with the outflow (project cost) expressed as a negative. If, for
example, the cash flows appear in column A, rows 1-6, proper syntax is: =IRR(A1:A6). The
IRR for each gold price is highlighted in bold font at the bottom of the table below.
e. The minimum acceptable price of gold is the price at which project NPV equals zero. This
price may be found in a few steps:
(i) Find the breakeven cash flows for the project in Excel with the PMT command and the
following syntax:
=PMT(discount rate,number of periods,-initial investment) = PMT(0.1120,5,-67800000)
= $18,437,051.
(ii) Next, work backwards from operating cash flow to annual revenue:
Operating Cash Flow $18,437,051
– Annual Depreciation –$13,560,000
= Net Profit After Taxes = $4,877,051
(1 – Tax Rate) (1 – 0.2104 = 0.7896]
=Net Profit Before Taxes $6,176,610
+ Depreciation + $13,560,000
+ Operating Expenses +$19,420,000
Annual Revenue (at which NPV = 0) = $39,156,610
(iii) Now, to find the gold price that makes NPV = 0, divide the annual revenue figure just
obtained by annual projected gold sales (174,000 expected sales over five years 5 years
= 34,800). So, the minimum acceptable gold price = $39,156,610 34,800 = $1,125.19
(rounded from $1,125.189932).