CSUN Coca Cola Business Valuation Project Worksheet

User Generated

whyvnpheyl

Business Finance

California state university Northridge

Description

- Provide interpretations of the ratios

- cost of debt is not the risk free rate; need to go to finer.org search for Bonds data, look for KO long term bonds

-need to calculate intrinsic equity value per share using the constant growth of dividends model

Unformatted Attachment Preview

Business description of the company The Coca-Cola Company is a global beverage company specializing in producing, marketing, and selling non-alcoholic drinks. They include water, sparkling soft drinks, sports beverages, dairy and plant-based drinks, enhanced water, and pure energy drinks. Its brands include Coca-Cola, Diet Coke, Coca-Cola Zero, Fanta, Sprite, Georgia, Minute Maid, Powerade, Minute Maid Pulpy, Aquarius, Del Valle, Simply, Gold Peak, Bonaqua, Schweppes, Glaceau Smartwater, Fuze Tea, and Ice Dew. It operates across the Asia Pacific, North and South America, the Middle- East, Europe and Africa. The organization was formed by Asa Griggs Candler in 1886 and is currently headquartered in Atlanta, GA. Untitled Spreadsheet Powered by Clearbit Coca-Cola Co (The) (NYS: KO) Exchange rate used is that of the Year End reported date As Reported Annual Balance Sheet Report Date 12/31/2020 Currency USD Audit Status Not Qualified Consolidated Yes Scale Thousands Cash & cash equivalents 6795000 Short-term investments 1771000 Total cash, cash equivalents & short-term investments 8566000 Marketable securities 2348000 Trade accounts receivable, gross 3670000 Less: allowances 526000 Trade accounts receivable, net 3144000 Raw materials & packaging 2106000 Finished goods 791000 Other inventory 369000 Inventories 3266000 Prepaid expenses & other assets 1916000 Assets held for sale Assets held for sale - discontinued operations Total current assets 19240000 Equity method investments 19273000 Other investments 812000 Other assets 6184000 Deferred income tax assets 2460000 Land 676000 Buildings & improvements 4782000 Machinery & equipment 14242000 Machinery, equipment & vehicle fleet Property, plant & equipment - cost 19700000 Less accumulated depreciation 8923000 Property, plant & equipment - net 10777000 Trademarks with indefinite lives 10395000 Bottlers' franchise rights with indefinite lives Goodwill 17506000 Other intangible assets 649000 Total assets 87296000 Accounts payable 3517000 Accrued marketing expenses 1930000 Variable consideration payable 1137000 Other accrued expenses 3352000 Accrued compensation 609000 Deferred tax liabilities Accrued sales, payroll & other taxes 443000 Container deposits 157000 12/31/2019 USD Not Qualified Yes Thousands 6480000 1467000 7947000 3228000 4495000 524000 3971000 2180000 851000 348000 3379000 1886000 20411000 19025000 854000 6075000 2412000 659000 4576000 13686000 18921000 8083000 10838000 9266000 109000 16764000 627000 86381000 3804000 2059000 3835000 1021000 442000 151000 Accounts payable & accrued expenses 11145000 Loans & notes payable 2183000 Current maturities of long-term debt 485000 Accrued income taxes 788000 Liabilities held for sale Liabilities held for sale - discontinued operations Total current liabilities 14601000 Notes 22550000 Debentures 1342000 Zero coupon notes Australian dollar notes 400000 Euro notes 13821000 Swiss franc notes 1236000 Other long term debt 615000 Fair value adjustment 646000 Total debt before current portion 40610000 Less current portion 485000 Long-term debt 40125000 Other liabilities 9453000 Deferred income tax liabilities 1833000 Common stock 1760000 Capital surplus 17601000 Reinvested earnings 66555000 Net foreign currency translation adjustment -12028000 Accumulated net gains (losses) on derivatives -194000 Unrealized net gains (losses) on available for sale securities 28000 Adjustment to pension & other postretirement benefit liabilities -2407000 Accumulated other comprehensive income (loss) -14601000 Treasury stock, at cost 52016000 Equity attributable to shareowners of The Coca-Cola Company 19299000 Equity attributable to noncontrolling interests 1985000 Total equity 21284000 11312000 10994000 4253000 414000 26973000 14621000 1366000 168000 677000 12807000 1129000 548000 453000 31769000 4253000 27516000 8510000 2284000 1760000 17154000 65855000 -11270000 -209000 75000 -2140000 -13544000 52244000 18981000 2117000 21098000 Total Equity and Liabilities 87296000 86381000 Total Liabilities 66012000 65283000 Close End Share Price $ 54.84 55.35 12/31/2018 USD Not Qualified Yes Thousands 8926000 2025000 10951000 5013000 3885000 489000 3396000 1862000 692000 212000 2766000 1962000 6546000 30634000 19407000 867000 4139000 2667000 485000 3838000 11922000 16245000 8013000 8232000 6682000 51000 10263000 274000 83216000 2498000 1787000 3352000 894000 315000 86000 12/31/2017 USD Not Qualified Yes Thousands 6006000 9352000 15358000 5317000 4144000 477000 3667000 1729000 693000 233000 2655000 2000000 219000 7329000 36545000 20856000 1096000 4560000 334000 3917000 12198000 16449000 8246000 8203000 6729000 138000 9401000 368000 87896000 2288000 2108000 3071000 854000 347000 80000 12/31/2016 USD Not Qualified Yes Thousands 8555000 9595000 18150000 4051000 4322000 466000 3856000 1565000 844000 266000 2675000 2481000 2797000 34010000 16260000 989000 4248000 589000 4574000 16093000 21256000 10621000 10635000 6097000 3676000 10629000 726000 87270000 2682000 2186000 2593000 857000 692000 372000 108000 8932000 13194000 4997000 378000 1722000 29223000 13619000 1390000 163000 723000 12994000 1128000 282000 62000 30361000 4997000 25364000 7638000 1933000 1760000 16520000 63234000 -11045000 -126000 50000 -1693000 -12814000 51719000 16981000 2077000 19058000 8748000 13205000 3298000 410000 37000 1496000 27194000 16854000 1559000 158000 760000 13663000 1148000 325000 13000 34480000 3298000 31182000 8021000 2522000 1760000 15864000 60430000 -8957000 -119000 493000 -1722000 -10305000 50677000 17072000 1905000 18977000 9490000 12498000 3527000 307000 710000 26532000 16922000 2111000 153000 741000 11567000 1304000 316000 97000 33211000 3527000 29684000 4081000 3753000 1760000 14993000 65502000 -9780000 314000 305000 -2044000 -11205000 47988000 23062000 158000 23220000 83216000 87896000 87270000 64158000 68919000 64050000 47.35 45.88 41.46 Untitled Spreadsheet Powered by Clearbit Coca-Cola Co (The) (NYS: KO) Exchange rate used is that of the Year End reported date As Reported Annual Income Statement Report Date Currency Audit Status Consolidated Scale Net operating revenues Cost of goods sold Gross profit Stock-based compensation expense Advertising expenses Selling & distribution expenses Other operating expenses Selling, general & administrative expenses Other operating charges Operating income (loss) Interest income Interest expense Equity income (loss) - net Other income (loss) - net Income before income taxes - U.S. Income before income taxes - international Income before income taxes U.S. income tax expense (benefit) - current State & local income tax expense (benefit) - current International income tax expense (benefit) - current Total current income taxes (benefit) U.S. income tax expense (benefit) - deferred State & local income tax expense (benefit) - deferred International income tax expense (benefit) - deferred Total deferred income taxes (benefit) Income taxes Net income (loss) from continuing operations Income (loss) from discontinued operations Consolidated net income (loss) Less: net loss (income) attributable to noncontrolling interests Net income attributablet to shareowners of the Cola-Cola Company Weighted average shares outstanding - basic Weighted average shares outstanding - diluted Year end shares outstanding Income (loss) per share from continuing operations - basic Income (loss) per share - discontinued operations - basic Net income (loss) per share - basic Income (loss) per share from continuing operations - diluted Income (loss) per share - discontinued operations - diluted 12/31/2020 USD Not Qualified Yes Thousands 33014000 13433000 19581000 126000 2777000 2638000 4190000 9731000 853000 8997000 370000 1437000 978000 841000 3149000 6600000 9749000 296000 396000 1307000 1999000 -220000 21000 181000 -18000 1981000 7768000 -21000 7747000 4295000 4323000 4302000 1.8 - Net income (loss) per share - diluted Dividends per share Total number of employees Number of common stockholders Foreign currency translation adjustments Tax Rate 1.79 1.64 80300 197226 -911000 20% 12/31/2019 USD Not Qualified Yes Thousands 37266000 14619000 22647000 201000 4246000 2873000 4783000 12103000 458000 10086000 563000 946000 1049000 34000 3249000 7537000 10786000 508000 94000 1479000 2081000 -65000 52000 -267000 -280000 1801000 8985000 -65000 8920000 4276000 4314000 4280000 2.09 - 12/31/2018 USD Not Qualified Yes Thousands 31856000 11770000 20086000 225000 4113000 1701000 4268000 10307000 1079000 8700000 682000 919000 1008000 -1121000 888000 7462000 8350000 591000 145000 1337000 2073000 -386000 -81000 17000 -450000 1623000 6727000 -251000 6476000 -42000 6434000 4259000 4299000 4268000 1.58 -0.07 1.51 1.57 -0.07 12/31/2017 USD Not Qualified Yes Thousands 35410000 13256000 22154000 219000 3958000 3257000 5062000 12496000 2157000 7501000 677000 841000 1071000 -1666000 -690000 7432000 6742000 5438000 121000 1257000 6816000 -1783000 14000 513000 -1256000 5560000 1182000 101000 1283000 -35000 1248000 4272000 4324000 4259000 0.28 0.02 0.29 0.27 0.02 2.07 1.6 86200 200770 74000 1.5 1.56 62600 206575 -2035000 0.29 1.48 61800 212331 861000 12/31/2016 USD Not Qualified Yes Thousands 41863000 16465000 25398000 258000 4004000 5177000 5823000 15262000 1510000 8626000 642000 733000 835000 -1234000 113000 8023000 8136000 1147000 113000 1182000 2442000 -838000 -91000 73000 -856000 1586000 6550000 -23000 6527000 4317000 4367000 4288000 1.51 - 1.49 1.4 100300 219011 -626000 Untitled Spreadsheet Ratios 2020 Current ratio 1.31771796 =current assets/current liabilities Inventory Turnover 4.04303988 =Annualized cost of sales/Average inventory DOS =Receivables / Average sales per day 34.75979887 FA Turnover =Sales / Net fixed assets 3.063375708 TA Turnover =Sales / Total assets' 0.37818457 Debt ratio =Total debt / Total debt + total equity 0.46519886 TIE (times-interest-earned) =EBIT / Interest expense 22.97425191 Profit margin (net) =Net income / Sales 0.234658024 ROA =Net income / Total assets ROE =Net income / Total common equity 0.363982334 P/E =Price / Earnings per share 30.46666667 0.088744043 2019 2018 2017 0.75671968 1.04828389 1.34386262 4.75801465 4.34237226 4.97410882 38.89376375 38.91072325 37.79878565 3.43845728 3.869776482 4.316713398 0.43141432 0.382811 0.40286247 0.36777764 0.3648457 0.39228179 39.39323467 34.66376496 42.10463734 0.239360275 0.201971371 0.035244281 0.103263449 0.077316862 0.014198598 0.42278889 0.337601007 0.065763819 26.48325359 31.35761589 158.2068966 Ratio Description 2016 1.28184833 6.1782364 33.62014189 3.936342266 0.4796952 0.3805546 57.11186903 0.155913336 0.074790879 0.281093885 27.45695364 20-year treasury bond on December 31, 2020 was 1.45% Risk Free Rate Risk Premium Risk Market Return Beta Calculation for Coca cola's cost of Equity (Ke) 1.45% 6% 7.45% 0.69 Cost of Equity (CAPM) Ke = Rf+Beta(Rm - Rf) KO Cost of equity (Ke) 5.59% Calculation for Coca cola's cost of Debt Risk Free Rate Tax Rate Cost of Debt 1.45% 20.32% 1.16% treasury bond on December 31, 2020 was 1.45% lculation for Coca cola's cost of Equity (Ke) Same as the 20-year treasury bond rate on December 31, 2020 Provided in the notes as 6% Equivalent to Risk free rate + Risk premium Calculation for Coca cola's cost of Debt Coca Cola WACC Market Capitalization (2020) $ 235,700,000,000.00 Total debt (2020) $ 44,420,000,000.00 Weight of Equity (We) Weight of Debt (Wd) Cost of Equity (Ke) Cost of Debt (Kd) 84% 16% 5.59% 1.16% WACC = (We*Ke) + (Kd*Kd(1-Tax)) 4.85% Coca Cola Valuation NOA = (Total Liabilities & Equity- cash & cash equivalents- Investments) - (Total Liabilities-Long Term Debt-Short NOA (2020) 69,215,000 NOPAT = EBIT *(1 - Tax Rate) NOPAT 7,768,000 Net Operating Profit Margin Net Operating Asset Turnover 0.24 0.476977534 KO Sales Growth Sales, rounded NOPAT NOA Reported 2020 KO DCF Model Increase is NOA FCFF Discount Factor Present Value of Horizon FCFF Cumulative PV of Horizon FCFF Present Value of Terminal FCFF Total Firm Value Less (plus NNO) Less NCI Corporate Value Shares Outstanding Equity Value Per Share (Intrinsic Value) 2020 Year End Share Price Reported 2020 33,014,000 7,768,000 $ 69,215,000 $ 2,925,649 $ 205,990,489 $ 208,916,138 1985000 $ 206,931,138 4,302,000 $ 48.10 $ 54.84 My estimated KO stock price of $48.10 is slightly lower than the market price of $54.84 on December 31, 2020, indi slightly undervalued on that date. Stock prices are a function of expected NOPAT and NOA, as well as the WACC d stock price may be due to more optimistic forecasts or a lower discount rate compared to other investors' and analysts a Cola Valuation Liabilities-Long Term Debt-Short TermDebt) WACC Outstanding Shares NNI 2021 15.40% 2022 Terminal Period 5.60% 38,098,156 40,231,653 8,964,272 9,466,271 $ 79,874,110 $ 84,347,060 2021 5% 4,302,000 1985000 1% $ 40,633,969 9,560,934 85,190,531 2022 Terminal Growth (1%) $ 10,659,110 $ 4,472,950 $ $ (1,694,838) $ 4,993,321 $ 0.95375 0.90963 $ (1,616,448) $ 4,542,097 54.84 on December 31, 2020, indicating that I believe the KO stock was nd NOA, as well as the WACC doscount rate. Differences in estimated red to other investors' and analysts' model assumptions. 843,471 8,717,463 KO Growth Estimates (Yahoo Finance) - https://finance.yahoo.com/quote/KO/analysis?p=KO Assume that Terminal Growth is 1%
Purchase answer to see full attachment
Explanation & Answer:
1 Page
User generated content is uploaded by users for the purposes of learning and should be used following Studypool's honor code & terms of service.

Explanation & Answer

View attached explanation and answer. Let me know if you have any questions.Hi, I finished. I made some changes to four tabs: ratios, cost of equity, WACC, and valuation. I explained what each ratio measures and the company's performance. The market cost of debt was calculated based on Coca-Cola's bonds issued in the United States. The WACC was also adjusted since the after-tax cost of debt changed. Finally, I expanded your explanation for KO's stock being OVERPRICED (not underpriced).

Business description of the company

The Coca-Cola Company is a global beverage company specializing in producing,
marketing, and selling non-alcoholic drinks. They include water, sparkling soft drinks,
sports beverages, dairy and plant-based drinks, enhanced water, and pure energy drinks.
Its brands include Coca-Cola, Diet Coke, Coca-Cola Zero, Fanta, Sprite, Georgia, Minute
Maid, Powerade, Minute Maid Pulpy, Aquarius, Del Valle, Simply, Gold Peak, Bonaqua,
Schweppes, Glaceau Smartwater, Fuze Tea, and Ice Dew. It operates across the Asia
Pacific, North and South America, the Middle- East, Europe and Africa. The organization
was formed by Asa Griggs Candler in 1886 and is currently headquartered in Atlanta, GA.

Untitled Spreadsheet

Powered by Clearbit

Coca-Cola Co (The) (NYS: KO)
Exchange rate used is that of the Year End reported date

As Reported Annual Balance Sheet
Report Date
12/31/2020
Currency
USD
Audit Status
Not Qualified
Consolidated
Yes
Scale
Thousands
Cash & cash equivalents
6795000
Short-term investments
1771000
Total cash, cash equivalents & short-term investments 8566000
Marketable securities
2348000
Trade accounts receivable, gross
3670000
Less: allowances
526000
Trade accounts receivable, net
3144000
Raw materials & packaging
2106000
Finished goods
791000
Other inventory
369000
Inventories
3266000
Prepaid expenses & other assets
1916000
Assets held for sale
Assets held for sale - discontinued operations
Total current assets
19240000
Equity method investments
19273000
Other investments
812000
Other assets
6184000
Deferred income tax assets
2460000
Land
676000
Buildings & improvements
4782000
Machinery & equipment
14242000
Machinery, equipment & vehicle fleet
Property, plant & equipment - cost
19700000
Less accumulated depreciation
8923000
Property, plant & equipment - net
10777000
Trademarks with indefinite lives
10395000
Bottlers' franchise rights with indefinite lives
Goodwill
17506000
Other intangible assets
649000
Total assets
87296000
Accounts payable
3517000
Accrued marketing expenses
1930000
Variable consideration payable
1137000
Other accrued expenses
3352000
Accrued compensation
609000
Deferred tax liabilities
Accrued sales, payroll & other taxes
443000
Container deposits
157000

12/31/2019
USD
Not Qualified
Yes
Thousands
6480000
1467000
7947000
3228000
4495000
524000
3971000
2180000
851000
348000
3379000
1886000
20411000
19025000
854000
6075000
2412000
659000
4576000
13686000
18921000
8083000
10838000
9266000
109000
16764000
627000
86381000
3804000
2059000
3835000
1021000
442000
151000

Accounts payable & accrued expenses
11145000
Loans & notes payable
2183000
Current maturities of long-term debt
485000
Accrued income taxes
788000
Liabilities held for sale
Liabilities held for sale - discontinued operations
Total current liabilities
14601000
Notes
22550000
Debentures
1342000
Zero coupon notes
Australian dollar notes
400000
Euro notes
13821000
Swiss franc notes
1236000
Other long term debt
615000
Fair value adjustment
646000
Total debt before current portion
40610000
Less current portion
485000
Long-term debt
40125000
Other liabilities
9453000
Deferred income tax liabilitie...


Anonymous
Really great stuff, couldn't ask for more.

Studypool
4.7
Trustpilot
4.5
Sitejabber
4.4

Related Tags