Answer Using Spreadsheet Below

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 "Data _coursework_2021.csv". You are required to answer the following questions independently. Question 1. (25 marks in total) (a) 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) (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 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) (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) 1/07/2015 1/08/2015 1/09/2015 1/10/2015 1/11/2015 1/12/2015 1/01/2016 1/02/2016 1/03/2016 1/04/2016 1/05/2016 1/06/2016 1/07/2016 1/08/2016 109/2016 1/10/2016 01/11/2016 1/12/2016 1/01/2017 1/02/2017 1/03/2017 1/04/2017 1/05/2017 1/06/2017 1/07/2017 1/08/2017 7/09/2017 1/10/2017 01/11/2017 1/12/2017 1/01/2018 1/02/2018 1/03/2018 1/04/2018 1/05/2018 7/06/2018 1/07/2018 1/08/2018 1/09/2018 1/10/2018 1/11/2018 1/12/2018 1/01/2019 1/02/2019 1/03/2019 1/04/2019 1/05/2019 1/06/2019 1/07/2019 1/08/2019 1/09/2019 1/10/2019 01/11/2019 1/12/2019 1/01/2020 /02/2020 /03/2020 /04/2020 Unilever PLC(ULVR.L easyJet plo (EZJ.L Vodafone Grou BP p.1.c. (BP.L Rio Tinto plc (RIO.L Rolls-Royce Holdings plc (RF GlaxoSmithKline plo (GSK The Royal Bank of Scotland Group plc (RE United Utilities Group Legal & General Gr FTSE All UK 3 MONTHS TREASURY BILLSYIELD (EP) 2907 1644 241.850006 395.450012 2486.5 794 1396 342.200012 892 260.700012 3652.8 0.469 2623 1683 226.699997 360.5 2388 743.5 1340.5 336.799988 855 252.699997 3434.7 0.46 2686 1777 208.449997 334 2210 677 1266 315 925 238.100006 3335.9 0.489 2893 1750 214.25 386.399994 2358.5 687.5 1405 317.600006 988.5 261.600006 3484.6 0.478 2834 1653 224.25 385.25 2208.5 606.5 1355.5 302.399994 966.5 271.5 3492.1 0.483 2926.5 1740 221 354 1979.5 575 1373 302 935.5 267.799988 3444.3 0.459 3085 1548 223.899994 376.100006 1714 555 1439 252.699997 956.5 243.5 3335.9 0.468 3092 1506 219.699997 351.450012 1904 679 1399.5 223.899994 925.5 227 3345.8 0.463 3152.5 1519 221.199997 350.149994 1955.5 682 1412 222.699997 923 235.199997 3395.2 0.468 3054.5 1473 219.199997 373.75 2300.5 669.5 1459 230 939.5 223.100006 3421.7 0.453 3146 1533 230.850006 357.25 1942.5 619 1445 246.5 970 239.399994 3429.8 0.478 3579 1086 227.649994 438.149994 2294 711.5 1604.5 171600006 1035 190.699997 3515.4 0.442 3533.5 1041 229.550003 426.850006 2461.5 791 1688 192.399994 1017 205.699997 3653.8 0.293 3529 1105 229.850006 427.899994 2301 770 1639 201.5 971.5 210.300003 3697.2 0.23 3654.5 1007 221.75 450 2574.5 720 1643 178.800003 1003 218.699997 3755.3 0.217 3418 937 224.800003 483.700012 2839.5 726.5 1618.5 189.100006 940 209.399994 3768.1 0.233 3196 990 193.899994 459.450012 2990 676.5 1495.5 194.300003 883 235.800003 3692.4 0.203 3292.5 1005 199.850006 509.600006 3158.5 668 1562 224.600006 901 247.600006 3873.2 0.218 3227.5 950 194.350006 472.850006 3491 667.5 1528 221.800003 917 234.899994 3858.3 0.226 3819 951 201.899994 453.549988 3297 787 1648 237.800003 978.5 248.300003 3953.4 0.213 3939.5 1026 208.100006 457.549988 3209.5 754 1659.5 242.100006 993.5 247.300003 3990 0.217 3972 1168 199.050003 442.5 3062 812 1550 265.399994 973.5 246.100006 3962.5 0.165 4331.5 1414 231.5 466.700012 3102.5 867.5 1703.5 260 1029 251.800003 4116,1 0.182 4155 1359 217.75 442.799988 3242 891 1635.5 247.199997 867.5 258.299988 4002.2 0.207 4321.5 1236 222 445.799988 3520 888 1514.5 248.600006 897.5 268.399994 4046.2 0.153 4519 1196 221.399994 445.149994 3746.5 914 1533.5 251.899994 910 260.200012 4073 0.207 4319 1217 208.800003 477.299988 3473 887 1488,5 268,299988 854.5 259.899994 4049.9 0.242 4269.5 1339 215.600006 510.200012 3548.5 973 1357.5 282.5 833 267 4117.7 0.369 4164 1406 224.300003 489.100006 3501.5 857 1279,5 274 820.5 267.200012 4033.8 0.399 4125.5 1464 235 522.700012 3942 847 1322.5 278 829.5 273.299988 4221.8 0.34 4000 1660 224.600006 501100006 3922 872 1320.400024 287.700012 738 270.600006 4137.7 0.312 3733.5 1679 203.75 475.149994 3926 839.599976 1307.199951 267.700012 666.200012 262.600006 3981.6 0.361 3955.5 1604.5 194.220001 479.25 3611 871.599976 1394 258.799988 715.400024 257.799988 3894.2 0.448 4077.5 1590 211.600006 538 3946 840 1461.400024 270.200012 743.200012 270 4127.7 0.496 4146.5 1709 191.820007 576.299988 4240 824.599976 1524 275.100006 775.799988 269.799988 4222.2 0.447 4192 1673 183.820007 578.299988 4201 988 200012 1529.800049 256.100006 763.200012 266 4202.3 0.58 4355 1619 186 573.299988 4196.5 991 1581.599976 255.399994 719.200012 262.700012 4253.3 0.699 4392 1525 164.5 547.299988 3659 1010 1560. 400024 241.300003 741.599976 257 4106.1 0.744 4216 1314 164.5 589.299988 3880 987.400024 1536.800049 250 704 262.200012 4127.9 0.731 4146.5 1200 147.820007 567.299988 3803.5 839 1511.199951 236.399994 726.599976 251.5 3904.2 0.715 4243.5 1112.5 168,940002 520 3559 849.599976 1621.599976 218.199997 760.400024 244.899994 3823.3 0.701 4108.5 1105 152.899994 495.950012 3730 830 1491.199951 216.699997 736.200012 231 3675.1 0.727 3984.5 1264 138.580002 520.099976 4187.5 884.400024 1477.199951 240.899994 830.799988 259.399994 3825.6 0.698 4008 1227.5 134.279999 534.400024 4336 955 1497.400024 265.700012 840.200012 280.600006 3888.8 0.628 4395 1117.5 139.800003 558.5 4461 903.200012 1596.800049 247 814.400024 275.299988 3978.3 0.769 4657.5 1163.5 142 559 4465 914.599976 1573.400024 239.600006 828.799988 278.200012 4068 0.752 4837 871.200012 129.419998 540.099976 4536.5 860 1527 213.699997 797.400024 256.600006 3923.9 0.731 4894.5 953.200012 129.320007 548.599976 4880.5 840.400024 1576.599976 219.800003 782.799988 269.5 4056.9 0.74 4955,5 964 150.080002 545.700012 4698 862 1702.599976 217 788.599976 261.799988 4134 0.76 5196 963.400024 155.240005 500.200012 4149.5 771.200012 1711.599976 185.449997 814 219.600006 3953 0.752 4890 1150 162 515.799988 4209 792.400024 1744.599976 207.600006 825.799988 248.399994 4061.7 0.775 4622.5 1238 157.399994 489.299988 4014 709 1768.599976 212.699997 870.200012 263.700012 3993.5 0.76 4578 1338 153.360001 480.200012 42015 711.599976 1754 226.199997 853.400024 280.899994 4066.7 0.759 4350.5 1424.5 146.759995 471.600006 4503 683.200012 1779 240.300003 943.400024 303 4196.5 0.771 4533.5 1393 149.300003 456.700012 4084 670 1784 218.300003 1013.5 305.5 4057.5 0.731 4170 1100.5 134.360001 396.149994 3608 620 1562. 199951 178.649994 944.200012 260 3673.6 0.644 4075.5 571 113 344.200012 3718.5 340.600006 1514.599976 112.900002 902.799988 193.850006 3107.4 0.274 4102 603.200012 112. 139999 313.100006 3684.5 330 1661 110.550003 902.200012 204.600006 3262.5 0.083 1000 1000000 100000 APIA ATA 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 "Data _coursework_2021.csv". You are required to answer the following questions independently. Question 1. (25 marks in total) (a) 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) (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 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) (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) 1/07/2015 1/08/2015 1/09/2015 1/10/2015 1/11/2015 1/12/2015 1/01/2016 1/02/2016 1/03/2016 1/04/2016 1/05/2016 1/06/2016 1/07/2016 1/08/2016 109/2016 1/10/2016 01/11/2016 1/12/2016 1/01/2017 1/02/2017 1/03/2017 1/04/2017 1/05/2017 1/06/2017 1/07/2017 1/08/2017 7/09/2017 1/10/2017 01/11/2017 1/12/2017 1/01/2018 1/02/2018 1/03/2018 1/04/2018 1/05/2018 7/06/2018 1/07/2018 1/08/2018 1/09/2018 1/10/2018 1/11/2018 1/12/2018 1/01/2019 1/02/2019 1/03/2019 1/04/2019 1/05/2019 1/06/2019 1/07/2019 1/08/2019 1/09/2019 1/10/2019 01/11/2019 1/12/2019 1/01/2020 /02/2020 /03/2020 /04/2020 Unilever PLC(ULVR.L easyJet plo (EZJ.L Vodafone Grou BP p.1.c. (BP.L Rio Tinto plc (RIO.L Rolls-Royce Holdings plc (RF GlaxoSmithKline plo (GSK The Royal Bank of Scotland Group plc (RE United Utilities Group Legal & General Gr FTSE All UK 3 MONTHS TREASURY BILLSYIELD (EP) 2907 1644 241.850006 395.450012 2486.5 794 1396 342.200012 892 260.700012 3652.8 0.469 2623 1683 226.699997 360.5 2388 743.5 1340.5 336.799988 855 252.699997 3434.7 0.46 2686 1777 208.449997 334 2210 677 1266 315 925 238.100006 3335.9 0.489 2893 1750 214.25 386.399994 2358.5 687.5 1405 317.600006 988.5 261.600006 3484.6 0.478 2834 1653 224.25 385.25 2208.5 606.5 1355.5 302.399994 966.5 271.5 3492.1 0.483 2926.5 1740 221 354 1979.5 575 1373 302 935.5 267.799988 3444.3 0.459 3085 1548 223.899994 376.100006 1714 555 1439 252.699997 956.5 243.5 3335.9 0.468 3092 1506 219.699997 351.450012 1904 679 1399.5 223.899994 925.5 227 3345.8 0.463 3152.5 1519 221.199997 350.149994 1955.5 682 1412 222.699997 923 235.199997 3395.2 0.468 3054.5 1473 219.199997 373.75 2300.5 669.5 1459 230 939.5 223.100006 3421.7 0.453 3146 1533 230.850006 357.25 1942.5 619 1445 246.5 970 239.399994 3429.8 0.478 3579 1086 227.649994 438.149994 2294 711.5 1604.5 171600006 1035 190.699997 3515.4 0.442 3533.5 1041 229.550003 426.850006 2461.5 791 1688 192.399994 1017 205.699997 3653.8 0.293 3529 1105 229.850006 427.899994 2301 770 1639 201.5 971.5 210.300003 3697.2 0.23 3654.5 1007 221.75 450 2574.5 720 1643 178.800003 1003 218.699997 3755.3 0.217 3418 937 224.800003 483.700012 2839.5 726.5 1618.5 189.100006 940 209.399994 3768.1 0.233 3196 990 193.899994 459.450012 2990 676.5 1495.5 194.300003 883 235.800003 3692.4 0.203 3292.5 1005 199.850006 509.600006 3158.5 668 1562 224.600006 901 247.600006 3873.2 0.218 3227.5 950 194.350006 472.850006 3491 667.5 1528 221.800003 917 234.899994 3858.3 0.226 3819 951 201.899994 453.549988 3297 787 1648 237.800003 978.5 248.300003 3953.4 0.213 3939.5 1026 208.100006 457.549988 3209.5 754 1659.5 242.100006 993.5 247.300003 3990 0.217 3972 1168 199.050003 442.5 3062 812 1550 265.399994 973.5 246.100006 3962.5 0.165 4331.5 1414 231.5 466.700012 3102.5 867.5 1703.5 260 1029 251.800003 4116,1 0.182 4155 1359 217.75 442.799988 3242 891 1635.5 247.199997 867.5 258.299988 4002.2 0.207 4321.5 1236 222 445.799988 3520 888 1514.5 248.600006 897.5 268.399994 4046.2 0.153 4519 1196 221.399994 445.149994 3746.5 914 1533.5 251.899994 910 260.200012 4073 0.207 4319 1217 208.800003 477.299988 3473 887 1488,5 268,299988 854.5 259.899994 4049.9 0.242 4269.5 1339 215.600006 510.200012 3548.5 973 1357.5 282.5 833 267 4117.7 0.369 4164 1406 224.300003 489.100006 3501.5 857 1279,5 274 820.5 267.200012 4033.8 0.399 4125.5 1464 235 522.700012 3942 847 1322.5 278 829.5 273.299988 4221.8 0.34 4000 1660 224.600006 501100006 3922 872 1320.400024 287.700012 738 270.600006 4137.7 0.312 3733.5 1679 203.75 475.149994 3926 839.599976 1307.199951 267.700012 666.200012 262.600006 3981.6 0.361 3955.5 1604.5 194.220001 479.25 3611 871.599976 1394 258.799988 715.400024 257.799988 3894.2 0.448 4077.5 1590 211.600006 538 3946 840 1461.400024 270.200012 743.200012 270 4127.7 0.496 4146.5 1709 191.820007 576.299988 4240 824.599976 1524 275.100006 775.799988 269.799988 4222.2 0.447 4192 1673 183.820007 578.299988 4201 988 200012 1529.800049 256.100006 763.200012 266 4202.3 0.58 4355 1619 186 573.299988 4196.5 991 1581.599976 255.399994 719.200012 262.700012 4253.3 0.699 4392 1525 164.5 547.299988 3659 1010 1560. 400024 241.300003 741.599976 257 4106.1 0.744 4216 1314 164.5 589.299988 3880 987.400024 1536.800049 250 704 262.200012 4127.9 0.731 4146.5 1200 147.820007 567.299988 3803.5 839 1511.199951 236.399994 726.599976 251.5 3904.2 0.715 4243.5 1112.5 168,940002 520 3559 849.599976 1621.599976 218.199997 760.400024 244.899994 3823.3 0.701 4108.5 1105 152.899994 495.950012 3730 830 1491.199951 216.699997 736.200012 231 3675.1 0.727 3984.5 1264 138.580002 520.099976 4187.5 884.400024 1477.199951 240.899994 830.799988 259.399994 3825.6 0.698 4008 1227.5 134.279999 534.400024 4336 955 1497.400024 265.700012 840.200012 280.600006 3888.8 0.628 4395 1117.5 139.800003 558.5 4461 903.200012 1596.800049 247 814.400024 275.299988 3978.3 0.769 4657.5 1163.5 142 559 4465 914.599976 1573.400024 239.600006 828.799988 278.200012 4068 0.752 4837 871.200012 129.419998 540.099976 4536.5 860 1527 213.699997 797.400024 256.600006 3923.9 0.731 4894.5 953.200012 129.320007 548.599976 4880.5 840.400024 1576.599976 219.800003 782.799988 269.5 4056.9 0.74 4955,5 964 150.080002 545.700012 4698 862 1702.599976 217 788.599976 261.799988 4134 0.76 5196 963.400024 155.240005 500.200012 4149.5 771.200012 1711.599976 185.449997 814 219.600006 3953 0.752 4890 1150 162 515.799988 4209 792.400024 1744.599976 207.600006 825.799988 248.399994 4061.7 0.775 4622.5 1238 157.399994 489.299988 4014 709 1768.599976 212.699997 870.200012 263.700012 3993.5 0.76 4578 1338 153.360001 480.200012 42015 711.599976 1754 226.199997 853.400024 280.899994 4066.7 0.759 4350.5 1424.5 146.759995 471.600006 4503 683.200012 1779 240.300003 943.400024 303 4196.5 0.771 4533.5 1393 149.300003 456.700012 4084 670 1784 218.300003 1013.5 305.5 4057.5 0.731 4170 1100.5 134.360001 396.149994 3608 620 1562. 199951 178.649994 944.200012 260 3673.6 0.644 4075.5 571 113 344.200012 3718.5 340.600006 1514.599976 112.900002 902.799988 193.850006 3107.4 0.274 4102 603.200012 112. 139999 313.100006 3684.5 330 1661 110.550003 902.200012 204.600006 3262.5 0.083 1000 1000000 100000 APIA ATA