Part D: Taking Stock (50 pointsl The spreadsheet Stocks.xlsx, available on Blackboard, contains a sample of weekly adjusted close prices for Tesla, Apple, Netix, Alphabet (Google), Ford, and the SPY (S&P 500 etf). 1. Use the two years of data Excel to form the weekly log (natural log) returns. Compute the mean and standard deviation of the weekly log return for each of the 6 investments. The Excel functions average and stdev.s will be helpful 2. Calculatethe an nualizedvolatilityof each asset its "beta" based on a regression of the weekly retums of each asset on the SPY, and the correlation of the weekly retum of each asset to the SPY retum. There are 6 separate regressions and the Excel functions siopeand cor-rel may be helpful. 3. Assume the SPY (the market) is predicted to have a 10% return next year. Use your regression estimates from the preceding question to predict the return of each investment. SPY TSLA APPL NFLX GOOG F Beta Correlation to SPY Expected return 4. Observing that the volatility of Netix is significantly larger than that of Ford you decide to hold one-fth of your portfolio in Netix and the remaining four-fths of your portfolio in Ford. What is the expected return and the risk (standard deviation) of the portfolio based on these weights, the volatilities and the correlation between the two investments? Note you will need to use your answers to questions 2 and 3