Please value the firm using the DCF approach Conditions: 1. Book value can be used for debt rating (

/in /by

Please value the firm using the DCF approach
Conditions:
1. Book value can be used for debt rating (assuming book equates to market) 2. Use market capitalization for common equity weighting 3. CAPM should be used to estimate the required return on common equity
would prefer if i could get my answer in a table format on excel and please try to show work
the following information should be able to provide the discount rate. if not just use a standard rate.
(s in millions) Historical Financial Statements Fiscal Year Ending December 31, 2015A 2016A 2017A Projected Financial Statements Fiscal Year Ending December 31, 2019P 2020P 2021P 2022P 2014A 2018A 2023P Income Statement Revenue % Growth Cost of Goods Sold COGS as % of Revenue Gross Profit Gross Profit Margin % Depreciation & Amortization SG&A Expenses SG&A as % of Revenue Other Operating Epenses Other Operating Expenses as % of Re: Operating Income / EBIT Operating income / EBIT Margin % EBITDA EBITDA Margin % EBITDA Growth $45,998.0 -1.8% $17,889.0 38.9% $28,109,0 61.1% $1,976.0 $17 2100 37.4% $1,1830 2.6% $7,7320 16.8% $9.700.0 21.1% $44 294.0 37% $17,482.0 39.5% $26.812 0 60,5% $1,970.0 $16.427,0 37.1% $1.657 0 3.7% $6,758.0 $41.883.0 55% $16,465.0 39.3% $25,398,0 60.7% $1,787.0 $15.370,0 36.7% $1,3710 3 3 % $6,870 0 $35,4100 -15 4% $13,255,0 37.4% $22,1550 62.6% $1,260,0 $12.654 0 35.7% $1.9020 5.4% $6,339.0 $31.856.0 100% $11.770.0 36.996 $20,088.0 63.1% $1.086.0 10.3070 32.4% $1.079 0 3.4% $7,614,0 $33 289.5 45% $12,787.8 38.4% $20,501.7 61.6% $829.7 $11.9392 35 9% $1,221.5 3.7% $6,511.3 $34,787.5 $36,353 0 45% 45% $13,363.3 $13,964.6 38.4% 38.4% $21.4243 $22,388 3 61.6% 61.6% $474,0 $154.3 $12.476.5 $13.0379 35 9% 35 9% $1.276.4 $1.3339 3.7% 3.7% $7.1973 $7,862 2 $37,988.9 4.5% $14,593,0 38.4% $23,395 8 61.6% ($134.6) $13.624 6 35.9% $1.3939 3.7% $8,511.9 $39.698.4 45% $15,249.7 38.4% $24.448.6 61.6% ($3972) $14 237.7 35 9% $1.456 6 3.7% $9,151.4 $8.728 0 197% 8.657 0 20.7% $7.599.0 21.5% $8,700,0 27 36 $7.341.0 22 1% $7.671.3 22 1% $8.016.5 221% $8.3773 221% $8.754 3 22 1% $856.0 $733.0 Interest Expense Interest Income | Net Interest Expense Other Income (Expense) Pretax Income Taxes Tax Rate Consolidated Net Income less Net income attributable to noncontrolir Net Income Net Margin % Net Income Growth (1) Excludes depreciation and amortization $483.0 $5940 $111.0 (8494.0) $9,325.0 $2,201,0 23.6% $7,124.0 $26.0 $7,098.0 15.4% ($243.0) $1,120.0 $9,605.0 $2,239.0 23.3% $7,366.0 $15.0 $73510 16 6% ($91.0) ($430.0) $8,136.0 $1,586.0 19.5% $6,550 0 $23.0 $6,5270 15 6% $841.0 $6770 ($164.0) ($693.0) $6.742.0 $5,560,0 82.5% $1,1820 $919,0 $655.1 $6820 $7051 ($2370) $51.0 ($113.0) ($125.5) $8,350.0 $7,266.5 $1,623.0 $1,526.0 19.4% 21.0% $6,7270 $5,7406 $42.0 $28.2 $6.685 01 $5.712 4 21.0% 17.2% 482.8% -14.5% $676,5 $7308 $542 ($131.1) $7,594.5 $1,5948 21.0% $5,9996 $28.2 95.971 4 17.2% 4.5% $703.4 $7564 $53.0 ($137.0) $7,9325 $1,665 8 21.0% $6,266.7 $28.2 86.2385 17.2% $730 2 $7828 $526 ($143.2) $8,286.7 $1,740 2 21.0% $6,546,5 $7571 $8102 $53.1 ($149.6) $8,657.8 $1,818.1 21.0% $6,839.7 $1,1470 3.2% -82.4% $6.518.3 17.2% $6.811.5 17.2% ($ in milions) Historical Financial Statements Fiscal Year Ending December 31, 2015A 2016A 2017A Projected Financial Statements Fiscal Year Ending December 31, 2020P 2021P 2 022P 2014A 2018A 2019P 2023P $7,3660 $1,970.0 $236.0 $5,740.6 $829.7 $229 $5.999.6 $474,0 $229 $6266.7 $154.3 $229 $6,546.5 ($134.6) $229 $6,839.7 ($3972) $229 Cash Flow Cash Flow from Operations Consolidated Net Income 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 Assi Other Operating Charges Other items Not Change in Oporating Assets and Liabilt Cash Flow from Operations $7,1240 $1.976.0 $2090 ($40.0) (53710) $4150 $8310 $7610 $1490 (5439.0) $10,615,0 ($122 0) ($1370) ($374 0) $929 0 $744 0 ($157 0) $10,528.0 $6,5500 $1,787.0 $2580 ($856.0) ($449 0) $1580 $1,1460 $6470 ($224 0) ($2250) $8,7920 $1,1820 $1.260.0 $2190 ($1.256.0) ($628 0) $281.0 $1.459.0 $1.218 0 ($2000) $3.4640 $6.930 0 $6.7270 $1,086.0 $225 0 ($450.0) ($457 0) ($380) $1890 $550 O 6020 ($1.202.0) $7,3200 ($4064) $1358 $650 2 $A226 $216.4 $2882 $8,507 4 ($4054) $135 8 $650 2 $8226 52164 $2882 $8.410.9 ($4054) $135 8 $650 2 $8226 2164 $2882 $8 3582 ($4054) $1358 $650 2 $8226 216 4 $2882 $8 349 1 ($4054) $1358 $6502 $8226 $2164 $2882 $8 379 7 Cash Flow from Investing Purchases of Investments ($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) Proceeds from Disposals of Investments $12.986.0 $14,079.0 $17,8420 $16,694,0 $14,977,0 $15,315,6 $15,315.6 $15,315.6 $15,315,6 $15,315,6 Acquisitions of Businesses, Equity Method ($389.0) ($2,491.0) ($838.0) ($3,809.0) ($1.040.0) ($1,713.4) ($1,713.4) ($1,713.4) ($1,713.4) ($1,713.4) Proceeds from Disposals of Businesses, Ex $1480 $505.0 $1,035.0 $3,821,0 $1,3020 $1,380.2 $1,380.2 $1,388.2 $1,388.2 $1,380.2 Purchases of Property, Plant and Equipmel ($2,406.0) ($2,553.0) ($2,262.0) ($1,675.0) ($1,347.0) $1,674.9 $1,750.3 $1,829.1 $1,911.4 $1.997.4 Proceeds from Disposals of Property, Plani $223.0 $85.0 $150,0 $104,0 $245,0 $161.4 $161.4 $161.4 $161.4 $161.4 Other Investing Activities ($40.0) ($305.0) ($93.0) $60.0) ($1532) ($153 2) ($1532) ($153.2) ($1532) Cash Flow from Investing ($7,508.0) (86, 186.0) ($1,004.0) ($2,254.0) $6,348.0 $1,603.1 $1,678.5 $1,757.3 $1,839.6 $1,925.6 Cash Flows from Financing Isances of Debt Payments of Debt Issuances of Stock Purchases of stock for Treasury Dividends Other Financing Activities Cash Flow from Financing 841,6740 $40,434,0 $27,281.0 $29,8570 $27339 0 $33,3170 $33,317,0 $33,317,0 $33,317,0 $33,3170 (536,962.0) ($37.738.0) ($25,615.0) ($28,768.0) ($30,568.0)|(331,930 2) ($31,930 2) ($31,9302) ($31,930 2) ($31,9302) $1,5320 $1,2450 $1,4340 $1,5950 $1,476,0 $1,456.4 $1,456.4 $1.456.4 $1.456.4 $1.456 4 (54,1620) ($3,5640) ($3,681.0) ($3,682.0) ($1,912 0) ($3,400 2) ($3,400 2) ($3,4002) ($3,400 2) ($3,400 2) (86,350.0) ($5,741.0) ($6,043.0) ($6,320.0) ($0,644.0) ($6,019.6) ($0,019.6) ($6,019.6) ($6,019.6) ($6,019.6) (5363 0) $251.0 $79.0 ($91.0) (5243.0) ($73.4) ($73.4) ($73.4) ($73.4) ($73.4) ($3,631.0) ($5,113.0) ($6,5450) ($7409 0) ($10,5520) ($6,650.0) (56,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 (85220) ($934 0) ($771.0) ($878 0) $1,243.0 ($5.0) $80 ($2,733.0) $2410 ($570) $3,116.0 ($262.0) 68.0 $3,460.6 ($8642) ($291.6) $3,439.4 $1,204,0 $378 3 $3.465.5 $7040 $3059 $3,538.7 $698 1 $4160 $3,655.3 $6116 $2852 Beginning Cash Position Change in Cash Position Ending Cash Position $10,414,0 ($1,456.0) $8.9580 $8,958.0 ($1.649.0) $7,309 0 $7,309.0 $1,246.0 $8,555 0 $8,555.0 ($2,549.0) $6,006 O $6,006.0 $2,920.0 $8,9260 $8,926.0 $2,304.8 $11,230 8 $11,230.8 $5,021.7 $16.2525 $16,252.5 $4,475.4 $20,7279 $20,727.9 $4,652.8 $25,380.7 $25,380.7 $4,532.1 $29.9128 (s in millions) Historical Financial Statements Fiscal Year Ending December 31, 2015A 2016A 2017A 2018A2019P Projected Financial Statements Fiscal Year Ending December 31, 2020P2021P2022P 2014A 2023P Balance Sheet Assets Cash and Cash Equivalents $8,958,0 Short-term Investments $9,0520 Total Cash, Cash Equivalents and short- $18,010,0 Marketable Securities $3,065.0 Accounts receivable $4,466.0 Inventory $3,100.0 Other Current Assets $3,745.0 Total Current Assets $32.986.0 $7,309,0 $8 3220 $15,631.0 $4.289.0 $3.941.0 $2.902.0 $6.652.0 $33,3950 $8,555.0 $9.5950 $18,150.0 $4.051.0 $3,856.0 $2,675.0 $5,278.0 $34,010,0 $6,006.0 $9 3520 $15,358,0 $5.317,0 $3,667.0 $2,655.0 $9,548.0 $36,545.0 $8.926.0 $11,230.8 $20250 $76692 $10,951.0 $18,900,0 $5.013.0 $5.013. 0 $3,396.0 $3,371.5 $2,766.0 $2.469.2 $8.508.0 $6.746.2 $30,6340 $36,499.9 $16.252.5 $76892 $23.921.7 5 .013,0 $3,478.8 $2,598.4 $6.746.2 $41,758.0 $20,7279 $8,0000 $28.727.9 $5.013,0 $3,635.3 $2.715.3 $6.746.2 $46,837.7 $25,380.7 $8 0000 $33,380.7 $5.013,0 $3,798.9 $2,837.5 $6.746.2 $51,776.3 $29.912.8 $8000 O $37912.8 $5.013.0 $3,969.8 $2,9652 $6,7462 $56.607.1 $9,9470 $3,6780 $4,4070 $12,3180 $3.4700 $4,2070 $16,2600 $9890 $4,248.0 $15,757 6 $2,020,0 $4,2462 $15,757 6 $2,020,0 $4,2462 $15,7576 $2,020,0 $4,2462 $15,7576 $2,020 0 $4,246 2 Equity Method Investments Other Investments Other Assets Deferred Income Tax Assets PP&E – Not Trademarks w/ Indof Lives Bottlers' Franchise Rights w/ Indef Lives Goodwill Other Intangible Assets Total Assets $14,6330 $6,533.0 $6,689.0 $12,100,0 $1,050.0 $92,023.0 $12,5710 $5989.0 $6.000.0 $11,289.0 $854.0 $90,093.0 $10,6350 $6,097.0 $3,676.0 $10,629.0 $7260 $87.270.0 $20,8560 $1,096.0 $4,230,0 $3300 $8,203.0 $6,729.0 $138.0 $9.401,0 $368.0 $87,896.0 $19,407 0 $8670 $4,139.0 $2 6670 58 232.0 56 682.0 $51.0 $10,283.0 $274.0 $83,216.0 $15,757 6 $2,020,0 $4,246.2 – $5,566.0 $6,406.0 $3,310.8 $10,736.4 $3,180 2 $6,406.0 $3,310.8 $10,736.4 $1,035.4 $6,406.0 $3,310.8 $10,736.4 ($902 8) $6,406.0 $3,310.8 $10,736.4 ($2,6645) $6,406.0 $3,310.8 $10,736.4 $84,542.9 $87.415.3 $90,350.1 $93,350.5 $96.419.6 Liabilities Accounts Payable and Accrued Expenses Loans and Notes Pavable Current Maturities of Long-term Debt Accrued Income Taxes Liabilities Held for Sale Total Current Liabilities $9,234.0 $19,1300 $3,5520 $400,0 $580 $32,374 0 $9.8600 $9.490 0 $13,1290 $12.498 0 $2 6770 $3,5270 $331,0 $307,0 $1,1330 $7100 $26.930,0 $26,5320 $8.748.0 $13 2050 $3 298 0 $410,0 $1,5330 $27.1940 $8,9320 $13,194 0 $4.997 0 $3780 $1,7220 $29 223,0 $8,1873 $13.0065 $3 0757 $3652 $1 268 86 $25,903.5 $8,5557 $13.0065 $3.268.4 $3652 $1,3260 $26,521.7 $8.940.7 $9.3430 $13,0065 $13,0065 $3.461.1 $3.653 8 $365 2 $3852 $1,3856 $1,448 0 $27.159.1 $27,816.5 $9,763.5 $13.006.5 $3 846.5 $3652 $1,5131 $28,494.8 Long-Term Debt Other Liabilities Deferred Income Tax Liabitios Total Liabilities $19,083.0 $4,389.0 $5.636.0 $61,462.0 $28,407,0 $4,301,0 $4.691.0 $64,329.0 $29,684.0 $4,081.0 $3.7530 $64,050.0 $31,182.0 $8.021.0 $2.5220 $68,919.0 $25,364,0 $7,638.0 $1.9330 $64,158.0 $22,135.0 $5,686.0 $3,707 O $57.431.5 $23,521.8 $5,686.0 $3.7070 $59.436.5 $24.908.6 $5,688.0 $3.707 $61,460.7 $26,295.4 $5,686.0 $3,707.0 $63,504.9 $27.682.2 $5,686.0 $3,707.0 $65,570.0 Shareholders Equity Common Stock $1,760,0 $1,7600 $1,7600 $1,7600 $1,760 0 $1,7600 $1,7600 $1,7600 $1,760,0 $1,760.0 Capital Surplus $13,1540 $14,016 0 $14,993 0 $15,8640 $16,520,0 $17,346.0 $18, 2133 $19,124,0 $20,000 2 $21,0842 Reinvested Earnings $63 4080 965 018 0 $65 5020 S60 4300 $63 2340 $63.5184 $63.518.4 $63 518.4 $63.5184 $63.518.4 Accumulatod Other Comprehensive Income ($5.777.0) ($10 174.0) ($11.205.0) ($10,305.0) ($12.814.0)|(510.065.0) ($10,055.0) ($10,055.0) ($10,055.0) ($10,055.0) Treasury Stock ($42,225.0) ($45,066.0) ($47.988.0) ($50,677.0) ($51,719.0)|(547, 535.0) ($47,535.0) ($47,535.0) ($47,535.0) ($47,535.0) Equity Attributable to Shareowners of the C Equity Attributable to Non-Controlling Intere Total Shareholders Equity $30,320.0 $241.0 $30,561.0 $25,554.0 $210,0 $25,764,0 $23,082.0 $158.0 $23,220.0 $17,072.0 $1.905.0 $18.977,0 $16,981.0 $2,077.0 $19,058.0 $25,034.4 $2,077.0 $27.111.4 $25,901.7 $2,077.0 $27.978.7 $26,812.4 $2.077.0 $28,889.4 $27.768.6 $2.077.0 $29,845.6 $28,772.8 $2.077.0 $30,849.6 Total Liabilities and Equity Check $92 0230 $0.0 $90 093 0 $0.0 $87 2700 $0.0 $87 8960 $0.0 $83 216.0 0.0 $84,5429 $87 4152 ($0.0) $0.0 $90 350.1 80.0 $93,350.5 50.0 $96.419.6 0.0 Coca-Cola Co. Operating Profit Margin Dec 31, 2018 Dec 31, 2017 Dec 31, 2016 Dec 31, 2015 Selected Financial Data (US$ in millions) Operating income Net operating revenues 8,700 31,856 7,501 35,410 8,626 41,863 8,728 44,294 Profitability Ratio 27.31% 21.18% 20.61% 19.70% Operating profit margin Benchmarks Operating Profit Margin, Competitors PepsiCo Inc. 15.64% 16.54% 15.58% 13.25% Operating Profit Margin, Sector Beverages 19.49% 18.20% 17.59% 15.91% Operating Profit Margin, Industry 11.75% Consumer Goods 9.78% 13.54% 12.78% Based on:10-K (filing date: 2019-02-21) 10-K (filing date: 2018-02-23),10-K (filing date: 2017-02-24),10-K (filing date: 2016-02-25) 10-K (filing date: 2015-02-25). H I 5% 99% 1% 2022 20 4.5% 38.4% 13.0% 36.0% 28.09 11.0% 9.0% 26.0% 8.0% 16.0% 21.0% 2023 4.5% 38.4% 13.0% 36.0% 28.0% 11.0% 9.0% 26.0% 8.0% 16.0% 21.0% 11% 2024 4.5% 38.4% 13.0% 36.0% 28.0% 11.0% 9.0% 26.0% 8.0% 16.0% 21.0% 0.0% 7% 9% 6% 8% 4% 7% NA 50 SO 50 50 A B C D E F G 124 Figure 9-3 125 Coca-Cola's Forecast of Operations for the Selected Scenario Millions of Dollars, Except for Per Share Data) 126 Status Quo Industry Micro Drive Micro Drive 127l Panel A: Inputs Actual Actual Forecast 128 Al. Operations 2018 2017 2018 2019 2020 2021 129 Sales growth rate 1546 -10% 4.5% 4.5% 4.5% 130 (COGS excl depr.)/Sales 76% 37% 37% 38.4% 38.4% 38.4% 1311 Depreciation (Net PP&E 15% 1396 13.0% 13.0% 13.0% 132 (Other op. exp.)/Sales 10% 41% 36% 36.0% 36.0 36 0% 11331 Cash/Sales 17% 28% 28.0% 28.0% 28.0% 134 (Acc.rec.)/Sales 8% 10% 11.0% 11.0% 11.0% 135 Inventory/Sales 15% 9.0% 9.0% 9.0% 136 (Net PP&E]/Sales 33% 23% 26% 26.09 26.0% 26.0% 137 (Acc. pay.)/Sales 8.0% 8.0% 8.0% 138 Accruals/Sales 15% 16% 16.0% 16.0% 16.0% 139 Tax rate 40% 83% 19% 21.0% 21.0% 21.0% 140 Cost of strategic initiatil NA 141 Panel B: Results Actual Forecast 1421 Sales Revenues 2018 2019 2020 2021 1431 Net sales $31.856 $33,290 $34,788 $36,353 144 B2. Operating Assets and Operating Labories 145 Cash $9,926 $9,321 $9,741 $10,179 146 Accounts receivable $3.396 $3,662 $3,827 $3,999 1471 Inventories $2,766 $2,996 53.131 3.272 148 Net PP&E SH.232 58,655 99,045 99,452 149 Accounts payable $2.498 $2,663 $2,783 $2,908 150l Accruals SS.225 $5,326 SS.566 55,816 1151 3. Operating Income 152 COGS (excl. depr.) $11,770 $12,783 $13,358 $13,960 153 Depreciation $1,086 $1,125 $1,176 $1,229 154 Other operating expenses $11,386 $11,984 $12,524 $13,087 15S Cost of strategic initiatives 1561 EBIT 57,614 $7,397 $7,730 $8,078 157) Net operating profit after taxes 56.134 $5,844 56.107 56.381 158 B4. Free Cash Flows 159 Net operating working capital 57,365 $7,989 $8,349 $8,725 160 Total net operating capital $15,597 $16,645 $17,394 $18,176 11611 FCT NOPAT- Anet op capital $3,521 $4,796 $5,358 $5,599 2024 2022 $37.989 2023 $39.698 $11.485 $10,637 $4.179 $3,419 $9,877 $3.039 $6,078 $11,116 $4,367 $3,573 $10,322 $3,176 56352 $11,616 94.563 33,734 $10,786 $3,319 $6.638 $14,588 $1,284 $13,676 $0 $8,441 $6,668 $15,244 $1,342 $14,291 $0 9.821 56.969 $15,930 $1,402 $14,935 NA So SO $9,218 57.282 $9,117 $9,528 $18,994 $19,849 $5,851 $6,113.82 $9,956 $20,742 $5,534.20 Coca-Cola Co. Current Ratio Dec 31, 2018 Dec 31, 2017 Dec 31, 2016 Dec 31, 2015 Selected Financial Data (US$ in millions) Current assets Current liabilities 36,545 27,194 33,395 26,930 Liquidity Ratio Current ratio 1.24 Benchmarks Current Ratio, Competitors PepsiCo Inc. Current Ratio, Sector Beverages 1.42 Current Ratio, Industry Consumer Goods 0.98 1.10 Based on:10 Kifiling date: 2019 02 21,10 Kiling date: 2018-02-23),10 K (filing date: 2017-02-24),10 K (filing date: 2016-02-23),10 K filling date: 2015-02-25).

PLACE YOUR ORDER TO GET STARTEDThe post Please value the firm using the DCF approach Conditions: 1. Book value can be used for debt rating ( appeared first on Essay Gem.

"Order a Custom Paper on Similar Assignment! No Plagiarism! Enjoy 20% Discount"

No related posts.