Factor Model Estimation Problem - CAPM

Transform the Price data to returns data using the log-ratio method. Perform a regression of XOM on SPY.

Perform a multiple regression of XOM on SPY and OIL.

Answer these questions in the indicated box in the file.

1) What is the beta of XOM? Is it significant?

2) Does it seem that OIL adds explanatory power above that of SPY in the multiple regression? What statistics support your answer?

3) Suppose you like the way XOM is run by its management, and you want to own the stock; however, you are worried about further declines in oil prices, suggest a portfolio that might be appropriate for such an outlook.

Attached are two excel files. One is an example of the assignment material that is expected, the other is the assignment with the questions listed.

Date SPY XOM Oil 1/2/2014 175.7579 93.556885 22 1/6/2014 176.9592 94.506454 21.68 1/13/2014 176.4787 93.227821 21.99 1/21/2014 171.9139 89.175659 22.620001 1/27/2014 171.2316 86.646591 22.74 2/3/2014 172.6731 85.764275 23.379999 2/10/2014 176.8439 89.106598 23.51 2/18/2014 176.7189 89.977684 24.09 2/24/2014 179.0253 91.151764 24.129999 3/3/2014 180.9185 89.939812 24.18 3/10/2014 177.4589 88.500626 23.290001 3/17/2014 179.7286 89.295967 23.49 3/24/2014 179.0433 92.505737 24.02 3/31/2014 179.9216 92.183815 23.889999 4/7/2014 175.2016 91.577843 24.440001 4/14/2014 179.912 95.081123 24.77 4/21/2014 179.8155 95.071663 24.01 4/28/2014 181.524 96.586594 23.799999 5/5/2014 181.4274 97.18515 23.860001 5/12/2014 181.5143 96.031708 24.41 5/19/2014 183.7344 96.584602 25.07 5/27/2014 185.9834 95.83152 24.709999 6/2/2014 188.5896 96.851509 24.66 6/9/2014 187.383 97.85244 25.73 6/16/2014 190.0364 98.977295 25.91 6/23/2014 189.92 96.479736 25.690001 6/30/2014 192.2283 97.795242 25.139999 7/7/2014 190.6862 96.98497 24.360001 7/14/2014 191.7531 97.928703 24.780001 7/21/2014 191.7627 98.357666 24.780001 7/28/2014 186.7 94.182381 23.700001 8/4/2014 187.4177 95.078445 23.639999 8/11/2014 189.823 95.05925 23.190001 8/18/2014 193.1884 94.550499 22.84 8/25/2014 194.6627 95.472008 23.43 9/2/2014 195.0506 95.280029 22.799999 9/8/2014 193.1303 91.93956 22.440001 9/15/2014 195.5628 93.225838 22.549999 9/22/2014 192.8345 91.603592 23 9/29/2014 191.4898 90.154137 22.01 10/6/2014 185.6629 87.927162 20.889999 10/13/2014 183.6459 87.552803 20.24 10/20/2014 191.40213 90.701286 19.959999 10/27/2014 196.4983 92.832268 19.790001 11/3/2014 198.1353 93.386482 19.190001 11/10/2014 199.0122 91.936234 18.49 11/17/2014 201.3898 93.59919 18.68 11/24/2014 201.8965 87.53714 16.209999 12/1/2014 202.676 90.708359 15.77 12/8/2014 195.748 83.727814 13.71 12/15/2014 202.3445 90.534325 13.65 12/22/2014 204.2257 90.118584 12.87 12/29/2014 201.27655 89.75119 12.21 1/5/2015 200.1204 89.045403 11.12 1/12/2015 197.5534 88.097908 11.09 1/20/2015 200.8259 87.875534 10.16 1/26/2015 195.4175 84.520615 10.68 2/2/2015 201.3941 89.13118 11.88 2/9/2015 205.53862 90.952774 12.01 2/17/2015 206.9691 87.592079 11.33 2/23/2015 206.4008 86.24781 10.95 3/2/2015 203.3047 83.413147 11.03 3/9/2015 201.6685 81.698715 10.03 3/16/2015 207.0761 82.351364 9.79 3/23/2015 202.4801 81.416222 10.25 3/30/2015 203.169 82.117584 10.51 4/6/2015 206.71196 83.344955 11.03 4/13/2015 204.6551 84.679489 12.01 4/20/2015 208.2964 84.71846 11.91 4/27/2015 207.3812 86.549782 12.39 5/4/2015 208.2669 86.686157 12.4 5/11/2015 209.0739 85.792389 12.48 5/18/2015 209.6152 84.97718 12.28 5/26/2015 207.79454 83.680725 12.35 6/1/2015 206.4462 82.77713 12.07 6/8/2015 206.6824 82.521767 12.31 6/15/2015 208.4789 83.690544 12.15 6/22/2015 207.4999 82.364624 12.11 6/29/2015 205.0275 81.657455 11.41 7/6/2015 205.18576 80.753868 10.55 Names: 1 2 3 Please write your answers in this box. 1) What is the beta of XOM? Is it significant? 2) Does it seem that OIL adds explanatory power above that of SPY in the multiple regression? What statistics support your answer? 3) Suppose you like the way XOM is run by its management, and you want to own the stock; however, you are worried about further declines in oil prices, suggest a portfolio that might be appropriate for such an outlook. 7/13/2015 210.12059 81.136909 7/20/2015 205.7 78.514526 7/27/2015 208.1724 77.797539 8/3/2015 205.6506 75.459976 8/10/2015 207.1043 77.6828 8/17/2015 195.6425 71.506638 8/24/2015 197.0764 74.421234 8/31/2015 190.4604 71.833794 9/8/2015 194.5645 72.061806 9/14/2015 194.2937 72.051888 9/21/2015 191.7289 72.597137 9/28/2015 193.8463 75.224236 10/5/2015 200.1389 78.575027 10/12/2015 202.0674 81.767197 10/19/2015 206.2823 82.262878 10/26/2015 206.6998 82.024948 11/2/2015 208.7973 83.739998 11/9/2015 201.3417 78.099998 11/16/2015 208.0717 79.790001 11/23/2015 208.32019 81.230003 11/30/2015 208.3798 78.860001 12/7/2015 200.6856 74.339996 12/14/2015 200.02 77.279999 12/21/2015 205.68 79.330002 12/28/2015 203.87 77.949997 1/4/2016 191.92 74.690002 1/11/2016 187.81 77.580002 1/19/2016 190.52 76.57 10.11 9.48 9.17 8.46 8.05 7.5 8.56 8.62 8.35 8.49 8.44 8.5 9.3 8.82 8.44 8.59 8.41 7.64 7.54 7.63 7.22 6.4 6.12 6.51 6.23 5.67 5.61 4.83 Date GLD SPY NEM 1/2/2014 119.29 175.7579 23.574385 1/6/2014 120.26 176.9592 23.45612 1/13/2014 120.93 176.4787 24.19528 1/21/2014 122.29 171.9139 24.52051 1/27/2014 120.09 171.2316 21.287907 2/3/2014 122.17 172.6731 21.209063 2/10/2014 127.15 176.8439 23.485685 2/18/2014 127.58 176.7189 23.042185 2/24/2014 127.62 179.0253 22.923922 3/3/2014 129.09 180.9185 24.24456 3/10/2014 133.1 177.4589 25.961924 3/17/2014 128.47 179.7286 24.345499 3/24/2014 124.56 179.0433 23.720749 3/31/2014 125.57 179.9216 23.909166 4/7/2014 126.93 175.2016 24.028166 4/14/2014 124.75 179.912 23.34391 4/21/2014 125.43 179.8155 26.229673 4/28/2014 125.06 181.524 24.514084 5/5/2014 124.1 181.4274 23.819916 5/12/2014 124.5 181.5143 23.790165 5/19/2014 124.51 183.7344 23.20508 5/27/2014 120.43 185.9834 22.699327 6/2/2014 120.61 188.5896 22.917496 6/9/2014 122.96 187.383 23.230698 6/16/2014 126.5 190.0364 24.680136 6/23/2014 126.66 189.92 25.007748 6/30/2014 127.16 192.2283 25.007748 7/7/2014 128.78 190.6862 25.742393 7/14/2014 126.13 191.7531 25.236084 7/21/2014 125.79 191.7627 24.968037 7/28/2014 124.38 186.7 25.017675 8/4/2014 126.19 187.4177 26.199064 8/11/2014 125.48 189.823 26.695448 8/18/2014 123.19 193.1884 26.199064 8/25/2014 123.86 194.6627 26.894001 9/2/2014 122.06 195.0506 25.782103 9/8/2014 118.38 193.1303 25.012617 9/15/2014 117.09 195.5628 24.05862 9/22/2014 117.06 192.8345 23.66112 9/29/2014 114.61 191.4898 22.558064 10/6/2014 117.59 185.6629 22.071125 10/13/2014 118.99 183.6459 22.259937 10/20/2014 118.35 191.40213 21.812752 10/27/2014 112.66 196.4983 18.642698 11/3/2014 112.97 198.1353 19.070009 11/10/2014 114.47 199.0122 19.03026 11/17/2014 115.39 201.3898 19.566883 11/24/2014 112.11 201.8965 18.284948 12/1/2014 114.43 202.676 18.935692 12/8/2014 117.41 195.748 18.955591 12/15/2014 114.77 202.3445 19.025246 12/22/2014 114.83 204.2257 18.756584 12/29/2014 114.08 201.27655 19.24416 1/5/2015 117.26 200.1204 20.617315 1/12/2015 122.52 197.5534 22.179537 1/20/2015 124.23 200.8259 24.030317 1/26/2015 123.45 195.4175 25.02536 2/2/2015 118.64 201.3941 23.930813 2/9/2015 117.98 205.53862 24.657196 2/17/2015 115.28 206.9691 25.45323 2/23/2015 116.16 206.4008 26.199514 3/2/2015 111.86 203.3047 23.18453 3/9/2015 110.88 201.6685 22.21426 3/16/2015 113.57 207.0761 22.861759 3/23/2015 115.06 202.4801 22.14453 3/30/2015 115.28 203.169 22.2541 4/6/2015 115.97 206.71196 22.24414 4/13/2015 115.6 204.6551 23.479374 4/20/2015 113.05 208.2964 24.883951 4/27/2015 113.08 207.3812 26.388145 5/4/2015 113.97 208.2669 25.939878 5/11/2015 117.53 209.0739 27.583532 5/18/2015 115.6 209.6152 26.906147 5/26/2015 114.1 207.79454 27.13526 6/1/2015 112.24 206.4462 25.810377 6/8/2015 113.23 206.6824 23.482168 6/15/2015 115.12 208.4789 24.579 6/22/2015 112.56 207.4999 23.76136 6/29/2015 111.76 205.0275 23.601824 7/6/2015 111.49 205.18576 22.45514 7/13/2015 7/20/2015 7/27/2015 8/3/2015 8/10/2015 8/17/2015 8/24/2015 8/31/2015 9/8/2015 9/14/2015 9/21/2015 9/28/2015 10/5/2015 10/12/2015 10/19/2015 10/26/2015 11/2/2015 11/9/2015 11/16/2015 11/23/2015 11/30/2015 12/7/2015 12/14/2015 12/21/2015 12/28/2015 108.65 210.12059 20.630407 105.35 205.7 17.74873 104.93 208.1724 17.120546 104.65 205.6506 16.093512 106.85 207.1043 17.300028 111.13 195.6425 18.087751 108.7 197.0764 17.120546 107.49 190.4604 15.654779 106.16 194.5645 16.219051 109.21 194.2937 16.958096 109.81 191.7289 16.5686 108.99 193.8463 17.047979 110.87 200.1389 18.905581 112.49 202.0674 18.965504 111.5 206.2823 19.52478 109.3 206.6998 19.434896 104.1 208.7973 17.517374 103.56 201.3417 17.477427 103.09 208.0717 17.657194 101.25 208.32019 17.367567 104.02 208.3798 20.433607 103.11 200.6856 19.129999 102.04 200.02 17.709999 103.05 205.68 18.540001 101.46 203.87 17.99 Date GLD SPY NEM 1/2/2014 119.29 175.7579 23.574385 1/6/2014 120.26 176.9592 23.45612 1/13/2014 120.93 176.4787 24.19528 1/21/2014 122.29 171.9139 24.52051 1/27/2014 120.09 171.2316 21.287907 2/3/2014 122.17 172.6731 21.209063 2/10/2014 127.15 176.8439 23.485685 2/18/2014 127.58 176.7189 23.042185 2/24/2014 127.62 179.0253 22.923922 3/3/2014 129.09 180.9185 24.24456 3/10/2014 133.1 177.4589 25.961924 3/17/2014 128.47 179.7286 24.345499 3/24/2014 124.56 179.0433 23.720749 3/31/2014 125.57 179.9216 23.909166 4/7/2014 126.93 175.2016 24.028166 4/14/2014 124.75 179.912 23.34391 4/21/2014 125.43 179.8155 26.229673 4/28/2014 125.06 181.524 24.514084 5/5/2014 124.1 181.4274 23.819916 5/12/2014 124.5 181.5143 23.790165 5/19/2014 124.51 183.7344 23.20508 5/27/2014 120.43 185.9834 22.699327 6/2/2014 120.61 188.5896 22.917496 6/9/2014 122.96 187.383 23.230698 6/16/2014 126.5 190.0364 24.680136 6/23/2014 126.66 189.92 25.007748 6/30/2014 127.16 192.2283 25.007748 7/7/2014 128.78 190.6862 25.742393 7/14/2014 126.13 191.7531 25.236084 7/21/2014 125.79 191.7627 24.968037 7/28/2014 124.38 186.7 25.017675 8/4/2014 126.19 187.4177 26.199064 8/11/2014 125.48 189.823 26.695448 8/18/2014 123.19 193.1884 26.199064 8/25/2014 123.86 194.6627 26.894001 9/2/2014 122.06 195.0506 25.782103 9/8/2014 118.38 193.1303 25.012617 9/15/2014 117.09 195.5628 24.05862 9/22/2014 117.06 192.8345 23.66112 9/29/2014 114.61 191.4898 22.558064 10/6/2014 117.59 185.6629 22.071125 10/13/2014 118.99 183.6459 22.259937 10/20/2014 118.35 191.40213 21.812752 10/27/2014 112.66 196.4983 18.642698 11/3/2014 112.97 198.1353 19.070009 11/10/2014 114.47 199.0122 19.03026 11/17/2014 115.39 201.3898 19.566883 11/24/2014 112.11 201.8965 18.284948 12/1/2014 114.43 202.676 18.935692 12/8/2014 117.41 195.748 18.955591 12/15/2014 114.77 202.3445 19.025246 12/22/2014 114.83 204.2257 18.756584 12/29/2014 114.08 201.27655 19.24416 1/5/2015 117.26 200.1204 20.617315 1/12/2015 122.52 197.5534 22.179537 1/20/2015 124.23 200.8259 24.030317 1/26/2015 123.45 195.4175 25.02536 2/2/2015 118.64 201.3941 23.930813 2/9/2015 117.98 205.53862 24.657196 2/17/2015 115.28 206.9691 25.45323 2/23/2015 116.16 206.4008 26.199514 3/2/2015 111.86 203.3047 23.18453 3/9/2015 110.88 201.6685 22.21426 3/16/2015 113.57 207.0761 22.861759 3/23/2015 115.06 202.4801 22.14453 3/30/2015 115.28 203.169 22.2541 4/6/2015 115.97 206.71196 22.24414 4/13/2015 115.6 204.6551 23.479374 4/20/2015 113.05 208.2964 24.883951 4/27/2015 113.08 207.3812 26.388145 5/4/2015 113.97 208.2669 25.939878 5/11/2015 117.53 209.0739 27.583532 5/18/2015 115.6 209.6152 26.906147 5/26/2015 114.1 207.79454 27.13526 6/1/2015 112.24 206.4462 25.810377 6/8/2015 113.23 206.6824 23.482168 6/15/2015 115.12 208.4789 24.579 6/22/2015 112.56 207.4999 23.76136 6/29/2015 111.76 205.0275 23.601824 GLD SPY NEM 0.008099 0.006812 -0.005029 0.005556 -0.002719 0.031026 0.011183 -0.026206 0.013352 -0.018154 -0.003977 -0.141371 0.017172 0.008383 -0.003711 0.039954 0.023867 0.101962 0.003376 -0.000707 -0.019064 0.000313 0.012967 -0.005146 0.011453 0.010519 0.056011 0.030591 -0.019308 0.068439 -0.035405 0.012709 -0.064284 -0.030908 -0.00382 -0.025997 0.008076 0.004894 0.007912 0.010772 -0.026584 0.004965 -0.017324 0.026531 -0.02889 0.005436 -0.000537 0.116555 -0.002954 0.009456 -0.067644 -0.007706 -0.000532 -0.028726 0.003218 0.000479 -0.00125 8.03E-005 0.012157 -0.024901 -0.033317 0.012166 -0.022036 0.001494 0.013916 0.009565 0.019297 -0.006418 0.013574 0.028383 0.014061 0.060524 0.001264 -0.000613 0.013187 0.00394 0.012081 0 0.012659 -0.008054 0.028953 -0.020792 0.005579 -0.019864 -0.002699 5.05E-005 -0.010678 -0.011272 -0.026756 0.001986 0.014447 0.003837 0.046141 -0.005642 0.012752 0.018769 -0.018419 0.017574 -0.018769 0.005424 0.007602 0.02618 -0.014639 0.001991 -0.042223 -0.030613 -0.009894 -0.0303 -0.010957 0.012517 -0.038887 -0.000256 -0.014049 -0.01666 -0.021152 -0.006998 -0.047741 0.025669 -0.030902 -0.021822 0.011835 -0.010923 0.008518 -0.005393 0.041367 -0.020294 -0.049272 0.026277 -0.15704 0.002748 0.008296 0.022662 0.01319 0.004416 -0.002087 0.008005 0.011876 0.027808 -0.028837 0.002513 -0.06776 0.020483 0.003854 0.03497 0.025709 -0.034781 0.00105 -0.022742 0.033144 0.003668 0.000523 0.009254 -0.014222 -0.006553 -0.014546 0.025663 0.027494 -0.005761 0.068924 0.043881 -0.01291 0.073039 0.01386 0.016429 0.080146 -0.006299 -0.0273 0.040574 -0.039743 0.030126 -0.044723 -0.005579 0.02037 0.029902 -0.023151 0.006936 0.031774 0.007605 -0.002749 0.028898 -0.03772 -0.015114 -0.122256 -0.0088 -0.008081 -0.042751 0.023971 0.026461 0.028731 0.013034 -0.022445 -0.031875 0.00191 0.003397 0.004936 0.005968 0.017288 -0.000448 -0.003196 -0.01 0.054044 -0.022306 0.017636 0.058101 0.000265 -0.004404 0.058692 0.00784 0.004262 -0.017133 0.030758 0.003867 0.061437 -0.016558 0.002586 -0.024864 -0.013061 -0.008724 0.008479 -0.016436 -0.00651 -0.050057 0.008782 0.001143 -0.094535 0.016554 0.008655 0.045651 -0.022489 -0.004707 -0.033832 -0.007133 -0.011987 -0.006737 NEM vs SPY Return SUMMARY OUTPUT 0.2 Regression Statistics Multiple R 0.103585 R Square 0.01073