In this practice problem, you will collect data from Yahoo Finance on monthly returns for 10 stocks from the DJIA universe. You will also have to collect data on the S&P500 monthly return (proxy for the market index), and the 13-week Treasury-bill rate (proxy for the risk-free rate). The sample period is 2007.1 2016.12 with 120 observations. Then you use Excel to do the following analysis.

1. Calculate the mean return and standard deviation of return for each of the 10 stocks, the market index and the risk-free rate. Using standard deviation as a measure of risk, rank the stocks according to their riskiness?

2. Calculate the correlation coefficients among the 10 stocks, and the correlation coefficients between the market index and each of the 10 stocks.

3. Take any two of the stocks (denoted by stock 1 and stock 2). Construct a portfolio consisting of 40% of stock 1 and 60% of stock 2. Calculate the mean and standard deviation of this portfolio. Compare the standard deviation of the portfolio with that of stocks 1 and 2. Do you find the risk of the portfolio lower than the risks of the individual stocks? Can you change the portfolio weight to further reduce the risk (standard deviation)? Find the portfolio combination that gives the lowest risk? What are this portfolios mean return and standard deviation?

Hint: use solver or mathematically solve the portfolio weights (pay attention to the lecture) 4. Form an equally weighted portfolio of the 10 stocks. Calculate the mean return and standard deviation of return for this equally-weighted portfolio. Does this portfolio appear to be less risky than the individual stocks?

5. From Yahoo Finance, collect data on the current market capitalization of equity for each of the 10 companies. Using these market capitalization data to construct the value-weighted average portfolio of the 10 stocks. Calculate the mean return and standard deviation of return for this valueweighted portfolio. Does this portfolio appear to be less risky than the individual stocks?

6. Calculate the beta coefficient for each of the 10 stocks. Rank the stocks according to their market (systematic) risks. Do you get the same ranking as in (1)?

7. Calculate the beta coefficient for (a) the portfolio constructed in (3) with 40% of stock 1 and 60% of stock 2; (b) the equally-weighted portfolio of the 30 stocks constructed in (4); and (c) the value-weighted portfolio of the 30 stocks constructed in (5).

8. Estimate the costs of equity capital for these 10 companies.

9. Now collect data from 2017.1 to 2018.12 for these 10 stocks. Compute the average return for the past 24 months and check whether stocks with higher betas in general yield higher returns.

Companies:

3M

Apple

Boeing

Coca Cola

Microsoft

Disney

McDonald's

Nike

Verizon

Johnson and Johnson