Build SARIMA model equation with exogenous variable or regressors

Question

I have a SARIMA model with one regressor (X):

> fit_arima.reg
Series: sales.ts 
Regression with ARIMA(0,0,1)(2,1,1)[7] errors 

Coefficients:
         ma1    sar1    sar2     sma1     xreg
      0.3078  0.3401  0.1026  -0.8621  -0.0015
s.e.  0.0305  0.0839  0.0636   0.0731   0.0016

sigma^2 estimated as 0.02782:  log likelihood=348.89
AIC=-685.79   AICc=-685.7   BIC=-656.73

I would like to use these coefficients to obtain the actual equation. I took the example from this post, this post, this post and look at the ARIMAX Model Muddle post from Rob Hyndman's blog, but still the fitted and predicted values generated from the R forecast differ from those reproduced by hand from the built equation. I would appreciate your help with the correct equation. Here the built formula:

(EDITED based on the reply of Dr. Reilly and this post):

# (1 - sar1*B7 - sar2*B14)*(1 - B7)*(1 - xreg*X(t))*Y(t) = (1 + ma1*B)*(1-sma1*B7)E(t)

# thus:

# Y(t) = sar1*(Yt-7) + sar2*(Yt-14) + xreg*(Xt) - sar1*xreg*(X-7) - sar2*xreg*(Xt-14) + (Yt-7) - sar1*(Yt-14) -sar2*(Yt-21) - xreg*(Xt-7) + sar1*xreg(Xt-14) + sar2*xreg*(Xt-21) + ma1*(Et-1) - sma1*(Et-7) - ma1*sma1*(Et-8)

Here the original transformed data:

> data$transf
  [1] 3.340642 3.665769 3.483445 3.192846 3.210586 3.463296 3.794070
  [8] 3.369216 3.051538 2.998695 3.069298 2.957607 3.360215 3.770705
 [15] 3.430720 3.050380 3.124830 3.305566 3.220892 3.747101 3.878349
 [22] 3.655427 3.037426 3.143951 3.137354 3.378216 3.740126 3.446692
 [29] 2.987219 3.171141 3.226600 3.193125 3.686726 3.486147 3.075547
 [36] 3.233504 3.339253 3.258637 3.675962 3.787460 3.399501 3.509203
 [43] 3.738622 3.580241 3.614053 3.802089 3.838471 3.427486 3.134177
 [50] 3.286456 3.443576 3.341435 3.772908 3.864452 3.480869 2.893207
 [57] 3.187803 3.277609 3.231470 3.725503 3.857995 3.505693 3.156246
 [64] 3.212454 3.256237 3.281488 3.691524 3.812780 3.400538 3.076276
 [71] 3.218536 3.138618 3.158061 3.670988 3.594171 3.069668 3.076276
 [78] 3.281033 3.351796 3.713491 3.889806 3.198107 3.254548 3.292699
 [85] 3.371806 3.747179 3.731508 3.454235 3.361728 3.381115 3.496376
 [92] 3.537693 3.656769 3.695131 3.292034 3.388279 3.425208 3.445293
 [99] 3.639785 3.721728 3.608847 3.115278 3.464340 3.420451 3.148294
[106] 3.562887 3.599774 3.846523 3.627263 3.262925 3.342620 3.224792
[113] 3.597476 3.710033 3.787319 3.605951 3.530072 3.026533 3.198382
[120] 3.293584 3.383277 3.784902 3.497483 3.085291 3.109579 3.365113
[127] 3.396025 3.620240 3.862191 3.437909 3.120574 3.274850 3.127105
[134] 3.353916 3.697665 3.735599 3.420781 3.180413 3.299725 3.266702
[141] 3.317227 3.516403 3.608098 3.452553 3.425045 3.452400 3.388101
[148] 3.494850 3.574726 3.698449 3.421439 3.093071 2.913284 3.119256
[155] 3.384891 3.478133 3.658965 3.388989 3.077004 3.160769 3.098644
[162] 3.453777 3.579212 3.708846 3.443106 3.160469 3.264346 3.019947
[169] 3.510277 3.585235 3.771146 3.398808 3.077731 3.253822 3.342620
[176] 3.193125 3.582972 3.674861 3.678791 3.128399 3.168497 3.291147
[183] 3.236033 3.581039 3.598134 3.430720 3.040207 3.231724 3.386499
[190] 3.372175 3.716337 3.750663 3.360593 3.142076 3.151676 3.258158
[197] 3.590396 3.489958 3.599119 3.445760 3.226084 3.301898 3.496376
[204] 3.486855 3.492341 3.515874 3.322426 3.315970 3.292034 3.396896
[211] 3.373096 3.611617 3.727541 3.391464 3.181844 3.405517 3.405346
[218] 3.510277 3.651666 3.650793 3.547898 3.180413 3.342423 3.458336
[225] 3.526598 3.494294 3.680698 3.256718 3.264818 3.454235 3.346744
[232] 3.560385 3.495128 3.599774 3.418798 3.461499 3.389698 3.349472
[239] 3.583879 3.522966 3.650599 3.462398 3.504743 3.188647 3.473049
[246] 3.758761 3.735759 3.325105 3.098990 3.225051 3.284656 3.302764
[253] 3.724440 3.627468 3.499962 3.125806 3.150142 3.354301 3.359456
[260] 3.752740 3.772762 3.391817 3.050380 3.187239 3.319730 3.308351
[267] 3.666892 3.752586 3.605521 3.071514 3.164055 3.259355 3.411788
[274] 3.650696 3.840169 3.370513 3.204391 3.211921 3.244277 3.217221
[281] 3.510277 3.813314 3.348694 2.962369 3.152594 3.501880 3.540705
[288] 3.426999 3.802910 3.394101 3.349472 3.573684 3.421275 3.696269
[295] 3.729893 3.722222 3.566673 3.147058 3.159266 3.296226 3.663512
[302] 3.567849 3.708336 3.359266 3.107549 3.201943 3.242790 3.325105
[309] 3.681693 3.833784 3.513750 3.107888 3.080266 3.502564 3.438542
[316] 3.715335 3.793581 3.391288 2.997823 3.372175 3.295127 3.555457
[323] 3.630123 3.845160 3.544192 3.068557 3.121231 3.271609 3.510277
[330] 3.610447 3.750354 3.425371 3.140508 3.211388 3.507856 3.465532
[337] 3.772688 3.690285 3.655715 3.187803 3.330008 3.348110 3.601191
[344] 3.794279 3.868938 3.683227 3.343802 3.591621 3.724194 3.732233
[351] 3.823474 3.790356 3.646011 3.075182 3.458033 3.769156 3.822430
[358] 3.796366 3.889358 3.280578 3.431846 3.276692 3.297979 3.366796
[365] 3.571126 3.244030 2.982723 3.044932 3.168792 3.423574 3.652150
[372] 3.736476 3.516006 3.006894 3.097951 3.195069 3.269746 3.637890
[379] 3.761552 3.205204 3.063333 3.269980 3.226858 3.345570 3.551938
[386] 3.832381 3.344981 3.026533 3.047664 3.311754 3.522575 3.674034
[393] 3.822691 3.499275 2.999131 3.082067 3.265525 3.410271 3.746712
[400] 3.670431 3.330211 3.358696 3.308137 3.670802 3.781109 3.806587
[407] 3.847881 3.380392 3.106531 3.203577 3.175802 3.497759 3.632862
[414] 3.805161 3.371806 2.946943 2.958564 3.063709 3.138618 3.816042
[421] 3.591955 3.107888 3.225309 3.189209 3.324282 3.721728 3.765966
[428] 2.967548 3.149527 3.293584 3.441695 3.572058 3.660771 3.424882
[435] 3.056524 3.249443 3.187803 3.339451 3.729893 3.819939 3.544440
[442] 3.190892 3.129690 3.250420 3.532372 3.713910 3.703205 3.194237
[449] 3.574957 3.401917 3.400538 3.503927 3.828982 3.585799 3.414973
[456] 3.313234 3.424882 3.464936 3.532627 3.668759 3.723045 3.334655
[463] 3.108565 3.146438 3.190892 3.372912 3.496930 3.540204 3.265290
[470] 3.034227 3.284882 3.320562 3.426674 3.721398 3.815445 3.441066
[477] 2.887617 3.318481 3.247237 3.506640 3.708931 3.677059 3.692142
[484] 3.260310 3.137037 3.133219 3.299507 3.644439 3.700098 3.416474
[491] 3.140194 3.235528 3.428783 3.412461 3.537819 3.522705 3.331427
[498] 3.093071 3.086004 3.388279 3.552911 3.663324 3.570193 3.661718
[505] 3.291369 3.569842 3.366796 3.694342 3.662947 3.551206 3.320354
[512] 2.972203 2.933993 3.230193 3.305136 3.564429 3.714330 3.623559
[519] 3.087071 3.176670 3.389166 3.306639 3.644242 3.683857 3.487704
[526] 2.905256 3.318481 3.296226 3.400711 3.514282 3.723620 3.190051
[533] 3.214314 3.344981 3.162863 3.698622 3.783332 3.458184 2.940018
[540] 3.026533 3.303412 3.255996 3.478855 3.650113 3.266467 3.070038
[547] 3.218798 3.276232 3.375115 3.606919 3.795254 3.178977 3.071514
[554] 3.176381 3.371437 3.323871 3.611192 3.634981 3.478422 3.249198
[561] 3.378034 3.347135 3.349083 3.401056 3.725095 3.490520 3.351603
[568] 3.149835 3.310481 3.269980 3.356790 3.480294 3.332438 3.225568
[575] 3.250664 3.308991 3.368473 3.707059 3.578066 3.431846 3.168203
[582] 3.260548 3.398634 3.610128 3.495267 3.683047 3.202488 3.315130
[589] 3.310481 3.300378 3.527372 3.553883 3.695919 3.456214 3.450095
[596] 3.255514 3.279667 3.488410 3.741703 3.669503 3.200303 3.296446
[603] 3.243782 3.138934 3.192010 3.705607 3.571942 3.375481 3.161667
[610] 3.013259 3.141450 3.432809 3.552668 3.658393 3.342225 2.907411
[617] 3.123198 3.224792 3.434729 3.523096 3.744136 3.334856 3.083861
[624] 3.065206 3.246252 3.042576 3.662852 3.812312 3.340841 3.124504
[631] 3.063333 3.210586 3.258637 3.640283 3.626443 3.436799 2.943000
[638] 3.178689 3.184407 3.326131 3.567026 3.781684 3.337459 3.024486
[645] 3.243534 3.360404 3.431846 3.581950 3.616055 3.416474 3.297104
[652] 3.384533 3.410777 3.667640 3.775756 3.758230 3.581039 3.128076
[659] 3.207904 3.170848 3.268110 3.716254 3.728273 3.412629 2.978637
[666] 3.103804 3.127105 3.396896 3.629104 3.662758 3.352183 2.991669
[673] 3.255273 3.216166 3.143327 3.531607 3.707911 3.454082 3.104487
[680] 3.123525 3.240050 3.339849 3.564548 3.673021 3.541454 3.063709
[687] 3.220892 3.172019 3.495960 3.652440 3.782831 3.519434 3.185259
[694] 3.211121 3.046105 3.439333 3.670802 3.738067 3.594945 3.038223
[701] 3.260787 3.510813 3.631545 3.814714 3.804821 3.658965 3.315551
[708] 3.538197 3.508664 3.681784 3.778079 3.715335 3.641474 3.489114
[715] 2.932981 3.518119 3.707655 3.734240 3.665206 3.775610 3.158362
[722] 3.498724 3.215109 3.611511 3.657725 3.220631 2.893207 2.990339
[729] 3.264109 3.018284 3.556423 3.706974 3.280578 3.091667 3.150449
[736] 3.056524 3.303412 3.542203 3.561459 3.392521 3.194514 2.958086
[743] 3.179264 3.348500 3.855337 3.396374 3.077731 3.188647 3.114277
[750] 3.150756 3.510679 3.777862 3.434729 3.033021 3.135451 3.578983
[757] 3.643354 3.825296 3.347720 3.111934 3.177536 3.428621 3.680879
[764] 3.496515 3.817698 3.282396 3.411956 3.431525 3.447623 3.436322
[771] 3.783689 3.830653 3.264582 3.067443 3.262688 3.233504 3.463296
[778] 3.413635 3.743196 3.359646 2.980003 3.144263 3.217747 3.514813
[785] 3.624798 3.338656 3.143951 3.016616 3.096910 3.232234 3.434729
[792] 3.632862 3.570309 3.196729 3.226858 3.363048 3.641573 3.753200
[799] 3.351796 2.991226 3.112270 3.548021 3.468790 3.670060 3.757168
[806] 3.803389 3.101403 3.168792 3.173478 3.150756 3.443576 3.667546
[813] 3.315340 3.315130 3.221936 3.388634 3.443419 3.481156 3.758988
[820] 3.358886 3.265054 3.306425 3.301030 3.594724 3.648945 3.631951
[827] 3.041393 3.290035 3.143951 2.919601 3.500922 3.648653 3.823930
[834] 3.319106 3.318898 3.242293 3.418964 3.605628 3.761025 3.615634
[841] 3.640084 3.161368 3.144574 3.604442 3.729651 3.414806 2.888179
[848] 3.014100 3.236537 3.563837 3.471438 3.695657 3.491222 3.042969
[855] 3.215373 3.169968 3.276692 3.563362 3.662663 3.378761 3.370883
[862] 3.343999 3.414973 3.304921 3.630224 3.697752 3.268812 3.032216
[869] 3.060698 3.548389 3.073352 3.534280 3.702344 3.328787 3.167022
[876] 3.233250 3.211121 3.645521 3.676328 3.456062 3.030195 3.044540
[883] 3.149835 3.325926 3.505693 3.620552 3.435844 3.058426 3.093422
[890] 3.244030 3.457276 3.520615 3.608526 3.352375 3.194514 3.154424
[897] 3.481443 3.294907 3.617420 3.685294 3.376029 3.035029 3.199481
[904] 3.439017 3.431846 3.360215 3.697229 3.432649 3.112270 3.082067
[911] 3.487563 3.236537 3.572291 3.731750 3.196729 3.111934 3.271144
[918] 3.188647 3.421275 3.667640 3.564903 3.305136 3.101403 3.163161
[925] 3.337260 3.311754 3.283527 3.761778 3.300813 3.341632 3.321184
[932] 3.335257 3.328583 3.631038 3.575419 3.466868 3.181844 3.216694
[939] 3.293141 3.635986 3.454845 3.561578 3.268812 3.085647

Here the values of the regressor or external variable:

> data$xreg
  [1]  6  3  4  4  3  4 10  9  9  7  9  4  3  3  8  3  2  2  3  3  2  5
 [23]  5  8  2 -2  5  7  6  7 10 10  9  5  7  8  3  1  1  1  3  6  9  9
 [45] 10 11  9 11 15 12 11  9  7  8 10  7  6  7  9  9  9  8  8  7 12 13
 [67] 10 14 11 12 12 15 13  9 13 10  9  7  8  8 11 11 14 13 18 13 13 14
 [89] 13 13 12 15 15 16 21 12 13 14 12 13 12 13 10 10 10 13 14 12 13 13
[111]  8  8  9 11 13 15 11 14  9 11 11 14 14 10 11 14 19 15 15 16 17 21
[133] 17 14 13 14 18 21 19 23 22 23 19 21 20 18 20 22 22 19 17 16 14 17
[155] 16 17 20 20 17 21 23 21 21 26 28 27 26 30 22 21 19 20 21 19 15 16
[177] 20 19 21 21 21 25 27 26 23 24 23 17 18 19 19 20 23 23 23 20 19 20
[199] 16 17 16 20 18 18 18 18 18 18 19 20 17 18 20 18 19 17 14 17 19 20
[221] 20 21 21 19 21 18 18 18 17 20 20 19 21 23 24 24 24 13 17 22 18 16
[243] 18 17 17 16 16 15 16 16 16 15 14 12 14 14 13 15 16 15 16 19 16 17
[265] 17 17 17 15 16 15 14 14 15 13 15 15 15 14 15 14 17 17 16 19 13 15
[287] 16 13 13 12 15 17 16 15 12 13 11  9 11 13 11 13 12  8  9  9  7 11
[309] 10  8  6  6 10 11 11  7  8  6 12 13 14 10  6  5  6  9  5  4  2  5
[331]  5  8  8  7  9 10  3  3  1  1  3  7  5  4  4  7  5  8 10 11 10  9
[353]  9  6  2  3  7 13 10  7  9  9 10  7  4  4  4  6  8  6  6  5  5  9
[375]  5  6  8  5  3  6  9 11 10  8  5  9 11 10  8  5  6  5  3  3  2  2
[397]  3  6  4  7  5  5  3  5  7  7  8  8  9  7  5  3  2  3  2 -1 -4 -2
[419] -2  2  7  8  8  7  7  9 11  9  9 10  9 10 -2 -1  0  6  8  9  9  7
[441]  9 11 12  6  7  6  7  7  9 11 10  9 14 14 10  9 12 10  9 10 15 12
[463] 12 16 21 25 24 21 20 13 14 12 12 10  8  6  6 11 11 14 18 18 21 23
[485] 23 17 14 15 12 15 17 20 13 14 15 17 17 19 19 17 16 17 21 23 22 16
[507] 18 18 20 21 22 16 15 18 18 19 18 18 19 15 19 19 20 17 16 21 22 21
[529] 18 20 21 22 23 21 22 23 23 25 24 22 23 24 28 28 28 25 18 20 20 22
[551] 25 27 26 21 22 24 20 24 26 28 27 28 31 26 20 18 20 21 23 26 29 26
[573] 26 28 28 20 14 16 19 20 21 22 20 17 19 19 21 23 22 19 20 16 16 16
[595] 17 18 16 17 18 21 20 21 17 17 18 16 17 19 18 19 13 17 16 18 20 21
[617] 19 19 19 14 11 11 12 14 18 19 14 15 13 12 18 16 14 18 10 12 13 17
[639] 19 18 20 21 12 14 17 13 13 12 15 16 10 13 14 11  9  5  6  7  7 10
[661] 10  8 10 11 14 13 11 11 11 11 11 12 12 12 12  9  8  8  6  3  4  4
[683]  7  7  6  6  7 12 11  9 11 13 11  7 12 12  9 10  9  7  7  4  3  2
[705]  4  8  9  8  7  9 11  9 11  5  8  6  8 10  9  8  8  5  4  2  4  8
[727]  9  7  3  3  7  9 10  7  8  7  2  4  4  3  3  1  3  9  8  5  3  4
[749]  1  1  1  2  3  6  9  7  9  9  5  7  9  8  9 11  9 11  8  9 10 11
[771] 11 13 13 15 15 15 11  9 11 11 10 11  9  8 12  8  8  7  9 12 12 10
[793]  7 10 10 12  9 10 11  9 10 10 11 14 16  8 10  7  6  7 10  8 10 12
[815]  8  9  9  8  6  8 11 13 16 17 19 20 20 22 16 15 13 13 10 11 14 15
[837] 13 11  9  9 10 12 12 13 12 14 13 14 16 15 12 15 16 18 18 18 21 19
[859] 20 19 16 14 14 20 17 24 22 17 15 16 16 15 15 16 16 13 14 16 17 16
[881] 18 17 17 17 18 19 17 22 22 18 17 19 30 21 19 19 19 22 24 21 18 19
[903] 20 22 23 22 20 18 18 22 21 20 17 21 20 22 28 28 32 21 17 19 22 17
[925] 19 21 21 20 22 20 19 20 20 20 18 18 14 18 15 19 16 18 17 18

Here the future values of the external variable used to forecast the dependent one:

> data$future_xreg
 [1] 18 19 20 23 25 28 28 28 20 19 20 23 19 19 21 19 16 16 17 17 17 17
[23] 17 18 18 18

Answer 1

provides the structure .. this was based upon 200 observations AND the form of the model you posted AND the parameters you posted where p115099003 is the name of the causal series. The equation predicts the 201 value .. the next value and then then be used recursively. Compare the coefficients and lag structure with your equation .

Following is your representation ( which omits the lag effect of X implied by the AR structure in the model

Your approach only has 7 contributions while the output from AUTOBOX ( a piece of software that I have helped to develop is here which correctly uses 9 conytributions ... your 7 plus the two lags of X for lag periods 7 and 14.

The noise model acts like a common multiplier on all variables in the model .

It is very important to be able to express/write the model in layman's terms i.e. a regression format in order to be able to "sell/explain" the solution.

Please let me know if this correction clears up your problem. I not I will actually use your data to perform a clinical analysis.

If my answer has been of help upvote it if you can and accept it to bring it the attention of others. Let me know how this works for you.