Question
Please show formulas for green boxes c D 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59
Please show formulas for green boxes
c D 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 Webmasters.com has developed a powerful new server that would be used for corporations' Internet activities. It would cost $4.250 million at Year O 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 11% ofthe year' s projected sales; for example, NWCO = The firm believes it could sell 985 units per year. The servers would sell for $4.47 thousand per unit and Webmasters believes that variable costs would amount to $2.25 thousand per unit. After Year 1, the sales price and variable costs will increase at the inflation rate of 2.0%. NOTE: The Input Data section already incorporates inflation in cells G70 through J70 and G71 through J71. The company's nonvariable costs would be $0.3 million at Year 1 and would increase with inflation. NOTE: The Input Data section already incorporates inflation in cells G72 through J72. The server project would have alife of4 years. Ifthe 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 equipment would be depreciated over a 5-year period, using MACRS rates given in the Input Data section below. The estimated market value ofthe equipment at the end ofthe project's 4-year life is $500 thousand. W ebmasters' federal-plus-state tax rate is 21 %. Its cost of capital is 14% for average-risk projects, defined as projects with a coemcient of variation of NPV between 1.9 and 2.3. Low-risk projects are evaluated with a WACC of 9%, and high-risk projects at 19%. Develop a spreadsheet model using the input data below, and use it to find the project's NPV, IRR, MIRR, and payback, assuming this is an average risk pr oject initially. In ut Data WACC Tax rate $ in thousands Growth rate for units sold Units sold Inflation in prices and costs (starting in year 2 Sales price per unit (excl. depr.) Variable costs per unit (excl. depr. Nonvariable costs (excl. depr. Equipment cost - Basis for depreciation Annual equipment depr. rate Sales price (salvage value) of equipment at Year 4 N et operating working capital/Sales 14% 21% $4,250 11% 1 985 $4.47 $2.25 $300 20.00% 11% Years 2 0.0% 985 2.0% $4.56 $2.30 $306 32.00% 11% 3 0.0% 985 2.0% $4.65 $2.34 $312 19.20% 11% 4 0.0% 985 2.0% $4.74 $2.39 $318 11.52% $500 0% ***Replace values in yellow cells with formulas. Otherwise your sensitivity and scenario analyses Intermediate Calculations Sales revenue Required level of net operating working capital $484 1 $4,403 $494 Years 2 $4,491 $504 3 $4,581 $514 4 $4,672 $0 c D 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 Webmasters.com has developed a powerful new server that would be used for corporations' Internet activities. It would cost $4.250 million at Year O 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 11% ofthe year' s projected sales; for example, NWCO = The firm believes it could sell 985 units per year. The servers would sell for $4.47 thousand per unit and Webmasters believes that variable costs would amount to $2.25 thousand per unit. After Year 1, the sales price and variable costs will increase at the inflation rate of 2.0%. NOTE: The Input Data section already incorporates inflation in cells G70 through J70 and G71 through J71. The company's nonvariable costs would be $0.3 million at Year 1 and would increase with inflation. NOTE: The Input Data section already incorporates inflation in cells G72 through J72. The server project would have alife of4 years. Ifthe 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 equipment would be depreciated over a 5-year period, using MACRS rates given in the Input Data section below. The estimated market value ofthe equipment at the end ofthe project's 4-year life is $500 thousand. W ebmasters' federal-plus-state tax rate is 21 %. Its cost of capital is 14% for average-risk projects, defined as projects with a coemcient of variation of NPV between 1.9 and 2.3. Low-risk projects are evaluated with a WACC of 9%, and high-risk projects at 19%. Develop a spreadsheet model using the input data below, and use it to find the project's NPV, IRR, MIRR, and payback, assuming this is an average risk pr oject initially. In ut Data WACC Tax rate $ in thousands Growth rate for units sold Units sold Inflation in prices and costs (starting in year 2 Sales price per unit (excl. depr.) Variable costs per unit (excl. depr. Nonvariable costs (excl. depr. Equipment cost - Basis for depreciation Annual equipment depr. rate Sales price (salvage value) of equipment at Year 4 N et operating working capital/Sales 14% 21% $4,250 11% 1 985 $4.47 $2.25 $300 20.00% 11% Years 2 0.0% 985 2.0% $4.56 $2.30 $306 32.00% 11% 3 0.0% 985 2.0% $4.65 $2.34 $312 19.20% 11% 4 0.0% 985 2.0% $4.74 $2.39 $318 11.52% $500 0% ***Replace values in yellow cells with formulas. Otherwise your sensitivity and scenario analyses Intermediate Calculations Sales revenue Required level of net operating working capital $484 1 $4,403 $494 Years 2 $4,491 $504 3 $4,581 $514 4 $4,672 $0
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started