Solution 11/26/2018
Chapter: 11 Cash Flow Estimation and Risk Analysis Note: when creating student version, be sure to delete Scenario Summary worksheet. Also delete the Best and Worse case scenarios from the Scenario Manager. Also delete this message in the student version.
Problem: 18
Input Data (in thousands of dollars)
Scenario name Base Case Note: the items in red will be used in a scenario analysis.
Probability of scenario 50%
Equipment cost $10,000
Net operating working capital/Sales 10% Key Results:
First year sales (in units) 1,000 NPV = $3,820
Sales price per unit $24.00 IRR = 22.2%
Variable cost per unit (excl. depr.) $18.00 Payback = 2.83
Nonvariable costs (excl. depr.) $1,000
Inflation in prices and costs 3.0%
Estimated salvage value at year 4 $500
Depreciation years Year 1 Year 2 Year 3 Year 4
Depreciation rates 20.00% 32.00% 19.20% 11.52%
Tax rate 25%
IRR = 22.19%
MIRR = 17.64%
Payback = 2.83
Discounted Payback = 3.19
Data for Payback Years
0 1 2 3 4
Net cash flow -$12,400 $4,178 $4,588 $4,382 $7,815
Cumulative CF -$12,400 -$8,222 -$3,634 $748 $8,564
Part of year required for payback 1.00 1.00 0.83 0.00
Data for Discounted Payback Years
0 1 2 3 4
-10% 900 $2,413
0% 1,000 $3,820
a. Develop a spreadsheet model, and use it to find the project’s NPV, IRR, and payback.
Webmasters.com has developed a powerful new server that would be used for corporations’ Internet activities. It
would cost $10 million at Year 0 to buy the equipment necessary to manufacture the server. The project would
require net working capital at the beginning of each year in an amount equal to 10% of the year's projected sales;
for example, NWC0 = 10%(Sales1).
Webmasters’ federal-plus-state tax rate is 25%. Its cost of capital is 10% for average-risk projects, defined as
projects with a coefficient of variation of NPV between 0.8 and 1.2. Low-risk projects are evaluated with a WACC of
8%, and high-risk projects at 13%. Also, the project's returns are expected to be highly correlated with returns on
the firm's other assets.
Years
cell in the data table and if Excel tries to iteratively replace Cell
D31 with the formula =D31 rather than a series of numbers,
Excel will calculate the wrong answer. Unfortunately, Excel
won't tell you that there is a problem, so you'll just get the
wrong values for the data table!
Years
The firm believes it could sell 1,000 units per year. The servers would sell for $24,000 per unit, and Webmasters
believes that variable costs would amount to $18,000 per unit. After Year 1, the sales price and variable costs will
increase at the inflation rate of 3%. The company’s nonvariable costs would be $1 million at Year 1 and also would
increase at the 3% inflation rate.
The server project would have a life of 4 years. If the project is undertaken, it must be continued for the entire 4
years. The equipment would be depreciated over a 5-year period, using MACRS rates. The estimated market value
of the equipment at the end of the project’s 4-year life is $500,000.
Page 1
-10% $21.60 -$2,047 -10% $16.20 $8,280
Note about data tables. The data in the column input should
NOT be input using a cell reference to the column input cell.
Sales price
12,000
14,000
16,000
Sensitivity Analysis
-10% $2,413 ######### $8,280
0% $3,820 $3,820 $3,820
10% $5,228 $9,688 -$640
20% $6,635 ######### #########
Range $5,630 $23,470 $17,840
IRR = 22.19%
Payback = 2.83
We indicate in the problem that this project's returns will tend to be highly correlated with the firm's other projects'
returns. Thus, its stand-alone risk (which is what we have been analyzing) also reflects its within-firm risk. If this
were not true, then we would need to make further risk adjustments.
e. On the basis of information in the problem, would you recommend that the project be accepted?
At this point, the project looks risky but acceptable. There is a good chance that it will produce a positive NPV, but
there is also a chance that the NPV could be quite low.
c. Now conduct a scenario analysis. Assume that there is a 25% probability that best-case conditions, with each of
the variables discussed in Part b being 20% better than its base-case value, will occur. There is a 25% probability
of worst-case conditions, with the variables 20% worse than base, and a 50% probability of base-case conditions.
(Hint: Use Scenario Manager. Go to the Data menu, choose What-If-Analyis, the choose Scenario Manager. After
you create the Scenario's, you can pick a scenario and type in the resulting NPV (but be sure to return the Scenario
to the base-case afterward). Or you can create a Scenario Summary and use a cell reference to the Scenario
Summary worksheet to show the NPV for each scenario.)
Sales Price
Variable
Costs per
The problem gave no information about the size of the project relative to the total corporation. If the company were
quite large, and this were but one of many projects, and if the projects were independent of one another, then it
should be accepted. However, if the firm were relatively small, and this project under bad conditions could
bankrupt the company, then the decision is not clear. If management is highly risk averse, they might turn it down.
However, well-diversified investors would probably prefer to see it accepted. So, to maximize the stock price, it
should be accepted.
Scenario Summary