Excel Project

profilevk4queen
PEP-Student1.xlsx
Home>Business & Finance homework help>Excel Project

PEP

Dec. 26, 2015 Dec. 31, 2016 Dec. 30, 2017 Dec. 29, 2018 Dec. 28, 2019 2020 2021 2022 2023 2024 Beyond
Assumptions
Terminal Growth Rate
P/E Multiples Valuation
Benchmark P/E
Earnings available to common shareholders
Equity intrinsic value
Shares remember to adjust for millions!
Implied price per share
P/S Multiples Valuation
Benchmark P/S
Sales Revenue
Equity intrinsic value
Shares
Implied price per share
EV/EBITDA Multiples Valuation
Benchmark EV/EBITDA
EBITDA
Enterprise value
Market value of non-operating assets
Market value of debt
Equity intrinsic value
Shares
Implied price per share
Equity Cash Flow Model: Discounting Equity Cash Flows at Cost of Equity
Ke (Cost of Equity)
1 2 3 4 5
Equity cash flow (FCFE)
Terminal value
PV
Equity value of operations
Market value of non-operating assets
Equity intrinsic value
Shares
Implied price per share
Corporate Valuation Model: Discounting Free Cash Flows at WACC
WACC (Weighted Average Cost of Capital)
1 2 3 4 5
Free cash flow (FCFF)
Terminal value
PV
Value of operations
Market value of debt
Market value of non-operating assets
Equity intrinsic value
Shares
Implied price per share
Adjusted Present Value Model: Discounting Unlevered Cash Flows at Unlevered Cost of Capital
Ku (Unlevered Cost of Capital)
1 2 3 4 5
FCFF
Terminal value
PV
Unlevered value of operations
1 2 3 4 5
Interest tax savings = IntExp * tax Rate
Terminal value
PV
Value of tax shield
Market value of debt
Market value of non-operating assets
Equity intrinsic value
Shares
Implied price per share
Cash Flow Computations
Year Ended… Dec. 26, 2015 Dec. 31, 2016 Dec. 30, 2017 Dec. 29, 2018 Dec. 28, 2019 2020 2021 2022 2023 2024
Shares Outstanding
Figures in…
OpInc
Tax Rate (t)
NOPAT = OpInc x (1-t)
Dep & Amort Exp
OCF = NOPAT + Dep & Amort
ΔNOWC
ΔGFA
FCFF = OCF - ΔNOWC - ΔGFA
Interest Expense, net
FCFF
ΔDebt
Int(1-t)
PfdCF
FCFE = FCFF + ΔDebt - Int(1-t) - PfdCF
FCFF
Int(t)
UCF = FCFF + Int(t)
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
Cost of sales 30884 28731 28222 28796 29381 30132
Gross profit 35799 34325 34577 34729 35280 37029
Selling, general and administrative expenses 26126 24613 24773 24453 25170 26738
Venezuela impairment charges 0 1359 0 0 0 0
Operating Profit 9581 8353 9804 10276 10110 10291
Other pension and retiree medical benefits (expense)/income -19 233 298 -44
Interest expense -909 -970 -1342 -1151 -1525 -1135
Interest income and other 85 59 110 244 306 200
Income before income taxes 8757 7442 8553 9602 9189 9312
Provision for/(benefit from) income taxes (See Note 5) 2199 1941 2174 4694 -3370 1959
Net income 6558 5501 6379 4908 12559 7353
Less: Net income attributable to noncontrolling interests 45 49 50 51 44 39
Net Income Attributable to PepsiCo 6513 5452 6329 4857 12515 7314
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
Short-term Investments 2592 2913 6967 8900 272 229
Restricted Cash 1997 0
Accounts and notes receivable, net 6651 6437 6694 7024 7142 7822
Inventories 3143 2720 2723 2947 3128 3338
Prepaid expenses and other current assets 2143 1865 908 1546 633 747
Assets, Current 20663 23031 26450 31027 21893 17645
Property, Plant and Equipment, net 17244 16317 16591 17240 17589 19305
Amortizable Intangible Assets, net 1449 1270 1237 1268 1644 1433
Goodwill 14965 14177 14430 14744 14808 15501
Indefinite-lived Intangible Assets (Excluding Goodwill) 12639 11811 12196 12570 14181 14610
Indefinite-Lived Intangible Assets 27604 25988 26626 27314 28989 30111
Equity Method Investments 2689 2311 1950 2042 2409 2683
Deferred Income Tax Assets, Net 4364 4359
Other Assets 860 750 636 913 760 3011
Total Assets 70509 69667 73490 79804 77648 78547
LIABILITIES AND EQUITY
Short-term debt obligations 5076 4071 6892 5485 4026 2920
Accounts payable and other current liabilities 13016 13507 14243 15017 18112 17541
Liabilities, Current 18092 17578 21135 20502 22138 20461
Long-Term Debt Obligations 23821 29213 30053 33796 28295 29148
Deferred Income Tax Liabilities, Net 5304 4959 4434 3242 3499 4091
Other Liabilities, Noncurrent 5744 5887 6669 11283 9114 9979
Liabilities 52961 57637 62291 68823 63046 63679
Commitments and contingencies
Preferred Stock, no par value 41 41 41 41 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
Additional Paid in Capital, Common Stock 4115 4076 4091 3996 3953 3886
Retained Earnings (Accumulated Deficit) 49092 50472 52518 52839 59947 61946
Accumulated Other Comprehensive Income (Loss), Net of Tax -10669 -13319 -13919 -13057 -15119 -14300
Treasury Stock, Value -24985 -29185 -36769
Stockholders' Equity Attributable to Parent 17578 12068 11246 11045 14518 14786
Stockholders' Equity Attributable to Noncontrolling Interest 110 107 104 92 84 82
Total Equity 17548 12030 11199 10981 14602 14868
Liabilities and Equity 70509 69667 73490 79804 77648 78547
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
Depreciation and amortization 2416 2368 2369 2399 2432
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
Net Revenue
Cost of sales
Gross profit
Selling, general and administrative expenses
Operating Profit
Other pension and retiree medical benefits (expense)/income
Interest expense
Interest income and other
Income before income taxes
Provision for/(benefit from) income taxes (See Note 5)
Net income
COMMON SIZE BALANCE SHEET
ASSETS
Cash and cash equivalents
Short-term Investments
Restricted Cash
Accounts and notes receivable, net
Inventories
Prepaid expenses and other current assets
Assets, Current
Property, Plant and Equipment, net
Amortizable Intangible Assets, net
Goodwill
Indefinite-lived Intangible Assets (Excluding Goodwill)
Indefinite-Lived Intangible Assets
Equity Method Investments
Deferred Income Tax Assets, Net
Other Assets
Total Assets
LIABILITIES AND EQUITY
Short-term debt obligations
Accounts payable and other current liabilities
Liabilities, Current
Long-Term Debt Obligations
Deferred Income Tax Liabilities, Net
Other Liabilities, Noncurrent
Liabilities
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
Retained Earnings (Accumulated Deficit)
Accumulated Other Comprehensive Income (Loss), Net of Tax
Treasury Stock, Value
Stockholders' Equity Attributable to Parent
Stockholders' Equity Attributable to Noncontrolling Interest
Total Equity
Liabilities and Equity
GROWTH OF INCOME STATEMENT
Net Revenue
Cost of sales
Gross profit
Selling, general and administrative expenses
Operating Profit
Other pension and retiree medical benefits (expense)/income
Interest expense
Interest income and other
Income before income taxes
Provision for/(benefit from) income taxes (See Note 5)
Net income
GROWTH OF BALANCE SHEET
ASSETS
Cash and cash equivalents
Short-term Investments
Restricted Cash
Accounts and notes receivable, net
Inventories
Prepaid expenses and other current assets
Assets, Current
Property, Plant and Equipment, net
Amortizable Intangible Assets, net
Goodwill
Indefinite-lived Intangible Assets (Excluding Goodwill)
Indefinite-Lived Intangible Assets
Equity Method Investments
Deferred Income Tax Assets, Net
Other Assets
Total Assets
LIABILITIES AND EQUITY
Short-term debt obligations
Accounts payable and other current liabilities
Liabilities, Current
Long-Term Debt Obligations
Deferred Income Tax Liabilities, Net
Other Liabilities, Noncurrent
Liabilities
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
Retained Earnings (Accumulated Deficit)
Accumulated Other Comprehensive Income (Loss), Net of Tax
Treasury Stock, Value
Stockholders' Equity Attributable to Parent
Stockholders' Equity Attributable to Noncontrolling Interest
Total Equity
Liabilities and Equity
Useful Ratios & Other Figures
PM (Net Profit Margin = NI/Revenue)
TAT (Total Asset Turnover = Revenue/Total Assets)
EM (Equity Multiplier = Total Assets/Total Equity)
ROE (Return on Equity = NI/Total Equity = PM*TAT*EM)
ROA (Return on Assets = NI/Total Assets = PM*TAT)
d (Dividend Payout Ratio = Dividends/NI … note: result is POSTIVE)
b (Retention Ratio = 1-d)
SGR (Sustainable Growth Rate = b*ROE)
IGR (Internal Growth Rate = b*ROA)
TIE (Times Interest Earned Ratio = EBIT/Interest)
DOL (Degree of Operating Leverage [point estimate] = Gross Profit/OpInc)
Days of sales in cash ( = Cash/[Revenue/365])
Inventory Turnover ( = COGS/Avg Inventory … note: Avg Inventory = [BOY+EOY]/2 )
Receivables Turnover (= Sales/Avg Receivables … note: Avg Receivables = [BOY+EOY]/2 )
Purchases ( = COGS + Δ inventory)
Payables Turnover ( = Purchases/Avg Payables … note: Avg Payables = [BOY+EOY]/2 )
Days in inventory ( = 365/Inventory Turnover)
Days in receivables ( = 365/Receivables Turnover)
Operating cycle ( = Days in Inventory + Days in Receivables)
Days in payables ( = 365/Paybales Turnover)
Cash cycle ( = Operating Cycle - Days in Payables)

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