FY2025 Revenue Report

Code

knitr::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 format
label_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 year
past_year=current_year-1

rev_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"))

Report Tables and Figures

Figure 1

Table 1

table output

Yearly Change in Revenue for Main Revenue Sources
Revenue Category	Current FY ($ billions)	Previous FY ($ billions)	FY 1998 ($ 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%
All Other Sources	18.50	18.62	5.63	-0.65%	4.50%
Total Revenue	118.16	115.13	32.03	2.64%	4.95%

Figure 2

Figure 3

Logic & Background (from workbook “logicmodel”)

Goal. Back-of-the-envelope estimate of OBBB’s impact on Illinois’ ability to levy and collect provider taxes. Dollar amounts below are millions, nominal.

Tax revenues (FY2025, as provided)

MCO assessments: $1,821.5. Illinois Comptroller
Hospital assessments: $2,004.6. Illinois Comptroller
Total (MCO + Hospital): $3,826.1
Healthcare Provider Supplemental Assessment (County Hospital?): $886.5 (see “For_Alea” tab in the workbook)
Alternative assumption (add county hospital assessment): $4,712.6
Assumed FY2025 revenue base used here: $4,712.6. FF summary workbook

Tax base & rate assumption

We need a dollar tax base (akin to net patient revenues). We observe dollars per bed-day / per member, but not a single base series.
Use an assumed tax rate of 5.0% for hospital and MCO assessments. Why 5%? KFF reports typical ranges 3.5%–5.5% for hospitals; MCO rates less clear.
- Source: Burns, Hinton, Williams, Rudowitz, “5 Key Facts About Medicaid and Provider Taxes,” KFF, Mar 26, 2025. https://www.kff.org/medicaid/issue-brief/5-key-facts-about-medicaid-and-provider-taxes/

Timing & growth

First 0.5 pp reduction required Oct 1, 2028 → hits SFY 2029 for 9 of 12 months (75%).
Annual growth in base: 1.0% (conservative; enrollment/utilization likely softening).

Figure 4

Appendix Item 1

table output

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.15%	6.64%
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.21	0.21	0.12	-2.69%	1.92%
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.13	32.03	2.64%	4.95%

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.

0121 estate tax refund

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 Funds
  filter(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 refunds
  mutate(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()
       )

Code

tax_refund_long <- exp_temp |>           # fund != "0401" # removes State Trust Funds
  filter(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 refunds
  mutate(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 calculations

exp_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 493 –> 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.

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 fund
    TRUE ~ 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 excluded
  mutate(object = ifelse((pension > 0 & in_ff == "0"), "4431", object)) |> 
  # changes weird teacher & judge retirement system  pensions object to normal pension object 4431
  mutate(pension =  ifelse(pension > 0 & in_ff == "0", 6, pension)) |> # coded as 6 if it was supposed to be excluded. 
  mutate(in_ff = ifelse(pension > 0, "1", in_ff))



# all other pensions objects  codes get agency code 901 for State Pension Contributions
exp_temp <- exp_temp |> 
  mutate(agency = ifelse(pension > 0, "901", as.character(agency)),
         agency_name = ifelse(agency == "901", "State Pension Contributions", as.character(agency_name)))

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 Medicaid
  summarize(expenditure = sum(expenditure))

Drop all cash transfers between funds, statutory transfers, and purchases of investments from expenditure data.

Code

transfers_drop <- exp_temp |> filter(
  agency == "799" | # statutory transfers
           object == "1993" |  # interfund cash transfers
           object == "1298") # purchase of investments
transfers_drop # items being dropped,

Code

# 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 costs


exp_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 2012
       TRUE ~ 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 number

healthcare_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 MFT
                    TRUE ~ 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

Debt

Code

debt_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)

Code

tollway_exp <- exp_temp |> filter(fund == "0455") |> group_by(fy) |> summarize(expenditure = sum(expenditure))
                                                                                                    #tollway_exp |> ggplot() + geom_line(aes(x=fy, y=expenditure)) + labs(title = "Fund 0455 from Expenditure: All Tollway Expenditures", caption = "Data from IOC Expenditure Files. Fund 0455 is the IL State Tollway")


# all tollway revenues, not just bond proceeds
alltollway<-rev_temp |> filter(fund == "0455" & source != "0571") |> group_by(fy) |> summarize(sum = sum(receipts, na.rm = TRUE))


# tollway bond proceeds
tollway_bondproc <- rev_temp |> filter(fund == "0455" & source == "0571" ) |> group_by(fy) |> summarize(sum = sum(receipts, na.rm = TRUE))

#alltollway |>  ggplot() + geom_line(aes(x=fy, y=sum)) + labs(title = "Fund 0455 - All Tollway Revenue", caption = "Data from IOC Revenue Files. Fund 0455 is the IL State Tollway Revenue") 

#tollway_bondproc |> ggplot() + geom_line(aes(x=fy, y=sum)) + labs(title = "Fund 0455 - Tollway Revenue: Tollway Bond Proceeds", caption = "Data from IOC Revenue Files. Fund 0455 is the IL State Tollway Revenue")
  
#ggplot() + geom_line(data=tollway_bondproc, aes(x=fy, y=sum)) + labs(title = "Fund 0455 - Tollway Revenue: Tollway Bond Proceeds", caption = "Data from IOC Revenue Files. Fund 0455 is the IL State Tollway Revenue")

#tollwaydebt |> ggplot() + geom_line(aes(x=fy, y=sum)) + labs(title = "Tollway Debt Service", caption = "Debt service includes principal and interest for the Illinois Tollway. Object = 8800 and fund = 0455")


#tollway debt principal and interest
tollwaydebt <- exp_temp |>filter(object == "8800" & fund == "0455") |> group_by(fy) |> summarize(sum=sum(expenditure)) 

# Tollway agency expenditures = SAME as filtering by fund == 0455
#tollway<-exp_temp |> filter(agency == "557")
#exp_temp |> filter(agency == "557") |> group_by(fy) |> summarize(sum = sum(expenditure)) |> arrange(-fy)

# contributions and benefits paid comparison
ggplot()+
  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.

Code

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" ~ "948", # 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"  ~ "Illinois Power Agency",
             group == "452" ~ "Labor",
             group == "458" ~ "State Lottery",
             group == "489" ~ "SERS",
             group == "478" ~ "Healthcare and Family Services",
             group == "482" ~ "Public Health",
             group == "497" ~ "Veteran's Affairs",
             group == "507" ~ "GOMB",
             group == "901" ~ "STATE PENSION CONTRIBUTION",
             group == "903" ~ "DEBT SERVICE",
             group == "910" ~ "LEGISLATIVE"  ,
             group == "920" ~ "JUDICIAL" ,
             group == "930" ~ "ELECTED OFFICERS" , 
             group == "940" ~ "OTHER HEALTH-RELATED", 
             group == "941" ~ "PUBLIC SAFETY" ,
             group == "942" ~ "ECON DEVT & INFRASTRUCTURE" ,
             group == "943" ~ "CENTRAL SERVICES",
             group == "944" ~ "BUS & PROFESSION 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 |> 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 number
rev_temp <- rev_temp |> 
  mutate(agency = case_when(
    (agency=="438"| agency=="475" |agency == "505") ~ "440",
    # financial institution &  professional regulation &
     # banks and real estate  --> coded as  financial and professional reg
    agency == "473" ~ "588", # nuclear safety moved into IEMA
    (agency =="531" | agency =="577") ~ "532", # coded as EPA
    (agency =="556" | agency == "538") ~ "406", # coded as agriculture
    agency == "560" ~ "592", # IL finance authority (fire trucks and agriculture stuff)to state fire marshal
    agency == "570" & fund == "0011" ~ "494",   # city of Chicago road fund to transportation
    TRUE ~ (as.character(agency))))

Federal Transfers

Code

#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()  )

Code

rev_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 Expenditures

Code

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))

med_reimburse <- rev_temp |> 
  filter(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))



ggplot()+
  geom_line(data=medicaid_cost, aes(x=fy, y=sum, color = "Expenditures")) + 
  geom_line(data=med_reimburse, aes(x=fy, y = sum, color = "Reimbursements")) + 
  scale_x_continuous(n.breaks = 6) +
  labs(title = "Medicaid reimbursements and 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 codes
    rev_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 sources
      TRUE ~ as.character(rev_type)))
# if not mentioned, then rev_type as it was

Code

# # 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

Code

# 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 drop
drop_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_type

table(rev_temp$rev_type_new)


   02    03    06    09    12    15    18    21    24    27    30    31    33 
  209   148   901   148   629   291    51  1423   517    85   677   140   160 
   35    36    39    42    48    54    57    58    59    60    63    78 
  751  5668 10376  3313    34  1351  7124   698   247   109  5797 12649

Code

rev_temp |> 
  group_by(fy, rev_type_new) |> 
  summarize(total_reciepts = sum(receipts)/1000000) |>
  pivot_wider(names_from = rev_type_new, values_from = total_reciepts, names_prefix = "rev_")

Code

rm(rev_1998_2022)
rm(exp_1998_2022)

Pivoting and Merging

Revenues

Code

ff_rev <- rev_temp |> 
  group_by(rev_type_new, fy) |> 
  summarize(sum_receipts = sum(receipts, na.rm=TRUE)/1000000 ) |>
  pivot_wider(names_from = "rev_type_new", values_from = "sum_receipts", names_prefix = "rev_")

 
ff_rev <- mutate_all(ff_rev, replace_na, 0)

# 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)"}

ff_rev |>  mutate_all(round, digits=0)

Table 1: Pivoted Revenue Table ($ Millions) - Intermediate Step. Not actually used for anything other than to have output in same format as old STATA output to make it easily comparable.

Expenditures

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_970
  left_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_970

ff_exp # not labeled

Table 2: Pivoted Expenditure Categories ($ Millions). Intermediate step. Not actually used for anything other than having output similar to past STATA output.

All Funds vs General Funds

Code

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)

Code

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)

Code

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 = "$")

Code

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)

Code

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)

Code

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",
             Category == "275" ~ "JRS",
             Category == "402" ~ "AGING" ,
             Category == "406" ~ "AGRICULTURE", 
             Category == "416" ~ "CENTRAL MANAGEMENT",
             Category == "418" ~ "CHILDREN AND FAMILY SERVICES", 
             Category == "420" ~ "COMMERCE AND ECONOMIC OPPORTUNITY",
             Category == "422" ~ "NATURAL RESOURCES" ,
             Category == "426" ~ "CORRECTIONS",
             Category == "427" ~ "EMPLOYMENT SECURITY" ,
             
             Category == "442" ~ "Human Rights" ,
             
             Category == "444" ~ "Human Services" ,
             Category == "445" ~ "IL Power Agency" ,
             Category == "448" ~ "Innovation and Technology", # AWM added fy2022
             
             Category == "452" ~ "Labor" ,
             
             Category == "458" ~ "State Lottery" ,
             
             Category == "478" ~ "FAMILY SERVICES net Medicaid",
             Category == "482" ~ "PUBLIC HEALTH", 
             Category == "492" ~ "REVENUE", 
             Category == "493" ~  "Teacher Retirmeent System (TRS)",
             Category == "494" ~ "TRANSPORTATION" ,
          
             Category == "489" ~ "SERS",
             Category == "507" ~ "GOMB",
             Category == "497" ~ "VETERNS' AFFAIRS" ,
             Category == "532" ~ "ENVIRONMENTAL PROTECT AGENCY" ,
             Category == "557" ~ "IL STATE TOLL HIGHWAY AUTH" ,
             Category == "589" ~ "State Employment Retirement System (SERS)",
             Category == "684" ~ "IL COMMUNITY COLLEGE BOARD", 
             Category == "691" ~ "IL STUDENT ASSISTANCE COMM" ,
             Category == "693" ~ "SURS",
             Category == "901" ~ "STATE PENSION CONTRIBUTION",
             Category == "903" ~ "DEBT SERVICE",
             Category == "904" ~ "State Employee Healthcare",
             Category == "910" ~ "LEGISLATIVE"  ,
             Category == "920" ~ "JUDICIAL" ,
             Category == "930" ~ "ELECTED OFFICERS" , 
             Category == "940" ~ "OTHER HEALTH-RELATED", 
             Category == "941" ~ "PUBLIC SAFETY" ,
             Category == "942" ~ "ECON DEVT & INFRASTRUCTURE" ,
             Category == "943" ~ "CENTRAL SERVICES",
             Category == "944" ~ "BUS & PROFESSION 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 Transfers",
             T ~ "CHECK ME!")
           ) |> 
  mutate(Category_name = str_to_title(Category_name))



# combine revenue and expenditures into one data frame
aggregated_totals_long <- rbind(rev_long, exp_long)


aggregated_totals_long |> mutate(`Dollars (Millions)` = round(Dollars, digits = 0)) |> select(-Dollars) |>
  select(Year, Category_name, `Dollars (Millions)`, type, Category)

Table 3: 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 year

year_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 4: Year totals without gap line

Fiscal Gap for each Fiscal Year ($ Millions)
Year	Expenditures	Revenue	Fiscal Gap
1998	31242	32030	788
1999	33846	33966	120
2000	37343	37051	-291
2001	40359	38286	-2073
2002	42067	37922	-4144
2003	42612	38453	-4159
2004	53025	42612	-10413
2005	45361	44306	-1055
2006	48061	46170	-1891
2007	51130	49494	-1636
2008	54172	51643	-2529
2009	56751	51466	-5284
2010	58049	51197	-6852
2011	58422	56304	-2118
2012	59864	58422	-1442
2013	63286	63102	-185
2014	66965	65269	-1696
2015	69940	66590	-3350
2016	63931	64155	224
2017	71727	63660	-8067
2018	74972	73015	-1957
2019	74405	74638	232
2020	81605	80589	-1017
2021	92888	95206	2319
2022	100068	116061	15993
2023	111973	111774	-199
2024	115004	115129	125
2025	114963	118163	3200
^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 text
annotation <- 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 graph
  geom_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 expenditures
  geom_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 text
annotation <- 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 text
  geom_text(data = annotation_nums, aes(x = x, y = y, label = scales::dollar(label, accuracy = 0.01L)), size = 3) +  ## Number locations and text
  theme_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

Expenditure and revenue amounts in billions of dollars:

Code

exp_long |>
  filter(Year == current_year) |>
  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) |>
  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)”:

Code

exp_long |>
  filter( Year == current_year) |>
  mutate(rank = rank(Dollars),
        Category_name = ifelse(rank > 13, 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) |>
  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")

Top 3 Revenues

Code

annotation <- 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) +
  scale_linetype_manual(values = c("dotted", "dashed", "dotdash")) +
  theme(legend.position = "right") +
  labs( 
       y = "Billions of Nominal Dollars",
       color = NULL, lty = NULL) 

Figure1 <- top3  +
  geom_line(data = year_totals, aes(x=Year, y = Revenue/1000), lwd = 1)

Code

ggplot2::ggsave("generated/FIGURE1.png", Figure1, width = 11, height = 6, dpi = 300)
Figure1

Code

annotation <- 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))

Figure 11: Top 3 Own-Source Revenues Sources

Sales Tax

Code

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_line(linewidth = 0.9) +
  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 Graphs
base_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_line(linewidth = 0.9) +
  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)    else NA),
  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) else NA)
)
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)

Code

ggsave("generated/figure2.png", p_sales4, width = 12, height = 8, dpi = 300)

Own Source and Fed Transfers

Code

ownsource_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")

Table 5: Own Source and Federal Revenue

Code

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 Expenditures

Key funds: Healthcare Provider Relieve (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

Code

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")

Code

rev_temp |>
  filter(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")

Code

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

Code

provider_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_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 ) + 
  geom_text(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-1, 2025 + 1), breaks = c(2000, 2005, 2010, 2015, 2020,2025), name = NULL) + 

  labs(
    title = "Medical Provider Assessments, as % of 1998 levels") +
theme(base_size = 14,
legend.position = "top")

ggsave(plot_indexed, file= "generated/figure3.png")
plot_indexed

Code

obbb_raw <- readxl::read_excel("../Fiscal-Future-Topics/data/FY2025 Files/Medicaid/obbb_v6.xlsx")

obbb_tidy <- obbb_raw |>
pivot_longer(`2025`: `2033`, names_to = "Year", values_to = "value")

p_obbb <- ggplot(obbb_tidy, aes(x = Year, y = value, group=Scenario, color = Scenario)) +
  geom_line(linewidth = 1.2) +
  geom_point(size = 2) +
  scale_y_continuous(labels = dollar, name =NULL) +
  theme(legend.position = "bottom",
base_size = 12) + labs(title= "Projected State Revenue Losses from OBBB's Caps on Provier Tax Rates",
subtitle = "Based on MCO & Hospital Assessments Only")

ggsave(p_obbb, file= "generated/figure4.png")
p_obbb

Figure 13: Projected State Revenue Losses from OBBB Caps on Provider Tax Rates (MCO & Hospital Assessments Only). Source: University of Illinois Fiscal Futures Project.

Change from Previous Year

Code

rev_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, 35000), 
                     labels = comma)

Code

exp_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 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, 35000), 
                     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.

Code

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 number
  group_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 column
revenue_wide2 <- rev_long |> 
  pivot_wider(names_from = Category_name, 
              values_from = Dollars) |>
  #  relocate("Other Revenue Sources **", .after = last_col()) |>
  relocate("Total", .after =  last_col())

Code

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 AND FAMILY SERVICES", 
             Category == "420" ~ "Community Development",
             Category == "422" ~ "NATURAL RESOURCES" ,
             Category == "426" ~ "CORRECTIONS",
             Category == "427" ~ "EMPLOYMENT SECURITY" ,
             Category == "442" ~ "Human Rights" ,
             
             Category == "444" ~ "Human Services" ,
             Category == "445" ~ "IL Power Agency" ,
             Category == "452" ~ "Labor" ,
             Category == "458" ~ "State Lottery" ,
             
             Category == "478" ~ "HEALTHCARE & FAM SER NET OF MEDICAID", 
             Category == "482" ~ "PUBLIC HEALTH", 
             Category == "492" ~ "REVENUE", 
             Category == "494" ~ "Transportation" ,
             Category == "497" ~ "Veterans' Affairs" ,
             Category == "507" ~ "GOMB",

             Category == "532" ~ "ENVIRONMENTAL PROTECT AGENCY" ,
             Category == "557" ~ "Tollway" ,
             Category == "901" ~ "State Pension Contribution",
             Category == "903" ~ "Debt Service",
             Category == "904" ~ "State Employee Healthcare",
             Category == "910" ~ "LEGISLATIVE"  ,
             Category == "920" ~ "JUDICIAL" ,
             Category == "930" ~ "ELECTED OFFICERS" , 
             Category == "941" ~ "Public Safety" ,
             Category == "942" ~ "ECON DEVT & INFRASTRUCTURE" ,
             Category == "943" ~ "CENTRAL SERVICES",
             Category == "944" ~ "BUS & PROFESSION 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)) |> 
  mutate(Category_name = str_to_title(Category_name))

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.

Code

max_cagr_years = current_year-1998

# function for calculating the CAGR
calc_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)   |>
  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 6: 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	7.81	4.73	9.00	11.75	8.97	6.25
Agriculture	2.07	5.95	11.84	12.20	19.22	16.12
Bus & Profession Regulation	2.09	-1.51	6.57	7.16	7.82	5.97
Capital Improvement	4.91	2.18	24.62	29.79	19.71	12.80
Central Management	4.48	4.54	3.19	6.33	1.04	-0.30
Check Me	0.00	0.00	0.00	0.00	0.00	0.00
Children And Family Services	1.11	4.59	7.18	11.83	5.36	-2.37
Community Development	5.08	5.95	23.35	8.38	8.71	9.95
Corrections	2.21	2.53	2.96	5.82	2.19	-2.64
Debt Service	5.32	-0.26	0.16	-0.78	0.29	-14.24
Elected Officers	4.24	4.65	7.44	8.92	9.38	6.66
Employment Security	1.35	1.77	1.94	-1.78	-1.97	-2.05
Environmental Protect Agency	4.39	4.43	6.61	14.87	26.77	12.40
Gomb	-1.70	22.39	17.97	53.09	54.10	-22.53
Healthcare & Fam Ser Net Of Medicaid	5.39	0.84	5.89	7.40	4.54	0.72
Human Rights	2.74	3.40	8.30	11.01	13.50	15.58
Human Services	3.71	5.95	11.89	13.07	9.15	0.49
Il Power Agency	Inf	32.67	29.29	29.94	12.56	-13.27
Judicial	3.84	3.93	4.66	5.81	1.75	-1.59
K-12 Education	4.97	5.33	4.53	2.13	-0.68	-4.33
Labor	4.38	-2.04	14.53	12.47	15.45	7.04
Legislative	5.04	8.29	13.27	15.26	-0.33	-2.49
Local Govt Revenue Sharing	3.58	3.65	6.85	-4.57	-9.18	-6.88
Medicaid	7.08	7.30	8.95	6.10	2.76	2.33
Natural Resources	2.91	3.27	9.15	13.91	15.70	12.94
Other Boards & Commissions	5.01	3.58	10.90	12.94	3.86	1.05
Other Departments	22.33	6.48	4.61	5.21	8.84	15.61
Public Health	5.44	5.22	3.77	-6.99	-6.29	-7.80
Public Safety	5.03	7.70	2.01	-1.52	-2.84	-24.64
Revenue	3.79	10.10	0.79	-13.67	-37.87	-13.28
State Employee Healthcare	6.26	4.36	4.76	7.78	11.95	18.97
State Lottery	4.24	-0.65	13.33	12.20	-20.65	-12.17
State Pension Contribution	0.00	0.00	0.00	-100.00	-100.00	0.00
Tollway	6.33	0.16	0.08	-2.89	0.82	-2.62
Transportation	4.55	3.77	9.98	12.83	11.37	11.06
University Education	2.91	2.61	4.10	4.33	3.07	-1.12
Veterans' Affairs	3.98	3.14	4.49	8.22	9.50	5.72
Total	4.94	5.10	7.09	4.73	1.33	-0.04

Code

CAGR_revenue_summary_tot <- cagr_calculations(rev_long) |>   
  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 7: 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.92	-0.66	-1.29	-2.93	-6.40	-2.69
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.64	4.81	9.36	7.15	6.45	13.15
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.95	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) |>
  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 row

totalrow <- which(grepl("Total", revenue_change2$`Revenue Category`))
revenue_change2 <- move_to_last(revenue_change2, totalrow) # Move "Total" to last row

lastrow = 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 8: 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.15%	6.64%
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.21	0.21	0.12	-2.69%	1.92%
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.13	32.03	2.64%	4.95%

Code

expenditure_change2 <- exp_long |>
  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 row

totalrow <- which(grepl("Total", expenditure_change2$`Expenditure Category`))
expenditure_change2 <- move_to_last(expenditure_change2, totalrow) # Move "Total" to last row

lastrow = nrow(expenditure_change2)

expenditure_change2 |> 
  kbl(row.names = FALSE) |> 
  kable_classic() |>
    row_spec(lastrow, bold = T, color = "black", background = "gray")

Table 9: 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	34.22	33.44	5.40	2.33%	7.08%
K-12 Education	20.77	21.71	5.60	-4.33%	4.97%
Human Services	10.52	10.47	3.93	0.49%	3.71%
Local Govt Revenue Sharing	8.99	9.66	3.48	-6.88%	3.58%
Transportation	6.57	5.91	1.98	11.06%	4.55%
University Education	4.96	5.02	2.28	-1.12%	2.91%
State Employee Healthcare	3.76	3.16	0.73	18.97%	6.26%
Other Departments	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%
Corrections	1.78	1.83	0.99	-2.64%	2.21%
Community Development	1.77	1.61	0.47	9.95%	5.08%
Children And Family Services	1.75	1.79	1.30	-2.37%	1.11%
Aging	1.64	1.54	0.22	6.25%	7.81%
Public Safety	1.64	2.18	0.44	-24.64%	5.03%
Central Management	1.39	1.40	0.43	-0.30%	4.48%
Elected Officers	1.27	1.19	0.42	6.66%	4.24%
Revenue	1.20	1.39	0.44	-13.28%	3.79%
Environmental Protect Agency	0.99	0.88	0.31	12.40%	4.39%
Capital Improvement	0.94	0.83	0.26	12.80%	4.91%
State Lottery	0.88	1.00	0.29	-12.17%	4.24%
Judicial	0.78	0.79	0.28	-1.59%	3.84%
Public Health	0.67	0.73	0.16	-7.80%	5.44%
Healthcare & Fam Ser Net Of Medicaid	0.47	0.46	0.11	0.72%	5.39%
Natural Resources	0.43	0.38	0.20	12.94%	2.91%
Other Boards & Commissions	0.35	0.35	0.09	1.05%	5.01%
Bus & Profession Regulation	0.27	0.25	0.15	5.97%	2.09%
Employment Security	0.26	0.27	0.18	-2.05%	1.35%
Legislative	0.24	0.24	0.06	-2.49%	5.04%
Veterans' Affairs	0.16	0.15	0.06	5.72%	3.98%
Agriculture	0.13	0.11	0.07	16.12%	2.07%
Il Power Agency	0.04	0.05	0.00	-13.27%	Inf
Gomb	0.03	0.03	0.04	-22.53%	-1.70%
Human Rights	0.02	0.02	0.01	15.58%	2.74%
Labor	0.02	0.02	0.01	7.04%	4.38%
Check Me	0.00	0.00	0.00	NA	0.00%
State Pension Contribution	0.00	0.00	0.00	NA	0.00%
Total	114.96	115.00	31.24	-0.04%	4.94%

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 10: 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 column
revenue_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 row

totalrow <- 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 row

lastrow = 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 11: 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.37	-0.65
Total Revenue	4.95	5.90	7.95	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 row

totalrow <- which(grepl("Total", revenue_change_majorcats$`Revenue Category`))
revenue_change_majorcats <- move_to_last(revenue_change_majorcats, totalrow) # Move "Total" to last row

lastrow = 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")

Code

save_kable(Table1, file = "generated/TABLE1.html", self_contained = TRUE)

Table1

Table 12: Top 10 Revenue Sources with CAGRs

Yearly Change in Revenue for Main Revenue Sources
Revenue Category	Current FY ($ billions)	Previous FY ($ billions)	FY 1998 ($ 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%
All Other Sources	18.50	18.62	5.63	-0.65%	4.50%
Total Revenue	118.16	115.13	32.03	2.64%	4.95%

Code

n_categories <- 9 + 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 ) |>
  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 Expenditures


CAGR_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 row

totalrow <- 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 row

lastrow = 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 categories
expenditure_change_majorcats <- exp_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_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 row

totalrow <- which(grepl("Total", expenditure_change_majorcats$`Expenditure Category`))
expenditure_change_majorcats <- move_to_last(expenditure_change_majorcats, totalrow) # Move "Total" to last row

lastrow = 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 13: 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
Debt Service	5.32	-0.26	0.16	-0.78	0.29	-14.24
Human Services	3.71	5.95	11.89	13.07	9.15	0.49
K-12 Education	4.97	5.33	4.53	2.13	-0.68	-4.33
Local Govt Revenue Sharing	3.58	3.65	6.85	-4.57	-9.18	-6.88
Medicaid	7.08	7.30	8.95	6.10	2.76	2.33
State Employee Healthcare	6.26	4.36	4.76	7.78	11.95	18.97
Transportation	4.55	3.77	9.98	12.83	11.37	11.06
University Education	2.91	2.61	4.10	4.33	3.07	-1.12
All Other Expenditures **	3.98	3.80	6.41	4.43	-2.27	-2.01
Other Departments	22.33	6.48	4.61	5.21	8.84	15.61
Total Expenditures	4.94	5.10	7.09	4.73	1.33	-0.04

Yearly Change in Expenditures
Expenditure Category	Current FY ($ Billions)	Previous FY ($ Billions)	FY 1998 ($ Billions)	1-Year Change	27 Year Change
Medicaid	34.22	33.44	5.40	2.33%	7.08%
K-12 Education	20.77	21.71	5.60	-4.33%	4.97%
Human Services	10.52	10.47	3.93	0.49%	3.71%
Local Govt Revenue Sharing	8.99	9.66	3.48	-6.88%	3.58%
Transportation	6.57	5.91	1.98	11.06%	4.55%
University Education	4.96	5.02	2.28	-1.12%	2.91%
State Employee Healthcare	3.76	3.16	0.73	18.97%	6.26%
Other Departments	2.15	1.86	0.01	15.61%	22.33%
Debt Service	1.96	2.29	0.48	-14.24%	5.32%
All Other Expenditures **	21.06	21.49	7.34	-2.01%	3.98%
Total Expenditures	114.96	115.00	31.24	-0.04%	4.94%