Find the monthly closing prices for the time period

Homework Problem: Beta, Diversification, and CAPM Required Returns1. Find the monthly closing prices for the time period beginning January 1, 2012 through December 31, 2012, for Apple Corporation (AAPL), AO Smith Corporation (AOS), Boeing (BA), and Chico’s women apparel (CHS). This data is easily found at Yahoo! Finance (http://finance.yahoo.com). [Go to this site. Enter a company’s stock symbol in the “Get Quotes” field. Once you arrive at the company’s “Summary” page, click on the “Historical Prices” link. Enter the required months for the range and ensure that you have selected “monthly” then click on “Get Prices”. Click on “Download to Spreadsheet”. Highlight the “Date” column and paste into your Excel worksheet. Do the same for the column labeled “Adj. Close”. The other data columns are not needed.]2. Find the closing price of the S&P 500 for the Same time period.3. Please add a column on the right of your data sheet computing the return of an equally weighted portfolio of all four of your stocks. Below each of the 6 series (the 4 individual stocks, the market, and the portfolio), compute the historical monthly mean and standard deviation of each series.4. Estimate the set of descriptive statistics provided by the Excel data analysis tools. You may access these tools by the following menu commands: TOOLS, DATA ANALYSIS, DESCRIPTIVE STATISTICS, (use SEPARATE WORKSHEET OPTION for output). Highlight all columns of returns simultaneously and prepare a separate worksheet page (labeled STATS) in the manner of Table 2. Check your daily mean and standard deviation estimates from the data file worksheet to see if they correspond to the respective estimates reported in the descriptive statistics. [The data analysis option should be the last menu item under tools if it has been activated. If it is missing on your menu, you can active it by TOOLS, ADD-INS, and check the box for DATA ANALYSIS TOOLS.5. Estimate the correlation matrix for the six series using TOOLS, DATA ANALYSIS, CORRELATION, (use SEPARATE WORKSHEET OPTION for output and label as CORR). The partially completed table is shown in Table 3. What two stocks when combined in a two-security portfolio should offer the greatest diversifica-tion effect?6. Estimate the beta coefficient for each of the individual securities as well as for the portfolio. Specifically, you can calculate betas in Excel using the graphical trend line method described below. You will produce separate graphs that look like Figure -1 for each stock and for the portfolio. IMPORTANT NOTE: the stock or portfo-lio returns are graphed on the Y axis and the S&P 500 (MKT) returns are always on the X axis. Otherwise, you will produce the wrong number for beta!i. Excel graphical method for estimating beta by regressing stock returns on the S&P 500 index returns: Choose the command INSERT, CHART menu options and complete all 4 steps of the chart wizard. Select XY SCATTER (step 1 of 4), NEXT, DATA IN COLUMNS and also use SERIES and only allow a single stock or portfolio column of returns as the Y-range for a graph, the X-range should be the S&P returns column (step 2 of 4), NEXT, specific preference OPTIONS of your choosing (step 3 of 4), NEXT, and OUTPUT AS NEW SHEET (step 4 of 4), Finish. Then click on the scatter of points and select ADD TRENDLINE, LINEAR. Next click on the options tab and se-lect DISPLAY EQUATION AND DISPLAY R2. See Figure 1 for an example of the market model estimation for GR .ii. You can rename the Excel worksheet name tab to GR.GRPH by using the right mouse button and the rename option.iii. To produce the next graph, create a copy of the sheet with the graph displayed, and then click on the scatter and then change the reference to the dependent variable (the security return) to the next re-spective column of interest.8. Check your trend line regression statistics of beta using a second method. You can do this by using TOOLS, DATA ANALYSIS, REGRESSION, (USE NEW SHEET OUTPUT OPTION). The X coefficient of regression is beta (= .0289 for GR) and should be identical to the beta from your trend line in the graphical method. As in the graphical approach above, the stock or portfolio returns should be the Y-range and the S&P 500 index returns the X-range. See Table 4 for an example using GR. Rename the worksheet name tab to GR.EST. Then repeat for each of the other stocks and for the portfolio.• Briefly describe what the stock’s Beta tells you about the stock9. Prepare a new worksheet within your workbook entitled CAPM. Here you will produce CAPM required returns based upon the betas of the 4 stocks and the portfolio and the following parameters: (see Table 5)i. Use an expected market premium of 5.4%.[Note: This market premium is defined as the expected market return minus the risk-free rate]ii. Use a risk-free (T-bill) rate of 2.0%.These required returns are used for numerous purposes within finance. As you will see in subsequent chap-ters, financial managers use these returns for valuation (i.e., to determine the worth of securities given the cash flows expected from these securities), for capital budgeting (i.e., whether or not to invest in particular new assets), and for cost of capital issues (i.e., determining the cost of funds to the firm).10. Based upon what we have covered in class, prepare an analysis on the data on the stocks and portfolio.1-7. What to turn in:• Printed copy of your data with the standard deviation and mean,• Printed copy of your completed Table 2 (showing the descriptive statistics for the market, each of the 4 individual stocks, and the “port”)• Your completed Table 3• Your completed Table 4 for each of the individual stocks and the “port”• Your completed Table 5• Your regression charts• Your analysis