1. Data Collection: This project makes use of monthly returns data on three stocks for a period of five years. You need to download holding period returns for the period January 2010 December 2015 on the stocks NKE, AAPL and KO and the value weighted return. You will also need the returns for the risk free rate. You can find the returns for the risk free rate on the Fama French database. Organize the data so you have the returns for each stock and the index in a different column.

2. Summary Statistics Calculations: For each of the stocks, the risk free rate and the index, calculate summary statistics on returns including, mean, standard deviation and variance.

3. The Capital Market Line: Using the mean returns and standard deviations you calculated in question 2, plot the three stocks, the returns on the Value Weighted Index and the risk free rate on an expected return standard deviation graph. Be sure to label the three securities, the index and the risk free rate on the graph. Draw the capital market line (CML). Discuss whether the data for the three securities is consistent with CAPM. (Note: you want to choose a scatterplot from the various graph types available to you in excel).

4. The Market Model: Calculate monthly excess returns for each of the stocks and the Index by subtracting the risk-free rate from the monthly returns. For each security, create an X-Y scatterplot with security excess returns on the Y-axis and market (the Index) excess returns on the X-axis. On each of the three graphs, add a trendline and calculate the Alpha and Beta for the security. (Note: once the scatterplot is done, go to layout and click on trendline, choose the last option (more trendline options) and click on linear and display equation on chart; click OK, and now you have your chart with the dots, the line and the line equation, which gives you the alpha and the beta).

5. The Security Market Line: Using the mean returns and Betas you calculated for the three securities, the risk free rate and the Index, create an expected return beta graph. Be sure to label the three securities and the Index on the graph. Draw the Security Market Line for this set of securities. Discuss whether each of the three securities is priced correctly according to CAPM? If not, what would be your buy/sell recommendations for these stocks. (Note: this graph is very similar to that in part 3, except that now the X axis is Beta).