1. Can you find an error in equation 1
2. Look at Figure 1, how well does the regression line predict the actual results? What does the R2 in the regression equation (table1) mean? Does this give you confidence in the calculated Beta or not?
3. The authors use very long-term ranges to estimate risk free rate and market premium. What are the benefits and risks of this? Would you use a different range of number?
4. Discuss the Beta results of the calculation. What does it mean? Does it make sense to you with what you know about Coco Cola?
5. Check the cost of equity determined in the case against the current Coca Cola stock price. Find the recent growth rate and use the formula Return on equity = (Dividend next year divided by current price) + growth rate. Is it close, if not why would it be different?
Journal of Business Case Studies – November/December 2010 Volume 6, Number 6 103 Calculating The Beta Coefficient And Required Rate Of Return For Coca-Cola John C. Gardner, University of New Orleans, USA Carl B. McGowan, Jr., Norfolk State University, USA Susan E. Moeller, Eastern Michigan University, USA ABSTRACT In this paper, we demonstrate how to compute the required rate of return for Coca-Cola using modern portfolio theory with data downloaded from the internet. We demonstrate how to calculate monthly returns for the index and Coca-Cola and how to use the returns to compute the beta coefficient and the required rate of return using the downloaded data. We show how to validate the data for the market index and the company and how to compute the returns using the dividend and stock split adjusted prices. We demonstrate how to graph the characteristic line for Coca-Cola and use the graph to check that the regression was run correctly. We use Coca-Cola and the S&P 500 Index in this paper, but any company listed on Yahoo! Finance can be used as the example. This paper can be used as the basis of a lecture on intermediate corporate finance or investments to demonstrate the process using a real company. Keywords: beta; characteristic line; required rate of return; Coca-Cola; teaching note INTRODUCTION arkowitz 1 (1952) began modern portfolio theory (MPT) which can be used to explain the relationship between risk and return for assets, particularly stocks. Stock of companies that have higher rates of return have higher levels of risk. In order to achieve a lower level of risk, an investor must accept a lower expected rate of return. This concept is called the dominance principle and allows for the creation of the efficient frontier. MPT partitions risk into non-systematic risk, which can be eliminated from a portfolio through diversification, and systematic risk that is market wide and cannot be diversified. Non-systematic risk is company specific and is reduced to zero in a large, well diversified portfolio. In order to determine systematic risk for a stock, we use the market model developed by Sharpe 2 (1964). The returns for a stock are regressed as the dependent variable against a market index used as the independent variable. The slope coefficient of the regression is the measure of systematic risk for the stock. Systematic risk measures the degree to which a stock moves with the market. A higher beta coefficient implies that returns for the stock move more than the market and a lower beta coefficient implies that returns for the stock move less that the market. The former are aggressive stocks and the latter are defensive stocks. In this paper, we show how to retrieve data from the internet, how to compute returns for both the market index and the stock, and how to run a regression to determine the beta coefficient to measure the systematic risk for the stock. In addition, we show how to graph the data with a trend line and statistics to verify that the first regression is run correctly; that is, with the correct variable as the independent variable. We show how to do all of this analysis using Excel. 1 Markowitz received The Sveriges Riksbank Prize in Economic Sciences in Memory of Alfred Nobel 1990. 2 Sharpe received The Sveriges Riksbank Prize in Economic Sciences in Memory of Alfred Nobel 1990. M Journal of Business Case Studies – November/December 2010 Volume 6, Number 6 104 DOWNLOADING DATA FROM THE INTERNET The data used for the analysis discussed in this paper are downloaded from the internet using the Yahoo! Finance website. The URL for Yahoo! Finance is http://finance.yahoo.com/. Once one arrives at the Yahoo! Finance website, the S&P 500 data can be found by clicking on the “S&P500” icon and then, clicking on the Historical Prices” icon. Click on the “Monthly” indicator to download monthly data and enter the dates. For this paper we download sixty-one monthly, observations in order to calculate sixty monthly returns. The data columns are: Date, Open, High, Low, Close, Average Volume, and Adjusted Close. The index and the Coca-Cola price are adjusted for splits and dividends. Move the cursor to the bottom of the data and click on “Download to Spreadsheet”. Save the data to a spreadsheet and repeat the process for the Coca-Cola data. Begin by entering the Coca-Cola ticker symbol, KO, and download and save the data for the save time period. CALCULATING RETURNS FOR THE S&P 500 INDEX AND FOR COCA-COLA3 In this paper, we use arithmetic returns to compute the beta coefficient for Coca-Cola. Arithmetic returns are calculated by dividing the ending index or stock value, (Value1), by the beginning value, (Value0), and subtracting one as in Equation [1]. An alternative method to calculate the return is to subtract the beginning value, (Value0), from the ending value, (Value1), and dividing by the beginning value, (Value0), as in Equation [2]. Both returns are adjusted for dividends and stock splits. The returns used in the regression analysis are arithmetic returns. Return = [(Value1- Value0) – 1] [1] Return = [(Value1- Value0)/Value0)] [2] Five years of Monthly data are used to generate sixty data points 4 . CALCULATING BETA FOR COCA-COLA 5 Modern Portfolio Theory shows that investors are rewarded for the systematic risk of an investment and not for the total risk of an investment because total risk includes firm specific risk that can be eliminated in a well diversified portfolio. The specific risk of an individual stock is the slope coefficient of the characteristic line which is the regression line between the monthly returns for the individual security and the monthly returns for the market index. Beta coefficient lines are calculated using a sixty month regression. In this example, the beta coefficient for Coca-Cola is calculated using sixty monthly observations of returns for Coca-Cola from 09/02/2003 to 08/01/2008 and returns for the S&P 500 Index for the same time period. Beta is the covariance between returns for Coca-Cola and returns for the S&P 500 divided by the variance for the S&P 500. RKO = AlphaKO + BetaKO (Rm) [3] RKO the return for Coca-Cola stock BetaKO the slope of the regression line between returns for the market and returns for Coca-Cola AlphaKO the intercept coefficient for the regression line between returns for the market and returns for Coca- Cola (Rm) the return on the S&P 500 Stock market Index (Rm – RF) the market risk premium is the additional return that stock holders receive for the additional risk of holding stocks rather than the risk free asset, long-term government bonds. Appendix A contains the data used to compute the Coca-Cola beta and are downloaded from Yahoo! Finance. Column 1 shows the date and Columns 2 and 3 contain the stock split and dividend adjusted index and price values, for the S&P 500 Index and for Coca-cola stock, respectively. The independent variable is the return for 3 See Brigham and Ehrhardt (2008, pages 223-225) for a discussion of how to compute a beta coefficient. 4 See Brigham and Ehrhardt (2008, page 223). 5 See Brigham and Ehrhardt (2008, pages 223-225) for a discussion of how to compute a beta coefficient. http://finance.yahoo.com/ Journal of Business Case Studies – November/December 2010 Volume 6, Number 6 105 the S&P500 (Column 4) and the dependent variable is the return for Coca-Cola (Column 5). The returns are calculated by dividing the ending index or stock value by the beginning value and subtracting one. An alternative method to calculate the return is to subtract the beginning value from the ending value and dividing by the beginning value. Both returns are adjusted for dividend and stock splits. The returns used are arithmetic returns. Table 1 contains the regression results for the regression between the return for the S&P500 and for Coca- Cola using Excel. The independent variable is the return for the S&P500 (x-axis) and the dependent variable is the return for Coca-Cola (y-axis). Both returns are adjusted for dividends and stock splits. The adjusted R 2 for the regression is 0.23 and the F-statistic is 18.65, both of which are statistically significant at the 0.0000 level. The regression coefficient is 0.7560 and has a t-statistic of 4.31 and is significant at the 0.0000 level. Table 1: Coca-Cola versus the S&P 500 Regression of Arithmetic Means Returns from 09/02/03 to 08/01/08 Regression Statistics Multiple R 0.493288 R Square 0.243333 Adjusted R Square 0.230287 Standard Error 3.723303 Observations 60 ANOVA df SS MS F Significance F Regression 1 258.57 258.57 18.65 0.000062 Residual 58 804.05 13.86 Total 59 1062.63 Coefficients Standard Error t Stat P-value Intercept 0.087056 0.485744 0.179223 0.858388 X Variable 1 0.765019 0.177137 4.318793 0.000062 Figure 1: Characteristic Line - Coca-Cola Coca-Cola Characteristic Line (9/2/03 to 8/01/08) y = 0.765x + 0.0871 R 2 = 0.2433 -15 -10 -5 0 5 10 15 -15 -10 -5 0 5 10 15 S&P 500 C o ca -C o la Journal of Business Case Studies – November/December 2010 Volume 6, Number 6 106 Figure 1 is a graph of the data used to compute the Coca-Cola beta, which is the characteristic line for Coca-Cola. Figure 1 was created in Excel using the Chart function. The independent variable is the return for the S&P500 (x-axis) and the dependent variable is the return for Coca-Cola (y-axis). Both returns are adjusted for dividends and for stock splits. The chart contains the trend line and R 2 . The statistics in the graph