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 projects 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 variation for NPV between 0.8 and 1.2. Low-risk projects are evaluated with a WACC of 8%, and high-risk projects at 13%.

Please show the formulas and excel sheet in pictures if possible. Thank you!

5 6 Webmasters.com has developed a powerful new server that would be used for corporations' Internet activities. It would cost 7 $10 million to buy the equipment necessary to manufacture the server, and it would require net working capital equal to 8 10% of sales. The servers would sell for $24,000 per unit, and Webmasters believes that variable costs would amount to 9 $17,500 per unit. After the first year, the sales price and variable costs would increase at the inflation rate of 3%. The 10 company's fixed costs be $1 million per year and increase with inflation. It would take one year to buy the required 11 equipment and set up operations, and the server project would have a life of 4 years. If the project is undertaken, it must be 12 continued for the entire 4 years. Also, the project's returns are expected to be highly correlated with returns on the firm's 13 other assets. The firm believes it could sell 1,000 units per year. 14 15 16 The equipment would be depreciated over a 5-year period, using MACRS rates. The estimated market value of the 17 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 18 capital is 10% for average risk projects, defined as projects with a coefficient of variation for NPV between 0.8 and 1.2. Low 19 risk projects are evaluated with a WACC of 8%, and high risk projects at 13%. 20 21 a. Develop a spreadsheet model and use it to find the project's NPV, IRR, and payback. 22 23 24 Key Output: NPV = 25 Part 1. Input Data (in thousands of dollars) IRR 26 MIRR = 27 Equipment cost $10,000 28 Net Operating WC/sales 10% Market value of equipment in 2005 $500 29 First year sales (in units) 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 Fixed costs $1,000 33 34 Part 2. Depreciation and Amortization Schedule Years Accum'd 35 Year Initial Cost 2 Depr'n 36 37 Equipment Depr'n Rate 20.0% 32.0% 19.0% 12.0% 38 Equipment Depr'n, Dollars 39 Ending Bk Val: Cost - Accum Dep'rn 1 3 40 Equipment 41 Part 3. Net Salvage Values, in Year 4 42 Estimated Market Value in Year 4 43 Book Value in Year 4 44 Expected Gain or Loss 45 Taxes paid or tax credit 46 Net cash flow from salvage 47 48 Part 4. Projected Net Cash Flows (Time line of annual cash flows) 49 Years 0 50 Investment Outlays at Time Zero: 51 Equipment 10,000 52 1 2 3 4 5 6 Webmasters.com has developed a powerful new server that would be used for corporations' Internet activities. It would cost 7 $10 million to buy the equipment necessary to manufacture the server, and it would require net working capital equal to 8 10% of sales. The servers would sell for $24,000 per unit, and Webmasters believes that variable costs would amount to 9 $17,500 per unit. After the first year, the sales price and variable costs would increase at the inflation rate of 3%. The 10 company's fixed costs be $1 million per year and increase with inflation. It would take one year to buy the required 11 equipment and set up operations, and the server project would have a life of 4 years. If the project is undertaken, it must be 12 continued for the entire 4 years. Also, the project's returns are expected to be highly correlated with returns on the firm's 13 other assets. The firm believes it could sell 1,000 units per year. 14 15 16 The equipment would be depreciated over a 5-year period, using MACRS rates. The estimated market value of the 17 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 18 capital is 10% for average risk projects, defined as projects with a coefficient of variation for NPV between 0.8 and 1.2. Low 19 risk projects are evaluated with a WACC of 8%, and high risk projects at 13%. 20 21 a. Develop a spreadsheet model and use it to find the project's NPV, IRR, and payback. 22 23 24 Key Output: NPV = 25 Part 1. Input Data (in thousands of dollars) IRR 26 MIRR = 27 Equipment cost $10,000 28 Net Operating WC/sales 10% Market value of equipment in 2005 $500 29 First year sales (in units) 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 Fixed costs $1,000 33 34 Part 2. Depreciation and Amortization Schedule Years Accum'd 35 Year Initial Cost 2 Depr'n 36 37 Equipment Depr'n Rate 20.0% 32.0% 19.0% 12.0% 38 Equipment Depr'n, Dollars 39 Ending Bk Val: Cost - Accum Dep'rn 1 3 40 Equipment 41 Part 3. Net Salvage Values, in Year 4 42 Estimated Market Value in Year 4 43 Book Value in Year 4 44 Expected Gain or Loss 45 Taxes paid or tax credit 46 Net cash flow from salvage 47 48 Part 4. Projected Net Cash Flows (Time line of annual cash flows) 49 Years 0 50 Investment Outlays at Time Zero: 51 Equipment 10,000 52 1 2 3 4