View the step-by-step solution to:

Question

# I need help answering the 3 questions below.

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 numbers?

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&amp;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' (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 (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.

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&amp;P 500 data can be found by clicking on the &quot;S&amp;P500&quot; icon and then, clicking on the
Historical Prices&quot; icon. Click on the &quot;Monthly&quot; 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 &quot;Download to
Spreadsheet&quot;. 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&amp; 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, (Value,), by the beginning value, (Value,), and
subtracting one as in Equation [1]. An alternative method to calculate the return is to subtract the beginning value,
(Value,), from the ending value, (Value,), and dividing by the beginning value, (Value,), as in Equation [2]. Both
returns are adjusted for dividends and stock splits. The returns used in the regression analysis are arithmetic returns.
Return = [(Value,- Valuco) - 1]
Retum = [(Value,- Value.)Valuco)]
[1]
[2]
Five years of Monthly data are used to generate sixty data points&quot;.
CALCULATING BETA FOR COCA-COLA
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&amp;P 500 Index for the same time period. Beta is the covariance between returns for Coca-Cola
and returns for the S&amp;P 500 divided by the variance for the S&amp;P 500.
Rko = Alphago + Betako (R.)
[3]
Rko
the return for Coca-Cola stock
Betaxo
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
(R.)
the return on the S&amp;P 500 Stock market Index
(Rm - RF)
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&amp;P 500 Index and for Coca-cola stock, respectively. The independent variable is the return for

the S&amp;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 I contains the regression results for the regression between the return for the S&amp;P500 and for Coca-
Cola using Excel. The independent variable is the return for the S&amp;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&quot; 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&amp;P 500 Regression of Arithmetic Means Returns from 09/02/03 to 08/01/08
Regression Statistics
Multiple F
0.493288
R Square
0.243333
0.230287
Standard Error
3.723303
Observations
60
ANOVA
d'f
SS
MS
F
Significance F
Regression
1
258.57
258.57
18.65
790000 0
Residual
58
804.05
13.86
Total
59
1062.63
Coefficients
Standard Error
I Star
P-value
Intercept
0.087056
0.485744
0.179223
0.858388
X Variable I
0.765019
0.177137
4.318793
0.000062
Coca-Cola Characteristic Line (9/2/03 to 8/0 1/08)
40
y = 0.765x + 0.0871
R- = 0.2433
Coca-Cola
15
-10
-5
10
S&amp;P 500
Figure 1: Characteristic Line - Coca-Cola

Journal of Business Case Studies - November December 2010
Volume 6 Number 6
Figure 1 is a graph of the data used to compute the Coca-cola beta, which is the characteristic line for
Coca-Cola. Figure I was created in Excel using the Chart function. The independent variable is the return for the
S&amp;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&quot;. The statistics in the graph are the same as
the regression statistics in Table 1. The pedagogical purpose of the graph is to chart the characteristic line for Coca-
Cola and to confirm that the regression was run with the correct independent and dependent variable. If the trend
line and statistics in the graph are not identical to the numbers in the regression, the student has reversed the
variables.
CALCULATING THE REQUIRED RATE OF RETURN FOR STOCKS
Graham and Harvey (2002) find that 73.5 percent of respondents to their survey indicate that the company
of the survey respondent uses the capital asset pricing model (CAPM) to determine the component cost of common
stock equity capital. In this paper, we use the CAPM to compute the required rate of return for Coca-Cola. The
required rate of return for Coca-Cola is the minimum rate of return demanded by stockholders of Coca-Cola stock.
The model used in this paper is based on the CAPM derived from the work of Sharpe (1964).
Rxo = Ry + Betako (R - RF)
[4]
Rko
- the required rate of return for Coca-Cola Stock
Rr
- the risk free rate of return
Betako
= the beta coefficient for Coca-Cola
Rm
= the rate of return on the stock market
(Rm - RF)
The required rate of return for Coca-Cola is the risk-free rate of return plus the risk premium for Coca-
Cola. The risk premium is the beta for Coca-Cola time the market price of risk.
COMPUTING THE REQUIRED RATE OF RETURN FOR COCA-COLA (KO) USING THE CAPM
The risk free rate is the total return (income plus capital appreciation) on Long-term Government Bonds
taken from SBBI 2007 . For the years from 1926 to 1976, SBBI uses the Government Bond File from the Center for
Research in Security Prices. For the period from 1976 to 2006, the returns in SBBI 2007 are computed from data
taken from the Wall Street Journal. The yield for the bond is the discount rate that equates the expected future cash
flows, coupon payments and maturity value, to the current price. Table 2 contains a summary of the input data and
sources of that data.
Table 2: Input Data and Sources
Variable
Value
Source
Betago
0.7650
Computed
Rf
0.0580
SBBI, 2007, page 31
0.1230
SBBI, 2007, page 31
K
0.1077
Computed
We use the security market line to compute the required rate of return for Coca-Cola. We use the long-
term bond rate taken from SBBI (2007) which equals 5.8% and the long-term market return of 12.3%. The market
risk premium is 6.5%. This yields a cost of equity for Coca-Cola of 10.77%.

Rko = Rr+ Betako (Rm - RF)
[5]
10.77% = 5.8% + 0.7650 (12.3% - 5.8%)
10.77% = 5.8% +0.7650 (6.5%)
10.77% = 5.8% + 4.97%
The required rate of return for Coca-Cola stock is 10.77%.
SUMMARY AND CONCLUSIONS
In this paper, we demonstrate how to compute the required rate of return for Coca-Cola using modern
portfolio theory. Data is downloaded from Yahoo! Finance for both Coca-Cola and for the S&amp;P 500 Index. The
adjusted stock price for Coca-Cola and the S&amp;P 500 Index are used to compute a five-year, monthly series of
returns. The characteristic line is the regression line from the regression in which the monthly returns for the S&amp; P
500 Index are the independent variables and the monthly returns for Coca-Cola are the dependent variables. The
regression is run using the Data Analysis Tool Pak in Excel and the Chart function. We use SBBI 2007 data to
compute the required rate of return using the market model. We compute a required rate of return for Coca-Cola
equal to 10.77%
The objective of this paper is to demonstrate how to download the data needed to compute the required rate
of return for Coca-Cola using Modern Portfolio Theory. 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&amp;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.
AUTHOR INFORMATION
John C. Gardner is the KPMG Professor of Accounting and Director of the Global Entrepreneurship Initiative in
the Department of Accounting at the University of New Orleans. He earned his undergraduate degree in accounting
from SUNY at Albany, and MBA and Ph.D. degrees in finance from Michigan State University. Dr. Gardner has
published in leading accounting, finance and management science journals including The Accounting Review,
Journal of Accounting Research, Contemporary Accounting Research, Accounting, Organizations and Society,
Journal of Financial and Quantitative Analysis and Decision Sciences. His research interests include multi-national
corporation financial management, capital structure, and financial and forensic accounting.
Carl B. McGowan, Jr., PhD, CFA is a Professor of Finance at Norfolk State University, has a BA in International
Relations (Syracuse), an MBA in Finance (Eastern Michigan), and a PhD in Business Administration (Finance) from
Michigan State. From 2003 to 2004, he held the RHB Bank Distinguished Chair in Finance at the Universiti
Kebangsaan Malaysia and has taught in Cost Rica, Malaysia, Moscow, Saudi Arabia, and The UAE. Professor
McGowan has published in numerous journals including Applied Financial Economics, Decision Science, Financial
Practice and Education, The Financial Review, International Business and Economics Research Journal, The
International Review of Financial Analysis, The Journal of Applied Business Research, The Journal of Business
Case Studies, The Journal of Diversity Management, The Journal of Real Estate Research, Managerial Finance,
Managing Global Transitions, The Southwestern Economic Review, and Urban Studies.
Susan E. Moeller is a Professor of Finance at Eastern Michigan University since 1990. Prior to joining EMU, Dr.
Moeller taught at Northeastern University in Boston and at the University of Michigan - Flint. Her corporate
experience was with Ford Motor Company. She has published in a number of journals including, Journal of
Economic and Financial Education, Journal of Business Case Studies, Journal of Global Business, Journal of
International Finance, Journal of Financial and Strategic Decisions, Management International Review, Journal of
Applied Business Research and AAll Journal.
107

Journal of Business Case Studies - November/December 2010
Volume 6. Number 6
APPENDIX A
Date
S &amp;P500
Rates of Return S&amp;P500 and COCA-COLA
8/1/2008
1249.01
KO
52.07
REarin
7/1/2008
-1.45
RKO
6/2/2008
1267.38
51.50
5/1/2008
51.98
-0.99
1.11
1280.00
1400.38
57.26
-8.60
0.92
4/1/2008
58.87
1.07
-9.22
3/3/2008
1385.59
1322.70
4.75
-2.73
3.29
2/1/2008
0.60
1/2/2008
1378.53
58.46
-3.48
4.12
-0.92
12/3/2007
1/1/2007
468.36
59.00
1481.14
61.37
-6.12
62.10
-0.86
3.86
10/1/2007
4.40
1.18
055
9/4/2007
1549.38
8/1/2007
1526.75
61.76
7/2/2007
1473.99
57.47
1.48
7.46
53.78
3.58
6.86
3.20
6/1/2007
1455.27
5/1/2007
1503.35
52. 11
4/2/2007
1530.62
52.31
-3.20
52.99
-1.78
-128
3/1/2007
1482.37
1420.86
52.19
3.25
4.33
1.53
2/1/2007
1406.82
48.00
8.73
1/3/2007
1438.24
46.68
-2.18
2.83
12/1/200
47.8
48.25
1.41
-2.5
11/1/200
1418.30
1 26
10/2/2006
1400.6
1377.94
46.83
46.72
1.65
3.03
9/1/2006
1335.85
44.68
3.15
0.24
7/3/200
1303.82
2.46
457
8/1/2006
0.29
1276.66
44.50
2.13
0.70
6/1/2006
1270.20
43.02
051
5/1/2006
0.01
3.44
4/3/2006
1270.0
-2.29
3/1/2006
1310.61
44.0
41.96
3.0
1.22
4.93
0.21
2/1/2006
1294.87
1/3/2006
1280.66
41.87
1280.08
41.97
1.11
-0.24
1 2/1/2005
1248.29
41.38
0.05
1.43
1 1/1/200
40.31
2.55
2.65
10/3/2005
1249.48
1207.01
42.69
0.10
42. 78
3.52
9/1/2005
-1.77
-021
122 8.81
43.19
0.95
8/1/2005
0.69
7/1/2005
1220.33
44.00
-1.12
-1.84
055
6/1/2005
1234.18
5/2/2005
1191.33
43.76
3.60
4.81
1191.50
41.75
4/1/2005
1156.85
44.63
0.01
3.00
6.4
3/1/2005
2.74
2/1/2005
180.59
43.44
41.67
-2.01
-1.91
4.25
1/3/2005
1203.60
1181.27
41.49
1.89
2.64
12/1/2004
121 1.92
-2.53
3.16
1173.82
41.6
-036
1 1/1/200-
10/1/2004
1130.20
39.31
3.25
40.66
3.86
5.93
9/1/2004
3.3
1114.58
1104.24
40.05
1.40
8/2/200-
0.94
1.52
7/1/2004
-10.42
6/1/2004
1101.72
44.71
43.86
0.23
-3.43
1.94
5/3/2004
1140.84
4/1/200
1120.68
50.48
51.35
1.80
121
-1.69
3/1/2004
1107.30
1.54
2/2/200-
1126.21
50.57
50.30
-1.68
0.54
1/2/2004
1144.94
49.96
-1.64
1.22
0.68
12/1/2003
1.73
1.46
2.98
1 1/3/2003
111 1.92
10/1/2003
105 8.20
5.08
9.14
9/2/2003
1050.71
46.50
5.50
0.22
8/1/2003
995.97
46.40
0.71
1008.01
42.96
8.01
43.52
1.29
109

### Why Join Course Hero?

Course Hero has all the homework and study help you need to succeed! We’ve got course-specific notes, study guides, and practice tests along with expert tutors.

### -

Educational Resources
• ### -

Study Documents

Find the best study resources around, tagged to your specific courses. Share your own to gain free Course Hero access.

Browse Documents