Refer to the Regression worksheet.
This worksheet contains (i) monthly returns of a portfolio of five risky assets, (ii) monthly
returns of the market index, (iii) monthly returns of the risk-free asset, (iv) monthly
excess returns of the above portfolio of five risky assets, (v) the month-to-month change
in COVID-19 cases, expressed in percentage points, and (vi) the month-to-month change
in the size of the central banks balance sheet (lagged by one period).
Estimate the following regression models and paste the outputs precisely at the specified
ranges in the worksheet. Outputs, even if they are correct, that are pasted at the wrong
locations will receive zero credit.
(a) [5 marks] Set output range to cell M4. Interpret the intercept, and the estimated
coefficient on % Change in COVID cases
Excess returnsportfolio = + 1 % Change in COVID cases +
(b) [5 marks] Set output range to cell M27. Interpret the intercept, and both estimated
coefficients.
Excess returnsportfolio = + 1 % Change in COVID cases
+ 2 % Change in CB balance sheet +
Compute the root mean squared errors (RMSE) of your regression models. Show all
computations in the worksheet clearly. Answers, even if they are correct, that are unac-
companied by worksheet computations will receive zero credit.
(c) [5 marks] What is the RMSE of the model in part (a)?
(d) [5 marks] What is the RMSE of the model in part (b)?
Optimization Simulation Regression 2 . w RE RE SI IN WE 3398555 2119 6.549 140" 1.494 -0.21% 0.4149 1890 . 0762% -19 -1394 E 24 4.1998 -1419 TOT 038 O 1.16 5.10 3:57% 5.16 3.00 0.72499 02 - 032 2.62% 1.30 -789 3379 0.07% 54 02189 1999 101 6.12914 -3.5470 -35657 165574 063514 44352 2.498% 47561 2.7561 -2.4748% 510 -10.97% 1645 0.0005 1.4700 -3.5774 2001 -70 2.6072% 9058 -5056 25115 + 6.299 9,99 4,499 1.70 1.74 2.809 00 0.00 -1.5 DK -3.98 3. 6.07 0.86699 0.87389 020729 2.747 14619 NO 74% 1.4 $ 2930 50 1.0492 0.92245 10783% 11.67% 2.99 . 5.899 1315 OBUS 1. -0.36% . 45 0.65 0.414 -2.595 110 2429 1.47 154 0.50 0.499 1.504 - 10 0.78 0564 100 100 US 46 4729 359% 09115 1054% 0.706896 3. 5394 14 1.359 GON 1.09 -1.75 0.18 2014 JN 0.59% 2440 -28157 0.5783 1.48929 -34 4483 120 I. 0.21% 189999 00 5.97% 0.42 "T 60 271 1.154 10.25 1.34 110 52 10712 1899 5.5.2014 1140 -2.25189 1946 16199 -2.9554 190 NICO -0.345 2.93% 3:00 -0.599 -2664 -0.40 3. NO SC 0.17 0575 -1.215 0.6 0.1720 0.43119 011 07 -0.41 -1.6 0:00 19 0.14 3719 36 0.1992 SCO 066 UE ISIN 0.64 0.776 $299 1190 -999 2.30 2.68% 2.09 0.71 0.1 0:57 1.519 0.10 2,479 0.591 2.17 OXIN -0.91 0.50 2.A -1.6 1.24% -18 0.77 0.72 0.30 06 06 0.00 0:22 -0.04 140 -0.20% 041634 1,07799 es . 0.65019 1311 033311 661009 -7771 -1.914 - 31994 2.18301 -29% -1.60249 0643% 14189 11870 7429 2057% -0.49% 16730 1.000 -1.50 -0% -3.6661 2.2012 641391 -2.3537% -122199 2.630 15 2.4257 183919 07 -3.29 1994 0.1 SON 04 22 20 2014 02 0.3 64 019 0349 03759 0.9472 0.67% -2.4016 3. -1414 OLSUN 3.44% 0.58% 1619 NO WED 0416 . MSISD VO 1.00 0.17 0467 0.4009 0639196 001 067 TI 0.71% Optimization Simulation Regression NIT STOC Changes % Change in C COVID Chance sheet 65.7% 325398 31.75% 0:56% 78.31 -2.95 31.7% -13 -4019 446116 207 3000% -2.47% L.19% -437 397 112.40 7001% 2595 -2.28% 472 10 SO SE - SON 61915 STORE -10 4. TO 1.63% -1.00 re 18.24% 222 20.93% 2058 400 10-40% 99 63 30:98% 2340 -0.4% 2159 -1.84% 0.79% -1419 1910 0.39% 1859 2435 OSTE Portfolio Market Referate Enees Retur Returns Portfolio 4.28 0.872% -5.15 2.39% 4224 03631% -3.72% 16 -6.0276 2018 1714 0.99% 47679 -704 338% 14 7.60 4.94 0.91019 66710 -6,29% 4. 0.7767% -70647 12.23 9.33% 0:25:09 -12.45 16,991 03230 16.620 15.22 -11.95 0.4001% -1567614 33% 600 01699% 5.17035 -6.39 -$189 -75949 2.74% 067 0.1779 256314 -4344 0.72815 50415 -7.27% 06 -7.9150 13.1046 938% 10211 1.2.009 08463% 11217 8.841 624 7.77421 -4.00 -3399 03277% 43297 1.195 04564% 041705 3.98! 16 0.91019 -730% 0.7174 -501199 2 2.21 04591 2,424 38 -15% 10368% 487454 8.499 7035 0.1486% 100 LIN 0.4094% 2.764 5.20% 1.419 0.80399 43945 7.69 05364 -10. 0.71794 -43003 2.54 10 0.7430 3959 0,70.2% 6.4541 4.57% 0:58 0.1919 20 0.2006 -0.79% 081434 3.5043 3.99 -3.07 0.606% 457239 2.294 1011 -0.2147 00 -0.05% 0.279 - 2259 1.559 0.7051% 5544 1.47% 0.4405% 15 -2.525 0.32% 0.7015 4.99 2.66 0.7520 19. B-5001 -3.91 4414 2014 0.40939 449 6.901 0.87025 -1 SKOM 2015 0.0" 03401% 241814 -1.446 03324 2194 1.73% 1.154 0.9577 07149 2.2015 1.699 0.12% 2.071 -3.72 0.01 0127 +0459% -3.6 0.39% SON 1199 0.4600 -5.5 4896 LIS 1024 3.5477 0.7300 74816 3.15% 0.84074 6641 -3301 -1054 03492 - 2849 167% 0.12914 2.7129 -0.601 03576 -1.15704 0.2350 -1 231 8,19% 1.25 05077% TIN 6.549 1.484 0.414 612919 so 2.60% -6.92% 2144 -2.09% - 349 -2.109 -1.50 -119 -1.1994 2.9% 16 ANTO 00% 0 407 0:44 00 120 17% 2014 0259 -3.99 1234 010 . 0.12% 10 0. 19 NOWSE O 20 -16% 439 -0.77% 0.19 0.2% . -1019 - 14 0.50 -2.10 -5.59 -0.0 -141 2.19% Sur TRUST 0.99 4660 WISSE ! 0.7% 119 121% 400% 0X19 1.79% -1.74% LIIS 504 SIN TO SUEY STE -2.119 3.06% Optimization Simulation Regression 2 . w RE RE SI IN WE 3398555 2119 6.549 140" 1.494 -0.21% 0.4149 1890 . 0762% -19 -1394 E 24 4.1998 -1419 TOT 038 O 1.16 5.10 3:57% 5.16 3.00 0.72499 02 - 032 2.62% 1.30 -789 3379 0.07% 54 02189 1999 101 6.12914 -3.5470 -35657 165574 063514 44352 2.498% 47561 2.7561 -2.4748% 510 -10.97% 1645 0.0005 1.4700 -3.5774 2001 -70 2.6072% 9058 -5056 25115 + 6.299 9,99 4,499 1.70 1.74 2.809 00 0.00 -1.5 DK -3.98 3. 6.07 0.86699 0.87389 020729 2.747 14619 NO 74% 1.4 $ 2930 50 1.0492 0.92245 10783% 11.67% 2.99 . 5.899 1315 OBUS 1. -0.36% . 45 0.65 0.414 -2.595 110 2429 1.47 154 0.50 0.499 1.504 - 10 0.78 0564 100 100 US 46 4729 359% 09115 1054% 0.706896 3. 5394 14 1.359 GON 1.09 -1.75 0.18 2014 JN 0.59% 2440 -28157 0.5783 1.48929 -34 4483 120 I. 0.21% 189999 00 5.97% 0.42 "T 60 271 1.154 10.25 1.34 110 52 10712 1899 5.5.2014 1140 -2.25189 1946 16199 -2.9554 190 NICO -0.345 2.93% 3:00 -0.599 -2664 -0.40 3. NO SC 0.17 0575 -1.215 0.6 0.1720 0.43119 011 07 -0.41 -1.6 0:00 19 0.14 3719 36 0.1992 SCO 066 UE ISIN 0.64 0.776 $299 1190 -999 2.30 2.68% 2.09 0.71 0.1 0:57 1.519 0.10 2,479 0.591 2.17 OXIN -0.91 0.50 2.A -1.6 1.24% -18 0.77 0.72 0.30 06 06 0.00 0:22 -0.04 140 -0.20% 041634 1,07799 es . 0.65019 1311 033311 661009 -7771 -1.914 - 31994 2.18301 -29% -1.60249 0643% 14189 11870 7429 2057% -0.49% 16730 1.000 -1.50 -0% -3.6661 2.2012 641391 -2.3537% -122199 2.630 15 2.4257 183919 07 -3.29 1994 0.1 SON 04 22 20 2014 02 0.3 64 019 0349 03759 0.9472 0.67% -2.4016 3. -1414 OLSUN 3.44% 0.58% 1619 NO WED 0416 . MSISD VO 1.00 0.17 0467 0.4009 0639196 001 067 TI 0.71% Optimization Simulation Regression NIT STOC Changes % Change in C COVID Chance sheet 65.7% 325398 31.75% 0:56% 78.31 -2.95 31.7% -13 -4019 446116 207 3000% -2.47% L.19% -437 397 112.40 7001% 2595 -2.28% 472 10 SO SE - SON 61915 STORE -10 4. TO 1.63% -1.00 re 18.24% 222 20.93% 2058 400 10-40% 99 63 30:98% 2340 -0.4% 2159 -1.84% 0.79% -1419 1910 0.39% 1859 2435 OSTE Portfolio Market Referate Enees Retur Returns Portfolio 4.28 0.872% -5.15 2.39% 4224 03631% -3.72% 16 -6.0276 2018 1714 0.99% 47679 -704 338% 14 7.60 4.94 0.91019 66710 -6,29% 4. 0.7767% -70647 12.23 9.33% 0:25:09 -12.45 16,991 03230 16.620 15.22 -11.95 0.4001% -1567614 33% 600 01699% 5.17035 -6.39 -$189 -75949 2.74% 067 0.1779 256314 -4344 0.72815 50415 -7.27% 06 -7.9150 13.1046 938% 10211 1.2.009 08463% 11217 8.841 624 7.77421 -4.00 -3399 03277% 43297 1.195 04564% 041705 3.98! 16 0.91019 -730% 0.7174 -501199 2 2.21 04591 2,424 38 -15% 10368% 487454 8.499 7035 0.1486% 100 LIN 0.4094% 2.764 5.20% 1.419 0.80399 43945 7.69 05364 -10. 0.71794 -43003 2.54 10 0.7430 3959 0,70.2% 6.4541 4.57% 0:58 0.1919 20 0.2006 -0.79% 081434 3.5043 3.99 -3.07 0.606% 457239 2.294 1011 -0.2147 00 -0.05% 0.279 - 2259 1.559 0.7051% 5544 1.47% 0.4405% 15 -2.525 0.32% 0.7015 4.99 2.66 0.7520 19. B-5001 -3.91 4414 2014 0.40939 449 6.901 0.87025 -1 SKOM 2015 0.0" 03401% 241814 -1.446 03324 2194 1.73% 1.154 0.9577 07149 2.2015 1.699 0.12% 2.071 -3.72 0.01 0127 +0459% -3.6 0.39% SON 1199 0.4600 -5.5 4896 LIS 1024 3.5477 0.7300 74816 3.15% 0.84074 6641 -3301 -1054 03492 - 2849 167% 0.12914 2.7129 -0.601 03576 -1.15704 0.2350 -1 231 8,19% 1.25 05077% TIN 6.549 1.484 0.414 612919 so 2.60% -6.92% 2144 -2.09% - 349 -2.109 -1.50 -119 -1.1994 2.9% 16 ANTO 00% 0 407 0:44 00 120 17% 2014 0259 -3.99 1234 010 . 0.12% 10 0. 19 NOWSE O 20 -16% 439 -0.77% 0.19 0.2% . -1019 - 14 0.50 -2.10 -5.59 -0.0 -141 2.19% Sur TRUST 0.99 4660 WISSE ! 0.7% 119 121% 400% 0X19 1.79% -1.74% LIIS 504 SIN TO SUEY STE -2.119 3.06%
