I am trying to calculate industry and size adjusted three-year average cash effective tax rate(CashETR). From a paper it states that it should be three-year average cash effective tax rate of the firms in the same quintile of total assets and in the same Fama-French 48 industry less the firm's three-year average cash effective tax rate, where cash effective tax rate is calculated as the cash taxes(ct) scaled by pre-tax income(pi) minuses special items(spi).
So I have already calculated each firm's three-year cash effective tax rate:
sort gvkey fyear
rangestat (sum)ct (count)ct, interval(fyear -2 0) by (gvkey)
replace ct_sum = . if ct_count <3
gen npi = pi - spi
rangestat (sum)npi (count)npi, interval(fyear -2 0) by (gvkey)
replace npi_sum = . if npi_count <3
gen cash_etr = ct_sum / npi_sum
And I group firms based on their quintile of total assets and Fama-French 48 industry:
ffind sic, newvar(ffi) type(48)
egen quintile = xtile(at), n(5)
egen at_ffi_id = group(quintile ffi)
But then I am so confused about what to do next. Because I think each firm has different fiscal year. After I group them, I can not rangestat because their fiscal year are different, which caused an incoherent time series. I'd truly appreciate it if someone could help me figure this out! Thank you!
Below are some figures:
gvkey double(at fyear) float(ffi ct) double(pi spi) float(npi cash_etr)
"008852" 3039.9 1992 2 . 421.5 -11.3 432.8 .
"009411" 9989 1992 2 . 1174 222 952 .
"007146" 1130.879 1992 2 . 148.217 1.9 146.317 .
"010793" 2617.679 1992 2 . 261.039 0 261.039 .
"006375" 4015 1992 2 . 1070.4 36.1 1034.3 .
"005568" 6821.321 1992 2 . 715.781 -192.3 908.081 .
"002282" 360.826 1992 2 . 68.54 . . .
"011609" 711.372 1992 2 . 232.303 3.458 228.845 .
"002663" 4353.8 1992 2 . 799.3 0 799.3 .
"005071" 4650.8 1992 2 . 844 -67 911 .
"023798" 1490.1 1992 2 . 173.9 0 173.9 .
"002562" 5171.2 1992 2 . 744.5 0 744.5 .
"001722" 7524.53 1992 2 . 759.569 0 759.569 .
"005597" 2672.909 1992 2 . 400.988 0 400.988 .
"009777" 378.641 1993 2 . 52.723 0 52.723 .
"004809" 490.948 1993 2 . 60.032 0 60.032 .
"009113" 31295 1993 2 . 111 -730 841 .
"010793" 3253.504 1993 2 . 309.635 0 309.635 .
"003821" 1109.154 1993 2 . 118.313 0 118.313 .
"006574" 308.474 1993 2 . 50.707 0 50.707 .
"023798" 1170.5 1993 2 . 186.5 0 186.5 .
"013318" 329.087 1993 2 . -21.6 -33.933 12.333 .
"008852" 2815.9 1993 2 . 467.6 -20.5 488.1 .
"012825" 121.494 1993 2 . 13.128 0 13.128 .
"009433" 567.852 1993 2 . 21.383 0 21.383 .
"009411" 10862 1993 2 . 1082 0 1082 .
"004078" 322.275 1993 2 . 27.971 0 27.971 .
"007146" 1313.236 1993 2 . 160.18 0 160.18 .
"005848" 1538.907 1993 2 . 131.257 0 131.257 .
"028701" 4293.9 1993 2 . 501.5 0 501.5 .
"006573" 302.05 1993 2 . 74.319 0 74.319 .
"006375" 4237.1 1993 2 . 1034.1 1.6 1032.5 .
"011609" 815.324 1993 2 . 278.835 0 278.835 .
"002663" 4897.5 1993 2 . 528.6 -353 881.6 .
"005597" 2855.091 1993 2 . 510.875 80.642 430.233 .
"002562" 5060.8 1993 2 . 790.3 0 790.3 .
"001722" 8404.111 1993 2 . 746.009 0 746.009 .
"005071" 5198.3 1993 2 . 753.3 -146.9 900.2 .
"011748" 156.204 1993 2 . 7.637 . . .
"002282" 413.875 1993 2 . 76.514 0 76.514 .
"003362" 10721.8 1993 2 . 720 0 720 .
"005568" 6381.146 1993 2 . 922.386 127.001 795.385 .
"006102" 814.775 1993 2 . -24.904 -82.445 57.541 .
"013318" 336.645 1994 2 . 24.699 0 24.699 .
"002562" 5668 1994 2 180 615 -227 842 .
"011713" 86.814 1994 2 . 15.382 0 15.382 .
"005071" 3358.2 1994 2 162.7 404.6 -183.2 587.8 .
"011748" 164.357 1994 2 . 8.086 0 8.086 .
"003362" 10801 1994 2 123.2 825.9 0 825.9 .
"002282" 488.101 1994 2 32.172 86.869 0 86.869 .
"023798" 1141.1 1994 2 . 192.1 0 192.1 .
"009113" 31408 1994 2 625 1375 -65 1440 .
"004078" 362.026 1994 2 . 1.635 0 1.635 .
"005568" 8247.188 1994 2 455.075 938.007 . . .
"006140" 598.441 1994 2 . 39.652 0 39.652 .
"010271" 142.28 1994 2 . 11.319 -.349 11.668 .
"005848" 1865.463 1994 2 119.022 308.889 0 308.889 .
"009777" 421.017 1994 2 . 61.38 0 61.38 .
"006375" 4467.3 1994 2 452.7 1130 .6 1129.4 .
"003821" 1202.426 1994 2 57.763 136.388 0 136.388 .
"011609" 978.834 1994 2 109.257 353.279 38.102 315.177 .
"005597" 2890.981 1994 2 149.152 333.138 -106.105 439.243 .
"010793" 3668 1994 2 . 118.617 -213.924 332.541 .
"001722" 8746.853 1994 2 . 738.303 0 738.303 .
"006574" 296.996 1994 2 . 44.327 0 44.327 .
"006573" 355.445 1994 2 35.76 98.093 0 98.093 .
"007146" 1568.701 1994 2 -16.494 94.907 -70.445 165.352 .
"028701" 3791 1994 2 154.6 451.3 -97 548.3 .
"012825" 127.366 1994 2 . 13.673 0 13.673 .
"009433" 486.127 1994 2 . 8.606 0 8.606 .
"004809" 560.852 1994 2 18.597 47.24 . . .
"008852" 3043.3 1994 2 101.2 378.7 -108.6 487.3 .
"009411" 11665 1994 2 . 389 -732 1121 .
"005709" 1196.718 1994 2 . 191.07 0 191.07 .
"006102" 846.705 1994 2 . 71.739 26.24 45.499 .
"009774" 550.225 1994 2 . 50.438 0 50.438 .
"002663" 4992 1994 2 399.1 988 0 988 .
"007146" 1614.341 1995 2 69.397 151.221 3.904 147.317 .
"005709" 1223.86 1995 2 79.788 194.679 0 194.679 .
"006573" 379.904 1995 2 41.98 114.808 0 114.808 .
"004809" 655.921 1995 2 33.474 68.015 . . .
"002562" 7502 1995 2 372 877 -58 935 .
"001722" 9756.887 1995 2 498.999 1181.523 . . .
"009411" 12431 1995 2 503 1219 0 1219 .
"006375" 4414.6 1995 2 224.7 796 -421.8 1217.8 .
"005071" 3294.7 1995 2 326 755.8 0 755.8 .
"009433" 476.507 1995 2 . -6.078 -4.3 -1.778 .
"005597" 2830.623 1995 2 204.281 465.953 .151 465.802 .
"004078" 414.105 1995 2 . -2.511 0 -2.511 .
"009113" 31518 1995 2 458 1266 -257 1523 .
"010793" 4444.3 1995 2 170.154 343.6 0 343.6 .
"028701" 4567.2 1995 2 194 515.1 -40.5 555.6 .
"009774" 857.619 1995 2 -30.344 30.251 0 30.251 .
"006574" 256.46 1995 2 . -10.1 -35.897 25.797 .
"006140" 1486.46 1995 2 62.374 50.335 -9.5 59.835 .
"011713" 85.056 1995 2 . 10.735 -.508 11.243 .
"003821" 1222.24 1995 2 -64.757 -69.395 -150 80.605 .
"002663" 6315 1995 2 384 1059 0 1059 .
"011748" 186.76 1995 2 . 11.107 .899 10.208 .
"005568" 8623.691 1995 2 416.976 1023.661 0 1023.661 .
So I have already calculated each firm's three-year cash effective tax rate:
sort gvkey fyear
rangestat (sum)ct (count)ct, interval(fyear -2 0) by (gvkey)
replace ct_sum = . if ct_count <3
gen npi = pi - spi
rangestat (sum)npi (count)npi, interval(fyear -2 0) by (gvkey)
replace npi_sum = . if npi_count <3
gen cash_etr = ct_sum / npi_sum
And I group firms based on their quintile of total assets and Fama-French 48 industry:
ffind sic, newvar(ffi) type(48)
egen quintile = xtile(at), n(5)
egen at_ffi_id = group(quintile ffi)
But then I am so confused about what to do next. Because I think each firm has different fiscal year. After I group them, I can not rangestat because their fiscal year are different, which caused an incoherent time series. I'd truly appreciate it if someone could help me figure this out! Thank you!
Below are some figures:
gvkey double(at fyear) float(ffi ct) double(pi spi) float(npi cash_etr)
"008852" 3039.9 1992 2 . 421.5 -11.3 432.8 .
"009411" 9989 1992 2 . 1174 222 952 .
"007146" 1130.879 1992 2 . 148.217 1.9 146.317 .
"010793" 2617.679 1992 2 . 261.039 0 261.039 .
"006375" 4015 1992 2 . 1070.4 36.1 1034.3 .
"005568" 6821.321 1992 2 . 715.781 -192.3 908.081 .
"002282" 360.826 1992 2 . 68.54 . . .
"011609" 711.372 1992 2 . 232.303 3.458 228.845 .
"002663" 4353.8 1992 2 . 799.3 0 799.3 .
"005071" 4650.8 1992 2 . 844 -67 911 .
"023798" 1490.1 1992 2 . 173.9 0 173.9 .
"002562" 5171.2 1992 2 . 744.5 0 744.5 .
"001722" 7524.53 1992 2 . 759.569 0 759.569 .
"005597" 2672.909 1992 2 . 400.988 0 400.988 .
"009777" 378.641 1993 2 . 52.723 0 52.723 .
"004809" 490.948 1993 2 . 60.032 0 60.032 .
"009113" 31295 1993 2 . 111 -730 841 .
"010793" 3253.504 1993 2 . 309.635 0 309.635 .
"003821" 1109.154 1993 2 . 118.313 0 118.313 .
"006574" 308.474 1993 2 . 50.707 0 50.707 .
"023798" 1170.5 1993 2 . 186.5 0 186.5 .
"013318" 329.087 1993 2 . -21.6 -33.933 12.333 .
"008852" 2815.9 1993 2 . 467.6 -20.5 488.1 .
"012825" 121.494 1993 2 . 13.128 0 13.128 .
"009433" 567.852 1993 2 . 21.383 0 21.383 .
"009411" 10862 1993 2 . 1082 0 1082 .
"004078" 322.275 1993 2 . 27.971 0 27.971 .
"007146" 1313.236 1993 2 . 160.18 0 160.18 .
"005848" 1538.907 1993 2 . 131.257 0 131.257 .
"028701" 4293.9 1993 2 . 501.5 0 501.5 .
"006573" 302.05 1993 2 . 74.319 0 74.319 .
"006375" 4237.1 1993 2 . 1034.1 1.6 1032.5 .
"011609" 815.324 1993 2 . 278.835 0 278.835 .
"002663" 4897.5 1993 2 . 528.6 -353 881.6 .
"005597" 2855.091 1993 2 . 510.875 80.642 430.233 .
"002562" 5060.8 1993 2 . 790.3 0 790.3 .
"001722" 8404.111 1993 2 . 746.009 0 746.009 .
"005071" 5198.3 1993 2 . 753.3 -146.9 900.2 .
"011748" 156.204 1993 2 . 7.637 . . .
"002282" 413.875 1993 2 . 76.514 0 76.514 .
"003362" 10721.8 1993 2 . 720 0 720 .
"005568" 6381.146 1993 2 . 922.386 127.001 795.385 .
"006102" 814.775 1993 2 . -24.904 -82.445 57.541 .
"013318" 336.645 1994 2 . 24.699 0 24.699 .
"002562" 5668 1994 2 180 615 -227 842 .
"011713" 86.814 1994 2 . 15.382 0 15.382 .
"005071" 3358.2 1994 2 162.7 404.6 -183.2 587.8 .
"011748" 164.357 1994 2 . 8.086 0 8.086 .
"003362" 10801 1994 2 123.2 825.9 0 825.9 .
"002282" 488.101 1994 2 32.172 86.869 0 86.869 .
"023798" 1141.1 1994 2 . 192.1 0 192.1 .
"009113" 31408 1994 2 625 1375 -65 1440 .
"004078" 362.026 1994 2 . 1.635 0 1.635 .
"005568" 8247.188 1994 2 455.075 938.007 . . .
"006140" 598.441 1994 2 . 39.652 0 39.652 .
"010271" 142.28 1994 2 . 11.319 -.349 11.668 .
"005848" 1865.463 1994 2 119.022 308.889 0 308.889 .
"009777" 421.017 1994 2 . 61.38 0 61.38 .
"006375" 4467.3 1994 2 452.7 1130 .6 1129.4 .
"003821" 1202.426 1994 2 57.763 136.388 0 136.388 .
"011609" 978.834 1994 2 109.257 353.279 38.102 315.177 .
"005597" 2890.981 1994 2 149.152 333.138 -106.105 439.243 .
"010793" 3668 1994 2 . 118.617 -213.924 332.541 .
"001722" 8746.853 1994 2 . 738.303 0 738.303 .
"006574" 296.996 1994 2 . 44.327 0 44.327 .
"006573" 355.445 1994 2 35.76 98.093 0 98.093 .
"007146" 1568.701 1994 2 -16.494 94.907 -70.445 165.352 .
"028701" 3791 1994 2 154.6 451.3 -97 548.3 .
"012825" 127.366 1994 2 . 13.673 0 13.673 .
"009433" 486.127 1994 2 . 8.606 0 8.606 .
"004809" 560.852 1994 2 18.597 47.24 . . .
"008852" 3043.3 1994 2 101.2 378.7 -108.6 487.3 .
"009411" 11665 1994 2 . 389 -732 1121 .
"005709" 1196.718 1994 2 . 191.07 0 191.07 .
"006102" 846.705 1994 2 . 71.739 26.24 45.499 .
"009774" 550.225 1994 2 . 50.438 0 50.438 .
"002663" 4992 1994 2 399.1 988 0 988 .
"007146" 1614.341 1995 2 69.397 151.221 3.904 147.317 .
"005709" 1223.86 1995 2 79.788 194.679 0 194.679 .
"006573" 379.904 1995 2 41.98 114.808 0 114.808 .
"004809" 655.921 1995 2 33.474 68.015 . . .
"002562" 7502 1995 2 372 877 -58 935 .
"001722" 9756.887 1995 2 498.999 1181.523 . . .
"009411" 12431 1995 2 503 1219 0 1219 .
"006375" 4414.6 1995 2 224.7 796 -421.8 1217.8 .
"005071" 3294.7 1995 2 326 755.8 0 755.8 .
"009433" 476.507 1995 2 . -6.078 -4.3 -1.778 .
"005597" 2830.623 1995 2 204.281 465.953 .151 465.802 .
"004078" 414.105 1995 2 . -2.511 0 -2.511 .
"009113" 31518 1995 2 458 1266 -257 1523 .
"010793" 4444.3 1995 2 170.154 343.6 0 343.6 .
"028701" 4567.2 1995 2 194 515.1 -40.5 555.6 .
"009774" 857.619 1995 2 -30.344 30.251 0 30.251 .
"006574" 256.46 1995 2 . -10.1 -35.897 25.797 .
"006140" 1486.46 1995 2 62.374 50.335 -9.5 59.835 .
"011713" 85.056 1995 2 . 10.735 -.508 11.243 .
"003821" 1222.24 1995 2 -64.757 -69.395 -150 80.605 .
"002663" 6315 1995 2 384 1059 0 1059 .
"011748" 186.76 1995 2 . 11.107 .899 10.208 .
"005568" 8623.691 1995 2 416.976 1023.661 0 1023.661 .
