Scenario analysis and risk-adjusted NPV

Extracted Content from Question Files:

Data Input
$10,000,000 $500,000
Equipment cost Market value of equipment
$3,000,000 40%
Net operating WC Tax rate
1,000 10%
Sales per year (in units) WACC
$24,000 Inflation: growth in sales price 0%
Sales price per unit
$17,500 Inflation: growth in variable costs per unit 0%
Variable cost per unit
$1,000,000 Inflation: growth in fixed costs 0%
Fixed costs
4 Growth in sales unit 0%
Project life (in years)

Depreciation schedule
Years Cumulative
1 2 3 4 Depreciation
Equipment depre rate 20% 32% 19% 12%
Equipment depre $2,000,000 $3,200,000 $1,900,000 $1,200,000 $8,300,000
Ending book value $8,000,000 $4,800,000 $2,900,000 $1,700,000

Net Salvage value at the end of year 4
Equipment
Estimated market value at the end of year 4 $500,000
Book value at the end of year 4 $1,700,000
Expected gain or loss ($1,200,000)
Tax paid or tax credit ($480,000)
Net cash flow from salvage $980,000

Projected net cash flows
Years
0 1 2 3 4
Investment outlays at time zero
Equipment ($10,000,000)
Increase in New Operating WC ($3,000,000)

Operating cash flows over the project's life
Units sold 1,000 1000 1000 1000
Sales price $24,000 24000 24000 24000

Sales revenue $24,000,000 $24,000,000 $24,000,000 $24,000,000
Variable costs $17,500,000 $17,500,000 $17,500,000 $17,500,000
Fixed operating costs $1,000,000 $1,000,000 $1,000,000 $1,000,000
Depreciation $2,000,000 $3,200,000 $1,900,000 $1,200,000
Operating income before taxes $3,500,000 $2,300,000 $3,600,000 $4,300,000
Taxes on operating income (40%) $1,400,000 $920,000 $1,440,000 $1,720,000
Net operating profit after taxes $2,100,000 $1,380,000 $2,160,000 $2,580,000
Add back depreciation $2,000,000 $3,200,000 $1,900,000 $1,200,000
Operating cash flow $4,100,000 $4,580,000 $4,060,000 $3,780,000

Terminal year cash flows
Return on net operating WC $3,000,000
Net salvage value $980,000
Total termination cash flows $3,980,000

($13,000,000) $4,100,000 $4,580,000 $4,060,000 $7,760,000
Net Cash Flow

$2,862,919.20
Net Present Value
18.85%
IRR
Sensitivity Analysis
% deviation from
base case Sales Price NPV Fixed Costs NPV
$2,862,919 $2,862,919
-20% $19,200 -$6,266,293 $800,000 $3,243,303
-10% $21,600 -$1,701,687 $900,000 $3,053,111
0% $24,000 $2,862,919 $1,000,000 $2,862,919
10% $26,400 $7,427,525 $1,100,000 $2,672,727
20% $28,800 $11,992,132 $1,200,000 $2,482,535

% deviation from
base case VC NPV
$2,862,919
-20% $14,000 $9,519,637
-10% $15,750 $6,191,278
0% $17,500 $2,862,919
10% $19,250 -$465,440
20% $21,000 -$3,793,798

Summarizing all the information
NPV at Different Deviations from Base
% Sales Fixed Variable
Deviation Price Costs Cost
-20% -$6,266,293 $3,243,303 $9,519,637
-10% -$1,701,687 $3,053,111 $6,191,278
0% $2,862,919 $2,862,919 $2,862,919 Base
10% $7,427,525 $2,672,727 -$465,440
20% $11,992,132 $2,482,535 -$3,793,798

Range 18,258,425 760,768 13,313,435

$15,000,000

$10,000,000

Sales price
$5,000,000
NPV

Fixed costs
$0 Variable costs

-$5,000,000

-$10,000,000
-20% -10% 0% 10% 20%

SPRING 06
PROBLEM SET #4

Read the problem and carefully follow the instructions given in the end.

Webmasters.com has developed a powerful new server that would be used for
corporation’s Internet activities. It would cost $10 million to purchase the equipment
necessary to manufacture the server, and $3 million of net operating working capital
would be required. The servers would sell for $24,000 per unit, and the Webmasters
believes that variables cost would amount to $17,500 per unit. The server also requires an
additional fixed cost of $1 million per year. The server project would have a life of 4
years. Conditions are expected to remain stable during each year of the operating life;
that is, unit sales, sales price, and costs would be unchanged. 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 in each of the next four years of operations.

The equipment would be depreciated over a 5-year period, using MACRS rates as
described in Exhibit 1. 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 percent. It
cost of capital is 10 percent for average-risk projects, defined as projects with a
coefficients of variation of NPV between 0.8 and 1.2. Low-risk projects are evaluated
with a WACC of 8 percent, and high-risk projects are evaluated at 13 percent.

a) Develop a spreadsheet model and use it to find the project’s NPV, IRR. At this stage,
compute NPV using a discount rate for average-risk projects (i.e. 10 percent).
Include a table which shows all calculations.

b) Now conduct a sensitivity analysis to determine the sensitivity of NPV to changes in
the sales price, variable costs per unit, and the number of units sold. Set these
variables values at 10 percent and 20 percent above and below their base-case values.
Include a table and graph in your analysis.

c) Now conduct a scenario analysis. Assume that there is a 25 percent probability that
“best-case” conditions with each of the variables discussed in part b being 20 percent
better than its base-case value, will occur. There is 25 percent probability of “worst-
case” conditions, with the variables 20 percent worse than base, and a 50 percent
probability of base-case conditions. Include a table in your analysis.

d) If the project appears to be more or less risky than an average project, find its risk-
adjusted NPV and IRR. Include a discussion on your choice of discount rate.
e) On the basis of the information in the problem, would you recommend that the
project be accepted? Including a discussion in support of your answer.

f) Discuss the significance of the following assumptions:
- “if the project is undertaken, it must be continued for the entire 4 years” and
- “the project’s returns are expected to be highly correlated with returns on the
firm’s other assets.”

If these assumptions were not true, how would it affect your analysis? Give a
qualitative answer.

You are required to write a report that addresses above questions. Your report must be
addressed to the CEO of Webmasters. Remember that CEOs are busy people. Your
report should be concise but should have complete information.

Important Instructions: Read these instructions carefully. I will not accept any
assignment which doesn’t follow these instructions.

Report MUST be neat and professionally written – have all tables/graphs properly
labeled, all columns of a table should be on one page (columns in a table should not run
across multiple pages), all pages should be nicely stapled, etc. It would help to go through
a tutorial on printing options (for links/sources, refer to notes on module 1). Some things
you might consider to increase visual appeal of the tables are: changing font size, shifting
to landscape/portrait page-setup, etc. 10% of the grade on this project is based on this
criterion.

Exhibit-1
MACRS Depreciation Schedule

Ownership 3-year 5-year 7-year 10-year
Year
1 33% 20% 14% 10%
2 45% 32% 25% 18%
3 15% 19% 17% 14%
4 7% 12% 13% 12%
5 11% 9% 9%
6 6% 9% 7%
7 9% 7%
8 4% 7%
9 7%
10 6%
11 3%

100% 100% 100% 100%