Step by Step Data Exploration and Table/Figure Building
Modify Expenditure File
Tax refunds
Aggregate expenditures: Save tax refunds as negative revenue. Code refunds to match the rev_type codes
02 = income taxes, fund 0278
03 = corporate income taxes, fund 0946, 0380
06 = sales tax
09 = motor fuel tax, fund 0436, 0012
24 = insurance taxes and fees
35 = all other tax refunds.
Estate tax refund, fund 0121
Aviation fuel refund fund, fund 0946
cannabis Excise Tax Refund, fund 0912
Rental Purchase agreement Refund 0671
What we want:
To exclude refunds as expenditures from our expenditure totals.
Exclude funds that hold refund funds from both revenue and expenditure sides
Revenue neutral unless budget tricks are occurring
still want to examine refunds over time to compare to past years calculations
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"& fund =="0278"~"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=="0380"~"03", # corporate franv 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 fund %in%c("0946", "0912", "0671") ~"35", # cannabis, aviation, rental purchase tax refund 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)) |>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") |>ungroup() |>ggplot()+geom_line(aes(x=fy, y=Amount, group =`Refund Type`, color =`Refund Type`))+scale_y_billions() +labs(title ="Refund Types") +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.",y="Dollars", x =element_blank() )
Figure 1: 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"& fund =="0278"~"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=="0380"~"03", # corporate franv 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 fund %in%c("0946", "0912", "0671") ~"35", # cannabis, aviation, rental purchase tax refund 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)) |>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), 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") +labs(title ="Tax refunds without FY23 Abatements",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.", ) +scale_y_billions()
Figure 2: Tax Refunds without FY23 Abatements
Code
tax_refund_long |>summarize(expenditure =sum(expenditure, na.rm=TRUE), .by =c(fy, in_ff) ) |>ggplot() +geom_line(aes(x=fy, y = expenditure, group =factor(in_ff), color =factor(in_ff)))+labs(title ="Excluded Refund Expenditures", y ="Dollars", x =element_blank())
Figure 3: Based on current fund coding in the fund_ab_in.xlsx file, most refund dollars are already being excluded by having in_ff = 0. Funds that have in_ff = 0 are excluded from revenue and expenditure sums when calculating the Fiscal Gap.
Code
tax_refund_long_rev <- rev_temp |>mutate(refund =case_when( fund =="0121"~"35", # Estate tax refund fund=="0278"~"02", # for income tax refunds (individual and corporate) fund=="0380"~"03", # corporate franchise tax refund fund=="0378"~"24", # insurance privilege tax refund fund %in%c("0946", "0912", "0671") ~"35", # cannabis, aviation, rental purchase tax refund T ~"CHECK")) |># if none of the items above apply to the observations, then code them as CHECK filter(refund !="CHECK")tax_refund_rev <- tax_refund_long_rev |>group_by(refund, fy)|>summarize(allocated_for_refunds =sum(receipts, na.rm =TRUE)/1000000) |>pivot_wider(names_from = refund, values_from = allocated_for_refunds, names_prefix ="ref_") |>mutate_all(replace_na, 0) |>arrange(fy)tax_refund_rev|>pivot_longer(c(ref_02:ref_35), names_to ="Refund Type", values_to ="Amount") |>ggplot()+geom_line(aes(x=fy,y=Amount, group =`Refund Type`, color =`Refund Type`))+labs(title ="Refund Types") +labs(title ="Revenue Allocated to Tax Refund Funds ",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.",y ="Millions of $", x =element_blank()) +scale_x_continuous(expand =c(0,0), limits =c(1998, current_year+.5), breaks =c(1998, 2005, 2010, 2015, 2020, current_year))
Figure 4: Tax Refunds from Revenues. Includes only funds designated as Refund funds in fund descriptions.
Abatements were a specific policy choice to do and different than normal refunds. Therefore, abatements were included in the fiscal gap calculation. This is not even a concern during most years. Only was an issue in 2023 when $1 billion abatement expenditure occurred.
Code
# manually adds the abatements as expenditure item and keeps on expenditure side.# otherwise ignored since it is in fund 0278, which is coded as in_ff=0# all other income tax refunds are excluded from fiscal gap calculationsexp_temp <- exp_temp |>mutate(in_ff =ifelse(object ==9900& fund =="0278", 1, in_ff))
Pension Expenditures
An Explanation of a Methodological Change in How We Categorize Some Pension Spending
In previous reports, a expenditure category was created to represent all Pension spending. It was created by removing pension expenditures out of the agencies that that had the expenses in order to highlight the amount that was spent on pensions.
As of the FY2024 report, pension expenditures will remain in the agency that has the pension expenses to better represent the total cost of providing a service to the public.
However, we thought that readers also might be interested in total pension spending during current and past years. We have added a separate table showing this information but emphasize that pension spending is already incorporated in other spending categories and should not be added to the total shown in Table 1 of the report.
New POB bond in 2019: Accelerated Bond Fund paid benefits in advance as lump sum
State pension contributions for TRS and SURS are largely captured with object=4431. (State payments into pension fund). State payments to the following pension systems:
State Employee Retirement System (SERS) Agency 589 –> SERS Agency 589 - Note: Object 4431 does not have SERS expenditures in it. Those are only in object 116X objects
State University Retirement System (SURS) Agency 693 –> University Education (Group = 960)
General Assembly Retirement System (GARS) –> Legislative (Group 910)
There are also “Other Post-Employment Benefits” (OPEBs). Expenditure object 4430 is for retirement benefits.
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 and other benefit costs should be included in the department that is paying employees to provide those services.
But if you are curious how much went into pensions and for which pension agency, they are totaled below:
Code
pension_agencies <-c("589", "593", "594", "693", "275", "131" )pension_objects <-c(4431, 1160:1165)exp_temp |>filter( fy ==2024& object %in% pension_objects) |>mutate(group =ifelse(!agency %in% pension_agencies, "901", agency),group_label =case_when( group =="593"| group =="594"~"TRS", group =="589"~"SERS", group =="693"~"SURS", group =="275"~"JRS", group =="131"~"GARS", group =="901"~"Other Pension Costs",TRUE~"CHECK ME")) |># group = ifelse(object %in% 1160:1165, "901", as.character(agency))) |> group_by(group, group_label) |>summarize(Expenditures =sum(expenditure, na.rm=TRUE)/1000000000)exp_temp |>filter( fy ==2025& object %in% pension_objects) |>mutate(group =ifelse(!agency %in% pension_agencies, "901", agency),group_label =case_when( group =="901"~"Other Pensions", group =="593"~"K-12 Education", group =="594"~"Chi. TPF ", group =="589"~"SERS", group =="693"~"Higher Education", group =="275"~"Judicial", group =="131"~"Legislative",TRUE~"CHECK ME")) |># group = ifelse(object %in% 1160:1165, "901", as.character(agency))) |> group_by(group, group_label) |>summarize(Expenditures =sum(expenditure, na.rm=TRUE)/1000000000)exp_temp |>filter( fy ==2024& object %in% pension_objects) |>mutate(group =ifelse(!agency %in% pension_agencies, "901", agency),group_label =case_when( group =="901"~"Other Department Pensions", group =="593"~"K-12 Education", group =="594"~"Chi. TPF ", group =="589"~"Other Department Pensions", group =="693"~"Higher Education", group =="275"~"Judicial", group =="131"~"Legislative",TRUE~"CHECK ME")) |># group = ifelse(object %in% 1160:1165, "901", as.character(agency))) |> group_by(group, group_label) |>summarize(Expenditures =sum(expenditure, na.rm=TRUE)/1000000000)# in billions exp_temp |>filter( fy ==2025& agency!="494"& object %in% pension_objects ) |>summarize(`Pension Expenditures`=sum(expenditure/1000000000, na.rm=TRUE))
TO DO: Relabel image and 2004 pension spike
Code
pension_totals <- exp_temp %>%arrange(fund) %>%mutate(pension =case_when( (object=="1298"&# Purchase of Investments, Normally excluded (fy==2010| fy==2011) & (fund=="0477"| fund=="0479"| fund=="0481")) ~3, ( object=="4431") ~1, # 4431 = easy to find pension payments INTO fund (object>"1159"& object<"1166") & fund !="0183"& fund !="0193"~2, # objects 1159 to 1166 are all considered Retirement by Comptroller, # Included - employer contributions from agencies/organizations/etc.#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) )table(pension_totals$pension)
0 1 2 3 4
253172 254 9379 6 27
Code
pension_totals %>%filter(pension !=0) %>%mutate(pension =as.factor(pension))%>%group_by(fy, pension) %>%summarize(expenditure =sum(expenditure/1000000000, na.rm =TRUE)) %>%ggplot(aes(x=fy, y = expenditure, group=pension)) +theme_classic()+geom_col(aes(fill = pension)) +labs (title ="Pension expenditures", caption ="1 = State contributions INTO pension funds. 2 = Employer Contributions. 3 = Purchase of Investments anomaly in 2010 and 2011. 4 = pension stabilization fund")+theme(legend.position ="bottom") +scale_y_continuous(labels = scales::dollar, name ="$ Billions")
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))
Can also be thought of past commitments vs current contributions. Cost of past commitments in the form of Pension benefits paid out,
Current Employees vs Retired Employees
Current Employees: - Group Insurance Benefits
Retired Employees: - Deferred Compensation
- Medicare Retirees and Survivors of State of Illinois Employees Group Insurance Program (SEGIP)
- Part of Medicare
Code
exp_temp |>filter(fy==2024) |>filter((appr_org=="01"| appr_org =="65"| appr_org=="88") & (object=="4900"| object=="4400") ) |>group_by(agency, agency_name) |># separates CHIP from health and human services and saves it as Medicaidsummarize(expenditure =sum(expenditure))
Drop all cash transfers between funds, statutory transfers, and purchases of investments from expenditure data.
# always check to make sure you aren't accidentally dropping something of interest.exp_temp <-anti_join(exp_temp, transfers_drop)
State employee healthcare costs
Re-commented this out: Commented out line of code that seperates healthcare costs. This should keep healthcare costs in the agency, similar to the change that was made for pensions.
It looks like healthcare costs shift to “Other Departments” which includes:
GOMB (507)
Human Rights (442)
Illinois Power Agency (445)
Labor (452)
State Lottery (458)
Veteran’s Affairs (497)
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)))
Local Transfers
Code
transfers_long <- exp_temp |>filter((group =="971"|group =="972"| group =="975"| group =="976")) # fund == "0325")transfers_long |>group_by(agency_name, group, fy) |>summarize(expenditure =sum(expenditure, na.rm=TRUE) )|>ggplot() +geom_line(aes(x=fy, y = expenditure, color=agency_name)) +alea_theme() +scale_x_continuous(expand =c(0,0), limits =c(1998, current_year+.5), breaks =c(1998, 2005, 2010, 2015, 2020, current_year)) +labs(title ="Transfers to Local Governments", caption ="Data Source: Illinois Office of the Comptroller")
Figure 5: Drop Transfers from State to Local Governments
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
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 proceedsalltollway<-rev_temp |>filter(fund =="0455"& source !="0571") |>group_by(fy) |>summarize(sum =sum(receipts, na.rm =TRUE))# tollway bond proceedstollway_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 interesttollwaydebt <- 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 comparisonggplot()+scale_x_continuous(expand =c(0,0), limits =c(1998, current_year+.5), breaks =c(1998, 2005, 2010, 2015, 2020, current_year)) +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")
Add Other Fiscal Future group codes
Commented out line that creates Other departments from a few agencies. Keeping them seperate to see where healthcare costs go.
exp_temp <- exp_temp |>#mutate(agency = as.numeric(agency) ) |># arrange(agency)|>mutate(group =case_when( agency>"100"& agency<"200"~"910", # legislative agency =="528"| (agency>"200"& agency<"300") ~"920", # judicial ####################################################### Not used if we are not separating pension costs!!# pension > 0 ~ "901", # pensions## New CODE: April 23rd, 2025: agency =="593"~"959", # TRS becomes part of K-12 costs agency =="594"~"959", # TRS agency =="589"~"589", # SERS becomes part of "Other Agencies" agency =="693"~"960", # SURS becomes part of group 960 agency =="275"~"920", # JRS becomes part of group 920 agency =="131"~"910", # GARS becomes part of Group 910###################################################### (agency>"309"& agency<"400") ~"930", # elected officers: Governor, lt gov, attorney general, sec. of state, comptroller, treasurer agency =="586"~"959", # create new K-12 group agency=="402"| agency=="418"| agency=="478"| agency=="444"| agency=="482"~as.character(agency), # aging, CFS, HFS, human services, public health T ~as.character(group)) ) |>mutate(group =case_when( agency=="478"& (appr_org=="01"| appr_org =="65"| appr_org=="88") & (object=="4900"| object=="4400") ~"945", # separates CHIP from health and human services and saves it as Medicaid agency =="586"& fund =="0355"~"945", # 586 (Board of Edu) has special education which is part of medicaid# OLD CODE: agency == "586" & appr_org == "18" ~ "945", # Spec. Edu Medicaid Matching agency=="425"| agency=="466"| agency=="546"| agency=="569"| agency=="578"| agency=="583"| agency=="591"| agency=="592"| agency=="493"| agency=="588"~"941", # public safety & Corrections agency=="420"| agency=="494"| agency=="406"| agency=="557"~as.character(agency), # econ devt & infra, tollway agency=="511"| agency=="554"| agency=="574"| agency=="598"~"946", # Capital improvement agency=="422"| agency=="532"~as.character(agency), # environment & nat. resources agency=="440"| agency=="446"| agency=="524"| agency=="563"~"944", # business regulation agency=="492"~"492", # revenue agency =="416"~"416", # central management services agency=="448"& fy >2016~"416", #add DoIT to central management T ~as.character(group))) |>mutate(group =case_when(# agency=="684" | agency=="691" ~ as.character(agency), # moved under higher education in next line. 11/28/2022 AWM agency=="692"| agency =="693"| agency=="695"| agency =="684"|agency =="691"| (agency>"599"& agency<"677") ~"960", # higher education agency=="427"~as.character(agency), # employment security############################ # Leaving these agencies as their own agency number for now. Had been coded to "Other departments" Group 948# - GOMB (507) # - Human Rights (442) # - Illinois Power Agency (445) # - Labor (452) # - State Lottery (458) # - Veteran's Affairs (497) agency=="507"| agency=="442"| agency=="445"| agency=="452"|agency=="458"| agency=="497"~as.character(agency), # Were included within "other departments"# agency=="507"| agency=="442" | agency=="445" | agency=="452" |agency=="458" | agency=="497" ~ "948", # other departments############################################ other boards & Commissions agency=="503"| agency=="509"| agency=="510"| agency=="565"|agency=="517"| agency=="525"| agency=="526"| agency=="529"| agency=="537"| agency=="541"| agency=="542"| agency=="548"| agency=="555"| agency=="558"| agency=="559"| agency=="562"| agency=="564"| agency=="568"| agency=="579"| agency=="580"| agency=="587"| agency=="590"| agency=="527"| agency=="585"| agency=="567"| agency=="571"| agency=="575"| agency=="540"| agency=="576"| agency=="564"| agency=="534"| agency=="520"| agency=="506"| agency =="533"~"949", # Other Departments# Before pensions were included back with the original agency that spent the money, remaining non-pension expenditures from agencies that deal with pensions were included with Other Departments # agency=="131" |# agency=="275" | #JRS# agency=="589" | #SERS# agency=="593"| # TRS# agency=="594"| # Also TRS# agency=="693" #SURS# ~ "948", T ~as.character(group))) |>mutate(group_name =case_when( group =="416"~"Central Management", group =="442"~"Human Rights", group =="445"~"IL Power Agency", group =="452"~"Labor", group =="458"~"State Lottery", group =="589"~"SERS", group =="478"~"Healthcare and Family Services", group =="482"~"Public Health", group =="901"~"State Pension Contributions", ## Split up into GARS, SERS, etc. now group =="903"~"Debt Service", group =="910"~"Legistlative" , group =="920"~"Judicial" , group =="930"~"Elected Officers" , group =="941"~"Public Safet" , group =="942"~"Econ Development & Infrastructure" , group =="943"~"Central Services", group =="944"~"Business & Professional Regulation" , group =="945"~"Medicaid" , group =="946"~"Capital Improvement" , group =="948"~"Other Departments" , group =="949"~"Other Boards & Commissions" , group =="959"~"K-12 Education" , group =="960"~"University Education" , group == agency ~as.character(agency_name),TRUE~"Check name"),year = fy)exp_temp <- exp_temp |>mutate(fund_cat_name =case_when( fund_cat_name =="General Fund"~"General Funds", fund_cat_name =="REVOLVING FUNDS"~"Revolving Funds", T ~ fund_cat_name ),federal_funded =case_when( fund_cat_name =="Federal Trust Funds"~"Federal Funds", group_name =="MEDICAID"& fund_cat_name =="General Funds"~"Federal Funds", T ~"State Funds" ))exp_temp |>filter(group_name =="Check name")
Important
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.
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)))) |>mutate(fund_cat_name =case_when( fund_cat_name =="General Fund"~"General Funds", fund_cat_name =="REVOLVING FUNDS"~"Revolving Funds", T ~ fund_cat_name ))
object 4900 is Lump Sums, object 4400 is Awards & Grants.
Code
medicaid_cost_total <- exp_temp |>filter(agency=="478"& (appr_org=="01"| appr_org =="65"| appr_org=="88") & (object=="4900"| object=="4400")) |>group_by(fy) |>summarize(sum=sum(expenditure, na.rm=TRUE))medicaid_cost <- exp_temp |>filter(#agency=="478" & # (appr_org=="01" | appr_org == "65" | appr_org=="88") & (object=="4900"| object=="4400")) |>group_by(fy, agency) |>summarize(sum=sum(expenditure, na.rm=TRUE))ggplot()+geom_line(data=medicaid_cost_total, aes(x=fy, y = sum, color ="Expenditures"), lwd =1) +geom_line(data=medicaid_cost, aes(x=fy, y = sum, color = agency)) +scale_x_continuous(n.breaks =6) +labs(title ="Medicaid expenditures", caption ="Medicaid expenditures include funds provided to medical providers.", color =element_blank() )
Health Insurance Premiums from Employees
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
# 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.
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:
Clean up code and annotations in chunk below:
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
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)
Create exp_970 for all local government transfers (exp_971 + exp_972 + exp_975 + exp_976).
Code
ff_exp <- exp_temp |>group_by(fy, group) |>summarize(sum_expenditures =sum(expenditure, na.rm=TRUE)/1000000 ) |>pivot_wider(names_from ="group", values_from ="sum_expenditures", names_prefix ="exp_")|>left_join(debt_keep_yearly) |>rename(exp_903 = debt_cost) |># join local transfers and create exp_970left_join(transfers) |>mutate(exp_970 = exp_971 + exp_972 + exp_975 + exp_976) ff_exp<- ff_exp |>select(-c(exp_971:exp_976)) # drop unwanted columns that are already included in exp_970ff_exp # not labeled
Table 1: Pivoted Expenditure Categories ($ Millions). Intermediate step. Not actually used for anything other than having output similar to past STATA output.
ggplot() +geom_line(data = rev_fund_cats, aes(x=fy, y = Revenue)) +geom_line(data = exp_fund_cats, aes(x=fy, y = Expenditures), lty=2) +scale_y_billions(name ="$") +labs(caption ="Dashed line is General Fund Expenditures",title ="General Fund Fiscal Gap",subtitle ="Pretend there is a line showing there is a difference between the two lines.",legend ="",x =element_blank())
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.
Code
rev_long <-pivot_longer(ff_rev, rev_02:rev_78, names_to =c("type","Category"), values_to ="Dollars", names_sep ="_") |>rename(Year = fy) |>mutate(Category_name =case_when( Category =="02"~"INDIVIDUAL INCOME TAXES" , Category =="03"~"CORPORATE INCOME TAXES" , Category =="06"~"SALES TAXES" , Category =="09"~"MOTOR FUEL TAX" , Category =="12"~"PUBLIC UTILITY TAXES" , Category =="15"~"CIGARETTE TAXES" , Category =="18"~"LIQUOR GALLONAGE TAXES" , Category =="21"~"INHERITANCE TAX" , Category =="24"~"INSURANCE TAXES, FEES & LICENSES" , Category =="27"~"CORP FRANCHISE TAXES & FEES" , Category =="30"~"HORSE RACING TAXES & FEES", Category =="31"~"MEDICAL PROVIDER ASSESSMENTS" , Category =="32"~"GARNISHMENT-LEVIES" , # dropped Category =="33"~"LOTTERY RECEIPTS" , Category =="35"~"OTHER TAXES" , Category =="36"~"RECEIPTS FROM REVENUE PRODUCING", Category =="39"~"LICENSES, FEES & REGISTRATIONS" , Category =="42"~"MOTOR VEHICLE AND OPERATORS" , Category =="45"~"STUDENT FEES-UNIVERSITIES", # dropped Category =="48"~"RIVERBOAT WAGERING TAXES" , Category =="51"~"RETIREMENT CONTRIBUTIONS" , # dropped Category =="54"~"GIFTS AND BEQUESTS", Category =="57"~"FEDERAL OTHER" , Category =="58"~"FEDERAL MEDICAID", Category =="59"~"FEDERAL TRANSPORTATION" , Category =="60"~"OTHER GRANTS AND CONTRACTS", Category =="63"~"INVESTMENT INCOME", Category =="66"~"PROCEEDS, INVESTMENT MATURITIES" , #dropped Category =="72"~"BOND ISSUE PROCEEDS", #dropped Category =="75"~"INTER-AGENCY RECEIPTS ", #dropped Category =="76"~"TRANSFER IN FROM OUT FUNDS", Category =="78"~"ALL OTHER SOURCES" , Category =="79"~"COOK COUNTY IGT", #dropped Category =="98"~"PRIOR YEAR REFUNDS", #dropped T ~"Check Me!" ) )|>mutate(Category_name =str_to_title(Category_name))exp_long <-pivot_longer(ff_exp, exp_402:exp_970 , names_to =c("type", "Category"), values_to ="Dollars", names_sep ="_") |>rename(Year = fy ) |>mutate(Category_name =case_when( Category =="131"~"GARS", # should be in Legislative Group already Category =="275"~"JRS", # should be in Judicial Category already Category =="402"~"Aging", Category =="406"~"Agriculture", # agriculture Category =="416"~"Central Management", ## contains DoIT also Category =="418"~"Children & Family Services", Category =="420"~"Commerce & Economic Opportunity", Category =="422"~"Natural Resources" , Category =="426"~"Corrections", Category =="427"~"Employment Security" , Category =="442"~"Human Rights" , # sometimes included in "Other Departments" when trying to have fewer expenditure categories Category =="444"~"Human Services" , Category =="445"~"IL Power Agency" , # IL Power Agency Category =="448"~"Innovation & Technology", # should be in Central Management already Category =="452"~"Labor" , # Sometimes included in "Other Departments when trying to have fewer categories Category =="458"~"State Lottery" , # State Lottery is sometimes included as "Other Departments when trying to have fewer expenditure categories Category =="478"~"Family Services (net Medicaid)", Category =="482"~"Public Health", Category =="492"~"Revenue", Category =="493"~"Teacher Retirement System (TRS)", # Should be included in K-12 already Category =="494"~"Transportation" , Category =="507"~"GOMB", # GOMB # GOMB is sometimes included as "Other Departments when trying to have fewer expenditure categories Category =="497"~"Veterans' Affairs", # Veterans' Affairs is sometimes included as "Other Departments when trying to have fewer expenditure categories Category =="532"~"Environmental Protection Agency" , Category =="557"~"IL State Tollway" , Category =="589"~"State Emp. Retirement System (SERS)", Category =="693"~"SURS", # should be in Higher Education already Category =="901"~"Other Pension Expenditure", Category =="903"~"Debt Service", Category =="904"~"State Employee Healthcare", Category =="910"~"Legislative" , Category =="920"~"Judicial" , Category =="930"~"Elected Officers" , Category =="941"~"Public Safety" , Category =="943"~"Central Services", Category =="944"~"Business & Professional Regulation" , Category =="945"~"Medicaid" , Category =="946"~"Capital Improvements" , Category =="948"~"Other Departments" , # Used when pre-grouping small agencies to group = 948. Category =="949"~"Other Boards & Commissions" , Category =="959"~"K-12 Education" , Category =="960"~"University Education", Category =="970"~"Local Govt Transfers", T ~"CHECK ME!") ) |> dplyr::mutate(Dollars =ifelse(is.na(Dollars), 0, Dollars))# combine revenue and expenditures into one data frameaggregated_totals_long <-rbind(rev_long, exp_long)# # if(any(aggregated_totals_long$Category_name == "CHECK ME!" & aggregated_totals_long$Dollars>0)){# # aggregated_totals_long <- aggregated_totals_long# # }else{# aggregated_totals_long <- aggregated_totals_long |> # filter(!Category_name %in% c("CHECK ME!", "Check Me!")) |># mutate(Dollars = ifelse(is.na(Dollars), 0, Dollars))# }# # if(!any(aggregated_totals_long$Category_name == "CHECK ME!" & # (aggregated_totals_long$Dollars > 0 | is.na(aggregated_totals_long$Dollars)) ) ) {# # aggregated_totals_long <- aggregated_totals_long |># filter(!Category_name %in% c("CHECK ME!", "Check Me!")) |># mutate(Dollars = ifelse(is.na(Dollars), 0, Dollars))# }if (any(aggregated_totals_long$Category_name =="CHECK ME!"& aggregated_totals_long$Dollars >0, na.rm =TRUE)) {stop("CHECK ME! category contains positive dollars. Investigate the categorization.")} else { aggregated_totals_long <- aggregated_totals_long |> dplyr::filter(!Category_name %in%c("CHECK ME!", "Check Me!"))}aggregated_totals_long |>mutate(`Dollars (Millions)`=round(Dollars, digits =0)) |>select(-Dollars) |>select(Year, Category_name, `Dollars (Millions)`, type, Category)
Table 2: Long Version of Data that has Revenue and Expenditures in One Dataframe. Creates expenditures_recoded_long_pensionchange_FY, revenues_recoded_long_pensionchange_FY and aggregated_totals_pensionchange which are exported as CSVs.
Code
year_totals <- aggregated_totals_long |>group_by(type, Year) |>summarize(Dollars =sum(Dollars, na.rm =TRUE)) |>pivot_wider(names_from ="type", values_from = Dollars) |>rename(Expenditures = exp,Revenue = rev) |>mutate(`Fiscal Gap`= Revenue - Expenditures)# creates variable for the Gap each yearyear_totals |>mutate_all(round, digits =0) |>kbl(caption ="Fiscal Gap for each Fiscal Year ($ Millions)") |>kable_styling(bootstrap_options =c("striped")) |>kable_classic() |>add_footnote(c("Values include State CURE dollars (SLFRF)") )
Table 3: Year totals with Fiscal Futures methodology for excluding/including revenue and expenditure objects.
Fiscal Gap for each Fiscal Year ($ Millions)
Year
Expenditures
Revenue
Fiscal Gap
1998
31241
32028
787
1999
33845
33964
118
2000
37341
37041
-299
2001
40354
38279
-2075
2002
42064
37919
-4144
2003
42608
38449
-4159
2004
53019
42605
-10414
2005
45359
44302
-1057
2006
48059
46166
-1894
2007
51128
49490
-1638
2008
54170
51637
-2533
2009
56750
51461
-5289
2010
58048
51192
-6856
2011
58419
56299
-2120
2012
59861
58418
-1443
2013
63285
63097
-188
2014
66963
65264
-1699
2015
69937
66585
-3353
2016
63928
64149
221
2017
71724
63654
-8070
2018
74966
73009
-1958
2019
74402
74632
230
2020
81603
80582
-1021
2021
92883
95201
2317
2022
100065
116056
15991
2023
111972
111766
-206
2024
114997
115121
124
2025
119505
118157
-1348
a Values include State CURE dollars (SLFRF)
Graphs made from aggregated_totals_long dataframe.
Fiscal Gap Graph
Code
## Adjust x and y coordinates to move placement of textannotation <-data.frame(x =c(2004, 2017, 2019),y =c(60, 50, 5), label =c("Expenditures","Revenue", "Fiscal Gap"))annotation_nums <-data.frame(x =c(2025, 2025, 2025),y =c(91, 123, -5), label =c( year_totals$Expenditures[year_totals$Year==current_year]/1000, year_totals$Revenue[year_totals$Year==current_year]/1000, year_totals$`Fiscal Gap`[year_totals$Year==current_year]/1000))## Dashed line versions for expenditures: fiscal_gap <-ggplot(data = year_totals, aes(x=Year, y = Revenue/1000)) +geom_recessions(text =FALSE, update = recessions)+# geom_smooth adds regression line, graphed first so it appears behind line graphgeom_smooth(aes(x = Year, y = Revenue/1000), color ="gray", alpha =0.7, method ="lm", se =FALSE) +# scale_linetype_manual(values="dashed")+geom_smooth(aes(x = Year, y = Expenditures/1000), color ="rosybrown2", linetype ="dotted", method ="lm", se =FALSE, alpha =0.7) +# line graph of revenue and expendituresgeom_line(aes(x = Year, y = Revenue/1000), color ="Black", size=1) +geom_line(aes(x = Year, y = Expenditures/1000, linetype ="dashed"), color ="red", lwd=1) +geom_line(aes(x = Year, y = (`Fiscal Gap`/1000)), color ="darkgray", lwd =1) +geom_hline(yintercept =0) +geom_text(data = annotation, aes(x=x, y=y, label=label,parse =TRUE))+theme(legend.position ="bottom", legend.title =element_blank())+scale_linetype_manual(values =c("dashed", "dashed")) +scale_x_continuous(expand =c(0,0),limits =c(1998, current_year+.5)) +xlab("Year") +ylab("Billions of Dollars") +ggtitle(paste0("Illinois Expenditures and Revenue Totals, 1998-",current_year))fiscal_gap## Adjust x and y coordinates to move placement of textannotation <-data.frame(x =c(2024, 2024, 2024),y =c(101, 130, 10), label =c("Expenditures","Revenue", "Fiscal Gap"))fiscal_gap2 <-ggplot(data = year_totals, aes(x=Year, y = Revenue/1000)) +geom_recessions(text =FALSE, update_recessions = recessions)+geom_line(aes(x = Year, y = Revenue/1000, color ="Revenue"), lwd =1, label ="Revenue") +geom_line(aes(x = Year, y = Expenditures/1000, color ="Expenditures"), linetype ="dotted", lwd =1, label ="Expenditures") +geom_line(aes(x = Year, y = (`Fiscal Gap`/1000)), color ="darkgray", lwd=1) +geom_text(data = annotation, aes(x=x, y=y, label=label)) +## Word locations and textgeom_text(data = annotation_nums, aes(x = x, y = y, label = scales::dollar(label, accuracy =0.01L)), size =3) +## Number locations and texttheme_classic() +theme(legend.position ="bottom", legend.title =element_blank()) +scale_color_manual(values =c("Revenue"="black", "Expenditures"="red")) +scale_linetype_manual(values =c("Revenue"="solid", "Expenditures"="dotted")) +geom_hline(yintercept =0) +scale_y_continuous(#labels = comma, limits =c(-12, 130), breaks =c(-10, 20, 40, 60, 80, 100, 120), minor_breaks =c(-10, 0, 10, 30, 50, 70, 90, 110))+scale_x_continuous(expand =c(0,0), limits =c(1998, current_year+1) ) +# scale_color_manual(values = c("red" = "Expenditures", "black" = "Revenue")) + xlab("Year") +ylab("Billions of Dollars") +ggtitle(paste0("Illinois Expenditures and Revenue Totals, 1998-",current_year))fiscal_gap2
Figure 7: Fiscal Gap Comparison
(a) Fiscal Gap With Trend Lines
(b) Fiscal Gap Without Trend Lines
Downloadable Tables
Code
aggregate_rev_labeled <- rev_long |>filter(Category_name !="CHECK ME!") |>group_by(Year, Category, Category_name) |>mutate(Dollars =round(Dollars)) |>arrange(Category) |>pivot_wider(names_from ="Year", values_from ="Dollars")datahub_rev <- aggregate_rev_labeled |>select(-type)datahub_rev |> DT::datatable(rownames =FALSE,extensions ='Buttons',options =list(dom ='Bfrtip', # 'B' for buttons, 'f' for filtering, 'r' for processing, 't' for table, 'i' for info, 'p' for paginationbuttons =c('copy', 'csv', 'excel', 'pdf', 'print'), # Specify the download formatslengthMenu =list(c(10, 25, 50, -1),c(10, 25, 50, "All") # Display options for page length ) ) )
Table 4: Fiscal Futures Revenue Categories. As of March 2026, this is now the downloadable table on IGPA Datahub site. Totals are in millions of nominal dollars.
Code
aggregate_exp_labeled <- exp_long |>filter(Category_name !="CHECK ME!") |>group_by(Year, Category, Category_name) |>mutate(Dollars =round(Dollars)) |>arrange(Category) |>pivot_wider(names_from ="Year", values_from ="Dollars")datahub_exp <- aggregate_exp_labeled |>select(-type)datahub_exp |> DT::datatable(rownames =FALSE,extensions ='Buttons',options =list(dom ='Bfrtip', # 'B' for buttons, 'f' for filtering, 'r' for processing, 't' for table, 'i' for info, 'p' for paginationbuttons =c('copy', 'csv', 'excel', 'pdf', 'print'), # Specify the download formatslengthMenu =list(c(10, 25, 50, -1),c(10, 25, 50, "All") # Display options for page length ) ) )
Table 5: Fiscal Futures Grouped Expenditure Categories. As of March 2026, this is now the downloadable table on IGPA Datahub site. Totals are in millions of nominal dollars.
Expenditure and revenue amounts in billions of dollars:
Code
exp_long |>filter(Year == current_year & Dollars >0 ) |>arrange(desc(`Dollars`)) |>ggplot() +geom_col(aes(x =fct_reorder(Category_name, `Dollars`), y = (`Dollars`/1000), fill ="red"))+coord_flip() +theme_classic()+theme(legend.position ="none") +labs(title =paste0("Expenditures for ", current_year))+xlab("Expenditure Categories") +ylab("Billions of Dollars") rev_long |>filter(Year == current_year & Dollars >0 ) |>arrange(desc(`Dollars`)) |>ggplot() +geom_col(aes(x =fct_reorder(Category_name, `Dollars`), y = (`Dollars`/1000)))+coord_flip() +theme_classic() +theme(legend.position ="none") +labs(title =paste0("Revenue for ", current_year))+xlab("Revenue Categories") +ylab("Billions of Dollars")
(a) FY25 Expenditures
(b) FY25 Revenue Sources
Figure 8: FY25 Totals
Expenditure and revenues when focusing on largest categories and combining others into “All Other Expenditures(Revenues)”:
Code
exp_long |>filter( Year == current_year & Dollars >0 ) |>mutate(rank =rank(Dollars),Category_name =ifelse(rank >13| Category !="589", Category_name, 'All Other Expenditures')) |># select(-c(Year, Dollars, rank)) |>arrange(desc(Dollars)) |>ggplot() +geom_col(aes(x =fct_reorder(Category_name, `Dollars`), y =`Dollars`/1000), fill ="rosybrown2") +coord_flip() +theme_classic() +labs(title =paste0("Expenditures for ", current_year))+xlab("") +ylab("Billions of Dollars")rev_long |>filter( Year == current_year & Dollars >0 ) |>mutate(rank =rank(Dollars),Category_name =ifelse(rank >10, Category_name, 'All Other Sources')) |>arrange(desc(Dollars)) |>ggplot() +geom_col(aes(x =fct_reorder(Category_name, `Dollars`/1000), y =`Dollars`/1000), fill ="dark gray")+coord_flip() +theme_classic() +labs(title =paste0("Revenue for ", current_year)) +xlab("") +ylab("Billions of Dollars")
(a) Largest Expenditures for FY2025
(b) Largest Revenue Sources for FY2025
Figure 9: Largest Groups
Expenditure and revenues when focusing on largest categories and combining others into “All Other Expenditures(Revenues)”:
Code
n_categories <-10# keep top 10 largest category names for bar graphcurrent_year <-2025# list of top 10 expenditure categoriescategories <- exp_long |>filter(Year ==2025 ) |>arrange(desc(Dollars)) |>slice(1:10)exp_temp |>group_by(year, group, group_name, fund_cat_name) |>summarize(sum_expenditure =sum(expenditure)/1000000) |>arrange(year) |>pivot_wider(names_from ="group_name", values_from ="sum_expenditure") |>filter(fund_cat_name =="Federal Trust Funds")
Key funds: Healthcare Provider Relief (0793) and Hospital Provider (0346) 0365 is Health and Human Services Medicaid TR ? 0740 is Medicaid Buy in Program
Healthcare provider taxes come from revenue source 0133.
2104 = Medicare Part D 2683 = MCO Provider Assessment –> Mostly goes to Healthcare Provider Relief Fund 0793 in HFS 2526 = Hospital Provider Fund (not used in fy2025)
0133 = Health Care Provider Tax –> Mostly goes to Hospital Provider Fund 0346 0137 = Health Care Prov-Hospital (not used in fy2025) 0145 = IHFA Medicaid Provider (not used in fy2025)
Illinois also received:
over 500 million for TANF grant (source = 1393).
Also receives food stamp funds, over 500 million for “Medical Administration” source 0675. This mostly goes to the General Revenue Fund.
around $1.3 billion for “Medical Assistance” (source 0692, which goes to Drug Rebate Fund 0728)
an additional $3.3 billion in medical assistance from source 0676 which mostly goes to the General Revenue fund (0001) and a little goes to Tobacco settlement recovery fund
Each year, you need to increase the cagr value by 1. The value should be the (current year - 1998). For FY23, this is 2023-1998 = 25. So all cagr values that were 24 will be changed to 25.
Code
max_cagr_years = current_year-1998# function for calculating the CAGRcalc_cagr <-function(df, n) { df <- df |>arrange(Category_name, Year) |>group_by(Category_name) |>mutate(cagr = ((`Dollars`/lag(`Dollars`, n)) ^ (1/ n)) -1,cagr =ifelse(is.na(cagr), 0, cagr))return(df)}cagr_calculations <-function(df){ # This works for one variable at a time df <- df cagr_max <-calc_cagr(df, max_cagr_years) |>summarize(cagr_max =round(sum(cagr*100, na.rm =TRUE), 2))# Update year in the filter() and summarize() commands to current year. cagr_10 <-calc_cagr(df, 10) |>filter(Year == current_year) |>summarize(cagr_10 =case_when(Year == current_year ~round(sum(cagr*100, na.rm =TRUE), 2))) cagr_5 <-calc_cagr(df, 5) |>filter(Year == current_year) |>summarize(cagr_5 =case_when(Year == current_year ~round(sum(cagr*100, na.rm =TRUE), 2))) cagr_3 <-calc_cagr(df, 3) |>filter(Year == current_year) |>summarize(cagr_3 =case_when(Year == current_year ~round(sum(cagr*100, na.rm =TRUE), 2))) cagr_2 <-calc_cagr(df, 2) |>filter(Year == current_year) |>summarize(cagr_2 =case_when(Year == current_year ~round(sum(cagr*100, na.rm =TRUE), 2))) cagr_1 <-calc_cagr(df, 1) |>filter(Year == current_year) |>summarize(cagr_1 =case_when(Year == current_year ~round(sum(cagr*100, na.rm =TRUE), 2)))# Combine all into one tibble result <-data.frame(cagr_max, cagr_10, cagr_5, cagr_3, cagr_2, cagr_1)return(result)}
Code
CAGR_expenditures_summary_tot <-cagr_calculations(exp_long) |>filter(Category_name !="CHECK ME") |># You should havec checked that there were no unlabeled expenditures or revenues way before this point!! select(-c(Category_name.1, Category_name.2, Category_name.3, Category_name.4, Category_name.5 )) |>rename("Expenditure Category"= Category_name, "1 Year CAGR"= cagr_1, "2 Year CAGR"= cagr_2, "3 Year CAGR"= cagr_3, "5 Year CAGR"= cagr_5, "10 Year CAGR"= cagr_10, "27 Year CAGR"= cagr_max )totalrow <-which(grepl("Total", CAGR_expenditures_summary_tot$`Expenditure Category`))CAGR_expenditures_summary_tot <-move_to_last(CAGR_expenditures_summary_tot, totalrow) lastrow =nrow(CAGR_expenditures_summary_tot)CAGR_expenditures_summary_tot |>kbl(caption ="CAGR Calculations for All Expenditure Categories" , row.names=FALSE) |>kable_classic() |>row_spec(lastrow, bold = T, color ="black", background ="gray")
Table 7: Expenditure Category CAGRs with Total CAGR (Ordered Alphabetically)
CAGR Calculations for All Expenditure Categories
Expenditure Category
27 Year CAGR
10 Year CAGR
5 Year CAGR
3 Year CAGR
2 Year CAGR
1 Year CAGR
Aging
8.02
5.28
10.14
13.70
11.85
11.93
Agriculture
2.31
6.62
13.26
14.58
23.02
23.66
Business & Professional Regulation
2.22
-1.08
7.53
8.82
10.05
12.58
Capital Improvement
4.97
2.35
25.04
30.53
20.73
14.73
Central Management
4.83
5.49
5.07
9.59
5.72
9.13
Children & Family Services
1.46
5.57
9.19
15.35
10.37
7.13
Commerce and Economic Development
5.22
6.33
24.25
9.70
10.71
14.02
Corrections
2.42
3.11
4.12
7.82
5.11
2.99
Debt Service
5.32
-0.26
0.16
-0.78
0.29
-14.24
Elected Officials
4.42
5.13
8.42
1.22
-4.61
11.60
Employment Security
1.67
2.64
3.70
1.07
2.32
6.72
Environmental Protection Agency
4.44
4.56
6.87
15.33
27.53
13.75
Healthcare & Family Services (Net Medicaid)
5.66
1.53
7.35
9.89
8.19
7.88
Human Services
3.97
6.67
13.42
15.65
12.90
7.53
Judicial
4.17
4.83
6.47
8.88
6.20
7.21
K-12 Education
5.09
5.65
5.16
3.15
0.81
-1.43
Legislative
5.26
8.90
14.56
17.46
2.53
3.18
Local Govt Revenue Sharing
3.60
3.71
6.97
-4.39
-8.92
-6.35
Medicaid
7.27
7.83
10.03
7.85
5.32
7.48
Natural Resources
3.02
3.57
9.78
15.01
17.38
16.24
Other Boards & Commissions
5.30
4.35
12.56
15.77
7.79
8.85
Other Departments
2.71
10.82
20.76
29.35
25.36
-1.56
Public Health
6.01
6.76
6.82
-2.39
0.75
6.57
Public Safety
5.27
8.36
3.26
0.51
0.17
-19.90
Revenue
3.86
10.31
1.18
-13.12
-37.27
-11.59
State Employee Healthcare
6.32
4.51
5.05
8.28
12.72
20.62
State Employment Retirement (SERS)
22.33
6.48
4.61
5.21
8.84
15.61
State Lottery
4.28
-0.56
13.55
12.56
-20.26
-11.31
Tollway
6.33
0.16
0.08
-2.89
0.82
-2.62
Transportation
4.63
3.99
10.43
13.61
12.53
13.38
University Education
3.00
2.85
4.58
5.14
4.27
1.20
Veterans' Affairs
4.12
3.50
5.23
9.49
11.43
9.49
Total
5.09
5.50
7.93
6.10
3.31
3.92
Code
CAGR_revenue_summary_tot <-cagr_calculations(rev_long) |>filter(Category_name !="CHECK ME") |># You should havec checked that there were no unlabeled expenditures or revenues way before this point!! select(-c(Category_name.1, Category_name.2, Category_name.3, Category_name.4, Category_name.5 )) |>rename("Revenue Category"= Category_name, "1 Year CAGR"= cagr_1, "2 Year CAGR"= cagr_2, "3 Year CAGR"= cagr_3, "5 Year CAGR"= cagr_5, "10 Year CAGR"= cagr_10, "27 Year CAGR"= cagr_max )CAGR_revenue_summary_tot <-move_to_last(CAGR_revenue_summary_tot, 1)totalrow <-which(grepl("Total", CAGR_revenue_summary_tot$`Revenue Category`))CAGR_revenue_summary_tot <-move_to_last(CAGR_revenue_summary_tot, totalrow)lastrow =nrow(CAGR_revenue_summary_tot)CAGR_revenue_summary_tot |>kbl(caption ="CAGR Calculations for All Revenue Sources (Ordered Alphabetical)", row.names =FALSE) |>kable_classic() |>row_spec(lastrow, bold = T, color ="black", background ="gray")
Table 8: Revenue Category CAGRs with Total CAGR (Ordered Alphabetically)
CAGR Calculations for All Revenue Sources (Ordered Alphabetical)
Revenue Category
27 Year CAGR
10 Year CAGR
5 Year CAGR
3 Year CAGR
2 Year CAGR
1 Year CAGR
Cigarette Taxes
1.32
-2.61
-4.91
-7.71
-8.20
-6.58
Corp Franchise Taxes & Fees
1.93
-0.66
-1.22
-2.95
-6.39
-2.61
Corporate Income Taxes
5.01
5.54
13.84
-11.48
-18.56
-16.23
Federal Medicaid
6.97
6.97
8.26
2.62
0.93
-3.72
Federal Other
4.28
6.45
3.65
-15.71
3.30
10.68
Federal Transportation
4.50
3.04
8.97
14.34
13.86
16.11
Gifts And Bequests
10.50
11.33
17.40
3.35
-1.23
-16.34
Horse Racing Taxes & Fees
-6.10
1.56
2.36
-3.08
0.32
-0.64
Individual Income Taxes
5.64
6.59
8.98
4.44
9.10
10.03
Inheritance Tax
3.31
6.10
16.30
-0.03
9.51
-3.93
Insurance Taxes, Fees & Licenses
6.62
4.80
9.39
7.15
6.61
13.43
Investment Income
6.18
38.91
40.04
157.55
36.97
11.20
Licenses, Fees & Registrations
7.71
6.36
9.80
6.29
4.84
-3.69
Liquor Gallonage Taxes
6.38
0.68
0.02
-1.80
-2.12
-2.60
Lottery Receipts
2.10
1.49
4.77
1.66
-3.09
-8.88
Medical Provider Assessments
8.33
9.16
6.29
8.07
7.36
8.55
Motor Fuel Tax
3.08
8.61
4.96
5.33
7.22
4.67
Motor Vehicle And Operators
2.95
0.63
2.38
0.88
1.33
0.15
Other Grants And Contracts
2.70
29.93
24.22
165.59
79.20
113.69
Other Taxes
8.11
12.57
19.52
11.44
10.68
17.71
Public Utility Taxes
0.79
0.01
0.74
1.53
1.18
2.65
Receipts From Revenue Producing
5.74
4.50
8.61
11.24
12.65
9.05
Riverboat Wagering Taxes
2.57
-1.09
5.07
9.35
9.34
15.57
Sales Taxes
3.17
4.12
6.35
2.57
1.46
1.67
All Other Sources
6.40
5.71
11.91
7.53
-1.02
-1.16
Total
4.95
5.90
7.96
0.60
2.82
2.64
Code
first_year =as.numeric(1998)n_year_change =as.numeric(current_year-1998)revenue_change2 <- rev_long |>filter(Year >= past_year | Year == first_year) |>filter(Category_name !="CHECK ME") |># You should havec checked that there were no unlabeled expenditures or revenues way before this point!! pivot_wider(names_from = Year , values_from = Dollars, names_prefix ="Dollars_") |>rename( Dollars_current = Dollars_2025,Dollars_lastyear = Dollars_2024 )|>mutate("Current FY ($ billions)"=round(Dollars_current/1000, digits =2),"Past FY ($ billions)"=round(Dollars_lastyear/1000, digits =2),"FY 1994 ($ billions)"=round(Dollars_1998/1000, digits =2),"1-Year Change"=percent((Dollars_current -Dollars_lastyear)/Dollars_lastyear, accuracy = .01)) |>left_join(CAGR_revenue_summary_tot, by =c("Category_name"="Revenue Category")) |>arrange(-`Current FY ($ billions)`)|>mutate(`27 Year CAGR`=percent(`27 Year CAGR`/100, accuracy=.01)) |>rename( "Revenue Category"= Category_name ) |>select(-c( Dollars_1998, Dollars_current, Dollars_lastyear, `1 Year CAGR`:`10 Year CAGR`))allother_row <-which(grepl("All Other", revenue_change2$`Revenue Category`))revenue_change2 <-move_to_last(revenue_change2, allother_row) # Move "All Other" to 2nd to last rowtotalrow <-which(grepl("Total", revenue_change2$`Revenue Category`))revenue_change2 <-move_to_last(revenue_change2, totalrow) # Move "Total" to last rowlastrow =nrow(revenue_change2)Table2 <- revenue_change2 |>filter(!is.na(`Revenue Category`)) |>kbl(caption ="Table 1. Yearly Change in Revenue (All Sources)", row.names =FALSE) |>kable_classic() |>row_spec(lastrow, bold = T, color ="black", background ="gray")#save_kable(Table2, file = "generated/App1_AllRevenueSources.html", self_contained = TRUE)Table2
Table 9: Yearly Change in Revenues - All FF Categories, Ordered from Largest to Smallest Revenue Amount
Table 1. Yearly Change in Revenue (All Sources)
Revenue Category
Current FY ($ billions)
Past FY ($ billions)
FY 1994 ($ billions)
1-Year Change
27 Year CAGR
Individual Income Taxes
30.13
27.38
6.85
10.03%
5.64%
Federal Medicaid
20.58
21.38
3.34
-3.72%
6.97%
Sales Taxes
16.70
16.43
7.20
1.67%
3.17%
Federal Other
11.61
10.49
3.75
10.68%
4.28%
Corporate Income Taxes
6.95
8.30
1.86
-16.23%
5.01%
Medical Provider Assessments
4.71
4.34
0.54
8.55%
8.33%
Receipts From Revenue Producing
3.29
3.01
0.73
9.05%
5.74%
Motor Fuel Tax
2.95
2.82
1.30
4.67%
3.08%
Federal Transportation
2.74
2.36
0.84
16.11%
4.50%
Licenses, Fees & Registrations
2.26
2.35
0.30
-3.69%
7.71%
Gifts And Bequests
2.05
2.45
0.14
-16.34%
10.50%
Other Taxes
2.01
1.70
0.24
17.71%
8.11%
Motor Vehicle And Operators
1.64
1.64
0.75
0.15%
2.95%
Public Utility Taxes
1.48
1.44
1.19
2.65%
0.79%
Lottery Receipts
1.46
1.61
0.83
-8.88%
2.10%
Investment Income
1.40
1.26
0.28
11.20%
6.18%
Insurance Taxes, Fees & Licenses
0.75
0.66
0.13
13.43%
6.62%
Cigarette Taxes
0.66
0.71
0.46
-6.58%
1.32%
Inheritance Tax
0.60
0.63
0.25
-3.93%
3.31%
Riverboat Wagering Taxes
0.42
0.36
0.21
15.57%
2.57%
Liquor Gallonage Taxes
0.30
0.31
0.06
-2.60%
6.38%
Corp Franchise Taxes & Fees
0.20
0.21
0.12
-2.61%
1.93%
Horse Racing Taxes & Fees
0.01
0.01
0.04
-0.64%
-6.10%
Other Grants And Contracts
0.01
0.00
0.00
113.69%
2.70%
All Other Sources
3.24
3.28
0.61
-1.16%
6.40%
Total
118.16
115.12
32.03
2.64%
4.95%
Code
expenditure_change2 <- exp_long |>filter(Category_name !="CHECK ME") |># You should havec checked that there were no unlabeled expenditures or revenues way before this point!! group_by(Year, Category_name) |>summarize(Dollars =sum(Dollars, na.rm=TRUE)) |>ungroup() |>filter(Year >= past_year | Year == first_year ) |>pivot_wider(names_from = Year , values_from = Dollars, names_prefix ="Dollars_") |>rename( Dollars_current = Dollars_2025,Dollars_lastyear = Dollars_2024 )|>mutate("FY 2025 ($ billions)"=round(Dollars_current/1000, digits =2),"FY 2024 ($ billions)"=round(Dollars_lastyear/1000, digits =2),"FY 1998 ($ billions)"=round(Dollars_1998/1000, digits =2),"1-Year Change"=percent((Dollars_current -Dollars_lastyear)/Dollars_lastyear, accuracy = .01)) |>left_join(CAGR_expenditures_summary_tot, by =c("Category_name"="Expenditure Category")) |>arrange(-`FY 2025 ($ billions)`)|>mutate(`27 Year CAGR`=percent(`27 Year CAGR`/100, accuracy=.01)) |>select(-c( Dollars_1998, Dollars_current, Dollars_lastyear, `1 Year CAGR`:`10 Year CAGR`)) |>rename("Expenditure Category"= Category_name ) # |> filter(!is.na(`Expenditure Category`))allother_row <-which(grepl("All Other", expenditure_change2$`Expenditure Category`))expenditure_change2 <-move_to_last(expenditure_change2, allother_row) # Move "All Other" to 2nd to last rowtotalrow <-which(grepl("Total", expenditure_change2$`Expenditure Category`))expenditure_change2 <-move_to_last(expenditure_change2, totalrow) # Move "Total" to last rowlastrow =nrow(expenditure_change2)Table3<- expenditure_change2 |>kbl(row.names =FALSE) |>kable_classic() |>row_spec(lastrow, bold = T, color ="black", background ="gray")#save_kable(Table3, file = "generated/App2_AllExpenditureCategories.html", self_contained = TRUE)Table3
Table 10: Yearly Change in Expenditures - All FF Categories, Ordered from Largest to Smallest Expenditure Amount
Expenditure Category
FY 2025 ($ billions)
FY 2024 ($ billions)
FY 1998 ($ billions)
1-Year Change
27 Year CAGR
Medicaid
35.94
33.44
5.40
7.48%
7.27%
K-12 Education
21.40
21.71
5.60
-1.43%
5.09%
Human Services
11.25
10.47
3.93
7.53%
3.97%
Local Govt Revenue Sharing
9.04
9.66
3.48
-6.35%
3.60%
Transportation
6.70
5.91
1.98
13.38%
4.63%
University Education
5.08
5.02
2.28
1.20%
3.00%
State Employee Healthcare
3.81
3.16
0.73
20.62%
6.32%
State Employment Retirement (SERS)
2.15
1.86
0.01
15.61%
22.33%
Debt Service
1.96
2.29
0.48
-14.24%
5.32%
Tollway
1.93
1.98
0.37
-2.62%
6.33%
Children & Family Services
1.92
1.79
1.30
7.13%
1.46%
Corrections
1.88
1.83
0.99
2.99%
2.42%
Commerce and Economic Development
1.84
1.61
0.47
14.02%
5.22%
Public Safety
1.75
2.18
0.44
-19.90%
5.27%
Aging
1.73
1.54
0.22
11.93%
8.02%
Central Management
1.52
1.40
0.43
9.13%
4.83%
Elected Officials
1.33
1.19
0.41
11.60%
4.42%
Revenue
1.22
1.39
0.44
-11.59%
3.86%
Environmental Protection Agency
1.00
0.88
0.31
13.75%
4.44%
Capital Improvement
0.95
0.83
0.26
14.73%
4.97%
State Lottery
0.89
1.00
0.29
-11.31%
4.28%
Judicial
0.85
0.79
0.28
7.21%
4.17%
Public Health
0.78
0.73
0.16
6.57%
6.01%
Healthcare & Family Services (Net Medicaid)
0.50
0.46
0.11
7.88%
5.66%
Natural Resources
0.44
0.38
0.20
16.24%
3.02%
Other Boards & Commissions
0.38
0.35
0.09
8.85%
5.30%
Business & Professional Regulation
0.28
0.25
0.15
12.58%
2.22%
Employment Security
0.28
0.27
0.18
6.72%
1.67%
Legislative
0.25
0.24
0.06
3.18%
5.26%
Veterans' Affairs
0.17
0.15
0.06
9.49%
4.12%
Agriculture
0.14
0.11
0.07
23.66%
2.31%
Other Departments
0.12
0.12
0.06
-1.56%
2.71%
Total
119.51
115.00
31.24
3.92%
5.09%
Summary Tables - Largest Categories
The 10 largest revenue sources and 15 largest expenditure sources remain separate categories and all other smaller sources/expenditures are combined into “All Other Revenues (Expenditures)”. These condensed tables are typically used in the Fiscal Futures articles. They were manually created in past years but this hopefully automates the process a bit until final formatting stages.
Table 11: Largest Revenue Categories with CAGRs
Code
n_categories <-10+1# (Top 10 and then Total )categories <- rev_long |>filter(Year == current_year ) |>arrange(desc(Dollars)) |>slice(1:n_categories)rev_majorcats <- rev_long |>filter( (Year == current_year | Year == first_year)& Category_name %in% categories$Category_name) rev_long_majorcats <- rev_long |>mutate(Category_name =ifelse(Category_name %in% rev_majorcats$Category_name, Category_name, "All Other Sources"),Category_name =ifelse(Category_name =="Total", "Total Revenue", Category_name)) |>group_by(Year, Category_name) |>summarize(Dollars =sum(Dollars, na.rm=TRUE))# creates wide version of table where each revenue source is a columnrevenue_wide_majorcats <- rev_long_majorcats |>pivot_wider(names_from = Category_name, values_from = Dollars) |>relocate("All Other Sources", .after =last_col()) |>relocate("Total Revenue", .after =last_col())
Code
CAGR_revenue_majorcats_tot <-cagr_calculations(rev_long_majorcats) |>select(-c(Category_name.1, Category_name.2, Category_name.3, Category_name.4, Category_name.5 )) |>rename("Revenue Category"= Category_name, "1 Year CAGR"= cagr_1, "2 Year CAGR"= cagr_2, "3 Year CAGR"= cagr_3, "5 Year CAGR"= cagr_5, "10 Year CAGR"= cagr_10, "27 Year CAGR"= cagr_max )allother_row <-which(grepl("All Other", CAGR_revenue_majorcats_tot$`Revenue Category`))CAGR_revenue_majorcats_tot <-move_to_last(CAGR_revenue_majorcats_tot, allother_row) # Move "All Other" to 2nd to last rowtotalrow <-which(grepl("Total", CAGR_revenue_majorcats_tot$`Revenue Category`))CAGR_revenue_majorcats_tot <-move_to_last(CAGR_revenue_majorcats_tot, totalrow) # Move "Total" to last rowlastrow =nrow(CAGR_revenue_majorcats_tot)CAGR_revenue_majorcats_tot |>kbl(caption ="CAGR Calculations for Largest Revenue Sources", row.names =FALSE) |>kable_classic() |>row_spec(lastrow, bold = T, color ="black", background ="gray")
Table 12: Top 10 Revenue Sources with CAGRs
CAGR Calculations for Largest Revenue Sources
Revenue Category
27 Year CAGR
10 Year CAGR
5 Year CAGR
3 Year CAGR
2 Year CAGR
1 Year CAGR
Corporate Income Taxes
5.01
5.54
13.84
-11.48
-18.56
-16.23
Federal Medicaid
6.97
6.97
8.26
2.62
0.93
-3.72
Federal Other
4.28
6.45
3.65
-15.71
3.30
10.68
Federal Transportation
4.50
3.04
8.97
14.34
13.86
16.11
Individual Income Taxes
5.64
6.59
8.98
4.44
9.10
10.03
Medical Provider Assessments
8.33
9.16
6.29
8.07
7.36
8.55
Motor Fuel Tax
3.08
8.61
4.96
5.33
7.22
4.67
Receipts From Revenue Producing
5.74
4.50
8.61
11.24
12.65
9.05
Sales Taxes
3.17
4.12
6.35
2.57
1.46
1.67
All Other Sources
4.50
4.99
9.45
6.74
3.38
-0.65
Total Revenue
4.95
5.90
7.96
0.60
2.82
2.64
Code
### Yearly change summary table for Top 10 Revenues ###revenue_change_majorcats <- rev_long_majorcats |>filter(Year >= past_year | Year == first_year) |>pivot_wider(names_from = Year , values_from = Dollars, names_prefix ="Dollars_") |>rename( Dollars_current = Dollars_2025,Dollars_lastyear = Dollars_2024 )|>mutate("Current FY ($ billions)"=round(Dollars_current/1000, digits =2),"Previous FY ($ billions)"=round(Dollars_lastyear/1000, digits =2),"FY 1998 ($ billions)"=round(Dollars_1998/1000, digits =2),"1-Year Change"=percent((Dollars_current -Dollars_lastyear)/Dollars_lastyear, accuracy = .01), ) |>left_join(CAGR_revenue_majorcats_tot, by =c("Category_name"="Revenue Category") ) |>arrange(-`Current FY ($ billions)`)|>mutate(`27 Year CAGR`=percent(`27 Year CAGR`/100, accuracy=.01)) |>select(-c(Dollars_1998, Dollars_current, Dollars_lastyear, `1 Year CAGR`:`10 Year CAGR` )) |>rename("Revenue Category"= Category_name )allother_row <-which(grepl("All Other", revenue_change_majorcats$`Revenue Category`))revenue_change_majorcats <-move_to_last(revenue_change_majorcats, allother_row) # Move "All Other" to 2nd to last rowtotalrow <-which(grepl("Total", revenue_change_majorcats$`Revenue Category`))revenue_change_majorcats <-move_to_last(revenue_change_majorcats, totalrow) # Move "Total" to last rowlastrow =nrow(revenue_change_majorcats)Table1 <- revenue_change_majorcats|>kbl(caption ="Yearly Change in Revenue for Main Revenue Sources", row.names =FALSE, align ="l") |>kable_classic() |>row_spec(lastrow, bold = T, color ="black", background ="gray")
n_categories <-10+1# (Top 9 and then Total )# keep top 10 largest categories or categories larger than 2 billion for final table in report (not a set rule, changes each year depending what the focus of the report is or what is highlighted.)categories <- exp_long |>filter(Year == current_year & Category_name !="State Employment Retirement (SERS)" ) |>arrange(desc(Dollars)) |>slice(1:n_categories)exp_majorcats <- exp_long |>filter( (Year == current_year | Year == first_year)& Category_name %in% categories$Category_name) exp_long_majorcats <- exp_long |>mutate(Category_name =ifelse(Category_name %in% exp_majorcats$Category_name, Category_name, "All Other Expenditures **"),Category_name =ifelse(Category_name =="Total", "Total Expenditures", Category_name)) |>group_by(Year, Category_name) |>summarize(Dollars =sum(Dollars, na.rm=TRUE))# expenditure_wide_majorcats <- exp_long_majorcats |> # pivot_wider(names_from = Category_name, # values_from = Dollars) |># relocate("All Other Expenditures **", .after = last_col()) |># relocate("Total Expenditures", .after = last_col())# CAGR values for largest expenditure categories and combined All Other ExpendituresCAGR_expenditures_majorcats_tot <-cagr_calculations(exp_long_majorcats) |>select(-c(Category_name.1, Category_name.2, Category_name.3, Category_name.4, Category_name.5 )) |>rename("Expenditure Category"= Category_name, "1 Year CAGR"= cagr_1, "2 Year CAGR"= cagr_2, "3 Year CAGR"= cagr_3, "5 Year CAGR"= cagr_5, "10 Year CAGR"= cagr_10,"27 Year CAGR"= cagr_max )allother_row <-which(grepl("Other", CAGR_expenditures_majorcats_tot$`Expenditure Category`))CAGR_expenditures_majorcats_tot <-move_to_last(CAGR_expenditures_majorcats_tot, allother_row) # Move "All Other" to 2nd to last rowtotalrow <-which(grepl("Total", CAGR_expenditures_majorcats_tot$`Expenditure Category`))CAGR_expenditures_majorcats_tot <-move_to_last(CAGR_expenditures_majorcats_tot, totalrow) # Move "Total" to last rowlastrow =nrow(CAGR_expenditures_majorcats_tot)CAGR_expenditures_majorcats_tot|>kbl(caption ="CAGR Calculations for Largest Expenditure Categories" , row.names=FALSE) |>kable_classic() |>row_spec(lastrow, bold = T, color ="black", background ="gray")# Yearly change for Top n largest expenditure categoriesexpenditure_change_majorcats <- exp_long_majorcats |>filter(Year >= past_year | Year == first_year & Category_name !="SERS") |>pivot_wider(names_from = Year , values_from = Dollars, names_prefix ="Dollars_") |>rename( Dollars_current = Dollars_2025,Dollars_lastyear = Dollars_2024 )|>mutate("Current FY ($ Billions)"=round(Dollars_current/1000, digits =2),"Previous FY ($ Billions)"=round(Dollars_lastyear/1000, digits =2),"FY 1998 ($ Billions)"=round(Dollars_1998/1000, digits =2),"1-Year Change"=percent((Dollars_current -Dollars_lastyear)/Dollars_lastyear, accuracy = .01), ) |>left_join(CAGR_expenditures_majorcats_tot, by =c("Category_name"="Expenditure Category")) |>arrange(-`Current FY ($ Billions)`)|>mutate(`27 Year CAGR`=percent(`27 Year CAGR`/100, accuracy=.01)) |>select(-c(Dollars_1998, Dollars_current, Dollars_lastyear, `1 Year CAGR`:`10 Year CAGR` )) |>rename(# "1-Year Change" = `1 Year CAGR`,"27 Year Change"=`27 Year CAGR`, "Expenditure Category"= Category_name )allother_row <-which(grepl("All Other", expenditure_change_majorcats$`Expenditure Category`))expenditure_change_majorcats <-move_to_last(expenditure_change_majorcats, allother_row) # Move "All Other" to 2nd to last rowtotalrow <-which(grepl("Total", expenditure_change_majorcats$`Expenditure Category`))expenditure_change_majorcats <-move_to_last(expenditure_change_majorcats, totalrow) # Move "Total" to last rowlastrow =nrow(expenditure_change_majorcats)expenditure_change_majorcats |>kbl(caption ="Yearly Change in Expenditures", row.names =FALSE, align ="l") |>kable_classic() |>row_spec(lastrow, bold = T, color ="black", background ="gray")
Table 14: Largest Expenditure Categories with CAGRs
CAGR Calculations for Largest Expenditure Categories
Expenditure Category
27 Year CAGR
10 Year CAGR
5 Year CAGR
3 Year CAGR
2 Year CAGR
1 Year CAGR
Children & Family Services
1.46
5.57
9.19
15.35
10.37
7.13
Debt Service
5.32
-0.26
0.16
-0.78
0.29
-14.24
Human Services
3.97
6.67
13.42
15.65
12.90
7.53
K-12 Education
5.09
5.65
5.16
3.15
0.81
-1.43
Local Govt Revenue Sharing
3.60
3.71
6.97
-4.39
-8.92
-6.35
Medicaid
7.27
7.83
10.03
7.85
5.32
7.48
State Employee Healthcare
6.32
4.51
5.05
8.28
12.72
20.62
Tollway
6.33
0.16
0.08
-2.89
0.82
-2.62
Transportation
4.63
3.99
10.43
13.61
12.53
13.38
University Education
3.00
2.85
4.58
5.14
4.27
1.20
All Other Expenditures **
4.86
4.94
7.89
6.41
0.18
4.53
Total Expenditures
5.09
5.50
7.93
6.10
3.31
3.92
Yearly Change in Expenditures
Expenditure Category
Current FY ($ Billions)
Previous FY ($ Billions)
FY 1998 ($ Billions)
1-Year Change
27 Year Change
Medicaid
35.94
33.44
5.40
7.48%
7.27%
K-12 Education
21.40
21.71
5.60
-1.43%
5.09%
Human Services
11.25
10.47
3.93
7.53%
3.97%
Local Govt Revenue Sharing
9.04
9.66
3.48
-6.35%
3.60%
Transportation
6.70
5.91
1.98
13.38%
4.63%
University Education
5.08
5.02
2.28
1.20%
3.00%
State Employee Healthcare
3.81
3.16
0.73
20.62%
6.32%
Debt Service
1.96
2.29
0.48
-14.24%
5.32%
Tollway
1.93
1.98
0.37
-2.62%
6.33%
Children & Family Services
1.92
1.79
1.30
7.13%
1.46%
All Other Expenditures **
20.46
19.58
5.68
4.53%
4.86%
Total Expenditures
119.51
115.00
31.24
3.92%
5.09%
Export summary file with totals:
Code
# Saves main items in one excel file named `summary_file.xlsx`. Delete `eval=FALSE` to run on local computer.#install.packages("openxlsx")library(openxlsx)#aggregated_totals_majorcats = rbind(rev_long, exp_long)todaysdate =Sys.Date()dataset_names <-list(#'Aggregate Revenues' = revenue_wide2, #'Aggregate Expenditures' = expenditure_wide2, 'Table 1'= revenue_change_majorcats, #Top categories with yearly change, 23 yr cagr'Table 2'= expenditure_change_majorcats,'Appendix 1'= revenue_change2,'Appendix 2'= expenditure_change2,'CAGR Rev-MajorCats'= CAGR_revenue_majorcats_tot, # Categories Match Table 1 in paper'CAGR Exp-MajorCats'= CAGR_expenditures_majorcats_tot, 'Fiscal Gap'= year_totals, # Total Revenue, Expenditure, and Fiscal gap per year'aggregated_totals_long'= aggregated_totals_long#, # all data in long format. Good for creating pivot tables in Excel# 'aggregated_fewercategories' = aggregated_totals_majorcats # combines categories that have small amounts into "All Other" category )# no pension categories included as separate category (except for specific pension repayments that happened in some years.) # pension costs are included with K-12 education, university education, etcwrite.xlsx(dataset_names, file =paste0("../Fiscal-Future-Topics/data/FY", current_year, " Files/summary_file_FY", current_year,"_", todaysdate, ".xlsx"))
Code
library(openxlsx)wb <-createWorkbook()# ---------- Expenditures sheet ----------addWorksheet(wb, "Expenditures")writeDataTable( wb,"Expenditures", datahub_exp,tableStyle ="TableStyleMedium2")col_widths <-pmax(nchar(names(datahub_exp)),sapply(datahub_exp, function(x) max(nchar(as.character(x)), na.rm =TRUE)))setColWidths( wb,"Expenditures",cols =1:ncol(datahub_exp),widths = col_widths +3)# ---------- Revenues sheet ----------addWorksheet(wb, "Revenues")writeDataTable( wb,"Revenues", datahub_rev,tableStyle ="TableStyleMedium2")col_widths <-pmax(nchar(names(datahub_rev)),sapply(datahub_exp, function(x) max(nchar(as.character(x)), na.rm =TRUE)))setColWidths( wb,"Revenues",cols =1:ncol(datahub_rev),widths = col_widths +3)# ---------- README sheet ----------addWorksheet(wb, "README")readme_text <-data.frame(README =c("Fiscal Futures DataHub Download","",paste0("Last updated: ", todaysdate),"","This file contains data downloaded from the Institute of Government and Public Affairs DataHub:","https://igpa.uillinois.edu/igpa-data-hub","","Dollar values are in 1000s of nominal dollars","","Full documentation is available on GitHub:","https://github.com/igpa-uillinois/Fiscal-Futures","","Dataset design and construction:","Professor David Merriman (University of Illinois Chicago)","Email: dmerrim@uic.edu" ))writeData(wb, "README", readme_text, colNames =FALSE)setColWidths(wb, "README", 1, 110)wrap <-createStyle(wrapText =TRUE)addStyle(wb, "README", wrap, rows =1:nrow(readme_text), cols =1, gridExpand =TRUE)# ---------- Save ----------saveWorkbook( wb,paste0("../Fiscal-Future-Topics/data/FY", current_year," Files/IGPA_Datahub_Download","_", todaysdate,".xlsx" ),overwrite =TRUE)
Source Code
---title: "FY2025 Revenue Report"format: html: theme: zephyr toc: true toc-location: left code-fold: true code-tools: true code-overflow: wrap code-copy: true df-print: paged---```{r}#| label: setup#| warning: false#| message: falseknitr::opts_chunk$set(warning =FALSE, message =FALSE)library(tidyverse)library(formatR)library(lubridate)library(scales)library(kableExtra)library(ggplot2)library(readxl)library(janitor)library(cmapplot)alea_theme <-function() { font <-"Whitney" ggplot2::theme(legend.position ="right",legend.title =element_blank(),panel.background = ggplot2::element_blank(),panel.grid.minor.x = ggplot2::element_blank(),panel.grid.major.y =element_line(color ="grey"),panel.grid.minor.y =element_line(color ="grey", linetype ="dashed"),# panel.grid.major.x = ggplot2::element_blank(),axis.ticks =element_line(color ="gray"),axis.ticks.x =element_blank() )}theme_set(alea_theme())# Custom billion formatlabel_billions <-function(digits =1) {function(x) {number_format(accuracy =10^-digits, suffix ="B")( x /1e9 ) }}scale_y_billions <-function(..., digits =1) {scale_y_continuous(labels =label_billions(digits), ...)}move_to_last <-function(df, n) df[c(setdiff(seq_len(nrow(df)), n), n), ]current_year <-2025# fiscal year, not calendar yearpast_year=current_year-1rev_temp <-read_csv(paste0("../../Fiscal Futures IGPA/Fiscal-Future-Topics/data/FY", current_year, " Files/rev_temp.csv"))exp_temp <-read_csv(paste0("../../Fiscal Futures IGPA/Fiscal-Future-Topics/data/FY", current_year, " Files/exp_temp.csv")) ``````{r}#| include: falseupdate_recessions <-function(url =NULL, quietly =FALSE){# Use default URL if user does not overrideif (is_null(url) |missing(url)) { url <-"https://data.nber.org/data/cycles/business_cycle_dates.json" }# locally bind variable names start_char <- end_char <- start_date <- end_date <- ongoing <- index <- peak <- trough <-NULLreturn(# attempt to download and format recessions tabletryCatch({ recessions <- jsonlite::fromJSON(url) |># drop first row trough dplyr::slice(-1) |># convert peaks and troughs... dplyr::mutate(# ...to R datesstart_date =as.Date(peak),end_date =as.Date(trough),# ... and clean char stringsstart_char =format(start_date, "%b %Y"),end_char =format(end_date, "%b %Y")) |># confirm ascending and create row number dplyr::arrange(start_date) |>mutate(index =row_number()) |>mutate(# Flag unfinished recessionsongoing =case_when(is.na(end_date) & index ==max(.$index) ~ T,TRUE~ F),# set ongoing recession to arbitrary future dateend_date =case_when( ongoing ~as.Date("2200-01-01"),TRUE~ end_date),# mark ongoing recession in char fieldend_char =case_when( ongoing ~"Ongoing",TRUE~ end_char) ) |># clean upselect(start_char, end_char, start_date, end_date, ongoing)if (!quietly) {message("Successfully fetched from NBER")}# Return recessions recessions },error =function(cond){if (!quietly) message("WARNING: Fetch or processing failed. `NULL` returned.")return(NULL) } ) )}recessions <-update_recessions()```# Step by Step Data Exploration and Table/Figure Building## Modify Expenditure File### Tax refundsAggregate expenditures: Save tax refunds as negative revenue. Code refunds to match the rev_type codes - 02 = income taxes, fund 0278 - 03 = corporate income taxes, fund 0946, 0380 - 06 = sales tax - 09 = motor fuel tax, fund 0436, 0012 - 24 = insurance taxes and fees - 35 = all other tax refunds. - Estate tax refund, fund 0121 - Aviation fuel refund fund, fund 0946 - cannabis Excise Tax Refund, fund 0912 - Rental Purchase agreement Refund 0671 What we want: - To exclude refunds as expenditures from our expenditure totals. - Exclude funds that hold refund funds from both revenue and expenditure sides - Revenue neutral unless budget tricks are occurring - still want to examine refunds over time to compare to past years calculations ```{r}#| label: fig-tax-refunds#| fig-cap: "Tax Refunds" 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"& fund =="0278"~"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=="0380"~"03", # corporate franv 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 fund %in%c("0946", "0912", "0671") ~"35", # cannabis, aviation, rental purchase tax refund 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)) |>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") |>ungroup() |>ggplot()+geom_line(aes(x=fy, y=Amount, group =`Refund Type`, color =`Refund Type`))+scale_y_billions() +labs(title ="Refund Types") +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.",y="Dollars", x =element_blank() ) ``````{r}#| label: fig-tax-refunds-noabatement#| fig-cap: "Tax Refunds without FY23 Abatements"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"& fund =="0278"~"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=="0380"~"03", # corporate franv 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 fund %in%c("0946", "0912", "0671") ~"35", # cannabis, aviation, rental purchase tax refund 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)) |>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), 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") +labs(title ="Tax refunds without FY23 Abatements",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.", ) +scale_y_billions()``````{r}#| label: fig-includedrefunds#| fig-cap: "Based on current fund coding in the fund_ab_in.xlsx file, most refund dollars are already being excluded by having `in_ff` = 0. Funds that have `in_ff` = 0 are excluded from revenue and expenditure sums when calculating the Fiscal Gap."tax_refund_long |>summarize(expenditure =sum(expenditure, na.rm=TRUE), .by =c(fy, in_ff) ) |>ggplot() +geom_line(aes(x=fy, y = expenditure, group =factor(in_ff), color =factor(in_ff)))+labs(title ="Excluded Refund Expenditures", y ="Dollars", x =element_blank())``````{r}#| label: fig-tax-refunds-revenueside#| fig-cap: "Tax Refunds from Revenues. Includes only funds designated as Refund funds in fund descriptions."tax_refund_long_rev <- rev_temp |>mutate(refund =case_when( fund =="0121"~"35", # Estate tax refund fund=="0278"~"02", # for income tax refunds (individual and corporate) fund=="0380"~"03", # corporate franchise tax refund fund=="0378"~"24", # insurance privilege tax refund fund %in%c("0946", "0912", "0671") ~"35", # cannabis, aviation, rental purchase tax refund T ~"CHECK")) |># if none of the items above apply to the observations, then code them as CHECK filter(refund !="CHECK")tax_refund_rev <- tax_refund_long_rev |>group_by(refund, fy)|>summarize(allocated_for_refunds =sum(receipts, na.rm =TRUE)/1000000) |>pivot_wider(names_from = refund, values_from = allocated_for_refunds, names_prefix ="ref_") |>mutate_all(replace_na, 0) |>arrange(fy)tax_refund_rev|>pivot_longer(c(ref_02:ref_35), names_to ="Refund Type", values_to ="Amount") |>ggplot()+geom_line(aes(x=fy,y=Amount, group =`Refund Type`, color =`Refund Type`))+labs(title ="Refund Types") +labs(title ="Revenue Allocated to Tax Refund Funds ",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.",y ="Millions of $", x =element_blank()) +scale_x_continuous(expand =c(0,0), limits =c(1998, current_year+.5), breaks =c(1998, 2005, 2010, 2015, 2020, current_year))```Abatements were a specific policy choice to do and different than normal refunds. Therefore, abatements were included in the fiscal gap calculation. This is not even a concern during most years. Only was an issue in 2023 when $1 billion abatement expenditure occurred. ```{r}# manually adds the abatements as expenditure item and keeps on expenditure side.# otherwise ignored since it is in fund 0278, which is coded as in_ff=0# all other income tax refunds are excluded from fiscal gap calculationsexp_temp <- exp_temp |>mutate(in_ff =ifelse(object ==9900& fund =="0278", 1, in_ff))```### Pension Expenditures**An Explanation of a Methodological Change in How We Categorize Some Pension Spending**In previous reports, a expenditure category was created to represent all Pension spending. It was created by removing pension expenditures out of the agencies that that had the expenses in order to highlight the amount that was spent on pensions.As of the FY2024 report, pension expenditures will remain in the agency that has the pension expenses to better represent the total cost of providing a service to the public.However, we thought that readers also might be interested in total pension spending during current and past years. We have added a separate table showing this information but emphasize that pension spending is already incorporated in other spending categories and should not be added to the total shown in Table 1 of the report.New POB bond in 2019: Accelerated Bond Fund paid benefits in advance as lump sum State pension contributions for TRS and SURS are largely captured with object=4431. **(State payments into pension fund).** State payments to the following pension systems:- Teachers Retirement System (TRS) Agency 593 --> K-12 Education (Group = 959)- State Employee Retirement System (SERS) Agency 589 --> SERS Agency 589 - _Note: Object 4431 does not have SERS expenditures in it. Those are only in object 116X objects_ - State University Retirement System (SURS) Agency 693 --> University Education (Group = 960) - Judges Retirement System (JRS) Agency 275 --> Judicial (Group = 920) - General Assembly Retirement System (GARS) --> Legislative (Group 910) There are also "Other Post-Employment Benefits" (OPEBs). Expenditure object 4430 is for retirement benefits.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 and other benefit costs should be included in the department that is paying employees to provide those services. But if you are curious how much went into pensions and for which pension agency, they are totaled below: ```{r}#| layout-ncol: 2pension_agencies <-c("589", "593", "594", "693", "275", "131" )pension_objects <-c(4431, 1160:1165)exp_temp |>filter( fy ==2024& object %in% pension_objects) |>mutate(group =ifelse(!agency %in% pension_agencies, "901", agency),group_label =case_when( group =="593"| group =="594"~"TRS", group =="589"~"SERS", group =="693"~"SURS", group =="275"~"JRS", group =="131"~"GARS", group =="901"~"Other Pension Costs",TRUE~"CHECK ME")) |># group = ifelse(object %in% 1160:1165, "901", as.character(agency))) |> group_by(group, group_label) |>summarize(Expenditures =sum(expenditure, na.rm=TRUE)/1000000000)exp_temp |>filter( fy ==2025& object %in% pension_objects) |>mutate(group =ifelse(!agency %in% pension_agencies, "901", agency),group_label =case_when( group =="901"~"Other Pensions", group =="593"~"K-12 Education", group =="594"~"Chi. TPF ", group =="589"~"SERS", group =="693"~"Higher Education", group =="275"~"Judicial", group =="131"~"Legislative",TRUE~"CHECK ME")) |># group = ifelse(object %in% 1160:1165, "901", as.character(agency))) |> group_by(group, group_label) |>summarize(Expenditures =sum(expenditure, na.rm=TRUE)/1000000000)exp_temp |>filter( fy ==2024& object %in% pension_objects) |>mutate(group =ifelse(!agency %in% pension_agencies, "901", agency),group_label =case_when( group =="901"~"Other Department Pensions", group =="593"~"K-12 Education", group =="594"~"Chi. TPF ", group =="589"~"Other Department Pensions", group =="693"~"Higher Education", group =="275"~"Judicial", group =="131"~"Legislative",TRUE~"CHECK ME")) |># group = ifelse(object %in% 1160:1165, "901", as.character(agency))) |> group_by(group, group_label) |>summarize(Expenditures =sum(expenditure, na.rm=TRUE)/1000000000)# in billions exp_temp |>filter( fy ==2025& agency!="494"& object %in% pension_objects ) |>summarize(`Pension Expenditures`=sum(expenditure/1000000000, na.rm=TRUE))```_**TO DO: Relabel image and 2004 pension spike**_```{r}pension_totals <- exp_temp %>%arrange(fund) %>%mutate(pension =case_when( (object=="1298"&# Purchase of Investments, Normally excluded (fy==2010| fy==2011) & (fund=="0477"| fund=="0479"| fund=="0481")) ~3, ( object=="4431") ~1, # 4431 = easy to find pension payments INTO fund (object>"1159"& object<"1166") & fund !="0183"& fund !="0193"~2, # objects 1159 to 1166 are all considered Retirement by Comptroller, # Included - employer contributions from agencies/organizations/etc.#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) )table(pension_totals$pension) pension_totals %>%filter(pension !=0) %>%mutate(pension =as.factor(pension))%>%group_by(fy, pension) %>%summarize(expenditure =sum(expenditure/1000000000, na.rm =TRUE)) %>%ggplot(aes(x=fy, y = expenditure, group=pension)) +theme_classic()+geom_col(aes(fill = pension)) +labs (title ="Pension expenditures", caption ="1 = State contributions INTO pension funds. 2 = Employer Contributions. 3 = Purchase of Investments anomaly in 2010 and 2011. 4 = pension stabilization fund")+theme(legend.position ="bottom") +scale_y_continuous(labels = scales::dollar, name ="$ Billions")```> Change in pension coding in chunk below:```{r}#| label: fig-pensions#| fig-cap: "Pensions"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) )``````{r}#| label: fig-pensions-POB#| fig-cap: "Pension Expenditures"# 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))```Can also be thought of past commitments vs current contributions.Cost of past commitments in the form of Pension benefits paid out, **Current Employees vs Retired Employees**Current Employees: - Group Insurance Benefits Retired Employees:- Deferred Compensation - Medicare Retirees and Survivors of State of Illinois Employees Group Insurance Program (SEGIP) - Part of Medicare ```{r}exp_temp |>filter(fy==2024) |>filter((appr_org=="01"| appr_org =="65"| appr_org=="88") & (object=="4900"| object=="4400") ) |>group_by(agency, agency_name) |># separates CHIP from health and human services and saves it as Medicaidsummarize(expenditure =sum(expenditure))```Drop all cash transfers between funds, statutory transfers, and purchases of investments from expenditure data.```{r}#| label: drop-transferstransfers_drop <- exp_temp |>filter( agency =="799"|# statutory transfers object =="1993"|# interfund cash transfers object =="1298") # purchase of investmentstransfers_drop # items being dropped, # always check to make sure you aren't accidentally dropping something of interest.exp_temp <-anti_join(exp_temp, transfers_drop)```### State employee healthcare costs> Re-commented this out: Commented out line of code that seperates healthcare costs. This should keep healthcare costs in the agency, similar to the change that was made for pensions.agency=="507"| agency=="442" | agency=="445" | agency=="452" |agency=="458" | agency=="497" ~ "948", # other departmentsIt looks like healthcare costs shift to "Other Departments" which includes: - GOMB (507) - Human Rights (442) - Illinois Power Agency (445) - Labor (452) - State Lottery (458) - Veteran's Affairs (497) ```{r}#| include: falseexp_temp |>filter(org_name =="BUREAU OF BENEFITS") |>group_by(fy, agency) |>summarize(expenditure =sum(expenditure) ) |>pivot_wider(names_from ="fy", values_from ="expenditure")exp_temp |>filter(org_name =="BUREAU OF BENEFITS") |>group_by(fy, agency) |>summarize(expenditure =sum(expenditure) ) |>ggplot() +geom_line(aes(x=fy, y=expenditure, group= agency, color = agency)) +scale_y_continuous(labels = scales::dollar) +labs(title="Bureau of Benefits Expenditures")exp_temp |>filter(org_name =="BUREAU OF BENEFITS") |>group_by(fy, object) |>summarize(expenditure =sum(expenditure) ) |>ggplot() +geom_line(aes(x=fy, y=expenditure, group= object, color = object)) +scale_y_continuous(labels = scales::dollar) +labs(title="Bureau of Benefits Expenditures")``````{r}#| label: eehc1#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")``````{r}#| label: transfers-to-localexp_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)))```### Local Transfers```{r}#| label: fig-drop-local-transfers#| fig-cap: "Drop Transfers from State to Local Governments"transfers_long <- exp_temp |>filter((group =="971"|group =="972"| group =="975"| group =="976")) # fund == "0325")transfers_long |>group_by(agency_name, group, fy) |>summarize(expenditure =sum(expenditure, na.rm=TRUE) )|>ggplot() +geom_line(aes(x=fy, y = expenditure, color=agency_name)) +alea_theme() +scale_x_continuous(expand =c(0,0), limits =c(1998, current_year+.5), breaks =c(1998, 2005, 2010, 2015, 2020, current_year)) +labs(title ="Transfers to Local Governments", caption ="Data Source: Illinois Office of the Comptroller")``````{r}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```### Debt```{r}#| label: debt-servicedebt_drop <- exp_temp |>filter(object =="8841"| object =="8811") # escrow OR principle#debt_drop |> group_by(fy) |> summarize(sum = sum(expenditure)) |> arrange(-fy)debt_keep <- exp_temp |>filter(fund !="0455"& (object =="8813"| object =="8800" )) # examine the debt costs we want to include#debt_keep |> group_by(fy) |> summarize(sum = sum(expenditure)) |> arrange(-fy) exp_temp <-anti_join(exp_temp, debt_drop) exp_temp <-anti_join(exp_temp, debt_keep)debt_keep <- debt_keep |>mutate(agency =ifelse(fund !="0455"& (object =="8813"| object =="8800"), "903", as.character(agency)),group =ifelse(fund !="0455"& (object =="8813"| object =="8800"), "903", as.character(group)),in_ff =ifelse(group =="903", 1, as.character(in_ff)))debt_keep_yearly <- debt_keep |>group_by(fy, group) |>summarize(debt_cost =sum(expenditure,na.rm=TRUE)/1000000) |>select(-group)``````{r}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 proceedsalltollway<-rev_temp |>filter(fund =="0455"& source !="0571") |>group_by(fy) |>summarize(sum =sum(receipts, na.rm =TRUE))# tollway bond proceedstollway_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 interesttollwaydebt <- 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 comparisonggplot()+scale_x_continuous(expand =c(0,0), limits =c(1998, current_year+.5), breaks =c(1998, 2005, 2010, 2015, 2020, current_year)) +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")```### Add Other Fiscal Future group codes> Commented out line that creates Other departments from a few agencies. Keeping them seperate to see where healthcare costs go. agency=="507"| agency=="442" | agency=="445" | agency=="452" |agency=="458" | agency=="497" ~ "948", # other departments<!--- if for the IGPA datahub visualizations, combine smallest categories to "Other Departments". For the download data link, keep them separate. At least that is the current request as of March 14 2026 --->```{r}#| label: group-codesexp_temp <- exp_temp |>#mutate(agency = as.numeric(agency) ) |># arrange(agency)|>mutate(group =case_when( agency>"100"& agency<"200"~"910", # legislative agency =="528"| (agency>"200"& agency<"300") ~"920", # judicial ####################################################### Not used if we are not separating pension costs!!# pension > 0 ~ "901", # pensions## New CODE: April 23rd, 2025: agency =="593"~"959", # TRS becomes part of K-12 costs agency =="594"~"959", # TRS agency =="589"~"589", # SERS becomes part of "Other Agencies" agency =="693"~"960", # SURS becomes part of group 960 agency =="275"~"920", # JRS becomes part of group 920 agency =="131"~"910", # GARS becomes part of Group 910###################################################### (agency>"309"& agency<"400") ~"930", # elected officers: Governor, lt gov, attorney general, sec. of state, comptroller, treasurer agency =="586"~"959", # create new K-12 group agency=="402"| agency=="418"| agency=="478"| agency=="444"| agency=="482"~as.character(agency), # aging, CFS, HFS, human services, public health T ~as.character(group)) ) |>mutate(group =case_when( agency=="478"& (appr_org=="01"| appr_org =="65"| appr_org=="88") & (object=="4900"| object=="4400") ~"945", # separates CHIP from health and human services and saves it as Medicaid agency =="586"& fund =="0355"~"945", # 586 (Board of Edu) has special education which is part of medicaid# OLD CODE: agency == "586" & appr_org == "18" ~ "945", # Spec. Edu Medicaid Matching agency=="425"| agency=="466"| agency=="546"| agency=="569"| agency=="578"| agency=="583"| agency=="591"| agency=="592"| agency=="493"| agency=="588"~"941", # public safety & Corrections agency=="420"| agency=="494"| agency=="406"| agency=="557"~as.character(agency), # econ devt & infra, tollway agency=="511"| agency=="554"| agency=="574"| agency=="598"~"946", # Capital improvement agency=="422"| agency=="532"~as.character(agency), # environment & nat. resources agency=="440"| agency=="446"| agency=="524"| agency=="563"~"944", # business regulation agency=="492"~"492", # revenue agency =="416"~"416", # central management services agency=="448"& fy >2016~"416", #add DoIT to central management T ~as.character(group))) |>mutate(group =case_when(# agency=="684" | agency=="691" ~ as.character(agency), # moved under higher education in next line. 11/28/2022 AWM agency=="692"| agency =="693"| agency=="695"| agency =="684"|agency =="691"| (agency>"599"& agency<"677") ~"960", # higher education agency=="427"~as.character(agency), # employment security############################ # Leaving these agencies as their own agency number for now. Had been coded to "Other departments" Group 948# - GOMB (507) # - Human Rights (442) # - Illinois Power Agency (445) # - Labor (452) # - State Lottery (458) # - Veteran's Affairs (497) agency=="507"| agency=="442"| agency=="445"| agency=="452"|agency=="458"| agency=="497"~as.character(agency), # Were included within "other departments"# agency=="507"| agency=="442" | agency=="445" | agency=="452" |agency=="458" | agency=="497" ~ "948", # other departments############################################ other boards & Commissions agency=="503"| agency=="509"| agency=="510"| agency=="565"|agency=="517"| agency=="525"| agency=="526"| agency=="529"| agency=="537"| agency=="541"| agency=="542"| agency=="548"| agency=="555"| agency=="558"| agency=="559"| agency=="562"| agency=="564"| agency=="568"| agency=="579"| agency=="580"| agency=="587"| agency=="590"| agency=="527"| agency=="585"| agency=="567"| agency=="571"| agency=="575"| agency=="540"| agency=="576"| agency=="564"| agency=="534"| agency=="520"| agency=="506"| agency =="533"~"949", # Other Departments# Before pensions were included back with the original agency that spent the money, remaining non-pension expenditures from agencies that deal with pensions were included with Other Departments # agency=="131" |# agency=="275" | #JRS# agency=="589" | #SERS# agency=="593"| # TRS# agency=="594"| # Also TRS# agency=="693" #SURS# ~ "948", T ~as.character(group))) |>mutate(group_name =case_when( group =="416"~"Central Management", group =="442"~"Human Rights", group =="445"~"IL Power Agency", group =="452"~"Labor", group =="458"~"State Lottery", group =="589"~"SERS", group =="478"~"Healthcare and Family Services", group =="482"~"Public Health", group =="901"~"State Pension Contributions", ## Split up into GARS, SERS, etc. now group =="903"~"Debt Service", group =="910"~"Legistlative" , group =="920"~"Judicial" , group =="930"~"Elected Officers" , group =="941"~"Public Safet" , group =="942"~"Econ Development & Infrastructure" , group =="943"~"Central Services", group =="944"~"Business & Professional Regulation" , group =="945"~"Medicaid" , group =="946"~"Capital Improvement" , group =="948"~"Other Departments" , group =="949"~"Other Boards & Commissions" , group =="959"~"K-12 Education" , group =="960"~"University Education" , group == agency ~as.character(agency_name),TRUE~"Check name"),year = fy)exp_temp <- exp_temp |>mutate(fund_cat_name =case_when( fund_cat_name =="General Fund"~"General Funds", fund_cat_name =="REVOLVING FUNDS"~"Revolving Funds", T ~ fund_cat_name ),federal_funded =case_when( fund_cat_name =="Federal Trust Funds"~"Federal Funds", group_name =="MEDICAID"& fund_cat_name =="General Funds"~"Federal Funds", T ~"State Funds" ))exp_temp |>filter(group_name =="Check name")```::: callout-importantAll 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.:::## Modify Revenue data```{r}#| label: rev-recode-agencies# 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)))) |>mutate(fund_cat_name =case_when( fund_cat_name =="General Fund"~"General Funds", fund_cat_name =="REVOLVING FUNDS"~"Revolving Funds", T ~ fund_cat_name ))```#### Federal Transfers```{r}#| label: fig-create-rev-federal-transfers#rev_temp <- rev_temp |> filter(in_ff==1)rev_temp <- rev_temp |>mutate(rev_type =ifelse(rev_type=="57"& agency=="478"& (source=="0618"|source=="2364"|source=="0660"|source=="1552"| source=="2306"| source=="2076"|source=="0676"|source=="0692"), "58", rev_type),rev_type_name =ifelse(rev_type=="58", "Federal Medicaid Reimbursements", rev_type_name),rev_type =ifelse(rev_type=="57"& agency=="494", "59", rev_type),rev_type_name =ifelse(rev_type=="59", "Federal Transportation", rev_type_name),rev_type_name =ifelse(rev_type=="57", "Federal - Other", rev_type_name),rev_type =ifelse(rev_type=="6", "06", rev_type),rev_type =ifelse(rev_type=="9", "09", rev_type)) rev_temp |>filter(rev_type =="58"| rev_type =="59"| rev_type =="57") |>group_by(fy, rev_type, rev_type_name) |>summarise(receipts =sum(receipts, na.rm =TRUE)/1000000) |>ggplot() +geom_line(aes(x=fy, y=receipts,color=rev_type_name)) +scale_y_continuous(labels = comma)+labs(title ="Federal to State Transfers", y ="Millions of Dollars", x ="") +theme(legend.position ="bottom", legend.title =element_blank() )``````{r}#| label: coding-stimuluspackage-dollarsrev_temp <- rev_temp |>mutate(covid_dollars =ifelse(source_name_AWM =="FEDERAL STIMULUS PACKAGE",1,0))rev_temp |>filter(source_name_AWM =="FEDERAL STIMULUS PACKAGE") |>group_by(fy) |>summarize(Received =sum(receipts))```### Medicaid Revenue vs Medicaid Expendituresobject 4900 is Lump Sums, object 4400 is Awards & Grants.```{r}medicaid_cost_total <- exp_temp |>filter(agency=="478"& (appr_org=="01"| appr_org =="65"| appr_org=="88") & (object=="4900"| object=="4400")) |>group_by(fy) |>summarize(sum=sum(expenditure, na.rm=TRUE))medicaid_cost <- exp_temp |>filter(#agency=="478" & # (appr_org=="01" | appr_org == "65" | appr_org=="88") & (object=="4900"| object=="4400")) |>group_by(fy, agency) |>summarize(sum=sum(expenditure, na.rm=TRUE))ggplot()+geom_line(data=medicaid_cost_total, aes(x=fy, y = sum, color ="Expenditures"), lwd =1) +geom_line(data=medicaid_cost, aes(x=fy, y = sum, color = agency)) +scale_x_continuous(n.breaks =6) +labs(title ="Medicaid expenditures", caption ="Medicaid expenditures include funds provided to medical providers.", color =element_blank() )```### Health Insurance Premiums from EmployeesInsurance 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)```{r}#| label: insurance-premiums#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``````{r}#| label: insurance-premiums-long# # 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)emp_premium_long# 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.*### 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:> Clean up code and annotations in chunk below:```{r}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``````{r}#| label: droprevtypes# 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)```## Pivoting and Merging### Revenues<!--- If there are NA rev types, it will cause the code to break when binding dataframes together a few chunks lower --->```{r}#| label: code--check#| include: falserev_temp |>filter(is.na(rev_type))``````{r}#| label: make-ffrev-tableff_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 <-mutate_all(ff_rev, replace_na, 0)``````{r}#| eval: false#| include: false# DOCUMENTATION PURPOSES ONLY: If refunds on the expenditure side are subtracted from the revenue side, it was done like this. The refunds were identified in the "Tax Refunds" section of the code. # OLD way of doing refunds ### 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)"}``````{r}#| label: tbl-labeled-final-ffrev-table#| tbl-cap: "Aggregated Revenue Categories ($ Millions), with old labels. Not actually used for anything other than to have output that was easiy comparable to old STATA output from past years."#| include: false# 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.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 TAXESFEES&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)```### ExpendituresCreate exp_970 for all local government transfers (exp_971 + exp_972 + exp_975 + exp_976).```{r}#| label: tbl-ffexp-notlabeled#| tbl-cap: "Pivoted Expenditure Categories ($ Millions). Intermediate step. Not actually used for anything other than having output similar to past STATA output."ff_exp <- exp_temp |>group_by(fy, group) |>summarize(sum_expenditures =sum(expenditure, na.rm=TRUE)/1000000 ) |>pivot_wider(names_from ="group", values_from ="sum_expenditures", names_prefix ="exp_")|>left_join(debt_keep_yearly) |>rename(exp_903 = debt_cost) |># join local transfers and create exp_970left_join(transfers) |>mutate(exp_970 = exp_971 + exp_972 + exp_975 + exp_976) ff_exp<- ff_exp |>select(-c(exp_971:exp_976)) # drop unwanted columns that are already included in exp_970ff_exp # not labeled```# All Funds vs General Funds**Work in progress!**```{r}rev_fund_cats <- rev_temp |>mutate(fund_cat_name =ifelse(fund_cat_name =="REVOLVING FUNDS", "Revolving Funds", fund_cat_name) ) |>filter(in_ff ==1) |>group_by(fy, fund_cat_name) |>summarize(Revenue =sum(receipts, na.rm=TRUE))rev_fund_cats |>filter(fy ==2025)exp_fund_cats <- exp_temp |>mutate(fund_cat_name =ifelse(fund_cat_name =="REVOLVING FUNDS", "Revolving Funds", fund_cat_name) ) |>filter(in_ff ==1) |>group_by(fy, fund_cat_name) |>summarize(Expenditures =sum(expenditure, na.rm=TRUE))exp_fund_cats |>filter(fy ==2025)ggplot() +geom_line(data = rev_fund_cats, aes(x=fy, y = Revenue, color = fund_cat_name)) +geom_line(data = exp_fund_cats, aes(x=fy, y = Expenditures, color = fund_cat_name), lty=2) +scale_y_billions(name ="$")``````{r}rev_fund_cats <- rev_temp |>mutate(fund_cat_name =ifelse(fund_cat_name =="REVOLVING FUNDS", "Revolving Funds", fund_cat_name) ) |>filter(in_ff ==1& fund_cat_name =="General Funds") |>group_by(fy, fund_cat_name) |>summarize(Revenue =sum(receipts, na.rm=TRUE))rev_fund_cats |>filter(fy ==2025)exp_fund_cats <- exp_temp |>mutate(fund_cat_name =ifelse(fund_cat_name =="REVOLVING FUNDS", "Revolving Funds", fund_cat_name) ) |>filter(in_ff ==1& fund_cat_name =="General Funds") |>group_by(fy, fund_cat_name) |>summarize(Expenditures =sum(expenditure, na.rm=TRUE))exp_fund_cats |>filter(fy ==2025)ggplot() +geom_line(data = rev_fund_cats, aes(x=fy, y = Revenue)) +geom_line(data = exp_fund_cats, aes(x=fy, y = Expenditures), lty=2) +scale_y_billions(name ="$") +labs(caption ="Dashed line is General Fund Expenditures",title ="General Fund Fiscal Gap",subtitle ="Pretend there is a line showing there is a difference between the two lines.",legend ="",x =element_blank())```# Graphs and TablesCreate 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.```{r}#| label: tbl-rev-and-exp-totals-long#| tbl-cap: "Long Version of Data that has Revenue and Expenditures in One Dataframe. Creates `expenditures_recoded_long_pensionchange_FY`, `revenues_recoded_long_pensionchange_FY` and `aggregated_totals_pensionchange` which are exported as CSVs."#| tbl-cap-location: toprev_long <-pivot_longer(ff_rev, rev_02:rev_78, names_to =c("type","Category"), values_to ="Dollars", names_sep ="_") |>rename(Year = fy) |>mutate(Category_name =case_when( Category =="02"~"INDIVIDUAL INCOME TAXES" , Category =="03"~"CORPORATE INCOME TAXES" , Category =="06"~"SALES TAXES" , Category =="09"~"MOTOR FUEL TAX" , Category =="12"~"PUBLIC UTILITY TAXES" , Category =="15"~"CIGARETTE TAXES" , Category =="18"~"LIQUOR GALLONAGE TAXES" , Category =="21"~"INHERITANCE TAX" , Category =="24"~"INSURANCE TAXES, FEES & LICENSES" , Category =="27"~"CORP FRANCHISE TAXES & FEES" , Category =="30"~"HORSE RACING TAXES & FEES", Category =="31"~"MEDICAL PROVIDER ASSESSMENTS" , Category =="32"~"GARNISHMENT-LEVIES" , # dropped Category =="33"~"LOTTERY RECEIPTS" , Category =="35"~"OTHER TAXES" , Category =="36"~"RECEIPTS FROM REVENUE PRODUCING", Category =="39"~"LICENSES, FEES & REGISTRATIONS" , Category =="42"~"MOTOR VEHICLE AND OPERATORS" , Category =="45"~"STUDENT FEES-UNIVERSITIES", # dropped Category =="48"~"RIVERBOAT WAGERING TAXES" , Category =="51"~"RETIREMENT CONTRIBUTIONS" , # dropped Category =="54"~"GIFTS AND BEQUESTS", Category =="57"~"FEDERAL OTHER" , Category =="58"~"FEDERAL MEDICAID", Category =="59"~"FEDERAL TRANSPORTATION" , Category =="60"~"OTHER GRANTS AND CONTRACTS", Category =="63"~"INVESTMENT INCOME", Category =="66"~"PROCEEDS, INVESTMENT MATURITIES" , #dropped Category =="72"~"BOND ISSUE PROCEEDS", #dropped Category =="75"~"INTER-AGENCY RECEIPTS ", #dropped Category =="76"~"TRANSFER IN FROM OUT FUNDS", Category =="78"~"ALL OTHER SOURCES" , Category =="79"~"COOK COUNTY IGT", #dropped Category =="98"~"PRIOR YEAR REFUNDS", #dropped T ~"Check Me!" ) )|>mutate(Category_name =str_to_title(Category_name))exp_long <-pivot_longer(ff_exp, exp_402:exp_970 , names_to =c("type", "Category"), values_to ="Dollars", names_sep ="_") |>rename(Year = fy ) |>mutate(Category_name =case_when( Category =="131"~"GARS", # should be in Legislative Group already Category =="275"~"JRS", # should be in Judicial Category already Category =="402"~"Aging", Category =="406"~"Agriculture", # agriculture Category =="416"~"Central Management", ## contains DoIT also Category =="418"~"Children & Family Services", Category =="420"~"Commerce & Economic Opportunity", Category =="422"~"Natural Resources" , Category =="426"~"Corrections", Category =="427"~"Employment Security" , Category =="442"~"Human Rights" , # sometimes included in "Other Departments" when trying to have fewer expenditure categories Category =="444"~"Human Services" , Category =="445"~"IL Power Agency" , # IL Power Agency Category =="448"~"Innovation & Technology", # should be in Central Management already Category =="452"~"Labor" , # Sometimes included in "Other Departments when trying to have fewer categories Category =="458"~"State Lottery" , # State Lottery is sometimes included as "Other Departments when trying to have fewer expenditure categories Category =="478"~"Family Services (net Medicaid)", Category =="482"~"Public Health", Category =="492"~"Revenue", Category =="493"~"Teacher Retirement System (TRS)", # Should be included in K-12 already Category =="494"~"Transportation" , Category =="507"~"GOMB", # GOMB # GOMB is sometimes included as "Other Departments when trying to have fewer expenditure categories Category =="497"~"Veterans' Affairs", # Veterans' Affairs is sometimes included as "Other Departments when trying to have fewer expenditure categories Category =="532"~"Environmental Protection Agency" , Category =="557"~"IL State Tollway" , Category =="589"~"State Emp. Retirement System (SERS)", Category =="693"~"SURS", # should be in Higher Education already Category =="901"~"Other Pension Expenditure", Category =="903"~"Debt Service", Category =="904"~"State Employee Healthcare", Category =="910"~"Legislative" , Category =="920"~"Judicial" , Category =="930"~"Elected Officers" , Category =="941"~"Public Safety" , Category =="943"~"Central Services", Category =="944"~"Business & Professional Regulation" , Category =="945"~"Medicaid" , Category =="946"~"Capital Improvements" , Category =="948"~"Other Departments" , # Used when pre-grouping small agencies to group = 948. Category =="949"~"Other Boards & Commissions" , Category =="959"~"K-12 Education" , Category =="960"~"University Education", Category =="970"~"Local Govt Transfers", T ~"CHECK ME!") ) |> dplyr::mutate(Dollars =ifelse(is.na(Dollars), 0, Dollars))# combine revenue and expenditures into one data frameaggregated_totals_long <-rbind(rev_long, exp_long)# # if(any(aggregated_totals_long$Category_name == "CHECK ME!" & aggregated_totals_long$Dollars>0)){# # aggregated_totals_long <- aggregated_totals_long# # }else{# aggregated_totals_long <- aggregated_totals_long |> # filter(!Category_name %in% c("CHECK ME!", "Check Me!")) |># mutate(Dollars = ifelse(is.na(Dollars), 0, Dollars))# }# # if(!any(aggregated_totals_long$Category_name == "CHECK ME!" & # (aggregated_totals_long$Dollars > 0 | is.na(aggregated_totals_long$Dollars)) ) ) {# # aggregated_totals_long <- aggregated_totals_long |># filter(!Category_name %in% c("CHECK ME!", "Check Me!")) |># mutate(Dollars = ifelse(is.na(Dollars), 0, Dollars))# }if (any(aggregated_totals_long$Category_name =="CHECK ME!"& aggregated_totals_long$Dollars >0, na.rm =TRUE)) {stop("CHECK ME! category contains positive dollars. Investigate the categorization.")} else { aggregated_totals_long <- aggregated_totals_long |> dplyr::filter(!Category_name %in%c("CHECK ME!", "Check Me!"))}aggregated_totals_long |>mutate(`Dollars (Millions)`=round(Dollars, digits =0)) |>select(-Dollars) |>select(Year, Category_name, `Dollars (Millions)`, type, Category)``````{r}#| label: tbl-year-totals-table-withgap#| tbl-cap: "Year totals with Fiscal Futures methodology for excluding/including revenue and expenditure objects."#| tbl-cap-location: topyear_totals <- aggregated_totals_long |>group_by(type, Year) |>summarize(Dollars =sum(Dollars, na.rm =TRUE)) |>pivot_wider(names_from ="type", values_from = Dollars) |>rename(Expenditures = exp,Revenue = rev) |>mutate(`Fiscal Gap`= Revenue - Expenditures)# creates variable for the Gap each yearyear_totals |>mutate_all(round, digits =0) |>kbl(caption ="Fiscal Gap for each Fiscal Year ($ Millions)") |>kable_styling(bootstrap_options =c("striped")) |>kable_classic() |>add_footnote(c("Values include State CURE dollars (SLFRF)") )```Graphs made from `aggregated_totals_long` dataframe.### Fiscal Gap Graph```{r}#| label: fig-fiscal-gap#| fig-cap: "Fiscal Gap Comparison"#| fig-subcap: #| - "Fiscal Gap With Trend Lines"#| - "Fiscal Gap Without Trend Lines"#| fig-cap-location: top## Adjust x and y coordinates to move placement of textannotation <-data.frame(x =c(2004, 2017, 2019),y =c(60, 50, 5), label =c("Expenditures","Revenue", "Fiscal Gap"))annotation_nums <-data.frame(x =c(2025, 2025, 2025),y =c(91, 123, -5), label =c( year_totals$Expenditures[year_totals$Year==current_year]/1000, year_totals$Revenue[year_totals$Year==current_year]/1000, year_totals$`Fiscal Gap`[year_totals$Year==current_year]/1000))## Dashed line versions for expenditures: fiscal_gap <-ggplot(data = year_totals, aes(x=Year, y = Revenue/1000)) +geom_recessions(text =FALSE, update = recessions)+# geom_smooth adds regression line, graphed first so it appears behind line graphgeom_smooth(aes(x = Year, y = Revenue/1000), color ="gray", alpha =0.7, method ="lm", se =FALSE) +# scale_linetype_manual(values="dashed")+geom_smooth(aes(x = Year, y = Expenditures/1000), color ="rosybrown2", linetype ="dotted", method ="lm", se =FALSE, alpha =0.7) +# line graph of revenue and expendituresgeom_line(aes(x = Year, y = Revenue/1000), color ="Black", size=1) +geom_line(aes(x = Year, y = Expenditures/1000, linetype ="dashed"), color ="red", lwd=1) +geom_line(aes(x = Year, y = (`Fiscal Gap`/1000)), color ="darkgray", lwd =1) +geom_hline(yintercept =0) +geom_text(data = annotation, aes(x=x, y=y, label=label,parse =TRUE))+theme(legend.position ="bottom", legend.title =element_blank())+scale_linetype_manual(values =c("dashed", "dashed")) +scale_x_continuous(expand =c(0,0),limits =c(1998, current_year+.5)) +xlab("Year") +ylab("Billions of Dollars") +ggtitle(paste0("Illinois Expenditures and Revenue Totals, 1998-",current_year))fiscal_gap## Adjust x and y coordinates to move placement of textannotation <-data.frame(x =c(2024, 2024, 2024),y =c(101, 130, 10), label =c("Expenditures","Revenue", "Fiscal Gap"))fiscal_gap2 <-ggplot(data = year_totals, aes(x=Year, y = Revenue/1000)) +geom_recessions(text =FALSE, update_recessions = recessions)+geom_line(aes(x = Year, y = Revenue/1000, color ="Revenue"), lwd =1, label ="Revenue") +geom_line(aes(x = Year, y = Expenditures/1000, color ="Expenditures"), linetype ="dotted", lwd =1, label ="Expenditures") +geom_line(aes(x = Year, y = (`Fiscal Gap`/1000)), color ="darkgray", lwd=1) +geom_text(data = annotation, aes(x=x, y=y, label=label)) +## Word locations and textgeom_text(data = annotation_nums, aes(x = x, y = y, label = scales::dollar(label, accuracy =0.01L)), size =3) +## Number locations and texttheme_classic() +theme(legend.position ="bottom", legend.title =element_blank()) +scale_color_manual(values =c("Revenue"="black", "Expenditures"="red")) +scale_linetype_manual(values =c("Revenue"="solid", "Expenditures"="dotted")) +geom_hline(yintercept =0) +scale_y_continuous(#labels = comma, limits =c(-12, 130), breaks =c(-10, 20, 40, 60, 80, 100, 120), minor_breaks =c(-10, 0, 10, 30, 50, 70, 90, 110))+scale_x_continuous(expand =c(0,0), limits =c(1998, current_year+1) ) +# scale_color_manual(values = c("red" = "Expenditures", "black" = "Revenue")) + xlab("Year") +ylab("Billions of Dollars") +ggtitle(paste0("Illinois Expenditures and Revenue Totals, 1998-",current_year))fiscal_gap2``````{r}#| include: falsefiscal_gap2 <-ggplot(data = year_totals, aes(x=Year, y = Revenue/1000)) +geom_recessions(text =FALSE, update = recessions)+geom_line(aes(x = Year, y = Revenue/1000, color ="Revenue"), lty ="solid" , lwd =1) +geom_line(aes(x = Year, y = Expenditures/1000, color ="Expenditures"), lty ="dashed" , lwd = .7 ) +geom_line(aes(x = Year, y = (`Fiscal Gap`/1000), color ="Fiscal Gap"), lwd=1) +geom_text(data = annotation, aes(x=x, y=y, label=label))+geom_text(data = annotation_nums, aes(x = x+1, y = y, label = scales::dollar(label, accuracy=0.01L)), size =3) +theme_classic() +theme(legend.position ="bottom",legend.title =element_blank(), panel.background =element_rect(fill='transparent'), #transparent panel bgplot.background =element_rect(fill='transparent', color=NA), #transparent plot bgpanel.grid.major =element_blank(), #remove major gridlinespanel.grid.minor =element_blank(), #remove minor gridlineslegend.background =element_rect(fill='transparent'), #transparent legend bg)+#scale_linetype_manual(values = c( "dashed", "solid", "solid" )) +#scale_linetype_manual(labels =c("Expenditures", "Revenue")) +geom_hline(yintercept =0) +scale_color_manual(values =c( "Expenditures"="red", "Revenue"="black", "Fiscal Gap"="darkgray"), guide =guide_legend(override.aes =list(linetype =c( "dashed", "solid", "solid"))) ) +scale_y_continuous(limits =c(-12, 120), breaks =c(-10, 20, 40, 60, 80, 100, 120))+scale_x_continuous(expand =c(0,0), limits =c(1998, current_year), breaks =c(1998, 2000, 2005, 2010, 2015, 2020, current_year) ) +xlab("Year") +ylab("Billions of Dollars") fiscal_gap2# ggsave(plot = fiscal_gap2, filename= "./paper-figures/Figure1-fiscalgap.eps", width = 6, height = 4)# ggsave(plot = fiscal_gap2, filename= "./paper-figures/Figure1-fiscalgap.png", width = 6, height = 4)```## Downloadable Tables```{r}#| label: tbl-datahub-export-revenues#| tbl-cap: "Fiscal Futures Revenue Categories. As of March 2026, this is now the downloadable table on IGPA Datahub site. Totals are in millions of nominal dollars." aggregate_rev_labeled <- rev_long |>filter(Category_name !="CHECK ME!") |>group_by(Year, Category, Category_name) |>mutate(Dollars =round(Dollars)) |>arrange(Category) |>pivot_wider(names_from ="Year", values_from ="Dollars")datahub_rev <- aggregate_rev_labeled |>select(-type)datahub_rev |> DT::datatable(rownames =FALSE,extensions ='Buttons',options =list(dom ='Bfrtip', # 'B' for buttons, 'f' for filtering, 'r' for processing, 't' for table, 'i' for info, 'p' for paginationbuttons =c('copy', 'csv', 'excel', 'pdf', 'print'), # Specify the download formatslengthMenu =list(c(10, 25, 50, -1),c(10, 25, 50, "All") # Display options for page length ) ) )``````{r}#| label: tbl-datahub-export-expenditures#| tbl-cap: "Fiscal Futures Grouped Expenditure Categories. As of March 2026, this is now the downloadable table on IGPA Datahub site. Totals are in millions of nominal dollars. "aggregate_exp_labeled <- exp_long |>filter(Category_name !="CHECK ME!") |>group_by(Year, Category, Category_name) |>mutate(Dollars =round(Dollars)) |>arrange(Category) |>pivot_wider(names_from ="Year", values_from ="Dollars")datahub_exp <- aggregate_exp_labeled |>select(-type)datahub_exp |> DT::datatable(rownames =FALSE,extensions ='Buttons',options =list(dom ='Bfrtip', # 'B' for buttons, 'f' for filtering, 'r' for processing, 't' for table, 'i' for info, 'p' for paginationbuttons =c('copy', 'csv', 'excel', 'pdf', 'print'), # Specify the download formatslengthMenu =list(c(10, 25, 50, -1),c(10, 25, 50, "All") # Display options for page length ) ) )```**Expenditure and revenue amounts in billions of dollars:**```{r}#| label: fig-bar-graphs-fy25#| fig-cap: "FY25 Totals"#| fig-subcap: #| - "FY25 Expenditures"#| - "FY25 Revenue Sources"exp_long |>filter(Year == current_year & Dollars >0 ) |>arrange(desc(`Dollars`)) |>ggplot() +geom_col(aes(x =fct_reorder(Category_name, `Dollars`), y = (`Dollars`/1000), fill ="red"))+coord_flip() +theme_classic()+theme(legend.position ="none") +labs(title =paste0("Expenditures for ", current_year))+xlab("Expenditure Categories") +ylab("Billions of Dollars") rev_long |>filter(Year == current_year & Dollars >0 ) |>arrange(desc(`Dollars`)) |>ggplot() +geom_col(aes(x =fct_reorder(Category_name, `Dollars`), y = (`Dollars`/1000)))+coord_flip() +theme_classic() +theme(legend.position ="none") +labs(title =paste0("Revenue for ", current_year))+xlab("Revenue Categories") +ylab("Billions of Dollars") ```**Expenditure and revenues when focusing on largest categories and combining others into "All Other Expenditures(Revenues)":**```{r}#| label: fig-bargraphs-largest-expandrev-cats#| fig-cap: "Largest Groups"#| fig-subcap: #| - "Largest Expenditures for FY2025"#| - "Largest Revenue Sources for FY2025"exp_long |>filter( Year == current_year & Dollars >0 ) |>mutate(rank =rank(Dollars),Category_name =ifelse(rank >13| Category !="589", Category_name, 'All Other Expenditures')) |># select(-c(Year, Dollars, rank)) |>arrange(desc(Dollars)) |>ggplot() +geom_col(aes(x =fct_reorder(Category_name, `Dollars`), y =`Dollars`/1000), fill ="rosybrown2") +coord_flip() +theme_classic() +labs(title =paste0("Expenditures for ", current_year))+xlab("") +ylab("Billions of Dollars")rev_long |>filter( Year == current_year & Dollars >0 ) |>mutate(rank =rank(Dollars),Category_name =ifelse(rank >10, Category_name, 'All Other Sources')) |>arrange(desc(Dollars)) |>ggplot() +geom_col(aes(x =fct_reorder(Category_name, `Dollars`/1000), y =`Dollars`/1000), fill ="dark gray")+coord_flip() +theme_classic() +labs(title =paste0("Revenue for ", current_year)) +xlab("") +ylab("Billions of Dollars")```**Expenditure and revenues when focusing on largest categories and combining others into "All Other Expenditures(Revenues)":**```{r}#| label: fig-bargraphs-largest-expend-byfundingsource#| fig-cap: "Largest Expenditures for FY2025"n_categories <-10# keep top 10 largest category names for bar graphcurrent_year <-2025# list of top 10 expenditure categoriescategories <- exp_long |>filter(Year ==2025 ) |>arrange(desc(Dollars)) |>slice(1:10)exp_temp |>group_by(year, group, group_name, fund_cat_name) |>summarize(sum_expenditure =sum(expenditure)/1000000) |>arrange(year) |>pivot_wider(names_from ="group_name", values_from ="sum_expenditure") |>filter(fund_cat_name =="Federal Trust Funds")exp_temp |>mutate(fund_cat_name =case_when( fund_cat_name =="General Fund"~"General Funds", fund_cat_name =="REVOLVING FUNDS"~"Revolving Funds", T ~ fund_cat_name ),federal_funded =case_when( fund_cat_name =="Federal Trust Funds"~"Federal Funds", group_name %in%c("MEDICAID", "Medicaid") & fund_cat_name =="General Funds"~"Federal Funds", T ~"State Funds" )) |>group_by(year, group, group_name, federal_funded) |>summarize(Dollars =sum(expenditure)/1000000) |>#filter(fund_cat_name == "Federal Trust Funds")filter( year == current_year) |># mutate(rank = rank(Dollars),mutate(Category_name =ifelse(group %in% categories$Category, group_name, 'All Other Expenditures')) |># select(-c(Year, Dollars, rank)) |>arrange(desc(Dollars)) |>ggplot() +geom_col(aes(x = Category_name, y =`Dollars`/1000, fill = federal_funded)) +coord_flip() +theme_classic() +labs(title =paste0("Expenditures for ", current_year))+xlab("") +ylab("Billions of Dollars")```### Top 3 Revenues```{r}#| label: build-Figure1annotation <-data.frame(x =c(2013, 2018, 2013),y =c(16, 10, 5), label =c("Individual Income Tax", "Federal Medicaid", "Sales Tax" ))top3 <- rev_long |>filter(Category =="02"| Category =="58"|# Category == "03" | Category =="06") |>mutate(Category_name =factor(Category_name, levels =c("Individual Income Taxes", "Federal Medicaid", "Sales Taxes")))top3 <-ggplot(data = top3, aes(x=Year, y=Dollars/1000))+geom_recessions(text =FALSE, update = recessions)+geom_line(aes(x=Year, y=Dollars/1000, color = Category_name, lty = Category_name), show.legend =NA, lwd = .7) +#geom_text(data = annotation, aes(x=x, y=y, label=label)) +theme_classic() +scale_x_continuous(expand =c(0,0),limits =c(1998, current_year+.5) ) +scale_y_continuous(labels = comma) +theme(legend.position =c(1, 0.5),legend.justification =c(0.5, 0.5),base_size =16,plot.margin =margin(5.5, 60, 5.5, 5.5) # top, right, bottom, left) +# theme(#legend.position = "inside",# #legend.location = "plot",# legend.position = "right",# legend.justification.right = c(1,.3),# #legend.justification.inside = c(1,.3),# base_size = 16) +labs( y ="Billions of Nominal Dollars",color =NULL,lty =NULL) Figure1 <- top3 +geom_line(data = year_totals, aes(x=Year, y = Revenue/1000, lty ="Total Revenue", color ="Total Revenue"), lwd =1) +# scale_linetype_manual(values = c("dotted", "dashed", "dotdash", "solid")) +scale_color_manual(values =c("Individual Income Taxes"="#1f77b4","Federal Medicaid"="#ff7f0e","Sales Taxes"="#2ca02c","Total Revenue"="black" )) +scale_linetype_manual(values =c("dotted", "dashed", "dotdash", "solid"), labels =c( "Individual Income Taxes", "Federal Medicaid", "Sales Taxes", "Total Revenue" )) ``````{r}#| label: fig-FIGURE1#| fig-cap: "Top 3 Revenue Sources" #ggplot2::ggsave("generated/FIGURE1.png", Figure1)#, #width = 11, height = 6, dpi = 300 )Figure1 ``````{r}#| label: fig-top3-ownsourcerevenues#| fig-cap: "Top 3 Own-Source Revenues Sources"#| fig-cap-location: topannotation <-data.frame(x =c(2013, 2018, 2013),y =c(16, 10, 5), label =c("Individual Income Tax", "Sales Tax", "Corporate Income Tax"))top3 <- rev_long |>filter(Category =="02"| Category =="03"| Category =="06")top3 <-ggplot(data = top3, aes(x=Year, y=Dollars/1000))+geom_recessions(text =FALSE, update = recessions)+geom_line(aes(x=Year, y=Dollars/1000, group = Category_name, color = Category_name), position ="stack") +# geom_text(data = annotation, aes(x=x, y=y, label=label)) +theme_classic() +scale_x_continuous(expand =c(0,0),limits =c(1998, current_year+.5) ) +scale_y_continuous(labels = comma) +scale_linetype_manual(values =c("dotted", "dashed", "solid")) +theme(legend.position ="none") +labs(title ="Top 3 Own Source Revenues", subtitle ="Individual Income Taxes, Sales Tax, and Corporate income taxes",y ="Billions of Nominal Dollars") top3 +geom_line(data = year_totals, aes(x=Year, y = Revenue/1000))```### Sales Tax```{r}adjustments <- readxl::read_xlsx("../Fiscal-Future-Topics/inputs/raw_index_data.xlsx")salestax <- rev_long |>filter(Category_name =="Sales Taxes") |>rename(year = Year, dollars = Dollars)# salestax <- aggregated_totals_long |> # filter(Category_name == "Sales Taxes")salestax <- salestax |>left_join(adjustments, by ="year")# Nominal GROWTH sales <- salestax %>%mutate(growth = (dollars/(lag(dollars)) )-1 )library(zoo)library(patchwork)# 5-year MOVING AVERAGE sales <- sales %>%mutate(ma_growth =rollmean(growth, 5, align="center", fill=NA))# Calculate averages and plot them avg_nom <-mean(sales$growth, na.rm =TRUE)avg_ma <-mean(sales$ma_growth, na.rm =TRUE)p_points <-ggplot(sales, aes(x = year, y = growth)) +geom_recessions(text =FALSE, update = recessions) +geom_point(size =1.6) +geom_line() +geom_hline(yintercept = avg_nom, linetype ="dashed") +scale_y_continuous(labels = percent,# limits = c(-.10, .10), breaks =c(-.05, 0, .05, .1)) +scale_x_continuous(breaks =c(2000, 2005, 2010, 2015, 2020, 2025))+labs(title ="Nominal", x =NULL, y ="Growth rate") +theme_minimal(base_size =12)p_ma <-ggplot(sales, aes(year, ma_growth)) +geom_recessions(text =FALSE, update = recessions)+geom_point(size =1.6) +geom_line() +geom_hline(yintercept = avg_ma, linetype ="dashed") +scale_y_continuous(labels = percent, limits =c(-.05, .10), breaks =c(-.05, 0, .05, .1)) +scale_x_continuous(breaks =c(2000, 2005, 2010, 2015, 2020, 2025))+labs(title ="Nominal Moving Average", x =NULL, y ="Moving Average\nGrowth Rate") +theme_minimal(base_size =12)# Real Dollars Graphsbase_year <-min(salestax$year, na.rm =TRUE)base_idx <- salestax %>%filter(year == base_year) %>%summarize(x =mean( CPIAUCSL, na.rm =TRUE)) %>%pull(x)sales <- salestax %>%mutate(real_dollars = dollars * (base_idx / CPIAUCSL),r_growth = real_dollars /lag(real_dollars) -1,ma212_real =rollmean(r_growth, 5, align="center", fill=NA) )avg_real <-mean(sales$r_growth, na.rm =TRUE)avg_ma_real<-mean(sales$ma212_real, na.rm =TRUE)p_real <-ggplot(sales, aes(year, r_growth)) +geom_recessions(text =FALSE, update = recessions)+geom_point(size =1.6) +geom_line() +# geom_smooth(se = FALSE, method = "loess", span = 0.6, linewidth = 0.7) +geom_hline(yintercept = avg_real, linetype ="dashed") +scale_y_continuous(labels = percent,breaks =c(-.05, 0, .05, .1)) +scale_x_continuous(breaks =c(2000, 2005, 2010, 2015, 2020, 2025))+labs(title ="Real", x =NULL, y ="Real Growth rate") +theme_minimal(base_size =12)p_ma_real <-ggplot(sales, aes(year, ma212_real)) +geom_recessions(text =FALSE, update = recessions)+geom_point(size =1.6) +geom_line() +geom_hline(yintercept = avg_ma_real, linetype ="dashed") +scale_y_continuous(labels = percent, limits =c(-0.05, .1)) +scale_x_continuous(breaks =c(2000, 2005, 2010, 2015, 2020, 2025))+labs(title ="Real Moving Average", x =NULL, y ="Moving Average\nReal Growth Rate") +theme_minimal(base_size =12)parts <-c(paste("Nominal", label_percent(accuracy =0.1)(avg_nom)),paste("Real", if (!is.na(avg_real)) label_percent(accuracy =0.1)(avg_real) elseNA),paste("5-Year MA (Nom.)", label_percent(accuracy =0.1)(avg_ma)),paste("5-Year MA (Real)", if (!is.na(avg_ma_real)) label_percent(accuracy =0.1)(avg_ma_real) elseNA))cap4 <-paste("Dashed lines = sample averages:", paste(na.omit(parts), collapse ="; "))p_sales4 <- (p_points | p_real) / (p_ma | p_ma_real) +plot_annotation(title ="Various Measures of Illinois Sales Tax Growth Rate",caption = cap4 )print(p_sales4)#ggsave("generated/figure2.png", p_sales4#, width = 12, height = 8, dpi = 300)```### Own Source and Fed TransfersFederal revenue to all fund types is coded as 3 types of federal revenue: Medicaid, Transportation, and Other. ```{r}#| label: tbl-ownsource-and-fed-revenue#| tbl-cap: "Own Source and Federal Revenue"#| tbl-location: marginownsource_rev <- rev_long |>filter(!Category %in%c("57", "58", "59")) |>group_by(Year) |>summarize(Dollars =sum(Dollars))ownsource_rev# ownsource_rev |> # ggplot()+geom_line(aes(x=Year, y=Dollars)) + # labs(title = "Own Source Revenues", subtitle = "Total own source revenue", y = "Millions of Dollars")``````{r}fed_rev <- ff_rev |>select(fy, rev_57, rev_58, rev_59) |>mutate(fed_total = rev_57+rev_58+rev_59)annotation <-data.frame(x =c(2013, 2015),y =c(50, 25), label =c("Own Source Revenue", "Federal Revenue"))annotation_nums <-data.frame(x =c(2023, 2023),y =c(50, 25), label =c("Own Source Revenue", "Federal Revenue"))ownsource_graph <-ggplot(ownsource_rev, aes(x=Year, y=Dollars/1000)) +geom_recessions(text =FALSE, update_recessions = recessions)+geom_line(data = ownsource_rev, aes(x=Year, y=Dollars/1000), color ="Red") +geom_line(data = fed_rev, aes(x=fy, y=fed_total/1000), color ="Black") +geom_text(data = annotation, aes(x=x, y=y, label=label))+scale_y_continuous(labels = comma)+scale_x_continuous(expand =c(0,0),limits =c(1998-.5, current_year+.5)) +theme(legend.position ="none") +theme_classic()+labs(title ="Own Source Revenue and Federal Revenue, 1998-2025", y ="Billions of Dollars")ownsource_graph```### Medicaid Reimbursements and ExpendituresKey funds: Healthcare Provider Relief (0793) and Hospital Provider (0346)0365 is Health and Human Services Medicaid TR ? 0740 is Medicaid Buy in ProgramHealthcare provider taxes come from revenue source 0133.2104 = Medicare Part D2683 = MCO Provider Assessment --> Mostly goes to Healthcare Provider Relief Fund 0793 in HFS2526 = Hospital Provider Fund (not used in fy2025)0133 = Health Care Provider Tax --> Mostly goes to Hospital Provider Fund 03460137 = Health Care Prov-Hospital (not used in fy2025)0145 = IHFA Medicaid Provider (not used in fy2025)**Illinois also received:** - over 500 million for TANF grant (source = 1393).- Also receives food stamp funds, over 500 million for "Medical Administration" source 0675. This mostly goes to the General Revenue Fund. - around \$1.3 billion for "Medical Assistance" (source 0692, which goes to Drug Rebate Fund 0728)- an additional $3.3 billion in medical assistance from source 0676 which mostly goes to the General Revenue fund (0001) and a little goes to Tobacco settlement recovery fund```{r}provider_taxes <- rev_temp |>filter(source =="0133"| source =="2683" ) |>group_by(fy, source) |>summarize(receipts=sum(receipts)) ggplot()+geom_line(data = provider_taxes, aes(x=fy, y = receipts, color = source)) +labs(title ="Provider Taxes", caption ="Revenue Sources 0133 and 2683.") +scale_y_billions(name ="Dollars")rev_temp |>filter(fy ==2025) |>filter(source =="0133") |>group_by(fy, fund_name_ab) |>summarize(receipts=sum(receipts))exp_temp |>filter(fund =="0346"& fy ==2025) |>summarize(spent =sum(expenditure))exp_temp |>filter(fund =="0346"& fy ==2025)rev_temp |># Healthcare Provider Taxes & MCO Provider assessmentfilter(source =="0133"| source =="2683") |>group_by(fy, fund_name_ab) |>summarize(receipts=sum(receipts)) |>ggplot() +geom_col(aes(x=fy, y = receipts, fill = fund_name_ab)) +scale_y_billions() +labs(x =element_blank(),title ="Funds that received Provider Taxes")medicaid_cost <- exp_temp |>filter(agency=="478"&#(appr_org=="01" | appr_org == "65" | appr_org=="88") & (object=="4900"| object=="4400")) |>group_by(fy) |>summarize(sum=sum(expenditure, na.rm=TRUE))med_reimburse <- rev_temp |>filter(rev_type =="58") |># rev_type=="57" & agency=="478" & # (source=="0618"|source=="2364"|# source=="0660"|source=="1552"| source=="2306"| source=="2076"|source=="0676"|source=="0692") ) |> group_by(fy) |>summarize(sum=sum(receipts, na.rm=TRUE))ggplot()+geom_line(data=medicaid_cost, aes(x=fy, y=sum), color ="red") +geom_line(data=med_reimburse, aes(x=fy, y = sum), color="black") +# geom_line(data = provider_taxes, aes(x=fy, y = receipts), color = "gray") +labs(title ="Medicaid reimbursements and Medicaid expenditures", caption ="Medicaid expenditures include funds provided to medical providers.",x =element_blank()) +scale_y_billions(name ="Dollars") +scale_x_continuous(breaks =c(2000, 2005, 2010, 2015, 2020, 2025))```### Provider Assessments, % of 1998 levels```{r}#| label: fig-FIGURE3##| fig-width: 6##| fig-height: 4provider_taxes <- rev_long |>filter(Category =="31")base_idx <- provider_taxes %>%filter(Year == base_year)provider_taxes <- provider_taxes |>mutate(rev_indexed = (Dollars / Dollars[Year==1998]),Series ="Provider Taxes") |>select(Year, rev_indexed, Series)rev_index = year_totals |>mutate(rev_indexed = (Revenue / Revenue[Year==1998]),Series ="Total Revenue") |>select(Year, rev_indexed, Series)plotdata <-rbind(provider_taxes, rev_index)plot_indexed <-ggplot(plotdata, aes(Year, rev_indexed, color = Series)) +geom_recessions(text =FALSE, update = recessions) +geom_line(lwd =1) +geom_point(data = plotdata |>group_by(Series) |>filter(Year ==max(Year)),aes(color = Series),hjust =-0.1, vjust =0.5, show.legend =FALSE ) +scale_color_manual(values =c("#2A5783", "#99C5E3") )+geom_label(data = plotdata |>group_by(Series) |>filter(Year ==max(Year)),aes(label = scales::percent(rev_indexed , accuracy =1), color = Series),hjust =-0.1, vjust =0.5, show.legend =FALSE ) +scale_y_continuous(labels = scales::percent, limits =c(0,9), name =NULL) +scale_x_continuous(limits =c(base_year, 2025+2), breaks =c(2000, 2005, 2010, 2015, 2020,2025), name =NULL) +#labs(#title = "Medical Provider Assessments, as % of 1998 levels") +theme(base_size =16,legend.position ="bottom")#ggsave(plot_indexed, file= "generated/figure3.png")#ggsave(plot_indexed, file= "generated/figure3_notitle.png")plot_indexed``````{r}#| label: fig-medicaid-obbb-plot##| fig-width: 6##| fig-height: 4#| fig-cap: "Projected State Revenue Losses from OBBB Caps on Provider Tax Rates (MCO & Hospital Assessments Only). Source: University of Illinois Fiscal Futures Project."#| fig-cap-location: margin#obbb_raw <- readxl::read_excel("../Fiscal-Future-Topics/data/FY2025 Files/Medicaid/obbb_v6.xlsx")obbb_raw <- readxl::read_excel("../Fiscal-Future-Topics/data/FY2025 Files/Medicaid/obbb_v9.xlsx")obbb_raw <- obbb_raw |>mutate(Scenario =case_when( Scenario =="Baseline: no growth in patient revenues"~"Baseline: no growth", Scenario =="Modest growth in patient revenues"~"Modest Growth", Scenario =="Modest contraction in patient revenues"~"Modest Contraction"))obbb_tidy <- obbb_raw |>pivot_longer(`2025`:`2033`, names_to ="Year", values_to ="value") |>mutate(Year =as.numeric(Year))p_obbb <-ggplot(obbb_tidy, aes(x = Year, y = value, group=Scenario, color = Scenario)) +geom_line(linewidth =1.2) +geom_point(size =2) +geom_label(data = obbb_tidy |>group_by(Scenario) |>filter(Year ==max(Year)),aes(label = scales::dollar(value , accuracy =1)),hjust =-0.1, vjust =0.5, show.legend =FALSE )+scale_color_manual(values =c("#2A5783", "#6495BF", "#B9DDF1") )+scale_y_continuous(labels = dollar, name ="Millions") +scale_x_continuous(limits =c(2026, 2034), breaks =c(2026:2033), name =NULL) +theme(legend.position ="bottom",base_size =16) #+ # labs(title= "Projected State Revenue Losses\nfrom OBBB's Caps on Provider Tax Rates",#subtitle = "Based on MCO & Hospital Assessments Only")#ggsave(p_obbb, file= "generated/figure4.png")#ggsave(p_obbb, file= "generated/figure4_notitle.png")p_obbb```# Change from Previous Year```{r}#| fig-height: 6rev_long |>filter(Year == current_year | Year == past_year) |>mutate(Year =as.character(Year)) |>ggplot(aes(x = Dollars, y =reorder(Category, Dollars))) +geom_line(aes(group = Category) )+geom_text(aes(x =ifelse(Year == current_year, as.numeric(Dollars), NA), label =ifelse(Year == current_year, Category_name, "")), hjust =-0.2,size =2.8) +geom_point(aes(color = Year), size=2) +labs(title ="2024 to 2025 Change in Revenue", x ="Millions of Dollars" , y ="", caption ="") +scale_fill_manual(values =c("#d62828", "#003049"), labels =c("FY 2024", "FY 2025"))+scale_color_manual(values =c("#d62828", "#003049")) +theme_classic()+theme(legend.position ="bottom" ,axis.text.y =element_blank(),axis.ticks.y =element_blank(),axis.line.y.left =element_blank()) +scale_x_continuous(limits =c(0, 40000), labels = comma)exp_long |>filter(!Category %in%c("Check Me!", "CHECK ME") ) |>filter(Year == current_year | Year == past_year) |>mutate(Year =as.character(Year)) |>ggplot(aes(x = Dollars, y =reorder(Category, Dollars))) +geom_line(aes(group = Category) )+geom_text(aes(x =ifelse(Year == current_year, as.numeric(Dollars), NA), label =ifelse(Year == current_year, Category_name, "")), hjust =-0.2,size =2.8) +geom_point(aes(color = Year), size=2) +labs(title ="2024 to 2025 Change in Expenditures", x ="Millions of Dollars" , y ="", caption ="") +scale_fill_manual(values =c("#d62828", "#003049"), labels =c("FY 2024", "FY 2025"))+scale_color_manual(values =c("#d62828", "#003049")) +theme_classic()+theme(legend.position ="bottom" ,axis.text.y =element_blank(),axis.ticks.y =element_blank(),axis.line.y.left =element_blank())+scale_x_continuous(limits =c(0, 40000), labels = comma )```Each year, you will need to update the CAGR formulas! Change the filter() year.`calc_cagr` is a function created for calculating the CAGRs for different spans of time.```{r}exp_totals <- ff_exp |>rowwise() |>mutate(exp_TOTALS =sum(across(exp_402:exp_970), na.rm=TRUE))rev_totals <- ff_rev |>rowwise() |>mutate(rev_TOTALS =sum(across(rev_02:rev_78), na.rm=TRUE))rev_long <-pivot_longer(rev_totals, rev_02:rev_TOTALS, names_to =c("type","Category"), values_to ="Dollars", names_sep ="_") |>rename(Year = fy) |>mutate(Category_name =case_when( Category =="02"~"INDIVIDUAL INCOME TAXES" , Category =="03"~"CORPORATE INCOME TAXES" , Category =="06"~"SALES TAXES" , Category =="09"~"MOTOR FUEL TAX" , Category =="12"~"PUBLIC UTILITY TAXES" , Category =="15"~"CIGARETTE TAXES" , Category =="18"~"LIQUOR GALLONAGE TAXES" , Category =="21"~"INHERITANCE TAX" , Category =="24"~"INSURANCE TAXES, FEES & LICENSES" , Category =="27"~"CORP FRANCHISE TAXES & FEES" , Category =="30"~"HORSE RACING TAXES & FEES", # in Other Category =="31"~"MEDICAL PROVIDER ASSESSMENTS" , Category =="32"~"GARNISHMENT-LEVIES" , # dropped Category =="33"~"LOTTERY RECEIPTS" , Category =="35"~"OTHER TAXES" , Category =="36"~"RECEIPTS FROM REVENUE PRODUCING", Category =="39"~"LICENSES, FEES & REGISTRATIONS" , Category =="42"~"MOTOR VEHICLE AND OPERATORS" , Category =="45"~"STUDENT FEES-UNIVERSITIES", # dropped Category =="48"~"RIVERBOAT WAGERING TAXES" , Category =="51"~"RETIREMENT CONTRIBUTIONS" , # dropped Category =="54"~"GIFTS AND BEQUESTS", Category =="57"~"FEDERAL OTHER" , Category =="58"~"FEDERAL MEDICAID", Category =="59"~"FEDERAL TRANSPORTATION" , Category =="60"~"OTHER GRANTS AND CONTRACTS", #other Category =="63"~"INVESTMENT INCOME", # other Category =="66"~"PROCEEDS,INVESTMENT MATURITIES" , #dropped Category =="72"~"BOND ISSUE PROCEEDS", #dropped Category =="75"~"INTER-AGENCY RECEIPTS ", #dropped Category =="76"~"TRANSFER IN FROM OUT FUNDS", #other Category =="78"~"ALL OTHER SOURCES" , Category =="79"~"COOK COUNTY IGT", #dropped Category =="98"~"PRIOR YEAR REFUNDS", #dropped Category =="TOTALS"~"Total", T ~"CHECK ME" ) ) |>select(-type, -Category) |># drop extra columns type and Category numbergroup_by(Year, Category_name) |>summarise(Dollars=round(sum(Dollars, na.rm=TRUE), digits=2)) |>mutate(Category_name =str_to_title(Category_name))# creates wide version of table where each revenue source is a columnrevenue_wide2 <- rev_long |>pivot_wider(names_from = Category_name, values_from = Dollars) |># relocate("Other Revenue Sources **", .after = last_col()) |>relocate("Total", .after =last_col())``````{r}exp_long <-pivot_longer(exp_totals, exp_402:exp_TOTALS , names_to =c("type", "Category"), values_to ="Dollars", names_sep ="_") |>rename(Year = fy ) |>mutate(Category_name =case_when( Category =="402"~"Aging" , Category =="406"~"Agriculture", Category =="416"~"Central Management", Category =="418"~"Children & Family Services", Category =="420"~"Commerce and Economic Development", Category =="422"~"Natural Resources" , Category =="426"~"Corrections", Category =="427"~"Employment Security" ,# Category == "442" ~ "Human Rights" , Category =="442"~"Other Departments" , Category =="444"~"Human Services" ,# Category == "445" ~ "IL Power Agency" , Category =="445"~"Other Departments" ,# Category == "452" ~ "Labor" , Category =="452"~"Other Departments" , Category =="458"~"State Lottery" , Category =="478"~"Healthcare & Family Services (Net Medicaid)", Category =="482"~"Public Health", Category =="492"~"Revenue", Category =="494"~"Transportation" , Category =="497"~"Veterans' Affairs" , Category =="497"~"Other Departments" , Category =="507"~"Other Departments", # Category == "507" ~ "GOMB", Category =="532"~"Environmental Protection Agency" , Category =="557"~"Tollway" , Category =="589"~"State Employment Retirement (SERS)", Category =="901"~"Other State Pension Contribution", Category =="903"~"Debt Service", Category =="904"~"State Employee Healthcare", Category =="910"~"Legislative" , Category =="920"~"Judicial" , Category =="930"~"Elected Officials" , Category =="941"~"Public Safety" , Category =="942"~"Econ Dev & Infrastructure" , Category =="943"~"Central Services", Category =="944"~"Business & Professional Regulation" , Category =="945"~"Medicaid" , Category =="946"~"Capital Improvement" , Category =="948"~"Other Departments" , Category =="949"~"Other Boards & Commissions" , Category =="959"~"K-12 Education" , Category =="960"~"University Education", Category =="970"~"Local Govt Revenue Sharing", Category =="TOTALS"~"Total", T ~"CHECK ME"# T ~ "All Other Expenditures **") )) |>select(-type, -Category) |>group_by(Year, Category_name) |>summarise(Dollars =round(sum(Dollars, na.rm=TRUE), digits=2))expenditure_wide2 <- exp_long |>pivot_wider(names_from = Category_name, values_from = Dollars) |>relocate("Total", .after =last_col())```Things to do when updating the code:- Each year, you need to increase the cagr value by 1. The value should be the (current year - 1998). For FY23, this is 2023-1998 = 25. So all cagr values that were 24 will be changed to 25.```{r}max_cagr_years = current_year-1998# function for calculating the CAGRcalc_cagr <-function(df, n) { df <- df |>arrange(Category_name, Year) |>group_by(Category_name) |>mutate(cagr = ((`Dollars`/lag(`Dollars`, n)) ^ (1/ n)) -1,cagr =ifelse(is.na(cagr), 0, cagr))return(df)}cagr_calculations <-function(df){ # This works for one variable at a time df <- df cagr_max <-calc_cagr(df, max_cagr_years) |>summarize(cagr_max =round(sum(cagr*100, na.rm =TRUE), 2))# Update year in the filter() and summarize() commands to current year. cagr_10 <-calc_cagr(df, 10) |>filter(Year == current_year) |>summarize(cagr_10 =case_when(Year == current_year ~round(sum(cagr*100, na.rm =TRUE), 2))) cagr_5 <-calc_cagr(df, 5) |>filter(Year == current_year) |>summarize(cagr_5 =case_when(Year == current_year ~round(sum(cagr*100, na.rm =TRUE), 2))) cagr_3 <-calc_cagr(df, 3) |>filter(Year == current_year) |>summarize(cagr_3 =case_when(Year == current_year ~round(sum(cagr*100, na.rm =TRUE), 2))) cagr_2 <-calc_cagr(df, 2) |>filter(Year == current_year) |>summarize(cagr_2 =case_when(Year == current_year ~round(sum(cagr*100, na.rm =TRUE), 2))) cagr_1 <-calc_cagr(df, 1) |>filter(Year == current_year) |>summarize(cagr_1 =case_when(Year == current_year ~round(sum(cagr*100, na.rm =TRUE), 2)))# Combine all into one tibble result <-data.frame(cagr_max, cagr_10, cagr_5, cagr_3, cagr_2, cagr_1)return(result)}``````{r}#| label: tbl-good-exp-CAGR-withTotals#| tbl-cap: "Expenditure Category CAGRs with Total CAGR (Ordered Alphabetically)"#| tbl-cap-location: topCAGR_expenditures_summary_tot <-cagr_calculations(exp_long) |>filter(Category_name !="CHECK ME") |># You should havec checked that there were no unlabeled expenditures or revenues way before this point!! select(-c(Category_name.1, Category_name.2, Category_name.3, Category_name.4, Category_name.5 )) |>rename("Expenditure Category"= Category_name, "1 Year CAGR"= cagr_1, "2 Year CAGR"= cagr_2, "3 Year CAGR"= cagr_3, "5 Year CAGR"= cagr_5, "10 Year CAGR"= cagr_10, "27 Year CAGR"= cagr_max )totalrow <-which(grepl("Total", CAGR_expenditures_summary_tot$`Expenditure Category`))CAGR_expenditures_summary_tot <-move_to_last(CAGR_expenditures_summary_tot, totalrow) lastrow =nrow(CAGR_expenditures_summary_tot)CAGR_expenditures_summary_tot |>kbl(caption ="CAGR Calculations for All Expenditure Categories" , row.names=FALSE) |>kable_classic() |>row_spec(lastrow, bold = T, color ="black", background ="gray")``````{r}#| label: tbl-RevCAGRs-allcats#| tbl-cap: "Revenue Category CAGRs with Total CAGR (Ordered Alphabetically)"#| tbl-cap-location: topCAGR_revenue_summary_tot <-cagr_calculations(rev_long) |>filter(Category_name !="CHECK ME") |># You should havec checked that there were no unlabeled expenditures or revenues way before this point!! select(-c(Category_name.1, Category_name.2, Category_name.3, Category_name.4, Category_name.5 )) |>rename("Revenue Category"= Category_name, "1 Year CAGR"= cagr_1, "2 Year CAGR"= cagr_2, "3 Year CAGR"= cagr_3, "5 Year CAGR"= cagr_5, "10 Year CAGR"= cagr_10, "27 Year CAGR"= cagr_max )CAGR_revenue_summary_tot <-move_to_last(CAGR_revenue_summary_tot, 1)totalrow <-which(grepl("Total", CAGR_revenue_summary_tot$`Revenue Category`))CAGR_revenue_summary_tot <-move_to_last(CAGR_revenue_summary_tot, totalrow)lastrow =nrow(CAGR_revenue_summary_tot)CAGR_revenue_summary_tot |>kbl(caption ="CAGR Calculations for All Revenue Sources (Ordered Alphabetical)", row.names =FALSE) |>kable_classic() |>row_spec(lastrow, bold = T, color ="black", background ="gray")``````{r}#| label: tbl-AppendixItem1#| tbl-cap: "Yearly Change in Revenues - All FF Categories, Ordered from Largest to Smallest Revenue Amount"first_year =as.numeric(1998)n_year_change =as.numeric(current_year-1998)revenue_change2 <- rev_long |>filter(Year >= past_year | Year == first_year) |>filter(Category_name !="CHECK ME") |># You should havec checked that there were no unlabeled expenditures or revenues way before this point!! pivot_wider(names_from = Year , values_from = Dollars, names_prefix ="Dollars_") |>rename( Dollars_current = Dollars_2025,Dollars_lastyear = Dollars_2024 )|>mutate("Current FY ($ billions)"=round(Dollars_current/1000, digits =2),"Past FY ($ billions)"=round(Dollars_lastyear/1000, digits =2),"FY 1994 ($ billions)"=round(Dollars_1998/1000, digits =2),"1-Year Change"=percent((Dollars_current -Dollars_lastyear)/Dollars_lastyear, accuracy = .01)) |>left_join(CAGR_revenue_summary_tot, by =c("Category_name"="Revenue Category")) |>arrange(-`Current FY ($ billions)`)|>mutate(`27 Year CAGR`=percent(`27 Year CAGR`/100, accuracy=.01)) |>rename( "Revenue Category"= Category_name ) |>select(-c( Dollars_1998, Dollars_current, Dollars_lastyear, `1 Year CAGR`:`10 Year CAGR`))allother_row <-which(grepl("All Other", revenue_change2$`Revenue Category`))revenue_change2 <-move_to_last(revenue_change2, allother_row) # Move "All Other" to 2nd to last rowtotalrow <-which(grepl("Total", revenue_change2$`Revenue Category`))revenue_change2 <-move_to_last(revenue_change2, totalrow) # Move "Total" to last rowlastrow =nrow(revenue_change2)Table2 <- revenue_change2 |>filter(!is.na(`Revenue Category`)) |>kbl(caption ="Table 1. Yearly Change in Revenue (All Sources)", row.names =FALSE) |>kable_classic() |>row_spec(lastrow, bold = T, color ="black", background ="gray")#save_kable(Table2, file = "generated/App1_AllRevenueSources.html", self_contained = TRUE)Table2``````{r}#| label: tbl-AppendixItem2#| tbl-cap: "Yearly Change in Expenditures - All FF Categories, Ordered from Largest to Smallest Expenditure Amount"expenditure_change2 <- exp_long |>filter(Category_name !="CHECK ME") |># You should havec checked that there were no unlabeled expenditures or revenues way before this point!! group_by(Year, Category_name) |>summarize(Dollars =sum(Dollars, na.rm=TRUE)) |>ungroup() |>filter(Year >= past_year | Year == first_year ) |>pivot_wider(names_from = Year , values_from = Dollars, names_prefix ="Dollars_") |>rename( Dollars_current = Dollars_2025,Dollars_lastyear = Dollars_2024 )|>mutate("FY 2025 ($ billions)"=round(Dollars_current/1000, digits =2),"FY 2024 ($ billions)"=round(Dollars_lastyear/1000, digits =2),"FY 1998 ($ billions)"=round(Dollars_1998/1000, digits =2),"1-Year Change"=percent((Dollars_current -Dollars_lastyear)/Dollars_lastyear, accuracy = .01)) |>left_join(CAGR_expenditures_summary_tot, by =c("Category_name"="Expenditure Category")) |>arrange(-`FY 2025 ($ billions)`)|>mutate(`27 Year CAGR`=percent(`27 Year CAGR`/100, accuracy=.01)) |>select(-c( Dollars_1998, Dollars_current, Dollars_lastyear, `1 Year CAGR`:`10 Year CAGR`)) |>rename("Expenditure Category"= Category_name ) # |> filter(!is.na(`Expenditure Category`))allother_row <-which(grepl("All Other", expenditure_change2$`Expenditure Category`))expenditure_change2 <-move_to_last(expenditure_change2, allother_row) # Move "All Other" to 2nd to last rowtotalrow <-which(grepl("Total", expenditure_change2$`Expenditure Category`))expenditure_change2 <-move_to_last(expenditure_change2, totalrow) # Move "Total" to last rowlastrow =nrow(expenditure_change2)Table3<- expenditure_change2 |>kbl(row.names =FALSE) |>kable_classic() |>row_spec(lastrow, bold = T, color ="black", background ="gray")#save_kable(Table3, file = "generated/App2_AllExpenditureCategories.html", self_contained = TRUE)Table3 ```## Summary Tables - Largest CategoriesThe 10 largest revenue sources and 15 largest expenditure sources remain separate categories and all other smaller sources/expenditures are combined into "All Other Revenues (Expenditures)". These condensed tables are typically used in the Fiscal Futures articles. They were manually created in past years but this hopefully automates the process a bit until final formatting stages.```{r}#| label: tbl-top-rev-CAGRs#| tbl-cap: Largest Revenue Categories with CAGRsn_categories <-10+1# (Top 10 and then Total )categories <- rev_long |>filter(Year == current_year ) |>arrange(desc(Dollars)) |>slice(1:n_categories)rev_majorcats <- rev_long |>filter( (Year == current_year | Year == first_year)& Category_name %in% categories$Category_name) rev_long_majorcats <- rev_long |>mutate(Category_name =ifelse(Category_name %in% rev_majorcats$Category_name, Category_name, "All Other Sources"),Category_name =ifelse(Category_name =="Total", "Total Revenue", Category_name)) |>group_by(Year, Category_name) |>summarize(Dollars =sum(Dollars, na.rm=TRUE))# creates wide version of table where each revenue source is a columnrevenue_wide_majorcats <- rev_long_majorcats |>pivot_wider(names_from = Category_name, values_from = Dollars) |>relocate("All Other Sources", .after =last_col()) |>relocate("Total Revenue", .after =last_col())``````{r}#| label: tbl-top10-rev-CAGRs#| tbl-cap: Top 10 Revenue Sources with CAGRs#| tbl-cap-location: topCAGR_revenue_majorcats_tot <-cagr_calculations(rev_long_majorcats) |>select(-c(Category_name.1, Category_name.2, Category_name.3, Category_name.4, Category_name.5 )) |>rename("Revenue Category"= Category_name, "1 Year CAGR"= cagr_1, "2 Year CAGR"= cagr_2, "3 Year CAGR"= cagr_3, "5 Year CAGR"= cagr_5, "10 Year CAGR"= cagr_10, "27 Year CAGR"= cagr_max )allother_row <-which(grepl("All Other", CAGR_revenue_majorcats_tot$`Revenue Category`))CAGR_revenue_majorcats_tot <-move_to_last(CAGR_revenue_majorcats_tot, allother_row) # Move "All Other" to 2nd to last rowtotalrow <-which(grepl("Total", CAGR_revenue_majorcats_tot$`Revenue Category`))CAGR_revenue_majorcats_tot <-move_to_last(CAGR_revenue_majorcats_tot, totalrow) # Move "Total" to last rowlastrow =nrow(CAGR_revenue_majorcats_tot)CAGR_revenue_majorcats_tot |>kbl(caption ="CAGR Calculations for Largest Revenue Sources", row.names =FALSE) |>kable_classic() |>row_spec(lastrow, bold = T, color ="black", background ="gray")``````{r}#| label: build-Table1### Yearly change summary table for Top 10 Revenues ###revenue_change_majorcats <- rev_long_majorcats |>filter(Year >= past_year | Year == first_year) |>pivot_wider(names_from = Year , values_from = Dollars, names_prefix ="Dollars_") |>rename( Dollars_current = Dollars_2025,Dollars_lastyear = Dollars_2024 )|>mutate("Current FY ($ billions)"=round(Dollars_current/1000, digits =2),"Previous FY ($ billions)"=round(Dollars_lastyear/1000, digits =2),"FY 1998 ($ billions)"=round(Dollars_1998/1000, digits =2),"1-Year Change"=percent((Dollars_current -Dollars_lastyear)/Dollars_lastyear, accuracy = .01), ) |>left_join(CAGR_revenue_majorcats_tot, by =c("Category_name"="Revenue Category") ) |>arrange(-`Current FY ($ billions)`)|>mutate(`27 Year CAGR`=percent(`27 Year CAGR`/100, accuracy=.01)) |>select(-c(Dollars_1998, Dollars_current, Dollars_lastyear, `1 Year CAGR`:`10 Year CAGR` )) |>rename("Revenue Category"= Category_name )allother_row <-which(grepl("All Other", revenue_change_majorcats$`Revenue Category`))revenue_change_majorcats <-move_to_last(revenue_change_majorcats, allother_row) # Move "All Other" to 2nd to last rowtotalrow <-which(grepl("Total", revenue_change_majorcats$`Revenue Category`))revenue_change_majorcats <-move_to_last(revenue_change_majorcats, totalrow) # Move "Total" to last rowlastrow =nrow(revenue_change_majorcats)Table1 <- revenue_change_majorcats|>kbl(caption ="Yearly Change in Revenue for Main Revenue Sources", row.names =FALSE, align ="l") |>kable_classic() |>row_spec(lastrow, bold = T, color ="black", background ="gray")``````{r}#| label: tbl-TABLE1#| tbl-cap: "Top 10 Revenue Sources with CAGRs"#save_kable(Table1, file = "generated/TABLE1.html", self_contained = TRUE)Table1``````{r}#| label: tbl-top-exp-CAGRs#| tbl-cap: Largest Expenditure Categories with CAGRsn_categories <-10+1# (Top 9 and then Total )# keep top 10 largest categories or categories larger than 2 billion for final table in report (not a set rule, changes each year depending what the focus of the report is or what is highlighted.)categories <- exp_long |>filter(Year == current_year & Category_name !="State Employment Retirement (SERS)" ) |>arrange(desc(Dollars)) |>slice(1:n_categories)exp_majorcats <- exp_long |>filter( (Year == current_year | Year == first_year)& Category_name %in% categories$Category_name) exp_long_majorcats <- exp_long |>mutate(Category_name =ifelse(Category_name %in% exp_majorcats$Category_name, Category_name, "All Other Expenditures **"),Category_name =ifelse(Category_name =="Total", "Total Expenditures", Category_name)) |>group_by(Year, Category_name) |>summarize(Dollars =sum(Dollars, na.rm=TRUE))# expenditure_wide_majorcats <- exp_long_majorcats |> # pivot_wider(names_from = Category_name, # values_from = Dollars) |># relocate("All Other Expenditures **", .after = last_col()) |># relocate("Total Expenditures", .after = last_col())# CAGR values for largest expenditure categories and combined All Other ExpendituresCAGR_expenditures_majorcats_tot <-cagr_calculations(exp_long_majorcats) |>select(-c(Category_name.1, Category_name.2, Category_name.3, Category_name.4, Category_name.5 )) |>rename("Expenditure Category"= Category_name, "1 Year CAGR"= cagr_1, "2 Year CAGR"= cagr_2, "3 Year CAGR"= cagr_3, "5 Year CAGR"= cagr_5, "10 Year CAGR"= cagr_10,"27 Year CAGR"= cagr_max )allother_row <-which(grepl("Other", CAGR_expenditures_majorcats_tot$`Expenditure Category`))CAGR_expenditures_majorcats_tot <-move_to_last(CAGR_expenditures_majorcats_tot, allother_row) # Move "All Other" to 2nd to last rowtotalrow <-which(grepl("Total", CAGR_expenditures_majorcats_tot$`Expenditure Category`))CAGR_expenditures_majorcats_tot <-move_to_last(CAGR_expenditures_majorcats_tot, totalrow) # Move "Total" to last rowlastrow =nrow(CAGR_expenditures_majorcats_tot)CAGR_expenditures_majorcats_tot|>kbl(caption ="CAGR Calculations for Largest Expenditure Categories" , row.names=FALSE) |>kable_classic() |>row_spec(lastrow, bold = T, color ="black", background ="gray")# Yearly change for Top n largest expenditure categoriesexpenditure_change_majorcats <- exp_long_majorcats |>filter(Year >= past_year | Year == first_year & Category_name !="SERS") |>pivot_wider(names_from = Year , values_from = Dollars, names_prefix ="Dollars_") |>rename( Dollars_current = Dollars_2025,Dollars_lastyear = Dollars_2024 )|>mutate("Current FY ($ Billions)"=round(Dollars_current/1000, digits =2),"Previous FY ($ Billions)"=round(Dollars_lastyear/1000, digits =2),"FY 1998 ($ Billions)"=round(Dollars_1998/1000, digits =2),"1-Year Change"=percent((Dollars_current -Dollars_lastyear)/Dollars_lastyear, accuracy = .01), ) |>left_join(CAGR_expenditures_majorcats_tot, by =c("Category_name"="Expenditure Category")) |>arrange(-`Current FY ($ Billions)`)|>mutate(`27 Year CAGR`=percent(`27 Year CAGR`/100, accuracy=.01)) |>select(-c(Dollars_1998, Dollars_current, Dollars_lastyear, `1 Year CAGR`:`10 Year CAGR` )) |>rename(# "1-Year Change" = `1 Year CAGR`,"27 Year Change"=`27 Year CAGR`, "Expenditure Category"= Category_name )allother_row <-which(grepl("All Other", expenditure_change_majorcats$`Expenditure Category`))expenditure_change_majorcats <-move_to_last(expenditure_change_majorcats, allother_row) # Move "All Other" to 2nd to last rowtotalrow <-which(grepl("Total", expenditure_change_majorcats$`Expenditure Category`))expenditure_change_majorcats <-move_to_last(expenditure_change_majorcats, totalrow) # Move "Total" to last rowlastrow =nrow(expenditure_change_majorcats)expenditure_change_majorcats |>kbl(caption ="Yearly Change in Expenditures", row.names =FALSE, align ="l") |>kable_classic() |>row_spec(lastrow, bold = T, color ="black", background ="gray")```Export summary file with totals:```{r exportexcelfile, warning = TRUE, message = TRUE}# Saves main items in one excel file named `summary_file.xlsx`. Delete `eval=FALSE` to run on local computer.#install.packages("openxlsx")library(openxlsx)#aggregated_totals_majorcats = rbind(rev_long, exp_long)todaysdate = Sys.Date()dataset_names <- list(#'Aggregate Revenues' = revenue_wide2, #'Aggregate Expenditures' = expenditure_wide2, 'Table 1' = revenue_change_majorcats, #Top categories with yearly change, 23 yr cagr 'Table 2' = expenditure_change_majorcats, 'Appendix 1' = revenue_change2, 'Appendix 2' = expenditure_change2, 'CAGR Rev-MajorCats' = CAGR_revenue_majorcats_tot, # Categories Match Table 1 in paper 'CAGR Exp-MajorCats' = CAGR_expenditures_majorcats_tot, 'Fiscal Gap' = year_totals, # Total Revenue, Expenditure, and Fiscal gap per year 'aggregated_totals_long' = aggregated_totals_long#, # all data in long format. Good for creating pivot tables in Excel # 'aggregated_fewercategories' = aggregated_totals_majorcats # combines categories that have small amounts into "All Other" category )# no pension categories included as separate category (except for specific pension repayments that happened in some years.) # pension costs are included with K-12 education, university education, etcwrite.xlsx(dataset_names, file = paste0("../Fiscal-Future-Topics/data/FY", current_year, " Files/summary_file_FY", current_year,"_", todaysdate, ".xlsx"))``````{r export-datahub-downloads}library(openxlsx)wb <- createWorkbook()# ---------- Expenditures sheet ----------addWorksheet(wb, "Expenditures")writeDataTable( wb, "Expenditures", datahub_exp, tableStyle = "TableStyleMedium2")col_widths <- pmax( nchar(names(datahub_exp)), sapply(datahub_exp, function(x) max(nchar(as.character(x)), na.rm = TRUE)))setColWidths( wb, "Expenditures", cols = 1:ncol(datahub_exp), widths = col_widths + 3)# ---------- Revenues sheet ----------addWorksheet(wb, "Revenues")writeDataTable( wb, "Revenues", datahub_rev, tableStyle = "TableStyleMedium2")col_widths <- pmax( nchar(names(datahub_rev)), sapply(datahub_exp, function(x) max(nchar(as.character(x)), na.rm = TRUE)))setColWidths( wb, "Revenues", cols = 1:ncol(datahub_rev), widths = col_widths + 3)# ---------- README sheet ----------addWorksheet(wb, "README")readme_text <- data.frame( README = c( "Fiscal Futures DataHub Download", "", paste0("Last updated: ", todaysdate), "", "This file contains data downloaded from the Institute of Government and Public Affairs DataHub:", "https://igpa.uillinois.edu/igpa-data-hub", "", "Dollar values are in 1000s of nominal dollars", "", "Full documentation is available on GitHub:", "https://github.com/igpa-uillinois/Fiscal-Futures", "", "Dataset design and construction:", "Professor David Merriman (University of Illinois Chicago)", "Email: dmerrim@uic.edu" ))writeData(wb, "README", readme_text, colNames = FALSE)setColWidths(wb, "README", 1, 110)wrap <- createStyle(wrapText = TRUE)addStyle(wb, "README", wrap, rows = 1:nrow(readme_text), cols = 1, gridExpand = TRUE)# ---------- Save ----------saveWorkbook( wb, paste0( "../Fiscal-Future-Topics/data/FY", current_year, " Files/IGPA_Datahub_Download", "_", todaysdate, ".xlsx" ), overwrite = TRUE)``````{r}#| eval: false#| include: false#| ## left for reference in case anyone wants it without formatting.## Unformatted version of excel filereadme_text <-data.frame(README =paste("This file contains data downloaded from the Institute of Government and Public Affairs at the University of Illinois's DataHub","https://igpa.uillinois.edu/igpa-data-hub","","Dollar values are in 1000s of nominal dollars","","The data is fully documented on the Github for the project","https://github.com/igpa-uillinois/Fiscal-Futures","","Professor David Merriman of the University of Illinois Chicago is responsible for the design and construction of the data set.","You may contact him at dmerrim@uic.edu",sep ="\n" ))dataset_names <-list('Expenditures'= datahub_exp,'Revenues'= datahub_rev,'README'= readme_text )write.xlsx(dataset_names, file =paste0("../Fiscal-Future-Topics/data/FY", current_year, " Files/datahub_download", current_year,"_", todaysdate, ".xlsx"))```
