Pinto.com has developed a powerful new server that would be used for corporations' Internet activities. It would cost $25 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 12% of the year's projected sales; for example, NWC₀= 12%(Sales₁). The servers would sell for $21,000 per unit, and Pinto believes that variable costs would amount to $15,000 per unit. After Year 1, the sales price and variable costs will increase at the inflation rate of 2.5%. The company's nonvariable costs would be $1.5 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 2,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 $1 million. Pinto.com's federal-plus-state tax rate is 20%. 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 an 8% project cost of capital and high-risk projects at 13%.

1. Develop a spreadsheet model, and use it to find the project's NPV, IRR, and payback.
2. 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.
3. 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.
4. If the project appears to be more or less risky than an average project, find its risk-adjusted NPV, IRR, and payback.
5. On the basis of information in the problem, would you recommend the project should be accepted?

Chapter 11 - Build a Model.xlsx Q Search Sheet Home Insert Page Layout Formulas Data Review View '+ Share E22 X V fix C D F G H J K L M N P Q R S T U V w X Y Z AA AB AC AD AE AF b. Now conduct a sensitivity analysis to determine the sensitivity of NPV to changes in the sales price, variable costs per 5 unit, and number of units sold. Set these variables' values at 10% and 20% above and below their base-case values. 76 78 7 % Deviation 1st YEAR UNIT SALES 79 from Base NPV Base Case 2,000 about data tables. The data in the column input should NOT be input using a cell reference to the column input cell. For example, 80 -20% the base case 1st Year Unit Sales in Cell B79 should be the number 298:288 10% 2,000 and NOT have the formula =D10 in that cell. This is because 0% you'll use D10 as the column input cell in the data table and if Excel tries to iteratively replace Cell D10 with the formula =D10 rather than 10% 20% a series of numbers, Excel will calculate the wrong answer. Unfortunately, Excel won't tell y tell you that there is a problem, so you'll just get the wrong values for the data table! % Deviation SALES PRICE % Deviation VARIABLE COST Base NPV from Base NPV $21.00 Base Case $15.00 -20% -20% 10% 10% 0% 0% 10% 10% 20% 20% 8 9 8 Deviation NPV at Different Deviations from Base from Sales Variable 99 100 Base Case Units Sold Price Cost/Unit 101 -20% SO 102 -10% 103 0% SO SO 104 18 8 SO 10% 20% 105 106 107 Range SU SU 108 109 10 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 113 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 15 create a Scenario S Scenario Summary and use a cell reference to the Scenario Summary worksheet to show the NPV for ea 116 117 Variable 118 119 Scenario Sales Price Costs per 120 Probability Unit Sales per Unit Unit NPV 122 Best Case 25% 2,400 $24.00 2,000 $20.00 $12.00 123 Base Case 50% 25% $16.00 $15.00 124 Worst Case 1,600 $18.00 125 126 Expected NPV = 127 Standard Deviation = 128 Coefficient of Variation = Std Dev / Expected NPV = 129 131 0 d. If the project appears to be more or less risky than an average project, find its risk-adjusted NPV, IRR, and payback. 2 CV range of firm's average-risk project: 0.8 Low-risk WACC = to 1.2 133 8% 135 WACC = 10% 136 High-risk WACC = 13% 138 7Risk-adjusted WACC = 139 Risk adjusted NPV = IRR 4 Build a Model + Ready 100%Chapter 11 - Build a Model.xlsx Q Search Sheet Home Insert Page Layout Formulas Data Review View "+ Share E22 $ X V fix B C D F G H I J K L M N P Q R S T U V w X Y z AA AB AC AD AE AF 143 2 e. On the basis of information in the problem, would you recommend that the project be accepted? 144 145 148 169 171 172 173 175 176 177 178 179 199 201 202 203 204 205 206 207 208 209 210 211 Build a Model + Ready 100%