Question

Wansley Lumber is considering the purchase of a paper company, which would require an initial investment of $300 million. Wansley estimates that the paper company would provide net cash flows of $40 million at the end of each of the next 20 years. The cost of capital for the paper company is 13%.

a. Should Wansley purchase the paper company?

b. Wansley realizes that the cash flows in Years 1 to 20 might be $30 million per year or $50 million per year, with a 50% probability of each outcome. Because of the nature of the purchase contract, Wansley can sell the company 2 years after purchase (at Year 2 in this case) for $280 million if it no longer wants to own it. Given this additional information, does decision-tree analysis indicate that it makes sense to purchase the paper company? Again, assume that all cash flows are discounted at 13%.

c. Wansley can wait for 1 year and find out whether the cash flows will be $30 million per year or $50 million per year before deciding to purchase the company. Because of the nature of the purchase contract, if it waits to purchase, Wansley can no longer sell the company 2 years after purchase. Given this additional information, does decision-tree analysis indicate that it makes sense to purchase the paper company? Is so, when? Again, assume that all cash flows are discounted at 13%.

-----

Can someone please help me answer this in excel and show me the proper formulas? Thank you!

Answer 1

Solution.

Ans A. Purchase of project is not good as it result in negative NPV. as under

So in excel you should enter cash flow from zero year to 'n' number year. then write discount rate of return and use formula like = npv(select discount rate, thena all cash flow excluding zero year cash flow) + zero year cash flow

Like i did = npv(G3- interest rate, D3:D22 all cash inflow excluding zero year) + D2 zero year cash flow

Ans (b). By applying same concept you just need to take average of both cash flow as i explained in image and compute by applying same formula

Ans (c)   Same concept you should apply given in Ans (b) but this time cash inflow in year 1 will be zero as he wanted to wait for it and both two calculation need to be done one with 30 million and other with 50 millions so simple.