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 via the images below. You are required to answer the following questions independently

Question 1. (25 marks in total)

(a) Calculate each stocks 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)

(b) 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)

(c) 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)

Question 2. (15 marks in total)

(a) 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)

(b) 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)

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