The monthly excess return of 6 stocks are given in the excel file (See the attached excel file). Using the information given to you in the excel, create an optimal portfolio that comprised of a passive portfolio of a market index and an active portfolio that is made up of these 6 stocks. If you make any assumption, please clearly state your assumption and why you made such assumption and its justification. Verify whether or not the assumptions underlying Index Models are met. Also, calculate the beta and the Sharpe ratio of the portfolio.

2 Date 3 2011-01 4 2011-02 5 2011-03 6 2011-04 7 2011-05 8 2011-06 9 2011-07 10 2011-08 11 2011-09 12 2011-10 13 2011-11 14 2011-12 15 2012-01 16 2012-02 17 2012-03 18 2012-04 19 2012-05 20 2012-06 21 2012-07 22 2012-08 23 2012-09 24 2012-10 25 2012-11 26 2012-12 27 2013-01 28 2013-02 29 2013-03 30 2013-04 31 2013-05 32 2013-06 33 2013-07 34 2013-08 35 2013-09 36 2013-10 37 2013-11 38 2013-12 39 2014-01 40 2014-02 41 2014-03 42 2014-04 43 2014-05 44 2014-06 45 2014-07 46 2014-08 47 2014-09 48 2014-10 49 2014-11 50 2014-12 51 2015-01 52 2015-02 53 2015-03 54 2015-04 55 2015-05 56 2015-06 57 2015-07 58 2015-08 59 2015-09 60 2015-10 61 2015-11 62 2015-12 63 Alpha 64 Market risk premium F G H Monthly Return Market Index WMTTGTVZ T GM Ford 0.017757 0.038281 -0.08954 0.00775 -0.05007 -0.01144 -0.05143 0.037065 -0.074045 -0.03812 0.0354 0.03015 -0.08222 -0.05753 0.002766 0.007769 -0.04894 0.04328 0.07798 -0.07516 -0.0099 0.028388 0.055992 -0.0185 -0.00737 0.03041 0.03386 0.03726 -0.015135 0.010804 0.01365 -0.0227 0.01394 -0.00893 -0.03575 -0.018595 -0.037868 -0.0531 0.00792 -0.00495 -0.04578 -0.07594 -0.022869 -0.008492 0.09723 -0.03941 -0.05516 -0.08867 -0.11498 -0.057672 0.015831 0.00912 0.0246 -0.02686 -0.13206 -0.08947 -0.084873 -0.024253 -0.0509 0.01756 0.00141 -0.16022 -0.1304 0.113882 0.092771 0.11634 0.01838 0.04268 0.28087 0.20776 -0.006326 0.038334 -0.03206 0.02018 -0.01136 -0.1765 -0.09257 0.003702 0.020798 -0.02808 0.06335 0.04348 -0.04791 0.01509 0.053877 0.026574 -0.00821 -0.04905 -0.0131 0.1848 0.15872 0.040586 -0.037758 0.12103 0.01135 0.03952 0.08266 -0.00382 0.023394 0.042012 0.02727 0.00255 0.02032 -0.01482 0.00707 -0.007501 -0.038118 -0.00636 0.06862 0.06718 -0.10401 -0.09248 -0.066231 0.123345 0.00396 0.0305 0.03759 -0.03548 -0.06453 0.037656 0.058753 0.00434 0.06674 0.04311 -0.11221 -0.09236 0.009566 0.066856 0.04158 0.0263 0.07502 -0.00121 -0.0372 0.025365 -0.020146 0.06175 -0.04964 -0.03466 0.08231 0.01533 0.025839 0.015829 -0.01037 0.06055 0.02823 0.06487 0.05497 -0.015201 0.015431 0.00331 -0.01021 -0.07192 0.11978 0.13582 0.004893 -0.041289 -0.00538 -0.01295 -0.0146 0.0136 0.02469 0.012131 -0.047505 -0.06313 -0.01967 -0.01271 0.11359 0.1306 0.053593 0.024709 0.02046 0.01926 0.04489 -0.02617 0.00722 0.007531 0.011066 0.04737 0.06616 0.03139 -0.03426 -0.02706 0.034474 0.06306 0.0864 0.05551 0.02092 0.02388 0.04202 0.014445 0.038121 0.03033 0.10682 0.03275 0.10806 0.04209 0.018866 -0.031209 -0.01012 -0.10092 -0.06614 0.0987 0.15079 -0.015339 -0.004977 -0.00951 0.03807 0.01142 -0.01741 -0.01369 0.052481 0.046115 0.03451 -0.00705 0.00884 0.07665 0.09741 -0.026114 -0.058008 -0.1058 -0.04284 -0.04123 -0.0503 -0.04128 0.037277 0.013228 0.01038 -0.01508 -0.0005 0.05526 0.0418 0.038751 0.036623 0.01156 0.09242 0.08258 0.02615 0.01905 0.024447 0.055005 -0.00714 -0.01812 -0.02785 0.04767 -0.00225 0.025926 -0.023037 -0.01052 -0.00987 -0.00162 0.05505 -0.0968 -0.030157 -0.051159 -0.10499 -0.01221 -0.03945 -0.1174 -0.02256 0.045673 -0.000232 0.11126 -0.00966 -0.04222 0.00283 0.02824 0.003969 0.029085 -0.03296 -0.00071 0.09784 -0.04138 0.01315 0.00146 0.042715 0.02029 -0.00672 0.03088 0.00154 0.04307 0.019918 -0.031162 -0.07415 0.06882 -0.00674 0.0026 0.01766 0.027741 -0.022344 0.02077 -0.02082 -0.0033 0.05822 0.04846 -0.020724 -0.020048 0.0281 0.04108 0.01931 -0.06852 -0.00571 0.039871 0.032318 0.01648 -0.0122 -0.018 0.02868 0.02261 -0.025229 0.012748 0.04335 0.00331 0.00791 -0.07366 -0.15059 0.020976 -0.002815 -0.01392 0.016 0.0015 -0.01711 -0.03908 0.02075 0.147365 0.20504 0.00637 0.0151 0.06425 0.116 -0.003916 -0.01378 0.02551 -0.07561 -0.05089 0.05295 -0.01492 -0.027352 -0.01068 -0.0305 -0.01132 -0.00615 -0.0658 -0.04149 0.055748 -0.012556 0.05061 0.08162 0.04962 0.14358 0.11061 -0.010639 -0.014378 0.068 -0.01678 -0.05547 0.01293 -0.01244 0.008522 -0.051264 -0.03968 0.04833 0.07514 -0.06527 -0.01197 0.010223 -0.042253 0.01271 -0.01993 -0.00299 0.02586 -0.03997 -0.019258 -0.044971 0.02912 -0.05724 0.02837 -0.06339 -0.01055 0.011795 0.014503 0.0024 0.01536 -0.00903 -0.05491 -0.0023 -0.060293 -0.094215 -0.04404 -0.01697 -0.04463 -0.06599 -0.06503 -0.033735 0.001699 0.01223 -0.05434 -0.01868 0.03193 -0.02163 0.07386 -0.117312 -0.01892 0.09034 0.04287 0.16279 0.10233 0.001742 0.027252 -0.05408 -0.0312 0.00408 0.03625 -0.03311 -0.023961 0.048436 -0.00018 0.01524 0.02028 -0.05225 -0.01845 0.0150 -0.0100 -0.0050 0.0075 0.0025 0.0120 6% 65 2 Date 3 2011-01 4 2011-02 5 2011-03 6 2011-04 7 2011-05 8 2011-06 9 2011-07 10 2011-08 11 2011-09 12 2011-10 13 2011-11 14 2011-12 15 2012-01 16 2012-02 17 2012-03 18 2012-04 19 2012-05 20 2012-06 21 2012-07 22 2012-08 23 2012-09 24 2012-10 25 2012-11 26 2012-12 27 2013-01 28 2013-02 29 2013-03 30 2013-04 31 2013-05 32 2013-06 33 2013-07 34 2013-08 35 2013-09 36 2013-10 37 2013-11 38 2013-12 39 2014-01 40 2014-02 41 2014-03 42 2014-04 43 2014-05 44 2014-06 45 2014-07 46 2014-08 47 2014-09 48 2014-10 49 2014-11 50 2014-12 51 2015-01 52 2015-02 53 2015-03 54 2015-04 55 2015-05 56 2015-06 57 2015-07 58 2015-08 59 2015-09 60 2015-10 61 2015-11 62 2015-12 63 Alpha 64 Market risk premium F G H Monthly Return Market Index WMTTGTVZ T GM Ford 0.017757 0.038281 -0.08954 0.00775 -0.05007 -0.01144 -0.05143 0.037065 -0.074045 -0.03812 0.0354 0.03015 -0.08222 -0.05753 0.002766 0.007769 -0.04894 0.04328 0.07798 -0.07516 -0.0099 0.028388 0.055992 -0.0185 -0.00737 0.03041 0.03386 0.03726 -0.015135 0.010804 0.01365 -0.0227 0.01394 -0.00893 -0.03575 -0.018595 -0.037868 -0.0531 0.00792 -0.00495 -0.04578 -0.07594 -0.022869 -0.008492 0.09723 -0.03941 -0.05516 -0.08867 -0.11498 -0.057672 0.015831 0.00912 0.0246 -0.02686 -0.13206 -0.08947 -0.084873 -0.024253 -0.0509 0.01756 0.00141 -0.16022 -0.1304 0.113882 0.092771 0.11634 0.01838 0.04268 0.28087 0.20776 -0.006326 0.038334 -0.03206 0.02018 -0.01136 -0.1765 -0.09257 0.003702 0.020798 -0.02808 0.06335 0.04348 -0.04791 0.01509 0.053877 0.026574 -0.00821 -0.04905 -0.0131 0.1848 0.15872 0.040586 -0.037758 0.12103 0.01135 0.03952 0.08266 -0.00382 0.023394 0.042012 0.02727 0.00255 0.02032 -0.01482 0.00707 -0.007501 -0.038118 -0.00636 0.06862 0.06718 -0.10401 -0.09248 -0.066231 0.123345 0.00396 0.0305 0.03759 -0.03548 -0.06453 0.037656 0.058753 0.00434 0.06674 0.04311 -0.11221 -0.09236 0.009566 0.066856 0.04158 0.0263 0.07502 -0.00121 -0.0372 0.025365 -0.020146 0.06175 -0.04964 -0.03466 0.08231 0.01533 0.025839 0.015829 -0.01037 0.06055 0.02823 0.06487 0.05497 -0.015201 0.015431 0.00331 -0.01021 -0.07192 0.11978 0.13582 0.004893 -0.041289 -0.00538 -0.01295 -0.0146 0.0136 0.02469 0.012131 -0.047505 -0.06313 -0.01967 -0.01271 0.11359 0.1306 0.053593 0.024709 0.02046 0.01926 0.04489 -0.02617 0.00722 0.007531 0.011066 0.04737 0.06616 0.03139 -0.03426 -0.02706 0.034474 0.06306 0.0864 0.05551 0.02092 0.02388 0.04202 0.014445 0.038121 0.03033 0.10682 0.03275 0.10806 0.04209 0.018866 -0.031209 -0.01012 -0.10092 -0.06614 0.0987 0.15079 -0.015339 -0.004977 -0.00951 0.03807 0.01142 -0.01741 -0.01369 0.052481 0.046115 0.03451 -0.00705 0.00884 0.07665 0.09741 -0.026114 -0.058008 -0.1058 -0.04284 -0.04123 -0.0503 -0.04128 0.037277 0.013228 0.01038 -0.01508 -0.0005 0.05526 0.0418 0.038751 0.036623 0.01156 0.09242 0.08258 0.02615 0.01905 0.024447 0.055005 -0.00714 -0.01812 -0.02785 0.04767 -0.00225 0.025926 -0.023037 -0.01052 -0.00987 -0.00162 0.05505 -0.0968 -0.030157 -0.051159 -0.10499 -0.01221 -0.03945 -0.1174 -0.02256 0.045673 -0.000232 0.11126 -0.00966 -0.04222 0.00283 0.02824 0.003969 0.029085 -0.03296 -0.00071 0.09784 -0.04138 0.01315 0.00146 0.042715 0.02029 -0.00672 0.03088 0.00154 0.04307 0.019918 -0.031162 -0.07415 0.06882 -0.00674 0.0026 0.01766 0.027741 -0.022344 0.02077 -0.02082 -0.0033 0.05822 0.04846 -0.020724 -0.020048 0.0281 0.04108 0.01931 -0.06852 -0.00571 0.039871 0.032318 0.01648 -0.0122 -0.018 0.02868 0.02261 -0.025229 0.012748 0.04335 0.00331 0.00791 -0.07366 -0.15059 0.020976 -0.002815 -0.01392 0.016 0.0015 -0.01711 -0.03908 0.02075 0.147365 0.20504 0.00637 0.0151 0.06425 0.116 -0.003916 -0.01378 0.02551 -0.07561 -0.05089 0.05295 -0.01492 -0.027352 -0.01068 -0.0305 -0.01132 -0.00615 -0.0658 -0.04149 0.055748 -0.012556 0.05061 0.08162 0.04962 0.14358 0.11061 -0.010639 -0.014378 0.068 -0.01678 -0.05547 0.01293 -0.01244 0.008522 -0.051264 -0.03968 0.04833 0.07514 -0.06527 -0.01197 0.010223 -0.042253 0.01271 -0.01993 -0.00299 0.02586 -0.03997 -0.019258 -0.044971 0.02912 -0.05724 0.02837 -0.06339 -0.01055 0.011795 0.014503 0.0024 0.01536 -0.00903 -0.05491 -0.0023 -0.060293 -0.094215 -0.04404 -0.01697 -0.04463 -0.06599 -0.06503 -0.033735 0.001699 0.01223 -0.05434 -0.01868 0.03193 -0.02163 0.07386 -0.117312 -0.01892 0.09034 0.04287 0.16279 0.10233 0.001742 0.027252 -0.05408 -0.0312 0.00408 0.03625 -0.03311 -0.023961 0.048436 -0.00018 0.01524 0.02028 -0.05225 -0.01845 0.0150 -0.0100 -0.0050 0.0075 0.0025 0.0120 6% 65