Good evening, I\'m looking to have the following example case worked through in

Question

Good evening, I'm looking to have the following example case worked through in Excel -- calculations needs to 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 project’s 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.

Capital’s 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 project’s 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?

Explanation / Answer

Step 1) Statement showing initial investment

Step 2) Depreciation = 4000000/5 = 800,000$

Step 3 ) Statement showing Annual cash flow

Step 4) Terminal value

Statement showing NPV

Thus project should be selected

Particulars Amount New machinery line 4000000 Increase in inventory 200000 increase in Account receivable 300000 Increase in account payable -150000 Long term debt -3000000 Initial cash flow/investment 1350000

Related Questions

Navigate

• Browse (All)
• Browse G
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.