Using Excel formulas please solce the following
Vita-Juice is considering jumping on the pomegranate and acai bandwagon by producing Vita-Poma and Vita-Acai beverages in 2018 (t=1). New production equipment and facilities costing $30 million will be required in 2017(t =0) and fall into the 5-year straight-line depreciation class. Additional net working capital of $5 million will also be needed in 2017. Here are sales projections for the proposed projects. Year Expected New 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, Vita-Juice estimates that projected sales for other beverages will fall $5 million in 2018, $8 million in 2019, $7 million in 2020, and $4 million in 2021 if they introduce the new beverages. Projected operating costs for these existing beverages are 68% of sales. At the end of 2021, the project will end and the production equipment and facilities could be sold for an estimated $10 million. Vita-Juice’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 2017 thru 2020?
3. What is the expected end of project termination cash flow at the end of 2020?
4. Calculate the NPV and IRR for the proposed project.
5. Should Vita-Juice go ahead with this project? Explain your answer.