I was wanting to make sure my work is correct! I need to find the IRR and NPV based off of the Hola Kola case and I also need to know to invest or not!

TB0343 Lena Chua Booth Hola-KolaThe Capital Budgeting Decision The consumption of sugar-sweetened beverages has been linked to risks for obesity, diabetes, and heart disease; therefore, a compelling case can be made for the need for reduced consumption of these beverages. Health Policy Report, The New England Journal of Medicine, October 15, 2009. Mexico leads world in soda consumption, World Health Organization planning to fight it. Carolyn Crist, Obesity Initiative, October 25, 2012. In December 2012, Antonio Ortega, the owner of Bebida Sol, had just finished reading a report done by his general manager, Pedro Cortez, about the possible investment in a new product line, Hola-Kola. The idea of Hola-Kola came about three months earlier when Antonio attended a seminar on youth obesity organized by a local high school that his two children attended. Even though he had often heard of the rising obesity problem in Mexico, Antonio was still very disturbed by the statistics indicating how the obesity rate in Mexico had tripled since 1980, and that 69.5% of the people 15 years and older were either obese or overweight. Even more shocking to Antonio, based on this statistic, Mexico now had the highest overweight rate in the world, surpassing the United States.1 After the seminar, Antonio discussed the idea of Hola-Kola, a low-price, zero-calorie carbonated soft drink, with Pedro Cortez. Pedro was excited about the idea, and liked the opportunity to launch something new, especially given that the company had not introduced a new product in the last five years. However, Pedro thought a market study should be done to gauge the potential demand before the firm undertook the investment. Company Background Bebida Sol is a small, privately owned carbonated soft drink company based in Puebla, Mexico. A retired executive from a popular fast-food restaurant chain, Roberto Ortega, founded it in 1998. During his career as a restaurant executive, Roberto learned that Mexicans, regardless of social status, loved their soda pop. Many would drink soda to quench their thirst on a regular basis, due to the lack of hygienic, drinkable water. With the influx of international brands of soda pop, Mexico now had the highest consumption of carbonated soft drinks per capita in the world.2 The average per capita consumption was 40% higher than the United States, at 163 liters (43 gallons) per year, while the United States consumed 118 liters (31 gallons), according to statistics presented by the international organization Oxfam and the Mexican NGO Consumer's Power. Due to the high obesity problem, health and consumer groups in Mexico had demanded that the government impose a 20% tax on soft drinks, claiming that it would not only reduce consumption, but the tax revenue could also be used to fight health problems that soft drinks generated.3 \"The World is Fat\" by Catherine Rampell, 9/23/2010. http://economix.blogs.nytimes.com/2010/09/23/the-world-is-fat/. http://gain.fas.usda.gov/Recent%20GAIN%20Publications/The%20Mexican%20Market%20for%20Soft% 20Drinks_ Mexico%20ATO_Mexico_8-20-2009.pdf; Global Agriculture Information Network (GAIN) report MX9326. 3 \"Mexico, Leader in Soft Drink Consumption\2012 Projected Revenue Units sold Selling Price per Unit Revenue 2013 2014 $7,200,000 $5 $36,000,000 $7,524,000 $5 $37,620,000 $7,862,580 $5 $39,312,900 $12,960,000 $13,543,200 $14,152,644 $500,000 $2,160,000 $600,000 $16,220,000 $500,000 $2,160,000 $600,000 $16,803,200 $500,000 $2,160,000 $600,000 $17,412,644 $300,000 $300,000 $300,000 $19,480,000 $9,200,000 $20,516,800 $9,200,000 $21,600,256 $9,200,000 $10,280,000 $11,316,800 $12,400,256 Taxes (30%) $3,084,000 $3,395,040 $3,720,077 Net Operating Profit After Taxes (NOPAT) $7,196,000 $7,921,760 $8,680,179 Add depreciation $9,200,000 $9,200,000 $9,200,000 Inventory based on Sales $3,000,000 $3,135,000 $3,276,075 Accounts Recievable $4,500,000 $4,702,500 $4,914,113 Accounts Payable $3,600,000 $3,762,000 $3,931,290 Net Working Capital Investment in NWC Initial Investment Free Cash Flow Total Cash Flow Cumulative FCF $3,900,000 $4,075,500 $4,258,898 $3,900,000 $175,500 $183,398 -$55,000,000 $16,946,260 $17,696,782 -$55,000,000 $16,946,260 $17,696,782 -$55,000,000 -$38,053,740 -$20,356,958 Production Costs Operating Expenses: Raw Materials Overhead Labor Costs Energy Costs Total: Selling and Admin Costs EBITDA Depreciation EBIT Year WACC Interest on Investment Present Value Total Present Value Net Present Value (NPV) Internal Rate of Return (IRR) -$55,000,000 0 1 2 18.20% 16.00% -$55,000,000 $59,857,140 $4,857,140 20% $14,608,845 $13,151,592 invest! 2015 2016 2017 $8,216,396 $5 $41,081,981 $8,586,134 $5 $42,930,670 $8,972,510 $5 $44,862,550 $14,789,513 $15,455,041 $16,150,518 $500,000 $2,160,000 $600,000 $18,049,513 $500,000 $2,160,000 $600,000 $18,715,041 $500,000 $2,160,000 $600,000 $19,410,518 $300,000 $300,000 $300,000 $22,732,468 $9,200,000 $23,915,629 $9,200,000 $13,532,468 $14,715,629 $4,059,740 $4,414,689 $9,472,727 $10,300,940 $9,200,000 $9,200,000 $25,152,032 $9,200,000 $15,952,032 $4,785,610 $11,166,422 $9,200,000 $3,423,498 $3,577,556 $3,738,546 $5,135,248 $5,366,334 $5,607,819 $4,108,198 $4,293,067 $4,486,255 $4,450,548 $4,650,823 $4,860,110 $191,650 $200,275 $209,287 $18,481,077 $19,300,665 $20,157,135 $18,481,077 $19,300,665 $20,157,135 -$1,875,881 $17,424,784 $37,581,919 3 $11,840,044 4 $10,659,586 5 $9,597,074 Internal Rate of Retun Year 2012 2013 2014 2015 2016 2017 Year 0 1 2 3 4 5 FCF -$55,000,000 $16,946,260 $17,696,782 $18,481,077 $19,300,665 $20,157,135 Present Value Net Present Value Internal Rate of Return= PV -$55,000,000 $14,608,845 $13,151,592 $11,840,044 $10,659,585 $9,597,074 $59,857,140 $4,857,140 20% Interest Rate WACC 16% 18.20% 0.16 0.182