Spreadsheet Assignment 2: Asset and Portfolio (Due October 15) Please answer the following questions with spreadsheet, answers without spreadsheet analysis will receive 0 points. Please highlight the cells that contain the answers with a different color and provide some necessary description. 1. Given the stock prices below for stock 1 and 2, answer the following questions. A. What is the average return, variance of return, and standard deviation of return for stock 1 and stock 2? (1 pt) B. What is the covariance of return between stock 1 and 2? What is the portfolio average return, variance of return if holding 20% in stock 1? (1 pt) C. Create a data table showing how portfolio variance and mean of return change when changing the weight of stock 1. The range of weight in stock 1 is -30% to 130%. (1 pt) Stock 1 Stock 2 2018-08- 01 42.65528 106.017 2018-09- 01 41.88643 108.3578 2018-10- 01 44.78922 101.1952 2018-11- 01 47.8318 105.0607 2018-12- 01 44.59543 96.64634 2019-01- 01 43.67527 99.36771 2019-02- 01 45.62191 106.5993 2019-03- 01 47.39324 112.7021 2019-04- 01 45.83832 124.7998 2019-05- 01 44.01085 118.1871 2019-06- 01 46.26201 128.4845 2019-07- 01 44.75584 130.7

2019-08- 01 47.59179 132.2251 2019-09- 01 49.39203 133.7912 2019-10- 01 49.48204 137.9677 2019-11- 01 49.80675 145.6759 2019-12- 01 50.76584 152.2742 2020-01- 01 49.14531 164.373 2020-02- 01 45.24579 156.4358 2020-03- 01 44.88657 152.6998 2020-04- 01 47.9943 173.5167 2020-05- 01 48.45135 177.4283 2020-06- 01 46.55146 197.5935 2020-07- 01 48.53579 199.0499 2020-08- 01 50.60928 218.9733 2020-09- 01 50.79713 204.7089 2020-10- 01 48.66244 197.059 2. Please choose 15 stocks at your own choice and get their historical monthly price for any consecutive 3 years using the spreadsheet I provided in week 4. The spreadsheet for downloading price is called Multiple Stock Quote Downloader. The price we are going to use is adjusted close price. You will also need the price for S&P 500, the ticker for that is ^GSPC. So you will download 16 prices. A. Show the price and return of the stocks you chose for each month. (1 pt) B. Get the matrix of excess returns for the 15 stocks based on the previous step. (1 pt) C. Get the variance-covariance matrix of these 15 stocks based on the previous step. (1 pt) D. Get the weight of the Global minimum variance portfolio (GMVP) based on the previous step, and the mean and variance of the GMVP. (1 pt) E. Calculate the alpha, beta, and R square for each of these 15 stocks. (1 pt) F. If you form an equal-weighted portfolio with the 15 stocks, what would be the portfolio alpha, beta, and R square? How do these numbers differ from the weighted average alpha, beta, and R square of the 15 stocks that form the portfolio? What do you think is driving the result? (2 pts)