Excel assignment
vk4queen
CostsofCapital-Student.xlsxCosts of Capital
| Costs of Capital | |||||
| 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 | ||||
| Market Risk-Premium (MRP) | |||||
| Tax Rate | |||||
| MV Debt | |||||
| MV Equity | |||||
| Adjusted Close on filing date | |||||
| Shares outstanding at time of filing | |||||
| Unlevered Beta | |||||
| Ku (Unlevered Cost of Capital) | |||||
|
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") | |||
|
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) | |||
|
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) | |
|
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 | |||||
| 0.00% | 0.00% | 0.00% | 0.00% | 0.00% | |
| ERROR:#DIV/0! |
Debt
| Debt | |||||||||
|
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 | |||||||||
| Cost of Debt | |||||||||
| Market Value of Debt | |||||||||
| Balance Sheet Debt | |||||||||
| Damodaran's method | |||||||||
| Interest Expense from Income Statement | |||||||||
| BV Debt from Balance Sheet | |||||||||
| Assumed Years to maturity | |||||||||
| Operating Leases | |||||||||
| PV of Operating Lease Expenses | |||||||||
| Expected Op. Lease Payments: | |||||||||
| Y1 | Y2 | Y3 | Y4 | Y5 | Y6 | ||||
| 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% | |
| Keurig Dr Pepper | KDP | 34.22 | 3.59 | 18.78 | 1.53 | 15.27 | 39.96 | 25.83% | |
| 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% | |
| Anheuser-Busch InBev SA/NV | BUD | 37.69 | 1.90 | 18.45 | 1.12 | 103.04 | 95.84 | 38.26% | |
| Molson Coors Beverage Co. | TAP | 35.14 | 0.72 | 11.49 | 0.87 | 9.93 | 7.53 | 113.08% | |
| Campbell's Soup | CPB | 30.03 | 1.79 | 14.90 | 0.41 | 6.95 | 14.88 | 23.86% | |
| Conagra | CAG | 20.92 | 1.53 | 14.27 | 0.81 | 9.75 | 15.90 | 23.20% | |
| Kellogg Co. | K | 21.36 | 1.63 | 13.95 | 0.58 | 9.11 | 21.88 | 24.64% | |
| Kraft Heinz | KHC | 19.81 | 1.50 | 13.21 | 1.01 | 32.78 | 37.75 | 26.00% | |
| Mondelez Int'l | MDLZ | 20.01 | 2.84 | 19.38 | 0.62 | 20.24 | 72.56 | -1.23% | |
| Average of Comparable Firms | |||||||||
| Median of Comparable Firms |
Regressed Beta
| Beta Regression | ||||||
| Date | S&P500 Adj Close | % change | Date | Subject Co. Adj. Close | % change | |
