Finance
Yinkus811
fproject.xlsxPROJECT_COVERPAGE
| Project: Intrinsic value evaluation of Coca Cola Company | |||
| Stock ticker: | KO | ||
| Group Members: 5 | |||
| Leonard | |||
| Temi | |||
| Jennifer | |||
| Andrew | |||
| Elizabeth | |||
| Intrinsic Value: Stock Price, company older than 30 years, based on financial statements | |||
| Evaluation of the intrinsic value of the company and 5 years in the future. | |||
| Use discounted cash flow method | |||
| VALUE GIVEN IN DOLLARS PER SHARE |
1_Financials-Inputs
| Misc. Data In Millions of USD (except for per share items) | |||||||||||||||||||||||||||
| Yahoo Finance KO Market Cap | $187,062 | ||||||||||||||||||||||||||
| Effective tax rate for 2018 (KO 2017 Annual Report): | 21% | ||||||||||||||||||||||||||
| 4 year average tax rate: | 23.60% | ||||||||||||||||||||||||||
| Google Finance KO Market Cap | $ 190,960.00 | ||||||||||||||||||||||||||
| Year-over-year growth rates | |||||||||||||||||||||||||||
| Income statement | -15.41% | -5.49% | -3.70% | -1.83% | -2.42% | ||||||||||||||||||||||
| In Millions of USD (except for per share items) | 2017 | 2016 | 2015 | 2014 | 2013 | 2012 | |||||||||||||||||||||
| Revenue | $ 35,410.00 | $ 41,863.00 | $ 44,294.00 | $ 45,998.00 | $46,854 | $48,017 | |||||||||||||||||||||
| Other Revenue, Total | - | - | - | - | |||||||||||||||||||||||
| Total Revenue | $ 35,410.00 | $ 41,863.00 | $ 44,294.00 | $ 45,998.00 | $46,854 | $48,017 | |||||||||||||||||||||
| Cost of Revenue, Total | $ 13,256.00 | $ 16,465.00 | $ 17,482.00 | $ 17,889.00 | $ 18,421.00 | $ 19,053.00 | |||||||||||||||||||||
| Gross Profit | $ 22,154.00 | $ 25,398.00 | $ 26,812.00 | $ 28,109.00 | $ 28,433.00 | $ 28,964.00 | |||||||||||||||||||||
| Selling/General/Admin. Expenses, Total | $ 12,449.00 | $ 15,272.00 | $ 16,451.00 | $ 17,273.00 | $ 17,310.00 | $ 17,738.00 | |||||||||||||||||||||
| Research & Development | - | - | - | - | |||||||||||||||||||||||
| Depreciation/Amortization | - | - | - | - | |||||||||||||||||||||||
| Interest Expense(Income) - Net Operating | - | - | - | - | |||||||||||||||||||||||
| Unusual Expense (Income) | $ (620.00) | $ 2,305.00 | $ 2,507.00 | $ 2,048.00 | |||||||||||||||||||||||
| Other Operating Expenses, Total | $ 231.00 | $ 462.00 | $ 156.00 | $ 338.00 | $ 895.00 | $ 447.00 | |||||||||||||||||||||
| Total Operating Expense | $ 25,363.00 | $ 34,494.00 | $ 36,572.00 | $ 37,493.00 | $ 36,626.00 | $ 37,238.00 | |||||||||||||||||||||
| Operating Income | $ 10,047.00 | $ 7,369.00 | $ 7,722.00 | $ 8,505.00 | $ 10,228.00 | $ 10,779.00 | |||||||||||||||||||||
| Interest Income(Expense), Net Non-Operating | - | - | - | - | |||||||||||||||||||||||
| Gain (Loss) on Sale of Assets | - | - | - | - | |||||||||||||||||||||||
| Other, Net | $ (4,447.00) | $ 306.00 | $ (148.00) | $ 1,016.00 | |||||||||||||||||||||||
| Income Before Tax | $ 6,742.00 | $ 8,136.00 | $ 9,605.00 | $ 9,325.00 | $ 11,477.00 | $ 11,809.00 | |||||||||||||||||||||
| Income After Tax | $ 4,792.00 | $ 6,550.00 | $ 7,366.00 | $ 7,124.00 | $ 8,626.00 | $ 9,086.00 | |||||||||||||||||||||
| Minority Interest | $ (35.00) | $ (23.00) | $ (15.00) | $ (26.00) | $ (42.00) | $ (67.00) | |||||||||||||||||||||
| Equity In Affiliates | - | - | - | - | |||||||||||||||||||||||
| Net Income Before Extra. Items | $ 4,757.00 | $ 6,527.00 | $ 7,351.00 | $ 7,098.00 | $8,584 | $9,019 | |||||||||||||||||||||
| Accounting Change | - | - | - | - | |||||||||||||||||||||||
| Discontinued Operations | - | - | - | - | |||||||||||||||||||||||
| Extraordinary Item | - | - | - | - | |||||||||||||||||||||||
| Net Income | $ 1,248.00 | $ 6,527.00 | $ 7,351.00 | $ 7,098.00 | $8,584 | $9,019 | |||||||||||||||||||||
| Preferred Dividends | - | - | - | - | $ - 0 | $ - 0 | |||||||||||||||||||||
| Income Available to Common Excl. Extra Items | $ 4,757.00 | $ 6,527.00 | $ 7,351.00 | $ 7,098.00 | $ 8,584.00 | $ 9,019.00 | |||||||||||||||||||||
| Income Available to Common Incl. Extra Items | $ 1,248.00 | $ 6,527.00 | $ 7,351.00 | $ 7,098.00 | $ 8,584.00 | $ 9,019.00 | |||||||||||||||||||||
| Basic Weighted Average Shares | - | - | - | - | |||||||||||||||||||||||
| Basic EPS Excluding Extraordinary Items | - | - | - | - | |||||||||||||||||||||||
| Basic EPS Including Extraordinary Items | - | - | - | - | |||||||||||||||||||||||
| Dilution Adjustment | - | - | - | - | |||||||||||||||||||||||
| Diluted Weighted Average Shares | $ 4,324.00 | $ 4,367.00 | $ 4,405.00 | $ 4,450.00 | $ 4,509.00 | $ 4,584.00 | |||||||||||||||||||||
| Diluted EPS Excluding Extraordinary Items | $ 1.10 | $ 1.49 | $ 1.67 | $ 1.60 | $1.94 | $2.00 | |||||||||||||||||||||
| Diluted EPS Including Extraordinary Items | - | - | - | - | |||||||||||||||||||||||
| Dividends per Share - Common Stock Primary Issue | $ 1.46 | $ 1.38 | $ 1.29 | $ 1.20 | |||||||||||||||||||||||
| Gross Dividends - Common Stock | - | - | - | - | |||||||||||||||||||||||
| Net Income after Stock Based Comp. Expense | - | - | - | - | |||||||||||||||||||||||
| Basic EPS after Stock Based Comp. Expense | - | - | - | - | |||||||||||||||||||||||
| Diluted EPS after Stock Based Comp. Expense | - | - | - | - | |||||||||||||||||||||||
| Depreciation, Supplemental | - | - | - | - | |||||||||||||||||||||||
| Total Special Items | - | - | - | - | |||||||||||||||||||||||
| Normalized Income Before Taxes | - | - | - | - | |||||||||||||||||||||||
| Effect of Special Items on Income Taxes | - | - | - | - | |||||||||||||||||||||||
| Income Taxes Ex. Impact of Special Items | - | - | - | - | |||||||||||||||||||||||
| Normalized Income After Taxes | - | - | - | - | |||||||||||||||||||||||
| Normalized Income Avail to Common | - | - | - | - | |||||||||||||||||||||||
| Basic Normalized EPS | - | - | - | - | |||||||||||||||||||||||
| Diluted Normalized EPS | $ 1.00 | $ 1.92 | $ 2.11 | $ 1.95 | $1.90 | $1.97 | |||||||||||||||||||||
| Balance sheet | |||||||||||||||||||||||||||
| In Millions of USD (except for per share items) | 2017 | 2016 | 2015 | 2014 | 2013 | 2012 | |||||||||||||||||||||
| Cash & Equivalents | $ 6,006.00 | $ 8,555.00 | $ 7,309.00 | $ 8,958.00 | $10,414 | $8,442 | |||||||||||||||||||||
| Short Term Investments | $ 14,722.00 | $ 13,961.00 | $ 12,715.00 | $ 12,784.00 | $ 6,707.00 | $ 5,017.00 | |||||||||||||||||||||
| Cash and Short Term Investments | $ 20,728.00 | $ 22,516.00 | $ 20,024.00 | $ 21,742.00 | $17,121 | $13,459 | |||||||||||||||||||||
| Accounts Receivable - Trade, Net | $ 3,667.00 | $ 3,856.00 | $ 3,941.00 | $ 4,466.00 | $ 4,873.00 | $ 4,759.00 | |||||||||||||||||||||
| Receivables - Other | - | - | - | $ - 0 | $ - 0 | $ - 0 | |||||||||||||||||||||
| Total Receivables, Net | $ 3,667.00 | $ 3,856.00 | $ 3,941.00 | $ 4,466.00 | $ 4,873.00 | $ 4,759.00 | |||||||||||||||||||||
| Total Inventory | $ 2,655.00 | $ 2,675.00 | $ 2,902.00 | $ 3,100.00 | $ 3,277.00 | $ 3,264.00 | |||||||||||||||||||||
| Prepaid Expenses | $ 1,902.00 | $ 1,766.00 | $ 2,035.00 | $ 2,062.00 | |||||||||||||||||||||||
| Other Current Assets, Total | $ 7,593.00 | $ 3,197.00 | $ 4,493.00 | $ 1,616.00 | $ 2,886.00 | $ 2,781.00 | |||||||||||||||||||||
| Total Current Assets | $ 36,545.00 | $ 34,010.00 | $ 33,395.00 | $ 32,986.00 | $ 31,304.00 | $ 30,328.00 | |||||||||||||||||||||
| Property/Plant/Equipment, Total - Gross | $ 16,449.00 | $ 21,256.00 | $ 22,354.00 | $ 25,258.00 | |||||||||||||||||||||||
| PROPERTY, PLANT AND EQUIPMENT - net | $ 14,633.00 | $ 14,967.00 | $ 14,476.00 | ||||||||||||||||||||||||
| Accumulated Depreciation, Total | $ (8,246.00) | $ (10,621.00) | $ (9,783.00) | $ (10,625.00) | |||||||||||||||||||||||
| Goodwill, Net | $ 9,401.00 | $ 10,629.00 | $ 11,289.00 | $ 12,100.00 | $ 12,312.00 | $ 12,255.00 | |||||||||||||||||||||
| Intangibles, Net | $ 7,235.00 | $ 10,499.00 | $ 12,843.00 | $ 14,272.00 | |||||||||||||||||||||||
| Long Term Investments | $ 21,980.00 | $ 17,251.00 | $ 16,033.00 | $ 13,859.00 | $ - 0 | $ - 0 | |||||||||||||||||||||
| Other Long Term Assets, Total | $ 4,532.00 | $ 4,246.00 | $ 3,865.00 | $ 4,173.00 | $ - 0 | $ - 0 | |||||||||||||||||||||
| Total Assets | $ 87,896.00 | $ 87,270.00 | $ 89,996.00 | $ 92,023.00 | $ 90,055.00 | $ 86,174.00 | |||||||||||||||||||||
| Accounts Payable | $ 2,288.00 | $ 2,682.00 | - | $ 2,089.00 | |||||||||||||||||||||||
| Accrued Expenses | $ 6,284.00 | $ 6,116.00 | - | $ 7,145.00 | |||||||||||||||||||||||
| Accounts payable and accrued expenses | $ 9,234.00 | $ 9,577.00 | $ 8,680.00 | ||||||||||||||||||||||||
| Notes Payable/Short Term Debt | $ 13,205.00 | $ 12,498.00 | $ 13,129.00 | $ 19,130.00 | $ 16,901.00 | $ 16,297.00 | |||||||||||||||||||||
| Current Port. of LT Debt/Capital Leases | $ 3,298.00 | $ 3,527.00 | $ 2,676.00 | $ 3,552.00 | $ 1,024.00 | $ 1,577.00 | |||||||||||||||||||||
| Other Current liabilities, Total | $ 2,119.00 | $ 1,872.00 | $ 1,733.00 | $ 458.00 | $ 309.00 | $ 1,267.00 | |||||||||||||||||||||
| Total Current Liabilities | $ 27,194.00 | $ 26,532.00 | $ 26,929.00 | $ 32,374.00 | $ 27,811.00 | $ 27,821.00 | |||||||||||||||||||||
| Long Term Debt | $ 31,182.00 | $ 29,684.00 | $ 28,311.00 | $ 19,063.00 | $ 19,154.00 | $ 14,736.00 | |||||||||||||||||||||
| Capital Lease Obligations | - | - | - | - | |||||||||||||||||||||||
| Total Long Term Debt | $ 31,182.00 | $ 29,684.00 | $ 28,311.00 | $ 19,063.00 | $ 19,154.00 | $ 14,736.00 | |||||||||||||||||||||
| Total Debt | $ 47,685.00 | $ 45,709.00 | $ 44,116.00 | $ 41,745.00 | $ 37,079.00 | $ 32,610.00 | |||||||||||||||||||||
| Deferred Income Tax | $ 2,522.00 | $ 3,753.00 | $ 4,691.00 | $ 5,636.00 | $ 6,152.00 | $ 4,981.00 | |||||||||||||||||||||
| Minority Interest | $ 1,905.00 | $ 158.00 | $ 210.00 | $ 241.00 | $ 267.00 | $ 378.00 | |||||||||||||||||||||
| Other Liabilities, Total | $ 8,021.00 | $ 4,081.00 | $ 4,301.00 | $ 4,389.00 | $ 3,498.00 | $ 5,468.00 | |||||||||||||||||||||
| Total Liabilities | $ 70,824.00 | $ 64,208.00 | $ 64,442.00 | $ 61,703.00 | $56,615 | $53,006 | |||||||||||||||||||||
| Redeemable Preferred Stock, Total | - | - | - | - | |||||||||||||||||||||||
| Preferred Stock - Non Redeemable, Net | - | - | - | - | |||||||||||||||||||||||
| Common Stock, Total | $ 1,760.00 | $ 1,760.00 | $ 1,760.00 | $ 1,760.00 | $ 1,760.00 | $ 1,760.00 | |||||||||||||||||||||
| Additional Paid-In Capital | $ 15,864.00 | $ 14,993.00 | $ 14,016.00 | $ 13,154.00 | $ 12,276.00 | $ 11,379.00 | |||||||||||||||||||||
| Retained Earnings (Accumulated Deficit) | $ 60,430.00 | $ 65,502.00 | $ 65,018.00 | $ 63,408.00 | $ 61,660.00 | $ 58,045.00 | |||||||||||||||||||||
| Treasury Stock - Common | $ (50,677.00) | $ (47,988.00) | $ (45,066.00) | $ (42,225.00) | $ (39,091.00) | $ (35,009.00) | |||||||||||||||||||||
| Other Equity, Total | $ (10,798.00) | $ (11,510.00) | $ (10,174.00) | $ (6,749.00) | $ (3,432.00) | $ (3,385.00) | |||||||||||||||||||||
| Total Equity | $ 17,072.00 | $ 23,062.00 | $ 25,554.00 | $ 30,320.00 | $ 33,440.00 | $ 33,168.00 | |||||||||||||||||||||
| Total Liabilities & Shareholders' Equity | $ 87,896.00 | $ 87,270.00 | $ 89,996.00 | $ 92,023.00 | $90,055 | $86,174 | |||||||||||||||||||||
| Shares Outs - Common Stock Primary Issue | - | - | - | - | |||||||||||||||||||||||
| Total Common Shares Outstanding | 4,259.00 | 4,288.00 | 4,324.00 | 4,366.00 | 3,498.00 | 5,468.00 | |||||||||||||||||||||
| Cash flow statement | |||||||||||||||||||||||||||
| In Millions of USD (except for per share items) | 2017 | 2016 | 2015 | 2014 | 2013 | 2012 | |||||||||||||||||||||
| Net Income/Starting Line | $ 1,283.00 | $ 6,550.00 | $ 7,366.00 | $ 7,124.00 | $ 8,626.00 | $ 9,086.00 | |||||||||||||||||||||
| Depreciation/Depletion | $ 1,260.00 | $ 1,787.00 | $ 1,970.00 | $ 1,976.00 | $ 1,977.00 | $ 1,982.00 | |||||||||||||||||||||
| Amortization | - | - | - | - | |||||||||||||||||||||||
| Deferred Taxes | $ (1,256.00) | $ (856.00) | $ 73.00 | $ (40.00) | $ 648.00 | $ 632.00 | |||||||||||||||||||||
| Non-Cash Items | $ 2,290.00 | $ 1,536.00 | $ 1,276.00 | $ 1,994.00 | $ 223.00 | $ 25.00 | |||||||||||||||||||||
| Changes in Working Capital | $ 3,529.00 | $ (221.00) | $ (157.00) | $ (439.00) | $ (932.00) | $ (1,080.00) | |||||||||||||||||||||
| Cash from Operating Activities | $ 7,106.00 | $ 8,796.00 | $ 10,528.00 | $ 10,615.00 | $ 10,542.00 | $ 10,645.00 | |||||||||||||||||||||
| Capital Expenditures | $ (1,675.00) | $ (2,262.00) | $ (2,553.00) | $ (2,406.00) | $ (2,550.00) | $ (2,780.00) | |||||||||||||||||||||
| Other Investing Cash Flow Items, Total | $ (775.00) | $ 1,263.00 | $ (3,633.00) | $ (5,100.00) | $ (1,664.00) | $ (8,624.00) | |||||||||||||||||||||
| Cash from Investing Activities | $ (2,450.00) | $ (999.00) | $ (6,186.00) | $ (7,506.00) | $ (4,214.00) | $ (11,404.00) | |||||||||||||||||||||
| Financing Cash Flow Items | $ (129.00) | $ 79.00 | $ 251.00 | $ (363.00) | $ 17.00 | $ 100.00 | |||||||||||||||||||||
| Total Cash Dividends Paid | $ (6,320.00) | $ (6,043.00) | $ (5,741.00) | $ (5,350.00) | $ (4,969.00) | $ (4,595.00) | |||||||||||||||||||||
| Issuance (Retirement) of Stock, Net | $ (2,087.00) | $ (2,247.00) | $ (2,319.00) | $ (2,630.00) | $ (3,504.00) | $ (3,070.00) | |||||||||||||||||||||
| Issuance (Retirement) of Debt, Net | $ 1,089.00 | $ 1,666.00 | $ 2,696.00 | $ 4,712.00 | $ 4,711.00 | $ 4,218.00 | |||||||||||||||||||||
| Cash from Financing Activities | $ (7,447.00) | $ (6,545.00) | $ (5,113.00) | $ (3,631.00) | $ (3,745.00) | $ (3,347.00) | |||||||||||||||||||||
| Foreign Exchange Effects | $ 242.00 | $ (6.00) | $ (878.00) | $ (934.00) | $ (611.00) | $ (255.00) | |||||||||||||||||||||
| Net Change in Cash | $ (2,549.00) | $ 1,246.00 | $ (1,649.00) | $ (1,456.00) | $ 1,972.00 | $ (4,361.00) | |||||||||||||||||||||
| Cash Interest Paid, Supplemental | $ 757.00 | $ 663.00 | $ 515.00 | $ 498.00 | |||||||||||||||||||||||
| Cash Taxes Paid, Supplemental | $ 1,904.00 | $ 1,554.00 | $ 2,357.00 | $ 1,926.00 | |||||||||||||||||||||||
Addl_Num_References
| NOPM = NOPAT/Revenue from Sales | NOPAT = Operating Income x (1- Tax Rate) | |||||||||||||||||||||||
| Revenue | ||||||||||||||||||||||||
| Stock | Excess | Growth | Net | Tax | Stock | Shares | 10-Yr | Bond | Preferred | Depreciation | Investment | Working | CURRENT/Short- | CURRENT/Short- | Equity | Company | Value | Value | Company | NOPAT | Corporate Debt | Enterprise Value | ||
| Symbol | Return | Rate | Oper | Rate | Price | Outstanding | Treas | Spread | Stock | Rate | Rate | Capital | Term | Term | Market | Beta | Debt | Preferred | WACC | (%) | ||||
| Period | 2017 | (%) | Profit | (%) | ($) | (Mil) | Yield | Treas | Yield | (% Rev) | (% Rev) | (% Rev) | Assets | Liabilities | Risk | Outstanding | Stock | (%) | Debt | |||||
| (Yrs) | Revenues | 12 year average | Margin | (%) | (%) | (%) | capital expenditures | ($ Mil) | ($ Mil) | Premium | ($ Mil) | Outstanding | Ratio | |||||||||||
| ($ Mil) | (%) | Default: | non-cash depreciation charges | (%) | 'Market Cap' | ($ Mil) | ||||||||||||||||||
| 1.50% | revenue | |||||||||||||||||||||||
| KO | 10 | $ 35,410.00 | 4.33% | 22.41% | 21.00% | $ 45.73 | 4,259 | 2.90% | 3.80% | 0 | 3.67 | 18.56% | 16.81 | $ 36,545.00 | $ 27,194.00 | 6.70% | 0.64 | $ 194,764.07 | 0 | TBD by Elizabeth | $ 7,937.13 | 80.58% | $ 223,626.07 | |
| number of years into the future that the Excel model forecasts cash flow | sum total of operating income for the reported tax year | calculate the growth rate from financial data | NOPAT/Sales Revenue | From KO financial statement | current trailing average/ yahoo finance 200-Day Moving Average: 45.73 | annual reports should list this number (Yahoo Finance) | treasury.gov for March 2018 | yield difference between the 10-year U.S. Treasury bonds and corporate debt | interest rate being paid on the preferred stock (Yahoo Finance: KO has no preferred stock) | ratio of the annual depreciation rate of items like property, plant and equipment (PPE) expenditures divided by the company’s annual operating revenues | capital expenditures from statement of cash flows - non-cash depreciation charges (income statement). divide result by revenue | cash a business requires for the expenses of its day-to-day operations | current assets that can be liquidated within one year | debt incurred by a company that is due within one year | extra premium (percentage) that an investor requires over and above a risk-free percentage premium (see Q12 for the group's required return above RF-Rate) | (Yahoo Finance) beta | Yahoo Finance PPS and # shares to find this value | Yahoo Finance (KO has no preferred stock) | The debt ratio is calculated as follows: Total Liabilities divided by Total Assets | |||||
| 3.80% | ||||||||||||||||||||||||
| Enterprise Value | ||||||||||||||||||||||||
| Current share price (P) | $ 45.73 | |||||||||||||||||||||||
| No. shares of common stock outstanding | 4,259,000,000 | |||||||||||||||||||||||
| USD $ in millions | ||||||||||||||||||||||||
| Common equity (market value) | $ 194,764.07 | |||||||||||||||||||||||
| Add: Equity attributable to noncontrolling interests (per books) | $ 1,905.00 | |||||||||||||||||||||||
| Total equity | $ 196,669.07 | |||||||||||||||||||||||
| Add: Loans and notes payable (per books) | $ 13,205.00 | |||||||||||||||||||||||
| Add: Current maturities of long-term debt (per books) | $ 3,298.00 | |||||||||||||||||||||||
| Add: Long-term debt, excluding current maturities (per books) | $ 31,182.00 | |||||||||||||||||||||||
| Total equity and debt | $ 244,354.07 | |||||||||||||||||||||||
| Less: Cash and cash equivalents | $ 6,006.00 | |||||||||||||||||||||||
| Less: Short-term investments & marketable securities | $ 14,722.00 | |||||||||||||||||||||||
| Less: Marketable securities | ||||||||||||||||||||||||
| Enterprise value (EV) | $ 223,626.07 | |||||||||||||||||||||||
| Stock Dividend Data | ||||||||||||||||||||||||
| DIVIDEND | ||||||||||||||||||||||||
| YIELD | ||||||||||||||||||||||||
| 3.55% | ||||||||||||||||||||||||
| Consumer Goods Average 1.79% | ||||||||||||||||||||||||
| Preferred stock is a special equity security that has properties of both equity and debt. Coca-Cola Co's preferred stock for the quarter that ended in Dec. 2017 was | $0 | |||||||||||||||||||||||
| The market value of preferred stock needs to be added to the market value of common stocks in the calculation of Enterprise Value. Coca-Cola Co's for the quarter that ended in Dec. 2017 was | $ 223,626.07 | mil | ||||||||||||||||||||||
| In the calculation of book value, the par value of preferred stocks needs to subtracted from total equity. Coca-Cola Co's Book Value per Share for the quarter that ended in Dec. 2017 was | $4.01 | |||||||||||||||||||||||
| Dividends paid to preferred stocks need to be subtracted from net income in the calculation of Earnings per Share (Diluted). Coca-Cola Co's Earnings per Share (Diluted) for the three months ended in Dec. 2017 was <0> | ($0.64) | $ - 0 |
2_ProjectRevenue
| 2006 | 2007 | 2008 | 2009 | 2010 | 2011 | 2012 | 2013 | 2014 | 2015 | 2016 | 2017 | |||||
| Revenue | $24,088 | $28,857 | $31,944 | $30,990 | $35,119 | $46,542 | $48,017 | $46,854 | $45,998 | $44,294 | $41,863 | $35,410 | ||||
| EPS | 1.08 | 1.285 | 1.245 | 1.465 | 2.53 | 1.85 | 1.97 | 1.9 | 1.6 | 1.67 | 1.49 | 0.29 | ||||
| Dividends | 0.62 | 0.68 | 0.76 | 0.82 | 0.88 | 0.94 | 1.02 | 1.12 | 1.22 | 1.32 | 1.4 | 1.48 | ||||
| Year-over-year growth rates | 12-year average (revenue growth) | |||||||||||||||
| 19.80% | 10.70% | -2.99% | 13.32% | 32.53% | 3.17% | -2.42% | -1.83% | -3.70% | -5.49% | -15.41% | 4.33% | |||||
| Projected | ||||||||||||||||
| Excess | Revenues | Annual | ||||||||||||||
| Period | =C*(1+O) | Growth | ||||||||||||||
| N | ($ Mil) | Rates | ||||||||||||||
| 2017 | 0 | $35,410.00 | ||||||||||||||
| 2018 | 1 | $36,944.62 | 4.33 | |||||||||||||
| 2019 | 2 | $38,545.74 | 4.33 | |||||||||||||
| 2020 | 3 | $40,216.25 | 4.33 | |||||||||||||
| 2021 | 4 | $41,959.16 | 4.33 | |||||||||||||
| 2022 | 5 | $43,777.61 | 4.33 | |||||||||||||
| 2023 | 6 | $45,674.86 | 4.33 | |||||||||||||
| 2024 | 7 | $47,654.34 | 4.33 | |||||||||||||
| 2025 | 8 | $49,719.61 | 4.33 | |||||||||||||
| 2026 | 9 | $51,874.38 | 4.33 | |||||||||||||
| 2027 | 10 | $54,122.54 | 4.33 | |||||||||||||
KO Revenue vs. Year-over-Year growth rates
Revenue 2007 2008 2009 2010 2011 2012 2013 2014 2015 2016 2017 28857 31944 30990 35119 46542 48017 46854 45998 44294 41863 35410 Growth Rate (YOY) 2007 2008 2009 2010 2011 2012 2013 2014 2015 2016 2017 0.10697577710780747 -2.9864763335837714E-2 0.13323652791222976 0.32526552578376378 3.1691805251170983E-2 -2.4220588541558199E-2 -1.8269518077432025E-2 -3.7045088916909434E-2 -5.4883279902469863E-2 -0.15414566562358167
3_CalcFCF
| FCF=[EBIT(1-T)+Depreciation] - [Groosss investment in fixed assets] - [Investment NOWC] | Year | FCF | ||||||||||||
| Year | EBIT | EBIT(1-T)* | Depretation | Gross Inv | Invest NOWC | FCF | 2017 | $ 6,909.00 | ||||||
| 2017 | $ 6,742.00 | $ 4,792.00 | $ 1,260.00 | $ 1,675.00 | $ (2,532.00) | $ 6,909.00 | 2016 | $ 13,939.00 | ||||||
| 2016 | $ 8,136.00 | $ 6,550.00 | $ 1,787.00 | $ 2,262.00 | $ (7,864.00) | $ 13,939.00 | 2015 | $ (79.00) | ||||||
| 2015 | $ 9,605.00 | $ 7,366.00 | $ 1,970.00 | $ 2,553.00 | $ 6,862.00 | $ (79.00) | 2014 | $ 17,968.00 | ||||||
| 2014 | $ 9,325.00 | $ 7,124.00 | $ 1,976.00 | $ 2,406.00 | $ (11,274.00) | $ 17,968.00 | 2013 | $ 5,954.00 | ||||||
| 2013 | $ 11,477.00 | $ 8,626.00 | $ 1,977.00 | $ 2,550.00 | $ 2,099.00 | $ 5,954.00 | ||||||||
| 2012 | $ 11,809.00 | $ 9,086.00 | $ 1,982.00 | $ 2,780.00 | ||||||||||
| *True values from data given | ||||||||||||||
| 2017 | 2016 | 2015 | 2014 | 2013 | 2012 | |||||||||
| Capital Expenditures | $ (1,675.00) | $ (2,262.00) | $ (2,553.00) | $ (2,406.00) | $ (2,550.00) | $ (2,780.00) | ||||||||
| Other Investing Cash Flow Items, Total | $ (775.00) | $ 1,263.00 | $ (3,633.00) | $ (5,100.00) | $ (1,664.00) | $ (8,624.00) | ||||||||
| Cash from Investing Activities | $ (2,450.00) | $ (999.00) | $ (6,186.00) | $ (7,506.00) | $ (4,214.00) | $ (11,404.00) | ||||||||
| NOWC | ||||||||||||||
| Cash & Equivalents | $ 6,006.00 | $ 8,555.00 | $ 7,309.00 | $ 8,958.00 | $10,414 | $8,442 | ||||||||
| Accounts Receivable - Trade, Net | $ 3,667.00 | $ 3,856.00 | $ 3,941.00 | $ 4,466.00 | $ 4,873.00 | $ 4,759.00 | ||||||||
| Total Inventory | $ 2,655.00 | $ 2,675.00 | $ 2,902.00 | $ 3,100.00 | $ 3,277.00 | $ 3,264.00 | ||||||||
| $ 12,328.00 | $ 15,086.00 | $ 14,152.00 | $ 16,524.00 | $ 18,564.00 | $ 16,465.00 | |||||||||
| Accounts Payable | $ 2,288.00 | $ 2,682.00 | - | $ 2,089.00 | ||||||||||
| - | Accrued Expenses | $ 6,284.00 | $ 6,116.00 | - | $ 7,145.00 | |||||||||
| $ 8,572.00 | $ 8,798.00 | $ - 0 | $ 9,234.00 | $ - 0 | $ - 0 | |||||||||
| NOWC | $ 3,756.00 | $ 6,288.00 | $ 14,152.00 | $ 7,290.00 | $ 18,564.00 | $ 16,465.00 | ||||||||
| Investment NOWC | $ (2,532) | $ (7,864) | $ 6,862 | $ (11,274) | $ 2,099 |
FCF
2017 2016 2015 2014 2013 6909 13939 -79 17968 5954
Year
Free Cash FLow
4_CalcWACC
| Debt | |
| Market value of debt | $ 194,764.07 |
| Yield of the bond | 3.80% |
| Tax rate | 21.00% |
| After tax cost of debt | 3.00% |
| Equity | |
| Stock price | $ 45.73 |
| Number of shares outstanding | 4259 |
| Market value of equity | $ 194,764.07 |
| Based on DDM, | |
| Growth rate | 4.33% |
| Dividend yield | 3.55% |
| Last divided per share | $ 1.62 |
| Cost of equity based on DDM | 8.04% |
| Based on CAPM | |
| Beta | 0.64 |
| Risk free rate | 2.90% |
| Market risk premium | 6.70% |
| Cost of equity based on CAPM | 7.18% |
| Cost of equity average | 7.61% |
| WACC Calculation | |
| Weight of debt | 50.00% |
| Weight of equity | 50.00% |
| After tax cost of debt | 3.00% |
| Cost of equity | 7.61% |
| WACC | 5.31% |
5_DiscountFCF
6_CalcIntrinsicValue
EQUATIONS
KO-SEC_CsldtdBalanceSheet
| CONSOLIDATED BALANCE SHEETS - USD ($) | Dec. 31, 2017 | Dec. 31, 2016 | Dec. 31, 2015 | Dec. 31, 2014 | Dec. 31, 2013 | Dec. 31, 2012 | |
| $ in Millions | |||||||
| CURRENT ASSETS | |||||||
| Cash and cash equivalents | $ 6,006.00 | $ 8,555.00 | $ 7,309.00 | $ 8,958.00 | $ 10,414.00 | $ 8,442.00 | |
| Short-term investments | $ 9,352.00 | $ 9,595.00 | $ 8,322.00 | $ 9,052.00 | $ 6,707.00 | $ 5,017.00 | |
| TOTAL CASH, CASH EQUIVALENTS AND SHORT-TERM INVESTMENTS | $ 15,358.00 | $ 18,150.00 | $ 15,631.00 | $ 18,010.00 | $ 17,121.00 | $ 13,459.00 | |
| Marketable securities | $ 5,317.00 | $ 4,051.00 | $ 4,269.00 | $ 3,665.00 | $ 3,147.00 | $ 3,092.00 | |
| Trade accounts receivable, less allowances of $477 and $466, respectively | $ 3,667.00 | $ 3,856.00 | $ 3,941.00 | $ 4,466.00 | $ 4,873.00 | $ 4,759.00 | |
| Inventories | $ 2,655.00 | $ 2,675.00 | $ 2,902.00 | $ 3,100.00 | $ 3,277.00 | $ 3,264.00 | |
| Prepaid expenses and other assets | $ 2,000.00 | $ 2,481.00 | $ 2,752.00 | $ 3,066.00 | $ 2,886.00 | $ 2,781.00 | |
| Assets held for sale | $ 219.00 | $ 2,797.00 | $ 3,900.00 | $ 679.00 | $ - 0 | $ 2,973.00 | |
| Assets held for sale - discontinuing operations | $ 7,329.00 | $ - 0 | |||||
| TOTAL CURRENT ASSETS | $ 36,545.00 | $ 34,010.00 | $ 33,395.00 | $ 32,986.00 | $ 31,304.00 | $ 30,328.00 | |
| EQUITY METHOD INVESTMENTS | $ 20,856.00 | $ 16,260.00 | $ 12,318.00 | $ 9,947.00 | $ 10,393.00 | $ 9,216.00 | |
| OTHER INVESTMENTS | $ 1,096.00 | $ 989.00 | $ 3,470.00 | $ 3,678.00 | $ 1,119.00 | $ 1,232.00 | |
| OTHER ASSETS | $ 4,560.00 | $ 4,248.00 | $ 4,110.00 | $ 4,407.00 | $ 4,661.00 | $ 3,585.00 | |
| PROPERTY, PLANT AND EQUIPMENT - net | $ 8,203.00 | $ 10,635.00 | $ 12,571.00 | $ 14,633.00 | $ 14,967.00 | $ 14,476.00 | |
| TRADEMARKS WITH INDEFINITE LIVES | $ 6,729.00 | $ 6,097.00 | $ 5,989.00 | $ 6,533.00 | $ 6,744.00 | $ 6,527.00 | |
| BOTTLERS' FRANCHISE RIGHTS WITH INDEFINITE LIVES | $ 138.00 | $ 3,676.00 | $ 6,000.00 | $ 6,689.00 | $ 7,415.00 | $ 7,405.00 | |
| GOODWILL | $ 9,401.00 | $ 10,629.00 | $ 11,289.00 | $ 12,100.00 | $ 12,312.00 | $ 12,255.00 | |
| OTHER INTANGIBLE ASSETS | $ 368.00 | $ 726.00 | $ 854.00 | $ 1,050.00 | $ 1,140.00 | $ 1,150.00 | |
| TOTAL ASSETS | $ 87,896.00 | $ 87,270.00 | $ 89,996.00 | $ 92,023.00 | $ 90,055.00 | $ 86,174.00 | |
| CURRENT LIABILITIES | |||||||
| Accounts payable and accrued expenses | $ 8,748.00 | $ 9,490.00 | $ 9,660.00 | $ 9,234.00 | $ 9,577.00 | $ 8,680.00 | |
| Loans and notes payable | $ 13,205.00 | $ 12,498.00 | $ 13,129.00 | $ 19,130.00 | $ 16,901.00 | $ 16,297.00 | |
| Current maturities of long-term debt | $ 3,298.00 | $ 3,527.00 | $ 2,676.00 | $ 3,552.00 | $ 1,024.00 | $ 1,577.00 | |
| Accrued income taxes | $ 410.00 | $ 307.00 | $ 331.00 | $ 400.00 | $ 309.00 | $ 471.00 | |
| Liabilities held for sale | $ 37.00 | $ 710.00 | $ 1,133.00 | $ 58.00 | $ - 0 | $ 796.00 | |
| Liabilities Held for Sale, Discontinued Operations | $ 1,496.00 | $ - 0 | |||||
| TOTAL CURRENT LIABILITIES | $ 27,194.00 | $ 26,532.00 | $ 26,929.00 | $ 32,374.00 | $ 27,811.00 | $ 27,821.00 | |
| LONG-TERM DEBT | $ 31,182.00 | $ 29,684.00 | $ 28,311.00 | $ 19,063.00 | $ 19,154.00 | $ 14,736.00 | |
| OTHER LIABILITIES | $ 8,021.00 | $ 4,081.00 | $ 4,301.00 | $ 4,389.00 | $ 3,498.00 | $ 5,468.00 | |
| DEFERRED INCOME TAXES | $ 2,522.00 | $ 3,753.00 | $ 4,691.00 | $ 5,636.00 | $ 6,152.00 | $ 4,981.00 | |
| THE COCA-COLA COMPANY SHAREOWNERS' EQUITY | |||||||
| Common stock, $0.25 par value; Authorized — 11,200 shares; Issued — 7,040 and 7,040 shares, respectively | $ 1,760.00 | $ 1,760.00 | $ 1,760.00 | $ 1,760.00 | $ 1,760.00 | $ 1,760.00 | |
| Capital surplus | $ 15,864.00 | $ 14,993.00 | $ 14,016.00 | $ 13,154.00 | $ 12,276.00 | $ 11,379.00 | |
| Reinvested earnings | $ 60,430.00 | $ 65,502.00 | $ 65,018.00 | $ 63,408.00 | $ 61,660.00 | $ 58,045.00 | |
| Accumulated other comprehensive income (loss) | $ (10,305.00) | $ (11,205.00) | $ (10,174.00) | $ (5,777.00) | $ (3,432.00) | $ (3,385.00) | |
| Treasury stock, at cost — 2,781 and 2,752 shares, respectively | $ (50,677.00) | $ (47,988.00) | $ (45,066.00) | $ (42,225.00) | $ (39,091.00) | $ (35,009.00) | |
| EQUITY ATTRIBUTABLE TO SHAREOWNERS OF THE COCA-COLA COMPANY | $ 17,072.00 | $ 23,062.00 | $ 25,554.00 | $ 30,320.00 | $ 33,173.00 | $ 32,790.00 | |
| EQUITY ATTRIBUTABLE TO NONCONTROLLING INTERESTS | $ 1,905.00 | $ 158.00 | $ 210.00 | $ 241.00 | $ 267.00 | $ 378.00 | |
| TOTAL EQUITY | $ 18,977.00 | $ 23,220.00 | $ 25,764.00 | $ 30,561.00 | $ 33,440.00 | $ 33,168.00 | |
| TOTAL LIABILITIES AND EQUITY | $ 87,896.00 | $ 87,270.00 | $ 89,996.00 | $ 92,023.00 | $ 90,055.00 | $ 86,174.00 | |
