HANESBRANDS EXCEL

profilevk4queen
SAMPLEPROJECT.xlsx
Home>Business & Finance homework help>HANESBRANDS EXCEL

PEP

Dec. 26, 2015 Dec. 31, 2016 Dec. 30, 2017 Dec. 29, 2018 Dec. 28, 2019 2020 2021 2022 2023 2024 Beyond
Assumptions
Sales growth -5% -0% 1% 2% 4% 1.6% 1.6% 1.6% 1.6% 1.6%
Terminal Growth Rate 3.00%
Interest Expense - Based on Debt * Cost of Debt % 2.53% 2.53% 2.53% 2.53% 2.53%
All Others Grow at the same rate as Sales
P/E Multiples Valuation
Benchmark P/E 27.13
Earnings available to common shareholders 7,314
Equity intrinsic value 198,443
Shares 1,390 remember to adjust for millions!
Implied price per share $ 142.77
P/S Multiples Valuation
Benchmark P/S 2.93
Sales Revenue 67,161
Equity intrinsic value 196,580
Shares 1,390
Implied price per share $ 141.42
EV/EBITDA Multiples Valuation
Benchmark EV/EBITDA 16.35
EBITDA 12,679
Enterprise value 207,302
Market value of non-operating assets 5,738
Market value of debt 38,730
Equity intrinsic value 174,309
Shares 1,390
Implied price per share $ 125.40
Equity Cash Flow Model: Discounting Equity Cash Flows at Cost of Equity
Ke (Cost of Equity) 6.22%
1 2 3 4 5
Equity cash flow (FCFE) 10,456.52 10,581.89 10,717.91 10,856.82 10,993.02
Terminal value 351,988.41
PV 9,844.50 9,379.44 8,943.97 8,529.62 8,131.12 260,352.67
Equity value of operations 305,181
Market value of non-operating assets 5,738
Equity intrinsic value 310,919
Shares 1,390
Implied price per share $ 223.68
Corporate Valuation Model: Discounting Free Cash Flows at WACC
WACC (Weighted Average Cost of Capital) 5.54%
1 2 3 4 5
Free cash flow (FCFF) 10,434 10,559 10,695 10,833 10,969
Terminal value 445017.726766243
PV 9,886.53 9,479.69 9,097.58 8,731.79 8,377.27 339873.495835967
Value of operations 385,446
Market value of debt 38,730
Market value of non-operating assets 5,738
Equity intrinsic value 352,454
Shares 1,390
Implied price per share $ 253.56
Adjusted Present Value Model: Discounting Unlevered Cash Flows at Unlevered Cost of Capital
Ku (Unlevered Cost of Capital) 5.65%
1 2 3 4 5
FCFF 10,434 10,559 10,695 10,833 10,969
Terminal value 426229.466273323
PV 9,876.06 9,459.61 9,068.70 8,694.85 8,332.99 323803.944592628
Unlevered value of operations 369,236
1 2 3 4 5
Interest tax savings = IntExp * tax Rate 130.7 132.8 134.9 137.0 139.2
Terminal value 5,410.16
PV 123.74 118.93 114.37 109.99 105.77 4,110
Value of tax shield 4,683
Market value of debt 38,730
Market value of non-operating assets 5,738
Equity intrinsic value 340,927
Shares 1,390
Implied price per share $ 245.27
Cash Flow Computations
Year Ended… Dec. 26, 2015 Dec. 31, 2016 Dec. 30, 2017 Dec. 29, 2018 12/28/19 2020 2021 2022 2023 2024
Shares Outstanding 1445412743 1427214232 1419908267 1404686108 1389544618
Figures in… Million USD Million USD Million USD Million USD Million USD Million USD Million USD Million USD Million USD Million USD
OpInc 8,353.0 9,804.0 10,276.0 10,110.0 10,291 10,455.7 10,622.9 10,792.9 10,965.6 11,141.0
Tax Rate (t) 26.1% 25.4% 48.9% -36.7% 21.0% 21.0% 21.0% 21.0% 21.0% 21.0%
NOPAT = OpInc x (1-t) 6,174.4 7,312.0 5,252.5 13,817.8 8,126 8,256 8,388 8,522 8,659 8,797
Dep & Amort Exp 2,416.0 2,368.0 2,369.0 2,399.0 2,432.0 2,396.8 2,393.0 2,398.0 2,403.7 2,404.7
OCF = NOPAT + Dep & Amort 8,590.4 9,680.0 7,621.5 16,216.8 10,558 10,653 10,781 10,920 11,062 11,202
ΔNOWC (1,406.0) (1,433.0) 418.0 (3,709.0) 1,575.0 (90.1) (91.6) (93.1) (94.5) (96.1)
ΔGFA 1,321.0 2,491.0 2,876.0 2,590.0 3,973.0 308.9 313.8 318.8 323.9 329.1
FCFF = OCF - ΔNOWC - ΔGFA 8,675.4 8,622.0 4,327.5 17,335.8 5,010 10,434 10,559 10,695 10,833 10,969
Interest Expense, net 911.0 1,232.0 907.0 1,219.0 935.0 621.4 631.0 641.1 651.4 661.8
FCFF 8,675.4 8,622.0 4,327.5 17,335.8 5,010 10,434 10,559 10,695 10,833 10,969
ΔDebt 4,387.0 3,661.0 2,336.0 (6,960.0) (253.0) 513.1 521.3 529.6 538.1 546.7
Int(1-t) 673.4 918.9 463.6 1,666.1 738 491 498 506 514 523
PfdCF - 0 - 0 - 0 - 0 -
FCFE = FCFF + ΔDebt - Int(1-t) - PfdCF 12,389.00 11,364.17 6,199.90 8,709.71 4,018.74 10,456.52 10,581.89 10,717.91 10,856.82 10,993.02
FCFF 8,675.4 8,622.0 4,327.5 17,335.8 5,010 10,434 10,559 10,695 10,833 10,969
Int(t) 237.6 313.1 443.4 (447.1) 197 131 133 135 137 139
UCF = FCFF + Int(t) 8,913.0 8,935.2 4,770.9 16,888.7 5,207 10,565 10,692 10,829 10,970 11,108
Financial Statements & Analysis
Consolidated Statement of Income - USD ($) shares in Millions, $ in Millions
Dec. 27, 2014 Dec. 26, 2015 Dec. 31, 2016 Dec. 30, 2017 Dec. 29, 2018 Dec. 28, 2019
Income Statement [Abstract]
Net Revenue 66683 63056 62799 63525 64661 67161 68236 69327 70437 71564 72709
Cost of sales 30884 28731 28222 28796 29381 30132 30614 31104 31602 32107 32621
Gross profit 35799 34325 34577 34729 35280 37029 37621 38223 38835 39456 40088
Selling, general and administrative expenses 26126 24613 24773 24453 25170 26738 27166 27600 28042 28491 28947
Venezuela impairment charges 0 1359 0 0 0 0 0 0 0 0 0
Operating Profit 9581 8353 9804 10276 10110 10291 10456 10623 10793 10966 11141
Other pension and retiree medical benefits (expense)/income 0 -19 233 298 -44 -45 -45 -46 -47 -48
Interest expense -909 -970 -1342 -1151 -1525 -1135 -825 -837 -851 -865 -878
Interest income and other 85 59 110 244 306 200 203 206 210 213 217
Income before income taxes 8757 7442 8553 9602 9189 9312 9461 9612 9766 9922 10081
Provision for/(benefit from) income taxes (See Note 5) 2199 1941 2174 4694 -3370 1959 1990 2022 2055 2087 2121
Net income 6558 5501 6379 4908 12559 7353 7471 7590 7712 7835 7960
Less: Net income attributable to noncontrolling interests 45 49 50 51 44 39 40 40 41 42 42
Net Income Attributable to PepsiCo 6513 5452 6329 4857 12515 7314 7431 7550 7671 7793 7918
Net Income Attributable to PepsiCo per Common Share
Basic 4.31 3.71 4.39 3.4 8.84 5.23
Diluted 4.27 3.67 4.36 3.38 8.78 5.2
Weighted-average common shares outstanding
Basic 1509 1469 1439 1425 1415 1399
Diluted 1527 1485 1452 1438 1425 1407
Consolidated Balance Sheet - USD ($) $ in Millions Dec. 27, 2014 Dec. 26, 2015 Dec. 31, 2016 Dec. 30, 2017 Dec. 29, 2018 Dec. 28, 2019
ASSETS
Cash and cash equivalents 6134 9096 9158 10610 8721 5509 5597 5687 5778 5870 5964
Short-term Investments 2592 2913 6967 8900 272 229 233 236 240 244 248
Restricted Cash 1997 0 0 0 0 0 0
Accounts and notes receivable, net 6651 6437 6694 7024 7142 7822 7947 8074 8203 8335 8468
Inventories 3143 2720 2723 2947 3128 3338 3391 3446 3501 3557 3614
Prepaid expenses and other current assets 2143 1865 908 1546 633 747 759 771 783 796 809
Assets, Current 20663 23031 26450 31027 21893 17645 17927 18214 18506 18802 19102
Property, Plant and Equipment, net 17244 16317 16591 17240 17589 19305 19614 19928 20247 20570 20900
Amortizable Intangible Assets, net 1449 1270 1237 1268 1644 1433 1456 1479 1503 1527 1551
Goodwill 14965 14177 14430 14744 14808 15501 15749 16001 16257 16517 16781
Indefinite-lived Intangible Assets (Excluding Goodwill) 12639 11811 12196 12570 14181 14610 14844 15081 15323 15568 15817
Indefinite-Lived Intangible Assets 27604 25988 26626 27314 28989 30111 30593 31082 31580 32085 32598
Equity Method Investments 2689 2311 1950 2042 2409 2683 2726 2770 2814 2859 2905
Deferred Income Tax Assets, Net 4364 4359 4429 4500 4572 4645 4719
Other Assets 860 750 636 913 760 3011 3059 3108 3158 3208 3260
Total Assets 70509 69667 73490 79804 77648 78547 79804 81081 82378 83696 85035
LIABILITIES AND EQUITY
Short-term debt obligations 5076 4071 6892 5485 4026 2920 2967 3014 3062 3111 3161
Accounts payable and other current liabilities 13016 13507 14243 15017 18112 17541 17822 18107 18397 18691 18990
Liabilities, Current 18092 17578 21135 20502 22138 20461 20788 21121 21459 21802 22151
Long-Term Debt Obligations 23821 29213 30053 33796 28295 29148 29614 30088 30570 31059 31556
Deferred Income Tax Liabilities, Net 5304 4959 4434 3242 3499 4091 4156 4223 4291 4359 4429
Other Liabilities, Noncurrent 5744 5887 6669 11283 9114 9979 10139 10301 10466 10633 10803
Liabilities 52961 57637 62291 68823 63046 63679 64698 65733 66785 67853 68939
Commitments and contingencies
Preferred Stock, no par value 41 41 41 41 0 0 0 0 0 0 0
PepsiCo Common Shareholders’ Equity
Common stock, par value 12/3¢ per share (authorized 3,600 shares, issued, net of repurchased common stock at par value: 1,391 and 1,409 shares, respectively) 25 24 24 24 23 23 23 24 24 25 25
Additional Paid in Capital, Common Stock 4115 4076 4091 3996 3953 3886 3948 4011 4076 4141 4207
Retained Earnings (Accumulated Deficit) 49092 50472 52518 52839 59947 61946 62937 63944 64967 66007 67063
Accumulated Other Comprehensive Income (Loss), Net of Tax -10669 -13319 -13919 -13057 -15119 -14300 -14529 -14761 -14997 -15237 -15481
Treasury Stock, Value -24985 -29185 -36769 -37357 -37955 -38562 -39179 -39806
Stockholders' Equity Attributable to Parent 17578 12068 11246 11045 14518 14786 15023 15263 15507 15755 16007
Stockholders' Equity Attributable to Noncontrolling Interest 110 107 104 92 84 82 83 85 86 87 89
Total Equity 17548 12030 11199 10981 14602 14868 15106 15348 15593 15843 16096
Liabilities and Equity 70509 69667 73490 79804 77648 78547 79804 81081 82378 83696 85035
Consolidated Statement of Cash Flows - USD ($) $ in Millions
Dec. 26, 2015 Dec. 31, 2016 Dec. 30, 2017 Dec. 29, 2018 Dec. 28, 2019
Operating Activities
Net income 5501 6379 4908 12559 7353 7471 7590 7712 7835 7960
Depreciation and amortization 2416 2368 2369 2399 2432 2397 2393 2398 2404 2405
Share-based compensation expense 295 284 292 256 237
Restructuring and impairment charges 230 160 295 308 370
Cash payments for restructuring charges -208 -125 -113 -255 -350
Pension and retiree medical plan expenses 467 501 221 221 519
Pension and retiree medical plan contributions -205 -695 -220 -1708 -716
Deferred income taxes and other tax charges and credits 78 452 619 -531 453
Other net tax benefits related to international reorganizations 0 -4347 -2
Net tax (benefit)/expense related to the TCJ Act 0 0 2451 -28 -8
Tax payments related to the TCJ Act 0 -115 -423
Change in assets and liabilities [Abstract]
Accounts and notes receivable -461 -349 -202 -253 -650
Inventories -244 -75 -168 -174 -190
Prepaid expenses and other current assets -50 10 20 9 -87
Accounts payable and other current liabilities 1692 997 201 882 735
Income taxes payable 55 329 -338 448 -287
Other, net -134 64 -305 -256 263
Net Cash Provided by (Used in) Operating Activities 10864 10673 10030 9415 9649
Investing Activities
Capital spending -2758 -3040 -2969 -3282 -4232
Sales of property, plant and equipment 86 99 180 134 170
Acquisitions and investments in noncontrolled affiliates -86 -212 -61 -1197 -1939
Other acquisitions and investments in noncontrolled affiliates -568 -299 -778
Divestitures 76 85 267 505 253
Short-term investments, by original maturity:
More than three months - purchases -4428 -12504 -18385 -5637 0
More than three months - maturities 4111 8399 15744 12824 16
More than three months - sales 0 0 790 1498 62
Three months or less, net 3 16 2 16 19
Other investing, net -5 9 29 2 -8
Net Cash (Used for)/Provided by Investing Activities -3569 -7148 -4403 4564 -6437
Financing Activities
Proceeds from issuances of long-term debt 8702 7818 7509 0 4621
Payments of long-term debt -4095 -3105 -4406 -4007 -3970
Cash tender and exchange offers/debt redemptions 0 -2504 0 -1589 -1007
Short-term borrowings, by original maturity
More than three months - proceeds 15 59 91 3 6
More than three months - payments -43 -27 -128 -17 -2
Three months or less, net 53 1505 -1016 -1352 -3
Cash dividends paid -4040 -4227 -4472 -4930 -5304
Share repurchases - common -5000 -3000 -2000 -2000 -3000
Share repurchases - preferred -5 -7 -5 -2 0
Proceeds from exercises of stock options 504 465 462 281 329
Withholding tax payments on RSUs, PSUs and PEPunits converted -151 -130 -145 -103 -114
Other financing -52 -58 -76 -53 -45
Net Cash Used for Financing Activities -4112 -3211 -4186 -13769 -8489
Effect of exchange rate changes on cash and cash equivalents and restricted cash -221 -252 47 -98 78
Cash and Cash Equivalents and Restricted Cash, Period Increase (Decrease) 2962 62 1488 112 -5199
Cash and cash equivalents and restricted cash 9096 9158 10657 10769 5570
Depreciation Expense (from Note 4) 2248 2217 2227 2241 2257
COMMON SIZE INCOME STATEMENT Dec. 27, 2014 Dec. 26, 2015 Dec. 31, 2016 Dec. 30, 2017 Dec. 29, 2018 Dec. 28, 2019
Net Revenue 100% 100% 100% 100% 100% 100% 100% 100% 100% 100% 100%
Cost of sales 46% 46% 45% 45% 45% 45% 45% 45% 45% 45% 45%
Gross profit 54% 54% 55% 55% 55% 55% 55% 55% 55% 55% 55%
Selling, general and administrative expenses 39% 39% 39% 38% 39% 40% 40% 40% 40% 40% 40%
Operating Profit 14% 13% 16% 16% 16% 15% 15% 15% 15% 15% 15%
Other pension and retiree medical benefits (expense)/income -0.03% 0.37% 0.46% -0.07% -0.07% -0.07% -0.07% -0.07% -0.07%
Interest expense -1% -2% -2% -2% -2% -2% -1% -1% -1% -1% -1%
Interest income and other 0.13% 0.09% 0.18% 0.38% 0.47% 0.30% 0.30% 0.30% 0.30% 0.30% 0.30%
Income before income taxes 13% 12% 14% 15% 14% 14% 14% 14% 14% 14% 14%
Provision for/(benefit from) income taxes (See Note 5) 3% 3% 3% 7% -5% 3% 3% 3% 3% 3% 3%
Net income 10% 9% 10% 8% 19% 11% 11% 11% 11% 11% 11%
COMMON SIZE BALANCE SHEET Dec. 27, 2014 Dec. 26, 2015 Dec. 31, 2016 Dec. 30, 2017 Dec. 29, 2018 Dec. 28, 2019
ASSETS
Cash and cash equivalents 9% 13% 12% 13% 11% 7% 7% 7% 7% 7% 7%
Short-term Investments 4% 4% 9% 11% 0% 0% 0% 0% 0% 0% 0%
Restricted Cash 3% 0% 0% 0% 0% 0% 0%
Accounts and notes receivable, net 9% 9% 9% 9% 9% 10% 10% 10% 10% 10% 10%
Inventories 4% 4% 4% 4% 4% 4% 4% 4% 4% 4% 4%
Prepaid expenses and other current assets 3% 3% 1% 2% 1% 1% 1% 1% 1% 1% 1%
Assets, Current 29% 33% 36% 39% 28% 22% 22% 22% 22% 22% 22%
Property, Plant and Equipment, net 24% 23% 23% 22% 23% 25% 25% 25% 25% 25% 25%
Amortizable Intangible Assets, net 2% 2% 2% 2% 2% 2% 2% 2% 2% 2% 2%
Goodwill 21% 20% 20% 18% 19% 20% 20% 20% 20% 20% 20%
Indefinite-lived Intangible Assets (Excluding Goodwill) 18% 17% 17% 16% 18% 19% 19% 19% 19% 19% 19%
Indefinite-Lived Intangible Assets 39% 37% 36% 34% 37% 38% 38% 38% 38% 38% 38%
Equity Method Investments 4% 3% 3% 3% 3% 3% 3% 3% 3% 3% 3%
Deferred Income Tax Assets, Net 6% 6% 6% 6% 6% 6% 6%
Other Assets 1% 1% 1% 1% 1% 4% 4% 4% 4% 4% 4%
Total Assets 100% 100% 100% 100% 100% 100% 100% 100% 100% 100% 100%
LIABILITIES AND EQUITY
Short-term debt obligations 7% 6% 9% 7% 5% 4% 4% 4% 4% 4% 4%
Accounts payable and other current liabilities 18% 19% 19% 19% 23% 22% 22% 22% 22% 22% 22%
Liabilities, Current 26% 25% 29% 26% 29% 26% 26% 26% 26% 26% 26%
Long-Term Debt Obligations 34% 42% 41% 42% 36% 37% 37% 37% 37% 37% 37%
Deferred Income Tax Liabilities, Net 8% 7% 6% 4% 5% 5% 5% 5% 5% 5% 5%
Other Liabilities, Noncurrent 8% 8% 9% 14% 12% 13% 13% 13% 13% 13% 13%
Liabilities 75% 83% 85% 86% 81% 81% 81% 81% 81% 81% 81%
Commitments and contingencies 0.00% 0.00% 0.00%
PepsiCo Common Shareholders’ Equity 0.06% 0.06% 0.06% 0.05% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00%
Common stock, par value 12/3¢ per share (authorized 3,600 shares, issued, net of repurchased common stock at par value: 1,391 and 1,409 shares, respectively) 0.04% 0.03% 0.03% 0.03% 0.03% 0.03% 0.03% 0.03% 0.03% 0.03% 0.03%
Additional Paid in Capital, Common Stock 6% 6% 6% 5% 5% 5% 5% 5% 5% 5% 5%
Retained Earnings (Accumulated Deficit) 70% 72% 71% 66% 77% 79% 79% 79% 79% 79% 79%
Accumulated Other Comprehensive Income (Loss), Net of Tax -15% -19% -19% -16% -19% -18% -18% -18% -18% -18% -18%
Treasury Stock, Value -35% -42% 0% 0% 0% -47% -47% -47% -47% -47% -47%
Stockholders' Equity Attributable to Parent 25% 17% 15% 14% 19% 19% 19% 19% 19% 19% 19%
Stockholders' Equity Attributable to Noncontrolling Interest 0.16% 0.15% 0.14% 0.12% 0.11% 0.10% 0.10% 0.10% 0.10% 0.10% 0.10%
Total Equity 25% 17% 15% 14% 19% 19% 19% 19% 19% 19% 19%
Liabilities and Equity 100% 100% 100% 100% 100% 100% 100% 100% 100% 100% 100%
GROWTH OF INCOME STATEMENT 2015 2016 2017 2018 2019
Net Revenue -5.44% -0.41% 1.16% 1.79% 3.87% 1.60% 1.60% 1.60% 1.60% 1.60%
Cost of sales -7% -2% 2% 2% 3% 2% 2% 2% 2% 2%
Gross profit -4% 1% 0% 2% 5% 2% 2% 2% 2% 2%
Selling, general and administrative expenses -6% 1% -1% 3% 6% 2% 2% 2% 2% 2%
Operating Profit -13% 17% 5% -2% 2% 2% 2% 2% 2% 2%
Other pension and retiree medical benefits (expense)/income -1326% 28% -115% 2% 2% 2% 2% 2%
Interest expense 7% 38% -14% 32% -26% -27% 2% 2% 2% 2%
Interest income and other -31% 86% 122% 25% -35% 2% 2% 2% 2% 2%
Income before income taxes -15% 15% 12% -4% 1% 2% 2% 2% 2% 2%
Provision for/(benefit from) income taxes (See Note 5) -12% 12% 116% -172% -158% 2% 2% 2% 2% 2%
Net income -16% 16% -23% 156% -41% 2% 2% 2% 2% 2%
GROWTH OF BALANCE SHEET 2015 2016 2017 2018 2019
ASSETS
Cash and cash equivalents 48% 1% 16% -18% -37% 2% 2% 2% 2% 2%
Short-term Investments 12% 139% 28% -97% -16% 2% 2% 2% 2% 2%
Restricted Cash -100% ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0!
Accounts and notes receivable, net -3% 4% 5% 2% 10% 2% 2% 2% 2% 2%
Inventories -13% 0% 8% 6% 7% 2% 2% 2% 2% 2%
Prepaid expenses and other current assets -13% -51% 70% -59% 18% 2% 2% 2% 2% 2%
Assets, Current 11% 15% 17% -29% -19% 2% 2% 2% 2% 2%
Property, Plant and Equipment, net -5% 2% 4% 2% 10% 2% 2% 2% 2% 2%
Amortizable Intangible Assets, net -12% -3% 3% 30% -13% 2% 2% 2% 2% 2%
Goodwill -5% 2% 2% 0% 5% 2% 2% 2% 2% 2%
Indefinite-lived Intangible Assets (Excluding Goodwill) -7% 3% 3% 13% 3% 2% 2% 2% 2% 2%
Indefinite-Lived Intangible Assets -6% 2% 3% 6% 4% 2% 2% 2% 2% 2%
Equity Method Investments -14% -16% 5% 18% 11% 2% 2% 2% 2% 2%
Deferred Income Tax Assets, Net -0% 2% 2% 2% 2% 2%
Other Assets -13% -15% 44% -17% 296% 2% 2% 2% 2% 2%
Total Assets -1% 5% 9% -3% 1% 2% 2% 2% 2% 2%
LIABILITIES AND EQUITY
Short-term debt obligations -20% 69% -20% -27% -27% 2% 2% 2% 2% 2%
Accounts payable and other current liabilities 4% 5% 5% 21% -3% 2% 2% 2% 2% 2%
Liabilities, Current -3% 20% -3% 8% -8% 2% 2% 2% 2% 2%
Long-Term Debt Obligations 23% 3% 12% -16% 3% 2% 2% 2% 2% 2%
Deferred Income Tax Liabilities, Net -7% -11% -27% 8% 17% 2% 2% 2% 2% 2%
Other Liabilities, Noncurrent 2% 13% 69% -19% 9% 2% 2% 2% 2% 2%
Liabilities 9% 8% 10% -8% 1% 2% 2% 2% 2% 2%
Commitments and contingencies
PepsiCo Common Shareholders’ Equity
Common stock, par value 12/3¢ per share (authorized 3,600 shares, issued, net of repurchased common stock at par value: 1,391 and 1,409 shares, respectively)
Additional Paid in Capital, Common Stock -1% 0% -2% -1% -2% 2% 2% 2% 2% 2%
Retained Earnings (Accumulated Deficit) 3% 4% 1% 13% 3% 2% 2% 2% 2% 2%
Accumulated Other Comprehensive Income (Loss), Net of Tax 25% 5% -6% 16% -5% 2% 2% 2% 2% 2%
Treasury Stock, Value 17% -100%
Stockholders' Equity Attributable to Parent -31% -7% -2% 31% 2% 2% 2% 2% 2% 2%
Stockholders' Equity Attributable to Noncontrolling Interest -3% -3% -12% -9% -2% 2% 2% 2% 2% 2%
Total Equity -31% -7% -2% 33% 2% 2% 2% 2% 2% 2%
Liabilities and Equity -1% 5% 9% -3% 1% 2% 2% 2% 2% 2%
Useful Ratios & Other Figures
PM (Net Profit Margin = NI/Revenue) 9% 10% 8% 19% 11% 11% 11% 11% 11% 11%
TAT (Total Asset Turnover = Revenue/Total Assets) 0.9051057172 0.8545244251 0.7960127312 0.8327452091 0.855042204 0.855042204 0.855042204 0.855042204 0.855042204 0.855042204
EM (Equity Multiplier = Total Assets/Total Equity) 5.7911055694 6.562193053 7.2674619798 5.3176277222 5.2829566855 5.2829566855 5.2829566855 5.2829566855 5.2829566855 5.2829566855
ROE (Return on Equity = NI/Total Equity = PM*TAT*EM) 45.7% 57.0% 44.7% 86.0% 49.5% 49.5% 49.5% 49.5% 49.5% 49.5%
ROA (Return on Assets = NI/Total Assets = PM*TAT) 7.9% 8.7% 6.2% 16.2% 9.4% 9.4% 9.4% 9.4% 9.4% 9.4%
d (Dividend Payout Ratio = Dividends/NI … note: result is POSTIVE) 73% 66% 91% 39% 72% 0% 0% 0% 0% 0%
b (Retention Ratio = 1-d) 27% 34% 9% 61% 28% 100% 100% 100% 100% 100%
SGR (Sustainable Growth Rate = b*ROE) 12% 19% 4% 52% 14% 49% 49% 49% 49% 49%
IGR (Internal Growth Rate = b*ROA) 2% 3% 1% 10% 3% 9% 9% 9% 9% 9%
TIE (Times Interest Earned Ratio = EBIT/Interest) 817% 694% 1059% 754% 996% 1522% 1523% 1523% 1523% 1523%
DOL (Degree of Operating Leverage [point estimate] = Gross Profit/OpInc) 411% 353% 338% 349% 360% 360% 360% 360% 360% 360%
Days of sales in cash ( = Cash/[Revenue/365]) 5265% 5323% 6096% 4923% 2994% 2994% 2994% 2994% 2994% 2994%
Inventory Turnover ( = COGS/Avg Inventory … note: Avg Inventory = [BOY+EOY]/2 ) 980% 1037% 1016% 967% 932% 910% 910% 910% 910% 910%
Receivables Turnover (= Sales/Avg Receivables … note: Avg Receivables = [BOY+EOY]/2 ) 964% 956% 926% 913% 898% 865% 865% 865% 865% 865%
Purchases ( = COGS + Δ inventory) 2830800% 2822500% 2902000% 2956200% 3034200% 3066752% 3115820% 3165673% 3216324% 3267785%
Payables Turnover ( = Purchases/Avg Payables … note: Avg Payables = [BOY+EOY]/2 ) 213% 203% 198% 178% 170% 173% 173% 173% 173% 173%
Days in inventory ( = 365/Inventory Turnover) 3724% 3520% 3593% 3773% 3916% 4012% 4012% 4012% 4012% 4012%
Days in receivables ( = 365/Receivables Turnover) 3788% 3816% 3941% 3998% 4066% 4218% 4218% 4218% 4218% 4218%
Operating cycle ( = Days in Inventory + Days in Receivables) 7512% 7336% 7534% 7772% 7982% 8229% 8229% 8229% 8229% 8229%
Days in payables ( = 365/Paybales Turnover) 17099% 17943% 18401% 20452% 21444% 21044% 21044% 21044% 21044% 21044%
Cash cycle ( = Operating Cycle - Days in Payables) -9587% -10607% -10866% -12680% -13462% -12815% -12815% -12815% -12815% -12815%

Costs of Capital

Costs of Capital
1.91%
Michael Dimond: from treasury.gov for date of interest		 Rf (Risk-free Rate) source: https://www.treasury.gov/resource-center/data-chart-center/interest-rates/Pages/TextView.aspx?data=longtermrateYear&year=2020 TREASURY 20-Yr CMT on filing date
5.50%
Michael Dimond: Estimated. Consider the implied market return (Rf+MRP) to make sure this is reasonable.		 Market Risk-Premium (MRP)
21.00%
Michael Dimond: Same rate used in NOPAT computation for subject company.		 Tax Rate
38,730.4
Michael Dimond: From "Debt" tab		 MV Debt
202,164.8
Michael Dimond: Market Capitalization as of the date of interest		 MV Equity
$ 145.49 Adjusted Close on filing date
1,389,544,618 Shares outstanding at time of filing
0.6801
Michael Dimond: From "Comparable Firms" tab		 Unlevered Beta
5.65%
Michael Dimond: Computed using CAPM and Unlevered Beta		 Ku (Unlevered Cost of Capital)
0.7831
Michael Dimond: Computed using Hamada's formula: Bu*(1+D/E(1-t))
Michael Dimond: from treasury.gov for date of interest		 Levered Beta ("Bottom-up Beta")
6.22%
Michael Dimond: Computed using CAPM and Levered Beta
Michael Dimond: Estimated. Consider the implied market return (Rf+MRP) to make sure this is reasonable.		 Ke (Cost of Equity … Common Equity)
2.53%
Michael Dimond: From "Debt" tab
Michael Dimond: Same rate used in NOPAT computation for subject company.
Michael Dimond: From "Debt" tab
Michael Dimond: Market Capitalization as of the date of interest		 Kd (Cost of Debt … Before Tax)
5.54%
Michael Dimond: WACC = we*Ke + wd*Kd*(1-t) where: we = MV Equity / (MV Equity + MV Debt) wd = MV Debt / (MV Equity + MV Debt) If preferred stock exists, include factors for weight of preferred stock and required return for preferred stock.
Michael Dimond: From "Comparable Firms" tab
Michael Dimond: Computed using CAPM and Unlevered Beta		 WACC (Weighted Average Cost of Capital)
Remember: Rf < Kd < WACC < Ku < Ke
1.91% 2.53% 5.54% 5.65% 6.22%
5.62%

Debt

Debt
2.53%
Michael Dimond: median from source: https://finra-markets.morningstar.com/BondCenter/Results.jsp?debtOrAssetClass=3%2C6&issuerName=&traceOrCusipOrBloomberg=PEP&state=&cusipOrFinraSymbol=&showResultsAs=B&spdsType=&treasuryOrAgencyType=&subProductType=&debtInstrumentType=&securityDescription=&industryGroup=&convertibleflag=&industrySubtype=&sourceOfPayment=&proceedsUse=&issuingAgency=&collateralOrAssetType=&couponType=&detailedCouponType=&couponRate=&interestFrequency=&interestType=&maturityDate=&moodysRating=&standardAndPoorsRating=&traceInvestmentGrade=&beginningOrNextCallDate=&poolNumber=&masterDealId=&trancheId=&tradeDate=&tradeYield=&tradePrice=&agency=&productDescription=&maturityCode=&settlementMonth=&productType=&amortizationType=&maturity=&coupon=&weightedAverageCoupon=&weightedAverageMaturity=&weightedAverageLoan=&averageLoanSize=&loanToValueRatio=&SubProductType=ABS&subProductAssetDescription=&rule144aindicator=&showAdvancedSearch=hide&postData=%7B%22Keywords%22%3A%5B%7B%22Name%22%3A%22debtOrAssetClass%22%2C%22Value%22%3A%223%2C6%22%7D%2C%7B%22Name%22%3A%22showResultsAs%22%2C%22Value%22%3A%22B%22%7D%2C%7B%22Name%22%3A%22traceOrCusipOrBloomberg%22%2C%22Value%22%3A%22PEP%22%7D%5D%7D		 Yield on Bonds
We could also use "synthetic ratings" to estimate the cost of debt…
based on TIE (Times Interest Earned ratio)
based on Altman Z-Score
2.53% Cost of Debt
38,730 Market Value of Debt
Balance Sheet Debt
37,094 Damodaran's method
1,135 Interest Expense from Income Statement
32,068 BV Debt from Balance Sheet
20 Assumed Years to maturity
Operating Leases
1,636 PV of Operating Lease Expenses
Expected Op. Lease Payments:
Y1 Y2 Y3 Y4 Y5 Y6
501 327 327 150 150 308
Based on data listed under "Credit Facilities and Long-Term Contractual Commitments" in Pepsico's 10-k

Comparable Firms

Comparable Firms
Ticker P/E ratio P/S ratio EV/EBITDA ratio
Michael Dimond: Enterprise Value to EBITDA		 Beta Debt Equity (Market Cap) Tax Rate Unlevered Beta
Coca Cola KO 18.78 5.06 15.79 0.55 50.42
Michael Dimond: Given in billions		 194.46
Michael Dimond: Given in billions		 12.91% 0.45
Keurig Dr Pepper KDP 34.22 3.59 18.78 1.5252 15.27 39.96 25.83% 1.19
Monster Beverage Corp. MNST 33.36 8.71 23.28 1.05 0.03
Michael Dimond: Converted from millions to billions		 36.36 23.33% 1.05
Anheuser-Busch InBev SA/NV BUD 37.69 1.90 18.45 1.12 103.04 95.84 38.26% 0.67
Molson Coors Beverage Co. TAP 35.14 0.72 11.49 0.87 9.93 7.53 113.08% 1.05
Campbell's Soup CPB 30.03 1.79 14.90 0.41 6.95 14.88 23.86% 0.30
Conagra CAG 20.92 1.53 14.27 0.8061 9.75 15.90 23.20% 0.55
Kellogg Co. K 21.36 1.63 13.95 0.58 9.11 21.88 24.64% 0.44
Kraft Heinz KHC 19.81 1.50 13.21 1.01 32.78 37.75 26.00% 0.61
Mondelez Int'l MDLZ 20.01 2.84 19.38 0.62 20.24 72.56 -1.23% 0.48
Average of Comparable Firms 27.13 2.93 16.35 0.68
Median of Comparable Firms 25.70 1.85 15.34 0.58