You are given price information on 10 stocks, the FTSE All Share Index, and risk-free rate (UK T-bill rate) from 07/2015 to 05/2020. Data can be accessed in the Excel spread sheet (I've listed the data after the question below). You are required to answer the following questions independently.

Part a. (25 marks in total)

(i) Calculate each stock’s average monthly return, its return variance and standard deviation and beta over the period 07/2015 to 05/2020, using the price information of the FTSE All Share Index as a proxy for market portfolio. Summarize your results in a table, and briefly describe how these are calculated (Please do not use Excel functions as your explanation). (9 marks)

(ii) Construct three types of portfolios. Portfolio A consists of any 3 of the stocks, Portfolio B consists of any 6 of the stocks, and Portfolio C consists of all 10 stocks. Assuming that each stock has equal weight in the portfolio, calculate Portfolio A, B, and C’s average monthly returns, return variances and standard deviations, and betas. Summarize your results in a table, and briefly describe how these are calculated (Please do not use Excel functions as your explanation). (8 marks)

(iii) If you rank portfolio A, B and C according to their betas and return variances respectively, will the three portfolios have same ranking? Can you provide economic reasons as to why the two rankings should be (in)consistent with each other? (8 marks)

Part b. (15 marks in total)

(i) If you are asked to construct your own portfolio by using information about the individual stocks in the excel spreadsheet, are there any stocks which you should not include in your portfolio? Can you provide your justifications? (You may draw performance graphs of one stock against the market portfolio). (9 marks)

(ii) Construct your own portfolio which consists of a different number of stocks from Portfolio A, B, and C and is “the best” for its return/risk characteristics. And can you justify why you choose such a portfolio? (6 marks)

Data sheet (no place for attachements, so I will write it down);

Date	Unilever PLC (ULVR.L)	easyJet plc (EZJ.L)	Vodafone Group Plc (VOD.L)	BP p.l.c. (BP.L)	Rio Tinto plc (RIO.L)	Rolls-Royce Holdings plc (RR.L	GlaxoSmithKline plc (GSK.L)	The Royal Bank of Scotland Group plc (RBS.L)	United Utilities Group PLC (UU.L)	Legal & General Group Plc (LGEN.L)	FTSE All	UK 3 MONTHS TREASURY BILLS YIELD (EP)
01/07/2015	2907	1644	241.85	395.45	2486.5	794	1396	342.2	892	260.7	3652.79	0.469
01/08/2015	2623	1683	226.7	360.5	2388	743.5	1340.5	336.8	855	252.7	3434.66	0.46
01/09/2015	2686	1777	208.45	334	2210	677	1266	315	925	238.1	3335.92	0.489
01/10/2015	2893	1750	214.25	386.4	2358.5	687.5	1405	317.6	988.5	261.6	3484.6	0.478
01/11/2015	2834	1653	224.25	385.25	2208.5	606.5	1355.5	302.4	966.5	271.5	3492.13	0.483
01/12/2015	2926.5	1740	221	354	1979.5	575	1373	302	935.5	267.8	3444.26	0.459
01/01/2016	3085	1548	223.9	376.1	1714	555	1439	252.7	956.5	243.5	3335.9	0.468
01/02/2016	3092	1506	219.7	351.45	1904	679	1399.5	223.9	925.5	227	3345.84	0.463
01/03/2016	3152.5	1519	221.2	350.15	1955.5	682	1412	222.7	923	235.2	3395.19	0.468
01/04/2016	3054.5	1473	219.2	373.75	2300.5	669.5	1459	230	939.5	223.1	3421.7	0.453
01/05/2016	3146	1533	230.85	357.25	1942.5	619	1445	246.5	970	239.4	3429.77	0.478
01/06/2016	3579	1086	227.65	438.15	2294	711.5	1604.5	171.6	1035	190.7	3515.45	0.442
01/07/2016	3533.5	1041	229.55	426.85	2461.5	791	1688	192.4	1017	205.7	3653.83	0.293
01/08/2016	3529	1105	229.85	427.9	2301	770	1639	201.5	971.5	210.3	3697.19	0.23
01/09/2016	3654.5	1007	221.75	450	2574.5	720	1643	178.8	1003	218.7	3755.34	0.217
01/10/2016	3418	937	224.8	483.7	2839.5	726.5	1618.5	189.1	940	209.4	3768.14	0.233
01/11/2016	3196	990	193.9	459.45	2990	676.5	1495.5	194.3	883	235.8	3692.4	0.203
01/12/2016	3292.5	1005	199.85	509.6	3158.5	668	1562	224.6	901	247.6	3873.22	0.218
01/01/2017	3227.5	950	194.35	472.85	3491	667.5	1528	221.8	917	234.9	3858.26	0.226
01/02/2017	3819	951	201.9	453.55	3297	787	1648	237.8	978.5	248.3	3953.42	0.213
01/03/2017	3939.5	1026	208.1	457.55	3209.5	754	1659.5	242.1	993.5	247.3	3990	0.217
01/04/2017	3972	1168	199.05	442.5	3062	812	1550	265.4	973.5	246.1	3962.49	0.165
01/05/2017	4331.5	1414	231.5	466.7	3102.5	867.5	1703.5	260	1029	251.8	4116.08	0.182
01/06/2017	4155	1359	217.75	442.8	3242	891	1635.5	247.2	867.5	258.3	4002.18	0.207
01/07/2017	4321.5	1236	222	445.8	3520	888	1514.5	248.6	897.5	268.4	4046.2	0.153
01/08/2017	4519	1196	221.4	445.15	3746.5	914	1533.5	251.9	910	260.2	4072.98	0.207
01/09/2017	4319	1217	208.8	477.3	3473	887	1488.5	268.3	854.5	259.9	4049.89	0.242
01/10/2017	4269.5	1339	215.6	510.2	3548.5	973	1357.5	282.5	833	267	4117.69	0.369
01/11/2017	4164	1406	224.3	489.1	3501.5	857	1279.5	274	820.5	267.2	4033.84	0.399
01/12/2017	4125.5	1464	235	522.7	3942	847	1322.5	278	829.5	273.3	4221.82	0.34
01/01/2018	4000	1660	224.6	501.1	3922	872	1320.4	287.7	738	270.6	4137.66	0.312
01/02/2018	3733.5	1679	203.75	475.15	3926	839.6	1307.2	267.7	666.2	262.6	3981.61	0.361
01/03/2018	3955.5	1604.5	194.22	479.25	3611	871.6	1394	258.8	715.4	257.8	3894.17	0.448
01/04/2018	4077.5	1590	211.6	538	3946	840	1461.4	270.2	743.2	270	4127.68	0.496
01/05/2018	4146.5	1709	191.82	576.3	4240	824.6	1524	275.1	775.8	269.8	4222.2	0.447
01/06/2018	4192	1673	183.82	578.3	4201	988.2	1529.8	256.1	763.2	266	4202.25	0.58
01/07/2018	4355	1619	186	573.3	4196.5	991	1581.6	255.4	719.2	262.7	4253.31	0.699
01/08/2018	4392	1525	164.5	547.3	3659	1010	1560.4	241.3	741.6	257	4106.14	0.744
01/09/2018	4216	1314	164.5	589.3	3880	987.4	1536.8	250	704	262.2	4127.91	0.731
01/10/2018	4146.5	1200	147.82	567.3	3803.5	839	1511.2	236.4	726.6	251.5	3904.23	0.715
01/11/2018	4243.5	1112.5	168.94	520	3559	849.6	1621.6	218.2	760.4	244.9	3823.34	0.701
01/12/2018	4108.5	1105	152.9	495.95	3730	830	1491.2	216.7	736.2	231	3675.06	0.727
01/01/2019	3984.5	1264	138.58	520.1	4187.5	884.4	1477.2	240.9	830.8	259.4	3825.62	0.698
01/02/2019	4008	1227.5	134.28	534.4	4336	955	1497.4	265.7	840.2	280.6	3888.81	0.628
01/03/2019	4395	1117.5	139.8	558.5	4461	903.2	1596.8	247	814.4	275.3	3978.28	0.769
01/04/2019	4657.5	1163.5	142	559	4465	914.6	1573.4	239.6	828.8	278.2	4067.98	0.752
01/05/2019	4837	871.2	129.42	540.1	4536.5	860	1527	213.7	797.4	256.6	3923.87	0.731
01/06/2019	4894.5	953.2	129.32	548.6	4880.5	840.4	1576.6	219.8	782.8	269.5	4056.88	0.74
01/07/2019	4955.5	964	150.08	545.7	4698	862	1702.6	217	788.6	261.8	4134.03	0.76
01/08/2019	5196	963.4	155.24	500.2	4149.5	771.2	1711.6	185.45	814	219.6	3953.02	0.752
01/09/2019	4890	1150	162	515.8	4209	792.4	1744.6	207.6	825.8	248.4	4061.74	0.775
01/10/2019	4622.5	1238	157.4	489.3	4014	709	1768.6	212.7	870.2	263.7	3993.46	0.76
01/11/2019	4578	1338	153.36	480.2	4201.5	711.6	1754	226.2	853.4	280.9	4066.73	0.759
01/12/2019	4350.5	1424.5	146.76	471.6	4503	683.2	1779	240.3	943.4	303	4196.47	0.771
01/01/2020	4533.5	1393	149.3	456.7	4084	670	1784	218.3	1013.5	305.5	4057.47	0.731
01/02/2020	4170	1100.5	134.36	396.15	3608	620	1562.2	178.65	944.2	260	3673.61	0.644
01/03/2020	4075.5	571	113	344.2	3718.5	340.6	1514.6	112.9	902.8	193.85	3107.42	0.274
01/04/2020	4102	603.2	112.14	313.1	3684.5	330	1661	110.55	902.2	204.6	3262.51	0.083
01/05/2020	4332	680	133.02	305.75	4313	271.6	1671.8	110.35	917.6	198.5	3363.67	0.08

Answer rating: 100% (QA)

Part a i To calculate the average monthly return variance and standard deviation we first need to calculate the monthly returns of each stock The monthly return is calculated by taking the logarithmic ... View the full answer