Answered step by step
Verified Expert Solution
Question
1 Approved Answer
The Excel file EXCELPROJECT.xls ' available in Canvas contains daily holding period returns of 10 stocks over the period January 3, 2011 to December 31,
The Excel file EXCELPROJECT.xls ' available in Canvas contains daily holding period returns of 10 stocks over the period January 3, 2011 to December 31, 2018. The 10 stocks are: International Business Machs Co: ticker 'IBM Kroger Company: ticker 'KR' Tesla (Motors) INC: ticker 'TSLA' Delta AIR LINES INC: ticker 'DAL Travelers Companies Inc: ticker 'TRV Exxon Mobil Corp: ticker XOM' Toyota Motor Corp: ticker 'TM' Accenture PLC Ireland: ticker 'ACN' XEROX CORP: ticker XRX' BERKSHIRE HATHAWAY INC DEL: ticker 'BRK' On January 2, 2019 (Wednesday) you decide to invest in a portfolio consisting of these 10 stocks. You want to find the optimal portfolio to invest in, so you analyze the performance of these 10 stocks over time by using past historical data from January 3, 2011 to December 31, 2018. Find the composition of the optimal portfolio you want to invest in. Assume that riskless borrowing/lending is possible. Please use the one-month T-bill rate on January 2, 2019, that is 0.0001. Short selling is allowed. Please solve this part using Excel Solver (use the 'GRG Nonlinear' Solving method). After finding the composition of the optimal portfolio, please report below the proportion of your funds invested in Toyota Motor Corp. (Please do not report the %, but rather the full number with 3 decimals.) Retums_TSLA Returns_TRV Returns_XOM Returns ACN Returns_IBM 0.4906 0.1085 Returns_KR -1.5653 -1.4084 Returns_DAL 0.1587 Retums_XRX 0.2062 0.1975 1.9557 1.6493 0.4695 0.267 -2.6473 0.5263 0.3996 -0.4479 0.5938 -1.5387 0.5912 -1.0984 -0.0461 1.0949 -0.4911 -0.0375 0.1878 0.5999 3.9135 1.2913 0.7436 -5.2373 -0.3975 3.5914 -0.5393 0.6971 -3.0769 -1.9841 Retums_TM 1.0174 0.5414 0.4257 1.6708 1.1283 0.2759 0.737 1.2713 1.1251 -0.6586 0.0207 0.3728 0.1651 -0.8859 1.3719 -1.9489 Returns_BRK 0.3745 -0.2985 0.923 -0.6674 -0.7839 -0.1129 -0.3139 1.6877 0.0248 1.102 -0.3241 -0.3716 0 1.2955 1.3986 -1.0575 0.9751 0.0929 1.2801 0.1465 0.0732 0.6426 0.5454 -0.6085 0.7454 1.1758 0.1698 1.4731 1.1177 -0.5971 -0.6263 1.5R2 0.7994 1.1102 2.2965 -0.0401 0.2607 0.6575 -2.7448 1.7925 -0.4272 -6.2792 -5.8677 1.856 0.3717 -0.0466 0.3733 0.3719 -0.139 -8.2157 -2.1577 0.0732 -0.4024 -0.9074 -1.383 0.7 -0.2979 1.8725 0.2082 -1.1 0 -1.3962 0.9234 0.6135 0.4355 -1.8213 2.6502 0.6024 -1.2831 -3.3796 -0.0097 0.8079 1.5138 7.6316 2.4691 -2.2243 0.6635 2.4482 1.3974 -0.5029 0.7897 0.2187 -0.7619 -1.6195 -0.1955 -0.2348 -0.3468 3.0951 0.5494 4.1594 -0.4938 1.1273 1.0967 0.8425 0.4857 -0.196 -0.2438 1.2357 0.1878 0.7929 0.4333 3.3455 0.0702 -0.1926 2.656 1.1339 0.2478 0.0186 -1.1540 1.7524 0.963 -0.159 0.1408 0.2874 0.5 1.1406 -0.8431 -0.2304 0.1848 0.0534 -3.2446 2.988 2.6926 -3.8211 -0.4628 -1.0228 0.3738 0.2162 -0.0482 -1.7704 1.2181 0.0191 -1.6043 0.7758 0.2835 0.6869 3.6517 0.3743 -0.7884 0.1255 -1.2949 1.8168 0.4311 1.8722 1.5069 -2.5979 1.0079 1.3507 2.0531 -0.3765 0.5167 1.3551 -0.2305 1.4787 -0.097 1.7486 0.1145 1.2456 0.2762 -1.1142 2.1395 4.0035 -0.5959 0.036 0.1918 0.7805 -0.6076 -1.3525 1.0283 3.732 1.5859 1.1275 1.3158 2.684 0.7651 -1.448 0.8063 0.9776 0.176 0.9522 0.2795 -0.4006 1.2242 1.6068 -0.5951 0.3628 0.4186 0.4358 1.6849 0.7194 -1.6524 6.1552 1 DATE 2 3-Jan-11 3 3 4-Jan-11 4 5-Jan-11 5 6-Jan-11 5 7-Jan-11 7 10-Jan-11 8 11-Jan-11 12-Jan-11 90 13-Jan-11 1 14-Jan-11 12 18-Jan-11 19-Jan-11 20-Jan-11 15 21-Jan-11 24-Jan-11 25-Jan-11 26-Jan-11 19 27-Jan-11 20 28-Jan-11 21 31-Jan-11 22 1-Feb-11 2-Feb-11 24 3-Feb-11 25 4-Feb-11 26 7-Feb-11 27 8-Feb-11 28 9-Feb-11 39 10-Feb-11 11-Feb-11 31 14-Feb-11 32 15-Feb-11 16-Feb-11 17-Feb-11 35 18-Feb-11 35 22-Feb-11 47 23-Feb-11 38 24-Feb-11 39 25-Feb-11 10 28-Feb-11 41 1-Mar-11 42 2-Mar-11 43 3-Mar-11 4-Mar-11 15 7-Mar-11 45 8-Mar-11 117 9-Mar-11 10-Mar-11 49 11-Mar-11 50 14-Mar-11 $1 15-Mar-11 52 16-Mar-11 53 17-Mar-11 54 18-Mar-11 0.0827 0.4484 4.0409 0.2284 0.057 -0.1838 0.4604 0.5499 0.2765 -0.2757 0.2682 -0.4591 1.9572 -1.0798 0.6913 0.1807 1.1182 0.202 -0.4509 0.6675 0.5447 0.4593 -5.2266 0 -0.2239 1.6158 -0.53 0.7771 -0.7083 -1.0118 0.114 -0.5182 0.7874 1.1968 -0.9037 -0.3401 0.0215 -0.4259 0.5503 -1.1134 1.3848 1.1993 0.1508 -0.7312 1.7964 -0.5723 0.9066 -0.2543 -0.4567 2.5235 -0.3845 0.2328 0.3439 3.065 0.1724 0.5469 0.3626 0.8025 2.6979 0.6768 -0.6723 -1.4382 0.0191 0.8014 2.7313 0.8668 0.1853 0.5382 2.2848 0.8678 0.227 0.7392 2.71 0 0.3751 -0.0235 -0.0359 0.7637 -0.2455 -0.2675 1.0399 8.275 4.5693 -1.7797 -5.6514 -0.1829 3.2065 -0.5599 0.0433 0.4329 1.1796 -0.7957 -2.313 1.1124 -2.6931 -1.2876 -5.5087 2.6071 0.9982 1.907 -0.6402 -0.1817 -1.804 -0.4398 -3.9753 -1.7479 -9.6554 0.1413 -2.2105 -0.3727 0.6034 1.9078 -1.2633 -0.5734 -0.0776 -0.3879 -0.6058 0.4789 0.175 -0.7424 -2.1195 0.9559 0.8309 0.4551 0.5141 0.2653 -1.7532 -1.0929 0.3583 0.9392 -0.2465 - 1.1799 0.1188 2.0729 -1.0093 -1.1741 1.8114 4.7935 1.1859 0.2093 0.3342 1.4155 0.0898 1.0791 -5.605 0.5537 -1.1847 0.7328 0.2226 0.RSBS 0.3302 1.8313 1.6894 -0.493 -1.3415 0.6216 1.0368 0.2798 - 1.2093 2.8396 2.3258 -0.2698 1.976 -0.9239 -0.0195 -4.3355 1.0837 -3.4113 -0.6923 0.40R1 -1.411 0.7211 2.2202 0.8698 3.6755 -0.7533 1.2334 -1.687 2.5195 2.422 0.2202 -1.2017 -0.7156 -1.3841 1.8154 0.1695 0.2961 0.0844 -0.3717 0.7802 9.7126 -1.7163 -0.8623 -0.4231 0.1416 -0.26 -3.5353 -0.4938 0.2481 -0.421 -0.9533 1.6843 -0.8045 -0.538 1.5404 1.4694 2.2061 2.3152 0.253 -0.6403 -1.249 0.8.RS -0.1253 -0.2091 0.5868 -0.0401 1.1227 0.2433 2.8722 0.2499 3.4067 -1.2903 -0.5565 0.0438 0.6575 0.5757 -1.4815 0.6152 -0.034 -0.3822 -1.209 0.3766 -1.2778 0.9093 0.3166 -2.1651 1.0896 -0.5001 2.256 -2.1366 4.576R -0.416 -1.2041 2.092 -0.6093 2.7574 0.4472 -2.3152 -2.5524 -4.5837 3.3333 0.3697 -0.252 -0.5776 -0.8031 -1.2017 -2.5556 -1.4313 0.6776 -0.6731 -1.4521 -3.0452 2.7356 -0.1972 -3.7857 -2.7794 -2.0792 0.0817 2.5092 -2 0.8929 0.5478 -2.0069 -1.9026 2.1989 0.9066 0.7712 1.0853 2.3326 -0.382 1.1091 3.854 -1.0565 The Excel file EXCELPROJECT.xls ' available in Canvas contains daily holding period returns of 10 stocks over the period January 3, 2011 to December 31, 2018. The 10 stocks are: International Business Machs Co: ticker 'IBM Kroger Company: ticker 'KR' Tesla (Motors) INC: ticker 'TSLA' Delta AIR LINES INC: ticker 'DAL Travelers Companies Inc: ticker 'TRV Exxon Mobil Corp: ticker XOM' Toyota Motor Corp: ticker 'TM' Accenture PLC Ireland: ticker 'ACN' XEROX CORP: ticker XRX' BERKSHIRE HATHAWAY INC DEL: ticker 'BRK' On January 2, 2019 (Wednesday) you decide to invest in a portfolio consisting of these 10 stocks. You want to find the optimal portfolio to invest in, so you analyze the performance of these 10 stocks over time by using past historical data from January 3, 2011 to December 31, 2018. Find the composition of the optimal portfolio you want to invest in. Assume that riskless borrowing/lending is possible. Please use the one-month T-bill rate on January 2, 2019, that is 0.0001. Short selling is allowed. Please solve this part using Excel Solver (use the 'GRG Nonlinear' Solving method). After finding the composition of the optimal portfolio, please report below the proportion of your funds invested in Toyota Motor Corp. (Please do not report the %, but rather the full number with 3 decimals.) Retums_TSLA Returns_TRV Returns_XOM Returns ACN Returns_IBM 0.4906 0.1085 Returns_KR -1.5653 -1.4084 Returns_DAL 0.1587 Retums_XRX 0.2062 0.1975 1.9557 1.6493 0.4695 0.267 -2.6473 0.5263 0.3996 -0.4479 0.5938 -1.5387 0.5912 -1.0984 -0.0461 1.0949 -0.4911 -0.0375 0.1878 0.5999 3.9135 1.2913 0.7436 -5.2373 -0.3975 3.5914 -0.5393 0.6971 -3.0769 -1.9841 Retums_TM 1.0174 0.5414 0.4257 1.6708 1.1283 0.2759 0.737 1.2713 1.1251 -0.6586 0.0207 0.3728 0.1651 -0.8859 1.3719 -1.9489 Returns_BRK 0.3745 -0.2985 0.923 -0.6674 -0.7839 -0.1129 -0.3139 1.6877 0.0248 1.102 -0.3241 -0.3716 0 1.2955 1.3986 -1.0575 0.9751 0.0929 1.2801 0.1465 0.0732 0.6426 0.5454 -0.6085 0.7454 1.1758 0.1698 1.4731 1.1177 -0.5971 -0.6263 1.5R2 0.7994 1.1102 2.2965 -0.0401 0.2607 0.6575 -2.7448 1.7925 -0.4272 -6.2792 -5.8677 1.856 0.3717 -0.0466 0.3733 0.3719 -0.139 -8.2157 -2.1577 0.0732 -0.4024 -0.9074 -1.383 0.7 -0.2979 1.8725 0.2082 -1.1 0 -1.3962 0.9234 0.6135 0.4355 -1.8213 2.6502 0.6024 -1.2831 -3.3796 -0.0097 0.8079 1.5138 7.6316 2.4691 -2.2243 0.6635 2.4482 1.3974 -0.5029 0.7897 0.2187 -0.7619 -1.6195 -0.1955 -0.2348 -0.3468 3.0951 0.5494 4.1594 -0.4938 1.1273 1.0967 0.8425 0.4857 -0.196 -0.2438 1.2357 0.1878 0.7929 0.4333 3.3455 0.0702 -0.1926 2.656 1.1339 0.2478 0.0186 -1.1540 1.7524 0.963 -0.159 0.1408 0.2874 0.5 1.1406 -0.8431 -0.2304 0.1848 0.0534 -3.2446 2.988 2.6926 -3.8211 -0.4628 -1.0228 0.3738 0.2162 -0.0482 -1.7704 1.2181 0.0191 -1.6043 0.7758 0.2835 0.6869 3.6517 0.3743 -0.7884 0.1255 -1.2949 1.8168 0.4311 1.8722 1.5069 -2.5979 1.0079 1.3507 2.0531 -0.3765 0.5167 1.3551 -0.2305 1.4787 -0.097 1.7486 0.1145 1.2456 0.2762 -1.1142 2.1395 4.0035 -0.5959 0.036 0.1918 0.7805 -0.6076 -1.3525 1.0283 3.732 1.5859 1.1275 1.3158 2.684 0.7651 -1.448 0.8063 0.9776 0.176 0.9522 0.2795 -0.4006 1.2242 1.6068 -0.5951 0.3628 0.4186 0.4358 1.6849 0.7194 -1.6524 6.1552 1 DATE 2 3-Jan-11 3 3 4-Jan-11 4 5-Jan-11 5 6-Jan-11 5 7-Jan-11 7 10-Jan-11 8 11-Jan-11 12-Jan-11 90 13-Jan-11 1 14-Jan-11 12 18-Jan-11 19-Jan-11 20-Jan-11 15 21-Jan-11 24-Jan-11 25-Jan-11 26-Jan-11 19 27-Jan-11 20 28-Jan-11 21 31-Jan-11 22 1-Feb-11 2-Feb-11 24 3-Feb-11 25 4-Feb-11 26 7-Feb-11 27 8-Feb-11 28 9-Feb-11 39 10-Feb-11 11-Feb-11 31 14-Feb-11 32 15-Feb-11 16-Feb-11 17-Feb-11 35 18-Feb-11 35 22-Feb-11 47 23-Feb-11 38 24-Feb-11 39 25-Feb-11 10 28-Feb-11 41 1-Mar-11 42 2-Mar-11 43 3-Mar-11 4-Mar-11 15 7-Mar-11 45 8-Mar-11 117 9-Mar-11 10-Mar-11 49 11-Mar-11 50 14-Mar-11 $1 15-Mar-11 52 16-Mar-11 53 17-Mar-11 54 18-Mar-11 0.0827 0.4484 4.0409 0.2284 0.057 -0.1838 0.4604 0.5499 0.2765 -0.2757 0.2682 -0.4591 1.9572 -1.0798 0.6913 0.1807 1.1182 0.202 -0.4509 0.6675 0.5447 0.4593 -5.2266 0 -0.2239 1.6158 -0.53 0.7771 -0.7083 -1.0118 0.114 -0.5182 0.7874 1.1968 -0.9037 -0.3401 0.0215 -0.4259 0.5503 -1.1134 1.3848 1.1993 0.1508 -0.7312 1.7964 -0.5723 0.9066 -0.2543 -0.4567 2.5235 -0.3845 0.2328 0.3439 3.065 0.1724 0.5469 0.3626 0.8025 2.6979 0.6768 -0.6723 -1.4382 0.0191 0.8014 2.7313 0.8668 0.1853 0.5382 2.2848 0.8678 0.227 0.7392 2.71 0 0.3751 -0.0235 -0.0359 0.7637 -0.2455 -0.2675 1.0399 8.275 4.5693 -1.7797 -5.6514 -0.1829 3.2065 -0.5599 0.0433 0.4329 1.1796 -0.7957 -2.313 1.1124 -2.6931 -1.2876 -5.5087 2.6071 0.9982 1.907 -0.6402 -0.1817 -1.804 -0.4398 -3.9753 -1.7479 -9.6554 0.1413 -2.2105 -0.3727 0.6034 1.9078 -1.2633 -0.5734 -0.0776 -0.3879 -0.6058 0.4789 0.175 -0.7424 -2.1195 0.9559 0.8309 0.4551 0.5141 0.2653 -1.7532 -1.0929 0.3583 0.9392 -0.2465 - 1.1799 0.1188 2.0729 -1.0093 -1.1741 1.8114 4.7935 1.1859 0.2093 0.3342 1.4155 0.0898 1.0791 -5.605 0.5537 -1.1847 0.7328 0.2226 0.RSBS 0.3302 1.8313 1.6894 -0.493 -1.3415 0.6216 1.0368 0.2798 - 1.2093 2.8396 2.3258 -0.2698 1.976 -0.9239 -0.0195 -4.3355 1.0837 -3.4113 -0.6923 0.40R1 -1.411 0.7211 2.2202 0.8698 3.6755 -0.7533 1.2334 -1.687 2.5195 2.422 0.2202 -1.2017 -0.7156 -1.3841 1.8154 0.1695 0.2961 0.0844 -0.3717 0.7802 9.7126 -1.7163 -0.8623 -0.4231 0.1416 -0.26 -3.5353 -0.4938 0.2481 -0.421 -0.9533 1.6843 -0.8045 -0.538 1.5404 1.4694 2.2061 2.3152 0.253 -0.6403 -1.249 0.8.RS -0.1253 -0.2091 0.5868 -0.0401 1.1227 0.2433 2.8722 0.2499 3.4067 -1.2903 -0.5565 0.0438 0.6575 0.5757 -1.4815 0.6152 -0.034 -0.3822 -1.209 0.3766 -1.2778 0.9093 0.3166 -2.1651 1.0896 -0.5001 2.256 -2.1366 4.576R -0.416 -1.2041 2.092 -0.6093 2.7574 0.4472 -2.3152 -2.5524 -4.5837 3.3333 0.3697 -0.252 -0.5776 -0.8031 -1.2017 -2.5556 -1.4313 0.6776 -0.6731 -1.4521 -3.0452 2.7356 -0.1972 -3.7857 -2.7794 -2.0792 0.0817 2.5092 -2 0.8929 0.5478 -2.0069 -1.9026 2.1989 0.9066 0.7712 1.0853 2.3326 -0.382 1.1091 3.854 -1.0565
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started