date x y z sp500 tbill 1 0.0069 -0.0909 0.0476 0.0041 0.0044 2 -0.192 -0.0496 0.0481 -0.0801 0.0049 3 -0.0637 -0.1254 0.0662 -0.0049 0.0048 4 -0.0181 -0.1469 0.0836 -0.0535 0.0048 5 0.0101 0.1774 0.0847 0.0607 0.0049 6 -0.0064 0.0245 0.0044 -0.0163 0.0048 7 -0.08 0.021 -0.1466 0.0239 0.0049 8 -0.033 -0.0376 0.113 -0.0219 0.005 9 0.0576 -0.0581 0.0529 -0.0308 0.0048 10 0.2132 0.1521 -0.3542 0.0967 0.0049 11 -0.0637 -0.0846 -0.1305 -0.0201 0.0049 12 0.2415 0.0406 -0.0776 -0.0509 0.0048 13 0.0493 0.0467 0.0145 0.0578 0.0046 14 0.0611 0.049 0.0298 0.0191 0.0044 15 0.0213 -0.188 0.1187 0.0625 0.0043 16 -0.0631 -0.0286 -0.0554 -0.0286 0.0041 17 0.0068 -0.0089 0.0959 -0.0063 0.0041 18 -0.1055 -0.0276 0.0277 -0.032 0.004 19 0.0579 0.1142 -0.0562 0.0544 0.004 20 -0.0827 0.1091 -0.0047 -0.025 0.0038 21 0.0201 0.1802 -0.0421 0.0379 0.0037 22 -0.1155 0.0442 0.0943 0.0388 0.0038 23 0.0663 -0.0737 -0.0151 -0.0323 0.0037 24 0.1 -0.0061 -0.0048 0.041 0.0036 25 -0.068 0.1166 0.0421 0.0564 0.0036 26 0.1949 0.112 -0.012 0.0591 0.0036 27 0.0638 0.1556 0.2469 0.0803 0.0033 28 -0.0752 0.141 -0.0703 0.0624 0.0038 29 -0.2033 -0.15 -0.0362 -0.1458 0.0041 30 -0.0153 0.1541 -0.1283 -0.0116 0.0042 31 -0.0723 -0.0229 0.0857 0.0394 0.0043 32 -0.0908 0.014 0.0205 -0.0188 0.0043 33 0.1686 0.1155 -0.0259 0.0091 0.0043 34 -0.0463 -0.0054 -0.0059 0.0499 0.0043 35 0.0474 0.0576 0.0829 0.0704 0.0042 36 0.081 -0.0562 -0.018 0.0102 0.0042 37 0.0428 -0.0445 0.0476 0.0157 0.0044 38 0.1525 0.1117 0.1204 0.0446 0.0043 39 0.0233 -0.0708 -0.0154 -0.0345 0.0043 40 0.0496 0.0456 0.0376 0.0532 0.0044 41 -0.0495 -0.0414 -0.1249 -0.0575 0.0044 42 0.0087 0.1717 0.077 0.0781 0.0044 43 -0.0198 0.0434 0.0245 0.0435 0.0045 44 0.0015 0.0779 0.0964 0.0586 0.0046 45 0.1238 0.1694 0.0959 0.0584 0.0047 46 -0.0185 -0.0452 -0.0447 -0.0426 0.0046 47 0.0189 -0.0837 0.0389 0.0059 0.0044 48 0.0465 0.0355 0.0743 0.0613 0.0044 49 -0.0558 -0.0494 -0.0103 -0.0215 0.0043 50 0.1214 0.2355 0.0985 0.0734 0.0043 51 0.0433 0.0361 0.0154 0.0261 0.0044 52 0.1097 0.0885 0.097 0.0542 0.0046 53 0.0247 0.064 -0.0042 0.0188 0.0045 54 -0.1136 0.0859 -0.0152 -0.0457 0.0046 55 0.035 -0.0726 0.0313 0.0023 0.0046 56 -0.0202 -0.0093 0.0399 0.0229 0.0044 57 -0.0277 -0.0315 -0.0029 0.0134 0.0044 58 -0.0248 -0.0928 0.0335 0.0079 0.0042 59 0.0195 0.1302 -0.0238 0.0069 0.0039 60 0.0913 0.1874 0.012 0.0326 0.004

2. You are a team in a portfolio management company who decides on equity asset allocations. Your financial analysts have identified the flowing three firms for potential investment and have provided you with their return data in an Excel data file called (project_data). The data set also contain information on returns for S&P 500 Index and T-bill returns during the same time.

X, Y and Z

a. Due to resource and regulatory constraints, you are able to invest only in any two of the three firms. Construct the efficient frontiers using the excess returns of different stocks combinations and conclude which combination may provide you with the best risk return trade-off by inspecting their efficient frontiers (for your convenience, please only use 10 portfolios to construct your individual frontier). Then explain why the stock combination may provide a better trade-off. Please present the efficient frontiers with proper labeling and make the necessary conclusions. [9 Marks]

b. Do the necessary computation and determine the minimum variance portfolios risk and returnfor each of the combination portfolios. Then conclude which stock combination provides the best minimum variance portfolio. Does the efficient frontier that you have determined to be the best in part (a) contain the minimum variance portfolio that you have identified as the best? Please show your computation and make your conclusion. [9 Marks]

c. Which stock combination provides you with the best total risk-adjusted return? Now use the best combination of stocks that you have identified to construct a portfolio that will have systematic risk exposure twice that of the market. Also determine the expected return, standard deviation of the portfolio. We will refer to this portfolio as the optimum portfolio below. [10 Marks]

d. Determine whether your optimum portfolio is beating the market portfolio (SP 500) on a systematic risk-adjusted basis. Do the necessary computation and make your conclusion. [5 Marks]

e. Can you shed any light on what are the drivers of the systematic risk of your portfolio? In particular, can you make any statement on the kind of industries your preferred stocks or firms are from and why? How do the individual stocks contribute to the systematic risk of your portfolio?[5 Marks]

f. Now you have the best portfolio on the basis of the mean-variance criteria. So you should be able to make the recommendation for investment for your firm. However, it is always a good practice to include some reservations that you may have on your portfolio strategy. Please list them, if you have any. Also, please explain the reasons for your reservations. [5 Marks]

g. Suppose all these firms are mature firms and their dividends are expected to grow at a constant rate of 3% for ever. If they all paid the same cash dividend of $1 lately, will they all have the same price? Please do the necessary computation using the data provided and make your conclusion. [5 Marks]