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

Question

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

Accepted Answer

The Answer is in the image, click to view ...

Question

Sensitivity and Sce View Review Formulas Data Page Layout Draw Insert Home Wrap Text Num A A 10 Arial $ Merge & Center A- Paste

Step by Step Solution

Step: 1

Get Instant Access to Expert-Tailored Solutions

Step: 2

Step: 3

Ace Your Homework with AI

Recommended Textbook for

Investment Science

Students also viewed these Finance questions

Question

Question

Question

Question

Question

Question

Question

Question

Question

Question

Question

Question

Question

Question