I\'m looking to have the following example case worked through in Excel -- calcu

Question

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 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

1. Initial Investment = Initial cost + refurbishing cost + Increase in Working capital

= 4000000 + 750000 + 350000

= 5100000

2. Calculation of future annual incremental operating cash flows -

3. Terminal Cash flow = Net working capital + salvage value of equipment + EBIT of 5th year

= 1722500

4. NPV = -151273

5. Calculation of IRR -

By excel you can calculate the IRR = 10.84%

function will be used in excel =IRR(Cash flow from 0 to 5 years)

6. No as its IRR is less than 12% and NPV comes Negative will not implement the project.

7. stock price will reduce as this project will has negative cashflow measn its profitability is negative.

Calculation of Net working capital -

Increase in inventory + increase in accounts receivable - increase in accounts payale

= 200000 + 300000 - 150000

= 350000

Pleae note all values are in $ and long term debts does not include in calculation of project cash flow as it has long term debt and does not mention that specifically taken for this project.

In case of any clarification required please comment.

Year 0 1 2 3 4 5 Initial Cost 4000000 refurbished cost' 750000 Increase Working capital 350000 Revenue 3500000 3500000 3500000 3500000 3500000 less COGS and SGA 2000000 2000000 2000000 2000000 2000000 less Depriciation 950000 950000 950000 950000 950000 add Salvage value 100000 EBIT 550000 550000 550000 550000 650000 less Tax @ 35% 192500 192500 192500 192500 227500 EAT 357500 357500 357500 357500 422500 add Depriciation 950000 950000 950000 950000 950000 Working capital 350000 Cash inflow 1307500 1307500 1307500 1307500 1722500

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

---

---

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