4/16/2010
Chapter 11. Ch 11-18 Build a Model
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, NWC 0 = 10%(Sales1). The servers
would sell for $24,000 per unit, and Webmasters believes that variable costs would amount to $17,500 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 would increase with inflation.
The server project would have a life of 4 years. If the project is undertaken, it must be continued for the entire 4 years. Also,
the project's returns are expected to be highly correlated with returns on the firm's other assets. The firm believes it could sell
1,000 units per year.
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. Webmasters’ federal-plus-state tax rate is 40%. 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%.
a. Develop a spreadsheet model, and use it to find the project’s NPV, IRR, and payback.
Key Output: NPV =
IRR =
MIRR =
Part 1. Input Data (in thousands of dollars)
Equipment cost
Net WC/Sales
First year sales (in units)
Sales price per unit
Variable cost per unit
Nonvariable costs
$10,000
10%
1,000
$24.00
$17.50
$1,000
Part 2. Depreciation and Amortization Schedule
Year
Initial Cost
Equipment Depr'n Rate
Equipment Depr'n, Dollars
Ending Bk Val: Cost – Accum Dep'rn
Part 3. Net Salvage Values, in Year 4
Estimated Market Value in Year 4
Book Value in Year 4
Expected Gain or Loss
Taxes paid or tax credit
Net cash flow from salvage
Market value of equipment at Year 4
Tax rate
WACC
Inflation
Years
2
1
20.0%
32.0%
Equipment
Page 1
3
19.0%
$500
40%
10%
3.0%
4
12.0%
Accum'd
Depr'n
Part 4. Projected Net Cash Flows (Time line of Annual Cash Flows)
Years
0
Investment Outlays at Time Zero:
Equipment
1
2
3
4
2
3
4
Operating Cash Flows over the Project's Life:
Units sold
Sales price
Variable costs
Sales revenue
Variable costs
Nonvariable operating costs
Depreciation (equipment)
Oper. income before taxes (EBIT)
Taxes on operating income (40%)
After-tax operating income
Add back depreciation
Operating cash flow
Terminal Year Cash Flows:
Required level of net working capital
Required investment in NWC
Terminal Year Cash Flows:
Net salvage value
Net Cash Flow (Time line of cash flows)
Part 5. Key Output: Appraisal of the Proposed Project
Net Present Value (at 10%)
IRR
MIRR
Payback (See calculation below)
Data for Payback Years
Net cash flow
Cumulative CF
Part of year required for payback
3
0
1
0
b. Now conduct a sensitivity analysis to determine the sensitivity of NPV to changes in the sales price, variable costs
per unit, and number of units sold. Set these variables’ values at 10% and 20% above and below their base-case
values. Include a graph in your analysis.
Page 2
Part 6. Evaluating Risk: Sensitivity Analysis
I. Sensitivity of NPV to Changes in Inputs. Here we use Excel "Data Tables" to find NPVs at different unit sales,
WACC, variable costs, sales price and nonvariable costs--changing one variable at a time, holding other things
constant.
% Deviation 1st YEAR UNIT SALES
from
Units
NPV
Base Case
Sold
$0
-20%
0
-10%
0
0%
0
10%
0
20%
0
% Deviation
from
Base Case
-20%
-10%
0%
10%
20%
% Deviation
from
Base Case
-20%
-10%
0%
10%
20%
% Deviation
from
Base Case
-20%
-10%
0%
10%
20%
VARIABLE COST
Variable
NPV
Costs
$0
0
0
0
0
0
% Deviation NONVARIABLE COST
from
Fixed
NPV
Base Case
Costs
$0
-20%
0
-10%
0
0%
0
10%
0
20%
0
WACC
WACC
NPV
$0
0
0
0
0
0
SALES PRICE
Sales
NPV
Price
$0
0
0
0
0
0
Note about data tables. The data in the column input should NOT be input
using a cell reference to the column input cell. For example, the base case
number of units sold in Cell B105 should be the number 1000; you should
NOT have the formula =D29 in that cell. This is because you'll use D29 as the
column input cell in the data table and if Excel tries to iteratively replace Cell
D29 with the formula =D29 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!
Page 3
Deviation
from
Base Case
-20%
-10%
0%
10%
20%
Sales
Price
$0
0
0
0
0
NPV at Different Deviations from Base
Variable
Nonvariable
Cost/Unit
Units Sold
Cost
$0
$0
$0
0
0
0
0
0
0
0
0
0
0
0
0
WACC
$0
0
0
0
0
Range
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.
Page 4
Part 7. Evaluating Risk: Scenario Analysis
Scenario
Best Case
Base Case
Worst Case
Probability
Sales
Price
Unit
Sales
Variable
Costs
25%
50%
25%
$28.80
$24.00
$19.20
1,200
1,000
800
$14.00
$17.50
$21.00
NPV
Squared
Deviation
Times
Probability
Expected NPV = sum, prob times NPV
Standard Deviation = Sq Root of column H sum
Coefficient of Variation = Std Dev / Expected NPV
d. If the project appears to be more or less risky than an average project, find its risk-adjusted NPV, IRR, and payback.
CV range of firm's average-risk project:
Low-risk WACC =
8%
WACC =
10%
High-risk WACC =
13%
0.8
to
1.2
Risk-adjusted WACC =
Risk adjusted NPV =
IRR =
Payback =
e. On the basis of information in the problem, would you recommend that the project be accepted?
Page 5
Page 6
(11–11)
Sceanrio Analysis
Shao Industries is considering a proposed project for its capital budget. The company
estimates the project’s NPV is $12 million. This estimate assumes that the economy
and market conditions will be average over the next few years. The company’s CFO,
however, forecasts there is only a 50% chance that the economy will be average. Recognizing
this uncertainty, she has also performed the following scenario analysis:
What is the project’s expected NPV, its standard deviation, and its coefficient of
variation?
11-12 New Project Analysis
Madison Manufacturing is considering a new machine that costs $250,000 and would
reduce pre-tax manufacturing costs by $90,000 annually. Madison would use the
3-year MACRS method to depreciate the machine, and management thinks the machine
would have a value of $23,000 at the end of its 5-year operating life. The
applicable depreciation rates are 33%, 45%, 15%, and 7%, as discussed in Appendix
11A. Working capital would increase by $25,000 initially, but it would be recovered
at the end of the project’s 5-year life. Madison’s marginal tax rate is 40%, and a 10%
WACC is appropriate for the project.
a. Calculate the project’s NPV, IRR, MIRR, and payback.
b. Assume management is unsure about the $90,000 cost savings—this figure could
deviate by as much as plus or minus 20%. What would the NPV be under each
of these extremes?
c. Suppose the CFO wants you to do a scenario analysis with different values for the
cost savings, the machine’s salvage value, and the working capital (WC) requirement.
She asks you to use the following probabilities and values in the scenario analysis:
Calculate the project’s expected NPV, its standard deviation, and its coefficient
of variation. Would you recommend that the project be accepted?
11.13 Replacement Analysis
The Everly Equipment Company purchased a machine 5 years ago at a cost of
$90,000. The machine had an expected life of 10 years at the time of purchase, and
it is being depreciated by the straight-line method by $9,000 per year. If the machine
is not replaced, it can be sold for $10,000 at the end of its useful life.
A new machine can be purchased for $150,000, including installation costs. During
its 5-year life, it will reduce cash operating expenses by $50,000 per year. Sales
are not expected to change. At the end of its useful life, the machine is estimated to
be worthless. MACRS depreciation will be used, and the machine will be depreciated
over its 3-year class life rather than its 5-year economic life, so the applicable depreciation
rates are 33%, 45%, 15%, and 7%.
The old machine can be sold today for $55,000. The firm’s tax rate is 35%, and
the appropriate WACC is 16%.
a. If the new machine is purchased, what is the amount of the initial cash flow at
Year 0?
b. What are the incremental net cash flows that will occur at the end of Years 1
through 5?
c. What is the NPV of this project? Should Everly replace the old machine?
11.14 Replacement Analysis
The Balboa Bottling Company is contemplating the replacement of one of its bottling
machines with a newer and more efficient one. The old machine has a book
value of $600,000 and a remaining useful life of 5 years. The firm does not expect
to realize any return from scrapping the old machine in 5 years, but it can sell it
now to another firm in the industry for $265,000. The old machine is being depreciated
by $120,000 per year, using the straight-line method.
The new machine has a purchase price of $1,175,000, an estimated useful life and
MACRS class life of 5 years, and an estimated salvage value of $145,000. The applicable
depreciation rates are 20%, 32%, 19%, 12%, 11%, and 6%. It is expected to
economize on electric power usage, labor, and repair costs, as well as to reduce the
number of defective bottles. In total, an annual savings of $255,000 will be realized if
the new machine is installed. The company’s marginal tax rate is 35%, and it has a
12% WACC
a. What is the initial net cash flow if the new machine is purchased and the old one
is replaced?
b. Calculate the annual depreciation allowances for both machines, and compute
the change in the annual depreciation expense if the replacement is made.
c. What are the incremental net cash flows in Years 1 through 5?
d. Should the firm purchase the new machine? Support your answer.
e. In general, how would each of the following factors affect the investment
decision, and how should each be treated?
(1) The expected life of the existing machine decreases.
(2) The WACC is not constant but is increasing as Balboa adds more projects
into its capital budget for the year.
11.15 Risky Cash Flows
The Bartram-Pulley Company (BPC) must decide between two mutually exclusive
investment projects. Each project costs $6,750 and has an expected life of 3 years.
Annual net cash flows from each project begin 1 year after the initial investment is
made and have the following probability distributions:
BPC has decided to evaluate the riskier project at a 12% rate and the less risky
project at a 10% rate.
a. What is the expected value of the annual net cash flows from each project? What
is the coefficient of variation (CV)? (Hint: σB = $5,798 and CVB = 0.76.)
b. What is the risk-adjusted NPV of each project?
c. If it were known that Project B is negatively correlated with other cash flows of
the firm whereas Project A is positively correlated, how would this affect the
decision? If Project B’s cash flows were negatively correlated with gross domestic
product (GDP), would that influence your assessment of its risk?
11.16 Simulation
Singleton Supplies Corporation (SSC) manufactures medical products for hospitals,
clinics, and nursing homes. SSC may introduce a new type of X-ray scanner designed to
identify certain types of cancers in their early stages. There are a number of uncertainties
about the proposed project, but the following data are believed to be reasonably accurate.
SSC uses a cost of capital of 15% to analyze average-risk projects, 12% for low-risk
projects, and 18% for high-risk projects. These risk adjustments primarily reflect the
uncertainty about each project’s NPV and IRR as measured by their coefficients of
variation. The firm is in the 40% federal-plus-state income tax bracket.
a. What is the expected IRR for the X-ray scanner project? Base your answer on
the expected values of the variables. Also, assume the after-tax “profits” figure
that you develop is equal to annual cash flows. All facilities are leased, so depreciation
may be disregarded. Can you determine the value of σIRR short of actual
simulation or a fairly complex statistical analysis?
b. Assume that SSC uses a 15% cost of capital for this project. What is the project’s
NPV? Could you estimate σNPV without either simulation or a complex statistical
analysis?
c. Show the process by which a computer would perform a simulation analysis for
this project. Use the random numbers 44, 17, 16, 58, 1; 79, 83, 86; and 19, 62, 6
to illustrate the process with the first computer run. Actually calculate the firstrun
NPV and IRR. Assume the cash flows for each year are independent of cash
flows for other years. Also, assume the computer operates as follows: (1) A developmental
cost and a project life are estimated for the first run using the first
two random numbers. (2) Next, sales volume, sales price, and cost per unit are
estimated using the next three random numbers and used to derive a cash flow
for the first year. (3) Then, the next three random numbers are used to estimate
sales volume, sales price, and cost per unit for the second year, hence the cash
flow for the second year. (4) Cash flows for other years are developed similarly,
on out to the first run’s estimated life. (5) With the developmental cost and the
cash flow stream established, NPV and IRR for the first run are derived and
stored in the computer’s memory. (6) The process is repeated to generate perhaps
500 other NPVs and IRRs. (7) Frequency distributions for NPV and IRR
are plotted by the computer, and the distributions’ means and standard deviations
are calculated.
11.17 Decision Tree
The Yoran Yacht Company (YYC), a prominent sailboat builder in Newport, may
design a new 30-foot sailboat based on the “winged” keels first introduced on the
12-meter yachts that raced for the America’s Cup.
The Yoran Yacht Company (YYC), a prominent sailboat builder in Newport, may
design a new 30-foot sailboat based on the “winged” keels first introduced on the
12-meter yachts that raced for the America’s Cup.
The next stage, if undertaken, would consist of making the molds and producing
two prototype boats. This would cost $500,000 at t = 1. If the boats test well, YYC
would go into production. If they do not, the molds and prototypes could be sold for
$100,000. The managers estimate the probability is 80% that the boats will pass testing
and that Stage 3 will be undertaken.
Stage 3 consists of converting an unused production line to produce the new design.
This would cost $1 million at t = 2. If the economy is strong at this point, the
net value of sales would be $3 million; if the economy is weak, the net value would be
$1.5 million. Both net values occur at t = 3, and each state of the economy has a
probability of 0.5. YYC’s corporate cost of capital is 12%.
a. Assume this project has average risk. Construct a decision tree and determine the
project’s expected NPV.
b. Find the project’s standard deviation of NPV and coefficient of variation of
NPV. If YYC’s average project had a CV of between 1.0 and 2.0, would this
project be of high, low, or average stand-alone risk?
Purchase answer to see full
attachment