PLEASE PROVIDE EXCEL EXPLANATION AND FORMULAS AS I AM NOT GETTING ERRORS.

1. Go to Yahoo! nance, and download monthly return series for the ve years (Jan 2014-Jan 2019) for the following three stocks: Oracle (ORCL), General Mills (GIS), and Conoco Phillips (COP). For each stock, calculate the mean monthly return for each stock, standard deviation, and list the highest two and lowest two month returns. In a couple of sentences, briey describe (just intuitively) which stock looks the safest, and which looks the riskiest. (Hint: remember that youre downloading stock prices, but what you want to work with is stock returns.)

2. For each pair of stocks (i.e., ORCL-GIS, GIS-COP, and ORCL-COP), calculate the covariance (Excel function COVAR()) in returns over the sample period. Also, report the correlation coecient (Excel function CORREL()) for each pair of stocks. Which two stocks have the highest correlation? Which two are least correlated?

3. For each pair of stocks, calculate the expected return and standard deviation of a portfolio with weights that vary between 0% and 100%, in 10% intervals. For example, take ORCL-GIS. Your rst portfolio will be 0% in ORCL and 100% in GIS. The second portfolio will be 10% in ORCL, and 90% in GIS. Your third portfolio will be 20% in ORCL, and 80% in GIS, and so on, all the way until your nal portfolio, which is 100%in ORCL, and 0% in GIS. Perform this same calculation for the other two pairs of stocks. For each portfolio, calculate the expected return and standard deviation using your estimates from question (2) above. HINT: Youre going to conduct calculations for 11 portfolios, for three dierent pairs of stocks, for a total of 33 return-standard deviation pairs.

4. Using EXCEL or a piece of graph paper (drawing by hand), make a plot with the quantities in part (3) above. The x-axis will be standard deviation, and the y-axis will be expected return. Connect the dots within each of your three portfolios. Note: if you do this by hand, because youre turning this in electronically, youll need to take a picture of the image as part of the deliverable.

5. Identify with a red line/pen the set of ecient portfolios in your graph. The denition of an ecient portfolio is one that, for a given standard deviation, oer the maximum possible expected return. In other words, ecient portfolios are those for which there are no other possible portfolios that are North and/or West of them on the mean-volatility graph. HINT: The result will be a funny looking curve that connects dierent parts of the parabolas you graphed in part (4). What statement can you make about the set of possible portfolios that lie either underneath and/or to the right of the set of ecient portfolios?

6. Call portfolio A the 50%-50% portfolio between COP and GIS, and call portfolio B the 50%-50% portfolio between ORCL and GIS. Conduct the same exercise (0-100, 10-90, 20-80, etc.) between portfolios A and B as you did in part (3) using individual stocks. Here, the 0-100 portfolio will be portfolio A, the 10-90 will be 10% portfolio A and 90% portfolio B, etc. For each of these mixes between portfolios A and B, calculate the expected return and standard deviation. HINT: Youll want to start by calculating the monthly returns of portfolios A and B, which will give you two time-series. Think about the returns of these portfolios just like you would think about the returns of individual stocks. For example, you have an estimate of the mean return of portfolio A, and an estimate of the volatility of portfolio A. Same for B. You can also calculate a sample covariance between the returns of portfolio A and portfolio B. You will use these quantities to trace out the curve required for this question.

7. Using a purple line, trace out the set of ecient portfolios, taking into account the new portfolios you calculated in part (6). How has this set changed, if at all, from the previous set of ecient portfolios? What is the intuition behind this change i.e., has it improved the ecient frontier? Why does this make sense?

EXTRA CREDIT THIS WILL BE CHALLENGING. Using solver in EXCEL, identify the set of ecient portfolios for all possible combinations of the three stocks given above. Youll want to type in the formula for portfolio standard deviation, as a function of possible weights on each of the three stocks. Then, for a given expected return, youll want EXCEL to search (using SOLVER) for the set of weights that minimizes the portfolio standard deviation. The resulting pair of (expected return, standard deviation) is a point on the ecient frontier. Go in small increments of expected return, and trace out the new ecient frontier. How does the new ecient frontier compare to the one you drew in part (6)?