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 x2,x2, and x3 be the proportion of the portfolio invested in Alpha, Bravo, and Charlie, respectively, with x1+x2+x3=1 x1+x2+x3=1. Markowitz showed that the expected return of the portfolio is E=0.062x1+0.146x2+0.128x3. 1. 3 shores on the diarinil) 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.0104x2x1+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 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 propgrtion 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. Perform an analysis of the TJ, Inc.'s product-mix problem, and prepare a report for the president of TJ, Inc, that summarizes your findings. Be sure to include information and analysis on the following: 1. The cost per pound of the nuts included in the Regular, Deluxe, and Holiday mixes 2. The optimal product mix and the total profit contribution 3. Recommendations regarding how the total profit contribution can be increased if additional quantities of nuts can be purchased 4. A recommendation as to whether TJ, Inc. should purchase an additional 1000 pounds of almonds for $1000 from a supplier who overbought 5. Recommendations on how profit contribution could be increased (if at all) if T, Inc. does not satisfy all existing orders