Ch 14-11 Build a Model Solution								3/6/2001
Chapter 14. Solution to Ch 14-11 Build a Model
Webmasters.com has developed a powerful new server that would be used for corporations’ Internet activities. It would cost $10 million to buy the equipment necessary to manufacture the server, and $3 million of net working capital would be required. The servers would sell for $24,000 per unit, and Webmasters believes that variable costs would amount to $17,500 per unit. The company’s fixed costs would also rise by $1 million per year. It would take one year to buy the required equipment and set up operations, and the server project would have a life of 4 years. Conditions are expected to remain stable during each year of the operating life, i.e., unit sales, the 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 equipment would be depreciated over a 5-year period, using MACRS rates as described in Appendix 12A. 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 for 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 =	$2,863
Part 1. Input Data (in thousands of dollars)							IRR =	18.8%
							MIRR =	15.6%
Equipment cost			$10,000
Net Operating WC			$3,000		Market value of equipment in 2005			$500
First year sales (in units)			1,000		Tax rate			40%
Sales price per unit			$24.00		WACC			10%
Variable cost per unit			$17.50
Fixed costs			$1,000
Part 2. Depreciation and Amortization Schedule				Years				Accum'd
Year			Initial Cost	1	2	3	4	Depr'n
Equipment Depr'n Rate				20.0%	32.0%	19.0%	12.0%
Equipment Depr'n, Dollars				$2,000	$3,200	$1,900	$1,200	$8,300
Ending Bk Val: Cost - Accum Dep'rn			10,000				$1,700
Part 3. Net Salvage Values, in 2005					Equipment
Estimated Market Value in 2005					$500
Book Value in 2005					1,700
Expected Gain or Loss					-1,200
Taxes paid or tax credit					-480
Net cash flow from salvage					$980
Part 4. Projected Net Cash Flows (Time line of annual cash flows)
		Years, 1-4 basis		0	1	2	3	4
		Years, actual year basis		2001	2002	2003	2004	2005
Investment Outlays at Time Zero:
Equipment				(10,000)
Increase in Net Operating WC				(3,000)
Operating Cash Flows over the Project's Life:
Units sold					1,000	1,000	1,000	1,000
Sales price					$24.00	$24.00	$24.00	$24.00
Sales revenue					$24,000	$24,000	$24,000	$24,000
Variable costs					17,500	17,500	17,500	17,500
Fixed operating costs					1,000	1,000	1,000	1,000
Depreciation (equipment)					2,000	3,200	1,900	1,200
Oper. income before taxes (EBIT)					3,500	2,300	3,600	4,300
Taxes on operating income (40%)					1,400	920	1,440	1,720
Net Operating Profit After Taxes (NOPAT)					2,100	1,380	2,160	2,580
Add back depreciation					2,000	3,200	1,900	1,200
Operating cash flow					$4,100	$4,580	$4,060	$3,780
Terminal Year Cash Flows:
Return of net operating working capital								$3,000
Net salvage value								980
Total termination cash flows								$3,980
Net Cash Flow (Time line of cash flows)				($13,000)	$4,100	$4,580	$4,060	$7,760
Part 5. Key Output: Appraisal of the Proposed Project
Net Present Value (at 10%)			$2,863
IRR			18.85%
MIRR			15.61%
Payback (See calculation below)			Applies MIN function to Row 73 to find first year when payback is positive. 0.00
Data for Payback Years				0	1	2	3	4
	Cumulative CF from Row 53			(13,000)	(8,900)	(4,320)	(260)	7,500
	IF Function to find payback				0.00	0.00	0.00	3.03
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.
Part 6. Evaluating Risk: Sensitivity Analysis
I. Sensitivity of NPV to Changes in Inputs. Here we use an Excel "Data Table" to find NPV
different unit sales, holding other thing constant.
% Deviation	1st YEAR UNIT SALES			% Deviation	WACC
from	Units	NPV		from		NPV
Base Case	Sold	NPV at 10%. Base Case $2,863		Base Case	WACC	2,863
-20%	800	$390		-20%	8.0%	$3,650
-10%	900	$1,627		-10%	9.0%	$3,249
0%	1,000	$2,863		0%	10.0%	$2,863
10%	1,100	$4,099		10%	11.0%	$2,491
20%	1,200	$5,335		20%	12.0%	$2,133
% Deviation	VARIABLE COSTS			% Deviation	SALES PRICE
from	Variable	NPV		from	Sales	NPV
Base Case	Costs	$2,863		Base Case	Price	$2,863
-20%	$14.00	$9,520		-20%	$19.20	-$6,266
-10%	$15.75	$6,191		-10%	$21.60	-$1,702
0%	$17.50	$2,863		0%	$24.00	$2,863
10%	$19.25	-$465		10%	$26.40	$7,428
20%	$21.00	-$3,794		20%	$28.80	$11,992
% Deviation	FIXED COSTS
from	Fixed	NPV
Base Case	Costs	$2,863
-20%	$800	$3,243
-10%	$900	$3,053
0%	$1,000	$2,863
10%	$1,100	$2,673
20%	$1,200	$2,483
	Deviation	NPV at Different Deviations from Base
	from	Sales	Variable		Fixed
	Base Case	Price	Cost/Unit	Units Sold	Cost	WACC
	-20%	($6,266)	$9,520	$390	$3,243	$3,650
	-10%	($1,702)	$6,191	$1,627	$3,053	$3,249
	0%	$2,863	$2,863	$2,863	$2,863	$2,863
	10%	$7,428	($465)	$4,099	$2,673	$2,491
	20%	$11,992	($3,794)	$5,335	$2,483	$2,133
	Range	18,258	13,313	5,726	761	1,516
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.
Part 7. Evaluating Risk: Scenario Analysis							Squared
							Deviation
			Sales	Unit	Variable		Times
Scenario		Probability	Price	Sales	Costs	NPV	Probability
Best Case		25%	$28.80	1,200	$14.00	$22,453	The deviation (NPV of the scenario minus expected NPV ) squared times the probability. 85264821		Manually changed the Part 1 inputs, then put the
Base Case		50%	$24.00	1,000	$17.50	$2,863	629768		NPV as calculated with the modified variables here.
Worst Case		25%	$19.20	800	$21.00	($12,238)	65798132		Took deviations from the expected values, squared
							151692721		them, and multiplied by the probabilities to get the
		Expected NPV = sum, prob times NPV				$3,985			numbers shown.
		Standard Deviation = Sq Root of column H sum				$9,268			Summed the squared deviations and took sq root
		Coefficient of Variation = Std Dev / Expected NPV				2.33			Std Dev divided by Expected NPV
a. Probability Graph
		Probability
		50%
		25%
	0
		2,863					NPV ($)
		Most Likely	Mean of distribution
The scenario analysis suggests that the project could be highly profitable, but also that it is quite risky. There is a
25% probability that the project would result in a loss of $12.2 million. There is also a 25% probability that it could
produce an NPV of $22.5 million. The standard deviation is high, at $9.2 million, and the coefficient of variation is a
high 2.33.
d. If the project appears to be more or less risky than an average project, find its risk-adjusted NPV, IRR, and payback.
With the high CV, we must re-evaluate the project using a higher WACC, 13%. That results in:
	Risk adjusted NPV =	$1,788
	IRR =	IRR does not change. 18.85%
	Payback =	Paypack does not change. $0.00
e. Based on the 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 quite low.
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.
We indicate in the problem that this project's returns will tend to be highly correlated with the firm's other projects'
returnst. 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.