A 1 11problem 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46
B
C
D
E
F
G
H
I 10.02.2003
27.12.2007 09:25
Chapter 11. Solution to end-of-chapter spreadsheet problem problem Webmasters.com has developed a powerful new server 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 operating working capital would be required. The servers would sell for $24,000 per unit, and Webmasters Webmasters believes that variable costs would amount to $17,500 $17,500 per unit. The company’s fixed costs would also rise by $1 $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. years. Conditions are expected to remain stable during each year of the operating life, i.e., unit sales, the sales p rice, and costs would be unchanged. If the project is undertaken, undertaken, it must be continued for the entire 4 years. Also, the project’s returns are 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 4-year life is $500,000. Webmasters’ federal-plusstate tax rate is 40%. Its cost of capital is 10% for average 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 projects at 13%. a. Develop a spreadsheet model and use it to find the project’s NPV, IRR, and payback. (Hint: You might want to modify the model on file 11model rather than create an entirely new model.) Key Output: Part 1. Key Input Data Equipment cost Net Operating WC First year sales (in units) Sales price per unit Variable cost per unit Fixed costs
$10.000 $3.000 1.000 $24,00 $17,50 $1.000
Part 2. Depreciation and A Amortization mortization Schedule Year Initial Cost Equipment Deprn Rate Equipment Deprn, Dollars Ending Bk Val: Cost - Accum'd Deprn
NPV = IRR = MIRR =
Market value of equipment in 2005 Tax rate WACC
1 20,0% $2.000
Years 2 32,0% $3.200
10.000
Part 3. Net Salvage Values, in 2005 Estimated Market Value in 2005 Book Value in 2005 Expected Gain or Loss Taxes paid or tax credit Net cash flow from salvage
Equipment $500 1.700 -1.200 -480 $980
Page 1
$2.863 18,8% 15,6% $500 40% 10%
3
4
19,0% $1.900
12,0% $1.200 $1.700
Accum'd Deprn
$8.300
A B C D E 47 Part 4. Projected Net Cash Flows (Time line of annual cash flows) 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73
Years, 1-4 basis Years, actual year basis Investment Outlays at Time Zero: Equipment Increase in Net Operating WC
74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92
Part 5. Key Output: Appraisal of the Proposed Project
0 2001
G 1 2002
H 2 2003
I 3 2004
4 2005
-10.000 -3.000
Operating Cash Flows over the Project's Life: Units sold Sales price Sales revenue Variable costs Fixed operating costs Depreciation (equipment) Oper. income before taxes (EBIT) Taxes on operating income (40%) Net Operating Profit After Taxes (NOPAT) Add back depreciation Operating cash flow Terminal Year Cash Flows: Return of net operating working capital Net salvage value Total termination cash flows
1.000 $24,00
1.000 $24,00
1.000 $24,00
1.000 $24,00
$24.000 17.500 1.000 2.000 3.500 1.400 2.100 2.000 $4.100
$24.000 17.500 1.000 3.200 2.300 920 1.380 3.200 $4.580
$24.000 17.500 1.000 1.900 3.600 1.440 2.160 1.900 $4.060
$24.000 17.500 1.000 1.200 4.300 1.720 2.580 1.200 $3.780 $3.000 980 $3.980
Net Cash Flow (Time line of cash fl ows)
($13.000)
Net Present Value (at 12%) IRR MIRR
$2.863 18,85% 15,61%
Payback. See calculation below)
3,03
Data for Payback
F
Years Cumulative CF from Row 53 IF Function to find payback
$4.100
$4.580
$4.060
$7.760
Applies MIN function to Row 64 to find first year when payback is positive. 0 -13.000
1 -8.900 FALSE
2 -4.320 FALSE
3 -260 FALSE
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. Sensitivity of NPV to Changes in Inputs. Here we use an Excel "Data Table" to find NPV different unit sales, holding other thing constant.
Page 2
4 7.500 3,03
A B C D 93 % Deviation 1st YEAR UNIT SALES from Units NPV 94 Sold $2.863 95 Base Case 96 -20% 800 $390 -10% 900 $1.627 97 0% 1.000 $2.863 Base Case 98 10% 1.100 $4.099 99 100 20% 1.200 $5.335 101 VARIABLE COSTS 102 % Deviation from Variable NPV 103 Costs $2.863 104 Base Case 105 -20% $14,00 $9.520 -10% $15,75 $6.191 106 0% $17,50 $2.863 Base Case 107 10% $19,25 -$465 108 109 20% $21,00 -$3.794 110 FIXED COSTS 111 % Deviation from Fixed NPV 112 Costs $2.863 113 Base Case 114 -20% $800 $3.243 -10% $900 $3.053 115 0% $1.000 $2.863 Base Case 116 10% $1.100 $2.673 117 118 20% $1.200 $2.483 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141
E % Deviation from Base Case -20% -10% 0% 10% 20% % Deviation from Base Case -20% -10% 0% 10% 20%
F
G
H
I
WACC WACC 8,0% 9,0% 10,0% 11,0% 12,0%
NPV 2.863 $3.650 $3.249 $2.863 $2.491 $2.133
SALES PRICE Sales NPV Price $2.863 $19,20 -$6.266 $21,60 -$1.702 $24,00 $2.863 $26,40 $7.428 $28,80 $11.992
Sensitivity Analysis $11.000 $9.000 $7.000 $5.000 V P N
Sales price
$3.000
Variable cost
$1.000
Units sold
($1.000) ($3.000) ($5.000) ($7.000) -20%
-10%
0%
Page 3
10%
20%
A
B Deviation from Base Case -20% -10% 0% 10% 20%
C
D E F NPV at Different Deviations from Base Variable Fixed Cost/Unit Units Sold Cost $9.520 $390 $3.243 $6.191 $1.627 $3.053 $2.863 $2.863 $2.863 ($465) $4.099 $2.673 ($3.794) $5.335 $2.483
G
H
142 Sales 143 Price NPV 144 145 ($6.266) $3.650 ($1.702) $3.249 146 $2.863 $2.863 147 $7.428 $2.491 148 149 $11.992 $2.133 150 Range 18.258 13.313 5.726 761 1.516 151 152 153 c. Now conduct a scenario analysis. Assume that there is a 25% probability that “best case” conditions, with each of 154 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. 155 156 Squared 157 158 Sales Unit Variable 159 Scenario Probability Price Sales Costs 160 161 162 Best Case 25% $28,80 1.200 $14,00 163 Base Case 50% $24,00 1.000 $17,50 Worst Case 25% $19,20 800 $21,00 164 165 166 Expected NPV = sum, prob times NPV Standard Deviation = Sq Root of column H sum 167 Coefficient of Variation = Std Dev / Expected NPV 168 169 a. Probability Graph Probability 170 171 50% 172 173 174 175 25% 176 177 -12.238 0 4.442 178 2.863 179 180 Most Likely Mean of distribution 181 182 b. Continuous Approximation 183 184 Probability Density 185 186 187 188 189 -12.238 0 4.442 190 2.863 191
Page 4
NPV $24.279 $2.863 ($12.238)
Deviation Times Probability 98379523 1246290 69553178 169178990
$4.442 $13.007 2,93
24.279 NPV ($)
24.279 NPV ($)
I
A 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212
B
C
D
E
F
G
H
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 i n 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%. NPV IRR payback
$1.788 18,85% 3,03
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. Also notice, that parameters like IRR and payback are unaffected by a changing discount rate.
e. On the basis of information in the problem, would you recommend that the project be accepted?
Page 5
I
