Microsoft Word - Case Study 4 Ch13 Colgate-Palmolive
CASE STUDY 5
The Beta for Colgate-Palmolive
Joey Moss, a recent finance graduate, has just begun his job with the investment firm of Covili
and Wyatt. Paul Covili, one of the firm's founders, has been talking to Joey about the firm's
investment portfolio.
As with any investment, Paul is concerned about the risk of the investment as well as the
potential return. More specifically, because the company holds a diversified portfolio, Paul is
concerned about the systematic risk of cu
ent and potential investments. One such position the
company cu
ently holds is stock in Colgate-Palmolive (CL). Colgate-Palmolive is the well-
known manufacturer of consumer products under
and names such as Colgate, Palmolive,
Softsoap, Irish Spring, Ajax, and others.
Covili and Wyatt cu
ently uses a commercial data vendor for information about its positions.
Because of this, Paul is unsure exactly how the numbers provided are calculated. The data
provider considers its methods proprietary, and it will not disclose how stock betas and other
information are calculated. Paul is uncomfortable with not knowing exactly how these numbers
are being computed and also believes that it could be less expensive to calculate the necessary
statistics in-house. To explore this question, Paul has asked Joey to do the following
assignments.
QUESTIONS
1. Go to finance.yahoo.com and download the ending monthly stock prices for Colgate-
Palmolive for the last 60 months. Use the adjusted closing price, which adjusts for
dividend payments and stock splits. Next, download the ending value of the S&P 500
index over the same period. For the historical risk-free rate, go to the St. Louis Federal
Reserve website (www.stlouisfed.org) and find the three-month Treasury bill secondary
market rate. Download this file. What are the monthly returns, average monthly returns,
and standard deviations for Colgate—Palmolive stock, the three-month Treasury bill, and
the S&P 500 for this period?
2. Beta is often estimated by linear regression. A model commonly used is called the market
model, which is:
Rt − Rft = αi + βi [RMt − Rft] + ∊t
In this regression, Rt is the return on the stock and Rft is the risk-free rate for the same
period. RMt is the return on a stock market index such as the S&P 500 index. αi is the
egression intercept, and βi is the slope (and the stock's estimated beta). ∊t represents the
esiduals for the regression. The intercept, αi, is often called Jensen's alpha. What does it
measure? If an asset has a positive Jensen's alpha, where would it plot with respect to the
SML? What is the financial interpretation of the residuals in the regression?
3. Use the market model to estimate the beta for Colgate—Palmolive using the last 36
months of returns. Plot the monthly returns on Colgate-Palmolive against the index and
also show the fitted line.
4. When the beta of a stock is calculated using monthly returns, there is a debate over the
number of months that should be used in the calculation. Rework the previous questions
using the last 60 months of returns. How does this answer compare to what you
calculated previously?
Sheet1
Date Stock Price Market Price 3-M Treasury Bill Risk-Free Rate Stock Return Market Return Stock Risk Premium Market Risk Primium
3/1/17
4/1/17
5/1/17
6/1/17
7/1/17
8/1/17
9/1/17
10/1/17
11/1/17 1 Risk-Free Rate Stock Return Market Return
12/1/17 Average
1/1/18 Standard deviation
2/1/18
3/1/18
4/1/18
5/1/18
6/1/18
7/1/18
8/1/18
9/1/18
10/1/18
11/1/18
12/1/18
1/1/19
2/1/19
3/1/19
4/1/19
5/1/19
6/1/19
7/1/19
8/1/19
9/1/19
10/1/19
11/1/19
12/1/19
1/1/20
2/1/20
3/1/20
4/1/20
5/1/20
6/1/20
7/1/20
8/1/20
9/1/20
10/1/20
11/1/20
12/1/20
1/1/21
2/1/21
3/1/21
4/1/21
5/1/21
6/1/21
7/1/21
8/1/21
9/1/21
10/1/21
11/1/21
12/1/21
1/1/22
2/1/22
3/1/22
Download CL stock price from https:
finance.yahoo.com/quote/CL?p=CL
and S&P 500 (Market) price from https:
finance.yahoo.com/quote/%5Egspc?ltr=1
Data range: 3/1/2017-3/1/2022
Frequency: Monthly
Use Adj. Closing prices
Download Three-Month Treasury bill rate (Risk Free) from https:
fred.stlouisfed.org/series/TB3MS
The data from the website are the annulized numbers before the pecentage sign. Therefore, to convert to monthly risk-free rate, we need to divide the data by (12*100=) 1200.
Stock (or Market) returns are calcuated by:
(cu
ent month price of the stock (or market) - previous month price of the stock (or market))/ previous month price of the stock (or market)
Stock (or Market) risk premiums are calculated by:
Stock (or Market) return - risk-free rate
Q1. Average return is calculated by the function "AVERAGE".
Standard deviation is calcuated by the funtion "STDEV".
Q3/4. Beta is calcuated by regression analysis, which can be found by:
DATA→Data Analysis→Select "Regression"→OK
→Input Y Range: select stock premium (36 months of data for Q3 and 60 months of data for Q4)
→Input X Range: select market premium (36 months of data for Q3 and 60 months of data for Q4)
→Check Line Fit Plots→OK
Beta can be found as the coefficent of X
**************************************************************
For the first time to use regression in excel, you need to install analysis toolpak by:
FILE →Options→Add-Ins→Select "Analysis ToolPak"→Manage "Excel Add-ins" Go→Check Analysis ToolPak→OK
*************************************************************