I'm looking to have the following example case worked through in Excel -- calculations MUST be performed using Excel functions, which is the part I'm struggling most with. Feel free to solve / set it up in any way that helps clarify how one would get these answers using Excel. Thank you very much!

Your employer, Capital Corp., is considering a capital project. The project involves installing a new

manufacturing line that will cost $4 million. The line will be installed area of the factory that was

refurbished in 2008 at a cost of $750,000. The line will be depreciated on a straight-line basis

over five years, to a salvage value of $0. If implemented, the project will cause an immediate

increase in Inventory of $200,000. It will also cause immediate increases in Accounts Receivable

of $300,000, Accounts Payable of $150,000, and Long-Term Debt of $3 million.

If implemented, the project is expected to generate annual sales of $3,500,000 during each of

the next five years. In addition, it is expected to generate combined COGS and SG&A expense of

$2 million per year. At the end of the projects five-year life, production will cease, and the

manufacturing line will be sold for an estimated $100,000. At that time, Inventory, Accounts

Receivable and Accounts Payable will return to their pre-project levels.

Capitals marginal and average tax rate is 35%. The firm has 1 million shares of common stock

outstanding. The firm requires a 12% rate of return on capital projects.

Prepare a discounted cash flow analysis to determine whether your employer should implement

this capital project. Your analysis should provide answers to each of the following questions.

1. What is the projects initial investment?

2. What are the future annual incremental operating cash flows?

3. What is the terminal cash flow?

4. What is the NPV?

5. What is the IRR?

6. Should Capital implement the project? Why or why not?

7. If Capital implements the project, what will be the impact on the stock price?