2 Investment company, PMBA Inc, intends to invest a given amount of money in four stocks (Stock A, Stock B, Stock C, and Stock D). 3 From the past data, the means and standard deviations of annual returns have been estimated. The correlations among the annual returns on the stocks are also calculated. 4 5 Stock Data 6 7 Mean return 8 9 10 11 12 13 14 StDev of return (0) Correlations (R) A (BUD C A A 0.12 0.07 Title 1 0.6 0.4 0.4 01 B 0.15 0.06 B 0.6 1 0.7 0.7 COO 0.11 0.09 C lo 21 22 (B) Solve the formulated model using Excel Solver.(5 pts) 23 Use tab "Q4 SS Model" 01 SS Model 0.4 0.7 1 0.5 D 0.13 0.07 15 16 The company wants to find a minimum variance portfolio that yields an expected annual return of at least 10%. 17 D 18 Answer A-C Below 19 (A) Formulate the problem as a Nonlinear programming spreadsheet model. Use next worksheet tab "Q4 SS Model" to develop your formulation. (10 pts) 20 Use tab "Q4 SS Model" 0.4 0.7 4751 24 25 (C) What is the mix of stocks (percentage of each stock) that minimizes portfolio variance? (5 pts) 26 Stock Fraction or % 27 Stock A 28 Stock B 29 Stock C 30 Stock D 31 0.5 02 02 SS Model 03 03 SS Model T

Investment company, PMBA Inc, intends to invest a given amount of monoy in four stocks (5tock A, Stock B, Stock C. and Stock D) From the past dats, the means and standard devlations of annuat retums have been estimated. The corretations among the anniat feturns on the stocks are also calculs The company wants fo find a minimum variance porfotio that yields an expected annual fetum of at least 10% Answer A.C Below (A) Formulate the problem as a Nanlinear programming spreadsheet model. Use next workheet tab "Qt SS Model" to develop your farmulation. (10 pts) Use tab "O4 sS Model" (B) Solve the formulated model using Excel Solver:(5 pts) Use tab "O4 55 Model" (C) What is the mix of stochs (percentage of each stock) that minimizes portfolio variance? (5 pts) Answer A-C Below (A) Formulate the problem as a Nonlinear programming spreadsheet model. Use Use tab "Q4 SS Model" (B) Solve the formulated model using Excel Solver.(5 pts) Use tab "Q4 SS Model" (C) What is the mix of stocks (percentage of each stock) that minimizes portfolio v Stock Fraction or % Stock A Stock B Stock C Stock D Q4. Portfolio Optimization at PMBA Inc Stock data \begin{tabular}{|l|c|c|c|c|} \hline & A & B & C & D \\ \hline Mean return & 0.12 & 0.15 & 0.11 & 0.13 \\ \hline StDev of return () & 0.07 & 0.06 & 0.09 & 0.07 \\ \hline \end{tabular} \begin{tabular}{|c|r|r|r|r|} \hline Correlations (R) & \multicolumn{1}{|c|}{ A } & \multicolumn{1}{|c|}{ B } & \multicolumn{1}{|c|}{ C } & \multicolumn{1}{|c|}{ D } \\ \hline A & 1 & 0.6 & 0.4 & 0.4 \\ \hline B & 0.6 & 1 & 0.7 & 0.7 \\ \hline C & 0.4 & 0.7 & 1 & 0.5 \\ \hline D & 0.4 & 0.7 & 0.5 & 1 \\ \hline \end{tabular}