ive completed some parts of this but help is needed on part 3,part 5 and letter C Part 7 and D

cell functions needed thank you

K D G Chapter 13. Solution to Ch 13-18 Build a Model 5 Webmasters.com has developed a powerful new server that would be used for corporations Internet 6 activities. It would cost $10 million at Year 0 to buy the equipment necessary to manufacture the server. The 7 project would require net working capital at the beginning of each year in an amount equal to 10% of the year's 8 projected sales, for example, NWC, -10%4Sales:). The servers would sell for $24,000 per unit, and 9 Webmasters believes that variable costs would amount to $17,500 per unit. After Year 1, the sales price and 10 variable costs will increase at the Inflation rate of 3%. The company's nonvariable costs would be $1 million 11 at Year 1 and would increase with inflation 12 13 The server project would have a life of 4 years. If the project is undertaken, it must be continued for the 14 entire 4 years. Also, the project's returns are expected to be Nighly correlated with returns on the firm's other 15 assets. The firm believes it could sell 1,000 units per year. 16 17 The equipment would be depreciated over a year period, using MACRS rates. The estimated market 18 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 19 variation of NPV between 0.8 and 1.2. Low-risk projects are evaluated with a WACC of 8%, and high risk 20 projects at 13% 21 22. Develop a spreadsheet model, and use it to find the project's NPV, IRR, and payback 23 24 Key Output NPV - 25 Part 1. Input Data fin thousands of dollars) IRR - MIRR. 27 Equipment cost $10,000 20 Net WC/Sales 10% Market value of equipment at Year 4 $500 29 First year sales in unita) 1,000 Tax rate 40% 30 Sales price per unit $24.00 WACC 10% 31 Variable cost per unit $17.50 Inflation 3.0% 32 Nonvariable costs $1,000 Accu'd Deprin 11.52% 51,152 1,728 58,272 1,728 3 4 55 Sale price 34 Part 2 Depreciation and Amortization Schedul Years 35 Yar Intel Cost 36 37 Equipment Deprin Rate 20.00% 32.00% 19. 20% 38 Equipment Deprir, Dollars $2,000 $3200 51.920 39 Ending Bk Val: Cost - Accum Depir 10.000 8.000 4.800 2.850 40 41 Part 3. Net Salvage Values, In Year 4 Equipment 42 Estimated Market value in Year 4 3500 43 Book Value In Year 4 1,728 44 Expected Gain or Loss -1.228 45 Taxes paid or tax credit 48 Net cash flow from salvage 47 48 Part 4. Projected Net Cash Flows (Timeline of Annual Cash Flows) 49 Years 1 2 50 Investment Oudlays at Time Zero 51 Equipment $10,000) 52 53 Operating Cash Flows over the Projecr's Life: 54 Units sold 1,000 1,000 $24.00 $24.72 56 Variable coste $18.03 $24.000 $24.720 59 Variable coste 17.500 18,025 60 Norwariable operating cost 1,000 1,030 61 Depreciation equipment 2.000 3.299 62 Oper. Income before taxes (EBIT) 63 Texes on operating income (40%) 1.400 ANG 64 After tax operating Income 65 Add back depreciation 2.000 66 Operating cash flow 2100 H670 67 68 Terminal Year Cash Flows: 69 Required level of niet working capital 52.400 $2,400 52.472 70 Required investment in NWG (52.400) 10 372) 71 72 Terminal Year Cash Flows: 73 Net salvage value 74 75 Net Cash Flow (Time line of cash flows) 52.00 10 14607 76 77 Part 5 Key Output Appraisal of the Proposed Project 78 70 Net Present Value (at 10%) 80 IRR 81 MIRR 1,000 $25.46 $18.57 1,000 $26.23 $18.12 317.50 56 Sales revenu 325,462 $20.225 18.566 1.061 18.12 1.000 1.1920 13.00 12 1.540 2009 12.168 312 1.132 Ho 2015 1.12 H.007 1.200 H.269 $2,546 (374) 52.623 (376) 401 HR quipment Depen Rate quipment Deprin, Dollars Inding Bk Val: Cost - Accum Degir 20.00% $2.000 8.000 32.00% $3,200 4.800 19.20% $1,920 2.880 11.52% $1,152 1.728 10,000 $8,272 1,728 Part 3. Net Salvage Values, in Year 4 Equipment Estimated Market Value In Year 4 $500 Book Value In Year 4 1,728 Expected Gain or Loss -1,228 Taxes paid or tax credit 491 Net cash flow from salvage $491 Part 4. Projected Net Cash Flows (Time line of Annual Cash Flows) Years 0 1 Investment Outlays at Time Zero: Equipment ($10,000) 2 3 4 Operating Cash Flows over the Project's Life: Units sold 1,000 1,000 1,000 1,000 Sales price $24.00 $24.72 $25.46 $26.23 Variable costs $17.50 $18.03 518.57 $19.12 Sales revenue $24,000 $24,720 $25,462 $26,225 Variable costs 17,500 18,025 18,566 19,123 Nonvariable operating costs 1,000 1,030 1,061 1,093 Depreciation equipment 2.000 3,200 1.920 1.152 2 Oper. Income before taxes (EBIT $3,500 $2,465 $3,915 $4,858 3 Taxes on operating Income (40%) 1400 1.566 1.943 4 Ahertax operating Income $2,100 $1,479 52320 $2,015 15 Add back depreciation 2.000 3.200 1920 1.152 56 Operating cash flow 54.100 $4,679 54.269 34,067 58 Terminal Year Cash Flows: 169 Required level of networking capital $2,400 $2,400 $2,472 $2,546 52.623 70 Required investment in NWC (52,400) $0 (572) (574) (576) 71 72 Terminal Year Cash Flows: 73 Net salvage value 491 74 75 Net Cash Flow (Time line of cash flows) 1512.400) $4,697 $415 $44822 76 77 Part 5. Key Output: Appraisal of the Proposed Project 78 79 Net Present Value (at 10%) 80 RR 81 MIRR 62 83 Payback (See calculation below) 85 Data for Payback Years Net cash flow 67 Cumulative CF 88 Part of year required for payback 80 SO 91. Now conduct a selvity analysis to determine the sensity of NPV to changes in the sales price, 92 variable sota 93 per unit and numero units sold set these variables' values at 10% and 20% above and below the base 95 Part 6. Evaluating Risk: Bensivity Analyse 97 Sensity of NPV to Change lineHere we use Excel Data Tables" te And NP Vesteren sales 98 WACC, variable costs, sales price and nonvariable conte-changing one variable at holding her 100% Deviator YEAR UNIT BALES WACA 101 NPM 103 104 25 Build a Model (7,000) Percentage Deviation from Base Deviation from Base Casel -20% -10% 0% 10% 20% NPV at Different Deviations from Base Sales Variable Nonvariable Price Cost/Unit Units Sold Cost $0 $0 $0 $0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 WACC $0 0 0 0 0 Range $0 $0 $0 $0 $0 Now conduct a scenario analysis. Assume that there is a 25% probability that best-case conditions, with ach of the variables discussed in Part b being 20% better than its base-case value, will occur. There is a 55% probability of worst-case conditions, with the variables 20% worse than base, and a 50% probability of ase-case conditions. art 7. Evaluating Risk: Scenario Analysis Squared Deviation Times Probability Scenario Sales Price Unit Sales Probability Variable Costs NPV Best Case Base Case Worst Case 25% 50% 25% $28.80 $24.00 $19.20 1,200 1,000 800 $14.00 $17.50 $21.00 Expected NPV = sum, prob times NPV Standard Deviation = Sq Root of column H sum Coefficient of Variation - Std Dev / Expected NPV to d. If the project appears to be more or less risky than an average project, find its risk-adjusted NPV, IRR, and 9 payback. 50 31 CV range of firm's average-risk project 0.8 1.2 B2 Low-risk WACC 8% 83 WACC = 10% B4 High-risk WACC - 13% 85 86 Risk-adjusted WACC = 187 Risk adjusted NPV = 188 IRR - 189 Payback 190 191. On the basis of information in the problem, would you recommend that the project be accepted? 192 193 194 195 196 197 198 199 200 201 202 203 204 K D G Chapter 13. Solution to Ch 13-18 Build a Model 5 Webmasters.com has developed a powerful new server that would be used for corporations Internet 6 activities. It would cost $10 million at Year 0 to buy the equipment necessary to manufacture the server. The 7 project would require net working capital at the beginning of each year in an amount equal to 10% of the year's 8 projected sales, for example, NWC, -10%4Sales:). The servers would sell for $24,000 per unit, and 9 Webmasters believes that variable costs would amount to $17,500 per unit. After Year 1, the sales price and 10 variable costs will increase at the Inflation rate of 3%. The company's nonvariable costs would be $1 million 11 at Year 1 and would increase with inflation 12 13 The server project would have a life of 4 years. If the project is undertaken, it must be continued for the 14 entire 4 years. Also, the project's returns are expected to be Nighly correlated with returns on the firm's other 15 assets. The firm believes it could sell 1,000 units per year. 16 17 The equipment would be depreciated over a year period, using MACRS rates. The estimated market 18 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 19 variation of NPV between 0.8 and 1.2. Low-risk projects are evaluated with a WACC of 8%, and high risk 20 projects at 13% 21 22. Develop a spreadsheet model, and use it to find the project's NPV, IRR, and payback 23 24 Key Output NPV - 25 Part 1. Input Data fin thousands of dollars) IRR - MIRR. 27 Equipment cost $10,000 20 Net WC/Sales 10% Market value of equipment at Year 4 $500 29 First year sales in unita) 1,000 Tax rate 40% 30 Sales price per unit $24.00 WACC 10% 31 Variable cost per unit $17.50 Inflation 3.0% 32 Nonvariable costs $1,000 Accu'd Deprin 11.52% 51,152 1,728 58,272 1,728 3 4 55 Sale price 34 Part 2 Depreciation and Amortization Schedul Years 35 Yar Intel Cost 36 37 Equipment Deprin Rate 20.00% 32.00% 19. 20% 38 Equipment Deprir, Dollars $2,000 $3200 51.920 39 Ending Bk Val: Cost - Accum Depir 10.000 8.000 4.800 2.850 40 41 Part 3. Net Salvage Values, In Year 4 Equipment 42 Estimated Market value in Year 4 3500 43 Book Value In Year 4 1,728 44 Expected Gain or Loss -1.228 45 Taxes paid or tax credit 48 Net cash flow from salvage 47 48 Part 4. Projected Net Cash Flows (Timeline of Annual Cash Flows) 49 Years 1 2 50 Investment Oudlays at Time Zero 51 Equipment $10,000) 52 53 Operating Cash Flows over the Projecr's Life: 54 Units sold 1,000 1,000 $24.00 $24.72 56 Variable coste $18.03 $24.000 $24.720 59 Variable coste 17.500 18,025 60 Norwariable operating cost 1,000 1,030 61 Depreciation equipment 2.000 3.299 62 Oper. Income before taxes (EBIT) 63 Texes on operating income (40%) 1.400 ANG 64 After tax operating Income 65 Add back depreciation 2.000 66 Operating cash flow 2100 H670 67 68 Terminal Year Cash Flows: 69 Required level of niet working capital 52.400 $2,400 52.472 70 Required investment in NWG (52.400) 10 372) 71 72 Terminal Year Cash Flows: 73 Net salvage value 74 75 Net Cash Flow (Time line of cash flows) 52.00 10 14607 76 77 Part 5 Key Output Appraisal of the Proposed Project 78 70 Net Present Value (at 10%) 80 IRR 81 MIRR 1,000 $25.46 $18.57 1,000 $26.23 $18.12 317.50 56 Sales revenu 325,462 $20.225 18.566 1.061 18.12 1.000 1.1920 13.00 12 1.540 2009 12.168 312 1.132 Ho 2015 1.12 H.007 1.200 H.269 $2,546 (374) 52.623 (376) 401 HR quipment Depen Rate quipment Deprin, Dollars Inding Bk Val: Cost - Accum Degir 20.00% $2.000 8.000 32.00% $3,200 4.800 19.20% $1,920 2.880 11.52% $1,152 1.728 10,000 $8,272 1,728 Part 3. Net Salvage Values, in Year 4 Equipment Estimated Market Value In Year 4 $500 Book Value In Year 4 1,728 Expected Gain or Loss -1,228 Taxes paid or tax credit 491 Net cash flow from salvage $491 Part 4. Projected Net Cash Flows (Time line of Annual Cash Flows) Years 0 1 Investment Outlays at Time Zero: Equipment ($10,000) 2 3 4 Operating Cash Flows over the Project's Life: Units sold 1,000 1,000 1,000 1,000 Sales price $24.00 $24.72 $25.46 $26.23 Variable costs $17.50 $18.03 518.57 $19.12 Sales revenue $24,000 $24,720 $25,462 $26,225 Variable costs 17,500 18,025 18,566 19,123 Nonvariable operating costs 1,000 1,030 1,061 1,093 Depreciation equipment 2.000 3,200 1.920 1.152 2 Oper. Income before taxes (EBIT $3,500 $2,465 $3,915 $4,858 3 Taxes on operating Income (40%) 1400 1.566 1.943 4 Ahertax operating Income $2,100 $1,479 52320 $2,015 15 Add back depreciation 2.000 3.200 1920 1.152 56 Operating cash flow 54.100 $4,679 54.269 34,067 58 Terminal Year Cash Flows: 169 Required level of networking capital $2,400 $2,400 $2,472 $2,546 52.623 70 Required investment in NWC (52,400) $0 (572) (574) (576) 71 72 Terminal Year Cash Flows: 73 Net salvage value 491 74 75 Net Cash Flow (Time line of cash flows) 1512.400) $4,697 $415 $44822 76 77 Part 5. Key Output: Appraisal of the Proposed Project 78 79 Net Present Value (at 10%) 80 RR 81 MIRR 62 83 Payback (See calculation below) 85 Data for Payback Years Net cash flow 67 Cumulative CF 88 Part of year required for payback 80 SO 91. Now conduct a selvity analysis to determine the sensity of NPV to changes in the sales price, 92 variable sota 93 per unit and numero units sold set these variables' values at 10% and 20% above and below the base 95 Part 6. Evaluating Risk: Bensivity Analyse 97 Sensity of NPV to Change lineHere we use Excel Data Tables" te And NP Vesteren sales 98 WACC, variable costs, sales price and nonvariable conte-changing one variable at holding her 100% Deviator YEAR UNIT BALES WACA 101 NPM 103 104 25 Build a Model (7,000) Percentage Deviation from Base Deviation from Base Casel -20% -10% 0% 10% 20% NPV at Different Deviations from Base Sales Variable Nonvariable Price Cost/Unit Units Sold Cost $0 $0 $0 $0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 WACC $0 0 0 0 0 Range $0 $0 $0 $0 $0 Now conduct a scenario analysis. Assume that there is a 25% probability that best-case conditions, with ach of the variables discussed in Part b being 20% better than its base-case value, will occur. There is a 55% probability of worst-case conditions, with the variables 20% worse than base, and a 50% probability of ase-case conditions. art 7. Evaluating Risk: Scenario Analysis Squared Deviation Times Probability Scenario Sales Price Unit Sales Probability Variable Costs NPV Best Case Base Case Worst Case 25% 50% 25% $28.80 $24.00 $19.20 1,200 1,000 800 $14.00 $17.50 $21.00 Expected NPV = sum, prob times NPV Standard Deviation = Sq Root of column H sum Coefficient of Variation - Std Dev / Expected NPV to d. If the project appears to be more or less risky than an average project, find its risk-adjusted NPV, IRR, and 9 payback. 50 31 CV range of firm's average-risk project 0.8 1.2 B2 Low-risk WACC 8% 83 WACC = 10% B4 High-risk WACC - 13% 85 86 Risk-adjusted WACC = 187 Risk adjusted NPV = 188 IRR - 189 Payback 190 191. On the basis of information in the problem, would you recommend that the project be accepted? 192 193 194 195 196 197 198 199 200 201 202 203 204