Please help.

Show calculation, short report and explanation

Calculations must be done in Excel You must create your own spreadsheet (do not copy and paste someone elses).

This question should be done using Method 1 as outlined in lecture 6 (i.e. Tax Effects, then Cash Flows then NPV)

As the financial advisor to All Star Manufacturing you are evaluating the following new investment in a manufacturing project: -

• The project has a useful life of 8 years.
• Land costs $10m and is estimated to have a resale value of $15m at the completion of the project.
• Buildings cost $12m, with allowable depreciation of 6% pa reducing balance and a salvage value of $10m.
• Equipment costs $5m, with allowable depreciation of 10% pa reducing balance and a salvage value of $1m. An investment allowance of 20% of the equipment cost is available.
• Revenues are expected to be $15m in year one and rise at 5% pa.
• Cash variable costs are estimated at 30% of revenue.
• Cash fixed costs are estimated at $3m pa.
• Managerial salaries of $800,000 will be allocated to the project, but these managerial positions will be unaffected by the acceptance of the project.
• An amount of $200,000 has been spent on a feasibility study for the new project.
• The project is to be partially financed with a loan of $13.5m to be repaid annually with equal instalments at a rate of 5% pa over 8 years.
• Except for initial outlays, assume cash flows occur at the end of each year.
• The tax rate is 30% and is payable in the year in which profit is earned.
• The after-tax required return for the project is 11% pa.

Required

1. Calculate the NPV. Is the project acceptable? Why or why not?
2. Conduct a sensitivity analysis showing how sensitive the project is to revenues, fixed costs and to the required rate of return. Explain your results.