Your client is obsessed with technology stocks and wants to invest only in Apple, Google (GOOGL) and Microsoft stock. Download weekly price data for those stocks from Yahoo Finance for 2012. For each stock, you should have one tab (named something like Apple, Google, MSFT) that looks like this (Apple example): A B D E F G 1 Date Open High Low Close Adj. Close Volume 58.49 60.39 58.43 60.34 40.55 287951300 2 1/1/12 54 12/30/12 72.93 76.48 72.71 | 76.02 54.39 164873100 Depending on the date you downloaded the data, your adjusted close prices may be different, but that is irrelevant. Hint: Select cell B2 and choose Window->Freeze Panes to always keep the date and header row in view. Part 1 Attempt 1/10 for 9 pts. Add a column labeled "Weekly return" to each tab and calculate the total return for each week, using the adjusted close. What was the return for Google from 12/23/12 to 12/30/12? Hint: If you select all 3 tabs at once by holding control/command before clicking the tab name and add the required formulas to one tab, the changes will appear on all 3 tabs. Don't forget to unselect the 3 tabs by clicking on a different tab when you're done, or you may accidentally overwrite content on other tabs. 4+ decimals Submit Part 2 Attempt 1/10 for 9 pts. On a new tab, create the covariance matrix using Excel's COVARIANCE.SO formula. What is the weekly covariance of returns for Google and Microsoft based on the historical data (report 7 digits)? 6+ decimals Submit Attempt 1/10 for 9 pts. Part 3 On the same tab, create the annualized covariance matrix. Covariance, just like variance, is additive: the covariance of 2-weekly returns is twice the covariance of weekly returns. What is the annualized covariance of returns for Apple and Google? Create the border-multiplied covariance matrix. What is the variance of the equally-weighted portfolio? 4+ decimals Submit Part 5 Attempt 1/10 for 9 pts. You did extensive security research and came up with the following annual expected returns: E(Apple) = 0.07 E(rGoogle) = 0.06 E(TMSFT) = 0.05 What is the expected return of the equally-weighted portfolio? Use Excel's SUMPRODUCT() function. 3+ decimals Submit Part 6 Attempt 1/10 for 9 pts. The yield on treasury bills is 2%. What is the Sharpe ratio of the equally-weighted portfolio? 3+ decimals Submit Part 7 - Attempt 1/10 for 9 pts. Determine the optimal risky portfolio (ORP) using Excel's Solver tool. What is the Sharpe ratio of the portfolio? 3+ decimals Submit Part 8 - Attempt 1/10 for 9 pts. You have $40,000 to invest, and want to achieve an expected return of 5% on the complete portfolio, including treasury bills. How much money should you put into Apple stock? Your client is obsessed with technology stocks and wants to invest only in Apple, Google (GOOGL) and Microsoft stock. Download weekly price data for those stocks from Yahoo Finance for 2012. For each stock, you should have one tab (named something like Apple, Google, MSFT) that looks like this (Apple example): A B D E F G 1 Date Open High Low Close Adj. Close Volume 58.49 60.39 58.43 60.34 40.55 287951300 2 1/1/12 54 12/30/12 72.93 76.48 72.71 | 76.02 54.39 164873100 Depending on the date you downloaded the data, your adjusted close prices may be different, but that is irrelevant. Hint: Select cell B2 and choose Window->Freeze Panes to always keep the date and header row in view. Part 1 Attempt 1/10 for 9 pts. Add a column labeled "Weekly return" to each tab and calculate the total return for each week, using the adjusted close. What was the return for Google from 12/23/12 to 12/30/12? Hint: If you select all 3 tabs at once by holding control/command before clicking the tab name and add the required formulas to one tab, the changes will appear on all 3 tabs. Don't forget to unselect the 3 tabs by clicking on a different tab when you're done, or you may accidentally overwrite content on other tabs. 4+ decimals Submit Part 2 Attempt 1/10 for 9 pts. On a new tab, create the covariance matrix using Excel's COVARIANCE.SO formula. What is the weekly covariance of returns for Google and Microsoft based on the historical data (report 7 digits)? 6+ decimals Submit Attempt 1/10 for 9 pts. Part 3 On the same tab, create the annualized covariance matrix. Covariance, just like variance, is additive: the covariance of 2-weekly returns is twice the covariance of weekly returns. What is the annualized covariance of returns for Apple and Google? Create the border-multiplied covariance matrix. What is the variance of the equally-weighted portfolio? 4+ decimals Submit Part 5 Attempt 1/10 for 9 pts. You did extensive security research and came up with the following annual expected returns: E(Apple) = 0.07 E(rGoogle) = 0.06 E(TMSFT) = 0.05 What is the expected return of the equally-weighted portfolio? Use Excel's SUMPRODUCT() function. 3+ decimals Submit Part 6 Attempt 1/10 for 9 pts. The yield on treasury bills is 2%. What is the Sharpe ratio of the equally-weighted portfolio? 3+ decimals Submit Part 7 - Attempt 1/10 for 9 pts. Determine the optimal risky portfolio (ORP) using Excel's Solver tool. What is the Sharpe ratio of the portfolio? 3+ decimals Submit Part 8 - Attempt 1/10 for 9 pts. You have $40,000 to invest, and want to achieve an expected return of 5% on the complete portfolio, including treasury bills. How much money should you put into Apple stock