-
Notifications
You must be signed in to change notification settings - Fork 0
/
FiscalFutures_FY2023_newdataupdate.Rmd
4960 lines (3756 loc) · 223 KB
/
FiscalFutures_FY2023_newdataupdate.Rmd
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
---
title: "FY2023 - Preparing New Fiscal Data for Fiscal Futures Project"
author: "Alea Wilbur-Mujtaba"
date: "`r format(Sys.time(), '%d %B, %Y')`"
output:
html_document:
code_folding: hide
toc: yes
toc_float: yes
theme: simplex
df_print: paged
---
```{r setup, warning = FALSE, message=FALSE}
library(haven)
library(formatR)
library(lubridate)
library(smooth)
library(forecast)
library(scales)
library(kableExtra)
library(ggplot2)
library(readxl)
library(tidyverse)
library(data.table)
library(quantmod)
library(geofacet)
library(janitor)
library(tidyverse)
knitr::opts_chunk$set(warning = FALSE, message = FALSE, error = TRUE)
```
# Data Creation and Cleaning
- Do the FOIA request. In a week or so, they send the expenditure and revenue data as excel files.\
- Checks whether there are any new agencies, re-used funds etc. Create a list of funds, agencies, fund names, etc. for the new year and compare it to the immediate prior year to identify new funds.\
- Update the funds_ab_in file which shows the use of funds. Use criteria to determine if the new funds should be in or out of the all-funds frame.\
- Change the variable names to be consistent with other files such as AGENCYNAME --\> agency_name
- Once variable names are shared over all years of data, combine past years with newest year. All revenue files are in a `revenue` folder that I reference when I set the working directory. When adding new fiscal years, put the the newest year of data for revenue and expenditures in their respective folders.
Normally, when your receive the new fiscal year files from the Comptrollers office, you will need to change the variable names so that they are consistent with past years. This is an example of reading in the new file and changing the variable names. They seem to change almost every year in the file received from the FOIA so if the code breaks here, check to make sure that the columns you are trying to rename exist and are spelled correctly! Once variables are the same, you will want to save the file as a csv file in its Revenue/Expenditure file and bind all past years and the current year together in one dataframe.
```{r rename-variables, eval=FALSE}
#
# # Preliminary data from August
# rev_fy23 <- read_xlsx("Fis_Fut_Rev_2023.xlsx") %>%
# rename(fy = 'FISCAL YEAR',
# fund = 'FUND',
# fund_name = 'FUND NAME',
# agency = 'AGENCY',
# agency_name = 'AGENCY NAME',
# source = 'REVENUE SOURCE',
# source_name = 'REV SRC NAME',
# receipts = 'AMOUNT'
# ) %>% mutate(data_source = "rev IOC 07.22.2023")
#
# exp_fy23 <- read_xlsx("Fis_Fut_Exp_2023.xlsx") %>%
# rename(fy = 'FISCAL YEAR',
# fund = 'FUND',
# fund_name = 'FUND NAME',
# agency = 'AGENCY',
# agency_name = 'AGENCY NAME',
# appr_org = 'DIVISION',
# org_name = 'DIVISION NAME',
# obj_seq_type = 'APPROPRIATION',
# wh_approp_name = 'APPROP NAME',
# appn_net_xfer = 'NET OF TRANS AMT',
# expenditure = 'EXPENDED YTD') %>%
# mutate( data_source = "exp IOC 07.11.2023"
# )
## New files had different variable names!!
## Had to update code slightly ##
rev_fy23 <- read_xlsx("Fis_Fut_Rev_2023_final.xlsx") %>%
rename(fy = 'FISCAL YEAR',
fund = 'FUND',
fund_name = 'FUND NAME',
agency = 'AGENCY',
agency_name = 'AGENCY NAME',
source = 'REVENUE SOURCE',
source_name = 'REVENUE SOURCE NAME',
receipts = 'REVENUE AMOUNT'
) %>% mutate(data_source = "rev IOC 011.13.2023")
exp_fy23 <- read_xlsx("Fis_Fut_Exp_2023_final.xlsx") %>%
rename(fy = 'FISCAL YEAR',
fund = 'FUND',
fund_name = 'FUND NAME',
agency = 'AGENCY',
agency_name = 'AGENCY NAME',
appr_org = 'ORGANIZTION', ## Yes, it was spelled wrong in the file we received...
org_name = 'ORGANIZATION NAME',
obj_seq_type = 'APPROPRIATION',
wh_approp_name = 'APPROPRIATION NAME',
appn_net_xfer = 'APPROPRIATION NET OF TRANSFERS',
expenditure = 'EXPENDITURES') %>%
mutate( data_source = "exp IOC 011.14.2023"
)
```
The code chunk below takes the .dta files for all fiscal years before FY 2022 and binds them together. Variable names were manually changed by past researchers so that they were consistent across years.
For FY 2023 and after, .dta files can be avoided entirely and .csv or excel files will be used. All files before this year had been saved and passed on as .dta files for Stata code before the transition to R in Fall 2022. For years after fy22: add line of code to bind csv files after binding the dta files together. Variable names must identical to merge files together.
```{r create-rev-csv-FY23, eval=FALSE}
# years after fy22: add line of code to bind csv files after binding the dta files.
setwd("C:/Users/aleaw/OneDrive/Documents/PhD Fall 2021 - Spring 2022/Merriman RA/Fiscal Futures IGPA/Fiscal Futures FY2023/revenue")
## combine dta files from past years
allrevfiles23 = list.files(path = "C:/Users/aleaw/OneDrive/Documents/PhD Fall 2021 - Spring 2022/Merriman RA/Fiscal Futures IGPA/Fiscal Futures FY2023/revenue",
#"C:/Users/aleaw/OneDrive/Documents/PhD Fall 2021 - Spring 2022/Merriman RA/Fiscal Futures IGPA/Fiscal Futures FY2022/revenue",
pattern = ".dta") %>%
lapply(read_dta) %>%
bind_rows
# add in excel file for current year
allrevfiles23 <- allrevfiles23 %>%
select(fy, fund, fund_name, agency, agency_name, source, source_name, receipts, data_source) %>%
rbind(rev_fy23)
#Fy21: 62294 observations, 13 variables
#FY22: 65094 obs, 13 vars
#FY23: 57822 obs
#setwd("C:/Users/aleaw/OneDrive/Documents/PhD Fall 2021 - Spring 2022/Merriman RA/Fiscal Futures IGPA/Fiscal Futures FY2022/Replication-Files/expenditures")
setwd("C:/Users/aleaw/OneDrive/Documents/PhD Fall 2021 - Spring 2022/Merriman RA/Fiscal Futures IGPA/Fiscal Futures FY2023/expenditures")
allexpfiles23 = list.files(path = "C:/Users/aleaw/OneDrive/Documents/PhD Fall 2021 - Spring 2022/Merriman RA/Fiscal Futures IGPA/Fiscal Futures FY2023/expenditures",
# path = "C:/Users/aleaw/OneDrive/Documents/PhD Fall 2021 - Spring 2022/Merriman RA/Fiscal Futures IGPA/Fiscal Futures FY2022/Replication-Files/expenditures",
pattern = ".dta") %>% lapply(read_dta) %>% bind_rows
# add in excel file for current year
allexpfiles23 <- allexpfiles23 %>%
select(fy, fund, fund_name, agency, agency_name, appr_org, org_name, obj_seq_type, wh_approp_name, appn_net_xfer, expenditure, data_source) %>%
rbind(exp_fy23)
# fy21 213372 observations, 20 variables
# fy22 225587 obs, 21 vars.
# fy23 238089 obs
```
```{r save-combinedfiles, eval=FALSE}
write_csv(allrevfiles23, "data/allrevfiles_Nov132023.csv")
write_csv(allexpfiles23, "data/allexpfiles_Nov132023.csv")
```
Code below reads in the csv files created in chunks above (allrevfiles.csv and allrexpfiles.csv). These files contain all years of data combined into one file BEFORE any recoding is done. Do not use this file for summing categories because it is just an inbetween step before recoding revenue and expenditure categories.
```{r readCSVs}
# combined in past chunks called create-rev-csv and create-exp-csv
allrevfiles23 <- read_csv("./data/allrevfiles_Nov132023.csv") #combined but not recoded
allexpfiles23 <- read_csv("./data/allexpfiles_Nov132023.csv") #combined but not recoded
```
## Inspecting new FY 2023 data files
Data files for closed years have been obtained from IOC. The numbers of funds, agencies, organizations, and revenue sources below were found by using pivot tables in the codebook files in the FY2022 Box folder. This could also be done using R and grouping new files by fund, agency, source number, source names, etc..
Revenue [[File:\\\\](File:){.uri}](%5BFile:\%5D(File:)%7B.uri%7D){.uri} - 674 Fund Numbers\
- 80 Agencies\
- 1178 Revenue source number & name combos\
- Check \# of numbers vs number of names soon
Expenditure [[File:\\\\](File:){.uri}](%5BFile:\%5D(File:)%7B.uri%7D){.uri} - 708 Fund Numbers\
- 107 Agencies\
- 98 Organization Numbers\
- 313 Organization names
### Finding new agencies and funds
General steps:
1. Identify new and reused funds for newest fiscal year.\
2. Recode funds to take into account different fund numbers/names over the years. See [Recoding New and Reused Funds] for code chunk that does this.\
3. Update fund_ab_in_2022.xlsx with any changes from previous fiscal year.
**New Agencies, Funds, and Organizations from Expenditure files:**
```{r}
# From Expenditure Data #####
# agencies referenced in any year before 2020:
agencies_past <- allexpfiles23 %>%
filter(fy < 2023) %>%
mutate(agency == as.character(agency)) %>%
group_by(agency, agency_name) %>% unique() %>%
summarize(expenditure = sum(expenditure, na.rm = TRUE)) %>%
drop_na() %>%
arrange(agency)
# agencies_past # 146 agencies ever
# agencies in 2022 data:
agencies23 <- allexpfiles23 %>%
filter(fy == 2023) %>%
mutate(agency == as.character(agency)) %>%
group_by(agency, agency_name) %>%
summarize(expenditure = sum(expenditure, na.rm = TRUE))
# agencies23 # 107 agencies this year
# no new agencies in FY23
anti_join(agencies23, agencies_past,
by = c("agency", "agency_name")) %>%
arrange(agency)
funds_past <- allexpfiles23 %>%
filter(fy < 2023) %>%
mutate(fund == as.character(fund)) %>%
group_by(fund, fund_name) %>%
summarize(count = n(), Expenditure = sum(expenditure, na.rm = TRUE)) %>%
drop_na()
funds23 <- allexpfiles23 %>%
filter(fy == 2023) %>%
mutate(fund == as.character(fund)) %>%
group_by(fund, fund_name) %>%
summarize(count = n(), Expenditure = sum(expenditure, na.rm = TRUE)) %>%
unique()
# 26 funds were in FY23 data that were not in past data:
anti_join(funds23, funds_past,
by = c("fund", "fund_name"
)) %>%
arrange(fund)
# orgs_past in the past = 920 org groups before 2023
orgs_past <- allexpfiles23 %>%
filter(fy < 2023) %>%
mutate(appr_org == as.character(appr_org)) %>%
group_by(appr_org, org_name) %>% unique() %>%
summarize(Expenditure = sum(expenditure, na.rm = TRUE)) %>%
drop_na()
# orgs22 # 399 org groups this year
orgs23 <- allexpfiles23 %>%
filter(fy == 2023) %>%
mutate(appr_org = as.character(appr_org)) %>%
group_by(appr_org, org_name) %>%
summarize(Expenditure = sum(expenditure, na.rm = TRUE))
# 3 org number and org name combos are new for FY2022:
anti_join(orgs23, orgs_past,
by = c("appr_org", "org_name")) %>%
arrange(appr_org)
```
26 funds identified from the expenditure file as having something changed compared to funds that existed in previous years. They are either new funds, reused funds, or have very minor typos or changes in their name. Some are also identified in the revenue section below:
**New Revenue Funds, Sources, and New Agencies:**
```{r }
#### From Revenue Data ####
# agencies_past # 108 agencies ever
agencies_past <- allrevfiles23 %>%
filter(fy < 2023) %>%
mutate(agency == as.character(agency)) %>%
group_by(agency, agency_name) %>%
unique() %>%
summarize(Receipts = sum(receipts, na.rm = TRUE)) %>%
drop_na()
# agencies23 # 80 agencies this year
agencies23 <- allrevfiles23 %>%
filter(fy == 2023) %>%
mutate(agency == as.character(agency)) %>%
group_by(agency, agency_name) %>%
summarize(Receipts = sum(receipts, na.rm = TRUE))
# 2 new agencies in revenue data this year
anti_join(agencies23, agencies_past, by = c("agency", "agency_name")) %>%
arrange(agency)
funds_past <- allrevfiles23 %>%
filter(fy < 2023) %>%
mutate(fund == as.character(fund)) %>%
group_by(fund, fund_name) %>%
summarize(count = n(), Receipts = sum(receipts, na.rm = TRUE)) %>%
drop_na()
funds23 <- allrevfiles23 %>%
filter(fy == 2023) %>%
mutate(fund == as.character(fund)) %>%
group_by(fund, fund_name) %>%
summarize(count = n(), Receipts = sum(receipts, na.rm = TRUE)) %>%
unique() %>%
drop_na()
# 13 revenue funds were in FY23 revenue data that were not in past data
# some could be small fund name changes
anti_join(funds23, funds_past, by = c("fund", "fund_name")) %>%
arrange(fund)
sources_past <- allrevfiles23 %>%
filter(fy < 2023) %>%
mutate(source == as.character(source)) %>%
group_by(source, source_name) %>%
summarize(count = n(), Receipts = sum(receipts, na.rm = TRUE)) %>%
drop_na()
sources23 <- allrevfiles23 %>%
filter(fy == 2023) %>%
mutate(source == as.character(source)) %>%
group_by(source, source_name) %>%
summarize(count = n(), Receipts = sum(receipts, na.rm = TRUE)) %>%
unique()
# 16 revenue sources were in FY22 data that were not in past data
# some could be small source name changes:
anti_join(sources23, sources_past, by = c("source", "source_name")) %>%
arrange(source)
```
Sources 2737 through 2756 were not found in the IOC_source file so I added them to `ioc_source_updated22_AWM.xlsx`. They do NOT have a rev_type in the file so each source must be searched on the Comptrollers website. We could ask for a variable key from the comptroller for a list of all revenue sources and their revenue type in the future to make our lives easier. I had to look at the sources within each revenue type to find the ones missing and then manually update our ioc_source file. Most of these were considered type 39, "Licenses, Fees, & Registrations". These were finally updated on January 20th 2023 after totals were created for the FY22 paper.
FY23: Sources 2751 to 2769. Only 2758,2761, 2769 has significant amount of money (Fed monies via IDVA, US DA, Opioid Settlement).
> Add 16 sources to list of sources
### Recoding New and Reused Funds
New funds will need to be manually added to the funds_ab_in excel file and determined if they should or should not be included in Fiscal Future calculations.
For funds that were reused once, a 9 replaces the 0 as the first digit. If reused twice, then the first two values are 10.
- Ex. 0350 --\> 9350 because its use changed.\
- Ex. 0367 becomes 10367 because its use has changed twice now. There was fund 0367 originally, then its use changed and it was recoded as 9367, and now it changed again so it is a 10367.\
- Excel file also has alternative ways to name funds (e.g. 0397-A and 0397-B) and variables for the year that the fund stopped being used. These have not been updated consistently over the years but it is useful information when trying to find any coding mistakes from the past.
New or reused funds revenue file recoding:
```{r recode-rev-funds}
# if first character is a 0, replace with a 9 if its purpose has changed
rev_1998_2023 <- allrevfiles23 %>%
mutate(fund = ifelse(fy < 2002 & fund %in% c("0730", "0241", "0350", "0367", "0381", "0382", "0526", "0603", "0734", "0913", "0379"), str_replace(fund, "0","9"), fund)) %>%
mutate(fund = ifelse (fy < 2008 & fund %in% c("0027", "0033", "0037", "0058", "0062", "0066", "0075", "0083", "0116", "0119", "0120", "0122", "0148", "0149", "0157", "0158", "0166", "0194", "0201", "0209", "0211", "0217", "0223", "0231", "0234", "0253", "0320", "0503", "0505", "0512", "0516", "0531", "0532", "0533", "0547", "0563", "0579", "0591", "0606", "0616", "0624", "0659", "0662", "0665", "0676", "0710",
"0068", "0076", "0115", "0119", "0168", "0182", "0199", "0241", "0307", "0506", "0509", "0513"), str_replace(fund, "0","9"), fund)) %>%
mutate(fund = ifelse(fy < 2016 & fund %in% c("0263", "0399", "0409"), str_replace(fund, "0","9"), fund)) %>%
mutate(fund = ifelse(fy < 2017 & fund == "0364", str_replace(fund, "0","9"), fund)) %>%
mutate(fund = ifelse(fy < 2018 & fund %in% c("0818", "0767", "0671", "0593", "0578"), str_replace(fund, "0","9"), fund)) %>%
mutate(fund = ifelse(fy>1999 & fy < 2018 & fund == "0231", "10231", fund) ) %>%
mutate(fund = ifelse(fy < 2019 & fund %in% c("0161", "0489", "0500", "0612", "0893", "0766"), str_replace(fund, "0","9"), fund)) %>%
mutate(fund = ifelse(fy < 2020 & fund %in% c("0254", "0304", "0324", "0610", "0887", "0908", "0939", "0968"), str_replace(fund, "0","9"), fund)) %>%
mutate(fund = ifelse(fy < 2021 & fund %in% c("0255", "0325", "0348", "0967", "0972"), str_replace(fund, "0","9"), fund) ) %>%
#2022 changes
mutate(fund = ifelse(fy < 2022 & fund %in% c("0110","0165","0351", "0392", "0393", "0422", "0544", "0628", "0634", "0656", "0672", "0683", "0723", "0734", "0742", "0743"), str_replace(fund, "0","9"), as.character(fund))) %>% # replaces first 0 it finds with a 9
mutate(fund = ifelse(fy < 2022 & fund == "0367", "10367", as.character(fund)) # fund reused for 3rd time
) %>%
# 2023 fund changes
mutate(fund = ifelse(fy < 2023 & fund %in% c("0099","0210","0246", "0250", "0264", "0268", "0300", "0311", "0403", "0448","0645", "0727", "0729", "0791"), str_replace(fund,"0", "9"), as.character(fund))) %>%
mutate(fund = ifelse(fy < 2023 & fund == "0734" , "10734", as.character(fund)),
fund = ifelse(fy<2023 & fund == "0820", "10820", as.character(fund) ) # fund reused for 3rd time
)
```
For 2022, new funds included The Essential Government Services fund, Electric Vehicle Rebate, Budget Stabilization fund, and more. New funds were added to the funds_ab_in.xlsx file and recoded funds were addressed in the code chunks below and updated in the same excel file.
For 2023:
- 5 New Funds: 0180, 0221, 0385, 0426, 0442
- Reused Funds: 0099, 0210, 0246, 0250, 0264, 0268, 0300, 0311, 0403, 0426, 0448, 0645, 0727, 0729, 0734, 0791, 0820
- fund 0734 and fund 0820 were used for the 3rd time and is treated slightly different (coded to 10734 instead of 9734)
> Funds_ab_in_2023.xlsx still needs the `in_ff` variable and `fund_category` variables filled in!
- 0448 was state projects but now is Medicaid Tech Assistance Center, is that still a type of state project or give it a new fund number? Probably depends on the ioc fund description. Did recode it for now as of Nov 17 2023.
Minor Name Changes (so no change to funds_ab_in file): 0156, 0099, and fund 0083 is just a slight name change from our records in the funds_ab_in.xlsx file (Gang Crime Witness Protection vs Violent Crime Witness Protection. Changed in funds_ab_in on 11.17.2023)
Expenditure recoding:
```{r recode-exp-funds}
# if first character is a 0, replace with a 9
exp_1998_2023 <- allexpfiles23 %>%
mutate(fund = ifelse(fy < 2002 & fund %in% c("0730", "0241", "0350", "0367", "0381", "0382", "0526", "0603", "0734", "0913", "0379"), str_replace(fund, "0","9"), fund)) %>%
mutate(fund = ifelse(fy < 2008 & fund %in% c("0027", "0033", "0037", "0058", "0062", "0066", "0075", "0083", "0116", "0119", "0120", "0122", "0148", "0149", "0157", "0158", "0166", "0194", "0201", "0209", "0211", "0217", "0223", "0231", "0234", "0253", "0320", "0503", "0505", "0512", "0516", "0531", "0532", "0533", "0547", "0563", "0579", "0591", "0606", "0616", "0624", "0659", "0662", "0665", "0676", "0710",
"0068", "0076", "0115", "0119", "0168", "0182", "0199", "0241", "0307", "0506", "0509", "0513"), str_replace(fund, "0","9"), fund)) %>%
mutate(fund = ifelse(fy < 2016 & fund %in% c("0263", "0399", "0409"), str_replace(fund, "0","9"), fund)) %>%
mutate(fund = ifelse(fy < 2017 & fund == "0364", str_replace(fund, "0","9"), fund)) %>%
mutate(fund = ifelse(fy < 2018 & fund %in% c("0818", "0767", "0671", "0593", "0578"), str_replace(fund, "0","9"), fund)) %>%
mutate(fund = ifelse(fy>1999 & fy < 2018 & fund == "0231", "10231", fund) ) %>%
mutate(fund = ifelse(fy < 2019 & fund %in% c("0161", "0489", "0500", "0612", "0893", "0766"), str_replace(fund, "0","9"), fund)) %>%
mutate(fund = ifelse(fy < 2020 & fund %in% c("0254", "0304", "0324", "0610", "0887", "0908", "0939", "0968"), str_replace(fund, "0","9"), fund)) %>%
mutate(fund = ifelse(fy < 2021 & fund %in% c("0255", "0325", "0348", "0967", "0972"), str_replace(fund, "0","9"), fund)) %>%
#2022 changes
mutate(fund = ifelse(fy < 2022 & fund %in% c("0110","0165","0351", "0392", "0393", "0422", "0544", "0628", "0634", "0656", "0672", "0683","0723", "0742", "0743"), str_replace(fund, "0","9"), as.character(fund))) %>% # replaces first 0 it finds with a 9
mutate(fund = ifelse(fy < 2022 & fund == "0367", "10367", as.character(fund)) ) %>% # fund reused for 3rd time
# 2023 fund changes
mutate(fund = ifelse(fy < 2023 & fund %in% c("0099","0210","0246", "0250", "0264", "0268", "0300", "0311", "0403", "0448","0645", "0727", "0729", "0791"), str_replace(fund,"0", "9"), as.character(fund)) ) %>%
mutate(fund = ifelse(fy < 2023 & fund == "0734" , "10734", as.character(fund)),
fund = ifelse(fy<2023 & fund == "0820", "10820", as.character(fund) ) # fund reused for 3rd time
)
```
The `funds_ab_in.xlsx` file contains all funds that have existed since 1998, if they still exist, indicates if fund numbers have been reused for varying purposes, and is updated yearly with new fund numbers used by the IOC.
```{r eval = FALSE}
# new chunk 2/23/2023 AWM to create file with all years that have the fund names and fund cat variables filled in. Currently some years have NAs. Only doing this for the Github folder so people have a clean file with as little missing data as possible.
# before funds are recoded!
library(fuzzyjoin)
funds_ab_in_2023 = readxl::read_excel("C:/Users/aleaw/OneDrive/Documents/PhD Fall 2021 - Spring 2022/Merriman RA/Fiscal Futures IGPA/Fiscal Futures FY2023/Fiscal-Future-Topics/data/funds_ab_in_2023.xlsx")
funds_ab_in_2023 <- funds_ab_in_2023 %>% casefold(upper=TRUE)
#exp_temp <- allexpfiles22 %>%
# janitor::clean_names() %>%
# select(-c(fund_cat, fund_cat_name )) %>%
#arrange(fund, fy) %>%
# filter(expenditure != 0) %>% # keeps everything that is not zero
# join funds_ab_in_2021 to exp_temp
#exp_temp <- regex_left_join(allexpfiles23, funds_ab_in_2023, by = "fund", ignore_case = TRUE) # matches most recent fund number
#table(exp_temp$fund_cat_name)
```
```{r create-exp_temp}
funds_ab_in_2023 = readxl::read_excel("C:/Users/aleaw/OneDrive/Documents/PhD Fall 2021 - Spring 2022/Merriman RA/Fiscal Futures IGPA/Fiscal Futures FY2023/Fiscal-Future-Topics/data/funds_ab_in_2023.xlsx")
exp_temp <- exp_1998_2023 %>%
arrange(fund, fy) %>%
filter(expenditure != 0) %>% # keeps everything that is not zero
# join funds_ab_in_2021 to exp_temp
left_join(funds_ab_in_2023, by = "fund") %>% # matches most recent fund number
select(-c(fund_ab, fund_ioc, fund_re, fund_name_ab, a_end, fund_category))
# Agency == 799 for Statutory transfers
# Object == 1993 is for Interfund cash transfers
exp_temp <- exp_temp %>%
mutate(transfer = ifelse(org_name == "TRANSFERS", 1, 0),
trans_agency = ifelse(org_name == "TRANSFERS", str_sub(obj_seq_type,1,3), NA),
trans_type = ifelse(org_name == "TRANSFERS", str_sub(obj_seq_type,4,9), NA)
) %>%
# these come from ioc_source file after merging
mutate(
object = ifelse(transfer == 0, as.character(str_sub(obj_seq_type, 1, 4)), NA_character_),
sequence = ifelse(transfer == 0, as.character(str_sub(obj_seq_type, 5,6)), NA_character_),
type = ifelse(transfer == 0, str_sub(obj_seq_type, 7,8), NA_character_)
)
# fund_cat = FIND_COLUMN, #create fund_cat column
# fund_cat_name = FIND_NAME) # create fund_cat_name column
```
- the initial combined years of data are saved as dataframes named `exp_1998_2022` and `rev_1998_2022`. These are then saved as exp_temp and rev_temp while recoding variables. This is BEFORE category groups are created and cleaned below. `exp_temp` and `rev_temp` are only temporary files; do not use for aggregating totals yet!
```{r remove-all_obs_df, include=FALSE, eval=FALSE}
#include = FALSE in the chunk settings run the code but do not include the chunk in the html output
# remove from computer memory to free up space (in case your computer needs it)
rm(allexpfiles23)
#rm(allrevfiles23)
rm(exp_fy23)
rm(revenue_fy23)
```
Update Agencies: Some agencies have merged with others or changed names over time.
```{r agencies-exp}
# recodes old agency numbers to consistent agency number
exp_temp <- exp_temp %>%
mutate(agency = case_when(
(agency=="438"| agency=="475" |agency == "505") ~ "440",
# financial institution & professional regulation &
# banks and real estate --> coded as financial and professional reg
agency == "473" ~ "588", # nuclear safety moved into IEMA
(agency =="531" | agency =="577") ~ "532", # coded as EPA
(agency =="556" | agency == "538") ~ "406", # coded as agriculture
agency == "560" ~ "592", # IL finance authority (fire trucks and agriculture stuff)to state fire marshal
agency == "570" & fund == "0011" ~ "494", # city of Chicago road fund to transportation
TRUE ~ (as.character(agency))))
```
For aggregating revenue, use the rev_1998_2022 dataframe, join the funds_ab_in_2022 file to it, and then join the ioc_source_type file to the dataset. Remember: You need to update the funds_ab_in and ioc_source_type file every year!
```{r create-rev-temp, warning = FALSE, message=FALSE}
# fund info to revenue for all years
rev_temp <- inner_join(rev_1998_2023, funds_ab_in_2023, by = "fund") %>% arrange(source)
# need to update the ioc_source_type file every year!
ioc_source_type <- readxl::read_xlsx("C:/Users/aleaw/OneDrive/Documents/PhD Fall 2021 - Spring 2022/Merriman RA/Fiscal Futures IGPA/Fiscal Futures FY2023/Fiscal-Future-Topics/data/ioc_source_updated23_AWM.xlsx")
rev_temp <- left_join(rev_temp, ioc_source_type, by = "source")
# automatically used source, source name does not match for the join to work using source_name
# recodes old agency numbers to consistent agency number
rev_temp <- rev_temp %>%
mutate(agency = case_when(
(agency=="438"| agency=="475" |agency == "505") ~ "440",
# financial institution & professional regulation &
# banks and real estate --> coded as financial and professional reg
agency == "473" ~ "588", # nuclear safety moved into IEMA
(agency =="531" | agency =="577") ~ "532", # coded as EPA
(agency =="556" | agency == "538") ~ "406", # coded as agriculture
agency == "560" ~ "592", # IL finance authority (fire trucks and agriculture stuff)to state fire marshal
agency == "570" & fund == "0011" ~ "494", # city of Chicago road fund to transportation
TRUE ~ (as.character(agency))))
```
# Overall Fiscal Health & Complex Fiscal Topics
> Click the different tabs to see each topic, how it was coded, and exploratory graphs!
## Pension Discusion
**For yearly expenditure calculations, the state contributions to the pension funds (object = 4431) should be the expenditure included for pensions. If trying to look at the bigger fiscal health picture and include unfunded liabilities and in/out flows, then items like purchase of investments and POB spikes in trends that occurred from policy changes should be analyzed and discussed in a separate section. Again, State contributions TO the pension funds are the expenditures BUT an additional graph/discussion on the employer contributions, employee contributions, and benefits paid out should be included and considered for additional context on Illinois' situation.**
Pension expenditures referenced in the analysis are based on state payments to the following pension systems:
• Teachers Retirement System (TRS)\
- New POB bond in 2019: Accelerated Bond Fund paid benefits in advance as lump sum
• State Employee Retirement System (SERS)\
• State University Retirement System (SURS)\
• Judges Retirement System (JRS)\
• General Assembly Retirement System (GARS)
**Additional context:**\
For the \$10 billion in 2004, they borrowed money and invested it in pension portfolio and hoped that the returns would be greater than the interest on the debt. If returns\>interest, then they increased the pension funds and it was a good idea. Otherwise a short term band-aid causes even more problems later. This added a significant amount to the unfunded pension liabilities. In 2010 and 2011, POBs served as a type of general borrowing for the state by borrowing against what was owed to the pension systems and using that revenue that should have funded pensions to instead subsidize the cost of providing core services. Illinois borrowed money (POBs) and used it to pay for government services. A temporary way to fill a budget gap for that 2010 that then costs more in the long run due to increased unfunded liabilities and interest on the borrowed money. - "Basket Case" by Dye 2015
In 2019 lawmakers offered a pension buyout plan where members could opt-out of their future benefits for a lump sum. However, few people participated in the buyout plan and very little savings have occurred so far. The buyout plan has been extended to 2026 in hopes that more people participate in it. Description of Pension Obligation Acceleration Bond at this [link](https://www.ilga.gov/legislation/ilcs/documents/003003300K7.7.htm). Proceeds of bonds go into pension obligation acceleration fund (which are not included as a revenue source) and the fund is only used to make accelerated pension benefit payments. The pension stabilization fund (0319) is money put into the pension funds to help pay for unfunded liabilities from past poor budgeting decisions.
**Data coding details**
- State pension contributions are largely captured with object=4431. **(These are the State expenditures included in analysis)**
- includes 8 billion payment in 2004 that creates large peak in expenditure graphs\
- Object 4431 does not capture recent pension stabilization fund which is fund = 0319, object = 1900 and has \$300 million investment in FY2022.
- Fund=0475 is the Municipal Retirement Fund - Not included because state just helps collect and disperse local pension funds. IMRF is most funded pension fund in Illinois. Fund ends in 2015. All were considered purchase of investments.
- IOC objects 1160-1165 are for all retirement expenditures for employers. These are not included in the analysis.
- Some expenditures with object=4430 (benefits paid to retirees) were paid for with Pension obligation bond funds (fund == 0825).
- In past years, some POB funded expenditures were moved to revenue side. Code logic was unclear. We are no longer doing this as of FY2021.
- Other types of pension expenditures to consider when looking at pension funds: Pension obligation acceleration bond, state pension obligation bond reimbursements, pension pickup, accelerated pension buy-out (bond financed funds)
Other items to be aware of that may contain useful pension context:
- object = 1298 is for Purchase of Investments and is excluded from analysis. In past analyses, there were a couple of exceptions during 2010 and 2011.
- Purchase of Investments captures the pension obligation bonds issued in 2010-2011.\
- object = 1900 for pension stabilization is under lump sums\
- object = 1167 and 1168 is for Employer pension contributions but is not used by IOC yet as of FY2022.\
- object 4900 is awards and grants lump sum
Employer contributions for pensions are excluded from analysis to avoid double counting the cost of pensions. Expenditures with object 4430 for pensions, benefits, and annuities appears in items from funds 0473, 0477, 0479, 0481, (TRS, JRS, SERS, GARS), 0755, 0786, 0787, 0788, 0789, 0799 (deferred compensation plan, GAR excess benefit, JRS excess benefit, SER excess benefit, TRS excess benefit, state university retirement system) are NOT included in the analysis. All are coded with in_ff=0 in the fund_ab_in.xlsx file of funds.
Most of these funds were found by either using CTRL-F with pension related words or scrowllin through code options on the comptroller's website.
```{r pension-check, }
# check what is being included in pensions
# funds related to pension contributions
pension_funds <- c("0472", "0473", "0477", "0479", "0481", "0755", "0786", "0787", "0788", "0789", "0799")
pension_check <- exp_temp %>%
mutate(pension = case_when(
# object == "4430" & fund == "0825" ~ "Object 4430 - Pension Buyout/Benefits Paid Early",
object=="4430" ~ "Object 4430 - Benefits Paid to Employees; EXCLUDED", # pensions, annuities, benefits
object=="4431" ~ "Object 4431 - State Contributions; INCLUDED", # 4431 = state payments into pension fund
(obj_seq_type > "11590000" & obj_seq_type < "11660000") ~ "Object 1160-1165 Employer Contributions to Pension Fund; EXCLUDED",
# objects 1159 to 1166 are all considered Retirement by Comptroller
TRUE ~ "0")) %>% # All other observations coded as 0 for non-pension items
# recodes specific instances of code anomalies from past years:
mutate(pension = case_when(
(object=="1298" & fund %in% pension_funds ) ~ "Object 1298 - Purchase of Investments; DROPPED",
# pension stabilization fund in 2022
# object == "1900" & fund == "0319" ~ "Fund 0319-Pension Stabilization",
object == "1900" & fund %in% pension_funds ~ "Fund 0319 - Pension Stabilization",
object == "4900" & fund %in% pension_funds ~ "Object 4900 - Awards/Grants; Weird 2010-2011 values",
TRUE ~ as.character(pension)) ) %>%
filter(pension != "0" )
pension_check %>% group_by(fy, pension) %>%
summarize(expenditure = sum(expenditure, na.rm = TRUE)) %>%
ggplot(aes(x=fy, y = expenditure, color = pension)) +
geom_line() +
theme_classic()+
labs (title = "Pension Fund Payments In and Retirement Benefits Out",
caption = "Object 4430 is retirement benefits paid to employees.
Object 4431 includes state payments INTO pension Fund.
Object 1998 is excluded except for years 2010 and 2011 due to POBs.")+
theme(legend.position = "bottom")+
guides(color = guide_legend(nrow=3))
```
```{r pension-check2, fig.show='hold', out.width="50%"}
pension_check %>% group_by(fy, object) %>%
summarize(expenditure = sum(expenditure, na.rm = TRUE)) %>%
ggplot(aes(x=fy, y = expenditure, color = object)) +
geom_line() +
theme_classic()+
labs (title = "Expenditures by Object")
pension_check %>% group_by(fy, type) %>%
summarize(expenditure = sum(expenditure, na.rm = TRUE)) %>%
ggplot(aes(x=fy, y = expenditure, color = type)) +
geom_line() +
theme_classic()+
labs (title = "Expenditures by Type", caption = "Not confident with what Type represents.
$10 billion POB issued in 2003-2004 and again in 2010-2011.")
```
```{r pension-check3, fig.show='hold', out.width="50%"}
pension_check3 <- exp_temp %>%
mutate(pension = case_when(
(object=="4430" ) ~ 1, # 4430 = pension benefits paid to retired employees
TRUE ~ 0)) %>%
filter(pension > 0 )
pension_check3 %>% group_by(fy) %>%
summarize(expenditure = sum(expenditure, na.rm = TRUE)) %>%
ggplot(aes(x=fy, y = expenditure)) +
geom_line() +
theme_classic()+
labs (title = "Pension Benefits Paid to Employees")
## taking care of Pension Obligation Bond proceeds
```
```{r fig.show='hold', out.width="50%"}
pension_picture <- exp_temp %>%
mutate(pension = case_when(
#object == "4430" & fund == "0825" ~ "Pension Buyout/Benefits Paid Early; INCLUDED",
(object=="4430") ~ "Benefits Paid to Employees", # pensions, annuities, benefits
(object=="4431") ~ "State Pension Contributions", # 4431 = state payments into pension fund
(obj_seq_type > "11590000" & obj_seq_type < "11660000") ~ "IOC Retirement Expense Objectw",
# objects 1159 to 1166 are all considered Retirement by Comptroller
TRUE ~ "0")) %>% # All other observations coded as 0 for non-pension items
# recodes specific instances of code anomalies from past years:
# mutate(pension = case_when( (object=="1298" & fund %in% pension_funds ) ~ "Purchase of Investments",
# pension stabilization fund in 2022
# object == "1900" & fund == "0319" ~ "Fund 0319-Pension Stabilization",
# object == "1900" & fund %in% pension_funds ~ "Pension Stabilization Fund",
#TRUE ~ as.character(pension)) ) %>%
filter(pension != "0" )
pension_picture %>% group_by(fy, pension) %>%
summarize(expenditure = sum(expenditure, na.rm = TRUE)) %>%
ggplot(aes(x=fy, y = expenditure, color = pension)) +
geom_line() +
theme_classic()+
labs (title = "Pension Fund Payments In and Retirement Benefits Out",
caption = "All pension expenditure types are included in IOC Expenditure data")+
theme(legend.position = "bottom", legend.title = element_blank())#+ guides(color = guide_legend(nrow=2))
```
Employer contributions and state pension contributions are an example of an expenditure being double counted if both are included.
### Pension Contributions - Revenue Data
Pension contributions from employees and employers are not included as revenue sources but are useful for understanding the money going into the funds and the money flowing out of the funds. Identifying and graphing employee and employer contributions, as well as benefits paid to retired employees and state contributions was important for checking the items that should and should not be included in the analysis.
```{r combo-contrib-graph}
# rev_type = 51 is for retirement/pension contributions from both employers and employees.
# current year employee revenue source = 0573, contributions by employee == 572 (stops at 2011)
retirement_contributions <- rev_temp %>%
filter(rev_type == "51") %>% group_by(fy) %>% summarize(contributions = sum(receipts))
employer_contributions <- rev_temp %>%
filter(rev_type == "51" & source == "0577") %>% group_by(fy) %>% summarize(contributions = sum(receipts))
employee_contributions <- rev_temp %>%
filter(rev_type == "51" & (source == "0572" | source == "0573") ) %>%
group_by(fy) %>% summarize(contributions = sum(receipts))
benefits_paid <- pension_check %>% filter(object == "4430") %>%
group_by(fy) %>%
summarize(expenditure = sum(expenditure, na.rm = TRUE))
state_contrib <- pension_check %>% filter(object == "4431") %>%
group_by(fy) %>%
summarize(expenditure = sum(expenditure, na.rm = TRUE))
```
```{r combo-contrib-graph2, fig.show='hold', out.width="50%" }
rev_temp %>%
filter(rev_type == "51") %>% # all retirement contributions
group_by(fy, source) %>%
summarise(sum = sum(receipts, na.rm = TRUE)) %>%
ggplot() +
theme_classic()+
geom_line(aes(x=fy, y = sum, color=source)) +
labs(title="All Retirement Contributions, ALL rev_source == 51",
caption = "Source 0573, 0572 is for employee contributions. 0577 is Contributions by employer.")
pension_picture %>% group_by(fy, pension) %>%
summarize(expenditure = sum(expenditure, na.rm = TRUE)) %>%
ggplot(aes(x=fy, y = expenditure, color = pension)) +
geom_line() +
theme_classic()+
geom_line(data=employee_contributions, aes(x=fy, y=contributions), color="green") +
geom_line(data=employer_contributions, aes(x=fy, y=contributions), color="orange") +
labs (title = "Pension Fund Payments In and Retirement Benefits Out",
caption = "Neon green - employee contributions INTO the fund.
Orange - employer contributions INTO the fund.")+
theme(legend.position = "bottom", legend.title = element_blank())
```
## Debt Service Discussion
Debt Service expenditures include interest payment on both short-term and long-term debt. We do not include escrow or principal payments.
**Decision from Sept 30 2022:** We are no longer including short term principal payments as a cost; only interest on borrowing is a cost. Pre FY22 and the FY21 correction, we did include an escrow payment and principle payments as costs but not bond proceeds as revenues. This caused expenditures to be inflated because we were essentially counting debt twice - the principle payment and whatever the money was spent on in other expenditure categories, which was incorrect.
**Objects:**\
8813 interest **INCLUDE AS COST**\
8811 is for principle **EXCLUDE from aggregate debt expenditure**\
8841 is for escrow payments **EXCLUDE from aggregate debt expenditure**\
8800 is for capital projects debt service (e.g. Build Illinois Bonds, Civic Center, Tollway **EXCLUDE tollway in debt cost** - Note: debt principle and interest are both included in capital projects because they are combined in the data observations; bond proceeds are not considered a revenue source. Can't include capital projects interest as easily as the GO bonds.
**Obj_seq_type:**\
- Exclude: Bond principle payments: obj_seq_type == 88110008\
- Exclude: Short term borrowing principle: obj_seq_type == 88110108\
- Include: General Obligation Bond Interest: obj_seq_type == 88130000 & 88130008\
- Include: Interest on short-term borrowing: 88130108\
- Exclude: Escrow payment == 88410008\
- Include: Build IL Bonds, capital projects principal AND interest\
- Tollway is obj_seq_type == 88000055, filter out fund == 0455 to remove tollway\
- fund == 0455 is the IL State Toll Highway fund, items mostly for operations and maintenance
#### Tollway Debt, Revenue, and Expenditures
```{r tollway}
tollway <- exp_temp %>% filter(fund == "0455") #all tollway expenditures
capitalproject_debtservice <- exp_temp %>%filter(object == "8800") # ALL Capital projects debt service
# look at Illinois tollway bond proceeds and debt service:
# rev_temp %>% filter(fund == "0455") # examine items in fund 0455
#exp_temp %>% filter(fund == "0455") %>% group_by(fy) %>% summarize(sum = sum(expenditure)) %>% arrange(-fy)
tollway
#rev_temp %>% filter(fund == "0455") %>% group_by(fy) %>% summarize(sum = sum(receipts)) %>% arrange(-fy)
tollway_exp <- exp_temp %>% filter(fund == "0455") %>% group_by(fy) %>% summarize(expenditure = sum(expenditure))
#tollway_exp %>% ggplot() + geom_line(aes(x=fy, y=expenditure)) + labs(title = "Fund 0455 from Expenditure: All Tollway Expenditures", caption = "Data from IOC Expenditure Files. Fund 0455 is the IL State Tollway")
# all tollway revenues, not just bond proceeds
alltollway<-rev_temp %>% filter(fund == "0455" & source != "0571") %>% group_by(fy) %>% summarize(sum = sum(receipts, na.rm = TRUE))
# tollway bond proceeds
tollway_bondproc <- rev_temp %>% filter(fund == "0455" & source == "0571" ) %>% group_by(fy) %>% summarize(sum = sum(receipts, na.rm = TRUE))
#alltollway %>% ggplot() + geom_line(aes(x=fy, y=sum)) + labs(title = "Fund 0455 - All Tollway Revenue", caption = "Data from IOC Revenue Files. Fund 0455 is the IL State Tollway Revenue")
#tollway_bondproc %>% ggplot() + geom_line(aes(x=fy, y=sum)) + labs(title = "Fund 0455 - Tollway Revenue: Tollway Bond Proceeds", caption = "Data from IOC Revenue Files. Fund 0455 is the IL State Tollway Revenue")
#ggplot() + geom_line(data=tollway_bondproc, aes(x=fy, y=sum)) + labs(title = "Fund 0455 - Tollway Revenue: Tollway Bond Proceeds", caption = "Data from IOC Revenue Files. Fund 0455 is the IL State Tollway Revenue")
#tollwaydebt %>% ggplot() + geom_line(aes(x=fy, y=sum)) + labs(title = "Tollway Debt Service", caption = "Debt service includes principal and interest for the Illinois Tollway. Object = 8800 and fund = 0455")
#tollway debt principal and interest
tollwaydebt <- exp_temp %>%filter(object == "8800" & fund == "0455") %>% group_by(fy) %>% summarize(sum=sum(expenditure))
# Tollway agency expenditures = SAME as filtering by fund == 0455
#tollway<-exp_temp %>% filter(agency == "557")
#exp_temp %>% filter(agency == "557") %>% group_by(fy) %>% summarize(sum = sum(expenditure)) %>% arrange(-fy)
# contributions and benefits paid comparison
ggplot()+
theme_classic()+
geom_line(data=tollway_bondproc, aes(x=fy, y=sum, color='Bond Proceeds')) +
geom_line(data= tollwaydebt, aes(x=fy, y = sum, color = 'Debt Service'))+
geom_line(data= tollway_exp, aes(x=fy, y = expenditure, color = 'Tollway Expenditures'))+
geom_line(data= alltollway, aes(x=fy, y = sum, color = "Tollway Revenue"))+
scale_color_manual(values = c(
'Bond Proceeds' = 'darkblue',
'Debt Service' = 'red',
'Tollway Expenditures' = 'orange',
'Tollway Revenue' = 'light green')) +
labs(title="Tollway bond procreeds, debt service, revenue, and expenditures.",
caption = "Tollway revenue + bond proceeds should be roughly equal to tollway expenditures + debt service.",
y = "Dollars")
```
#### State Principal and Interest
Filtering for interest on short term borrowing and GO bonds (88130008, 88130000, and 88130108) and GO bond principal amounts (88110008).
- object == 8813 is for all debt service interest but obj_seq_type is used to specify short term borrowing versus regular debt service.
- an Interest to Principal ratio is also calculated in the table below.
Looking only at general obligation principal payments and interest payments:
```{r}
# GO bond principal and GO bond interest
GObond_debt <- exp_temp %>%
filter(obj_seq_type == "88110008" |obj_seq_type == "88130000" | obj_seq_type == "88130008") %>%
group_by(fy, obj_seq_type) %>%
summarize(sum = sum(expenditure, na.rm=TRUE)) %>%
pivot_wider(names_from = obj_seq_type, values_from = sum) %>%
mutate(principal = `88110008`,
interest = sum(`88130008`+`88130000`, na.rm = TRUE),
ratio = (as.numeric(interest)/as.numeric(principal)))
GObond_debt %>% select(principal, interest, ratio) %>%
mutate(across(principal:interest, ~format(., big.mark= ",", scientific = F)))
# GObond_debt %>% ggplot() +
# geom_line(aes(x=fy, y=principal, color = "Principal"))+
# geom_line(aes(x=fy, y=interest, color = "Interest")) +
# labs(title = "General Obligation principal and interest payments")
GObond_debt %>% ggplot() +
theme_classic()+
geom_col(aes(x=fy, y=interest/1000000, fill = "Interest")) +
geom_col(aes(x=fy, y=principal/1000000, fill = "Principal"))+
labs(title = "Debt Service", subtitle = "General Obligation Principal and Interest Payments")
```
Looking only at short term borrowing principal and interest payments:
```{r}
# short term borrowing, first observation is in 2004?
short_debt <- exp_temp %>%
filter(obj_seq_type == 88110108 |obj_seq_type == 88130108) %>%
group_by(fy, obj_seq_type) %>%
summarize(sum = sum(expenditure, na.rm=TRUE)) %>%
pivot_wider(names_from = obj_seq_type, values_from = sum) %>%
mutate(principal = `88110108`,
interest = `88130108`,
ratio = (as.numeric(interest)/as.numeric(principal)))
short_debt %>% select(principal, interest, ratio) %>%
mutate(across(principal:interest, ~format(., big.mark= ",", scientific = F)))
short_debt %>% ggplot() + theme_classic()+
geom_col(aes(x=fy, y=principal/1000000, fill = "Principal"))+
geom_col(aes(x=fy, y=interest/1000000, fill = "Interest")) +
labs(title = "Debt Service", subtitle = "Short Term Borrowing: Principal and Interest Payments")
```
When including short term borrowing and normal debt service, the debt ratio seems more normal and the total interest and principal payments over the years are smoothed out.
Principal and interest amounts calculated exclude the Illinois Tollway debt service and debt for capital projects. Capital projects debt service is examined below:
```{r}
capitalprojects <- exp_temp %>% filter(object == "8800")
all_debt <- exp_temp %>%
filter(fund != "0455" & (object == "8811" |object == "8813" | object == "8800") )%>%
group_by(fy, object) %>%
summarize(sum = sum(expenditure, na.rm=TRUE)) %>%
pivot_wider(names_from = object, values_from = sum) %>%
mutate(principal = `8811`,
interest = `8813`,
CapitalProjects = `8800`,
ratio = (as.numeric(interest)/as.numeric(principal)))
all_debt %>% select(principal, interest, CapitalProjects, ratio) %>%
mutate(across(principal:CapitalProjects, ~format(., big.mark= ",", scientific = F)))
all_debt %>% ggplot() +
theme_classic()+
geom_line(aes(x=fy, y=principal/1000000, color = "Principal"))+
geom_line(aes(x=fy, y=interest/1000000, color = "Interest"))+
geom_line(aes(x=fy, y = CapitalProjects / 1000000, color = "Capital Projects"))+
labs(y = "Debt ($Millions)",
title = "Principal and Interest payments", subtitle = "Principal and interest from short term borrowing and GO Bonds debt service", caption = "Capital projects does not include Illinois tollway debt service.
Capital projects include interest and principal values as one value and cannot be sepearated.")
```
```{r fig.show='hold', out.width="50%"}
all_debt %>% ggplot() + theme_classic()+
geom_line(aes(x=fy, y=principal/1000000, color = "Principal"))+
geom_line(aes(x=fy, y=interest/1000000, color = "Interest"))+
geom_line(aes(x=fy, y = CapitalProjects / 1000000, color = "Capital Projects Debt Service"))+
geom_line(data = tollwaydebt, aes( x=fy, y=sum/1000000, color = "Tollway Debt Service"))+
labs(y = "Debt ($Millions)", title = "Short term borrowing and GO Bonds",
subtitle = "Principal and Interest payments", caption = "Capital projects does not include Illinois tollway debt service.")
all_debt %>% ggplot() + theme_classic()+
geom_line(aes(x=fy, y=(principal+interest)/1000000, color = "Principal & Interest"))+
#geom_line(aes(x=fy, y=interest/1000000, color = "Interest"))+
geom_line(aes(x=fy, y = CapitalProjects / 1000000, color = "Capital Projects Debt Service"))+
geom_line(data = tollwaydebt, aes( x=fy, y=sum/1000000, color = "Tollway Debt Service"))+
labs(y = "Debt ($Millions)", title = "Illinois Debt Service Expenditures: Short term borrowing and GO Bonds",
subtitle = "Principal and Interest payments", caption = "Capital projects does not include Illinois tollway debt service.")
```
Capital projects include the IL Civic Center and Build Illinois Bonds. Tollway principal and interest has been dropped from the State's Debt Service expenditure but is counted in the Illinois Tollway Expenditure cost.
## State Employee Healthcare Discussion
State Employee Health Care = Sum of expenditures for "health care coverage as elected by members per state employees group insurance act." The payments are made from the Health Insurance Reserve Fund. Employee contributions are not considered a revenue source or an expenditure in our analysis.
Funding for the State Employees Group Insurance plan originates from two funds. The Health Insurance Reserve Fund (HIRF) and the Group Insurance Premium Fund (GIPF). Contributions and payment for Health coverage benefits are deposited INTO HIRF and contributions for life insurance are deposited into the GIPF.
HIRF is the fund mainly used to administer the group insurance program. Funding for HIRF comes from several different revenue sources, the General Revenue Fund (GRF), Road Fund, reimbursements, university funds, and misc funds. [CGFA Report](https://cgfa.ilga.gov/Upload/fy2011stateemployees'groupinsurance.pdf)
**Coding details**\
In FY2013, the Local Government Health Insurance fund was transferred to the department of Central Management Services (agency changes from 478 to 416 in data.)
Employer group insurance contributions for health insurance are excluded to avoid double counting the cost of healthcare provision. All employer group insurance contributions are coded as object = 1180. BUT the last two fiscal years were coded as 1900 instead of 1180 for lump sums instead of employer contributions\
- anything to do with pandemic money categorization?
Fund = 0907 = health insurance reserve, in_ff = 1
Fund = 0457 is "Group insurance premium", in_ff = 1