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 return for 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.

date	x	y	z	sp500	tbill
1	0.006889	-0.09091	0.047579	0.004053	0.004442
2	-0.19197	-0.04955	0.048119	-0.08007	0.004867
3	-0.06373	-0.12542	0.066231	-0.00495	0.004767
4	-0.01805	-0.14688	0.08365	-0.05348	0.004825
5	0.010131	0.177404	0.084704	0.060699	0.004892
6	-0.00644	0.024529	0.004405	-0.01634	0.004792
7	-0.08	0.02097	-0.14662	0.023934	0.004858
8	-0.03295	-0.03756	0.112971	-0.02191	0.00495
9	0.057577	-0.05807	0.052863	-0.0308	0.004833
10	0.213236	0.152061	-0.3542	0.09672	0.004883
11	-0.06368	-0.08463	-0.13049	-0.02011	0.004867
12	0.241454	0.040561	-0.07758	-0.0509	0.004792
13	0.049328	0.046695	0.014468	0.057844	0.004592
14	0.061051	0.048981	0.029797	0.019062	0.004367
15	0.021342	-0.18802	0.118667	0.062539	0.004267
16	-0.06306	-0.0286	-0.05542	-0.02855	0.004133
17	0.006802	-0.00895	0.095928	-0.00625	0.004092
18	-0.10548	-0.02756	0.02766	-0.03205	0.003958
19	0.057942	0.114221	-0.05622	0.054438	0.004017
20	-0.08268	0.109053	-0.00466	-0.02497	0.003833
21	0.020081	0.180188	-0.04212	0.037944	0.003708
22	-0.11545	0.044183	0.094274	0.038794	0.003775
23	0.066288	-0.07367	-0.01513	-0.03228	0.003733
24	0.099999	-0.0061	-0.00479	0.041009	0.003592
25	-0.06796	0.116578	0.042083	0.056375	0.0036
26	0.194898	0.111956	-0.01198	0.059126	0.003608
27	0.063756	0.155641	0.246924	0.080294	0.003292
28	-0.07517	0.140955	-0.07026	0.062396	0.00375
29	-0.20327	-0.15	-0.03622	-0.1458	0.004117
30	-0.0153	0.154056	-0.12835	-0.01162	0.004233
31	-0.0723	-0.02287	0.085693	0.039438	0.004275
32	-0.09082	0.014026	0.020532	-0.01883	0.0043
33	0.168567	0.115523	-0.02593	0.009076	0.00425
34	-0.04634	-0.00539	-0.00589	0.049946	0.004258
35	0.047367	0.057596	0.082935	0.070449	0.0042
36	0.080996	-0.05615	-0.01801	0.01015	0.00415
37	0.042793	-0.04452	0.04758	0.015732	0.004367
38	0.152502	0.111675	0.120404	0.044587	0.004308
39	0.023269	-0.07075	-0.01538	-0.03448	0.004308
40	0.049634	0.045622	0.037559	0.053154	0.004358
41	-0.0495	-0.04137	-0.1249	-0.05747	0.004392
42	0.008715	0.171746	0.076991	0.078146	0.004367
43	-0.01985	0.043353	0.024479	0.043453	0.004483
44	0.001531	0.077882	0.096421	0.058577	0.004617
45	0.123792	0.169398	0.095861	0.058406	0.0047
46	-0.01852	-0.04522	-0.04475	-0.04261	0.004558
47	0.018868	-0.08366	0.038919	0.005928	0.004358
48	0.046473	0.035478	0.074332	0.061317	0.004417
49	-0.05584	-0.04941	-0.01034	-0.02151	0.004317
50	0.121442	0.235467	0.098485	0.073376	0.004283
51	0.043305	0.036145	0.015385	0.026101	0.004375
52	0.109651	0.088526	0.097046	0.054203	0.004583
53	0.024718	0.063953	-0.0042	0.018814	0.004458
54	-0.11361	0.08586	-0.01517	-0.04575	0.0046
55	0.03498	-0.0726	0.031294	0.002257	0.004567
56	-0.02016	-0.00928	0.039941	0.022853	0.004442
57	-0.02767	-0.03146	-0.00295	0.013431	0.004358
58	-0.02481	-0.09276	0.033537	0.007917	0.004217
59	0.019455	0.130188	-0.02381	0.006934	0.003908
60	0.091294	0.187415	0.012048	0.032617	0.004017

**copy into excel please