Hi, I need the answer and also explanation of how to put it on excel, please :)
using formulas and cell references in excel. Use the excel spreadsheet template that is available on Canvas, under Assignments, for more details. You may round to two decimal places in your calculations. Suppose Nautical Adventures, Inc., is considering the following project (all dollar figures are in thousands of dollars). In year 0 , the project requires an $11,500 investment in plant and equipment. This equipment is depreciated using the straight-line method over seven years, and has a salvage value (after-tax) of $1,200 in year 7. The project is expected to generate sales of 3,000 units in year 1 . Details on the annual unit sales are given in the spreadsheet template, highlighted in yellow. Sales revenue per unit is forecast to be $10.00 in year 1 and then grow with inflation. Variable cost per unit is forecast to be $8.00 in year 1 and then grow with inflation. Cash fixed costs are forecast to be $5,280 in year 1 and then grow with inflation. The inflation rates that apply each year (to sales, VC, and FC) are shown in the spreadsheet. The company uses a 10% discount rate to evaluate new product decisions. The tax rate is forecast to be a constant 24.0%. The project will require working capital in the amount of $0.90 in year 0 for every unit of next year's forecasted sales, and this amount ($0.90) will grow with inflation going forward. 1) What is the project's NPV? What is the IRR? Would you accept this project? 2) Suppose you need to do some sensitivity analysis (please show on separate worksheet tab): (a) What if the firm suspects that this project is riskier than the firm's average project and decides to increase the cost of capital to 13%. What is the NPV? (b) What if the firm fears that its variable costs may be higher than estimated? What if the variable cost/unit starts off at $8.40 in year 1 , and then grows with inflation - what is the NPV? (assume the original cost of capital of 10% ) using formulas and cell references in excel. Use the excel spreadsheet template that is available on Canvas, under Assignments, for more details. You may round to two decimal places in your calculations. Suppose Nautical Adventures, Inc., is considering the following project (all dollar figures are in thousands of dollars). In year 0 , the project requires an $11,500 investment in plant and equipment. This equipment is depreciated using the straight-line method over seven years, and has a salvage value (after-tax) of $1,200 in year 7. The project is expected to generate sales of 3,000 units in year 1 . Details on the annual unit sales are given in the spreadsheet template, highlighted in yellow. Sales revenue per unit is forecast to be $10.00 in year 1 and then grow with inflation. Variable cost per unit is forecast to be $8.00 in year 1 and then grow with inflation. Cash fixed costs are forecast to be $5,280 in year 1 and then grow with inflation. The inflation rates that apply each year (to sales, VC, and FC) are shown in the spreadsheet. The company uses a 10% discount rate to evaluate new product decisions. The tax rate is forecast to be a constant 24.0%. The project will require working capital in the amount of $0.90 in year 0 for every unit of next year's forecasted sales, and this amount ($0.90) will grow with inflation going forward. 1) What is the project's NPV? What is the IRR? Would you accept this project? 2) Suppose you need to do some sensitivity analysis (please show on separate worksheet tab): (a) What if the firm suspects that this project is riskier than the firm's average project and decides to increase the cost of capital to 13%. What is the NPV? (b) What if the firm fears that its variable costs may be higher than estimated? What if the variable cost/unit starts off at $8.40 in year 1 , and then grows with inflation - what is the NPV? (assume the original cost of capital of 10% )
