In 1952, Harry Markowitz wrote a dissertation at the University of Chicago that revolutionized portfolio management. Markowitz developed the portfolio statistics which are used today to measure the risk and return of a portfolio. Many extensions and concepts of portfolio management were subsequently developed from his original work. Diversification, efficient portfolios, the efficient frontier, CAPM, Fama-French models, arbitrage pricing, and many more extensions were developed. The basic statistics of portfolio theory, as proposed by Markowitz, are calculated in this spreadsheet. Assignment: Each student will complete a Portfolio Statistics spreadsheet. This spreadsheet will calculate the necessary statistics to measure the risk and return of a two-asset portfolio. Monthly data is provided for Starbucks (SBUX) and the S&P 500 (SPY) along with a template to program formulas. The following statistics, both monthly and annual, will be calculated in Excel for both SBUX and SPY: average return, population variance and standard deviation, covariance, correlation coefficient, and beta. The portfolio average return, variance, and standard deviation for a combination of SBUX and SPY will be calculated and programmed to change portfolio weights. The end result will be an efficient portfolio graph, as shown below. The blank template to complete is available on Blackboard

. Calculate the monthly holding period returns for both SBUX and SPY. Remember SPY is the exchange traded fund for the S&P 500, a market proxy. 2. Calculate the monthly statistics for SBUX and SPY using Excel functions. 3. Annualize the monthly statistics for SBUX and SPY. See the Annualizing the Mean and Standard Deviation handout on Blackboard. 4. Calculate the monthly and annual Covariance, Correlation Coefficient, and Beta. 5. Calculate the monthly and annual Portfolio Variance using the 2x2 SBUX/SPY boxes. See the Portfolio Theory notes and example problem #2. 6. Calculate the Portfolio statistics; average annual return, variance, and standard deviation. 7. In worksheet #2, Portfolios, you will be calculating the average portfolio return and portfolio standard deviation for each pair of weights given in each row. The weights represent the percent ownership in each investment. The individual statistics will come from worksheet #1, SBUX vs. SPY and will be linked to the formulas for the average portfolio return and the portfolio standard deviation in each row. The formulas for the average portfolio return and the portfolio standard deviation are summarized on page 11 of the Portfolio Theory handout. Once you have successfully programmed the formulas in the Portfolios worksheet, worksheet #3, Graphs will automatically populate. You do not need to construct the graph. The graph shown in the Guidelines document is your check figure. If you have programmed your formulas correctly in the Portfolios worksheet, the graph you see in the Guidelines will be the result. There are not number check figures.