BAF 321 Project- Fall 2020 8. Collect the beta of each of the 6 stocks. 9. Calculate the required return based on CAPM model for each stock compare it to the average return you have previously calculated in question 3 (Hint: find the risk free rate and the return on the market during the same period). 10. Finally, plot them on SML line, by clearing showing which ones are underpriced, overpriced, or fairly price. The stocks (with their symbols in parentheses are): (A) (ORLY) (FOLD) (AZO) (ATML) (AN) Agilent Technologies, Inc. O'Reilly Automotive, Inc. Amicus Therapeutics, Inc. AutoZone, Inc. Atmel Corporation AutoNation, Inc. Today is October 30, 2020, and you have just started your new job with a financial planning firm. You have been asked to review a portion of a client's stock portfolio to determine the risk/return profiles of 6 stocks in the portfolio. Unfortunately, your small firm cannot afford the expensive databases that would provide all this information with a few simple keystrokes, but that's why they hired you. Specifically, you have been asked to determine the monthly average returns and standard deviations for the 6 stocks for the past five years. The stocks (with their symbols in parentheses) are presented in the attached excel sheet. Questions: 1. Collect price information for each stock from Yahoo! Finance (finance.yahoo.com) as follows: a. Enter the stock symbol. On the page for that stock, click "Historical Prices" on the left side of the page. b. Enter the "start date" as October 1, 2015, and the "end date" as October 30, 2020 to cover the five-year period. Make sure you click "monthly" next to the date. c. After hitting "Get Prices," scroll to the bottom of the first page and click "Download to Spreadsheet." d. Delete all the columns except the date and the adjusted close the first and last columns). e. Keep the Excel file open and go back to the Yahoo! Finance Web page and hit the back button. If you are asked if you want to save the data, click no. f. Repeat these steps for the remaining stocks, pasting each closing price right next to the other stocks, again making sure that the correct prices on the correct dates all appear on the same rows. 2. Convert these prices to monthly returns as the percentage change in the monthly prices. (Hint: Create a separate worksheet within the Excel file.) Note that to compute a return for each month, you need a beginning and ending price, so you will not be able to compute the return for the first month. 3. Compute the mean monthly returns and standard deviations for the monthly returns of each of the stocks. Convert the monthly statistics to annual statistics for easier interpretation (muliply the mean monthly return by 12, and multiply the monthly standard deviation by square root of 12). 4. Add a column in your Excel worksheet with the monthly return to an equally weighted portfolio of these stocks. Compute the mean and standard deviation of monthly returns for the equally weighted portfolio. Double-check that the average return on this equally weighted portfolio is equal to the average return of all of the individual stocks. Convert these monthly statistics to annual statistics (as described in step 3) for interpretation. 5. Add another two columns that shows the return and standard deviation of two portfolios with random weights allocated to these 6 stocks. Make sure to specify the weights allocated to each stock for these two portfolios. 6. Create an Excel plot with standard deviation (volatility) on the x-axis and average return on the y-axis as follows: a. Create three columns on your spreadsheet with the statistics you created in Questions 3, 4, and 5 for each of the individual stocks, the equally weighted portfolio, and the two random portfolios. The first column will have the ticker, the second will have annual standard deviation, and the third will have the annual mean return. b. Plot them on the same graph. 7. What do you notice about the average of the volatilities of the individual stocks, compared to the volatility of the portfolios? BAF 321 Project- Fall 2020 8. Collect the beta of each of the 6 stocks. 9. Calculate the required return based on CAPM model for each stock compare it to the average return you have previously calculated in question 3 (Hint: find the risk free rate and the return on the market during the same period). 10. Finally, plot them on SML line, by clearing showing which ones are underpriced, overpriced, or fairly price. The stocks (with their symbols in parentheses are): (A) (ORLY) (FOLD) (AZO) (ATML) (AN) Agilent Technologies, Inc. O'Reilly Automotive, Inc. Amicus Therapeutics, Inc. AutoZone, Inc. Atmel Corporation AutoNation, Inc. Today is October 30, 2020, and you have just started your new job with a financial planning firm. You have been asked to review a portion of a client's stock portfolio to determine the risk/return profiles of 6 stocks in the portfolio. Unfortunately, your small firm cannot afford the expensive databases that would provide all this information with a few simple keystrokes, but that's why they hired you. Specifically, you have been asked to determine the monthly average returns and standard deviations for the 6 stocks for the past five years. The stocks (with their symbols in parentheses) are presented in the attached excel sheet. Questions: 1. Collect price information for each stock from Yahoo! Finance (finance.yahoo.com) as follows: a. Enter the stock symbol. On the page for that stock, click "Historical Prices" on the left side of the page. b. Enter the "start date" as October 1, 2015, and the "end date" as October 30, 2020 to cover the five-year period. Make sure you click "monthly" next to the date. c. After hitting "Get Prices," scroll to the bottom of the first page and click "Download to Spreadsheet." d. Delete all the columns except the date and the adjusted close the first and last columns). e. Keep the Excel file open and go back to the Yahoo! Finance Web page and hit the back button. If you are asked if you want to save the data, click no. f. Repeat these steps for the remaining stocks, pasting each closing price right next to the other stocks, again making sure that the correct prices on the correct dates all appear on the same rows. 2. Convert these prices to monthly returns as the percentage change in the monthly prices. (Hint: Create a separate worksheet within the Excel file.) Note that to compute a return for each month, you need a beginning and ending price, so you will not be able to compute the return for the first month. 3. Compute the mean monthly returns and standard deviations for the monthly returns of each of the stocks. Convert the monthly statistics to annual statistics for easier interpretation (muliply the mean monthly return by 12, and multiply the monthly standard deviation by square root of 12). 4. Add a column in your Excel worksheet with the monthly return to an equally weighted portfolio of these stocks. Compute the mean and standard deviation of monthly returns for the equally weighted portfolio. Double-check that the average return on this equally weighted portfolio is equal to the average return of all of the individual stocks. Convert these monthly statistics to annual statistics (as described in step 3) for interpretation. 5. Add another two columns that shows the return and standard deviation of two portfolios with random weights allocated to these 6 stocks. Make sure to specify the weights allocated to each stock for these two portfolios. 6. Create an Excel plot with standard deviation (volatility) on the x-axis and average return on the y-axis as follows: a. Create three columns on your spreadsheet with the statistics you created in Questions 3, 4, and 5 for each of the individual stocks, the equally weighted portfolio, and the two random portfolios. The first column will have the ticker, the second will have annual standard deviation, and the third will have the annual mean return. b. Plot them on the same graph. 7. What do you notice about the average of the volatilities of the individual stocks, compared to the volatility of the portfolios