Stock Selection Case Study Markowitz won the Nobel Prize for his work in stock portfolio theory. He was the first to measure portfolio risk using the variance of returns. 1He introduced stock selection based on an "efficient frontier", namely, by picking the stocks that give the portfolio "with minimum variance for a given return" and "maximum return for a given variance." An investor is considering buying a combination of 3 stocks - "Alpha," "Bravo," and "Charlie" - using Markowitz's portfolio optimization method. The expected returns and risk of these stocks are given in Table 1. Table 1. Return and Risk Data for 3 stocks An investor would like to purchase some combination of the 3 stocks to build a portfolio that gives at least a 10% return at the lowest possible risk (i.e., at minimum portfolio variance). A portfolio's variance depends on how each individual stock's price fluctuations are correlated with every other stock's, known as the covariance of returns, and so she identified the covariances given in the variance-covariance matrix in Table 2. For example, the covariance between Alpha and Bravo is 0.0187.2 Table 2. Variance-Covariance Matrix of Returns She recalled that according to Markowitz, she should let x1,x2, and x3 be the proportion of the portfolio invested in Alpha, Bravo, and Charlie, respectively, with x1+x2+x3=1. Markowitz showed that the expected return of the portfolio is E=0.062x1+0.146x2+0.128x3. 1. His seminal paper is H.M. Markowitz, 1952, \Portfolio Selection," Journal of Finance, 7(1),7791. 2. Some details may be useful. First, covariances in this case are positive, indicating that all 3 stock prices tend to rise and fall together to some degree. Second, the covariance of a stock with itself is simply its variance (as shown on the diagonal). Similarly, Markowitz showed that the portfolio's variance is the following function of covariances: V=0.0146x1x1+0.0187x1x2+0.0145x1x3+0.0187x2x1+0.0854x2x2+0.0104x2x3+0.0145x3x1+0.0104x3x2+0.0289x3x3. You are asked to create a spreadsheet model to develop an optimal feasible solution of the investment portfolio to present to the investor. Your solution should inform the investor how she should proportion her investments across the three stocks in her portfolio. In your final writeup, please make sure to identify the decision variables, the objective function, and the constraints. Describe whether any of the constraints are integer and binary? Describe the methodology used to develop the solution and identify whether your solution is an optimal feasible solution or a feasible solution? How did you know your solution was optimal or not? Did you have to make a Solver selection to ensure you solution was optimal? Finally, explain the investment solution you have developed for the investor and whether your solution meets her portfolio criteria of risk and return. There is no risk free rate. I believe the risk (variance of returns) is used in place. Stock Selection Case Study Markowitz won the Nobel Prize for his work in stock portfolio theory. He was the first to measure portfolio risk using the variance of returns. 1He introduced stock selection based on an "efficient frontier", namely, by picking the stocks that give the portfolio "with minimum variance for a given return" and "maximum return for a given variance." An investor is considering buying a combination of 3 stocks - "Alpha," "Bravo," and "Charlie" - using Markowitz's portfolio optimization method. The expected returns and risk of these stocks are given in Table 1. Table 1. Return and Risk Data for 3 stocks An investor would like to purchase some combination of the 3 stocks to build a portfolio that gives at least a 10% return at the lowest possible risk (i.e., at minimum portfolio variance). A portfolio's variance depends on how each individual stock's price fluctuations are correlated with every other stock's, known as the covariance of returns, and so she identified the covariances given in the variance-covariance matrix in Table 2. For example, the covariance between Alpha and Bravo is 0.0187.2 Table 2. Variance-Covariance Matrix of Returns She recalled that according to Markowitz, she should let x1,x2, and x3 be the proportion of the portfolio invested in Alpha, Bravo, and Charlie, respectively, with x1+x2+x3=1. Markowitz showed that the expected return of the portfolio is E=0.062x1+0.146x2+0.128x3. 1. His seminal paper is H.M. Markowitz, 1952, \Portfolio Selection," Journal of Finance, 7(1),7791. 2. Some details may be useful. First, covariances in this case are positive, indicating that all 3 stock prices tend to rise and fall together to some degree. Second, the covariance of a stock with itself is simply its variance (as shown on the diagonal). Similarly, Markowitz showed that the portfolio's variance is the following function of covariances: V=0.0146x1x1+0.0187x1x2+0.0145x1x3+0.0187x2x1+0.0854x2x2+0.0104x2x3+0.0145x3x1+0.0104x3x2+0.0289x3x3. You are asked to create a spreadsheet model to develop an optimal feasible solution of the investment portfolio to present to the investor. Your solution should inform the investor how she should proportion her investments across the three stocks in her portfolio. In your final writeup, please make sure to identify the decision variables, the objective function, and the constraints. Describe whether any of the constraints are integer and binary? Describe the methodology used to develop the solution and identify whether your solution is an optimal feasible solution or a feasible solution? How did you know your solution was optimal or not? Did you have to make a Solver selection to ensure you solution was optimal? Finally, explain the investment solution you have developed for the investor and whether your solution meets her portfolio criteria of risk and return. There is no risk free rate. I believe the risk (variance of returns) is used in place