RSM Co is considering a project which will require the purchase of $2.7 million in new equipment. The equipment will be depreciated straight-line to a book value of $1 million over the 5-year life of the project. Annual sales from this project are estimated at $2,950,000. The variable cost is 40% of the annual sales and there is an annual fixed cost of $200,000. Sway's Back Store will sell the equipment at the end of the project for 30% of its original cost. New net working capital equal to 15% of sales will be required to support the project. All of the new net working capital will be recouped at the end of the project. The firm’s WACC is 12% per year. The tax rate is 40%.

Please expain how to solve on excel

1. Please calculate the project NPV, IRR, Discounted Payback Period and Modified IRR (reinvestment return is 10%).

2. If annual sales is changed to $1,800,000, what is the project’s NPV. assuming every else the same? What is the sensitivity of NPV to annual sales?

3. In the worst case, annual sales decreases to $1.2 million, and WACC increases to 15%. What is the project’s NPV under the worst case?

4. In the best case, annual sales increases to $ 2 million, and WACC decrease to 10%. What is the project’s NPV under the best case?

5. What is the expected NPV and standard deviation of NPV, based on the three scenarios? (assume equal chance of occurrence for each scenario)

6. The company launches the project based on a marketing campaign one year before the project starts. The campaign costed $200,000. If the company launch the project without the campaign, there is only 50% chance that the project turns out to be expected. There is also 50% chance that the project would fail the market and leads to a NPV of - $2,450,000. How much value does the marketing campaign add to project?