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. ${?}^{1}He$ 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. The covariances are given in the variance$-$covariance matrix

in Table $2.$ For example, the covariance between Alpha and Bravo is $0\mathrm{.}0187.{?}^2$

Table $2.$ Variance$-$Covariance Matrix of Returns

According to Markowitz, if $x_1,x_2,$ and $x_3$ is the proportion of the portfolio invested in

Alpha, Bravo, and Charlie, respectively, then

$x_1+x_2+x_3=1$

Equation $1$

Markowitz showed that the expected return of the portfolio is

$E=0\mathrm{.}062x_1+0\mathrm{.}146x_2+0\mathrm{.}128x_3.$

Equation $2$

His seminal paper is H$.$M$.$ Markowitz, $1952,\backslash$Portfolio Selection," Journal of Finance, $7(1),77-91.$

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\mathrm{.}0146x_1{x}_1+0\mathrm{.}0187x_1{x}_2+0\mathrm{.}0145x_1{x}_3$

$+0\mathrm{.}0187x_2{x}_1+0\mathrm{.}0854x_2{x}_2+0\mathrm{.}0104x_2{x}_3$

$+0\mathrm{.}0145x_3{x}_1+0\mathrm{.}0104x_3{x}_2+0\mathrm{.}0289x_3{x}_3.$

Equation $3$

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.