1) Just as you did for the first part of this project, download return data from 01/2011 12/2015 for the following stocks: CAT (Caterpillar Inc.), JPM (JP Morgan Chase & Co.), PG (Proctor & Gamble Co.), and ORCL (Oracle Corp.). You will also need the return data for your stock from the first project, the S&P Composite Index, and the risk-free rate that was used in the first part of this project. Be sure to have all 60 monthly returns for all assets. Working with Excels Analysis ToolPak to run and interpret regressions:

2) First, make sure that the Analysis ToolPak is loaded in Excel: a) Click the File tab, click Options, and then click the Add-Ins category. b) In the Manage box (at the bottom of the screen), select Excel Add-ins and then click Go. c) In the Add-Ins available box, select the Analysis ToolPak check box and also check the Solver Add-in check box as well since you will need it later, then click OK. d) Tip: If Analysis ToolPak is not listed in the Add-Ins available box, click Browse to locate it. e) If you are prompted that the Analysis ToolPak is not currently installed on your computer, click Yes to install it.

3) Now, you are ready to run regressions using your data: a. Our statistical model to estimate Alpha and Beta is based on excess returns. Therefore, you will need to calculate excess returns for each of the five stocks and the S&P Composite Index. b. Next, in the Data Tab, select Data Analysis then select Regression and click on OK c. In our statistical model, we are using the excess returns on the market portfolio (in this case, the S&P Composite Index) to explain the variation in excess returns of your stock. Therefore, the input Y-Range is the excess returns on your stock and the Input X-Range is the excess returns on the S&P Composite Index. d. Select your Output Range (any cell in your excel workbook that you would like the output displayed) and select the Residuals check box (residuals are the error terms from your regression model they are the differences in what is estimated by your trend line and the actual values) Then click on OK e. Next, Interpret the R-Square of your regression model. f. Calculate the systematic risk, the firm-specific risk, and the total risk of your stock (HINT: a slide in Unit 2 Lecture 3 will be very useful here). Also, use these numbers to confirm the R-Square of the SUMMARY OUTPUT. Also, compare the sum of the two risk measures to the total risk of your stock calculated by simply using the VARP function on the excess returns on your stock. g. Use the statistical equation for calculating beta from class notes to confirm the Beta calculated in the ANOVA output. h. Calculate the expected annual return for the stock using the CAPM model. Use the estimated Beta from your regression analysis, use 1.5% for the annual risk free rate, and use 7% for the expected annual return on the market portfolio. i. Run the regression model for the other four stocks. Determine their expected annual returns just as you did in Step (h) for your stock. You do not have to complete Steps (e), (f), and (g) for these four stocks.

4) Determining the minimum variance and optimal risky portfolio using Excels Solver function: a. We will start with two risky assets your stock and PG. In this section, we will be using raw returns. The first thing we must do is create the variance-covariance matrix. To do this, select Data Analysis in the Data Tab, then select Covariance from the list. Next, for the Input Range simply select the raw returns on your stock and on PG. Be sure to have the name of the stock as a header in your column of returns. Select the header as part of the Input Range and be sure to check the box Labels in First Row. Then choose an output cell in your spreadsheet and click OK. b. A table will be outputted which is not quite the variance-covariance matrix. The intersection cell between your stock across the top and down the side is the variance of your stock. The same is true for PG. The intersection cell between your stock and PG is the covariance between the returns on your stock and PG. Notice the cell in the top-right corner is blank. This is because the covariance between your stock and PG is already listed in the bottom-left cell. We need the entire table filled in; so copy the covariance from the bottom-left cell and copy it into the topright cell of the table. This is now your variance-covariance matrix. c. Next, follow the excel example posted to the course website and create a 2-by-2 table with the first row being the expected return on your stock you calculated from 2-h and the expected return on PG (which you complete in step 2-i). d. Again, follow the excel example to create the cells that will calculate the expected return, standard deviation, and Sharpe Measure on a combination portfolio given weights for your stock and PG. Note: when using matrix algebra functions in Excel, you must press CTRL+SHIFT+ENTER when you calculate a cell value. e. We will now use the Solver Function to maximize the Sharpe Measure (which from the class notes is the same as maximizing the slope of the CAL to determine the ORP). Go to the Data Tab and then to Solver. i. First, set the objective cell we select the cell that calculates the Sharpe Measure because our objective is to maximize the Sharpe Measure. Make sure to select the Max button. ii. We are going to maximize this function by changing the weights for your stock and PG. Therefore, in the By Changing Variable Cells you will select the cells that represent your portfolio weights. iii. We will want to add some constraints. Lets assume we dont want to short-sell any of the stocks in our portfolio. Therefore, we can hit the Add button and highlight the cells that represent the portfolio weights. Then set these so that they have to be greater than or equal to 0. Also, we need the weights of our portfolio to sum to 1. Therefore, add another constraint by highlighting the cell that sums the weights and set it equal to 1. iv. Go ahead and uncheck the box that says Make Unconstrained Variables Non-Negative. v. Hit the Solve button. vi. Make sure the pop-up window says that solver found a solution and then select the button that says Keep Solver Solution and hit OK. f. Use the equation for solving for the optimal weights of two risky assets in the ORP from the textbook (and class notes) and compare it to your answer from Excel (p. 162 from the 9th ed.). NOTE: Your answer wont necessarily be the same. You constrained the weights to be nonnegative in Excel while the equation you use from the book does not constrain the weights. But if the answer from Excel and the answer from using the equation from the textbook are both positive, then the two answers should be the same. g. Use the same tools used here to determine the weights on the minimum variance portfolio. In this case, however, do NOT constrain your weights to be greater than 0. Also, make sure that the box next to Make Unconstrained Variables Non-Negative is unchecked. Use the equation from class notes (or the text book) to find the weights on the minimum variance portfolio (footnote on p. 157) to compare to your solution using Solver. Since you are not constraining the weights, you should get the same answer.

5) Now, expand the exact same analysis from Part 3 to create the optimal risky portfolio using all five stocks (exclude the S&P Composite returns). Create constraints that do not allow any one weight to be greater than 50% of your overall portfolio. Constrain all weights to be greater than or equal to zero. You do not need to determine the MVP for this part, just determine ORP.

6) Fama-French 3-Factor model regression: Do this only for the stock you were originally assigned for the first project. a. First, download the risk premiums on HML and SMB from the WRDS website. On the home page under the Your Subscriptions tab select Fama French Liquidity Factors. Select Factors Monthly Frequency. Choose the date range of January 2011 to October 2015 (as with the risk free rate of return, the returns arent updated. For SMB, use these returns for 10/2015, 11/2015, and 12/2015: 0.0782, 0.0046, -0.0223. For HML, use these returns: -0.0173, 0.0286, and -0.0334). In Step 2 choose the Small Minus Big (SMB) and High Minus Low (HML) factors. Export this data. b. You do not create excess returns for HML and SMB because they already represent risk premiums, just as excess returns represent the risk premium relative to the risk-free rate. Just as before, the input y-range is the excess returns on your stock. But this time, you need to highlight all three columns of explanatory variables (excess returns on the S&P Composite, HML, and SMB) for the input x-range. Do not worry about getting the residuals for this regression. c. Specifically state your statistical model for explaining the excess returns on your stock. d. Interpret the R-Square in this regression. e. Explain what the sign on the SMB coefficient for your stock means. Based on the coefficient for SMB, does your stock seem to be significantly affected by the risk premium captured by SMB? f. Explain what the sign on the HML coefficient for your stock means. Based on the coefficient for HML, does your stock seem to be significantly affected by the risk premium captured by HML?