Coca-Cola Project Analysis

This is a EXCEL assignment so please add functions that used or text explainations THANKS!

To show your work and to receive credit, please use Excel cell formulas,

financial functions or algebraic time value of money equations to answer these questions in your spreadsheet.

Coca-Cola is considering jumping on the pomegranate bandwagon by producing Poma-Cola and Pomegranate Sprite carbonated beverages in 2016 (t=1). New production equipment and facilities costing $30 million will be required in 2015 (t =0) and fall into the 5-year straight-line depreciation class. Additional net working capital of $5 million will also be needed in 2015. Here are sales projections for the proposed projects.

Year Expected New Pomegranate Sales

2016 $22 million

2017 $30 million

2018 $25 million

2019 $20 million

Projected operating costs (other than depreciation) are 73% of sales for the new beverages. However, Coca-cola estimates that projected sales for other carbonated beverages will fall $5 million in 2016, $9 million in 2017, $7 million in 2018, and $4 million in 2019 if they introduce the new beverages. Projected operating costs for these existing beverages are 68% of sales. At the end of 2019, the project will end and the production equipment and facilities could be sold for an estimated $10 million.

Coca-Colas marginal tax rate is 40% and this is an average risk project for the company giving the project a WACC of 9%. Answer the following.

1. What is the initial cost of the project?

2. What are the expected operating cash flows for 2016 thru 2019?

3. What is the expected end of project termination cash flow at the end of 2019?

4. Calculate the NPV and IRR for the proposed project.

5. Should Coca-Cola go ahead with this project? Explain your answer.