You are given price information on 10 stocks, the FTSE All Share Index, and risk-free rate (UK T-bill rate) from 01/2013to 07/2017. Data can be accessed in the Excel spread sheet Data _coursework_supplemetary.csv. You are required to answerthe following questionsindependently.Question 1.(15 marks in total)(a)Calculate each stocks average monthly return, its return variance and standard deviationand betaover the period 01/2013to 07/2017, 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).(9marks)(b) Explain why some stocks have betas greater than 1 while others are below 1 (you may do some research on firm backgrounds and their businesses). (6 marks)Question 2.(25marks in total)(a)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 Cs 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)(b)Which of the Portfolios A, B, and C, has the highest return standard deviation? Does this portfolio havethe highest average monthly return? Can you explain why? (5marks)(c)Is Portfolio Bs return standard deviation higher than any of its 6 component stocks return standard deviations? Why? (6marks)(d) Should investors invest their money in the componentstocksor in the portfolio? Please provide justifications for your answer.(6 marks)
Date 891 890 918 953 Unilever PLeasyJet plc Vodafone (BP plc (ERio Tinto p Rolls-Royo GlaxoSmit The Royal United Utili Legal & GeFTSE ALL UK 3 MONTHS TREASURY BILLS YIELD (EP) 2587 931 179.438 486.75 3580 948 1445.5 3433 732 152.4 3287.38 0.309 2828 997 172.608 445.7 3541 1028 1450 323.9 737 160.1 3349.39 0.289 2784 1080 194.556 449.6 3085 1130 1538.5 2755 708.5 172.7 3380.84 0.3443 2788 1117 204.565 441.8 2919 1130 1680.5 3063 740.5 189.5 3390.18 0.3752 2775 1266 200.134 483.3 2857 1205 1713.5 336.6 758 179 3473.82 0.3273 2682 1296 195.859 444.95 2683 1134 1848 273.5 884 171.4 3289.71 0.309 2889 1412 205.92 451 45 2955 1175 1884 317.6 721.5 192.9 3509.94 0.3289 2459 1233 215.044 4482 2913 1112 1845.5 333.7 678,5 186.8 3410.43 0.2997 2440 1278 225.209 433.1 3023 1112 1557.5 350.9 196.2 3443.85 0.2686 2525 1309 234.072 443.9 3158 1150 1643.5 367.6 7045 216.3 3585.32 0.3504 2489 1424 236.47 482 3281.5 1235 1618.5 3272 658.5 213.9 3548.45 0.3184 2482 1536 247.105 473.15 3409.5 1275 1811.5 338.1 671.5 222.7 3609.63 0.2882 2339 1644 238.209 478 3243.5 1187 1584 340 716 215.3 3498.51 0.257 2441 1721 249 504.4 3432.5 999 1671.5 327.9 7795 240.4 3888.88 0.3801 2581 1715 220.3 480 33375 1074 1591.5 311 788 2047 3555,59 0.3768 2844 1637 223.95 4992 32245 1050 1632 298.8 798 211.8 3819.83 0.421 2880 1530 209.5 507 3057 1040 1601 3486 888 230.2 3855.01 0.3438 2851 1365 195 514.9 3108.5 1089 1564 328.4 882 225.4 3800.19 0.2762 2570 1295 198.1 484 3392 1040 1438.5 3553 234.4 3585.62 0.4171 2859 1335 208.75 481.85 3213.5 1021 14745 362.8 877 241.73639.54 0.4178 2585 1423 2044 453.45 30315 9845 1413 3682 808 228.9 3533.93 0.3974 2514 1500 207.3 446 29715 843 1417.5 388 854 5 231 3503 48 0.4803 2706 1653 233.95 4202 29925 8425 1485 3953 905.5 248.7 3593.32 0.3901 2828 1871 222.65 411 3000 870 1376 3944 248.8 3532.74 0.4129 2930 1882 234.5 439 2925 891.5 1487 3624 1027 267.9 3621.81 0.3884 2888 1732 2244 448.06 3192 949 1541.5 3672 948 279.5 3744 26 0.3421 2815 1882 220.45 436.7 2772 1546 340 933 2786 3663.58 0.3999 2886 1813 230.55 470.1 2885 1048 15145 338.5 971 280 3760.06 0.4417 2887 1809 255.35 450.75 2884 998 1453.5 345.3 998 265.8 3797.12 0.4385 2730 1546 229.85 4324 2733 870 13225 3515 892 248.9 3570.58 0.4842 2907 1844 241.85 416.95 2486.5 794 1396 3422 892 280.7 3652.79 0.4956 2823 1883 228.7 360.5 2388 743.5 1340.5 3388 856 252.7 3434.66 0.4848 2686 1777 208.45 333.45 2210 677 1266 315 238.1 3335.92 0.4544 2893 1750 21425 386.4 2358.5 8875 1405 317.6 988.5 281.6 3484.6 0.4685 2834 1653 22425 385.15 2208.5 808.5 1355.5 3024 986.5 271.5 349213 0.4801 2928.5 1740 221 354 1979.5 575 1373 302 935.5 267.8 3444 26 0.4836 3085 1548 223.9 342.25 1714 555 1439 2527 956.5 243.5 3335.9 0.4511 3092 1506 219.7 330.1 1904 679 1399.5 223.9 925.5 227 3345.84 0.4811 3152.5 1519 2212 353.35 1966.5 682 1412 2227 923 235.23395.19 0.487 3054.5 1473 219.2 339.55 2234 889.5 1459 230 9395 223.1 3421.7 0.4598 3146 1533 230.85 357 25 1942.5 819 1445 248.5 970 239.4 3429.77 0.4442 3579 1088 227.65 424.8 2294 711,5 16045 171.6 1035 190.7 3515.45 0.4254 3533.5 1041 229.55 428.85 2481.5 791 1688 1924 1017 205.7 3653.83 0.3998 3529 1105 229.85 433.7 2301 770 1839 2015 971.5 210.3 3897.19 0.3427 3654.5 1007 221.75 450 25745 720 1843 178.8 218.7 3755.34 0.2212 3418 937 224.8 4895 2839.5 728.5 1618.5 1891 940 209.4 3768.14 0.1837 3198 990 193.9 459.45 2990 878.5 1495.5 1943 883 225.8 3692.4 0.1599 3292.5 1005 199.85 5096 3158.5 688 1582 2240 901 247.8 3873.22 0.1209 3227.5 950 194 35 513.3 3491 867.5 1528 221.8 917 234.93858.26 0.0106 3819 951 201.9 4515 3297 787 1848 237,8 978,5 248.3 3953.42 0.1642 3939.5 208.1 454.75 3209.5 754 1659.5 2421 993.5 247.3 3990 0.0344 3972 1168 199.06 3082 812 1550 265.4 973,5 246.1 3982.49 0.0521 4331.5 1414 231.5 486.7 3102.5 887.5 1703.5 280 1029 4118.08 0.0893 4155 1359 217.75 442.8 3242 891 1835.5 2472 8675 258.3 4002.18 0.0575 4321.5 1236 222 445.8 3504 888 15145 248.6 8975 288.4 4048.2 0.1262 925 1003 1028 471.1 251.8
