Answered step by step
Verified Expert Solution
Question
1 Approved Answer
I need help with this part In your final writeup, please make sure to identify the decision variables, the objective function, and the constraints. Describe
I need help with this part
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. 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 bulld 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. Markovitz, 1952, XPortfolio Selection, "Journal of Finance, 7(1), 77-91. 2. Sonse 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. 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. 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 bulld 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. Markovitz, 1952, XPortfolio Selection, "Journal of Finance, 7(1), 77-91. 2. Sonse 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 Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started