Aggregate expenditures: Save tax refunds as negative revenue. Code refunds to match the rev_type codes (02=income taxes, 03 = corporate income taxes, 06=sales tax, 09=motor fuel tax, 24=insurance taxes and fees, 35 = all other tax refunds).
Code
tax_refund_long <- exp_temp %>%# fund != "0401" # removes State Trust Fundsfilter(fund !="0401"& (object =="9900"| object=="9910"|object=="9921"|object=="9923"|object=="9925")) %>%# keeps these objects which represent revenue, insurance, treasurer,and financial and professional reg tax refundsmutate(refund =case_when( object =="9900"~"FY23_Rebates", fund=="0278"& sequence =="00"~"02", # for income tax refund fund=="0278"& sequence =="01"~"03", # tax administration and enforcement and tax operations become corporate income tax refund fund =="0278"& sequence =="02"~"02", object=="9921"~"21", # inheritance tax and estate tax refund appropriation object=="9923"~"09", # motor fuel tax refunds obj_seq_type =="99250055"~"06", # sales tax refund fund=="0378"& object=="9925"~"24", # insurance privilege tax refund (fund=="0001"& object=="9925") | (object=="9925"& fund =="0384"& fy ==2023) ~"35", # all other taxes# fund=="0001" & object=="9925" ~ "35", # all other taxes T ~"CHECK")) # if none of the items above apply to the observations, then code them as CHECK exp_temp <-left_join(exp_temp, tax_refund_long) %>%mutate(refund =ifelse(is.na(refund),"not refund", as.character(refund)))tax_refund <- tax_refund_long %>%group_by(refund, fy)%>%summarize(refund_amount =sum(expenditure, na.rm =TRUE)/1000000) %>%pivot_wider(names_from = refund, values_from = refund_amount, names_prefix ="ref_") %>%mutate_all(~replace_na(.,0)) %>%arrange(fy)# # tax_refund %>% # pivot_longer(c(ref_06:ref_35, ref_FY23_Rebates), names_to = "Refund Type", values_to = "Amount") %>%# ggplot()+# theme_classic()+# geom_line(aes(x=fy,y=Amount, group = `Refund Type`, color = `Refund Type`))+# labs(title = "Refund Types", # caption = "Refunds are excluded from Expenditure totals and instead subtracted from Revenue totals") + # labs(title = "Tax refunds", # caption = "Rev_type codes: 02=income taxes, 03=corporate income taxes, 06=sales tax, 09=motor fuel tax, # 24=insurance taxes and fees, 35 = all other tax refunds." )# remove the items we recoded in tax_refund_long# exp_temp <- exp_temp %>% filter(refund == "not refund")
Code
# manually adds the abatements as expenditure item and keeps on expenditure side.# otherwise ignored since it is in fund 0278 and exp_temp <- exp_temp %>%mutate(in_ff =ifelse(object ==9900, 1, in_ff))
2.1.2 Pension Expenditures
State pension contributions are largely captured with object=4431. (State payments into pension fund). 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)
While it is good to know the overall cost of pensions for the state, if you want to know the true cost of providing services, pension costs should be included in the department that is paying employees to provide those services.
Change in pension coding in chunk below:
Code
exp_temp <- exp_temp %>%arrange(fund) %>%mutate(pension =case_when( ## Commented out line below:# (object=="4431") ~ 1, # 4431 = easy to find pension payments INTO fund (object=="1298"&# Purchase of Investments, Normally excluded (fy==2010| fy==2011) & (fund=="0477"| fund=="0479"| fund=="0481")) ~3, #judges retirement OUT of fund# state borrowed money from pension funds to pay for core services during 2010 and 2011. # used to fill budget gap and push problems to the future. fund =="0319"~4, # pension stabilization fundTRUE~0) )
Code
# special accounting of pension obligation bond (POB)-funded contributions to JRS, SERS, GARS, TRS exp_temp <- exp_temp %>%# change object for 2010 and 2011, retirement expenditures were bond proceeds and would have been excludedmutate(object =ifelse((pension >0& in_ff =="0"), "4431", object)) %>%# changes weird teacher & judge retirement system pensions object to normal pension object 4431mutate(pension =ifelse(pension >0& in_ff =="0", 6, pension)) %>%# coded as 6 if it was supposed to be excluded. mutate(in_ff =ifelse(pension >0, "1", in_ff))# all other pensions objects codes get agency code 901 for State Pension Contributionsexp_temp <- exp_temp %>%mutate(agency =ifelse(pension >0, "901", as.character(agency)),agency_name =ifelse(agency =="901", "State Pension Contributions", as.character(agency_name)))
Drop all cash transfers between funds, statutory transfers, and purchases of investments from expenditure data.
# always check to make sure you aren't accidently dropping something of interest.exp_temp <-anti_join(exp_temp, transfers_drop)
2.1.3 State employee healthcare costs
Code
#if observation is a group insurance contribution, then the expenditure amount is set to $0 (essentially dropped from analysis)# pretend eehc is named group_insurance_contribution or something like that# eehc coded as zero implies that it is group insurance# if eehc=0, then expenditures are coded as zero for group insurance to avoid double counting costsexp_temp <- exp_temp %>%mutate(eehc =ifelse(# group insurance contributions for 1998-2005 and 2013-present fund =="0001"& (object =="1180"| object =="1900") & agency =="416"& appr_org=="20", 0, 1) )%>%mutate(eehc =ifelse(# group insurance contributions for 2006-2012 fund =="0001"& object =="1180"& agency =="478"& appr_org=="80", 0, eehc) )%>%# group insurance contributions from road fund# coded with 1900 for some reason??mutate(eehc =ifelse( fund =="0011"& object =="1900"& agency =="416"& appr_org=="20", 0, eehc) ) %>%mutate(expenditure =ifelse(eehc=="0", 0, expenditure)) %>%mutate(agency =case_when( # turns specific items into State Employee Healthcare (agency=904) fund=="0907"& (agency=="416"& appr_org=="20") ~"904", # central management Bureau of benefits using health insurance reserve fund=="0907"& (agency=="478"& appr_org=="80") ~"904", # agency = 478: healthcare & family services using health insurance reserve - stopped using this in 2012TRUE~as.character(agency))) %>%mutate(agency_name =ifelse( agency =="904", "STATE EMPLOYEE HEALTHCARE", as.character(agency_name)),in_ff =ifelse( agency =="904", 1, in_ff),group =ifelse(agency =="904", "904", as.character(agency))) # creates group variable# Default group = agency numberhealthcare_costs <- exp_temp %>%filter(group =="904")
Code
exp_temp <- exp_temp %>%mutate(agency =case_when(fund=="0515"& object=="4470"& type=="08"~"971", # income tax to local governments fund=="0515"& object=="4491"& type=="08"& sequence=="00"~"971", # object is shared revenue payments fund=="0802"& object=="4491"~"972", #pprt transfer fund=="0515"& object=="4491"& type=="08"& sequence=="01"~"976", #gst to local fund=="0627"& object=="4472"~"976" , # public transportation fund but no observations exist fund=="0648"& object=="4472"~"976", # downstate public transportation, but doesn't exist fund=="0515"& object=="4470"& type=="00"~"976", # object 4470 is grants to local governments object=="4491"& (fund=="0188"|fund=="0189") ~"976", fund=="0187"& object=="4470"~"976", fund=="0186"& object=="4470"~"976", object=="4491"& (fund=="0413"|fund=="0414"|fund=="0415") ~"975", #mft to local fund =="0952"~"975", # Added Sept 29 2022 AWM. Transportation Renewal MFTTRUE~as.character(agency)),agency_name =case_when(agency =="971"~"INCOME TAX 1/10 TO LOCAL", agency =="972"~"PPRT TRANSFER TO LOCAL", agency =="975"~"MFT TO LOCAL", agency =="976"~"GST TO LOCAL",TRUE~as.character(agency_name)),group =ifelse(agency>"970"& agency <"977", as.character(agency), as.character(group)))
Code
transfers_long <- exp_temp %>%filter(group =="971"|group =="972"| group =="975"| group =="976")transfers <- transfers_long %>%group_by(fy, group ) %>%summarize(sum_expenditure =sum(expenditure)/1000000) %>%pivot_wider(names_from ="group", values_from ="sum_expenditure", names_prefix ="exp_" )exp_temp <-anti_join(exp_temp, transfers_long)dropped_inff_0 <- exp_temp %>%filter(in_ff ==0)exp_temp <- exp_temp %>%filter(in_ff ==1) # drops in_ff = 0 funds AFTER dealing with net-revenue above
All expenditures recoded but not aggregated: Allows for inspection of individual expenditures within larger categories. This stage of the data is extremely useful for investigating how individual items have been coded before they are aggregated into larger categories.
2.2 Modify Revenue data
Code
# recodes old agency numbers to consistent agency numberrev_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 transportationTRUE~ (as.character(agency))))
Insurance premiums for employees is coded below but it is NOT used in the fiscal futures model. Employee and employer premiums are considered rev_51 and dropped from analysis in later step.
0120 = ins prem-option life
0120 = ins prem-optional life/univ
0347 = optional health - HMO
0348 = optional health - dental
0349 = optional health - univ/local SI
0350 = optional health - univ/local
0351 = optional health - retirement
0352 = optional health - retirement SI
0353 = optional health - retire/dental
0354 = optional health - retirement hmo
2199-2209 = various HMOs, dental, health plans from Health Insurance Reserve (fund)
Code
#collect optional insurance premiums to fund 0907 for use in eehc expenditure rev_temp <- rev_temp %>%mutate(employee_premiums =ifelse(fund=="0907"& (source=="0120"| source=="0121"| (source>"0345"& source<"0357")|(source>"2199"& source<"2209")), 1, 0),# adds more rev_type codesrev_type =case_when( fund =="0427"~"12", # pub utility tax fund =="0742"| fund =="0473"~"24", # insurance and fees fund =="0976"~"36",# receipts from rev producing fund =="0392"|fund =="0723"~"39", # licenses and fees fund =="0656"~"78", #all other rev sourcesTRUE~as.character(rev_type)))# if not mentioned, then rev_type as it was# # optional insurance premiums = employee insurance premiums# emp_premium <- rev_temp %>%# group_by(fy, employee_premiums) %>%# summarize(employee_premiums_sum = sum(receipts)/1000000) %>%# filter(employee_premiums == 1) %>%# rename(year = fy) %>% # select(-employee_premiums)emp_premium_long <- rev_temp %>%filter(employee_premiums ==1)# 381 observations have employee premiums == 1# drops employee premiums from revenue# rev_temp <- rev_temp %>% filter(employee_premiums != 1)# should be dropped in next step since rev_type = 51
Note: In FY21, employee premiums were subtracted from state healthcare costs on the expenditure side to calculate a “Net Healthcare Cost” but that methodology has been discontinued. Totals were practically unchanged: revenue from employee premiums is also very small.
2.2.2 Transfers in and Out:
Funds that hold and disperse local taxes or fees are dropped from the analysis. Then other excluded revenue types are also dropped.
Drops Blank, Student Fees, Retirement contributions, proceeds/investments, bond issue proceeds, interagency receipts, cook IGT, Prior year refunds:
Code
rev_temp <- rev_temp %>%filter(in_ff ==1) %>%mutate(local =ifelse(is.na(local), 0, local)) %>%# drops all revenue observations that were coded as "local == 1"filter(local !=1)# 1175 doesnt exist?in_from_out <-c("0847", "0867", "1175", "1176", "1177", "1178", "1181", "1182", "1582", "1592", "1745", "1982", "2174", "2264")# what does this actually include:# all are items with rev_type = 75 originally. in_out_df <- rev_temp %>%mutate(infromout =ifelse(source %in% in_from_out, 1, 0)) %>%filter(infromout ==1)rev_temp <- rev_temp %>%mutate(rev_type_new =ifelse(source %in% in_from_out, "76", rev_type))# if source contains any of the codes in in_from_out, code them as 76 (all other rev).# I end up excluding rev_76 in later steps
Are the 4 smallest categories from past years still the 4 smallest categories? Check it each year.
Code
# revenue types to dropdrop_type <-c("32", "45", "51", "66", "72", "75", "76", "79", "98", "99")# drops Blank, Student Fees, Retirement contributions, proceeds/investments,# bond issue proceeds, interagency receipts, cook IGT, Prior year refunds.rev_temp <- rev_temp %>%filter(!rev_type_new %in% drop_type)# keep observations that do not have a revenue type mentioned in drop_typetable(rev_temp$rev_type_new)
# combines smallest 4 categories to to "Other"# they were the 4 smallest in past years, are they still the 4 smallest? # rev_temp <- rev_temp %>% # mutate(rev_type_new = ifelse(rev_type=="30" | rev_type=="60" | rev_type=="63" ,# ## | rev_type=="76", # "78", rev_type_new))#table(rev_temp$rev_type_new) # check workrm(rev_1998_2022)rm(exp_1998_2022)write_csv(exp_temp, "data/exp_fy24_pensionrecode.csv")write_csv(rev_temp, "data/rev_fy24_pensionrecode.csv")
2.3 Pivoting and Merging
Local Government Transfers (exp_970) should be on the expenditure side
2.3.1 Revenues
Code
ff_rev <- rev_temp %>%group_by(rev_type_new, fy) %>%summarize(sum_receipts =sum(receipts, na.rm=TRUE)/1000000 ) %>%pivot_wider(names_from ="rev_type_new", values_from ="sum_receipts", names_prefix ="rev_")# ff_rev<- left_join(ff_rev, tax_refund)#ff_rev <- left_join(ff_rev, pension2_fy22, by=c("fy" = "year"))#ff_rev <- left_join(ff_rev, eehc2_amt) ff_rev <-mutate_all(ff_rev, ~replace_na(.,0))# # ff_rev <- ff_rev %>%# mutate(rev_02 = rev_02 - ref_02,# rev_03 = rev_03 - ref_03,# rev_06 = rev_06 - ref_06,# rev_09 = rev_09 - ref_09,# rev_21 = rev_21 - ref_21,# rev_24 = rev_24 - ref_24,# rev_35 = rev_35 - ref_35# # # rev_78new = rev_78 #+ pension_amt #+ eehc# ) %>% # select(-c(ref_02:ref_35, rev_99, rev_NA, rev_76# #, ref_CHECK#, pension_amt , rev_76,# # , eehc# ))# # ff_rev#noproblem <- c(0) # if ref_CHECK = $0, then there is no problem. :) # # if((sum(ff_rev$ref_CHECK) == 0 )){# # ff_rev <- ff_rev %>%# # mutate(rev_02 = rev_02 - ref_02,# rev_03 = rev_03 - ref_03,# rev_06 = rev_06 - ref_06,# rev_09 = rev_09 - ref_09,# rev_21 = rev_21 - ref_21,# rev_24 = rev_24 - ref_24,# rev_35 = rev_35 - ref_35# ) %>% # select(-c(ref_02:ref_35, rev_99, rev_76, ref_CHECK )) # }else{"You have a problem! Check what revenue items did not have rev codes (causing it to be coded as rev_NA) or the check if there were refunds that were not assigned revenue codes (tax_refunds_long objects)"}ff_rev %>%mutate_all(., ~round(.,digits=0))
Since I already pivot_wider()ed the table in the previous code chunk, I now change each column’s name by using rename() to set new variable names. Ideally the final dataframe would have both the variable name and the variable label but I have not done that yet.
Code
aggregate_rev_labels <- ff_rev %>%rename("INDIVIDUAL INCOME TAXES, gross of local, net of refunds"= rev_02,"CORPORATE INCOME TAXES, gross of PPRT, net of refunds"= rev_03,"SALES TAXES, gross of local share"= rev_06 ,"MOTOR FUEL TAX, gross of local share, net of refunds"= rev_09 ,"PUBLIC UTILITY TAXES, gross of PPRT"= rev_12,"CIGARETTE TAXES"= rev_15 ,"LIQUOR GALLONAGE TAXES"= rev_18,"INHERITANCE TAX"= rev_21,"INSURANCE TAXES&FEES&LICENSES, net of refunds"= rev_24 ,"CORP FRANCHISE TAXES & FEES"= rev_27,"HORSE RACING TAXES & FEES"= rev_30, # in Other"MEDICAL PROVIDER ASSESSMENTS"= rev_31 ,# "GARNISHMENT-LEVIES " = rev_32 , # dropped"LOTTERY RECEIPTS"= rev_33 ,"OTHER TAXES"= rev_35,"RECEIPTS FROM REVENUE PRODUCNG"= rev_36, "LICENSES, FEES & REGISTRATIONS"= rev_39 ,"MOTOR VEHICLE AND OPERATORS"= rev_42 ,# "STUDENT FEES-UNIVERSITIES" = rev_45, # dropped"RIVERBOAT WAGERING TAXES"= rev_48 ,# "RETIREMENT CONTRIBUTIONS " = rev_51, # dropped"GIFTS AND BEQUESTS"= rev_54, "FEDERAL OTHER"= rev_57 ,"FEDERAL MEDICAID"= rev_58, "FEDERAL TRANSPORTATION"= rev_59 ,"OTHER GRANTS AND CONTRACTS"= rev_60, #other"INVESTMENT INCOME"= rev_63, # other# "PROCEEDS,INVESTMENT MATURITIES" = rev_66 , #dropped# "BOND ISSUE PROCEEDS" = rev_72, #dropped# "INTER-AGENCY RECEIPTS" = rev_75, #dropped# "TRANSFER IN FROM OUT FUNDS" = rev_76, # dropped"ALL OTHER SOURCES"= rev_78,# "COOK COUNTY IGT" = rev_79, #dropped# "PRIOR YEAR REFUNDS" = rev_98 #dropped ) aggregate_rev_labels %>%mutate_all(., ~round(., digits =0))
Table 2.2: Aggregated Revenue Categories ($ Millions), with old labels
2.3.2 Expenditures
Create exp_970 for all local government transfers (exp_971 + exp_972 + exp_975 + exp_976).
Table 2.4: Final Expenditure Categories, with Fiscal Futures Grouped Expenditure Categories
3 Graphs and Tables
Create total revenues and total expenditures only:
after aggregating expenditures and revenues, pivoting wider, then I want to drop the columns that I no longer want and then pivot_longer(). After pivoting_longer() and creating rev_long and exp_long, expenditures and revenues are in the same format and can be combined together for the totals and gap each year.
