Sensitivity and Sce View Review Formulas Data Page Layout Draw Insert Home Wrap Text Num A A 10 Arial $ Merge & Center A- Paste I U v f 146 J K G H F D C B A 11/26/18 Cash Flow Estimation and Risk Analysis 2 Chapter: 3 Problem: 11 18 4 5 Webmasters.com has developed a powerful new server that would be used for corporations Internet activites. It would cost 6 $10.4 million at Year 0 to buy the equipment necessary to manufacture the server. The project would require net working 7 capital at the beginning of each year in an amount equal to 9.75 % of the year's projected sales; for example, NWC, 8 9 75%Ralas.l 10 The Erm beleves it could sell 1,000 units per year. The servers would sell for $24,780 per unit, and Webmasters belleves that 11 variable cos ts would amount to $18,350 per unit Afer Year 1, the sales price and variable costs will increase at the inlation 12 rate of 3.35 % The company's nonvariable costs would be $1.12 million at Year 1 and also would increase at the 3.35% 13 Infation rate 14 15 The server project would have a life of 4 years. If the project is undertaken, it must be continued for the entire 4 years. The 16 equipment would be depreciated over a 5-year period, using MACRS rates. The estimated market value of the equipment at the 17 end of the project's 4-year life is $454,500 18 19 20 with a coefficient of variation of NPV between 0.8 and 1.2. Low-risk projects are evaluated with a WACC of 7.71 % , and high 21 risk projects at 11.76 %. Also , the projecfs returns are expected to be highly correlated with returns on the firm's other assets. 22 23 24 a. Develop a spreadsheet model, and use it to find the project's NPV, IRR, and payback 25 26 Input Data (in thousands of dollars) 27 Scenario name 28 Probability of scenario 29 Equipment cost 30 Net operating working capita/Sales 31 First year sales (in units) 32 Sales price per unit 33 Variable cost per unit (excl. depr.) 34 Nonvariable costs (excl. depr) 35 Inflation in prices and costs 36 Estimated salvage value at year 4 37 Depreciation years 38 Depreciation rates 39 Tax rate 40 WACC for average-risk projects Webmasters federal-plus-4tate tax rate is 22.9 %. Its cost of capital is 10.41% for average-risk projects, defined as projects Base Case Note: the items in red will be used in a ssnario analysis. 50 % $10,400,000 9.75 % 1,000 $24,780.00 $18,350.00 $1,200,000 3.35 %) $454,500 Key Results: NPV 0.0 % IRR 0.00 Payback Year 2 32.00% Year 3 19.20% Year 4 Year 1 11.52% 20.00% 22.9 % 10.41% 41 2 42 Intermediate Calculations 43 Units sold 44 Sales price per unit (excl. depr.) 45 Variable costs per unit (excl. depr.) 46 Nonvariable costs (excl. de pr.) 47 Sales revenue 48 Required level of net operating working capital 49 Basis for de preciation 50 Annual equipment depr. rate 0 32.00% 11.52%) 19.20% 20.00% Build a Model Ready Itl Sensitivity and Scenario A Review View Formulas Data Draw Page Layout Insert Home wrap Text Number A A 10 Arial $ 9% Marge & Center v A- IU Paste X f 46 J H G C A 4rsesrevenue 48 Required level of net operating working capital 49 Basis for depreciation 50 Annual equipment depr. rate 51 Annual depreciation expense 52 Ending Bk Val: Cost-Accum Deprn 53 Salvage value 54 Proft (or loss) on salvage 55 Tax on profit (or loss) 56 Net cash fow due to salvage 57 58 Cash Flow Forecast 59 Sales revenue 60 Variable costs 61 Nonvariable operating costs 62 Depreciation (equipmen) 63 Oper, income before taxes (EBIT) 64 Taxes on operating income (40%) 11.52% 19.20% 32.00% 20.00% Years 2 1 65 Net operating proft after taxes ee Add back depreciation 67 Equipment purchases 68 Cash fow due to change in NOWC es Net cash fiow due to salvage 70 Net Cash Flow (Time line of cash lows) 71 72 Key Results: Appraisal of the Proposed Project 73 74 Net Present Value (at 10% ) 75 IRR 76 MIRR 77 Payback 78 Discounted Payback 79 Data for Payback Years Years 4 1 Net cash fiow Cumalative CF 82 Part of year required for payback 830 840 66 Data for Discounted Payback Years Years 3 1 2 0 87 $0 Net cash flow so Discounded cash low 89 Cumulative CF Part of year required for discounted payback 92 93 04 b Now conduct a aensitvity aalysis to determine the sensitivity of NPV to chenges in the sales price, variable costs per 95 unit, and number of units sold Set these variables values at 10% and 20% aboave and below their base-case values. include 96 a graph in your analysis 97 Build a Model + Ready MacBook Sensitivity and Sce Formulas Data Review View Draw Page Layout Home Insert A A Wrap Text Num Arial 10 Paste A $ Merge & Center I Uv x f 46 C D G H A B J K 93 94 b. Now conduct a sensitvity analysis to de termine the sensitivity of NPV to changes in the sales price, variable costs per 95 unit, and number of units sold. Set these variables' values at 10 % and 20% above and below their base-case values. Include 96 a graph in your analysis 97 98 % Deviation 1st YEAR UNIT SALES Base 1,000 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 1st Year Unit Sales in Cell B100 should be the numbear 1,000 and NOT have the formula D31 in that cell. This is because youll use D31 as the column input cell in the data table and if Excel tries to iteratively replace Cell D31 with the formula 031 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! from NPV 99 100 Base Case 101 $0 20 % 102 -10 % 103 104 105 106 107 108 109 % Deviation 110 111 112 113 114. 115 116 117 118 0% 10 % 20% % Deviation from SALES PRICE Bast VARIABLE COST from Base Case NPV Base NPV $24.00 Base Case $18.00 $0 20% 40% 0 % 20% -10% 0 % 10% 20% 10% 20% 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 Build a Model Ready MacBo A A Arial 10 Wrap Text v N Paste A I Merge & Center $ 146 A. B D E F G H K 141 Deviation NPV at Different Deviations from Base 142 from Sales Variable 143 Base Case Units Sold Price CostUnit 144 145 146 147 148 149 150 151 152 20% $0 $0 $0 $0 $0 10% $0 $0 $0 $0 $0 $0 0 % 10% $0 20% $0 $0 $0 Range S0 153 c. Now conduct a scenario 154 variables discussed in Partb being 20% better than its base-case value, will occur. Thare is a 25% probability of worstcase 155 conditions, with the variables 20% worse than base, and a 50 % probability of base-case conditions. (Hint: Use Scenario 156 ysis. Assume that there is a 25 % probability that best-case conditions, with each of the Manager. Go to the Data menu, choose What-if-Analyis, the choose Scenario Manager. After you create the Scenario's, you can 157 158 pick a scenario and type in the resuiting NPV (but be sure to return the Scenario to the base-case afterward). Or you can create a Scenario Summary and use a cell reference to the Scenario Summary worksheet to show the NPV for each scenario.) 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 Variable Costs per Unit Sales Price Scenario Probability Unit Sales per Unit NPV Best Case Base Case Worst Case 25% 50 % 25% 1,200 1,000 800 $28.80 $24.00 $19.20 $14.40 $18.00 $17.28 Expected NPV Standard Deviation Std Dev / Expected NPV Coeficient of Variation 174 d if the project appears to be more or less riaky than an average project, ind its risk-adjusted NPV, IRR, and payback. 175 176 CV range of firm's average-risk project 177 178 179 180 181 Risk-adjusted WACC 182 183 0.8 to 1.2 Low-risk WACC WACC Highrisk WACC 8% 10% 13% Risk adjusted NPV IRR Payback 184 185 186 187 188 On the basis of information in the problem, would you recommend that the project be accepted? Build a Model Ready MacBook Sensitivity an View Review Formulas Data Page Layout Draw Insert Home Wrap Text X A A 10 Arial Merge & Center A. I U - Paste B 146 H H G E D C A 160 161 Varlable Sales Price Costs per 162 NPV Unit Sales Unit per Unit Probability Scenario 163 164 165 166 167 168 169 170 171 1,200 1,000 800 $28.80 $14.40 25% Best Case $24.00 $19.20 $18.00 50% Base Case $17.28 25% Worst Case Expected NPV Standard Deviation Coefficient of Variation Std Dev / Expected NPV 172 173 174 d If the project appears to be more or less risky than an average project, find its riskadjusted NPV, IRR, and payback 175 176 CV range of firm's average-risk project 177 178 179 180 181 Risk-adjusted WACC 182 1.2 0.8 to 8 % 10% Low-risk WACC WACC 13% High-risk WACC Risk adjusted NPV 183 IRR Payback 184 185 186 . On the basis of information in the problem, would you recommend that the project be accepted? 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 Sensitivity and Sce View Review Formulas Data Page Layout Draw Insert Home Wrap Text Num A A 10 Arial $ Merge & Center A- Paste I U v f 146 J K G H F D C B A 11/26/18 Cash Flow Estimation and Risk Analysis 2 Chapter: 3 Problem: 11 18 4 5 Webmasters.com has developed a powerful new server that would be used for corporations Internet activites. It would cost 6 $10.4 million at Year 0 to buy the equipment necessary to manufacture the server. The project would require net working 7 capital at the beginning of each year in an amount equal to 9.75 % of the year's projected sales; for example, NWC, 8 9 75%Ralas.l 10 The Erm beleves it could sell 1,000 units per year. The servers would sell for $24,780 per unit, and Webmasters belleves that 11 variable cos ts would amount to $18,350 per unit Afer Year 1, the sales price and variable costs will increase at the inlation 12 rate of 3.35 % The company's nonvariable costs would be $1.12 million at Year 1 and also would increase at the 3.35% 13 Infation rate 14 15 The server project would have a life of 4 years. If the project is undertaken, it must be continued for the entire 4 years. The 16 equipment would be depreciated over a 5-year period, using MACRS rates. The estimated market value of the equipment at the 17 end of the project's 4-year life is $454,500 18 19 20 with a coefficient of variation of NPV between 0.8 and 1.2. Low-risk projects are evaluated with a WACC of 7.71 % , and high 21 risk projects at 11.76 %. Also , the projecfs returns are expected to be highly correlated with returns on the firm's other assets. 22 23 24 a. Develop a spreadsheet model, and use it to find the project's NPV, IRR, and payback 25 26 Input Data (in thousands of dollars) 27 Scenario name 28 Probability of scenario 29 Equipment cost 30 Net operating working capita/Sales 31 First year sales (in units) 32 Sales price per unit 33 Variable cost per unit (excl. depr.) 34 Nonvariable costs (excl. depr) 35 Inflation in prices and costs 36 Estimated salvage value at year 4 37 Depreciation years 38 Depreciation rates 39 Tax rate 40 WACC for average-risk projects Webmasters federal-plus-4tate tax rate is 22.9 %. Its cost of capital is 10.41% for average-risk projects, defined as projects Base Case Note: the items in red will be used in a ssnario analysis. 50 % $10,400,000 9.75 % 1,000 $24,780.00 $18,350.00 $1,200,000 3.35 %) $454,500 Key Results: NPV 0.0 % IRR 0.00 Payback Year 2 32.00% Year 3 19.20% Year 4 Year 1 11.52% 20.00% 22.9 % 10.41% 41 2 42 Intermediate Calculations 43 Units sold 44 Sales price per unit (excl. depr.) 45 Variable costs per unit (excl. depr.) 46 Nonvariable costs (excl. de pr.) 47 Sales revenue 48 Required level of net operating working capital 49 Basis for de preciation 50 Annual equipment depr. rate 0 32.00% 11.52%) 19.20% 20.00% Build a Model Ready Itl Sensitivity and Scenario A Review View Formulas Data Draw Page Layout Insert Home wrap Text Number A A 10 Arial $ 9% Marge & Center v A- IU Paste X f 46 J H G C A 4rsesrevenue 48 Required level of net operating working capital 49 Basis for depreciation 50 Annual equipment depr. rate 51 Annual depreciation expense 52 Ending Bk Val: Cost-Accum Deprn 53 Salvage value 54 Proft (or loss) on salvage 55 Tax on profit (or loss) 56 Net cash fow due to salvage 57 58 Cash Flow Forecast 59 Sales revenue 60 Variable costs 61 Nonvariable operating costs 62 Depreciation (equipmen) 63 Oper, income before taxes (EBIT) 64 Taxes on operating income (40%) 11.52% 19.20% 32.00% 20.00% Years 2 1 65 Net operating proft after taxes ee Add back depreciation 67 Equipment purchases 68 Cash fow due to change in NOWC es Net cash fiow due to salvage 70 Net Cash Flow (Time line of cash lows) 71 72 Key Results: Appraisal of the Proposed Project 73 74 Net Present Value (at 10% ) 75 IRR 76 MIRR 77 Payback 78 Discounted Payback 79 Data for Payback Years Years 4 1 Net cash fiow Cumalative CF 82 Part of year required for payback 830 840 66 Data for Discounted Payback Years Years 3 1 2 0 87 $0 Net cash flow so Discounded cash low 89 Cumulative CF Part of year required for discounted payback 92 93 04 b Now conduct a aensitvity aalysis to determine the sensitivity of NPV to chenges in the sales price, variable costs per 95 unit, and number of units sold Set these variables values at 10% and 20% aboave and below their base-case values. include 96 a graph in your analysis 97 Build a Model + Ready MacBook Sensitivity and Sce Formulas Data Review View Draw Page Layout Home Insert A A Wrap Text Num Arial 10 Paste A $ Merge & Center I Uv x f 46 C D G H A B J K 93 94 b. Now conduct a sensitvity analysis to de termine the sensitivity of NPV to changes in the sales price, variable costs per 95 unit, and number of units sold. Set these variables' values at 10 % and 20% above and below their base-case values. Include 96 a graph in your analysis 97 98 % Deviation 1st YEAR UNIT SALES Base 1,000 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 1st Year Unit Sales in Cell B100 should be the numbear 1,000 and NOT have the formula D31 in that cell. This is because youll use D31 as the column input cell in the data table and if Excel tries to iteratively replace Cell D31 with the formula 031 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! from NPV 99 100 Base Case 101 $0 20 % 102 -10 % 103 104 105 106 107 108 109 % Deviation 110 111 112 113 114. 115 116 117 118 0% 10 % 20% % Deviation from SALES PRICE Bast VARIABLE COST from Base Case NPV Base NPV $24.00 Base Case $18.00 $0 20% 40% 0 % 20% -10% 0 % 10% 20% 10% 20% 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 Build a Model Ready MacBo A A Arial 10 Wrap Text v N Paste A I Merge & Center $ 146 A. B D E F G H K 141 Deviation NPV at Different Deviations from Base 142 from Sales Variable 143 Base Case Units Sold Price CostUnit 144 145 146 147 148 149 150 151 152 20% $0 $0 $0 $0 $0 10% $0 $0 $0 $0 $0 $0 0 % 10% $0 20% $0 $0 $0 Range S0 153 c. Now conduct a scenario 154 variables discussed in Partb being 20% better than its base-case value, will occur. Thare is a 25% probability of worstcase 155 conditions, with the variables 20% worse than base, and a 50 % probability of base-case conditions. (Hint: Use Scenario 156 ysis. Assume that there is a 25 % probability that best-case conditions, with each of the Manager. Go to the Data menu, choose What-if-Analyis, the choose Scenario Manager. After you create the Scenario's, you can 157 158 pick a scenario and type in the resuiting NPV (but be sure to return the Scenario to the base-case afterward). Or you can create a Scenario Summary and use a cell reference to the Scenario Summary worksheet to show the NPV for each scenario.) 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 Variable Costs per Unit Sales Price Scenario Probability Unit Sales per Unit NPV Best Case Base Case Worst Case 25% 50 % 25% 1,200 1,000 800 $28.80 $24.00 $19.20 $14.40 $18.00 $17.28 Expected NPV Standard Deviation Std Dev / Expected NPV Coeficient of Variation 174 d if the project appears to be more or less riaky than an average project, ind its risk-adjusted NPV, IRR, and payback. 175 176 CV range of firm's average-risk project 177 178 179 180 181 Risk-adjusted WACC 182 183 0.8 to 1.2 Low-risk WACC WACC Highrisk WACC 8% 10% 13% Risk adjusted NPV IRR Payback 184 185 186 187 188 On the basis of information in the problem, would you recommend that the project be accepted? Build a Model Ready MacBook Sensitivity an View Review Formulas Data Page Layout Draw Insert Home Wrap Text X A A 10 Arial Merge & Center A. I U - Paste B 146 H H G E D C A 160 161 Varlable Sales Price Costs per 162 NPV Unit Sales Unit per Unit Probability Scenario 163 164 165 166 167 168 169 170 171 1,200 1,000 800 $28.80 $14.40 25% Best Case $24.00 $19.20 $18.00 50% Base Case $17.28 25% Worst Case Expected NPV Standard Deviation Coefficient of Variation Std Dev / Expected NPV 172 173 174 d If the project appears to be more or less risky than an average project, find its riskadjusted NPV, IRR, and payback 175 176 CV range of firm's average-risk project 177 178 179 180 181 Risk-adjusted WACC 182 1.2 0.8 to 8 % 10% Low-risk WACC WACC 13% High-risk WACC Risk adjusted NPV 183 IRR Payback 184 185 186 . On the basis of information in the problem, would you recommend that the project be accepted? 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208