Assignment 3: Estimate a Price for Coca-Cola (KO) Common Stock Using a Two-Stage DDM, FCFFM and RV Coca-Cola (KO) is the world's largest producer of soft-drink concentrates, syrups, and juices.Its...

Assignment 3:
Estimate a Price for Coca-Cola (KO) Common Stock Using a Two-Stage DDM, FCFFM and RV

Coca-Cola (KO) is the world's largest producer of soft-drink concentrates, syrups, and juices.Its soft-drink brands include Coke, Diet Coke, Cherry Coke, Sprite, Tab, Nestea, and Barq's.The firm sells about 59% of its concentrates and syrups to company-owned and independent bottlers in the United States and abroad, who distribute them to end users.Coca-Cola also makes fruit juices sold under names like Minute Maid.For the first time in its history, Coca-Cola launched a lemon-flavored alcoholic drink in the southwestern island of Kyushu in Japan.Coca-Cola’s drink, called Lemon-Do, will be available with 3%, 5%, and 7% alcohol, placing itself in a highly competitive market comprised of giants such as Suntory, Kirin, and Asahi.

All ten (10) questions are worth 10 points each (10 x 10 = 100 points).You MUST show all of your calculation in the questions below is order to receive credit for any numerical answers.

Two-Stage Dividend Discount Model

Follow the Two-Stage DDM method for estimating stock price as presented in class and demonstrated in the Two-Stage DDM.xls template.If you need a further review, remember that you studied the Two-Stage DDM in F371.

Q1.

TheFirst Stepin using the Two-Stage DDM is to find themost recent annual dividend, D(0),for KO.Next, estimate an annual expected first-stage growth rate in dividendsduring stage 1,g1,ANDthenumber of years, N, that you expect the first stage to persist.Two (2) popular methods for estimating g1that were discussed in class are:

A. (Method 1):Form anestimate for g1,the expected annual growth rate in dividendsbased on the average annual historical growth in dividends over the past five (5) years.
The assumption in Method 1is that the historical average growth rate in dividends over the past five years will be a good estimate for the future growth rate, g1, over the number of years, N, that you have assumed for stage 1. LISTyour result.

B. (Method 2):Use the formula provide in the AAII article“Methods for Valuing a Stock”for estimatingthe sustainable growth rate* in EACH of the past five (5) years(formula is shown below) thentake a simple average of these five annual sustainable growth estimatesin order to arrive at a second estimate for g1.

*Sustainable Growth Rate (g1) = ROE x retention ratio

The assumption in Method 2is that the historical average sustainable growth rate, which is essentially the expected sustainable growth rate in net income for a given level of ROE and a given retention ratio, is a good estimate for g1 (the growth rate in dividends).The sustainable growth rate formula assumes the capital structure remains constant and no new common stock is issued.You were introduced to this concept in F371.LIST your result.

C. SELECTtheONE (1) single estimate from Q1A and Q1Babove that YOU believe is themost appropriate value of g1for valuing KO using the Two-Stage DDM.Further, LISTyour selected value forg1 ANDthenumber of years, N,you expect g1 to persist.Provide a short paragraph (at least three sentences)thatEXPLAINSyour reasoning for selecting these values ofg1 AND N.

*Key Learning Point:Realize that any estimate of the future growth in dividends will never equal the actual growth in dividends.All financial valuation estimates are subject toforecast error, which is also referred to asestimation risk.

Q2.

TheSecond Stepin using the Two-Stage DDM is to estimate anannual required return on equity,Re.For this assignment,assume that Re remains constant in stage 1 and stage 2.Three (3) popular methodsfor estimating Re that were discussed in class are:

A. (Method 1):Calculate thearithmetic averageof the historical annual total returns over thepast five (5) years.
The assumption in Method 1is that the historical average annual return is a good estimate of future annual returns. This is not a law of nature.It is an assumption.To do this you will need to calculate annual total returns from the most recent 5-year period usingMonthly Historical Prices.Calculate the annual total returns based on theDecember-ending Adj Close*price found under Historical Prices on thewww.finance.yahoo.comwebsite.

Annual Total Return = [Adj Close* Price December (t) – Adj Close* Price December (t-1)] / Adj Close* Price December (t-1)

Example:

Annual Total Return 2019 = [Adj Close* Price December 2019 – Adj Close* Price December 2018] / Adj Close* Price December 2018

The Adj Close* priceis adjusted for dividends and splits, so this price is all that you need to calculate a total return.LIST your result.

B. (Method 2):Use theCAPMto estimate Re.The assumption in Method 2is that the CAPM is an appropriate required return generating model.We know, however, there are other models, such as the Fama-French Three-Factor Model.

CAPM:E(Re) = Rf + Beta x [E(Rm) – Rf]

As you know, the CAPM requiresestimates of Rf, Beta and an appropriate expected Market Risk Premium (MRP = E(Rm) – Rf) over an assumed infinite life of the firm.Using only recent (short-term) estimates of Rf, Beta and the MRP may not be appropriate for long-term valuation.Once again, more estimation risk!For this assignment,ASSUME that:

i)Rf is somewhere between 1.00% and 3.00%
ii)Beta for KO is somewhere between .80 and 1.10
iii) MRP = [E(Rm) – Rf) is somewhere between 4.00% and 7.00%

*IMPORTANT:Keep in mind that you are estimating a value for Re the will hold forever (or at least over the life of the firm).As such, current values for Rf, Beta and MRP MAY NOT BE APPROPRIATE for the long-term. You will see the effect of estimation risk in Q4.LIST and EXPLAIN your select values for Rf, Beta and MRP then list your estimate for Re based on the CAPM.

C. (Method 3)Add a3% equity risk premium (ERP) to the Yield to Maturity (YTM) on a KO long-term bond that has AT LEAST 20 years to maturity.This is often called theBond Yield plus Risk Premium (BYPRP)method.The assumption in Method 3is that the required return on equity, Re, MUST be greater than the required return on debt, Rd, for any firm.

If KO does not have a long-term (20-year+) bond outstanding, then add 3% to the YTM on a 20-year+ corporate bond with the same bond rating (i.e. A, AA, or AAA).You must find the YTM on a KO20-year+ bondor theYTM on a comparable corporate 20-year+ bond.

IMPORTANT:Realize that any risky 20-year corporate bond must have a YTM that is greater than the YTM on a risk-free 20-year Treasury bond.So, DO NOT list the YTM on a KO or risk equivalent 20+ corporate bond as being less than the YTM on a 20-year Treasury bond!

Websites for bond information:

1.FINRA Bond Market Data



www.finra.org/marketdata

FINRA is an independent regulator for the securities industry.In the bonds section of the Market Data Center, you can get general bond market information, as well as price information with intraday transaction prices (delayed 15 minutes) for corporate bonds, municipal bonds, U.S. Treasury and government agency bonds.A bond screen lets you search by issuer name, CUSIP, type, maturity, yield, coupon type, trade activity and other criteria.

Click onBonds.

Click onSearch.

Click onCorporatein the Quick Search box.

TypeCoca Colain the Issuer Name box (not KO).

2. Business Insider

https://markets.businessinsider.com/bonds

Click on the link above then scroll down the page toBond Finder

Fill in the following information in the appropriate box:

Bond TypeCorporate

Bond RatingA1

CountriesUSA

*Given the coupon, price and maturity date you can solve for the yield to maturity (YTM) as you learned in F371 and as we discussed in the Module “Bond Pricing and Interest Rate Risk”.

LIST and EXPLAINyour estimate for Re based on theBYPRPmethod.

D. SELECT the ONE (1) single estimate of Re from Q2A – Q2Dabove that YOU believe is the most appropriate for valuing KO using the Two-Stage DDMand provide a short paragraph (at least three sentences) that

EXPLAINS your reasoning.

Q3.Estimate a second stage (also called the stable stage) growth rate in dividends, g2, for use in the Two-Stage DDM.Your estimate for g2 is the growth rate that you expect will continue “forever” in the second stage.Your estimate forg2 should not be much greater than the expected future growth in the overall economy(expected growth in GDP).Why?LISTyour estimate for g2 and provide a short paragraph thatEXPLAINSyour reasoning AND the source of your estimate for long-term growth in GDP.Finally,estimate andLISTa price per share for KO using your estimates for g1, N, Re, g2 and KO’scurrent annual dividend(most recent quarterly dividend x 4)as D(0)in the Two-Stage DDM(submit your spreadsheet).

Q4.PerformSensitivity Analysis (SA)on your estimated price per share in by varyingEACHof your three (3) estimated input parameters(g1, g2 and Re)by +/- 2.0 percent in .5 percent incrementswhile holding the other two parameters constant.

Use the Data Table command in Excel to create for the Sensitivity Analysis.

https://www.techrepublic.com/blog/windows-and-office/create-an-excel-data-table-to-compare-multiple-results/

Provide two (2) short paragraphs (at least three sentences in each paragraph) thatLISTSandDISCUSSESyour results.That is, describe in words how your value estimates change as you change your growth estimates, discount rate estimate and your estimate for N.Always remember that estimates are nothing more than estimates and subject to forecast error.This is why 1000 analysts can easily arrive at 1000 different estimates of value.

Two-Stage Free Cash Flow to the Firm Model (FCFFM)

Use the Two-Stage FCFFM valuation method presented in class in order to answer the following questions.

Q5.Calculate KO’s FCFFfor EACH of the past five (5) yearsusing the financial statement data found in KO’s annual reports and using the FCFFM.xls template.The template will calculate the annual FCFF value for you, but you MUST insert the correct input values for Depreciation & Amortization, Capital Investment, Capital Sales, etc.

Note:msn.money has net Cap Exp:



https://www.msn.com/en-us/money/stockdetails/financials/fi-126.1.KO.NYS

As in Q1 ,estimate first-stage growth, g1,in the Two-Stage FCFFM AND thenumber of years, N,that you expect theg1 level of growth in FCFFto persistby looking atthe arithmetic average historicTRENDin FCFF over the past five (5) years.You can use the recent arithmetic average growth(positive or negative)in FCFF over the past five years as your estimate for g1 or you may revise it based on any other information you feel is relevant (new products, increased competition, etc.).LISTyourestimate for g1 and number of years, N,you expect it to persist.Provide a short paragraph (at least three sentences) thatEXPLAINSyour reasoning.

Q6.Using your estimates of Re and Rd from Assignment 5 (DDM Valuation),estimateKO’s WACCas the appropriate discount rate in the FCFF Model.
As you learned in F371, you should use market value weights for both debt and equity and not book value weights when calculating a firm’s WACC.The problem here is that you do not have the time or resources to calculate KO’s market value of debt or the average YTM on that debt.Therefore, make sure that you include the reported book value of debt as shown on the most current balance sheet statement as a proxy for the market value of debt.Assume that KO’s WACC remains constant in stage 1 and stage 2.LISTyour estimate for KO’s WACC.

*The FCFFM Excel template will do this for you, but you must enter your estimates of Re, Rd, the market value of KO’s equity and the book value of KO’s long-term debt and other long-term liabilities.Make sure you pay attention when we discuss this in class!

Q7.
Assume that the average annual FCFF from Q5 is goodestimate of KO’sFCFF(0)for use in the FCFM.LISTyour estimate for FCFF(0) then estimate andLISTa price per share for KO using your estimates for g1, N, WACC, g2 and FCFF(0) in the Two-Stage FCFFM(submit your spreadsheet).Use the same estimate second stage or stable stage growth, g2, in the FCFFM that you selected for the DDM.

Key Learning Point:Unlike using the current dividend as D(0) in the DDM, the current year’s FCFF may not be appropriate for using a base FCFF(0) value.Remember, your estimate ofFCFF(0) is the value on which you believe all future annual FCFF estimates are basedgiven your estimates of g1 and g2 (notice how many times the word “estimates” is used).You cannot avoid estimation risk!

*IMPORTANT:
There is nothing magic about using a 5-year historical average as a starting point for estimating future values for input parameters.Using the most recent years of financial data does NOT prevent you from using additional historical financial information when forming your expectations about the future.You should realize, however, that if you simply use only the most recent annual statement (only one year of data), your estimates for future cash flows (particularly free cash flow) will be biased by the most recent one-year period.This is why you should consider using an average of recent time series of historical values for your base FCFF(0) estimate.

As we discussed in class, current and historical cash flows can be observed.Today's stock value, however, is a function of "EXPECTED" (estimated) future cash flows.It should be obvious from the sensitivity analysis results that SMALL variations in expected growth rates and discount rates can have a LARGE impact on present value estimates.
One thousand analysts can easily arrive at 1000 different valuation estimates because they will all form different expectations about future growth rates, discount rates, etc.As you progress through the course, always remember that financial valuation is subject to estimation risk!

Q8.PerformSensitivity Analysis (SA)on your estimated price per share in by varyingEACHof your three (3) input parameters(g1, g2 and WACC)by +/- 2.0 percent in .5 percent increments while holding the other two parameters constant.Always remember that estimates are nothing more than estimates and subject to forecast error.This is why 1000 analysts can easily arrive at 1000 different estimates of value.LISTandDISCUSSyour results.That is, describe in words how your value estimates change as you change your growth and discount rate estimates.

Relative Valuation

Q9.Apply Relative Valuation methods to Coca-Cola (KO) using the followingthree (3) popular relative valuation ratios - Price/Earnings, Price/Book, Price/Sales (call these three ratios Price/”X” ratios).
Select at least five (5) comparable firms that are in the same industry as KO and use the average P/X ratio for the group of comparable firms in your valuation analysis.The P/X ratios for the template can be found by using the screener on theFinviz.comwebsite. Use theRelative Valuation (RV)Excel spreadsheet tab found in the Two Stage_DDM_FCFE_FCFF_RV.xlsx file.

a) LISTyour comparable firms.COMPAREandCONTRASTthemagnitude of the specific median or average P/X ratio for the comparable firms to the same P/X ratio for KO.ForEACHof the three (3) ratios, state whether you believe KO is under or overvalued relative to the comparable firms you have selected.Based on the magnitude of three P/X ratioscollectively(i.e. the individual ratios from Q7 above) may not be in agreement, but looking at all three of the P/X ratios, state whether you believe KO undervalued or overvalued?EXPLAINyour reasoning.

b) Multiply the median or average per share “Price/X” ratio of the comparable firms by the per share value of “X” for KO.COMPAREandCONTRASTthe price per share estimates that result from EACH Price/X ratio.For EACH ratio, state whether you believe KO is under or overvalued relative to the comparable firms.That is, COMPARE and CONTRAST the resulting RV price per share estimate for KOto the current observable market price.As in the DDM and FCFFM questions above, submit your spreadsheet to show your work.

Q10.Overall Consensus Valuation Estimate – 10 points

Based on ALL of YOUR price per share estimates from both the DDM, FCFFM and RV methods, state whether you believe that KO is currently undervalued or overvalued in the market?EXPLAINyour reasoning.