Determine if Coca Cola should invest in a new drink project so you need to determine net cash flows and NPV and determine if the project is viable or not. Capital expenditures to produce the ride will initially require an investment of $1.2 billion. Other development costs that will be required to finish the ride is $500 million this year. Any ongoing costs for upgrades will be covered in the margin calculation below. The ride is expected to have a life of five years. First-year revenues for the new ride are expected to be $2,000,000,000 ($2,000 million). The ride revenues are expected to grow by 20% for the second year, and then decrease by 10% for the third, decrease by 20% for the 4th and finally decrease by 50% for the 5th (final) year of operation. Your job is to determine the rest of the cash flows associated with this project. Your boss has indicated that the operating costs and net working capital requirements are similar to the rest of the company's products. Since your boss hasn't been much help, here are some tips to guide your analysis: 1. You will need to use the Financial Statements that you downloaded. 2. Determine the free cash flow. Compute the free cash flow for each year using: Free Cash Flow = (Revenues - Costs-Depreciation) x(1- Tax Rate) + Depreciation - Capex - Change in NWC Set up the timeline and computation of the free cash flow in separate, contiguous columns for each year of the project life. Be sure to make outflows negative and inflows positive. a. Assume that the project's profitability will be similar to Coca Cola's existing projects and estimate costs for each year of your project by using the average ratio of non-depreciation costs to revenue for the last 2 fiscal years in practice you really tend to use at least 4 years worth of data, but for this exercise 2 years will suffice): (Costs of Goods Sold + SG&A)/Sales You should assume that this ratio will hold for this project as well. You do not need to break out the individual components of operating costs in your forecast Simply the forecast to the Total Cost of Goods Sold + SG&A +R&D for each year. Determine Coca Cola's implicit tax rate as 1- (Income After Tax/Income Before Tax) using the most recent financial statement. Recalculate the WACC form Part 1 using this tax rate. Calculate the net working capital required each year by assuming that the level of NWC will be a constant percentage of the project's sales. Use Coca Cola's last 2 fiscal year average NWC/Sales to estimate the required percentage. (Use only accounts receivable, accounts payable, and inventory to measure working capital. Other components of current assets and liabilities are harder to interpret and are not necessarily reflective of the project's required NWC-e.g., Coca Cola's cash holdings.) To determine the free cash flow, calculate the additional capital investment and the change in net working capital each year. Determine the NPV of the project with WACC calculated in Part 1 step 10 and the projects IRR For the NPV calculation remember to add the first CF when you are using the excel function =NPV(rate, CF1:CF5) + CFO For the IRR include all cash flows in your excel calculation. B 3 4 7 A 1 CONSOLIDATED STATEMENTS OF INCOME - USD ($) shares in Millions, $ in Millions 2 Revenues Cost of Goods and Services Sold 5 GROSS PROFIT 6 Selling, General and Administrative Expense Other Cost and Expense, Operating 8 OPERATING INCOME 9 Interest income 10 Interest expense 11 Equity income (loss) - net 12 Other income (loss) - net 13 INCOME BEFORE INCOME TAXES 14 Income taxes 15 CONSOLIDATED NET INCOME 16 Net Income (Loss) Attributable to Noncontrolling Interest 17 NET INCOME ATTRIBUTABLE TO SHAREOWNERS OF THE COCA-COLA COMPANY 18 BASIC NET INCOME PER SHARE (in dollars per share) 19 DILUTED NET INCOME PER SHARE (in dollars per share) 20 AVERAGE SHARES OUTSTANDING (in shares) 21 Effect of dilutive securities (in shares) 22 AVERAGE SHARES OUTSTANDING ASSUMING DILUTION (in shares) 12 Months Ended Dec 31, 2019 Dec 31, 2018 $ 37,266 $ 34,300 14,619 13,067 22,647 21,233 12,103 11,002 458 1,079 10,086 9,152 563 689 946 950 1,049 1,008 34 (1,674) 10,786 8,225 1,801 1,749 8,985 6,476 65 42 $8,920 $ 6,434 $ 2.09 $1.51 $2.07 $ 1.50 4,276 4,259 38 40 4,314 4,299 B D Dec 31, 2019 Dec. 31, 2018 1 CONSOLIDATED BALANCE SHEETS - USD ($) $ in Millions 2 CURRENT ASSETS 3 Cash and cash equivalents 4 Short-term investments 5 TOTAL CASH, CASH EQUIVALENTS AND SHORT-TERM INVESTMENTS 6 Marketable securities 7 Trade accounts receivable, less allowances of $524 and $501, respectively 8 Inventories 9 Prepaid expenses and other assets 10 TOTAL CURRENT ASSETS 11 EQUITY METHOD INVESTMENTS 12 OTHER INVESTMENTS 13 OTHER ASSETS 14 Deferred Tax Assets, Net, Noncurrent 15 PROPERTY, PLANT AND EQUIPMENT - net 16 TRADEMARKS WITH INDEFINITE LIVES 17 BOTTLERS' FRANCHISE RIGHTS WITH INDEFINITE LIVES 18 GOODWILL 19 OTHER INTANGIBLE ASSETS 20 TOTAL ASSETS 21 CURRENT LIABILITIES 22 Accounts payable and accrued expenses 23 Notes and Loans Payable, Current 24 Current maturities of long-term debt 25 Accrued income taxes 26 TOTAL CURRENT LIABILITIES 27 LONG-TERM DEBT 28 OTHER LIABILITIES 29 DEFERRED INCOME TAXES 30 THE COCA-COLA COMPANY SHAREOWNERS' EQUITY 31 Common stock, $0.25 par value; authorized - 11,200 shares; issued - 7,040 shares 32 Capital surplus 33 Reinvested earnings 34 Accumulated other comprehensive income (loss) 35 Treasury stock, at cost - 2,760 and 2,772 shares, respectively 36 EQUITY ATTRIBUTABLE TO SHAREOWNERS OF THE COCA-COLA COMPANY 37 EQUITY ATTRIBUTABLE TO NONCONTROLLING INTERESTS 38 TOTAL EQUITY 39 TOTAL LIABILITIES AND EQUITY $ 6,480 1,467 7,947 3,228 3,971 3,379 1,886 20,411 19,025 854 6,075 2,412 10,838 9,266 109 16,764 627 86,381 $ 9,077 2,025 11,102 5,013 3,685 3,071 2,059 24,930 19,412 867 4,148 2,674 9,598 6,682 51 14,109 745 83,216 11,312 10,994 4,253 414 26,973 27,516 8,510 2,284 9,533 13,835 5,003 411 28,782 25,376 7,646 2,354 1,760 17,154 65,855 (13,544) (52,244) 18,981 2,117 21,098 $ 86,381 1,760 16,520 63,234 (12,814) (51,719) 16,981 2,077 19,058 $ 83,216 B 1 12 Months Ended Dec 31, 2018 2 Dec 31, 2019 De $ 8,985 1,365 201 (280) (421) 91 (467) 127 504 366 10,471 $ 6,476 1,086 225 (413) (457) (50) 743 558 699 (1,240) 7,627 CONSOLIDATED STATEMENTS OF CASH FLOWS - USD ($) $ in Millions 3 OPERATING ACTIVITIES 4 CONSOLIDATED NET INCOME 5 Depreciation and amortization 6 Stock-based compensation expense 7 Deferred income taxes 8 Equity (income) loss - net of dividends 9 Foreign currency adjustments 10 Significant (gains) losses on sales of assets - net 11 Other operating charges 12 Other items 13 Net change in operating assets and liabilities 14 Net cash provided by operating activities 15 INVESTING ACTIVITIES 16 Purchases of investments 17 Proceeds from disposals of investments 18 Acquisitions of businesses, equity method investments and nonmarketable securities 19 Proceeds from disposals of businesses, equity method investments and nonmarkatable securities 20 Purchases of property, plant and equipment 21 Proceeds from disposals of property, plant and equipment 22 Other investing activities 23 Net cash provided by (used in) Investing activities 24 FINANCING ACTIVITIES 25 Issuances of debt 26 Payments of debt 27 Issuances of stock 28 Purchases of stock for treasury 29 Dividends 30 Other financing activities 31 Net cash provided by (used in) financing activities 32 EFFECT OF EXCHANGE RATE CHANGES ON CASH AND CASH EQUIVALENTS 33 CASH AND CASH EQUIVALENTS Net increase (decrease) in cash, cash equivalents, restricted cash and restricted cash equivalents during 34 the year 35 Cash, Cash Equivalents, Restricted Cash and Restricted Cash Equivalents at the Beginning of the Year 36 Cash, Cash Equivalents, Restricted Cash and Restricted Cash Equivalents at the End of the Year Restricted Cash and Restricted Cash Equivalents not included in Cash and Cash Equivalents on Balance 37 Sheet 38 Cash and cash equivalents 39 (4,704) 6,973 (5,542) 429 (2,054) 978 (56) (3,976) 17,789) 14,977 (1,263) 1,362 (1,548) 248 (60) 5,927 23,009 (24,850) 1,012 (1,103) (6,845) (227) (9,004) (72) 27,605 (30,600) 1,476 (1,912) (6,644) (272) (10,347) (262) (2,581) 2,945 9,318 6,737 257 6,373 9,318 241 $ 6,480 $ 9,077 D H AAP's New Stores Project Amounts in Millions Year 0 2. 3 5 Revenues 1,200,000,000 Costs Depreciation Tax Rate Capital Expenditures NWC Chg NWC Recover NWC FCF (millions) Cost of Capital NPV- IRRE (For the IRR include all cash flows in your excel calculation.) Determine if Coca Cola should invest in a new drink project so you need to determine net cash flows and NPV and determine if the project is viable or not. Capital expenditures to produce the ride will initially require an investment of $1.2 billion. Other development costs that will be required to finish the ride is $500 million this year. Any ongoing costs for upgrades will be covered in the margin calculation below. The ride is expected to have a life of five years. First-year revenues for the new ride are expected to be $2,000,000,000 ($2,000 million). The ride revenues are expected to grow by 20% for the second year, and then decrease by 10% for the third, decrease by 20% for the 4th and finally decrease by 50% for the 5th (final) year of operation. Your job is to determine the rest of the cash flows associated with this project. Your boss has indicated that the operating costs and net working capital requirements are similar to the rest of the company's products. Since your boss hasn't been much help, here are some tips to guide your analysis: 1. You will need to use the Financial Statements that you downloaded. 2. Determine the free cash flow. Compute the free cash flow for each year using: Free Cash Flow = (Revenues - Costs-Depreciation) x(1- Tax Rate) + Depreciation - Capex - Change in NWC Set up the timeline and computation of the free cash flow in separate, contiguous columns for each year of the project life. Be sure to make outflows negative and inflows positive. a. Assume that the project's profitability will be similar to Coca Cola's existing projects and estimate costs for each year of your project by using the average ratio of non-depreciation costs to revenue for the last 2 fiscal years in practice you really tend to use at least 4 years worth of data, but for this exercise 2 years will suffice): (Costs of Goods Sold + SG&A)/Sales You should assume that this ratio will hold for this project as well. You do not need to break out the individual components of operating costs in your forecast Simply the forecast to the Total Cost of Goods Sold + SG&A +R&D for each year. Determine Coca Cola's implicit tax rate as 1- (Income After Tax/Income Before Tax) using the most recent financial statement. Recalculate the WACC form Part 1 using this tax rate. Calculate the net working capital required each year by assuming that the level of NWC will be a constant percentage of the project's sales. Use Coca Cola's last 2 fiscal year average NWC/Sales to estimate the required percentage. (Use only accounts receivable, accounts payable, and inventory to measure working capital. Other components of current assets and liabilities are harder to interpret and are not necessarily reflective of the project's required NWC-e.g., Coca Cola's cash holdings.) To determine the free cash flow, calculate the additional capital investment and the change in net working capital each year. Determine the NPV of the project with WACC calculated in Part 1 step 10 and the projects IRR For the NPV calculation remember to add the first CF when you are using the excel function =NPV(rate, CF1:CF5) + CFO For the IRR include all cash flows in your excel calculation. B 3 4 7 A 1 CONSOLIDATED STATEMENTS OF INCOME - USD ($) shares in Millions, $ in Millions 2 Revenues Cost of Goods and Services Sold 5 GROSS PROFIT 6 Selling, General and Administrative Expense Other Cost and Expense, Operating 8 OPERATING INCOME 9 Interest income 10 Interest expense 11 Equity income (loss) - net 12 Other income (loss) - net 13 INCOME BEFORE INCOME TAXES 14 Income taxes 15 CONSOLIDATED NET INCOME 16 Net Income (Loss) Attributable to Noncontrolling Interest 17 NET INCOME ATTRIBUTABLE TO SHAREOWNERS OF THE COCA-COLA COMPANY 18 BASIC NET INCOME PER SHARE (in dollars per share) 19 DILUTED NET INCOME PER SHARE (in dollars per share) 20 AVERAGE SHARES OUTSTANDING (in shares) 21 Effect of dilutive securities (in shares) 22 AVERAGE SHARES OUTSTANDING ASSUMING DILUTION (in shares) 12 Months Ended Dec 31, 2019 Dec 31, 2018 $ 37,266 $ 34,300 14,619 13,067 22,647 21,233 12,103 11,002 458 1,079 10,086 9,152 563 689 946 950 1,049 1,008 34 (1,674) 10,786 8,225 1,801 1,749 8,985 6,476 65 42 $8,920 $ 6,434 $ 2.09 $1.51 $2.07 $ 1.50 4,276 4,259 38 40 4,314 4,299 B D Dec 31, 2019 Dec. 31, 2018 1 CONSOLIDATED BALANCE SHEETS - USD ($) $ in Millions 2 CURRENT ASSETS 3 Cash and cash equivalents 4 Short-term investments 5 TOTAL CASH, CASH EQUIVALENTS AND SHORT-TERM INVESTMENTS 6 Marketable securities 7 Trade accounts receivable, less allowances of $524 and $501, respectively 8 Inventories 9 Prepaid expenses and other assets 10 TOTAL CURRENT ASSETS 11 EQUITY METHOD INVESTMENTS 12 OTHER INVESTMENTS 13 OTHER ASSETS 14 Deferred Tax Assets, Net, Noncurrent 15 PROPERTY, PLANT AND EQUIPMENT - net 16 TRADEMARKS WITH INDEFINITE LIVES 17 BOTTLERS' FRANCHISE RIGHTS WITH INDEFINITE LIVES 18 GOODWILL 19 OTHER INTANGIBLE ASSETS 20 TOTAL ASSETS 21 CURRENT LIABILITIES 22 Accounts payable and accrued expenses 23 Notes and Loans Payable, Current 24 Current maturities of long-term debt 25 Accrued income taxes 26 TOTAL CURRENT LIABILITIES 27 LONG-TERM DEBT 28 OTHER LIABILITIES 29 DEFERRED INCOME TAXES 30 THE COCA-COLA COMPANY SHAREOWNERS' EQUITY 31 Common stock, $0.25 par value; authorized - 11,200 shares; issued - 7,040 shares 32 Capital surplus 33 Reinvested earnings 34 Accumulated other comprehensive income (loss) 35 Treasury stock, at cost - 2,760 and 2,772 shares, respectively 36 EQUITY ATTRIBUTABLE TO SHAREOWNERS OF THE COCA-COLA COMPANY 37 EQUITY ATTRIBUTABLE TO NONCONTROLLING INTERESTS 38 TOTAL EQUITY 39 TOTAL LIABILITIES AND EQUITY $ 6,480 1,467 7,947 3,228 3,971 3,379 1,886 20,411 19,025 854 6,075 2,412 10,838 9,266 109 16,764 627 86,381 $ 9,077 2,025 11,102 5,013 3,685 3,071 2,059 24,930 19,412 867 4,148 2,674 9,598 6,682 51 14,109 745 83,216 11,312 10,994 4,253 414 26,973 27,516 8,510 2,284 9,533 13,835 5,003 411 28,782 25,376 7,646 2,354 1,760 17,154 65,855 (13,544) (52,244) 18,981 2,117 21,098 $ 86,381 1,760 16,520 63,234 (12,814) (51,719) 16,981 2,077 19,058 $ 83,216 B 1 12 Months Ended Dec 31, 2018 2 Dec 31, 2019 De $ 8,985 1,365 201 (280) (421) 91 (467) 127 504 366 10,471 $ 6,476 1,086 225 (413) (457) (50) 743 558 699 (1,240) 7,627 CONSOLIDATED STATEMENTS OF CASH FLOWS - USD ($) $ in Millions 3 OPERATING ACTIVITIES 4 CONSOLIDATED NET INCOME 5 Depreciation and amortization 6 Stock-based compensation expense 7 Deferred income taxes 8 Equity (income) loss - net of dividends 9 Foreign currency adjustments 10 Significant (gains) losses on sales of assets - net 11 Other operating charges 12 Other items 13 Net change in operating assets and liabilities 14 Net cash provided by operating activities 15 INVESTING ACTIVITIES 16 Purchases of investments 17 Proceeds from disposals of investments 18 Acquisitions of businesses, equity method investments and nonmarketable securities 19 Proceeds from disposals of businesses, equity method investments and nonmarkatable securities 20 Purchases of property, plant and equipment 21 Proceeds from disposals of property, plant and equipment 22 Other investing activities 23 Net cash provided by (used in) Investing activities 24 FINANCING ACTIVITIES 25 Issuances of debt 26 Payments of debt 27 Issuances of stock 28 Purchases of stock for treasury 29 Dividends 30 Other financing activities 31 Net cash provided by (used in) financing activities 32 EFFECT OF EXCHANGE RATE CHANGES ON CASH AND CASH EQUIVALENTS 33 CASH AND CASH EQUIVALENTS Net increase (decrease) in cash, cash equivalents, restricted cash and restricted cash equivalents during 34 the year 35 Cash, Cash Equivalents, Restricted Cash and Restricted Cash Equivalents at the Beginning of the Year 36 Cash, Cash Equivalents, Restricted Cash and Restricted Cash Equivalents at the End of the Year Restricted Cash and Restricted Cash Equivalents not included in Cash and Cash Equivalents on Balance 37 Sheet 38 Cash and cash equivalents 39 (4,704) 6,973 (5,542) 429 (2,054) 978 (56) (3,976) 17,789) 14,977 (1,263) 1,362 (1,548) 248 (60) 5,927 23,009 (24,850) 1,012 (1,103) (6,845) (227) (9,004) (72) 27,605 (30,600) 1,476 (1,912) (6,644) (272) (10,347) (262) (2,581) 2,945 9,318 6,737 257 6,373 9,318 241 $ 6,480 $ 9,077 D H AAP's New Stores Project Amounts in Millions Year 0 2. 3 5 Revenues 1,200,000,000 Costs Depreciation Tax Rate Capital Expenditures NWC Chg NWC Recover NWC FCF (millions) Cost of Capital NPV- IRRE (For the IRR include all cash flows in your excel calculation.)