*This is an assignment that I have to turn in using Excel, if you can tell me which formulas to use and which numbers to put in where, that would be great! I want to understand the question, not just get the answer!*
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-Colaâs 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.
*This is an assignment that I have to turn in using Excel, if you can tell me which formulas to use and which numbers to put in where, that would be great! I want to understand the question, not just get the answer!*
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-Colaâs 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.
Related questions
Meg Whalen, a financial analyst for Cheek Products, Inc., believes two major changes are needed at the company. First, she thinks that the company would be better off if it sold several divisions and concentrated on its core competencies in snack foods and security systems. Second, the company is financed entirely with equity. Because the cash flows of the company are relatively steady, Meg thinks the company's debt-equity ratio should be at least 0.25.
Meg has suggested a potential LBO to her partners, who have asked Meg to provide projections of the cash flows for the company. Here are Meg's estimates:
| 2015 | 2016 | 2017 | 2018 | 2019 | |
| Sales | 2,749 | 3,083 | 3,322 | 3,400 | 3,539 |
| Costs | 731 | 959 | 1,009 | 1,091 | 1,149 |
| Depreciation | 485 | 516 | 537 | 564 | 575 |
| EBT | 1,533 | 1,608 | 1,776 | 1,745 | 1,815 |
| CapEx | 279 | 242 | 304 | 308 | 304 |
| Change in NWC | -122 | -186 | 101 | 95 | 108 |
| Asset Sales | 1,419 | 1,028 | n.a. | n.a. | n.a. |
At the end of the five years, Meg estimates that the growth rate in cash flows will be 3.50% per year. The CapEx are for new projects and the replacement equipment that wears out. Additionally, the company would realize cash flow from the sale of several divisions. Even though the company will sell these divisions, overall sales should increase because of a more concentrated effort on the remaining divisions.
Meg is also aware that they will have to borrow a considerable amount of the purchase price. The interest payments on the debt for each of the next five years if the LBO is undertaken will be these (in millions):
| 2015 | 2016 | 2017 | 2018 | 2019 | |
| Interest Payments | 1,927 | 1,859 | 2,592 | 2,526 | 2,614 |
The company currently has a required return on assets of 14 percent. Because of the high debt level, the debt will carry a yield to maturity of 12.50% for the next five years. When the debt is refinanced in five years, they believe the new yield to maturity will be 8.00%. The company currently has 425 million shares of stock outstanding that sell for $29 per share. The corporate tax rate is 40%. If the partners decide to undertake the LBO, what is the most they should offer per share?
