Your supervisor asked to help review a portion of a client's stock portfolio to determine the risk/return profiles of 12 stocks in the portfolio. Specifically, you have been asked to determine the monthly average returns and standard deviations for the 12 stocks for the past five years. The stocks (with their symbols in parentheses) are: - Archer Daniels Midland (ADM) - Boeing (BA) - Caterpillar (CAT) - Deere \& CO, (DE) - General Mills, Inc. (GIS) - eBay(EBAM) - Hershey (HSY) - International Business Machines - Corporation (IBM) - JPMorgan Chase \& Co. (JPM) - Microsoft (MSFT) - Procter and Gamble (PG) - Walmart (WMT) The consulting firm can access expensive databases that provide all this information with a few simple keystrokes. Stili, your supervisor wants you to bulld your research skills; thus, she asked you to gather the data yourself. She suggested the following data sources. Find price data for each stock on Yahoo! Finance (finance.yahoo.com) by doing the following: a) Enter the stock symbol. On the page for that stock, click "Historical Data." b) Enter the "start date" and the "end date" to cover the five-year period. Make sure you click "monthly" next to the date; the closing prices reported by Yahool will then be for the last day of each month. c) Download the prices after updating the date range and frequency, d) Keep the column "Adjusted Close" for the stock. e) 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 appear on the same rows. 1. Collect the monthly price for each stock for the past five years. Remember to use the "Adjusted Close." The adjusted closing prices are adjusted to account for events like dividend payments and stock splits. You will compute returns from the percentage change in monthly adjusted prices. 2. Convert these prices to monthly returns as the percentage change in the monthly adjusted 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 (multiply the mean monthly return by 12 and multiply the monthly standard deviation by 12). 4. Add a column in your Excel worksheet with each month's average return across stocks. This is the monthly return to an equally weighted portfollo of these 12 stocks. Compute monthly returns' mean and standard deviation for the equally weighted portfolio. Double-check that the average return on this equally weighted portfolio is equal to the average return of all individual stocks. Convert these monthly statistics to annual statistics (as described in Step 3) for interpretation 5. Using the annual statistics, create an Excel plot with standard deviation (volatility) on the x-axis and average return on the y-axis as follows: - Create three columns on your spreadsheet with the statistics you created in Steps 3 and 4 for each of the individual stocks and the equally weighted portfolio. The first column will have the ticker, the second will have the annual standard deviation, and the third will have the annual mean return. - Highlight the data in the last two columns (standard deviation and mean), and choose Insert> Chart (r Scatter Plot. Complete the chart wizard to finish the plot. 6. What do you notice about the average of the volatilities (standard deviations) of the individual stocks, compared to the volatility of the equally weighted portfolio