CASE 2: The Beta for Colgate-Palmolive Joey Moss, a recent finance graduate has begun his job with the investment firm of Coxili and Wyatt. Paul Coxili 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 current and potential investments. One such position the company currently holds is stock in Colgate-Palmolive (CL). Colgate-Palmolive is the well-known manufacturer of consumer products under brand names such as Colgate, Palmolive, Softsoar, Irish Spring, Ajax and others. Caxili and Wyatt currently use 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 that it could be less expensive to calculate the necessary statistics in-house. To explore this issue, Paul has asked Joey to perform the following assignments: 1. Beta is often estimated by linear regression. A model commonly used is called the market model, which is: R-RA= a +B (Rm-B) +et In this regression, R, is the return on the stock and Ro is the risk-free rate for the same period. Rj is the return on a stock market index such as the S&P 500 index; a is the regression intercept; B is the slope (and the stock's estimated beta); and e, represents the residuals for the regression. Use the market model above, and the data I have provided to you here, to estimate the p for Colgate-Palmolive using the last 36 months of returns. Use Excel to estimate the regression. 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 question using the last 60 months of returns. How does this answer compare to what you calculated previously? What are some arguments for and against using shorter versus longer periods? Compare your beta for Colgate-Palmolive to the beta of the same company and another competitor of CL you might find on finance.yahoo.com. How similar are they? Why might they be different? NOTE: I have provided you all of the data you will need to perform the above estimations. Also, I have provided a detailed write up on how to install and run a regression estimation in Excel. Please go through these files before attempting the above. Please upload your Excel Spreadsheet with your name and the name of your consulting partner. CASE 2: The Beta for Colgate-Palmolive Joey Moss, a recent finance graduate has begun his job with the investment firm of Coxili and Wyatt. Paul Coxili 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 current and potential investments. One such position the company currently holds is stock in Colgate-Palmolive (CL). Colgate-Palmolive is the well-known manufacturer of consumer products under brand names such as Colgate, Palmolive, Softsoar, Irish Spring, Ajax and others. Caxili and Wyatt currently use 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 that it could be less expensive to calculate the necessary statistics in-house. To explore this issue, Paul has asked Joey to perform the following assignments: 1. Beta is often estimated by linear regression. A model commonly used is called the market model, which is: R-RA= a +B (Rm-B) +et In this regression, R, is the return on the stock and Ro is the risk-free rate for the same period. Rj is the return on a stock market index such as the S&P 500 index; a is the regression intercept; B is the slope (and the stock's estimated beta); and e, represents the residuals for the regression. Use the market model above, and the data I have provided to you here, to estimate the p for Colgate-Palmolive using the last 36 months of returns. Use Excel to estimate the regression. 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 question using the last 60 months of returns. How does this answer compare to what you calculated previously? What are some arguments for and against using shorter versus longer periods? Compare your beta for Colgate-Palmolive to the beta of the same company and another competitor of CL you might find on finance.yahoo.com. How similar are they? Why might they be different? NOTE: I have provided you all of the data you will need to perform the above estimations. Also, I have provided a detailed write up on how to install and run a regression estimation in Excel. Please go through these files before attempting the above. Please upload your Excel Spreadsheet with your name and the name of your consulting partner