Forecast Free Cash Flow (NOPAT adjusted for depreciation, asset purchases and sales, and working capital changes) for the next 5 years and your terminal FCF for Year 6. Describe any assumptions used.
please provide your answer in an excel table and show calculations.
Historical Financial Statements Projected Financial Statements Fiscal Year Ending December 31, (S in millions) Fiscal Year Ending December 31, 2014A 2015A 2016A 2017A 2018A 2019P 2020P 2021P 2022P 2023P Balance Sheet Assets Cash and Cash Equivalents Short-term Investments $11,230.8 $16,252.5 $7,669.2 $8,958.0 $9,052.0 Total Cash, Cash Equivalents and short $18,010.0 $3,665.0 $4,466.0 $3,100.0 $3,745.0 $32,986.0 $20,727.9 $8,000.0 $28,727.9 $5,013.0 $3,635.3 $2,715.3 $6,746 2 $30,634.0 $36,499.9 $41,758.0 $46,837.7 $51776.3 $56,607.1 $6,006.0 $9,352.0 $15,358.0 $5,317.0 $3,667.0 $2,655.0 $9,548.0 $33,395.0 $34,010.0 $36,545.0 $7,309.0 $8,555.0 $9,595.0 $8,926.0 $25,380.7 $8,000.0 $33,380.7 $5,013.0 $3,798.9 $2,837.5 $6,746.2 $29.912.8 $8,322.0 $15,631.0 $18,150.0 $4,269.0 $3,941.0 $2,902.0 $8,000.0 $37,912.8 $5,013.0 $3,969.8 $2,965 2 $6,746 2 $2,025.0 $7,609 2 $10,951.0 $18,900.0 $23,921.7 $5,013.0 $3,396.0 $2,766.0 S8,508.0 $4,051.0 Marketable Securibes $5,013.0 $3,371.5 $2,469.2 $6.746.2 $5,013.0 $3,478.8 $2,598.4 $6,746.2 Accounts receivable $3,858.0 $2,675.0 $5.278.0 Inventory Other Current Assets Total Current Assets $6,652.0 $19,407 0 $15,757 6 $15,7576 $15,7576 $15,757 6 $15,757 6 Equity Method Investments Other Investments $9,9470 $3,6780 $4,407.0 $12,318 0 $16,2600 $20,856 0 $1,096.0 $4,230.0 $330 0 $8,203.0 $6,729.0 $138.0 $3,470 0 $909 0 $867 0 $4,139 0 $2,020.0 $4,246.2 $2,0200 $4,246 2 $2,0200 $4,246 2 $2,0200 $4,246 2 $2,020 0 $4,2462 Other Assets Deferred Income Tax Assets $4,207.0 $4,248.0 $2,667.0 $8,232.0 $6,682.0 $14,633.0 $6,533.0 $6,689.0 $12,100.0 $1,050.0 $92,023.0 $12,571.0 $10,635.0 $5,989.0 $6,000.0 $11,289.0 $10,629.0 $5,566.0 $6,406.0 $3,310.8 $10,263.0 $10,736.4 $10,736.4 $3,180 2 $6,406.0 $3,310.8 ($902.8) $6,406.0 $3,310.8 $10,736.4 $10,736.4 $10,736.4 $1,035 4 $6,406.0 $3,310.8 ($2,664.5) $6,406.0 $3,310.8 PP&E Not Tradomarks w Indef Lives Bottlers' Franchise Rights w Indef Lives $6,097.0 $3,676.0 $51.0 Goodwill $9,401.0 Other Intangible Assets $854.0 $726.0 $368.0 $274.0 $83,216.0 $84,542.9 $87.415.3 $93,350.5 $96.419.6 $90,093.0 $87,270.0 $87,896.0 $90,350.1 Total Assets Liabilities Accounts Payable and Accrued Expenses Loans and Notes Payable Current Maturities of Long-term Debt Accrued Income Taxes $9,234.0 $19,130 0 $3,552 0 $400.0 $58 0 $9,660 0 $13.129 0 $2,6770 $331.0 $1,1330 $9,490 0 $12,498 0 $3,527 0 $8.748.0 $13,205 0 $3,298 0 $8,9407 $13,194.0 $13,006.5 $13,006.5 $13,006.5 $13,006.5 $13,006.5 $3,461.1 $8,9320 $8,187 3 $8,555.7 $9.343.0 $9,763.5 $4,997 0 $378 0 $1,722 0 $1,268 86 $29,223.0 $25,903.5 $3,0757 $365 2 $3,268 4 $3652 $1,326 0 $3,653 8 $365 2 $3,846 5 $365 2 $1,513.1 $307 0 $410 0 $3652 $710 0 $1,533 0 $1,385 6 Liabilities Held for Sale $1,448 0 Total Current Liabilities $32,374.0 $26,9300 $26,532.0 $27,194.0 $26,521.7 $27,159.1 $27,816.5 $28,494.8 $25,364.0 $22,135.0 $23,521.8 $24,908.6 $26,295.4 Long-Term Debt $19,083.0 $28,407.0 $29,684.0 $31,182.0 $27,682.2 $5,688.0 $5,688.0 Other Liabilities $4,389.0 $5,636.0 $61,462.0 $4,301.0 $4,691.0 $64,329.0 $64,050.0 $4,081.0 $8,021.0 $7,638.0 $1,933 0 $64,158.0 $57,431.5 $59,436.5 $61,460.7 $63,504.9 $5,686.0 $3,707.0 $5,688.0 $3,707.0 $5,688.0 $2,522 0 Doforred Incomo Tax Liabilitios $3,753.0 $3,707.0 $3,707.0 $3,707.0 $65,570.0 Total Liabilities $68,919.0 Shareholders Equity $1,760 0 $14,016 0 $65,018 0 $1,7600 $1,760.0 $16,520.0 $17,346 0 $18,213.3 $19,124.0 $20,080 2 $21,084.2 $63,234.0 $63,518.4 $63,518.4 $1,760 0 $1,760.0 Common Stock $1,760 0 $13,154 0 $63,408 0 $1,760 0 $14,993 0 $65,502 0 $1,760 0 $1,760 0 $1,760.0 Capital Surplus Reinvested Eamings Accumulated Other Comprehonshve Income ($5,777.0) ($10,174.0) ($11,205.0) ($10,305.0) ($12,814.0) ($10,055.0) ($10,055.0) ($10,055.0) ($10,055.0) ($10,055.0) Treasury Stock $15.864 0 $60,430 0 $63,518.4 $63,518.4 $63,518.4 ($42,225.0) ($45,066.0) (S47,988.0) ($50,677.0) ($51,719.0) (S47,535.0) ($47,535.0) ($47,535.0) ($47,535.0) ($47,535.0) $17,072.0 $16,981.0 $25,034.4 $25,901.7 $1,905.0 $18,977.0 Equity Atributable to Shareowners of the C $30,320.0 Equity Altributable to Non-Controling Intere Total Shareholders Equity $25,554.0 $23,062.0 $210.0 $158.0 $25,764.0 $23,220.0 $26,812.4 $27,768.6 $28,772.6 $2,077.0 $241.0 $30,561.0 $2,077.0 $19,058.0 $27,111.4 $27,978.7 $28,889.4 $29,845.6 $30,849.6 $2,077.0 $2,077.0 $2,077.0 $2,077.0 $83,216.0 $84,542 9 $87,415.2 $90,350.1 $93,350.5 $96.419.6 S0.0 Total Liabilities and Equity $90,093.0 $87,270 0 $92,023 0 S0.0 $87 896 0 Check $0.0 $00 $00 $0.0 (S0.0) $S0.0 $0.0 S0.0 Projected Financial Statements Fiscal Year Ending December 31, Historical Financial Statements Fiscal Year Ending December 31, (S in milions) 2017A 2018A 2014A 2015A 2016A 2019P 2020P 2021P 2022P 2023P Cash Flow Cash Flow from Operations $1,182.0 Consolidated Net Income $7,124.0 $7,366 0 $1,970.0 $236 0 $6,550.0 $1,787.0 $258 0 ($856 0) ($449 0) $158 0 $1,146.0 $647 0 ($224 0) ($225 0 $6,727 0 $5,740.6 $5,999.6 $6,266 7 $6,546 5 ($134.6) $229 $6,839.7 ($397 2) $229 Depreciation and Amortization Stock-based Compensation Expense Deferred Income Taxes Equity (Income) Loss-Net of Dividends Foreign Currency Adjustments Significant (Gains) Losses on Sales of Ass Other Operating Charges Other itams Not Chango in Oporating Assats and Liabil $1,976.0 $1,260.0 $1,086.0 $829.7 $474.0 $154.3 $209 0 ($40 0) ($3710) $415 0 $219.0 $225 0 ($450 0) ($457 0) ($38 0) $189.0 $558 0 $229 $229 $229 ($1,256 0) ($628 0) $281 0 $1,459.0 $1,218.0 ($269 0) $3.464.0 $73.0 ($405 4) $135 8 ($122 0) ($137 0) ($374 0) $929.0 ($405 4) $135 8 $6502 $822 6 ($405 4) $135 ($405 4) $135 8 $650 2 $822 6 ($405 4) $135 8 $831 0 $761 0 $650 2 $650 2 $822 6 $650 2 sa22.6 $822 6 $149 0 $744.0 $682 0 $216 4 $216.4 $216 4 $216.4 $216 4 (S439.0) ($157 0) $10,528.0 ($1,202.0) $288 2 $288 2 $288 2 $288 2 $288 2 Cash Flow from Operations $10,615.0 $8,792 0 $6.930 0 $7,320 0 $8,507 4 $8,410.9 $8,358 2 $8,349.1 $8,379 7 Cash Flow from Investing Purchases of Investments Proceeds from Disposals of Investments Acquisitions of Businesses, Equity Method ($17,800.0) ($15,831.0) ($16,626.0) ($17,296.0) ($7,789.0) ($15,068.4) ($15,068.4) ($15,068.4) ($15,068.4) ( $15,068.4) $15,315.6 $15,315.6 $12,986.0 ($389.0) Proceeds from Disposals of Businesses, E $148.0 Purchases of Property, Plant and Equipme ($2,406.0) Proceeds from Disposals of Property, Plan $223.0 ($268.0) Cash Flow from Investing ($7,508.0) $14,079.0 $17,842.0 $16,694.0 ($838 0) $1,035.0 $15,315.6 ($3,809.0) ($1,040.0) ($1,713.4) ($1,713.4) ($1,713.4) ($1,713.4) ($1,713.4) $1,386.2 $14,977.0 $15,315.6 $15,315.6 ($2,491.0) $565.0 ($2,553.0) ($2,262.0) ($1,675.0) ($1,347.0) $1,674.9 $85.0 ($40.0) $1,386.2 $3,821.0 $1,302.0 $1,386.2 $1,386.2 $1,386.2 $1,750.3 $1,829.1 $1,911.4 $1,997.4 $150.0 ($305.0) (S6,186.0) ($1,004.0) ($2,254.0) $6,348.0 $104.0 $245.0 $161.4 ($153 2) $1,603.1 $161.4 (S153.2) $1,678.5 $161.4 ($153 2) $1,757.3 $161.4 ($1532) $1,839.6 $161.4 Other Investing Activities ($153 2) $1,925.6 (893.0) ($60.0) Cash Flows from Financing Issuances of Debt Payments of Debt Issuances of Stock Purchases of stock for Treasury $40,434.0 $27,281.0 $29,8570 $27,339 0 $33,317.0 $33,317.0 $33,317.0 $33,317.0 $33,3170 $41,6740 ($36,962 0) ($37,738.0) ($25,615.0) ($28,768.0) ($30,568.0) (31,930 2) ( $31,930.2) ($31,930.2) ($31,930.2) ($31,930 2) $1,456 4 ($3,564 0) ($3,681.0) ($3,682 0) ($1,912.0) ($3,400 2) ($3,400.2) ($3,400 2) ($3,400 2) ($3,400 2) $1,532.0 $1,245 0 $1,434 0 $1,595 0 $1,476.0 $1,456.4 $1,456 4 $1,456 4 $1,456 4 ($4,162 0) ($5,741.0) (S6,043.0) ($6,320.0) ($6,644.0) (S6,019.6) ($6,019.6) (S6,019.6) ($6,019.6) ($6,019.6) (S6,350.0 Dividends Other Financing Activities ($363.0) Cash Flow from Financing ($3,631.0) $251.0 $79.0 ($91.0) ($243.0) ($73.4) ($73.4) ($/3.4) (S73.4) ($/3.4) ($5,113.0) ($6,545.0) ($7,409.0) ($10,552 0) ($6,650 0) ($6,650.0) ($6,650 0) ($6,650 0) ($6,650 0) Net Cash Flow Changes on Cash and Cash Equivalents Other Changes in Cash Flow (5522 0) ($771 0) ($878 0) $1,2430 ($5.0) $80 ($2,733 0) $241.0 ($57 0) $3,116.0 ($262.0) $66.0 $3,460.6 ($864 2) ($291.6) $3,439.4 $1,204.0 $378.3 $3,465.5 $704 0 $3,538 7 $698 1 $3,655.3 $611.6 $265 2 ($934 0) $305 9 $416.0 Beginning Cash Position Change in Cash Position $10,4140 $8,958.0 $7,309.0 $8,555 0 $6,006.0 $8,926.0 $11,230.8 $16,252.5 $20,727.9 $25,380.7 ($1,649.0) $1,246.0 $5,021.7 ($1,458.0) $8,958 0 ($2,549.0) $6,006 0 $2,920.0 $2,304.8 $4.475.4 $4,652.8 $4,532.1 $11,230 8 $16,252.5 $20,727.9 $25,380.7 $29.912.8 Ending Cash Position $7,309 0 $8,555 0 $8,926.0 Projected Financial Statements Historical Financial Statements Fiscal Year Ending December 31, (S in millions) Fiscal Year Ending December 31, 2018A 2023P 2014A 2015A 2016A 2017A 2019P 2020P 2021P 2022P Income Statement Revenue $45,998.0 -1.8 % $35,410.0 -15 4 % $31.856.0 $33,289.5 $34,787.5 4.5% $36,353.0 $37,988.9 45% $39,698.4 4.5% $44,294.0 $41,863.0 -10.0 % -5 5% % Growth 37% 45% 4.5% Cost of Goods Sold $17.889.0 $17,482.0 39.5 % $26,812.0 $25,398.0 $16,465.0 $13,255.0 $11.770.0 $12,787.8 $13,363.3 $13,964.6 $14,593.0 $15,249.7 38.4 % $20,088.0 $20,501.7 63.1 % 38.9 % $28,109.0 39.3% 36.9% 38.4% $22,388 3 $23,395 8 $24,4486 COGS as % of Revenue- 37.4 % 38.4% 38.4 % 38.4% Gross Profit $22,155.0 $21,424.3 60.5 % 61.6 % Gross Profit Margin % 61.1% 60.7% 62.6% 61.6% 61.6% 61.6% 61.6% $154.3 $1,976.0 $1.970.0 $1,787.0 $15,370.0 36 7% $1,260.0 $1,086.0 $10,307.0 $11,939.2 $829.7 $474.0 ($134.6) ($397.2) $14,237.7 35 9% Depreciation & Amortization SG&A Expenses SG&A as % of Revenue $12,476.5 35.9% $17,218.0 $16,427.0 $12,654.0 $13,037.9 $13,624.6 359% 37.4 % $1,183.0 2.6 % $7,732.0 16 8 % $9,708.0 21.15 35.9% 37.1% 35.7% 32.4% 35 9% Other Operating Epenses Other Operating Expenses as % of Re Operating Income / EBIT Operating Income /EBIT Margin % EBITDA EBITDA Margin % EBITDA Growth $1,657.0 $1,371.0 $1,902.0 5.4 % $6,339.0 17.9% $1,079.0 3.4 % $7,614.0 23.9% $1,221.5 3.7% $1,276.4 $1,333.9 3.7 % $7,862 2 21.6% $1,393.9 3.7% $1,456.6 3.7 % $9,151.4 23.1% 3.3 % $6,870 0 16 4% 3.7% 3.7% $6,758 0 15.3% $6.511.3 $7,197.3 20.7% $8.511.9 19.6 % $7,341.0 22 1% 22.4% $8,657.0 20.7% $7,599.0 21.5 % -12 2 % $8,700.0 27.3% $8,377.3 22.1% $8,728.0 $7,671.3 22.1 % 45% $8,016.5 $8,754.3 22.1 % 19.7 % -10 1 % 22.1 % 4.5 % -0.8 % 14 5% -15.6% 4.5 % 4.5% na Interest Expense Interest Income $483.0 $856.0 $733.0 $841.0 $919.0 $655.1 $676.5 $703.4 $730 2 $757.1 $594.0 $613.0 ($243.0) $1,120.0 $9,605.0 $677.0 $730 8 $782.8 $810.2 S642.0 ($91.0) ($430.0) $8,136.0 $1,586.0 $682.0 ($237.0) ($113.0) $8,350.0 $1,623.0 $706.1 $51.0 $756.4 $53.0 ($137.0) $7,932.5 Net Interest Expense Other Income /(Expense) Pretax Income Taxes $111.0 ($494.0) $9,325.0 ($164.0) ($693.0) $6,742.0 $54.2 $526 ($143.2) $8,286.7 $53.1 ($149.6) $8,657.8 ($125.5) $7,266.5 ($131.1) $7,594.5 $1,594.8 $2,201.0 $2,239.0 $5,560.0 $1,526.0 $1,665.8 $1,740.2 $1,818.1 23.6 % 194 % $6,727 0 $42.0 $6,685.0 21.0% 21.0 % $5,999 6 $28.2 $5,971.4 21.0 % $6,266.7 $28.2 $6,238.5 17.2% 4.5 % 23.3% 82.5% 21.0% Tax Rate 19 5% 21.0% 21.0% Consolidated Net Income less: Net income attributable to noncontrolin Net Income Net Margin % Net Income Growth (1) Excludes depreciation and amortization $5,740 6 $28.2 $5,712.4 $7,124.0 $26.0 $7,098.0 15.4 % $7,366.0 $15.0 $7,351.0 16.6 % $6,550 0 $23.0 $6,527.0 15.6 % $1,182.0 $35.0 $1,147.0 $6,546.5 $28.2 $6,518.3 $6,839.7 $28 2 $6,8115 17.2% 3.2% 17.2% 17.2% 17.2% 4.5% 4.5 % 3.6% -11.2% -82 4 % 482.8% -14.5% 4.5% na Coca-Cola Co. Operating Profit Margin Dec 31, 2015 Dec 31, 2018 Dec 31, 2017 Dec 31, 2016 Selected Financial Data (US$ in millions) Operating income Net operating revenues 8,700 8,626 7,501 8,728 31,856 35,410 41,863 44,294 Profitability Ratio 21.18 % Operating profit margin 27.31% 20.61 % 19.70% Benchmarks Operating Profit Margin, Competitors 13.25 % 15.64% 16.54% 15.58% PepsiCo Inc. Operating Profit Margin, Sector 19.49% 18.20% 15.91% Beverages 17.59% Operating Profit Margin, Industry 12.78 % Based on:10-K (filing date: 2019-02-21),10-K (filing date: 2018-02-23),10-K (filing date: 20 7-02-24),10-K (filing date: 2016-02-25),10-K (filing date: 2015-02-25) 11.75 % 13.54 % 9.78% Consumer Goods Coca-Cola Co. Current Ratio Dec 31, 2015 Dec 31, 2018 Dec 31, 2017 Dec 31, 2016 Selected Financial Data (US$ in millions) Current assets 30,634 36,545 34,010 33,395 Current liabilities 29,223 27,194 26,532 26,930 Liquidity Ratio Current ratio 1.05 1.34 1.28 1.24 Benchmarks Current Ratio, Competitors PepsiCo Inc 0.99 1.51 1.28 1.31 Current Ratio, Sector 1.42 Beverages 1.02 1.28 1.27 Current Ratio, Industry Consumer Goods 0.98 1.10 1.09 1.17 Based on:10-K (filing date: 2019 02 21),10-K (filing date: 2018-02 23),10-K Ifiling date: 2017 02 24),10-K (filing date: 2016-02 25),10 KIfiling date: 2015-02-23) A B C D E G H 124 Figure 9-3 125 Coca-Cola's Forecast of Operations for the Selected Scenario (Millions of Dollars, Except for Per Share Data) 126 127 Panel A: Inputs 128 A1. Operations 129 Sales growth rate 130 (COGS excL depr.)/Sales 131 Depreciation/(Net PP&E 132 (Other op. exp.)/Sales 133 Cash/Sales 134 (Acc. rec.))/Sales 135 Inventory/Sales 136 (Net PP&E)/Sales 137 (Acc. pay./Sales 138 Accruals/Sales 139 Tax rate 140 Cost of strategic initiati 141 Panel B: Results 142 B1 143 Net sales 144 B2. Operating Assets and Operating Labties 145 Cash 146 Accounts receivable 147 Inventories 148 Net PP&E 149 Accounts payable 150 Accruals 151 83. Operating Income 152 COGS (excL depr.) 153 Depreciation 154 Other operating expenses 155 Cost of strategic initiatives 156 EBIT 157 Net operating profit after taxes 158 B4. Free Cash Flows 159 Net operating working capital 160 Total net operating capital 161 FCF NOPAT- A net op capital Status Quo Industry Actual MicroDrive MicroDrive Actual Forecast 2019 2021 2022 2018 2017 2018 2020 2023 2024 -10 % 5% 15% 4.5% 4.5% 4.5% 4.5% 4.5% 4.5% 37% 38.4 % 38.4% 76% 37% 38.4% 38.4% 38.4% 38.4% 9% 15% 13% 13.0% 13.0% 13.0% 13.0% 13.0% 13.0% 10% 41% 36% 36.0% 36.0% 36.0% 36.0% 36.0% 36.0% 1% 17% 28% 28.0% 28.0% 28.0% 28.0% 28.0% 28.0% 8% 10% 11% 11.0% 11.0% 11.0% 11.0% 11.0% 11.0% 7% 9% 9.0% 9.0% 9.0% 9.0% 9.0% 9.0% 15% 26% 26.0% 33% 23% 26.0% 26.0% 26.0% 26.0% 26.0% 8.0 % 4% 6% 8% 8.0% 8.0% 8.0% 8.0% 8.0% 16.0% 21.0% 16.0% 16.0% 16.0% 7% 15% 16% 16.0% 16.0% 21.0% 40% 83% 19% 21.0% 21.0% 21.0% 21.0% NA NA NA S0 $0 $0 S0 S0 0,0% Actual Forecast Revenue 2018 2021 2022 2019 2020 2023 2024 $31,856 $33,290 $34,788 $36,353 $37,989 $39,698 $41,485 S8,926 $9,321 S9,741 $10,179 $10,637 $11,116 $11,616 $3,999 S3,272 9,452 $3,396 $3,662 $3,827 $4,179 $4,367 $4,563 S3,131 S9,045 $2,766 S8,232 $2,996 S8,655 $3,419 $3,573 S3,734 $10,786 S9,877 $10,322 $2,498 $2,663 $2,783 $2,908 $3,039 $3,176 $3,319 S5,225 $5.816 $5,326 S5,566 $6,078 $6,352 $6,638 $11,770 $1,086 $12,783 $13,358 $13,960 $14,588 $15,244 $15,930 $1,125 $1,176 $1,229 $1,284 $1,342 $1,402 $13.087 $13.676 $14,291 $14,935 S11,386 $11.984 $12,524 NA $0 S0 $0 $7,614 $7,397 S7,730 S8,078 $8,441 $8,821 $9,218 S5,844 S6,969 S7,282 S6,134 $6,107 $6,381 $6,668 $9,117 $7,365 $7,989 S8,349 S8,725 $9,528 $9,956 $15,597 $16,645 $17,394 $18,176 $18,994 $19,849 $20,742 $3,521 $4,796 S5,358 $5,599 S5,851 $6,113.82 $5,534.20 Historical Financial Statements Projected Financial Statements Fiscal Year Ending December 31, (S in millions) Fiscal Year Ending December 31, 2014A 2015A 2016A 2017A 2018A 2019P 2020P 2021P 2022P 2023P Balance Sheet Assets Cash and Cash Equivalents Short-term Investments $11,230.8 $16,252.5 $7,669.2 $8,958.0 $9,052.0 Total Cash, Cash Equivalents and short $18,010.0 $3,665.0 $4,466.0 $3,100.0 $3,745.0 $32,986.0 $20,727.9 $8,000.0 $28,727.9 $5,013.0 $3,635.3 $2,715.3 $6,746 2 $30,634.0 $36,499.9 $41,758.0 $46,837.7 $51776.3 $56,607.1 $6,006.0 $9,352.0 $15,358.0 $5,317.0 $3,667.0 $2,655.0 $9,548.0 $33,395.0 $34,010.0 $36,545.0 $7,309.0 $8,555.0 $9,595.0 $8,926.0 $25,380.7 $8,000.0 $33,380.7 $5,013.0 $3,798.9 $2,837.5 $6,746.2 $29.912.8 $8,322.0 $15,631.0 $18,150.0 $4,269.0 $3,941.0 $2,902.0 $8,000.0 $37,912.8 $5,013.0 $3,969.8 $2,965 2 $6,746 2 $2,025.0 $7,609 2 $10,951.0 $18,900.0 $23,921.7 $5,013.0 $3,396.0 $2,766.0 S8,508.0 $4,051.0 Marketable Securibes $5,013.0 $3,371.5 $2,469.2 $6.746.2 $5,013.0 $3,478.8 $2,598.4 $6,746.2 Accounts receivable $3,858.0 $2,675.0 $5.278.0 Inventory Other Current Assets Total Current Assets $6,652.0 $19,407 0 $15,757 6 $15,7576 $15,7576 $15,757 6 $15,757 6 Equity Method Investments Other Investments $9,9470 $3,6780 $4,407.0 $12,318 0 $16,2600 $20,856 0 $1,096.0 $4,230.0 $330 0 $8,203.0 $6,729.0 $138.0 $3,470 0 $909 0 $867 0 $4,139 0 $2,020.0 $4,246.2 $2,0200 $4,246 2 $2,0200 $4,246 2 $2,0200 $4,246 2 $2,020 0 $4,2462 Other Assets Deferred Income Tax Assets $4,207.0 $4,248.0 $2,667.0 $8,232.0 $6,682.0 $14,633.0 $6,533.0 $6,689.0 $12,100.0 $1,050.0 $92,023.0 $12,571.0 $10,635.0 $5,989.0 $6,000.0 $11,289.0 $10,629.0 $5,566.0 $6,406.0 $3,310.8 $10,263.0 $10,736.4 $10,736.4 $3,180 2 $6,406.0 $3,310.8 ($902.8) $6,406.0 $3,310.8 $10,736.4 $10,736.4 $10,736.4 $1,035 4 $6,406.0 $3,310.8 ($2,664.5) $6,406.0 $3,310.8 PP&E Not Tradomarks w Indef Lives Bottlers' Franchise Rights w Indef Lives $6,097.0 $3,676.0 $51.0 Goodwill $9,401.0 Other Intangible Assets $854.0 $726.0 $368.0 $274.0 $83,216.0 $84,542.9 $87.415.3 $93,350.5 $96.419.6 $90,093.0 $87,270.0 $87,896.0 $90,350.1 Total Assets Liabilities Accounts Payable and Accrued Expenses Loans and Notes Payable Current Maturities of Long-term Debt Accrued Income Taxes $9,234.0 $19,130 0 $3,552 0 $400.0 $58 0 $9,660 0 $13.129 0 $2,6770 $331.0 $1,1330 $9,490 0 $12,498 0 $3,527 0 $8.748.0 $13,205 0 $3,298 0 $8,9407 $13,194.0 $13,006.5 $13,006.5 $13,006.5 $13,006.5 $13,006.5 $3,461.1 $8,9320 $8,187 3 $8,555.7 $9.343.0 $9,763.5 $4,997 0 $378 0 $1,722 0 $1,268 86 $29,223.0 $25,903.5 $3,0757 $365 2 $3,268 4 $3652 $1,326 0 $3,653 8 $365 2 $3,846 5 $365 2 $1,513.1 $307 0 $410 0 $3652 $710 0 $1,533 0 $1,385 6 Liabilities Held for Sale $1,448 0 Total Current Liabilities $32,374.0 $26,9300 $26,532.0 $27,194.0 $26,521.7 $27,159.1 $27,816.5 $28,494.8 $25,364.0 $22,135.0 $23,521.8 $24,908.6 $26,295.4 Long-Term Debt $19,083.0 $28,407.0 $29,684.0 $31,182.0 $27,682.2 $5,688.0 $5,688.0 Other Liabilities $4,389.0 $5,636.0 $61,462.0 $4,301.0 $4,691.0 $64,329.0 $64,050.0 $4,081.0 $8,021.0 $7,638.0 $1,933 0 $64,158.0 $57,431.5 $59,436.5 $61,460.7 $63,504.9 $5,686.0 $3,707.0 $5,688.0 $3,707.0 $5,688.0 $2,522 0 Doforred Incomo Tax Liabilitios $3,753.0 $3,707.0 $3,707.0 $3,707.0 $65,570.0 Total Liabilities $68,919.0 Shareholders Equity $1,760 0 $14,016 0 $65,018 0 $1,7600 $1,760.0 $16,520.0 $17,346 0 $18,213.3 $19,124.0 $20,080 2 $21,084.2 $63,234.0 $63,518.4 $63,518.4 $1,760 0 $1,760.0 Common Stock $1,760 0 $13,154 0 $63,408 0 $1,760 0 $14,993 0 $65,502 0 $1,760 0 $1,760 0 $1,760.0 Capital Surplus Reinvested Eamings Accumulated Other Comprehonshve Income ($5,777.0) ($10,174.0) ($11,205.0) ($10,305.0) ($12,814.0) ($10,055.0) ($10,055.0) ($10,055.0) ($10,055.0) ($10,055.0) Treasury Stock $15.864 0 $60,430 0 $63,518.4 $63,518.4 $63,518.4 ($42,225.0) ($45,066.0) (S47,988.0) ($50,677.0) ($51,719.0) (S47,535.0) ($47,535.0) ($47,535.0) ($47,535.0) ($47,535.0) $17,072.0 $16,981.0 $25,034.4 $25,901.7 $1,905.0 $18,977.0 Equity Atributable to Shareowners of the C $30,320.0 Equity Altributable to Non-Controling Intere Total Shareholders Equity $25,554.0 $23,062.0 $210.0 $158.0 $25,764.0 $23,220.0 $26,812.4 $27,768.6 $28,772.6 $2,077.0 $241.0 $30,561.0 $2,077.0 $19,058.0 $27,111.4 $27,978.7 $28,889.4 $29,845.6 $30,849.6 $2,077.0 $2,077.0 $2,077.0 $2,077.0 $83,216.0 $84,542 9 $87,415.2 $90,350.1 $93,350.5 $96.419.6 S0.0 Total Liabilities and Equity $90,093.0 $87,270 0 $92,023 0 S0.0 $87 896 0 Check $0.0 $00 $00 $0.0 (S0.0) $S0.0 $0.0 S0.0 Projected Financial Statements Fiscal Year Ending December 31, Historical Financial Statements Fiscal Year Ending December 31, (S in milions) 2017A 2018A 2014A 2015A 2016A 2019P 2020P 2021P 2022P 2023P Cash Flow Cash Flow from Operations $1,182.0 Consolidated Net Income $7,124.0 $7,366 0 $1,970.0 $236 0 $6,550.0 $1,787.0 $258 0 ($856 0) ($449 0) $158 0 $1,146.0 $647 0 ($224 0) ($225 0 $6,727 0 $5,740.6 $5,999.6 $6,266 7 $6,546 5 ($134.6) $229 $6,839.7 ($397 2) $229 Depreciation and Amortization Stock-based Compensation Expense Deferred Income Taxes Equity (Income) Loss-Net of Dividends Foreign Currency Adjustments Significant (Gains) Losses on Sales of Ass Other Operating Charges Other itams Not Chango in Oporating Assats and Liabil $1,976.0 $1,260.0 $1,086.0 $829.7 $474.0 $154.3 $209 0 ($40 0) ($3710) $415 0 $219.0 $225 0 ($450 0) ($457 0) ($38 0) $189.0 $558 0 $229 $229 $229 ($1,256 0) ($628 0) $281 0 $1,459.0 $1,218.0 ($269 0) $3.464.0 $73.0 ($405 4) $135 8 ($122 0) ($137 0) ($374 0) $929.0 ($405 4) $135 8 $6502 $822 6 ($405 4) $135 ($405 4) $135 8 $650 2 $822 6 ($405 4) $135 8 $831 0 $761 0 $650 2 $650 2 $822 6 $650 2 sa22.6 $822 6 $149 0 $744.0 $682 0 $216 4 $216.4 $216 4 $216.4 $216 4 (S439.0) ($157 0) $10,528.0 ($1,202.0) $288 2 $288 2 $288 2 $288 2 $288 2 Cash Flow from Operations $10,615.0 $8,792 0 $6.930 0 $7,320 0 $8,507 4 $8,410.9 $8,358 2 $8,349.1 $8,379 7 Cash Flow from Investing Purchases of Investments Proceeds from Disposals of Investments Acquisitions of Businesses, Equity Method ($17,800.0) ($15,831.0) ($16,626.0) ($17,296.0) ($7,789.0) ($15,068.4) ($15,068.4) ($15,068.4) ($15,068.4) ( $15,068.4) $15,315.6 $15,315.6 $12,986.0 ($389.0) Proceeds from Disposals of Businesses, E $148.0 Purchases of Property, Plant and Equipme ($2,406.0) Proceeds from Disposals of Property, Plan $223.0 ($268.0) Cash Flow from Investing ($7,508.0) $14,079.0 $17,842.0 $16,694.0 ($838 0) $1,035.0 $15,315.6 ($3,809.0) ($1,040.0) ($1,713.4) ($1,713.4) ($1,713.4) ($1,713.4) ($1,713.4) $1,386.2 $14,977.0 $15,315.6 $15,315.6 ($2,491.0) $565.0 ($2,553.0) ($2,262.0) ($1,675.0) ($1,347.0) $1,674.9 $85.0 ($40.0) $1,386.2 $3,821.0 $1,302.0 $1,386.2 $1,386.2 $1,386.2 $1,750.3 $1,829.1 $1,911.4 $1,997.4 $150.0 ($305.0) (S6,186.0) ($1,004.0) ($2,254.0) $6,348.0 $104.0 $245.0 $161.4 ($153 2) $1,603.1 $161.4 (S153.2) $1,678.5 $161.4 ($153 2) $1,757.3 $161.4 ($1532) $1,839.6 $161.4 Other Investing Activities ($153 2) $1,925.6 (893.0) ($60.0) Cash Flows from Financing Issuances of Debt Payments of Debt Issuances of Stock Purchases of stock for Treasury $40,434.0 $27,281.0 $29,8570 $27,339 0 $33,317.0 $33,317.0 $33,317.0 $33,317.0 $33,3170 $41,6740 ($36,962 0) ($37,738.0) ($25,615.0) ($28,768.0) ($30,568.0) (31,930 2) ( $31,930.2) ($31,930.2) ($31,930.2) ($31,930 2) $1,456 4 ($3,564 0) ($3,681.0) ($3,682 0) ($1,912.0) ($3,400 2) ($3,400.2) ($3,400 2) ($3,400 2) ($3,400 2) $1,532.0 $1,245 0 $1,434 0 $1,595 0 $1,476.0 $1,456.4 $1,456 4 $1,456 4 $1,456 4 ($4,162 0) ($5,741.0) (S6,043.0) ($6,320.0) ($6,644.0) (S6,019.6) ($6,019.6) (S6,019.6) ($6,019.6) ($6,019.6) (S6,350.0 Dividends Other Financing Activities ($363.0) Cash Flow from Financing ($3,631.0) $251.0 $79.0 ($91.0) ($243.0) ($73.4) ($73.4) ($/3.4) (S73.4) ($/3.4) ($5,113.0) ($6,545.0) ($7,409.0) ($10,552 0) ($6,650 0) ($6,650.0) ($6,650 0) ($6,650 0) ($6,650 0) Net Cash Flow Changes on Cash and Cash Equivalents Other Changes in Cash Flow (5522 0) ($771 0) ($878 0) $1,2430 ($5.0) $80 ($2,733 0) $241.0 ($57 0) $3,116.0 ($262.0) $66.0 $3,460.6 ($864 2) ($291.6) $3,439.4 $1,204.0 $378.3 $3,465.5 $704 0 $3,538 7 $698 1 $3,655.3 $611.6 $265 2 ($934 0) $305 9 $416.0 Beginning Cash Position Change in Cash Position $10,4140 $8,958.0 $7,309.0 $8,555 0 $6,006.0 $8,926.0 $11,230.8 $16,252.5 $20,727.9 $25,380.7 ($1,649.0) $1,246.0 $5,021.7 ($1,458.0) $8,958 0 ($2,549.0) $6,006 0 $2,920.0 $2,304.8 $4.475.4 $4,652.8 $4,532.1 $11,230 8 $16,252.5 $20,727.9 $25,380.7 $29.912.8 Ending Cash Position $7,309 0 $8,555 0 $8,926.0 Projected Financial Statements Historical Financial Statements Fiscal Year Ending December 31, (S in millions) Fiscal Year Ending December 31, 2018A 2023P 2014A 2015A 2016A 2017A 2019P 2020P 2021P 2022P Income Statement Revenue $45,998.0 -1.8 % $35,410.0 -15 4 % $31.856.0 $33,289.5 $34,787.5 4.5% $36,353.0 $37,988.9 45% $39,698.4 4.5% $44,294.0 $41,863.0 -10.0 % -5 5% % Growth 37% 45% 4.5% Cost of Goods Sold $17.889.0 $17,482.0 39.5 % $26,812.0 $25,398.0 $16,465.0 $13,255.0 $11.770.0 $12,787.8 $13,363.3 $13,964.6 $14,593.0 $15,249.7 38.4 % $20,088.0 $20,501.7 63.1 % 38.9 % $28,109.0 39.3% 36.9% 38.4% $22,388 3 $23,395 8 $24,4486 COGS as % of Revenue- 37.4 % 38.4% 38.4 % 38.4% Gross Profit $22,155.0 $21,424.3 60.5 % 61.6 % Gross Profit Margin % 61.1% 60.7% 62.6% 61.6% 61.6% 61.6% 61.6% $154.3 $1,976.0 $1.970.0 $1,787.0 $15,370.0 36 7% $1,260.0 $1,086.0 $10,307.0 $11,939.2 $829.7 $474.0 ($134.6) ($397.2) $14,237.7 35 9% Depreciation & Amortization SG&A Expenses SG&A as % of Revenue $12,476.5 35.9% $17,218.0 $16,427.0 $12,654.0 $13,037.9 $13,624.6 359% 37.4 % $1,183.0 2.6 % $7,732.0 16 8 % $9,708.0 21.15 35.9% 37.1% 35.7% 32.4% 35 9% Other Operating Epenses Other Operating Expenses as % of Re Operating Income / EBIT Operating Income /EBIT Margin % EBITDA EBITDA Margin % EBITDA Growth $1,657.0 $1,371.0 $1,902.0 5.4 % $6,339.0 17.9% $1,079.0 3.4 % $7,614.0 23.9% $1,221.5 3.7% $1,276.4 $1,333.9 3.7 % $7,862 2 21.6% $1,393.9 3.7% $1,456.6 3.7 % $9,151.4 23.1% 3.3 % $6,870 0 16 4% 3.7% 3.7% $6,758 0 15.3% $6.511.3 $7,197.3 20.7% $8.511.9 19.6 % $7,341.0 22 1% 22.4% $8,657.0 20.7% $7,599.0 21.5 % -12 2 % $8,700.0 27.3% $8,377.3 22.1% $8,728.0 $7,671.3 22.1 % 45% $8,016.5 $8,754.3 22.1 % 19.7 % -10 1 % 22.1 % 4.5 % -0.8 % 14 5% -15.6% 4.5 % 4.5% na Interest Expense Interest Income $483.0 $856.0 $733.0 $841.0 $919.0 $655.1 $676.5 $703.4 $730 2 $757.1 $594.0 $613.0 ($243.0) $1,120.0 $9,605.0 $677.0 $730 8 $782.8 $810.2 S642.0 ($91.0) ($430.0) $8,136.0 $1,586.0 $682.0 ($237.0) ($113.0) $8,350.0 $1,623.0 $706.1 $51.0 $756.4 $53.0 ($137.0) $7,932.5 Net Interest Expense Other Income /(Expense) Pretax Income Taxes $111.0 ($494.0) $9,325.0 ($164.0) ($693.0) $6,742.0 $54.2 $526 ($143.2) $8,286.7 $53.1 ($149.6) $8,657.8 ($125.5) $7,266.5 ($131.1) $7,594.5 $1,594.8 $2,201.0 $2,239.0 $5,560.0 $1,526.0 $1,665.8 $1,740.2 $1,818.1 23.6 % 194 % $6,727 0 $42.0 $6,685.0 21.0% 21.0 % $5,999 6 $28.2 $5,971.4 21.0 % $6,266.7 $28.2 $6,238.5 17.2% 4.5 % 23.3% 82.5% 21.0% Tax Rate 19 5% 21.0% 21.0% Consolidated Net Income less: Net income attributable to noncontrolin Net Income Net Margin % Net Income Growth (1) Excludes depreciation and amortization $5,740 6 $28.2 $5,712.4 $7,124.0 $26.0 $7,098.0 15.4 % $7,366.0 $15.0 $7,351.0 16.6 % $6,550 0 $23.0 $6,527.0 15.6 % $1,182.0 $35.0 $1,147.0 $6,546.5 $28.2 $6,518.3 $6,839.7 $28 2 $6,8115 17.2% 3.2% 17.2% 17.2% 17.2% 4.5% 4.5 % 3.6% -11.2% -82 4 % 482.8% -14.5% 4.5% na Coca-Cola Co. Operating Profit Margin Dec 31, 2015 Dec 31, 2018 Dec 31, 2017 Dec 31, 2016 Selected Financial Data (US$ in millions) Operating income Net operating revenues 8,700 8,626 7,501 8,728 31,856 35,410 41,863 44,294 Profitability Ratio 21.18 % Operating profit margin 27.31% 20.61 % 19.70% Benchmarks Operating Profit Margin, Competitors 13.25 % 15.64% 16.54% 15.58% PepsiCo Inc. Operating Profit Margin, Sector 19.49% 18.20% 15.91% Beverages 17.59% Operating Profit Margin, Industry 12.78 % Based on:10-K (filing date: 2019-02-21),10-K (filing date: 2018-02-23),10-K (filing date: 20 7-02-24),10-K (filing date: 2016-02-25),10-K (filing date: 2015-02-25) 11.75 % 13.54 % 9.78% Consumer Goods Coca-Cola Co. Current Ratio Dec 31, 2015 Dec 31, 2018 Dec 31, 2017 Dec 31, 2016 Selected Financial Data (US$ in millions) Current assets 30,634 36,545 34,010 33,395 Current liabilities 29,223 27,194 26,532 26,930 Liquidity Ratio Current ratio 1.05 1.34 1.28 1.24 Benchmarks Current Ratio, Competitors PepsiCo Inc 0.99 1.51 1.28 1.31 Current Ratio, Sector 1.42 Beverages 1.02 1.28 1.27 Current Ratio, Industry Consumer Goods 0.98 1.10 1.09 1.17 Based on:10-K (filing date: 2019 02 21),10-K (filing date: 2018-02 23),10-K Ifiling date: 2017 02 24),10-K (filing date: 2016-02 25),10 KIfiling date: 2015-02-23) A B C D E G H 124 Figure 9-3 125 Coca-Cola's Forecast of Operations for the Selected Scenario (Millions of Dollars, Except for Per Share Data) 126 127 Panel A: Inputs 128 A1. Operations 129 Sales growth rate 130 (COGS excL depr.)/Sales 131 Depreciation/(Net PP&E 132 (Other op. exp.)/Sales 133 Cash/Sales 134 (Acc. rec.))/Sales 135 Inventory/Sales 136 (Net PP&E)/Sales 137 (Acc. pay./Sales 138 Accruals/Sales 139 Tax rate 140 Cost of strategic initiati 141 Panel B: Results 142 B1 143 Net sales 144 B2. Operating Assets and Operating Labties 145 Cash 146 Accounts receivable 147 Inventories 148 Net PP&E 149 Accounts payable 150 Accruals 151 83. Operating Income 152 COGS (excL depr.) 153 Depreciation 154 Other operating expenses 155 Cost of strategic initiatives 156 EBIT 157 Net operating profit after taxes 158 B4. Free Cash Flows 159 Net operating working capital 160 Total net operating capital 161 FCF NOPAT- A net op capital Status Quo Industry Actual MicroDrive MicroDrive Actual Forecast 2019 2021 2022 2018 2017 2018 2020 2023 2024 -10 % 5% 15% 4.5% 4.5% 4.5% 4.5% 4.5% 4.5% 37% 38.4 % 38.4% 76% 37% 38.4% 38.4% 38.4% 38.4% 9% 15% 13% 13.0% 13.0% 13.0% 13.0% 13.0% 13.0% 10% 41% 36% 36.0% 36.0% 36.0% 36.0% 36.0% 36.0% 1% 17% 28% 28.0% 28.0% 28.0% 28.0% 28.0% 28.0% 8% 10% 11% 11.0% 11.0% 11.0% 11.0% 11.0% 11.0% 7% 9% 9.0% 9.0% 9.0% 9.0% 9.0% 9.0% 15% 26% 26.0% 33% 23% 26.0% 26.0% 26.0% 26.0% 26.0% 8.0 % 4% 6% 8% 8.0% 8.0% 8.0% 8.0% 8.0% 16.0% 21.0% 16.0% 16.0% 16.0% 7% 15% 16% 16.0% 16.0% 21.0% 40% 83% 19% 21.0% 21.0% 21.0% 21.0% NA NA NA S0 $0 $0 S0 S0 0,0% Actual Forecast Revenue 2018 2021 2022 2019 2020 2023 2024 $31,856 $33,290 $34,788 $36,353 $37,989 $39,698 $41,485 S8,926 $9,321 S9,741 $10,179 $10,637 $11,116 $11,616 $3,999 S3,272 9,452 $3,396 $3,662 $3,827 $4,179 $4,367 $4,563 S3,131 S9,045 $2,766 S8,232 $2,996 S8,655 $3,419 $3,573 S3,734 $10,786 S9,877 $10,322 $2,498 $2,663 $2,783 $2,908 $3,039 $3,176 $3,319 S5,225 $5.816 $5,326 S5,566 $6,078 $6,352 $6,638 $11,770 $1,086 $12,783 $13,358 $13,960 $14,588 $15,244 $15,930 $1,125 $1,176 $1,229 $1,284 $1,342 $1,402 $13.087 $13.676 $14,291 $14,935 S11,386 $11.984 $12,524 NA $0 S0 $0 $7,614 $7,397 S7,730 S8,078 $8,441 $8,821 $9,218 S5,844 S6,969 S7,282 S6,134 $6,107 $6,381 $6,668 $9,117 $7,365 $7,989 S8,349 S8,725 $9,528 $9,956 $15,597 $16,645 $17,394 $18,176 $18,994 $19,849 $20,742 $3,521 $4,796 S5,358 $5,599 S5,851 $6,113.82 $5,534.20
