FY2023 - Preparing New Fiscal Data for Fiscal Futures Project

library(haven)
library(formatR)
library(lubridate)
library(smooth)
library(forecast)
library(scales)
library(kableExtra)
library(ggplot2)
library(readxl)
library(tidyverse)
library(data.table)
library(quantmod)
library(geofacet)
library(janitor)

library(tidyverse)

knitr::opts_chunk$set(warning = FALSE, message = FALSE, error = TRUE)

Data Creation and Cleaning

• Do the FOIA request. In a week or so, they send the expenditure and revenue data as excel files.
  

• Checks whether there are any new agencies, re-used funds etc. Create a list of funds, agencies, fund names, etc. for the new year and compare it to the immediate prior year to identify new funds.
  

• Update the funds_ab_in file which shows the use of funds. Use criteria to determine if the new funds should be in or out of the all-funds frame.
  

• Change the variable names to be consistent with other files such as AGENCYNAME –> agency_name

• Once variable names are shared over all years of data, combine past years with newest year. All revenue files are in a revenue folder that I reference when I set the working directory. When adding new fiscal years, put the the newest year of data for revenue and expenditures in their respective folders.

Normally, when your receive the new fiscal year files from the Comptrollers office, you will need to change the variable names so that they are consistent with past years. This is an example of reading in the new file and changing the variable names. They seem to change almost every year in the file received from the FOIA so if the code breaks here, check to make sure that the columns you are trying to rename exist and are spelled correctly! Once variables are the same, you will want to save the file as a csv file in its Revenue/Expenditure file and bind all past years and the current year together in one dataframe.

# 
# # Preliminary data from August 
# rev_fy23 <- read_xlsx("Fis_Fut_Rev_2023.xlsx") %>% 
#   rename(fy = 'FISCAL YEAR',
#          fund = 'FUND',
#          fund_name = 'FUND NAME',
#          agency = 'AGENCY',
#          agency_name = 'AGENCY NAME',
#          source = 'REVENUE SOURCE',
#          source_name = 'REV SRC NAME',
#          receipts = 'AMOUNT'
#   )  %>% mutate(data_source = "rev IOC 07.22.2023")
# 
# exp_fy23 <- read_xlsx("Fis_Fut_Exp_2023.xlsx") %>% 
#   rename(fy = 'FISCAL YEAR',
#          fund = 'FUND',
#          fund_name = 'FUND NAME',
#          agency = 'AGENCY',
#          agency_name = 'AGENCY NAME',
#          appr_org = 'DIVISION',
#          org_name = 'DIVISION NAME',
#          obj_seq_type = 'APPROPRIATION',
#          wh_approp_name = 'APPROP NAME',
#          appn_net_xfer = 'NET OF TRANS AMT',
#          expenditure = 'EXPENDED YTD') %>%
#   mutate( data_source = "exp IOC 07.11.2023"
#   ) 




## New files had different variable names!! 
## Had to update code slightly ## 
rev_fy23 <- read_xlsx("Fis_Fut_Rev_2023_final.xlsx") %>% 
  rename(fy = 'FISCAL YEAR',
         fund = 'FUND',
         fund_name = 'FUND NAME',
         agency = 'AGENCY',
         agency_name = 'AGENCY NAME',
         source = 'REVENUE SOURCE',
         source_name = 'REVENUE SOURCE NAME',
         receipts = 'REVENUE AMOUNT'
  )  %>% mutate(data_source = "rev IOC 011.13.2023")

exp_fy23 <- read_xlsx("Fis_Fut_Exp_2023_final.xlsx") %>% 
  rename(fy = 'FISCAL YEAR',
         fund = 'FUND',
         fund_name = 'FUND NAME',
         agency = 'AGENCY',
         agency_name = 'AGENCY NAME',
         appr_org = 'ORGANIZTION', ## Yes, it was spelled wrong in the file we received... 
         org_name = 'ORGANIZATION NAME',
         obj_seq_type = 'APPROPRIATION',
         wh_approp_name = 'APPROPRIATION NAME',
         appn_net_xfer = 'APPROPRIATION NET OF TRANSFERS',
         expenditure = 'EXPENDITURES') %>%
  mutate( data_source = "exp IOC 011.14.2023"
  ) 

The code chunk below takes the .dta files for all fiscal years before FY 2022 and binds them together. Variable names were manually changed by past researchers so that they were consistent across years.

For FY 2023 and after, .dta files can be avoided entirely and .csv or excel files will be used. All files before this year had been saved and passed on as .dta files for Stata code before the transition to R in Fall 2022. For years after fy22: add line of code to bind csv files after binding the dta files together. Variable names must identical to merge files together.

# years after fy22: add line of code to bind csv files after binding the dta files.
setwd("C:/Users/aleaw/OneDrive/Documents/PhD Fall 2021 - Spring 2022/Merriman RA/Fiscal Futures IGPA/Fiscal Futures FY2023/revenue")


## combine dta files from past years
allrevfiles23 = list.files(path = "C:/Users/aleaw/OneDrive/Documents/PhD Fall 2021 - Spring 2022/Merriman RA/Fiscal Futures IGPA/Fiscal Futures FY2023/revenue",
                             #"C:/Users/aleaw/OneDrive/Documents/PhD Fall 2021 - Spring 2022/Merriman RA/Fiscal Futures IGPA/Fiscal Futures FY2022/revenue",  
                           pattern = ".dta") %>% 
  lapply(read_dta) %>% 
  bind_rows

# add in excel file for current year
allrevfiles23 <- allrevfiles23 %>% 
  select(fy, fund, fund_name, agency, agency_name, source, source_name, receipts, data_source) %>% 
  rbind(rev_fy23)


#Fy21: 62294 observations, 13 variables
#FY22: 65094 obs, 13 vars
#FY23: 57822 obs




#setwd("C:/Users/aleaw/OneDrive/Documents/PhD Fall 2021 - Spring 2022/Merriman RA/Fiscal Futures IGPA/Fiscal Futures FY2022/Replication-Files/expenditures")

setwd("C:/Users/aleaw/OneDrive/Documents/PhD Fall 2021 - Spring 2022/Merriman RA/Fiscal Futures IGPA/Fiscal Futures FY2023/expenditures")

allexpfiles23 = list.files(path = "C:/Users/aleaw/OneDrive/Documents/PhD Fall 2021 - Spring 2022/Merriman RA/Fiscal Futures IGPA/Fiscal Futures FY2023/expenditures",
                           # path = "C:/Users/aleaw/OneDrive/Documents/PhD Fall 2021 - Spring 2022/Merriman RA/Fiscal Futures IGPA/Fiscal Futures FY2022/Replication-Files/expenditures", 
                           pattern = ".dta") %>%  lapply(read_dta) %>% bind_rows

# add in excel file for current year
allexpfiles23 <- allexpfiles23 %>% 
  select(fy, fund, fund_name, agency, agency_name, appr_org, org_name, obj_seq_type, wh_approp_name, appn_net_xfer, expenditure, data_source) %>% 
  rbind(exp_fy23)

# fy21 213372 observations, 20 variables
# fy22 225587 obs, 21 vars.
# fy23 238089 obs

write_csv(allrevfiles23, "allrevfiles_Nov132023.csv")


write_csv(allexpfiles23, "allexpfiles_Nov132023.csv")

Code below reads in the csv files created in chunks above (allrevfiles.csv and allrexpfiles.csv). These files contain all years of data combined into one file BEFORE any recoding is done. Do not use this file for summing categories because it is just an inbetween step before recoding revenue and expenditure categories.

# combined in past chunks called create-rev-csv and create-exp-csv

allrevfiles23 <- read_csv("allrevfiles_Nov132023.csv") #combined but not recoded
allexpfiles23 <- read_csv("allexpfiles_Nov132023.csv") #combined but not recoded

Inspecting new FY 2023 data files

Data files for closed years have been obtained from IOC. The numbers of funds, agencies, organizations, and revenue sources below were found by using pivot tables in the codebook files in the FY2022 Box folder. This could also be done using R and grouping new files by fund, agency, source number, source names, etc..

Revenue File:
- 674 Fund Numbers
- 80 Agencies
- 1178 Revenue source number & name combos
- Check # of numbers vs number of names soon

Expenditure File:
- 708 Fund Numbers
- 107 Agencies
- 98 Organization Numbers
- 313 Organization names

Finding new agencies and funds

General steps:

1. Identify new and reused funds for newest fiscal year.
   
2. Recode funds to take into account different fund numbers/names over the years. See Recoding New and Reused Funds for code chunk that does this.
   
3. Update fund_ab_in_2022.xlsx with any changes from previous fiscal year.

New Agencies, Funds, and Organizations from Expenditure files:

#### From Expenditure Data #####

# agencies referenced in any year before 2020:
agencies_past <- allexpfiles23 %>% 
  filter(fy < 2023) %>% 
  mutate(agency == as.character(agency)) %>% 
  group_by(agency, agency_name) %>% unique() %>% 
  summarize(expenditure = sum(expenditure, na.rm = TRUE)) %>% 
  drop_na() %>% 
  arrange(agency)
# agencies_past # 146 agencies ever


# agencies in 2022 data:
agencies23 <- allexpfiles23  %>% 
  filter(fy == 2023) %>% 
  mutate(agency == as.character(agency)) %>% 
  group_by(agency, agency_name) %>% 
  summarize(expenditure = sum(expenditure, na.rm = TRUE))
# agencies23 # 107 agencies this year


# no new agencies in FY23
anti_join(agencies23, agencies_past, 
          by = c("agency", "agency_name")) %>% 
  arrange(agency)

funds_past <- allexpfiles23  %>% 
  filter(fy < 2023) %>% 
  mutate(fund == as.character(fund)) %>% 
  group_by(fund, fund_name) %>% 
  summarize(count = n(), Expenditure = sum(expenditure, na.rm = TRUE))  %>% 
  drop_na()

funds23 <- allexpfiles23  %>% 
  filter(fy == 2023) %>% 
  mutate(fund == as.character(fund)) %>% 
  group_by(fund, fund_name) %>%  
  summarize(count = n(), Expenditure = sum(expenditure, na.rm = TRUE)) %>% 
  unique()


# 26 funds were in FY23 data that were not in past data:
anti_join(funds23, funds_past, 
          by = c("fund", "fund_name"
                 )) %>% 
  arrange(fund)

# orgs_past in the past = 920 org groups before 2023
orgs_past <- allexpfiles23  %>% 
  filter(fy < 2023) %>% 
  mutate(appr_org == as.character(appr_org)) %>% 
  group_by(appr_org, org_name) %>% unique() %>% 
  summarize(Expenditure = sum(expenditure, na.rm = TRUE)) %>% 
  drop_na()


# orgs22 # 399 org groups this year
orgs23 <- allexpfiles23  %>% 
  filter(fy == 2023) %>% 
  mutate(appr_org = as.character(appr_org)) %>% 
  group_by(appr_org, org_name) %>% 
  summarize(Expenditure = sum(expenditure, na.rm = TRUE))


# 3 org number and org name combos are new for FY2022:
anti_join(orgs23, orgs_past,
          by = c("appr_org", "org_name")) %>% 
  arrange(appr_org)

26 funds identified from the expenditure file as having something changed compared to funds that existed in previous years. They are either new funds, reused funds, or have very minor typos or changes in their name. Some are also identified in the revenue section below:

New Revenue Funds, Sources, and New Agencies:

#### From Revenue Data ####



# agencies_past # 108 agencies ever
agencies_past <- allrevfiles23  %>% 
  filter(fy < 2023) %>% 
  mutate(agency == as.character(agency)) %>% 
  group_by(agency, agency_name) %>% 
  unique() %>% 
  summarize(Receipts = sum(receipts, na.rm = TRUE)) %>% 
  drop_na()


# agencies23 # 80 agencies this year
agencies23 <- allrevfiles23  %>% 
  filter(fy == 2023) %>% 
  mutate(agency == as.character(agency)) %>% 
  group_by(agency, agency_name) %>% 
  summarize(Receipts = sum(receipts, na.rm = TRUE))

# 2 new agencies in revenue data this year
anti_join(agencies23, agencies_past, by = c("agency", "agency_name")) %>% 
  arrange(agency)

funds_past <- allrevfiles23  %>% 
  filter(fy < 2023) %>% 
  mutate(fund == as.character(fund)) %>% 
  group_by(fund, fund_name) %>% 
  summarize(count = n(), Receipts = sum(receipts, na.rm = TRUE))  %>% 
  drop_na()

funds23 <- allrevfiles23  %>% 
  filter(fy == 2023) %>% 
  mutate(fund == as.character(fund)) %>% 
  group_by(fund, fund_name) %>%  
  summarize(count = n(), Receipts = sum(receipts, na.rm = TRUE)) %>% 
  unique() %>% 
  drop_na()

# 13 revenue funds were in FY23 revenue data that were not in past data 
# some could be small fund name changes
anti_join(funds23, funds_past, by = c("fund", "fund_name")) %>% 
  arrange(fund)

sources_past <- allrevfiles23  %>% 
  filter(fy < 2023) %>% 
  mutate(source == as.character(source)) %>% 
  group_by(source, source_name) %>% 
  summarize(count = n(), Receipts = sum(receipts, na.rm = TRUE))  %>% 
  drop_na()

sources23 <- allrevfiles23  %>% 
  filter(fy == 2023) %>% 
  mutate(source == as.character(source)) %>% 
  group_by(source, source_name) %>% 
  summarize(count = n(), Receipts = sum(receipts, na.rm = TRUE)) %>% 
  unique()


# 16 revenue sources were in FY22 data that were not in past data 
# some could be small source name changes:
anti_join(sources23, sources_past, by = c("source", "source_name")) %>% 
  arrange(source)

Sources 2737 through 2756 were not found in the IOC_source file so I added them to ioc_source_updated22_AWM.xlsx. They do NOT have a rev_type in the file so each source must be searched on the Comptrollers website. We could ask for a variable key from the comptroller for a list of all revenue sources and their revenue type in the future to make our lives easier. I had to look at the sources within each revenue type to find the ones missing and then manually update our ioc_source file. Most of these were considered type 39, “Licenses, Fees, & Registrations”. These were finally updated on January 20th 2023 after totals were created for the FY22 paper.

FY23: Sources 2751 to 2769. Only 2758,2761, 2769 has significant amount of money (Fed monies via IDVA, US DA, Opioid Settlement).

Add 16 sources to list of sources

Recoding New and Reused Funds

New funds will need to be manually added to the funds_ab_in excel file and determined if they should or should not be included in Fiscal Future calculations.

For funds that were reused once, a 9 replaces the 0 as the first digit. If reused twice, then the first two values are 10.

• Ex. 0350 –> 9350 because its use changed.
  
• Ex. 0367 becomes 10367 because its use has changed twice now. There was fund 0367 originally, then its use changed and it was recoded as 9367, and now it changed again so it is a 10367.
  
• Excel file also has alternative ways to name funds (e.g. 0397-A and 0397-B) and variables for the year that the fund stopped being used. These have not been updated consistently over the years but it is useful information when trying to find any coding mistakes from the past.

New or reused funds revenue file recoding:

# if first character is a 0, replace with a 9 if its purpose has changed

rev_1998_2023 <- allrevfiles23 %>%
      mutate(fund = ifelse(fy < 2002 & fund %in% c("0730", "0241", "0350", "0367", "0381", "0382", "0526", "0603", "0734", "0913", "0379"), str_replace(fund, "0","9"), fund)) %>%
  
  mutate(fund = ifelse (fy < 2008 & fund %in% c("0027", "0033", "0037", "0058", "0062", "0066", "0075", "0083", "0116", "0119", "0120", "0122", "0148", "0149", "0157", "0158", "0166", "0194", "0201", "0209", "0211", "0217", "0223", "0231", "0234", "0253", "0320", "0503", "0505", "0512", "0516", "0531", "0532", "0533", "0547", "0563", "0579", "0591", "0606", "0616", "0624", "0659", "0662", "0665", "0676", "0710", 
"0068", "0076", "0115", "0119", "0168", "0182", "0199", "0241", "0307", "0506", "0509", "0513"), str_replace(fund, "0","9"), fund)) %>%
  
  mutate(fund = ifelse(fy < 2016 & fund %in% c("0263", "0399", "0409"), str_replace(fund, "0","9"), fund)) %>%
  
  mutate(fund =  ifelse(fy < 2017 & fund == "0364", str_replace(fund, "0","9"), fund)) %>%
  
  mutate(fund =  ifelse(fy < 2018 & fund %in% c("0818", "0767", "0671", "0593", "0578"), str_replace(fund, "0","9"), fund)) %>%
  
  mutate(fund = ifelse(fy>1999 & fy < 2018 & fund == "0231", "10231", fund) ) %>%
  
  mutate(fund = ifelse(fy < 2019 & fund %in% c("0161", "0489", "0500", "0612", "0893", "0766"), str_replace(fund, "0","9"), fund)) %>%
  
  mutate(fund =  ifelse(fy < 2020 & fund %in% c("0254", "0304", "0324", "0610", "0887", "0908", "0939", "0968"), str_replace(fund, "0","9"), fund)) %>%
  
  mutate(fund =  ifelse(fy < 2021 & fund %in% c("0255", "0325", "0348", "0967", "0972"), str_replace(fund, "0","9"), fund) ) %>%
  
   #2022 changes
  mutate(fund = ifelse(fy < 2022 & fund %in% c("0110","0165","0351", "0392", "0393", "0422", "0544", "0628", "0634",  "0656", "0672", "0683", "0723", "0734", "0742", "0743"), str_replace(fund, "0","9"), as.character(fund))) %>%  # replaces first 0 it finds with a 9
  mutate(fund = ifelse(fy < 2022 & fund == "0367", "10367", as.character(fund)) # fund reused for 3rd time
  ) %>%
  
  
  # 2023 fund changes
  mutate(fund = ifelse(fy < 2023 & fund %in% c("0099","0210","0246", "0250", "0264", "0268", "0300", "0311", "0403", "0448","0645", "0727", "0729", "0791"), str_replace(fund,"0", "9"), as.character(fund))) %>%
  mutate(fund = ifelse(fy < 2023 & fund == "0734" , "10734", as.character(fund)),
         fund = ifelse(fy<2023 & fund == "0820", "10820", as.character(fund) ) # fund reused for 3rd time
  )

For 2022, new funds included The Essential Government Services fund, Electric Vehicle Rebate, Budget Stabilization fund, and more. New funds were added to the funds_ab_in.xlsx file and recoded funds were addressed in the code chunks below and updated in the same excel file.

For 2023:

• 5 New Funds: 0180, 0221, 0385, 0426, 0442

• Reused Funds: 0099, 0210, 0246, 0250, 0264, 0268, 0300, 0311, 0403, 0426, 0448, 0645, 0727, 0729, 0734, 0791, 0820

  • fund 0734 and fund 0820 were used for the 3rd time and is treated slightly different (coded to 10734 instead of 9734)

Funds_ab_in_2023.xlsx still needs the in_ff variable and fund_category variables filled in!

• 0448 was state projects but now is Medicaid Tech Assistance Center, is that still a type of state project or give it a new fund number? Probably depends on the ioc fund description. Did recode it for now as of Nov 17 2023.

Minor Name Changes (so no change to funds_ab_in file): 0156, 0099, and fund 0083 is just a slight name change from our records in the funds_ab_in.xlsx file (Gang Crime Witness Protection vs Violent Crime Witness Protection. Changed in funds_ab_in on 11.17.2023)

Expenditure recoding:

# if first character is a 0, replace with a 9

exp_1998_2023 <- allexpfiles23 %>%
      mutate(fund = ifelse(fy < 2002 & fund %in% c("0730", "0241", "0350", "0367", "0381", "0382", "0526", "0603", "0734", "0913", "0379"), str_replace(fund, "0","9"), fund)) %>%
  
  mutate(fund = ifelse(fy < 2008 & fund %in% c("0027", "0033", "0037", "0058", "0062", "0066", "0075", "0083", "0116", "0119", "0120", "0122", "0148", "0149", "0157", "0158", "0166", "0194", "0201", "0209", "0211", "0217", "0223", "0231", "0234", "0253", "0320", "0503", "0505", "0512", "0516", "0531", "0532", "0533", "0547", "0563", "0579", "0591", "0606", "0616", "0624", "0659", "0662", "0665", "0676", "0710", 
"0068", "0076", "0115", "0119", "0168", "0182", "0199", "0241", "0307", "0506", "0509", "0513"), str_replace(fund, "0","9"), fund)) %>%
  
  mutate(fund = ifelse(fy < 2016 & fund %in% c("0263", "0399", "0409"), str_replace(fund, "0","9"), fund)) %>%
  
  mutate(fund =  ifelse(fy < 2017 & fund == "0364", str_replace(fund, "0","9"), fund)) %>%
  
  mutate(fund =  ifelse(fy < 2018 & fund %in% c("0818", "0767", "0671", "0593", "0578"), str_replace(fund, "0","9"), fund)) %>%
  mutate(fund = ifelse(fy>1999 & fy < 2018 & fund == "0231", "10231", fund) ) %>%
  
  mutate(fund = ifelse(fy < 2019 & fund %in% c("0161", "0489", "0500", "0612", "0893", "0766"), str_replace(fund, "0","9"), fund)) %>%
  
  mutate(fund =  ifelse(fy < 2020 & fund %in% c("0254", "0304", "0324", "0610", "0887", "0908", "0939", "0968"), str_replace(fund, "0","9"), fund)) %>%
  
  mutate(fund =  ifelse(fy < 2021 & fund %in% c("0255", "0325", "0348", "0967", "0972"), str_replace(fund, "0","9"), fund))  %>%
  
  #2022 changes
  mutate(fund = ifelse(fy < 2022 & fund %in% c("0110","0165","0351", "0392", "0393", "0422", "0544", "0628", "0634",  "0656", "0672", "0683","0723", "0742", "0743"), str_replace(fund, "0","9"), as.character(fund))) %>%  # replaces first 0 it finds with a 9
  mutate(fund = ifelse(fy < 2022 & fund == "0367", "10367", as.character(fund))  ) %>%  # fund reused for 3rd time 
  
 # 2023 fund changes
  mutate(fund = ifelse(fy < 2023 & fund %in% c("0099","0210","0246", "0250", "0264", "0268", "0300", "0311", "0403", "0448","0645", "0727", "0729", "0791"), str_replace(fund,"0", "9"), as.character(fund))  ) %>%
  mutate(fund = ifelse(fy < 2023 & fund == "0734" , "10734", as.character(fund)),
         fund = ifelse(fy<2023 & fund == "0820", "10820", as.character(fund) ) # fund reused for 3rd time
  )

The funds_ab_in.xlsx file contains all funds that have existed since 1998, if they still exist, indicates if fund numbers have been reused for varying purposes, and is updated yearly with new fund numbers used by the IOC.

# new chunk 2/23/2023 AWM to create file with all years that have the fund names and fund cat variables filled in. Currently some years have NAs. Only doing this for the Github folder so people have a clean file with as little missing data as possible. 
# before funds are recoded!
library(fuzzyjoin)

funds_ab_in_2023 = readxl::read_excel("C:/Users/aleaw/OneDrive/Documents/PhD Fall 2021 - Spring 2022/Merriman RA/Fiscal Futures IGPA/Fiscal Futures FY2023/funds_ab_in_2023.xlsx") 

funds_ab_in_2023 <- funds_ab_in_2023 %>% casefold(upper=TRUE)

#exp_temp <- allexpfiles22 %>% 
  
#  janitor::clean_names() %>%
 # select(-c(fund_cat, fund_cat_name )) %>%
  #arrange(fund, fy) %>%
#  filter(expenditure != 0) %>%             # keeps everything that is not zero
# join  funds_ab_in_2021  to exp_temp

#exp_temp <- regex_left_join(allexpfiles23, funds_ab_in_2023, by = "fund", ignore_case = TRUE)  # matches most recent fund number

#table(exp_temp$fund_cat_name)

funds_ab_in_2023 = readxl::read_excel("C:/Users/aleaw/OneDrive/Documents/PhD Fall 2021 - Spring 2022/Merriman RA/Fiscal Futures IGPA/Fiscal Futures FY2023/funds_ab_in_2023.xlsx")


exp_temp <- exp_1998_2023 %>% 
  arrange(fund, fy) %>%
  filter(expenditure != 0) %>%             # keeps everything that is not zero
# join  funds_ab_in_2021  to exp_temp
 left_join(funds_ab_in_2023, by = "fund") %>% # matches most recent fund number 
  select(-c(fund_ab, fund_ioc, fund_re, fund_name_ab, a_end, fund_category))


# Agency == 799 for Statutory transfers 
#  Object == 1993 is for Interfund cash transfers  
exp_temp <- exp_temp %>% 
  mutate(transfer = ifelse(org_name == "TRANSFERS", 1, 0),
         trans_agency = ifelse(org_name == "TRANSFERS", str_sub(obj_seq_type,1,3), NA),
         trans_type = ifelse(org_name == "TRANSFERS", str_sub(obj_seq_type,4,9), NA)
         ) %>%   
  
  # these come from ioc_source file after merging
  mutate(
         object = ifelse(transfer == 0, as.character(str_sub(obj_seq_type, 1, 4)), NA_character_),
         sequence = ifelse(transfer == 0, as.character(str_sub(obj_seq_type, 5,6)), NA_character_),
         type = ifelse(transfer == 0, str_sub(obj_seq_type, 7,8), NA_character_)
         ) 
       #  fund_cat = FIND_COLUMN, #create fund_cat column
      #   fund_cat_name = FIND_NAME) # create fund_cat_name column

• the initial combined years of data are saved as dataframes named exp_1998_2022 and rev_1998_2022. These are then saved as exp_temp and rev_temp while recoding variables. This is BEFORE category groups are created and cleaned below. exp_temp and rev_temp are only temporary files; do not use for aggregating totals yet!

Update Agencies: Some agencies have merged with others or changed names over time.

# recodes old agency numbers to consistent agency number
exp_temp <- exp_temp %>% 
  mutate(agency = case_when(
    (agency=="438"| agency=="475" |agency == "505") ~ "440",
    # financial institution &  professional regulation &
     # banks and real estate  --> coded as  financial and professional reg
    agency == "473" ~ "588", # nuclear safety moved into IEMA
    (agency =="531" | agency =="577") ~ "532", # coded as EPA
    (agency =="556" | agency == "538") ~ "406", # coded as agriculture
    agency == "560" ~ "592", # IL finance authority (fire trucks and agriculture stuff)to state fire marshal
    agency == "570" & fund == "0011" ~ "494",   # city of Chicago road fund to transportation
    TRUE ~ (as.character(agency)))) 

For aggregating revenue, use the rev_1998_2022 dataframe, join the funds_ab_in_2022 file to it, and then join the ioc_source_type file to the dataset. Remember: You need to update the funds_ab_in and ioc_source_type file every year!

# fund info to revenue for all years
rev_temp <- inner_join(rev_1998_2023, funds_ab_in_2023, by = "fund") %>% arrange(source)

# need to update the ioc_source_type file every year! 
ioc_source_type <- readxl::read_xlsx("C:/Users/aleaw/OneDrive/Documents/PhD Fall 2021 - Spring 2022/Merriman RA/Fiscal Futures IGPA/Fiscal Futures FY2023/ioc_source_updated23_AWM.xlsx")

rev_temp <- left_join(rev_temp, ioc_source_type, by = "source")
# automatically used source, source name does not match for the join to work using source_name



# recodes old agency numbers to consistent agency number
rev_temp <- rev_temp %>% 
  mutate(agency = case_when(
    (agency=="438"| agency=="475" |agency == "505") ~ "440",
    # financial institution &  professional regulation &
     # banks and real estate  --> coded as  financial and professional reg
    agency == "473" ~ "588", # nuclear safety moved into IEMA
    (agency =="531" | agency =="577") ~ "532", # coded as EPA
    (agency =="556" | agency == "538") ~ "406", # coded as agriculture
    agency == "560" ~ "592", # IL finance authority (fire trucks and agriculture stuff)to state fire marshal
    agency == "570" & fund == "0011" ~ "494",   # city of Chicago road fund to transportation
    TRUE ~ (as.character(agency)))) 

Overall Fiscal Health & Complex Fiscal Topics

Click the different tabs to see each topic, how it was coded, and exploratory graphs!

Pension Discusion

For yearly expenditure calculations, the state contributions to the pension funds (object = 4431) should be the expenditure included for pensions. If trying to look at the bigger fiscal health picture and include unfunded liabilities and in/out flows, then items like purchase of investments and POB spikes in trends that occurred from policy changes should be analyzed and discussed in a separate section. Again, State contributions TO the pension funds are the expenditures BUT an additional graph/discussion on the employer contributions, employee contributions, and benefits paid out should be included and considered for additional context on Illinois’ situation.

Pension expenditures referenced in the analysis are based on state payments to the following pension systems:

• Teachers Retirement System (TRS)
- New POB bond in 2019: Accelerated Bond Fund paid benefits in advance as lump sum

• State Employee Retirement System (SERS)
• State University Retirement System (SURS)
• Judges Retirement System (JRS)
• General Assembly Retirement System (GARS)

Additional context:
For the $10 billion in 2004, they borrowed money and invested it in pension portfolio and hoped that the returns would be greater than the interest on the debt. If returns>interest, then they increased the pension funds and it was a good idea. Otherwise a short term band-aid causes even more problems later. This added a significant amount to the unfunded pension liabilities. In 2010 and 2011, POBs served as a type of general borrowing for the state by borrowing against what was owed to the pension systems and using that revenue that should have funded pensions to instead subsidize the cost of providing core services. Illinois borrowed money (POBs) and used it to pay for government services. A temporary way to fill a budget gap for that 2010 that then costs more in the long run due to increased unfunded liabilities and interest on the borrowed money. - “Basket Case” by Dye 2015

In 2019 lawmakers offered a pension buyout plan where members could opt-out of their future benefits for a lump sum. However, few people participated in the buyout plan and very little savings have occurred so far. The buyout plan has been extended to 2026 in hopes that more people participate in it. Description of Pension Obligation Acceleration Bond at this link. Proceeds of bonds go into pension obligation acceleration fund (which are not included as a revenue source) and the fund is only used to make accelerated pension benefit payments. The pension stabilization fund (0319) is money put into the pension funds to help pay for unfunded liabilities from past poor budgeting decisions.

Data coding details

• State pension contributions are largely captured with object=4431. (These are the State expenditures included in analysis)

  • includes 8 billion payment in 2004 that creates large peak in expenditure graphs
    
  • Object 4431 does not capture recent pension stabilization fund which is fund = 0319, object = 1900 and has $300 million investment in FY2022.

• Fund=0475 is the Municipal Retirement Fund - Not included because state just helps collect and disperse local pension funds. IMRF is most funded pension fund in Illinois. Fund ends in 2015. All were considered purchase of investments.

• IOC objects 1160-1165 are for all retirement expenditures for employers. These are not included in the analysis.

• Some expenditures with object=4430 (benefits paid to retirees) were paid for with Pension obligation bond funds (fund == 0825).

• In past years, some POB funded expenditures were moved to revenue side. Code logic was unclear. We are no longer doing this as of FY2021.

• Other types of pension expenditures to consider when looking at pension funds: Pension obligation acceleration bond, state pension obligation bond reimbursements, pension pickup, accelerated pension buy-out (bond financed funds)

Other items to be aware of that may contain useful pension context:

• object = 1298 is for Purchase of Investments and is excluded from analysis. In past analyses, there were a couple of exceptions during 2010 and 2011.
  • Purchase of Investments captures the pension obligation bonds issued in 2010-2011.
    
• object = 1900 for pension stabilization is under lump sums
  
• object = 1167 and 1168 is for Employer pension contributions but is not used by IOC yet as of FY2022.
  
• object 4900 is awards and grants lump sum

Employer contributions for pensions are excluded from analysis to avoid double counting the cost of pensions. Expenditures with object 4430 for pensions, benefits, and annuities appears in items from funds 0473, 0477, 0479, 0481, (TRS, JRS, SERS, GARS), 0755, 0786, 0787, 0788, 0789, 0799 (deferred compensation plan, GAR excess benefit, JRS excess benefit, SER excess benefit, TRS excess benefit, state university retirement system) are NOT included in the analysis. All are coded with in_ff=0 in the fund_ab_in.xlsx file of funds.

Most of these funds were found by either using CTRL-F with pension related words or scrowllin through code options on the comptroller’s website.

# check what is being included in pensions

# funds related to pension contributions
pension_funds <- c("0472", "0473", "0477", "0479", "0481", "0755", "0786", "0787", "0788", "0789", "0799")

pension_check <- exp_temp %>% 
  mutate(pension = case_when( 
 # object == "4430" & fund == "0825" ~ "Object 4430 - Pension Buyout/Benefits Paid Early",
    object=="4430" ~ "Object 4430 - Benefits Paid to Employees; EXCLUDED", # pensions, annuities, benefits
    object=="4431" ~ "Object 4431 - State Contributions; INCLUDED", # 4431 = state payments into pension fund
        (obj_seq_type > "11590000" & obj_seq_type < "11660000")  ~ "Object 1160-1165 Employer Contributions to Pension Fund; EXCLUDED",
    # objects 1159 to 1166 are all considered Retirement by Comptroller 
    
            TRUE ~ "0")) %>%  # All other observations coded as 0 for non-pension items
  
  # recodes specific instances of code anomalies from past years:
  mutate(pension = case_when(
    (object=="1298" & fund %in% pension_funds ) ~ "Object 1298 - Purchase of Investments; DROPPED", 

    
      # pension stabilization fund in 2022 
 # object == "1900" & fund == "0319" ~ "Fund 0319-Pension Stabilization", 
    object == "1900" & fund %in% pension_funds ~ "Fund 0319 - Pension Stabilization", 

  
  object == "4900" & fund %in% pension_funds ~ "Object 4900 - Awards/Grants; Weird 2010-2011 values",
  
    TRUE ~ as.character(pension)) ) %>% 
  filter(pension != "0" )

pension_check %>% group_by(fy, pension) %>% 
  summarize(expenditure = sum(expenditure, na.rm = TRUE)) %>%
  ggplot(aes(x=fy, y = expenditure, color = pension)) + 
  geom_line() + 
  theme_classic()+
  labs (title = "Pension Fund Payments In and Retirement Benefits Out", 
  caption = "Object 4430 is retirement benefits paid to employees. 
  Object 4431 includes state payments INTO pension Fund.
  Object 1998 is excluded except for years 2010 and 2011 due to POBs.")+
    theme(legend.position = "bottom")+
  guides(color = guide_legend(nrow=3))

pension_check %>% group_by(fy, object) %>% 
  summarize(expenditure = sum(expenditure, na.rm = TRUE)) %>%
  ggplot(aes(x=fy, y = expenditure, color = object)) + 
  geom_line() + 
    theme_classic()+
  labs (title = "Expenditures by Object")

pension_check %>% group_by(fy, type) %>% 
  summarize(expenditure = sum(expenditure, na.rm = TRUE)) %>%
  ggplot(aes(x=fy, y = expenditure, color = type)) + 
  geom_line() + 
    theme_classic()+
  labs (title = "Expenditures by Type", caption = "Not confident with what Type represents. 
        $10 billion POB issued in 2003-2004 and again in 2010-2011.")

pension_check3 <- exp_temp %>% 
  mutate(pension = case_when( 
       (object=="4430"  ) ~ 1, # 4430 = pension benefits paid to retired employees
            TRUE ~ 0)) %>% 
  filter(pension > 0 )

pension_check3 %>% group_by(fy) %>% 
  summarize(expenditure = sum(expenditure, na.rm = TRUE)) %>%
  ggplot(aes(x=fy, y = expenditure)) + 
  geom_line() + 
    theme_classic()+
  labs (title = "Pension Benefits Paid to Employees")

## taking care of Pension Obligation Bond proceeds

pension_picture <- exp_temp %>% 
  mutate(pension = case_when( 
  #object == "4430" & fund == "0825" ~ "Pension Buyout/Benefits Paid Early; INCLUDED",
    (object=="4430") ~ "Benefits Paid to Employees", # pensions, annuities, benefits
    (object=="4431") ~ "State Pension Contributions", # 4431 = state payments into pension fund
        (obj_seq_type > "11590000" & obj_seq_type < "11660000")  ~ "IOC Retirement Expense Objectw",
    # objects 1159 to 1166 are all considered Retirement by Comptroller 
    
            TRUE ~ "0")) %>%  # All other observations coded as 0 for non-pension items
  
  # recodes specific instances of code anomalies from past years:
#  mutate(pension = case_when( (object=="1298" & fund %in% pension_funds ) ~ "Purchase of Investments", 

    
      # pension stabilization fund in 2022 
 # object == "1900" & fund == "0319" ~ "Fund 0319-Pension Stabilization", 
   # object == "1900" & fund %in% pension_funds ~ "Pension Stabilization Fund", 
  
    #TRUE ~ as.character(pension)) ) %>% 
  filter(pension != "0" )

pension_picture %>% group_by(fy, pension) %>% 
  summarize(expenditure = sum(expenditure, na.rm = TRUE)) %>%
  ggplot(aes(x=fy, y = expenditure, color = pension)) + 
  geom_line() + 
  theme_classic()+
  labs (title = "Pension Fund Payments In and Retirement Benefits Out", 
  caption = "All pension expenditure types are included in IOC Expenditure data")+
    theme(legend.position = "bottom", legend.title = element_blank())#+ guides(color = guide_legend(nrow=2))

Employer contributions and state pension contributions are an example of an expenditure being double counted if both are included.

Pension Contributions - Revenue Data

Pension contributions from employees and employers are not included as revenue sources but are useful for understanding the money going into the funds and the money flowing out of the funds. Identifying and graphing employee and employer contributions, as well as benefits paid to retired employees and state contributions was important for checking the items that should and should not be included in the analysis.

# rev_type = 51 is for retirement/pension contributions from both employers and employees.

# current year employee revenue source = 0573, contributions by employee == 572 (stops at 2011)
retirement_contributions <- rev_temp %>% 
  filter(rev_type == "51") %>% group_by(fy) %>% summarize(contributions = sum(receipts))

employer_contributions <- rev_temp %>% 
  filter(rev_type == "51" & source == "0577") %>% group_by(fy) %>% summarize(contributions = sum(receipts))

employee_contributions <- rev_temp %>% 
  filter(rev_type == "51" & (source == "0572" | source == "0573") ) %>% 
  group_by(fy) %>% summarize(contributions = sum(receipts))

benefits_paid <- pension_check %>% filter(object == "4430") %>%
  group_by(fy) %>% 
  summarize(expenditure = sum(expenditure, na.rm = TRUE))

state_contrib <- pension_check %>% filter(object == "4431") %>%
  group_by(fy) %>% 
  summarize(expenditure = sum(expenditure, na.rm = TRUE))

rev_temp %>% 
  filter(rev_type == "51") %>% # all retirement contributions
  group_by(fy, source) %>% 
  summarise(sum = sum(receipts, na.rm = TRUE)) %>%
  ggplot() +
  theme_classic()+
  geom_line(aes(x=fy, y = sum, color=source)) + 
  labs(title="All Retirement Contributions, ALL rev_source == 51", 
       caption = "Source 0573, 0572 is for employee contributions. 0577 is Contributions by employer.")


pension_picture %>% group_by(fy, pension) %>% 
  summarize(expenditure = sum(expenditure, na.rm = TRUE)) %>%
  ggplot(aes(x=fy, y = expenditure, color = pension)) + 
  geom_line() + 
    theme_classic()+
  geom_line(data=employee_contributions, aes(x=fy, y=contributions), color="green") +
    geom_line(data=employer_contributions, aes(x=fy, y=contributions), color="orange") +
  labs (title = "Pension Fund Payments In and Retirement Benefits Out", 
  caption = "Neon green - employee contributions INTO the fund. 
  Orange - employer contributions INTO the fund.")+
    theme(legend.position = "bottom", legend.title = element_blank())

Debt Service Discussion

Debt Service expenditures include interest payment on both short-term and long-term debt. We do not include escrow or principal payments.

Decision from Sept 30 2022: We are no longer including short term principal payments as a cost; only interest on borrowing is a cost. Pre FY22 and the FY21 correction, we did include an escrow payment and principle payments as costs but not bond proceeds as revenues. This caused expenditures to be inflated because we were essentially counting debt twice - the principle payment and whatever the money was spent on in other expenditure categories, which was incorrect.

Objects:
8813 interest INCLUDE AS COST
8811 is for principle EXCLUDE from aggregate debt expenditure
8841 is for escrow payments EXCLUDE from aggregate debt expenditure
8800 is for capital projects debt service (e.g. Build Illinois Bonds, Civic Center, Tollway EXCLUDE tollway in debt cost - Note: debt principle and interest are both included in capital projects because they are combined in the data observations; bond proceeds are not considered a revenue source. Can’t include capital projects interest as easily as the GO bonds.

Obj_seq_type:
- Exclude: Bond principle payments: obj_seq_type == 88110008
- Exclude: Short term borrowing principle: obj_seq_type == 88110108
- Include: General Obligation Bond Interest: obj_seq_type == 88130000 & 88130008
- Include: Interest on short-term borrowing: 88130108
- Exclude: Escrow payment == 88410008
- Include: Build IL Bonds, capital projects principal AND interest
- Tollway is obj_seq_type == 88000055, filter out fund == 0455 to remove tollway
- fund == 0455 is the IL State Toll Highway fund, items mostly for operations and maintenance

Tollway Debt, Revenue, and Expenditures

tollway <- exp_temp %>% filter(fund == "0455") #all tollway expenditures
capitalproject_debtservice <- exp_temp %>%filter(object == "8800") # ALL Capital projects debt service

# look at Illinois tollway bond proceeds and debt service: 
# rev_temp %>% filter(fund == "0455") # examine items in fund 0455
#exp_temp %>% filter(fund == "0455") %>% group_by(fy) %>% summarize(sum = sum(expenditure)) %>% arrange(-fy)
tollway

#rev_temp %>% filter(fund == "0455") %>% group_by(fy) %>% summarize(sum = sum(receipts)) %>% arrange(-fy)


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


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


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

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

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

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


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

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

# contributions and benefits paid comparison
ggplot()+
    theme_classic()+
  geom_line(data=tollway_bondproc, aes(x=fy, y=sum, color='Bond Proceeds')) +
  geom_line(data= tollwaydebt, aes(x=fy, y = sum, color = 'Debt Service'))+ 
  geom_line(data= tollway_exp, aes(x=fy, y = expenditure, color = 'Tollway Expenditures'))+ 
  geom_line(data= alltollway, aes(x=fy, y = sum, color = "Tollway Revenue"))+ 
  scale_color_manual(values = c(
    'Bond Proceeds' = 'darkblue',
    'Debt Service' = 'red',
    'Tollway Expenditures' = 'orange',
    'Tollway Revenue' = 'light green')) +
  labs(title="Tollway bond procreeds, debt service, revenue, and expenditures.", 
       caption = "Tollway revenue + bond proceeds should be roughly equal to tollway expenditures + debt service.", 
       y = "Dollars")

State Principal and Interest

Filtering for interest on short term borrowing and GO bonds (88130008, 88130000, and 88130108) and GO bond principal amounts (88110008).

• object == 8813 is for all debt service interest but obj_seq_type is used to specify short term borrowing versus regular debt service.

• an Interest to Principal ratio is also calculated in the table below.

Looking only at general obligation principal payments and interest payments:

# GO bond principal and GO bond interest
GObond_debt <- exp_temp %>% 
  filter(obj_seq_type == "88110008" |obj_seq_type == "88130000" | obj_seq_type == "88130008") %>% 
  group_by(fy, obj_seq_type) %>% 
  summarize(sum = sum(expenditure, na.rm=TRUE)) %>% 
  pivot_wider(names_from = obj_seq_type, values_from = sum) %>% 
  mutate(principal = `88110008`,
         interest = sum(`88130008`+`88130000`, na.rm = TRUE),
         ratio = (as.numeric(interest)/as.numeric(principal)))

GObond_debt %>% select(principal, interest, ratio) %>%
  mutate(across(principal:interest, ~format(., big.mark= ",", scientific = F)))

# GObond_debt %>% ggplot() + 
#   geom_line(aes(x=fy, y=principal, color = "Principal"))+ 
#   geom_line(aes(x=fy, y=interest, color = "Interest")) + 
#   labs(title = "General Obligation principal and interest payments")

GObond_debt %>% ggplot() + 
    theme_classic()+
  geom_col(aes(x=fy, y=interest/1000000, fill = "Interest")) + 
  geom_col(aes(x=fy, y=principal/1000000, fill = "Principal"))+ 
  labs(title = "Debt Service", subtitle = "General Obligation Principal and Interest Payments")

Looking only at short term borrowing principal and interest payments:

# short term borrowing, first observation is in 2004?
short_debt <- exp_temp %>% 
  filter(obj_seq_type == 88110108 |obj_seq_type == 88130108) %>% 
  group_by(fy, obj_seq_type) %>% 
  summarize(sum = sum(expenditure, na.rm=TRUE)) %>% 
  pivot_wider(names_from = obj_seq_type, values_from = sum) %>% 
  mutate(principal = `88110108`,
         interest = `88130108`,
         ratio = (as.numeric(interest)/as.numeric(principal)))

short_debt %>% select(principal, interest, ratio) %>%
  mutate(across(principal:interest, ~format(., big.mark= ",",  scientific = F)))

short_debt %>% ggplot() +   theme_classic()+
  geom_col(aes(x=fy, y=principal/1000000, fill = "Principal"))+ 
  geom_col(aes(x=fy, y=interest/1000000, fill = "Interest")) + 
  labs(title = "Debt Service", subtitle = "Short Term Borrowing: Principal and Interest Payments")

When including short term borrowing and normal debt service, the debt ratio seems more normal and the total interest and principal payments over the years are smoothed out.

Principal and interest amounts calculated exclude the Illinois Tollway debt service and debt for capital projects. Capital projects debt service is examined below:

capitalprojects <- exp_temp %>% filter(object == "8800")

all_debt <- exp_temp %>% 
  filter(fund != "0455" & (object == "8811" |object == "8813" | object == "8800") )%>% 
  group_by(fy, object) %>% 
  summarize(sum = sum(expenditure, na.rm=TRUE)) %>% 
  pivot_wider(names_from = object, values_from = sum) %>% 
  mutate(principal = `8811`,
         interest = `8813`,
         CapitalProjects = `8800`,
         ratio = (as.numeric(interest)/as.numeric(principal)))

all_debt %>% select(principal, interest, CapitalProjects, ratio) %>%
  mutate(across(principal:CapitalProjects, ~format(., big.mark= ",", scientific = F)))

all_debt %>% ggplot() + 
    theme_classic()+
  geom_line(aes(x=fy, y=principal/1000000, color = "Principal"))+ 
  geom_line(aes(x=fy, y=interest/1000000, color = "Interest"))+
  geom_line(aes(x=fy, y = CapitalProjects / 1000000, color = "Capital Projects"))+
  labs(y = "Debt ($Millions)",
       title = "Principal and Interest payments", subtitle = "Principal and interest from short term borrowing and GO Bonds debt service", caption = "Capital projects does not include Illinois tollway debt service.
       Capital projects include interest and principal values as one value and cannot be sepearated.")

all_debt %>% ggplot() +   theme_classic()+
  geom_line(aes(x=fy, y=principal/1000000, color = "Principal"))+ 
  geom_line(aes(x=fy, y=interest/1000000, color = "Interest"))+
  geom_line(aes(x=fy, y = CapitalProjects / 1000000, color = "Capital Projects Debt Service"))+
  geom_line(data = tollwaydebt, aes( x=fy, y=sum/1000000, color = "Tollway Debt Service"))+
  labs(y = "Debt ($Millions)", title = "Short term borrowing and GO Bonds",
       subtitle = "Principal and Interest payments", caption = "Capital projects does not include Illinois tollway debt service.") 

all_debt %>% ggplot() +   theme_classic()+
  geom_line(aes(x=fy, y=(principal+interest)/1000000, color = "Principal & Interest"))+ 
  #geom_line(aes(x=fy, y=interest/1000000, color = "Interest"))+
  geom_line(aes(x=fy, y = CapitalProjects / 1000000, color = "Capital Projects Debt Service"))+
  geom_line(data = tollwaydebt, aes( x=fy, y=sum/1000000, color = "Tollway Debt Service"))+
  labs(y = "Debt ($Millions)", title = "Illinois Debt Service Expenditures: Short term borrowing and GO Bonds",
       subtitle = "Principal and Interest payments", caption = "Capital projects does not include Illinois tollway debt service.") 

Capital projects include the IL Civic Center and Build Illinois Bonds. Tollway principal and interest has been dropped from the State’s Debt Service expenditure but is counted in the Illinois Tollway Expenditure cost.

State Employee Healthcare Discussion

State Employee Health Care = Sum of expenditures for “health care coverage as elected by members per state employees group insurance act.” The payments are made from the Health Insurance Reserve Fund. Employee contributions are not considered a revenue source or an expenditure in our analysis.

Funding for the State Employees Group Insurance plan originates from two funds. The Health Insurance Reserve Fund (HIRF) and the Group Insurance Premium Fund (GIPF). Contributions and payment for Health coverage benefits are deposited INTO HIRF and contributions for life insurance are deposited into the GIPF.

HIRF is the fund mainly used to administer the group insurance program. Funding for HIRF comes from several different revenue sources, the General Revenue Fund (GRF), Road Fund, reimbursements, university funds, and misc funds. CGFA Report

Coding details
In FY2013, the Local Government Health Insurance fund was transferred to the department of Central Management Services (agency changes from 478 to 416 in data.)

Employer group insurance contributions for health insurance are excluded to avoid double counting the cost of healthcare provision. All employer group insurance contributions are coded as object = 1180. BUT the last two fiscal years were coded as 1900 instead of 1180 for lump sums instead of employer contributions
- anything to do with pandemic money categorization?

Fund = 0907 = health insurance reserve, in_ff = 1

Fund = 0457 is “Group insurance premium”, in_ff = 1

Fund = 0193 is “Local govt health insurance reserve”, in=ff = 0
fund = 0477 is “Community College Health Insurance”, in=ff = 0.
- had large amount in early years

Fund = 9939 is “group self-insurers’ insolv”, in_ff = 1
Fund = 0940 is Self-Insurers security, in_ff = 0
Fund = 0739 is Group Workers Comp Pool Insol, in_ff = 1

• eehc = 0 means it is NOT a state healthcare cost but it is an employer contribution of some type to some fund
  
• eehc = 1 means it is a state employee healthcare cost and it is an employer contribution to health insurance

health_ins_reserve <- exp_temp %>% filter(fund == "0907") %>% group_by(fy) %>% 
    summarize(fund_0907 = sum(expenditure)) 

health_ins_reserve %>% 
  ggplot(aes(x=fy, y=fund_0907)) + theme_classic()+geom_col() + labs(title="Health Insurance Reserve", subtitle = "Sum of expenditures from fund 907")

# object 1180 is inconsistently coded over time form the IOC 
# object 1180 should be employer contributions to healthcare group insurance
employer_contributions <- exp_temp %>% filter(object == "1180") %>% group_by(fy) %>% summarize(object1180 = sum(expenditure)) 

employer_contributions%>% 
  ggplot(aes(x=fy, y=object1180)) + geom_col() +theme_classic() + labs(title="Employer Contributions to Healthcare Group Insurance, IOC Object 1180")


employer_contributions2 <- exp_temp %>% filter(object == "1180" & fund=="0001") %>% group_by(fy) %>% summarize(object1180 = sum(expenditure)) 

employer_contributions2 %>% 
  ggplot(aes(x=fy, y=object1180)) + theme_classic()+geom_col() + labs(title="Employer Contributions to Healthcare Group Insurance", subtitle = "IOC Object 1180 from Fund 001")


# examine combined group insurance totals per year
group_ins2 <- exp_temp %>% 
  mutate(eehc = ifelse(
    # group insurance contributions for 1998-2005 and 2013-present
    # CMS took over health insurance in 2013
   fund == "0001" & (object == "1180" | object =="1900") & agency == "416" & appr_org=="20", 1, 0) )%>% 
  mutate(eehc = ifelse(
    # group insurance contributions for 2006-2012
    # health insurance was in healthcare and family services, agency 478 for a few years
    fund == "0001" & object == "1180" & agency == "478" & appr_org=="80", 1, eehc) )%>%
  filter(eehc == 1) %>% 
    group_by(fy) %>% 
    summarize(dropped_group_premiums = sum(expenditure, na.rm=TRUE))

group_ins2 %>% ggplot(aes(x=fy, y=dropped_group_premiums)) + geom_line() + theme_classic() + labs(title="Employer Healthcare Group Insurance Contributions", subtitle= " - Dropped from analysis to avoid double counting healthcare expenditures", caption = "Objects 1180 and 1900 from fund 0001. See code for additional coding details.")

If observation is a group insurance contribution, then the expenditure amount is set to $0 (essentially dropped from analysis).

Health Insurance Premiums - Revenue Side

Employee insurance premiums for healthcare are a revenue source for the state in the IOC data but are NOT included in the Fiscal Futures analysis and fiscal gap calculations.

Source #’s:
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)

health_insurance_fund_rev<- rev_temp %>% 
  filter(fund=="0907") %>% 
    group_by(fy) %>%
  summarize(health_ins_rev = sum(receipts)) 

health_insurance_fund_rev %>% 
  ggplot(aes(x=fy, y = health_ins_rev)) + 
 theme_classic()+ 
  geom_col() +
  labs( title = "Health insurance fund - All revenue, Fund 0907")



#collect optional insurance premiums to fund 0907 for use in eehc expenditure  
employee_health_premiums <- rev_temp %>% 
  mutate(employee_premiums = ifelse(
    fund=="0907" & (source=="0120"| source=="0121"| (source>"0345" & source<"0357")|(source>"2199" & source<"2209")), 1, 0)) %>%
  filter(employee_premiums == 1)

# optional insurance premiums = employee insurance premiums
emp_premium <- employee_health_premiums %>%
  group_by(fy) %>%
  summarize(employee_premiums_sum = sum(receipts))
  
emp_premium %>% ggplot(aes(x=fy, y = employee_premiums_sum)) + 
theme_classic()+geom_col()  + labs( title = "Employee health insurance premiums")

# contributions and benefits paid comparison
ggplot()+
  #  geom_line(data=group_ins, aes(x=fy, y=object1180, color='Group Insurance1')) +
      geom_line(data=health_insurance_fund_rev, aes(x=fy, y=health_ins_rev, color='Health Insurance Fund - All Revenue')) +
 geom_line(data = emp_premium, aes(x=fy, y = employee_premiums_sum, color = 'Revenue from Employee Premiums')) +
    geom_line(data=health_ins_reserve, aes(x=fy, y=fund_0907, color='Cost of Provision')) +
    geom_line(data=employer_contributions, aes(x=fy, y=object1180, color='Group Insurance-Object1180')) +
 #   geom_line(data=employer_contributions2, aes(x=fy, y=object1180, color='Employer Contributions-General Fund')) +

  geom_line(data=group_ins2, aes(x=fy, y=dropped_group_premiums, color='Group Insurance - 1180 & 1900')) +
  #geom_line(data= healthcare_costs, aes(x=fy, y = cost_of_provision, color = 'Healthcare Costs'))+ 
  scale_color_manual(values = c(
    'Cost of Provision' = 'darkblue',
    'Health Insurance Fund - All Revenue' = 'light green',
    'Revenue from Employee Premiums' = 'dark green',
    'Group Insurance - 1180 & 1900' = 'blue',
    'Group Insurance-Object1180' = 'light blue'
   #     'Employer Contributions-General Fund' = 'light blue'
)) +
  theme_classic()+
  labs(title="Healthcare costs and group insurance contributions", 
       caption = "Healthcare costs and group insurance contributions", 
       y = "Dollars", x = "")

exp_temp <- exp_temp %>% 
  mutate(eehc = ifelse(object == "1180", 1, 0)) %>%
  mutate(eehc = ifelse((eehc == 1 & in_ff =="0"), 2, eehc))
#%>%mutate(in_ff = ifelse(eehc == 2, "1", in_ff) ) %>% filter(eehc ==2 )
table(exp_temp$eehc)

## 
##      0      1      2 
## 156483   4525    152

Federal Medicaid Reimbursements and Medicaid Costs

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 = "red") + 
  geom_line(data=med_reimburse, aes(x=fy, y = sum), color="black") +
  theme_classic() + labs(title = "Medicaid reimbursements and Medicaid expenditures", caption = "Medicaid expenditures include funds provided to medical providers. ")

Expenditure & Revenue Categorization

Click the Expenditure or Revenue Tabs to see how categories were combined and coded to make the summary tables.

Modify Expenditure File

Tax refunds

Aggregate expenditures: Save tax refunds as negative revenue. Code refunds to match the rev_type codes (02=income taxes, 03 = corporate income taxes, 06=sales tax, 09=motor fuel tax, 24=insurance taxes and fees, 35 = all other tax refunds).

ref_check has small value for government machines!

# table(exp_temp$sequence)
# class(exp_temp$sequence)
# class(exp_temp$object)
# class(exp_temp$obj_seq_type)

## negative revenue becomes tax refunds

tax_refund_long <- exp_temp %>%           # fund != "0401" # removes State Trust Funds
  filter(fund != "0401" & (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(
                    fund == "0278" & sequence == "00" ~ "02",  # for income tax refund
                    fund=="0278" & sequence == "01" ~ "03", # tax administration and enforcement and tax operations become corporate income tax refund
                    fund == "0278" & sequence == "02" ~ "02",
                    object=="9921" ~ "21",                # inheritance tax and estate tax refund appropriation
                    object=="9923" ~ "09",                # motor fuel tax refunds
                    obj_seq_type == "99250055" ~ "06",    # sales tax refund
                    fund=="0378" & object=="9925" ~ "24", # insurance privilege tax refund
                    fund=="0001" & object=="9925" ~ "35", # all other taxes
                  # TRUE ~ "CHECK"
                  TRUE ~ NA
   #  .default = as.character(x)
   ))                       # if none of the items above apply to the observations, then code them as CHECK 

    
exp_temp <- left_join(exp_temp, tax_refund_long) %>%
  mutate(refund = ifelse(is.na(refund),"not refund", as.character(refund)))

tax_refund <- tax_refund_long %>% 
  group_by(refund, fy)%>%
  summarize(refund_amount = sum(expenditure, na.rm = TRUE)/1000000) %>%
  pivot_wider(names_from = refund, values_from = refund_amount, names_prefix = "ref_") %>%
  mutate_all(~replace_na(.,0)) %>%
  arrange(fy)

tax_refund %>% pivot_longer( 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", caption = "Refunds are excluded from Expenditure totals and instead subtracted from Revenue totals") + 
  labs(title = "Tax refunds", 
       caption = "Rev_type codes: 02=income taxes, 03=corporate income taxes, 06=sales tax, 09=motor fuel tax, 
       24=insurance taxes and fees, 35 = all other tax refunds." )

# remove the items we recoded in tax_refund_long
exp_temp <- exp_temp %>% filter(refund == "not refund")

tax_refund amounts are removed from expenditure totals and subtracted from revenue totals (since they were tax refunds).

Pension Expenditures

exp_temp <-  exp_temp %>% 
  arrange(fund) %>%
  mutate(pension = case_when( 
   (object=="4431") ~ 1, # 4431 = easy to find pension payments INTO fund
   
 # (object>"1159" & object<"1166") & fund != "0183" & fund != "0193"   ~ 2, 
   # objects 1159 to 1166 are all considered Retirement by Comptroller, 
  # Excluded - employer contributions from agencies/organizations/etc.

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

table(exp_temp$pension) 

## 
##      0      1      3      4 
## 172885    234      6     10

exp_temp %>% filter(pension != 0) %>%
  mutate(pension = as.factor(pension))%>%
  group_by(fy, pension) %>% 
  summarize(expenditure = sum(expenditure, na.rm = TRUE)) %>%
  ggplot(aes(x=fy, y = expenditure, group=pension)) + 
  geom_line(aes(color = pension)) + 

  labs (title = "Pension expenditures", 
  caption = "1 = State contributions INTO pension funds")+
    theme(legend.position = "bottom")

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

table(exp_temp$pension) 

## 
##      0      1      4      6 
## 172885    232     10      8

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

exp_temp %>% 
 filter(pension > 0) %>%  
  mutate(pension = as.factor(pension)) %>%
  group_by(fy, pension) %>% 
  summarize(expenditure = sum(expenditure, na.rm=TRUE)) %>%
  ggplot(aes(x=fy, y=expenditure, color = pension)) +
  geom_line() + 
  labs (title = "Pension Expenditures", 
  caption = "")

exp_temp %>% 
 filter(pension > 0) %>%  
  group_by(fy) %>% 
  summarize(expenditure = sum(expenditure, na.rm=TRUE)) %>%
  ggplot(aes(x=fy, y=expenditure)) +
  geom_line() + 
  labs (title = "Pension Expenditures")

Drop Interfund transfers

• object == 1993 is for interfund cash transfers
  
• agency == 799 is for statutory transfers
  
• object == 1298 is for purchase of investments and is not spending EXCEPT for costs in 2010 and 2011 (and were recoded already to object == “4431”). Over 168,000 observations remain.

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

exp_temp <- anti_join(exp_temp, transfers_drop)
exp_temp

transfers_drop %>% filter(fy>2019 & object == 1993) %>% group_by(obj_seq_type) %>% summarize(sum = sum(expenditure)) %>% arrange(-sum)

State employee healthcare costs

State Employee Health Care = Sum of expenditures for “health care coverage as elected by members per state employees group insurance act.” The payments are made from the Health Insurance Reserve Fund. We subtract the share that came from employee contributions. Employee contributions are not considered a revenue source or an expenditure in our analysis.

In FY2013, the Local Government Health Insurance fund was transferred to the department of Central Management Services (agency changes from 478 to 416 in data.)

Employer group insurance contributions for health insurance are excluded to avoid double counting the cost of healthcare. All employer group insurance contributions are coded as object = 1180. BUT the last two fiscal years were coded as 1900 instead of 1180 for lump sums instead of employer contributions

• anything to do with pandemic federal money categorization? Was a lump sum or transfer used to pay the group contribution and coded differently?

• This group insurance expenditure coded as 1900 instead of 1180 caused one of the major problems when calculating the fiscal gap for FY21. Stata code originally failed to drop group insurance expenditure in FY21 (since object was = 1900 instead of 1180) and resulted in expenditures being around $2 billion higher than they should have been for Central Management expenditures. This was noticed by GOMB (Alexis Sturm) and resulted in looking deeper into what items are going in and out of health care funds (See State Employee Healthcare Discussion).

Relevant funds:
Fund = 0457 is “Group insurance premium”, in_ff = 1
Fund = 0193 is “Local govt health insurance reserve”, in=ff = 0
fund = 0477 is “Community College Health Insurance”, in=ff = 0.
- had large amount in early years
Fund = 0907 = health insurance reserve, in_ff = 1
Fund = 9939 is “group self-insurers’ insolv”, in_ff = 1
Fund = 0940 is Self-Insurers security, in_ff = 0
Fund = 0739 is Group Workers Comp Pool Insol, in_ff = 1

• eehc = 0 means it is NOT a state healthcare cost but it is an employer contribution of some type to some fund
  
• eehc = 1 means it is a state employee healthcare cost and it is an employer contribution to health insurance

Of the many different ways I have coded the healthcare costs, most do not have expenditures during 2010, 2011, 2016, 2017.

If observation is a group insurance contribution, then the expenditure amount is set to $0 (essentially dropped from analysis).

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

healthcare_costs

exp_temp %>% filter(group == "904") %>% group_by(fy) %>% 
  summarise(healthcare_cost = sum(expenditure, na.rm = TRUE)) %>% 
  ggplot() +geom_line(aes(x=fy, y=healthcare_cost)) + labs(title="State Employee Healthcare Costs - Included in Fiscal Futures Model", caption = "Fund 0907 for agencies responsible for health insurance reserve (DHFS & CMS)")

#exp_temp <- anti_join(exp_temp, healthcare_costs) %>% mutate(expenditure = ifelse(object == "1180", 0, expenditure))

#healthcare_costs_yearly <- healthcare_costs %>% group_by(fy, group) %>% summarise(healthcare_cost = sum(expenditure, na.rm = TRUE)/1000000) %>% select(-group)

This code chunk above for dealing with group insurance means that healthcare costs need to be added to expenditures after other group names are assigned. Then employee contributions/insurance premiums from the revenue side need to be subtracted from the total cost of employee healthcare for the net cost. Do not do this.

Local Transfers

Separate transfers to local from parent agencies that come from DOR(492) or Transportation (494). Treats muni revenue transfers as expenditures, not negative revenue.

The share of certain taxes levied state-wide at a common rate and then transferred to local governments. (Purely local-option taxes levied by specific local governments with the state acting as collection agent are NOT included.)

The six corresponding revenue items are:

• Local share of Personal Income Tax - Individual Income Tax Pass-Through New 2021 (source 2582). • Local share of General Sales Tax
• Personal Property Replacement Tax on Business Income
• Personal Property Replacement Tax on Public Utilities

• Local share of Motor Fuel Tax - Transportation Renewal Fund 0952

Until Dec 18. 2022, Local CURE was being aggregated into Revenue totals since the agency was the Department of Revenue. However the $371 million expenditure is for “LOC GOVT ARPA” and the revenue source that is Local CURE is also $371 million. Since it cancels out and is just passed through the state government, I am changing changing the fund_ab_in file so that in_ff=0 for the Local CURE fund. It also inflates the department of revenue expenditures in a misleading way when the expense is actually a transfer to local governments.

• Dropping Local CURE fund from analysis results in a $371 million decrease in the department of Revenue (where the Local Government ARPA transfer money). The appropriation for it was over $740 million so some will probably be rolled over to FY23 too.
  
• In the FY21 New and Reused Funds word document, 0325 Local CURE is described as “Created as a federal trust fund. The fund is established to receive transfers from either the disaster response and recovery fund or the state cure fund of federal funds received by the state. These transfers, subject to appropriation, will provide for the administration and payment of grants and expense reimbursements to units of local government. Revenues should be under Federal Other and expenditures under Commerce and Economic Opportunity.” - I propose changing it to exclude for both (in_ff=0).

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

transfers_long <- exp_temp %>% 
  filter((group == "971" |group == "972" | group == "975" | group == "976"))# | fund == "0325")

transfers_long %>% 
  group_by(agency_name, fy) %>% 
  summarize(expenditure = sum(expenditure, na.rm=TRUE) )%>% 
  ggplot() + geom_line(aes(x=fy, y = expenditure, color=agency_name)) + labs(title = "Transfers to Local Governments", caption = "Data Source: Illinois Office of the Comptroller")

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

The Local Transfers from the Personal Property Replacement Tax (fund 802) increased over $2 billion from corporate income taxes alone. Personal property replacement taxes (PPRT) are revenues collected by the state of Illinois and paid to local governments to replace money that was lost by local governments when their powers to impose personal property taxes on corporations, partnerships, and other business entities were taken away.

Debt Service

Debt Service expenditures include interest payment on both short-term and long-term debt. We do not include escrow or principal payments.

Decision from Sept 30 2022: We are no longer including short term principal payments as a cost; only interest on borrowing is a cost. Pre FY22 and the FY21 correction, we did include an escrow payment and principle payments as costs but not bond proceeds as revenues. This caused expenditures to be inflated because we were essentially counting debt twice - the principle payment and whatever the money was spent on in other expenditure categories, which was incorrect.

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)

Medicaid

Medicaid. That portion of the Healthcare and Family Services (or Public Aid in earlier years, agency code 478) budget for Medical (appr_organization code 65) for awards and grants (object codes 4400 and 4900).

Jan 2015 FF Documentation - “Typically, presentations of the Illinois budget—for example, the Illinois Office of the Comptroller’s Fiscal Focus reports—use”medical assistance payments” made by the Department of Healthcare and Family Services (DHFS) as a proxy for Medicaid expenditures but these numbers include both Medicaid and non-Medicaid expenditures such as state-funded medical programs (e.g. AllKids). Medicaid expenditures are not limited to medical assistance in DHFS - the program also covers clients of home and community-based care programs that are administered by the Department of Human Services and Department of Aging. Because Medicaid is an entitlement, it is a reimbursement for medical care for qualifying individuals. Medical bills may be paid from designated funds by any of several agencies that have spending authority from those funds, as they provide health care services to individuals who qualify for Medicaid or another form of assistance. So, the numbers for medical assistance payments from DHFS may both over-state and underestimate the costs (and growth rate) of the actual Medicaid program. This is a legitimate concern, because the expenditure numbers for Illinois’ medical assistance payments do not correspond to the numbers disseminated by national Medicaid data sources”

• 2015 Documentation notes that federal cash receipts for Medicaid are used to estimate Medicaid expenditures from the General Revenue Fund (GRF) by dividing the cash receipts by the match rate. Medicaid expenditures from specially-designated funds are assumed to be the warrants issued from those funds. Then the two components—GRF and special funds—are added together for a total number. (Note that the number for FF Medicaid expenditures includes the state portion and the federal portion combined. Past code uses sequence variable also to identify CHIP and GRF (sequence = 20, 54, 61, 62, or 65).

• Department of Healthcare and Family Services administers medical assistance programs (e.g. Medicaid, CHIP, All Kids). The Fiscal Futures model moves Medicaid and CHIP expenditures to their own expenditure category. State funded programs remain in DHFS.

Side note: State CURE healthcare provider relief will remain in the Medicaid expenditure category due to the nature of it being federal funds providing public health services and funding to locations that provide public services.

• Uses same appropriation name of “HEALTHCARE PROVIDER RELIEF” and fund == 0793 and obj_seq_type == 49000000. So can defend including healthcare provider relief as Medicaid expenditure.

Federal Medical Assistance Program (FMAP): in 1965. The FMAP formula compares the state per-capita income to the national per-capita income. There is no cap on the dollar amount that the federal government pays, so the more that a state spends the more that it receives. a maximum of 83%. States with a higher per-capita income receive lower FMAP funding but no less than 50%, and the states that have a lower per-capita income receive higher FMAP funding. Those that need more, get more.

Add Other Fiscal Future group codes

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
      pension>0  ~ "901", # pensions
      (agency>"309" & agency<"400") ~ "930",    # elected officers
      
      agency == "586" ~ "959", # create new K-12 group

      agency=="402" | agency=="418" | agency=="478" | agency=="444" | agency=="482" ~ as.character(agency), # aging, Children and Family Services (DCFS), Healthcare and Family Services (DHFS), human services (DHS), public health (DPH)
      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=="406" | agency=="420" | agency=="494" |  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=="695" | agency == "684" |agency == "691" | (agency>"599" & agency<"677") ~ "960", # higher education
    
    agency=="427"  ~ as.character(agency), # employment security
    
    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", 
    
    # non-pension expenditures of retirement funds moved to "Other Departments"
    # should have removed pension expenditures already from exp_temp in Pensions step above
    agency=="131" | agency=="275" | agency=="589" |agency=="593"|agency=="594"|agency=="693" ~ "948",
    
    T ~ as.character(group))) %>%

  mutate(group_name = 
           case_when(
             group == "416" ~ "Central Management",
             group == "478" ~ "Healthcare and Family Services",
             group == "482" ~ "Public Health",
             group == "900" ~ "NOT IN FRAME",
             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(group),
             TRUE ~ "Check name"),
         year = fy)

exp_temp %>% filter(group_name == "Check name")

#write_csv(exp_temp, "all_expenditures_recoded.csv")

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 almost all questions we have about the data.

Note that these are the raw figures BEFORE we take the additional steps:

• Subtract tax refunds from tax revenues by revenue type.

exp_temp %>% filter(fy>2020 & fund == "0561") %>% group_by(wh_approp_name, fy) %>% summarize(sum=sum(expenditure)) %>% arrange(-sum)

SBE Federal Department of Education is fund 0561. Fund 0579 is the State Board of Education.

Transportation, Tollway, & Capital Improvement

cap_improve<- exp_temp %>% filter(group=="946") %>% group_by(group_name, fy) %>% arrange(-fy) # Capital Improvement
exp_temp %>% filter(group=="942") %>% group_by(group_name, fy) %>% arrange(-fy) # econ development & infra, doesn't exist
transpo <- exp_temp %>% filter(group=="494") %>% group_by(group_name, fy) %>% arrange(-fy) # transportation

transpo %>% group_by(wh_approp_name, fy) %>% summarize(sum=sum(expenditure)) %>% arrange(-fy, -sum) %>% pivot_wider(names_from = fy, values_from = sum)

p1 <- transpo %>% group_by(fy) %>% summarize(sum=sum(expenditure)) %>% ggplot()+geom_line(aes(x=fy, y=sum, color = "Transportation")) +scale_y_continuous(limits = c(0,6000000000)) +
  geom_line(data= tollway_exp, aes(x=fy, y = expenditure, color = 'Tollway Expenditures')) #+geom_line(data=cap_improve, aes(x=fy, y=expenditure, color = "Capital Improvement"))
p1
p2<- cap_improve %>% group_by(fy) %>% summarize(sum=sum(expenditure)) %>% ggplot()+geom_line(aes(x=fy, y=sum)) + scale_y_continuous(limits = c(0,6000000000)) 
p2
gridExtra::grid.arrange(p1,p2, nrow =1) 

Transportation is made up of the road fund, capital admininistrative costs, maintenance.

Tollway expenditures include maintenance and operation, principle and interest payments.

Capital improvement has a lot of projects that use bond financed funds for schools, sports facilities, etc. Agencies 511, 554, 574, and 598 are coded as group 946, capital improvement (Capital Development Board, Sports Facilities Development Authority, Metro Pier and Exposition Authority, and Upper River Development Authority which is no longer used).

Some items that could be considered capital improvement, like the Connect Illinois Broadband project, are included in the Economic Development spending category.

Modify Revenue data

Revenue Categories NOT included in Fiscal Futures:
- 32. Garnishment-Levies. (State is fiduciary, not beneficiary.)
- 45. Student Fees-Universities. (Excluded from state-level budget.)
- 51. Retirement Contributions (of individuals and non-state entities).
- 66. Proceeds, Investment Maturities. (Not sustainable flow.)
- 72. Bond Issue Proceeds. (Not sustainable flow.)
- 75. Inter-Agency Receipts.
- 79. Cook County Intergovernmental Transfers. (State is not beneficiary.)
- 98. Prior Year Refunds.
- 99. Statutory Transfers.

All Other Sources

Expanded to include the following smaller sources:
- 30. Horse Racing Taxes & Fees.
- 60. Other Grants and Contracts.
- 63. Investment Income.

For aggregating revenue, use the rev_1998_2022 dataframe, join the funds_ab_in_2022 file to it, and then join the ioc_source_type file to the dataset. Remember: You need to update the funds_ab_in and ioc_source_type file every year!

# fund info to revenue for all years
rev_temp <- inner_join(rev_1998_2023, funds_ab_in_2023, by = "fund") %>% arrange(source)

# need to update the ioc_source_type file every year! 
ioc_source_type <- readxl::read_xlsx("C:/Users/aleaw/OneDrive/Documents/PhD Fall 2021 - Spring 2022/Merriman RA/Fiscal Futures IGPA/Fiscal Futures FY2023/ioc_source_updated23_AWM.xlsx")

rev_temp <- left_join(rev_temp, ioc_source_type, by = "source")
# automatically used source, source name does not match for the join to work using source_name



# recodes old agency numbers to consistent agency number
rev_temp <- rev_temp %>% 
  mutate(agency = case_when(
    (agency=="438"| agency=="475" |agency == "505") ~ "440",
    # financial institution &  professional regulation &
     # banks and real estate  --> coded as  financial and professional reg
    agency == "473" ~ "588", # nuclear safety moved into IEMA
    (agency =="531" | agency =="577") ~ "532", # coded as EPA
    (agency =="556" | agency == "538") ~ "406", # coded as agriculture
    agency == "560" ~ "592", # IL finance authority (fire trucks and agriculture stuff)to state fire marshal
    agency == "570" & fund == "0011" ~ "494",   # city of Chicago road fund to transportation
    TRUE ~ (as.character(agency)))) 

Federal Revenue

The Fiscal Futures Model divides federal funds (IOC revenue type = 57) into Medicaid, Transportation, and All Other Federal Funds. These can include: Health and Human Services Grants, Federal Stimulus Package, Department of Education Grants, Department of Transportation Grants, Department of Agriculture Grants, TANF Grants, and Department of Labor Grants.

Federal Medicaid: DHFS receives money for Medicaid that is deposited into the General Revenue Fund. There are also Special State Funds used for Medicaid that receive specific revenues (e.g. Healthcare Provider Taxes) which are matched with Federal Funds. The federal receipts in these special funds are aggregated and added to the federal receipts in the GRF that are received by DHFS.

Sources:

• 618 = Health and Human Services (not used)
  
• 660 = HHS/Hospital Participation
  
• 676 = Medical Assistance
  
• 692 = Medical Assistance
  
• 1552 = DHHS/ FFP-Medicaid Rehab Option
• 2306 = Enhanced Fed Fin PART-ARRA
• 2076 = IDPH-HHS/CMS
  
• 2364 = Department of Insurance (not used)

Other potential medicaid sources from CTRL-Fing “med” in revenue sources:
Sources: 2104 = Medicare Part D & 675 = Medical Administration

• Revenue Source=1530 is labeled Medicaid Matching in IOC Sources list but it isn’t used? 2140 is Matching Grant Monies but is also not used?

The Department of Healthcare and Family Services (DHFS) receives federal monies for Medicaid that are deposited into the General Revenue Fund. In addition, a number of special state funds(approximately 20) are used for Medicaid. These funds receive specific revenues –e.g., Healthcare Provider Taxes—which are then matched with federal monies at approximately 50 percent. There are differences in the proportion of federal vs. state monies in the various funds, but the key is that there is a significant federal component to the receipts in the funds. The federal receipts in these special funds are aggregated and added to the federal receipts in the GRF that are received by DHFS.

Transportation: If Agency is 494 and considered Federal Revenue, then it is recoded to its own category of “Federal Transportation”.

#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)) +
      theme_bw() +
  scale_y_continuous(labels = comma)+
  labs(title = "Federal to State Transfers", 
       y = "Millions of Dollars", x = "") + 
  theme(legend.position = "bottom", legend.title = element_blank()  )

Looking at Total Federal Revenue over the years:

fedrev<- rev_temp %>% 
  filter(rev_type == "58" | rev_type == "59" | rev_type == "57") 

fedrev %>% 
  group_by(fy) %>% 
  summarise(receipts = sum(receipts, na.rm = TRUE)/1000000) %>% 
  ggplot() +
  geom_line(aes(x=fy, y=receipts)) +
      theme_bw() +
  scale_y_continuous(labels = comma)+
  labs(title = "All Federal Revenue", 
       y = "Millions of Dollars", x = "") + 
  theme(legend.position = "bottom", legend.title = element_blank()  )+
    scale_y_continuous(limits = c(0,45000))

fedrev %>% 
  filter(source_name_AWM != "FEDERAL STIMULUS PACKAGE") %>%
  group_by(fy) %>% 
  summarise(receipts = sum(receipts, na.rm = TRUE)/1000000) %>% 
  ggplot() +
  geom_line(aes(x=fy, y=receipts)) +
      theme_bw() +
  scale_y_continuous(labels = comma)+
  labs(title = "All Federal EXCEPT Federal Stimulus Package", 
       y = "Millions of Dollars", x = "",
       caption = "Note: Dropping Federal Stimulus Package revenue only removes the $3.5 billion from FY20, $0.3 billion from FY21, and 
       $8.5 billion from FY22. There is still over $12 billion more in Federal Revenue compared to FY19.") + 
  theme(legend.position = "bottom", legend.title = element_blank()  ) +
  scale_y_continuous(limits = c(0,45000))

Dropping Federal Stimulus Package from the revenue sources removes 3.5 billion in revenue from FY20 and nearly 8.5 billion from FY22. It also changes the revenue following the Great Recession in FY2009.

All revenue sources within “Federal - Other” source.

rev_temp %>% filter(rev_type == "57" & fy >2018) %>% group_by(fund_name, source_name_AWM,  fy) %>% summarize(receipts =sum(receipts)) %>% arrange(-receipts) %>% pivot_wider(names_from = fy, values_from = receipts)

# fed_rev_compare <- rev_temp %>% filter((rev_type == "57" | rev_type == "58" | rev_type == "59") & (fy == 2022 | fy==2021 | fy==2020 | fy == 2019)) %>%  arrange(-receipts)
# write_csv(fed_rev_compare, "comparefedrev.csv")


rev_temp %>% filter(source_name_AWM == "FEDERAL STIMULUS PACKAGE") %>% group_by(fy, fund_name) %>% summarize(receipts =sum(receipts)) %>% arrange(-fy)

rev_temp %>% filter(fy > 2018 & source_name_AWM == "FEDERAL STIMULUS PACKAGE") %>% group_by(fund_name, fy) %>% summarize(receipts =sum(receipts)) %>% arrange(-receipts)

rev_temp %>% filter(rev_type == "57" & fy > 2018 & fund_name == "SBE FEDERAL DEPT OF EDUCATION") %>% group_by(source_name_AWM , fund_name, fy) %>% summarize(receipts =sum(receipts)) %>% arrange(-receipts)

exp_temp %>% filter(fy >2019 & (fund_name == "STATE CURE" | fund_name == "LOCAL CURE" | fund_name == "SBE FEDERAL DEPT OF EDUCATION" | fund_name == "DISASTER RESPONSE AND RECOVERY" | fund_name == "ESSENTIAL GOVT SERV SUPPORT" )) %>% group_by(fy, agency_name, wh_approp_name, fund_name) %>% 
  summarize(sum=sum(expenditure),
            appropriated = sum(appn_net_xfer)) %>% 
  arrange(-appropriated)

exp_temp %>% filter(fy >2018 & (fund_name == "STATE CURE" | fund_name == "LOCAL CURE" | fund_name == "SBE FEDERAL DEPT OF EDUCATION" | fund_name == "DISASTER RESPONSE AND RECOVERY" | fund_name == "ESSENTIAL GOVT SERV SUPPORT" )) %>% group_by(fy, wh_approp_name, fund_name) %>% 
  summarize(sum=sum(expenditure),
            appropriated = sum(appn_net_xfer)) %>% 
  arrange(-appropriated)

exp_temp %>% filter(fy >2018 & (fund_name == "STATE CURE" | fund_name == "LOCAL CURE" | fund_name == "SBE FEDERAL DEPT OF EDUCATION" | fund_name == "DISASTER RESPONSE AND RECOVERY" | fund_name == "ESSENTIAL GOVT SERV SUPPORT" )) %>% group_by(fund_name, fy, agency_name) %>% 
  summarize(sum=sum(expenditure),
            appropriated = sum(appn_net_xfer)) %>% 
  arrange(-appropriated)

exp_temp %>% filter(fy == 2023 & (fund_name == "STATE CURE" | fund_name == "LOCAL CURE")) %>% group_by(org_name, agency_name, object, wh_approp_name, fund_name) %>% summarize(sum=sum(expenditure)) %>% arrange(-sum)

exp_temp %>% filter(fy == 2023 & (fund_name == "STATE CURE" | fund_name == "LOCAL CURE")) %>% group_by(agency_name, object, wh_approp_name, fund_name) %>% summarize(sum=sum(expenditure)) %>% arrange(-sum)

exp_temp %>% filter(fy == 2023 & (fund_name == "STATE CURE" | fund_name == "LOCAL CURE")) %>% group_by(fund_name, object, org_name) %>% summarize(sum=sum(expenditure)) %>% arrange(-sum)

exp_temp %>% filter(fy == 2023 & (fund_name == "STATE CURE" | fund_name == "LOCAL CURE")) %>% group_by(fund_name, agency_name) %>% summarize(sum=sum(expenditure)) %>% arrange(-sum)

exp_temp %>% filter(fy == 2023 & (fund_name == "STATE CURE" | fund_name == "LOCAL CURE")) %>% group_by(agency_name) %>% summarize(sum=sum(expenditure)) %>% arrange(-sum)

exp_temp %>% filter(fy == 2022 | fy == 2021 & (fund_name == "STATE CURE" | fund_name == "LOCAL CURE")) %>% group_by(wh_approp_name, fund_name) %>% summarize(sum=sum(expenditure)) %>% arrange(-sum)

Dropping State CURE Revenue

The Fiscal Futures model focuses on sustainable revenue sources. To understand our fiscal gap and outlook, we need to exclude these one time revenues. GOMB has emphasized that they have allocated COVID dollars to one time expenditures (unemployment trust fund, budget stabilization fund, etc.). The fiscal gap, graphs,and CAGRs have been recalculated in the Drop COVID Dollars section below. In addition, an attempt at forecasting revenue and expenditures is also made after dropping the federal COVID dollars.

NOTE: I have only dropped State and Local CURE revenue so far. Federal money went into other funds during the beginning of pandemic.

# only labels observations where the source was specifically named "federal stimulous package"
# does not code other COVID legislative funds that went under other names and had more specific purposes.
rev_temp <- rev_temp %>% mutate(covid_dollars = ifelse(source_name_AWM == "FEDERAL STIMULUS PACKAGE",1,0))

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)

#collect optional insurance premiums to fund 0907 for use in eehc expenditure  
rev_temp <- rev_temp %>% 
  mutate(
    #variable not used in aggregates, but could be interesting for other purposes
    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



# # optional insurance premiums = employee insurance premiums

# emp_premium <- rev_temp %>%
#   group_by(fy, employee_premiums) %>%
#   summarize(employee_premiums_sum = sum(receipts)/1000000) %>%
#   filter(employee_premiums == 1) %>%
#   rename(year = fy) %>% 
#   select(-employee_premiums)

emp_premium_long <- rev_temp %>%  filter(employee_premiums == 1)
# 381 observations have employee premiums == 1


# drops employee premiums from revenue
# rev_temp <- rev_temp %>% filter(employee_premiums != 1)
# should be dropped in next step since rev_type = 51

Employee premiums are dropped in the following steps. 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:

I don’t have much faith in the transfers in and out steps- AWM

I am currently choosing to exclude the totals from this step. Overall, this decreases the total revenues in “All Other Revenues” by a few million dollars.

• in_from_out <- c(“0847”, “0867”, “1175”, “1176”, “1177”, “1178”, “1181”, “1182”, “1582”, “1592”, “1745”, “1982”, “2174”, “2264”)
  
• See the methodology document for the list of what these sources/funds are.

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

Corporate income tax Individual Income Tax Pass-Through (source =2582) was over 2 billion. The PTE tax allows a workaround to the federal $10,000 limitation for state and local tax (SALT) deductions and expires Jan 1. 2026 (to correspond with remaining years that the Tax Cuts and Jobs Act SALT limitation is in effect) Tax Adviser. With the enactment of the Tax Cuts and Jobs Act of 2017 (“TCJA”), individual taxpayers were limited to a $10,000 state and local tax deduction per year. In response to this limitation, many states created a workaround mechanism, introducing a pass-through entity tax (“PTET”). This shifted the state and local tax deduction from an individual taxpayer to the entity level that is not subject to the $10,000 limitation. Implications: Illinois residents in multistate passthrough entities will need to pay estimated taxes on income that is not subject to the SALT cap tax. TCJA of 2017 decreased

# revenue types to drop
drop_type <- c("32", "45", "51", 
               "66", "72", "75", "79", "98")

# 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 
##   177   132   852   134   593   269    47  1421   473    79   665   130   141 
##    35    36    39    42    48    54    57    58    59    60    63    76    78 
##   691  5328  9487  2939    32  1283  6683   646   233   105  5321   159 11711 
##    99 
##  1115

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_") %>%
  mutate_if(is.numeric, round)

# combines smallest 4  categories to to "Other"
# they were the 4 smallest in past years, are they still the 4 smallest? 

rev_temp <- rev_temp %>%  
 mutate(rev_type_new = ifelse(rev_type=="30" | rev_type=="60" | rev_type=="63" | rev_type=="76", "78", rev_type_new))


#table(rev_temp$rev_type_new)  # check work



rm(rev_1998_2022)
rm(exp_1998_2022)


#write.csv(exp_temp, "exp_fy22_recoded_12192022.csv")
#write.csv(rev_temp, "rev_fy22_recoded_12192022.csv")

Pivoting and Merging Data for Summary Tables

• Local Government Transfers (exp_970) should be on the expenditure side

• State employer group insurance contributions should be dropped to avoid double counting both the state. Do not do this. This was done for FY21 only and will not be done again.
  

• Subtract employee insurance premiums from State Employee Healthcare Expenditures (group == 904) - Employee Premiums = Actual state healthcare costs.
  

• ff_exp\(exp904 − emp_premium\)employee_premiums_sum = statehealthcarecosts

  • Did in FY21, but not doing again. Minor difference in fiscal gap overall from change in methodology.

Revenues

I chose to drop rev_76 for Transfers in and Out because I do not understand why that step occurs in the previously used Stata code. Rev_76 was created and included in rev_78 for All Other Revenues in old Stata code for years before FY21 but that method has been discontinued for FY22.

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

ff_rev<- left_join(ff_rev, tax_refund)

#ff_rev <- left_join(ff_rev, pension2_fy22, by=c("fy" = "year"))

#ff_rev <- left_join(ff_rev, eehc2_amt) 
ff_rev <- mutate_all(ff_rev, ~replace_na(.,0))


ff_rev <- ff_rev %>%
  mutate(rev_02 = rev_02 - round(ref_02),
         rev_03 = rev_03 - round(ref_03),
         rev_06 = rev_06 - round(ref_06),
         rev_09 = rev_09 - round(ref_09),
         rev_21 = rev_21 - round(ref_21),
         rev_24 = rev_24 - round(ref_24),
         rev_35 = rev_35 - round(ref_35)

      #   rev_78new = rev_78 #+ pension_amt #+ eehc
         ) %>% 
  select(-c(ref_02:ref_35, rev_99, rev_NA, rev_76#, pension_amt , rev_76,
          #  , eehc
            ))

ff_rev 

Since I already pivot_wider()ed the table in the previous code chunk, I now change each column’s name by using rename() to set new variable names. Ideally the final dataframe would have both the variable name and the variable label but I have not done that yet.

aggregate_rev_labels <- ff_rev %>%
  rename("INDIVIDUAL INCOME TAXES, gross of local, net of refunds" = rev_02,
         "CORPORATE INCOME TAXES, gross of PPRT, net of refunds" = rev_03,
         "SALES TAXES, gross of local share" = rev_06 ,
         "MOTOR FUEL TAX, gross of local share, net of refunds" = rev_09 ,
         "PUBLIC UTILITY TAXES, gross of PPRT" = rev_12,
         "CIGARETTE TAXES" = rev_15 ,
         "LIQUOR GALLONAGE TAXES" = rev_18,
         "INHERITANCE TAX" = rev_21,
         "INSURANCE TAXES&FEES&LICENSES, net of refunds" = rev_24 ,
         "CORP FRANCHISE TAXES & FEES" = rev_27,
       # "HORSE RACING TAXES & FEES" = rev_30,  # in Other
         "MEDICAL PROVIDER ASSESSMENTS" = rev_31 ,
         # "GARNISHMENT-LEVIES " = rev_32 , # dropped
         "LOTTERY RECEIPTS" = rev_33 ,
         "OTHER TAXES" = rev_35,
         "RECEIPTS FROM REVENUE PRODUCNG" = rev_36, 
         "LICENSES, FEES & REGISTRATIONS" = rev_39 ,
         "MOTOR VEHICLE AND OPERATORS" = rev_42 ,
         #  "STUDENT FEES-UNIVERSITIES" = rev_45,   # dropped
         "RIVERBOAT WAGERING TAXES" = rev_48 ,
         # "RETIREMENT CONTRIBUTIONS " = rev_51, # dropped
         "GIFTS AND BEQUESTS" = rev_54, 
         "FEDERAL OTHER" = rev_57 ,
         "FEDERAL MEDICAID" = rev_58, 
         "FEDERAL TRANSPORTATION" = rev_59 ,
         #"OTHER GRANTS AND CONTRACTS" = rev_60, #other
       # "INVESTMENT INCOME" = rev_63, # other
         # "PROCEEDS,INVESTMENT MATURITIES" = rev_66 , #dropped
         # "BOND ISSUE PROCEEDS" = rev_72,  #dropped
         # "INTER-AGENCY RECEIPTS" = rev_75,  #dropped
      #  "TRANSFER IN FROM OUT FUNDS" = rev_76,  #other
         "ALL OTHER SOURCES" = rev_78,
         # "COOK COUNTY IGT" = rev_79, #dropped
         # "PRIOR YEAR REFUNDS" = rev_98 #dropped
  ) 

aggregate_rev_labels

Expenditures

Create exp_970 for all local government transfers (exp_971 + exp_972 + exp_975 + exp_976).

Create state employee healthcare costs that reflects the health costs minus the optional insurance premiums that came in (904_new=904−med_option_amt_recent). Do not do this. This was done for FY21 only and will not be done again. Small differences in overall Fiscal Gap from methodology change.

ff_exp <- exp_temp %>% 
  group_by(fy, group) %>% 
  summarize(sum_expenditures = round(sum(expenditure, na.rm=TRUE)/1000000 )) %>%
  pivot_wider(names_from = "group", values_from = "sum_expenditures", names_prefix = "exp_")%>%
  
    left_join(debt_keep_yearly) %>%
  mutate(exp_903 = debt_cost) %>%

  #  left_join(healthcare_costs_yearly) %>%

  # join state employee healthcare and subtract employee premiums
  # left_join(emp_premium, by = c("fy" = "year")) %>%
#  mutate(exp_904_new = (`healthcare_cost` - `employee_premiums_sum`)) %>% # state employee healthcare premiums
  
 # left_join(retirement_contributions) %>%
  #    mutate(exp_901_new = exp_901 - contributions/1000000) %>% #employee pension contributions


  # 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(debt_cost, exp_971:exp_976)) # drop unwanted columns
ff_exp

Clean Table Outputs

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.

Dropping ref_check for now!

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",  # 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
                 T ~ "Check Me!"

  ) )%>% 
  mutate(Category_name = str_to_title(Category_name)) %>% select(-ref_NA)


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 == "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 == "444" ~ "HUMAN SERVICES" ,
             Category == "448" ~ "Innovation and Technology", # AWM added fy2022
             Category == "478" ~ "FAMILY SERVICES net Medicaid", 
             Category == "482" ~ "PUBLIC HEALTH", 
             Category == "492" ~ "REVENUE", 
             Category == "494" ~ "TRANSPORTATION" ,
             Category == "532" ~ "ENVIRONMENTAL PROTECT AGENCY" ,
             Category == "557" ~ "IL STATE TOLL HIGHWAY AUTH" ,
             Category == "684" ~ "IL COMMUNITY COLLEGE BOARD", 
             Category == "691" ~ "IL STUDENT ASSISTANCE COMM" ,
             Category == "900" ~ "NOT IN FRAME",
             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))

write_csv(exp_long, "expenditures_recoded_long_FY23.csv")
write_csv(rev_long, "revenue_recoded_long_FY23.csv")

aggregated_totals_long <- rbind(rev_long, exp_long)


aggregated_totals_long %>% 
  mutate_if(is.numeric, round) %>%
  rename("Dollars (in Millions)"  = Dollars, 
         "Revenue Category" = Category_name)

year_totals <- aggregated_totals_long %>% 
  mutate(Year = as.character(Year)) %>%
  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` = round(Revenue - Expenditures))
# %>%  arrange(desc(Year))
# creates variable for the Gap each year

# 
# year_totals %>% mutate_if(is.numeric, function(x) format(round(x), big.mark = ",")) %>%
# kable(digits = 0, format = "markdown", caption = "Fiscal Gap for each Fiscal Year", align = "r", full_width = FALSE)
# 
# year_totals %>% mutate_if(is.numeric, function(x) format(round(x), big.mark = ",")) %>%
# kable(digits = 0, format = "markdown", caption = "Fiscal Gap for each Fiscal Year", align = "r")  %>%
#   kable_classic() %>% add_footnote(c("Fiscal Gap calculations may vary from past publications; see methodology changes.", "Values include Federal Stimulus Package Revenue"))

year_totals %>% mutate_if(is.numeric, function(x) format(round(x), big.mark = ",")) %>%  
  kbl(caption = "Fiscal Gap for each Fiscal Year", align = "r", full.width=FALSE) %>% 
kable_classic() %>%  
  add_footnote(c("Fiscal Gap calculations may vary from past publications; see methodology changes.", "Values include Federal Stimulus Package Revenue"))

Fiscal Gap for each Fiscal Year

Year	Expenditures	Revenue	Fiscal Gap
1998	31,219	31,264	45
1999	33,803	33,031	-772
2000	37,280	35,845	-1,435
2001	40,298	37,151	-3,147
2002	42,014	36,827	-5,187
2003	42,570	36,805	-5,765
2004	52,979	40,856	-12,123
2005	45,332	42,864	-2,468
2006	48,030	44,698	-3,332
2007	51,098	48,031	-3,067
2008	54,138	50,215	-3,923
2009	56,722	49,859	-6,863
2010	59,248	49,839	-9,409
2011	60,404	54,732	-5,672
2012	59,832	56,249	-3,583
2013	63,263	60,806	-2,457
2014	66,939	62,770	-4,169
2015	69,920	64,109	-5,811
2016	63,913	61,989	-1,924
2017	71,703	61,347	-10,356
2018	74,943	70,466	-4,477
2019	74,383	72,154	-2,229
2020	81,574	78,143	-3,431
2021	92,807	91,808	-999
2022	99,785	113,027	13,242
2023	110,575	108,085	-2,490
a Fiscal Gap calculations may vary from past publications; see methodology changes.
b Values include Federal Stimulus Package Revenue

Graphs

Graphs made from aggregated_totals_long dataframe.

annotation <- data.frame(
  x = c(2004, 2017, 2019),
  y = c(60, 50, 5),  
  label = c("Expenditures","Revenue", "Fiscal Gap")
)

# with trend lines:
# year_totals %>%  
#   ggplot() +
#   # geom_smooth adds regression line, graphed first so it appears behind line graph
#   geom_smooth(aes(x = Year, y = Revenue/1000), color = "rosybrown2", alpha = 0.7, method = "lm", se = FALSE) + 
#   geom_smooth(aes(x = Year, y = Expenditures/1000), color = "gray", method = "lm", se = FALSE) +
#   
#   # 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), color = "red", size=1) +
#   
#   # labels
#     theme_bw() +
#   scale_y_continuous(labels = comma)+
#   xlab("Year") + 
#   ylab("Billions of Dollars")  +
#   ggtitle("Illinois Expenditures and Revenue Totals, 1998-2022")




# # without trend lines:
# year_totals %>%  
#   ggplot() +
#   # line graph of revenue and expenditures
#   geom_line(aes(x = Year, y = Revenue), color = "Black", size=1) +
#   geom_line(aes(x = Year, y = Expenditures), color = "red", size=1) +
#       theme_bw() +
#   scale_y_continuous(labels = comma)+
#   xlab("Year") + 
#   ylab("Millions of Dollars")  +
#   ggtitle("Illinois Expenditures and Revenue Totals, 1998-2022")


## Dashed line versions for expenditures: 

fiscal_gap <- year_totals %>%  
  mutate(Year = as.numeric(Year)) %>%
  ggplot() +
  geom_hline(yintercept = 0) +
  # 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 = "gray") +
  
  geom_text(data = annotation, aes(x=x, y=y, label=label))+
  # labels
    theme_bw() +
    theme(legend.position = "none")+

    scale_linetype_manual(values = c("dashed", "dashed")) +

#  scale_y_continuous(labels = comma)+
  xlab("Year") + 
  ylab("Billions of Dollars")  +
  ggtitle("Illinois Expenditures and Revenue Totals, 1998-2023")

fiscal_gap

# annotation_billions <- data.frame(
#   x = c(2004, 2017, 2019),
#   y = c(60, 50, 5),  
#   label = c("Expenditures","Revenue", "Fiscal Gap"))


fiscal_gap2 <- year_totals %>%  
  mutate(Year = as.numeric(Year)) %>%
  ggplot() +
  geom_hline(yintercept = 0) +
  geom_line(aes(x = Year, y = Revenue/1000), color = "Black", lwd=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 = "gray") +
  
  geom_text(data = annotation, aes(x=x, y=y, label=label))+
    theme_bw() +
  theme(legend.position = "none")+
    scale_linetype_manual(values = c("dashed", "dashed")) +

  scale_y_continuous(labels = comma)+
  xlab("Year") + 
  ylab("Billions of Dollars")  +
  ggtitle("Illinois Expenditures and Revenue Totals, 1998-2023")

fiscal_gap2

Expenditure and revenue amounts in millions of dollars:

exp_long %>%
  filter(Year == 2023) %>%
  #mutate(`Total Expenditures`= sum(Dollars, na.rm = TRUE)) %>%
 # select(-c(Year, `Total Expenditures`)) %>%
  arrange(desc(`Dollars`)) %>%
  ggplot() + 
  geom_col(aes(x = fct_reorder(Category_name, `Dollars`), y = `Dollars`, fill = "red"))+ 
  coord_flip() +
      theme_bw()+
  theme(legend.position = "none") +
  labs(title = "Expenditures for FY2023") +
    xlab("Expenditure Categories") +
  ylab("Millions of Dollars") 

rev_long %>%
  filter(Year == 2023) %>%
  #mutate(`Total Expenditures`= sum(Dollars, na.rm = TRUE)) %>%
 # select(-c(Year, `Total Expenditures`)) %>%
  arrange(desc(`Dollars`)) %>%
  ggplot() + 
  geom_col(aes(x = fct_reorder(Category_name, `Dollars`), y = `Dollars`))+ 
  coord_flip() +
    theme_bw() +
    theme(legend.position = "none") +
      labs(title = "Revenues for FY2023")+
    xlab("Revenue Categories") +
  ylab("Millions of Dollars") 

Expenditure and revenues when focusing on largest categories and combining others into “All Other Expenditures(Revenues)”:

exp_long %>%
  filter( Year == 2023) %>%
  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`), fill = "rosybrown2")+ 
  coord_flip() +
      theme_bw() +
    labs(title = "Expenditures for FY2023") +
    xlab("") +
  ylab("Millions of Dollars")

rev_long %>%
  filter( Year == 2023) %>%
  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`), y = `Dollars`), fill = "dark gray")+ 
  coord_flip() +
      theme_bw() +
    labs(title = "Revenues for FY2023") +
    xlab("") +
  ylab("Millions of Dollars")

Changes in Categories - 2021 to 2022:

rev_long %>%
    filter(Year == "2023" | Year == "2022") %>%
  mutate(Year = as.character(Year)) %>%
  ggplot(aes(x = Dollars, y = reorder(Category, Dollars))) +
  geom_line(aes(group = Category) )+
    geom_text(aes(x = ifelse(Year == "2023", as.numeric(Dollars), NA),  label = ifelse(Year == "2023", Category_name, "")),  
            hjust = -0.2,
            size = 2.8) +
         geom_point(aes(color = Year), size=2)  +
  labs(title = "2022 to 2023 Change in Revenue", x = "Millions of Dollars" , y = "",  caption = "")  +
   scale_fill_manual(values = c("#d62828", "#003049"), labels = c("FY 2022", "FY 2023"))+
    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(),
 # axis.line.x = element_blank(),
  #  axis.title.y = element_blank(),
 # axis.ticks.x = element_blank()
 )+
  scale_x_continuous(limits = c(0, 35000), labels = comma)

exp_long %>%
    filter(Year == "2023" | Year == "2022") %>%
  mutate(Year = as.character(Year)) %>%
  ggplot(aes(x = Dollars, y = reorder(Category, Dollars))) +
  geom_line(aes(group = Category) )+
  geom_text(aes(x = ifelse(Year == "2023", (as.numeric(Dollars)+1100), NA),  
                label = ifelse(Year == "2023", Category_name, "")),  
            hjust = 0,
            size = 2.8) +
  geom_point(aes(color = Year), size=2 #, alpha = 0.5
             )  +
  labs(title = "2022 to 2023 Change in Expenditures", x = "Millions of Dollars" , y = "",  caption = "")  +
   scale_fill_manual(values = c("#d62828", "#003049"), labels = c("FY 2022", "FY 2023"))+
    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(),
  #axis.line.x = element_blank(),
   # axis.title.y = element_blank(),
  #axis.ticks.x = element_blank()
  )+
  scale_x_continuous(limits = c(0, 35000), labels = comma)

Top 3 Revenues

annotation <- data.frame(
  x = c(2012, 2019, 2012),
  y = c(16000, 10000, 5000),  
  label = c("Individual Income Tax", "Sales Tax", "Corporate Income Tax")
)

top3 <- rev_long  %>% 
  filter(Category == "02" | Category == "03" | Category == "06") %>%
  ggplot()+
  geom_line(aes(x=Year, y=Dollars, color = Category_name)) + 
  geom_text(data = annotation, aes(x=x, y=y, label=label))+
    theme_bw() +
  
  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 = "Nominal Dollars (in Millions)") 
  

top3

Own Source and Fed Transfers

ownsource_rev <- rev_long %>%
  filter(!Category %in% c("57", "58", "59")) %>%
  group_by(Year) %>% 
  summarize(Dollars = sum(Dollars))

# ownsource_rev %>% 
#   ggplot()+geom_line(aes(x=Year, y=Dollars)) + 
#   labs(title = "Own Source Revenues", subtitle = "Total own source revenue", y = "Millions of Dollars")

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(2010, 2010),
  y = c(50000, 25000),  
  label = c("Own Source Revenue", "Federal Transfers")
)


ggplot() + 
  geom_line(data = ownsource_rev, aes(x=Year, y=Dollars), color = "Red") + 
  geom_line(data = fed_rev, aes(x=fy, y=fed_total), color = "Black") + 
    geom_text(data = annotation, aes(x=x, y=y, label=label))+
    scale_y_continuous(labels = comma)+
  theme(legend.position = "none")+

  theme_bw()+
  labs(title = "Own Source Revenue and Federal Transfers", 
  y = "Nominal Dollars (in Millions)")

Change from Previous Year

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.

exp_totals <- ff_exp %>% rowwise() %>% mutate(exp_TOTALS = sum(across(exp_402:exp_970)))
rev_totals <- ff_rev %>%    rowwise() %>% 
  mutate(rev_TOTALS = sum(across(rev_02:rev_78)))



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"

  ) ) %>% 
  select(-type, -Category) %>%  # drop extra columns type and Category number
  group_by(Year, Category_name) %>%
  summarise(Dollars= round(sum(Dollars),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())

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 == "444" ~ "Human Services" ,
           Category == "478" ~ "HEALTHCARE & FAM SER NET OF MEDICAID", 
            Category == "482" ~ "PUBLIC HEALTH", 
            Category == "492" ~ "REVENUE", 
             Category == "494" ~ "Transportation" ,
             Category == "532" ~ "ENVIRONMENTAL PROTECT AGENCY" ,
             Category == "557" ~ "Tollway" ,
             Category == "684" ~ "IL COMMUNITY COLLEGE BOARD", 
             Category == "691" ~ "IL STUDENT ASSISTANCE COMM" ,
             Category == "900" ~ "NOT IN FRAME",
             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 Revenue Sharing",
          Category == "TOTALS" ~ "Total") #,T ~ "All Other Expenditures **")
           ) %>% 
  select(-type, -Category) %>% 
  group_by(Year, Category_name) %>% 
  summarise(Dollars= round(sum(Dollars),digits=2)) %>% 
  mutate(Category_name = str_to_title(Category_name))

expenditure_wide2 <- exp_long%>% 
  pivot_wider(names_from = Category_name, 
              values_from = Dollars) %>%
  #relocate("All Other Expenditures **", .after = last_col()) %>%
  relocate("Total", .after =  last_col())


# function for calculating the CAGR
calc_cagr <- function(df, n) {
  df <- exp_long %>%
    #select(-type) %>%
    arrange(Category_name, Year) %>%
    group_by(Category_name) %>%
    mutate(cagr = ((`Dollars` / lag(`Dollars`, n)) ^ (1 / n)) - 1)

  return(df)
}

Things to do when updating the code:

• Each year, you neeed 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.

# This works for one variable at a time
cagr_25 <- calc_cagr(exp_long, 25) %>% 
  # group_by(Category) %>%
  summarize(cagr_25 = round(sum(cagr*100, na.rm = TRUE), 2))

# doesn't need to be changed since it is just pre-covid 
cagr_precovid <- exp_long %>%
  filter(Year <= 2019) %>%
  calc_cagr(21) %>% 
  summarize(cagr_21 = round(sum(cagr*100, na.rm = TRUE), 2))


# Update year in the filter() and summarize() commands to current year.
cagr_10 <- calc_cagr(exp_long, 10) %>% 
  filter(Year == 2023) %>%
  summarize(cagr_10 = case_when(Year == 2023 ~ round(sum(cagr*100, na.rm = TRUE), 2)))

cagr_5 <- calc_cagr(exp_long, 5) %>% 
  filter(Year == 2023) %>%
  summarize(cagr_5 = case_when(Year == 2023 ~ round(sum(cagr*100, na.rm = TRUE), 2)))

cagr_3 <- calc_cagr(exp_long, 3) %>% 
  filter(Year == 2023) %>%
  summarize(cagr_3 = case_when(Year == 2023 ~ round(sum(cagr*100, na.rm = TRUE), 2)))

cagr_2 <- calc_cagr(exp_long, 2) %>% 
  filter(Year == 2023) %>%
  summarize(cagr_2 = case_when(Year == 2023 ~ round(sum(cagr*100, na.rm = TRUE), 2)))

cagr_1 <- calc_cagr(exp_long, 1) %>% 
  filter(Year == 2023) %>%
  summarize(cagr_1 = case_when(Year == 2023 ~ round(sum(cagr*100, na.rm = TRUE), 2)))

# update variables so cagr_24 becomes cagr_25
CAGR_expenditures_summary_tot <- data.frame(cagr_1, cagr_2, cagr_3, cagr_5, cagr_10, cagr_25 ) %>% 
  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,"25 Year CAGR" = cagr_25 )

move_to_last <- function(df, n) df[c(setdiff(seq_len(nrow(df)), n), n), ]

CAGR_expenditures_summary_tot <- move_to_last(CAGR_expenditures_summary_tot, 29) 

#CAGR_expenditures_summary_tot <-   select(CAGR_expenditures_summary_tot, -1) 

CAGR_expenditures_summary_tot%>%   
  kbl(caption = "CAGR Calculations for All Expenditure Categories" , row.names=FALSE) %>% 
     kable_classic() 

CAGR Calculations for All Expenditure Categories

Expenditure Category	1 Year CAGR	2 Year CAGR	3 Year CAGR	5 Year CAGR	10 Year CAGR	25 Year CAGR
Aging	17.55	9.83	9.02	7.66	2.10	7.72
Agriculture	0.00	15.47	7.53	1.12	2.48	0.82
Bus & Profession Regulation	5.99	6.97	5.84	3.79	-1.77	1.62
Capital Improvement	52.57	19.41	28.05	23.53	2.05	3.80
Central Management	17.69	7.02	4.65	6.15	4.02	4.76
Children And Family Services	26.04	10.05	8.40	7.78	3.33	0.78
Community Development	7.69	-5.46	34.19	24.64	4.33	4.79
Corrections	13.45	2.78	3.48	-2.65	3.12	2.21
Debt Service	-2.89	-1.86	0.07	-0.20	0.80	5.73
Elected Officers	8.01	5.71	6.17	4.81	4.53	3.88
Employment Security	-1.45	-8.96	4.57	2.72	0.19	1.63
Environmental Protect Agency	-5.67	-4.47	-5.02	-7.43	1.36	2.78
Healthcare & Fam Ser Net Of Medicaid	13.26	4.24	6.75	0.77	0.91	5.46
Human Services	21.34	16.11	13.77	10.19	4.64	3.29
Judicial	20.75	8.50	8.60	7.28	5.64	3.37
K-12 Education	9.86	9.86	9.15	7.04	5.35	4.35
Legislative	66.41	41.09	27.82	18.80	5.82	5.20
Local Govt Revenue Sharing	5.36	23.23	19.08	12.02	6.46	4.68
Medicaid	13.10	11.05	13.29	11.76	8.17	7.43
Natural Resources	10.27	5.19	5.02	4.86	2.81	1.94
Other Boards & Commissions	33.88	15.00	15.84	8.51	1.52	5.13
Other Departments	16.22	8.66	8.38	8.06	6.03	9.36
Public Health	-8.36	-9.81	11.05	15.14	7.77	6.44
Public Safety	1.22	-6.86	5.37	14.12	7.58	5.69
Revenue	8.07	8.65	20.74	30.48	17.30	6.39
State Employee Healthcare	-0.10	2.08	0.22	-12.50	3.27	5.82
State Pension Contribution	5.05	10.11	8.85	9.55	9.06	10.52
Tollway	-9.90	-1.77	-0.42	5.23	7.14	6.78
Transportation	16.28	-2.95	7.20	6.91	1.59	3.80
University Education	12.53	7.49	5.01	4.61	0.98	0.81
Total	10.81	9.15	10.67	8.09	5.74	5.19

# revenue version function:
calc_cagr <- function(df, n) {
  df <- rev_long %>%
    arrange(Category_name, Year) %>%
    group_by(Category_name) %>%
    mutate(cagr = ((Dollars / lag(Dollars, n)) ^ (1 / n)) - 1)

  return(df)
}

# This works for one variable at a time
cagr_25 <- calc_cagr(rev_long, 25) %>% 
     # group_by(Category) %>%
  summarize(cagr_25 = round(sum(cagr*100, na.rm = TRUE), 2))

cagr_10 <- calc_cagr(rev_long, 10) %>% 
  filter(Year == 2023) %>%
  summarize(cagr_10 = case_when(Year == 2023 ~ round(sum(cagr*100, na.rm = TRUE), 2)))

cagr_5 <- calc_cagr(rev_long, 5) %>% 
  filter(Year == 2023) %>%
  summarize(cagr_5 = case_when(Year == 2023 ~ round(sum(cagr*100, na.rm = TRUE), 2)))

cagr_3 <- calc_cagr(rev_long, 3) %>% 
  filter(Year == 2023) %>%
  summarize(cagr_3 = case_when(Year == 2023 ~ round(sum(cagr*100, na.rm = TRUE), 2)))

cagr_2 <- calc_cagr(rev_long, 2) %>% 
  filter(Year == 2023) %>%
  summarize(cagr_2 = case_when(Year == 2023 ~ round(sum(cagr*100, na.rm = TRUE), 2)))

 cagr_1 <- calc_cagr(rev_long, 1) %>% 
  filter(Year == 2023) %>%
  summarize(cagr_1 = case_when(Year == 2023 ~ round(sum(cagr*100, na.rm = TRUE), 2)))

CAGR_revenue_summary_tot <- data.frame(cagr_1, cagr_2, cagr_3, cagr_5, cagr_10, cagr_25) %>%   
  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,"25 Year CAGR" = cagr_25 )

CAGR_revenue_summary_tot <- move_to_last(CAGR_revenue_summary_tot,1)
CAGR_revenue_summary_tot <- move_to_last(CAGR_revenue_summary_tot,22)

CAGR_revenue_summary_tot %>% 
  kbl(caption = "CAGR Calculations for All Revenue Sources", row.names = FALSE) %>% 
     kable_classic() 

CAGR Calculations for All Revenue Sources

Revenue Category	1 Year CAGR	2 Year CAGR	3 Year CAGR	5 Year CAGR	10 Year CAGR	25 Year CAGR
Cigarette Taxes	-6.66	-7.48	-2.66	0.54	-0.86	2.13
Corp Franchise Taxes & Fees	4.46	-16.05	2.23	1.61	0.94	2.61
Corporate Income Taxes	4.32	35.74	46.02	28.69	9.92	7.57
Federal Medicaid	6.09	7.28	13.44	8.44	9.32	7.47
Federal Other	-43.87	8.68	3.88	13.40	5.67	4.35
Federal Transportation	15.27	-5.75	5.82	5.82	2.17	3.79
Gifts And Bequests	13.16	18.34	31.72	11.69	10.40	11.50
Individual Income Taxes	-7.27	2.18	7.87	5.60	4.09	5.13
Inheritance Tax	-17.85	5.67	21.81	7.24	5.71	2.78
Insurance Taxes&Fees&Licenses	8.72	2.51	11.41	3.37	3.83	6.66
Licenses, Fees & Registrations	9.28	2.26	13.25	11.07	6.18	7.94
Liquor Gallonage Taxes	-1.25	0.64	1.41	1.32	1.22	7.09
Lottery Receipts	11.93	2.46	10.36	4.32	1.23	2.53
Medical Provider Assessments	9.48	3.60	5.57	13.14	9.46	8.41
Motor Fuel Tax	1.52	3.78	3.41	13.47	7.38	2.73
Motor Vehicle And Operators	0.00	-2.85	3.08	1.49	0.89	3.09
Other Taxes	12.97	36.05	25.79	14.84	16.69	8.06
Public Utility Taxes	2.20	2.67	0.44	0.17	-0.43	0.76
Receipts From Revenue Producing	8.46	5.71	6.00	0.89	2.69	5.21
Riverboat Wagering Taxes	9.06	40.42	2.30	-4.27	-3.83	2.05
Sales Taxes	4.81	8.00	9.70	6.61	4.68	3.29
NA	Inf	Inf	Inf	Inf	Inf	Inf
All Other Sources	50.67	44.03	24.30	14.20	10.57	6.08
Total	-4.37	8.50	11.42	8.93	5.92	5.09

rm(cagr_1, cagr_2, cagr_3, cagr_5, cagr_10, cagr_25)

Update all years in mutate() commands so that they all go up by 1:

revenue_change2 <- rev_long %>%
  #select(-c(Category)) %>%
  filter(Year > 2021) %>%
  pivot_wider(names_from = Year , values_from = Dollars,   names_prefix = "Dollars_") %>%
  mutate(
    "FY 2023 ($ billions)" = round(Dollars_2023/1000, digits = 1),
    "FY 2022 ($ billions)" = round(Dollars_2022/1000, digits = 1),

#    "Change from 2022 to 2023" = round(Dollars_2022 - Dollars_2021, digits = 2),
         "1-Year Change" = round(((Dollars_2023 -Dollars_2022)/Dollars_2022*100), digits = 2)) %>%
  left_join(CAGR_revenue_summary_tot, by = c("Category_name" = "Revenue Category")) %>% 
    arrange(-`FY 2023 ($ billions)`)%>%
#  filter(Category_ame != "NA") %>%
  #select(-c(Dollars_2021, Dollars_2021, `1 Year CAGR`:`10 Year CAGR`)) %>%
  rename( "25-Year CAGR" = `25 Year CAGR`, 
          "Revenue Category" = Category_name ) %>%
  select(-c(Dollars_2022, Dollars_2023, `1 Year CAGR`:`10 Year CAGR`)) 


revenue_change2 <- move_to_last(revenue_change2,8)
revenue_change2 <- move_to_last(revenue_change2,1)

revenue_change2 %>%   
  filter(!is.na(`Revenue Category`)) %>%

  kbl(caption = "Table 1. Yearly Change in Revenue", row.names = FALSE) %>% 
   kable_classic() %>%
    row_spec(23, bold = T, color = "black", background = "gray")

Table 1. Yearly Change in Revenue

Revenue Category	FY 2023 ($ billions)	FY 2022 ($ billions)	1-Year Change	25-Year CAGR
Individual Income Taxes	22.1	23.8	-7.27	5.13
Federal Medicaid	20.2	19.0	6.09	7.47
Sales Taxes	16.2	15.4	4.81	3.29
Federal Other	10.9	19.4	-43.87	4.35
Corporate Income Taxes	10.1	9.7	4.32	7.57
All Other Sources	4.1	2.7	50.67	6.08
Receipts From Revenue Producing	2.6	2.4	8.46	5.21
Motor Fuel Tax	2.5	2.5	1.52	2.73
Federal Transportation	2.1	1.8	15.27	3.79
Gifts And Bequests	2.1	1.9	13.16	11.50
Licenses, Fees & Registrations	2.1	1.9	9.28	7.94
Lottery Receipts	1.6	1.4	11.93	2.53
Motor Vehicle And Operators	1.6	1.6	0.00	3.09
Other Taxes	1.6	1.4	12.97	8.06
Public Utility Taxes	1.4	1.4	2.20	0.76
Cigarette Taxes	0.8	0.8	-6.66	2.13
Insurance Taxes&Fees&Licenses	0.7	0.6	8.72	6.66
Inheritance Tax	0.5	0.6	-17.85	2.78
Liquor Gallonage Taxes	0.3	0.3	-1.25	7.09
Riverboat Wagering Taxes	0.3	0.3	9.06	2.05
Corp Franchise Taxes & Fees	0.2	0.2	4.46	2.61
Medical Provider Assessments	4.1	3.7	9.48	8.41
Total	108.1	113.0	-4.37	5.09

expenditure_change2 <- exp_long %>%
  #select(-c(type,Category)) %>%
  filter(Year > 2021) %>%
  pivot_wider(names_from = Year , values_from = Dollars,   names_prefix = "Dollars_") %>%
  mutate(
    "FY 2023 ($ billions)" = round(Dollars_2023/1000, digits = 1),
    "FY 2022 ($ billions)" = round(Dollars_2022/1000, digits = 1),

  #  "Change from 2021 to 2022" = Dollars_2022 - Dollars_2021,
         "1-Year Change" = round((Dollars_2023 -Dollars_2022)/Dollars_2022*100, digits = 2) )%>%
  left_join(CAGR_expenditures_summary_tot, by = c("Category_name" = "Expenditure Category")) %>% 
  arrange(-`FY 2023 ($ billions)`)%>%
  select(-c(Dollars_2023, Dollars_2022, `1 Year CAGR`:`10 Year CAGR`)) %>%

  rename( "25-Year CAGR" = `25 Year CAGR`, 
          "Expenditure Category" = Category_name )

expenditure_change2 <- move_to_last(expenditure_change2, 1)

expenditure_change2 %>% 

 filter(!is.na(`Expenditure Category`)) %>%

  kbl(caption = "Table 2. Yearly Change in Expenditures", row.names = FALSE) %>% 
  kable_classic() %>%
    row_spec(31, bold = T, color = "black", background = "gray")

Table 2. Yearly Change in Expenditures

Expenditure Category	FY 2023 ($ billions)	FY 2022 ($ billions)	1-Year Change	25-Year CAGR
Medicaid	32.4	28.7	13.10	7.43
K-12 Education	14.7	13.4	9.86	4.35
Local Govt Revenue Sharing	10.9	10.3	5.36	4.68
Human Services	8.8	7.3	21.34	3.29
State Pension Contribution	6.8	6.5	5.05	10.52
Other Departments	5.7	4.9	16.22	9.36
Transportation	5.0	4.3	16.28	3.80
State Employee Healthcare	3.0	3.0	-0.10	5.82
University Education	2.5	2.3	12.53	0.81
Debt Service	2.0	2.0	-2.89	5.73
Revenue	2.0	1.8	8.07	6.39
Tollway	1.9	2.1	-9.90	6.78
Corrections	1.7	1.5	13.45	2.21
Public Safety	1.7	1.7	1.22	5.69
Children And Family Services	1.6	1.3	26.04	0.78
Community Development	1.5	1.4	7.69	4.79
Aging	1.4	1.2	17.55	7.72
Central Management	1.4	1.2	17.69	4.76
Elected Officers	1.1	1.0	8.01	3.88
Public Health	0.8	0.8	-8.36	6.44
Capital Improvement	0.7	0.4	52.57	3.80
Environmental Protect Agency	0.6	0.7	-5.67	2.78
Judicial	0.6	0.5	20.75	3.37
Healthcare & Fam Ser Net Of Medicaid	0.4	0.4	13.26	5.46
Employment Security	0.3	0.3	-1.45	1.63
Natural Resources	0.3	0.3	10.27	1.94
Other Boards & Commissions	0.3	0.2	33.88	5.13
Bus & Profession Regulation	0.2	0.2	5.99	1.62
Legislative	0.2	0.1	66.41	5.20
Agriculture	0.1	0.1	0.00	0.82
Total	110.6	99.8	10.81	5.19

Summary Tables - Largest Categories

The 10 largest revenue sources and 13 largest expenditure sources remain separate categories and all other smaller sources/expenditures are combined into “All Other _____”. 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.

• take ff_rev and ff_exp data frames, which were in wide format, pivot them longer and mutate the Category_name variable to nicer labels. Keep largest categories separate and aggregate the rest.

exp_totals <- ff_exp %>% rowwise() %>% mutate(exp_TOTALS = sum(across(exp_402:exp_970))) # creates total column too

rev_totals <- ff_rev %>% rowwise() %>%  mutate(rev_TOTALS = sum(across(rev_02:rev_78)))

rev_long_majorcats <- 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" ~ "Income Tax" ,
    Category == "03" ~ "Corporate Income Tax" ,
    Category == "06" ~ "Sales Tax" ,
    Category == "09" ~ "Motor Fuel Taxes" ,
 #   Category == "12" ~ "PUBLIC UTILITY TAXES, gross of PPRT" ,
  #  Category == "15" ~ "CIGARETTE TAXES" ,
 #   Category == "18" ~ "LIQUOR GALLONAGE TAXES" ,
 #  Category == "21" ~ "INHERITANCE TAX" ,
  #  Category == "24" ~ "INSURANCE TAXES&FEES&LICENSES, net of refunds " ,
   # 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, Registration" ,
   # 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 Reimbursements", 
    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 == "78new" ~  "ALL OTHER SOURCES" ,
   # Category == "79" ~   "COOK COUNTY IGT", #dropped
 #   Category == "98" ~  "PRIOR YEAR REFUNDS", #dropped
                
Category == "TOTALS" ~ "Total Revenue",
T ~ "All Other Sources **" # any other Category number that was not specifically referenced is cobined into Other Revenue Sources

  ) ) %>% 
  select(-type, -Category) %>%  # drop extra columns type and Category number
  group_by(Year, Category_name) %>%
  summarise(Dollars= round(sum(Dollars),digits=2)) 

# revenue_wide # not actually in wide format yet. 
# has 10 largest rev sources separate and combined all others to Other in long data format. 


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


exp_long_majorcats <- 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 == "444" ~ "Human Services" ,
           #  Category == "478" ~ "HEALTHCARE & FAM SER NET OF MEDICAID", 
           #  Category == "482" ~ "PUBLIC HEALTH", 
           #  Category == "492" ~ "REVENUE", 
             Category == "494" ~ "Transportation" ,
           #  Category == "532" ~ "ENVIRONMENTAL PROTECT AGENCY" ,
             Category == "557" ~ "Tollway" ,
           #  Category == "684" ~ "IL COMMUNITY COLLEGE BOARD", 
            # Category == "691" ~ "IL STUDENT ASSISTANCE COMM" ,
           #  Category == "900" ~ "NOT IN FRAME",
             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 Revenue Sharing",
          Category == "TOTALS" ~ "Total Expenditures",
             T ~ "All Other Expenditures **")
           ) %>% 
  select(-type, -Category) %>% 
  group_by(Year, Category_name) %>% 
  summarise(Dollars= round(sum(Dollars),digits=2))

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

# function for calculating the CAGR
calc_cagr <- function(df, n) {
  df <- exp_long_majorcats %>%
    #select(-type) %>%
    arrange(Category_name, Year) %>%
    group_by(Category_name) %>%
    mutate(cagr = ((`Dollars` / lag(`Dollars`, n)) ^ (1 / n)) - 1)

  return(df)
}

# This works for one variable at a time
cagr_25 <- calc_cagr(exp_long_majorcats, 25) %>% 
  # group_by(Category) %>%
  summarize(cagr_25 = round(sum(cagr*100, na.rm = TRUE), 2))

cagr23_precovid <- exp_long_majorcats %>%
  filter(Year <= 2019) %>%
  calc_cagr(21) %>% 
  summarize(cagr_21 = round(sum(cagr*100, na.rm = TRUE), 2))



cagr_10 <- calc_cagr(exp_long_majorcats, 10) %>% 
  filter(Year == 2023) %>%
  summarize(cagr_10 = case_when(Year == 2023 ~ round(sum(cagr*100, na.rm = TRUE), 2)))

cagr_5 <- calc_cagr(exp_long_majorcats, 5) %>% 
  filter(Year == 2023) %>%
  summarize(cagr_5 = case_when(Year == 2023 ~ round(sum(cagr*100, na.rm = TRUE), 2)))

cagr_3 <- calc_cagr(exp_long_majorcats, 3) %>% 
  filter(Year == 2023) %>%
  summarize(cagr_3 = case_when(Year == 2023 ~ round(sum(cagr*100, na.rm = TRUE), 2)))

cagr_2 <- calc_cagr(exp_long_majorcats, 2) %>% 
  filter(Year == 2023) %>%
  summarize(cagr_2 = case_when(Year == 2023 ~ round(sum(cagr*100, na.rm = TRUE), 2)))

cagr_1 <- calc_cagr(exp_long_majorcats, 1) %>% 
  filter(Year == 2023) %>%
  summarize(cagr_1 = case_when(Year == 2023 ~ round(sum(cagr*100, na.rm = TRUE), 2)))

CAGR_expenditures_majorcats_tot <- data.frame(cagr_1, cagr_2, cagr_3, cagr_5, cagr_10, cagr_25 ) %>% 
  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,"25-Year CAGR" = cagr_25 )

move_to_last <- function(df, n) df[c(setdiff(seq_len(nrow(df)), n), n), ]

CAGR_expenditures_majorcats_tot <- move_to_last(CAGR_expenditures_majorcats_tot, 1)
CAGR_expenditures_majorcats_tot <- move_to_last(CAGR_expenditures_majorcats_tot, 13) 


CAGR_expenditures_majorcats_tot%>%   
  kbl(caption = "CAGR Calculations for Largest Expenditure Categories" , row.names=FALSE) %>% 
     kable_classic() 

CAGR Calculations for Largest Expenditure Categories

Expenditure Category	1 Year CAGR	2 Year CAGR	3 Year CAGR	5 Year CAGR	10 Year CAGR	25-Year CAGR
Capital Improvement	52.57	19.41	28.05	23.53	2.05	3.80
Central Management	17.69	7.02	4.65	6.15	4.02	4.76
Community Development	7.69	-5.46	34.19	24.64	4.33	4.79
Debt Service	-2.89	-1.86	0.07	-0.20	0.80	5.73
Human Services	21.34	16.11	13.77	10.19	4.64	3.29
K-12 Education	9.86	9.86	9.15	7.04	5.35	4.35
Local Govt Revenue Sharing	5.36	23.23	19.08	12.02	6.46	4.68
Medicaid	13.10	11.05	13.29	11.76	8.17	7.43
Public Safety	1.22	-6.86	5.37	14.12	7.58	5.69
State Employee Healthcare	-0.10	2.08	0.22	-12.50	3.27	5.82
State Pension Contribution	5.05	10.11	8.85	9.55	9.06	10.52
Tollway	-9.90	-1.77	-0.42	5.23	7.14	6.78
Transportation	16.28	-2.95	7.20	6.91	1.59	3.80
All Other Expenditures **	13.08	6.46	8.01	6.52	4.32	3.88
Total Expenditures	10.81	9.15	10.67	8.09	5.74	5.19

current_year <- 2023
last_year <- 2022

# Yearly change for Top 13 largest expenditure categories
expenditure_change_majorcats <- exp_long_majorcats %>%
  filter(Year > 2021) %>%
  pivot_wider(names_from = Year , values_from = Dollars,   names_prefix = "Dollars_") %>%
  mutate("FY 2023 ($ Billions)" = round(Dollars_2023/1000, digits = 1),
         "FY 2022 ($ Billions)" = round(Dollars_2022/1000, digits = 1),
         "1-Year Change" = percent((Dollars_2023 -Dollars_2022)/Dollars_2022, accuracy = .1) )  %>%
  left_join(CAGR_expenditures_majorcats_tot, by = c("Category_name" = "Expenditure Category")) %>% 
  arrange(-`FY 2023 ($ Billions)`)%>%
  mutate(`25-Year CAGR` = percent(`25-Year CAGR`/100, accuracy=.1)) %>%
  select(-c(Dollars_2023, Dollars_2022, `1 Year CAGR`:`10 Year CAGR`)) %>%
  rename( "25-Year CAGR" = `25-Year CAGR`, 
          "Expenditure Category" = Category_name )

expenditure_change_majorcats <- move_to_last(expenditure_change_majorcats, 3) 

expenditure_change_majorcats <- move_to_last(expenditure_change_majorcats, 1)

expenditure_change_majorcats %>% 
  kbl(caption = "Yearly Change in Expenditures", row.names = FALSE, align = "l") %>% 
  kable_classic() %>%
    row_spec(15, bold = T, color = "black", background = "gray")

Yearly Change in Expenditures

Expenditure Category	FY 2023 ($ Billions)	FY 2022 ($ Billions)	1-Year Change	25-Year CAGR
Medicaid	32.4	28.7	13.1%	7.4%
K-12 Education	14.7	13.4	9.9%	4.3%
Local Govt Revenue Sharing	10.9	10.3	5.4%	4.7%
Human Services	8.8	7.3	21.3%	3.3%
State Pension Contribution	6.8	6.5	5.1%	10.5%
Transportation	5.0	4.3	16.3%	3.8%
State Employee Healthcare	3.0	3.0	-0.1%	5.8%
Debt Service	2.0	2.0	-2.9%	5.7%
Tollway	1.9	2.1	-9.9%	6.8%
Public Safety	1.7	1.7	1.2%	5.7%
Community Development	1.5	1.4	7.7%	4.8%
Central Management	1.4	1.2	17.7%	4.8%
Capital Improvement	0.7	0.4	52.6%	3.8%
All Other Expenditures **	19.8	17.5	13.1%	3.9%
Total Expenditures	110.6	99.8	10.8%	5.2%

Top 10 revenue sources CAGRs and Yearly Change Tables:

##### Top 10 revenue CAGRs: ####


calc_cagr <- function(df, n) {
  df <- rev_long_majorcats %>%
    arrange(Category_name, Year) %>%
    group_by(Category_name) %>%
    mutate(cagr = ((Dollars / lag(Dollars, n)) ^ (1 / n)) - 1)

  return(df)
}

# This works for one variable at a time
cagr_25 <- calc_cagr(rev_long_majorcats, 25) %>% 
     # group_by(Category) %>%
  summarize(cagr_25 = round(sum(cagr*100, na.rm = TRUE), 2))

cagr_10 <- calc_cagr(rev_long_majorcats, 10) %>% 
  filter(Year == 2023) %>%
  summarize(cagr_10 = case_when(Year == 2023 ~ round(sum(cagr*100, na.rm = TRUE), 2)))

cagr_5 <- calc_cagr(rev_long_majorcats, 5) %>% 
  filter(Year == 2023) %>%
  summarize(cagr_5 = case_when(Year == 2023 ~ round(sum(cagr*100, na.rm = TRUE), 2)))

cagr_3 <- calc_cagr(rev_long_majorcats, 3) %>% 
  filter(Year == 2023) %>%
  summarize(cagr_3 = case_when(Year == 2023 ~ round(sum(cagr*100, na.rm = TRUE), 2)))

cagr_2 <- calc_cagr(rev_long_majorcats, 2) %>% 
  filter(Year == 2023) %>%
  summarize(cagr_2 = case_when(Year == 2023 ~ round(sum(cagr*100, na.rm = TRUE), 2)))

 cagr_1 <- calc_cagr(rev_long_majorcats, 1) %>% 
  filter(Year == 2023) %>%
  summarize(cagr_1 = case_when(Year == 2023 ~ round(sum(cagr*100, na.rm = TRUE), 2)))

CAGR_revenue_majorcats_tot <- data.frame(cagr_1, cagr_2, cagr_3, cagr_5, cagr_10, cagr_25) %>%   
  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, "25-Year CAGR" = cagr_25 )

CAGR_revenue_majorcats_tot <- move_to_last(CAGR_revenue_majorcats_tot,1)
CAGR_revenue_majorcats_tot <- move_to_last(CAGR_revenue_majorcats_tot,11)

CAGR_revenue_majorcats_tot %>% 
  kbl(caption = "CAGR Calculations for Largest Revenue Sources", row.names = FALSE) %>% 
     kable_classic() 

CAGR Calculations for Largest Revenue Sources

Revenue Category	1 Year CAGR	2 Year CAGR	3 Year CAGR	5 Year CAGR	10 Year CAGR	25-Year CAGR
Corporate Income Tax	4.32	35.74	46.02	28.69	9.92	7.57
Federal Medicaid Reimbursements	6.09	7.28	13.44	8.44	9.32	7.47
Federal Other	-43.87	8.68	3.88	13.40	5.67	4.35
Federal Transportation	15.27	-5.75	5.82	5.82	2.17	3.79
Income Tax	-7.27	2.18	7.87	5.60	4.09	5.13
Licenses, Fees, Registration	9.28	2.26	13.25	11.07	6.18	7.94
Medical Provider Assessments	9.48	3.60	5.57	13.14	9.46	8.41
Motor Fuel Taxes	1.52	3.78	3.41	13.47	7.38	2.73
Receipts from Revenue Producing	8.46	5.71	6.00	0.89	2.69	5.21
Sales Tax	4.81	8.00	9.70	6.61	4.68	3.29
All Other Sources **	14.45	14.15	13.77	6.91	4.76	4.30
Total Revenue	-4.37	8.50	11.42	8.93	5.92	5.09

###### Yearly change summary table for Top 10 Revenues #####
revenue_change_majorcats <- rev_long_majorcats %>%
  #select(-c(Category)) %>%
  filter(Year > 2021) %>%
  pivot_wider(names_from = Year , values_from = Dollars,   names_prefix = "Dollars_") %>%
  mutate(
    "FY 2023 ($ billions)" = round(Dollars_2023/1000, digits = 1),
            "FY 2022 ($ billions)" = round(Dollars_2022/1000, digits = 1),

         "1-Year Change" = percent(((Dollars_2023 -Dollars_2022)/Dollars_2022), accuracy = .1)) %>%
  left_join(CAGR_revenue_majorcats_tot, by = c("Category_name" = "Revenue Category")) %>% 
    arrange(-`FY 2023 ($ billions)`)%>%
  #select(-c(Dollars_2021, Dollars_2021, `1 Year CAGR`:`10 Year CAGR`)) %>%
  mutate("25-Year Change" = percent(`25-Year CAGR`/100, accuracy=.1)) %>%
  rename("Revenue Category" = Category_name ) %>%
  select(-c(Dollars_2023, Dollars_2022, `1 Year CAGR`:`25-Year CAGR`)) 

revenue_change_majorcats <- move_to_last(revenue_change_majorcats,6)

revenue_change_majorcats <- move_to_last(revenue_change_majorcats,1)

revenue_change_majorcats%>% 
  kbl(caption = "Yearly Change in Revenue for Main Revenue Sources", row.names = FALSE, align = "l") %>% 
   kable_classic() %>%
    row_spec(12, bold = T, color = "black", background = "gray")

Yearly Change in Revenue for Main Revenue Sources

Revenue Category	FY 2023 ($ billions)	FY 2022 ($ billions)	1-Year Change	25-Year Change
Income Tax	22.1	23.8	-7.3%	5.1%
Federal Medicaid Reimbursements	20.2	19.0	6.1%	7.5%
Sales Tax	16.2	15.4	4.8%	3.3%
All Other Sources **	15.2	13.3	14.4%	4.3%
Corporate Income Tax	10.1	9.7	4.3%	7.6%
Medical Provider Assessments	4.1	3.7	9.5%	8.4%
Receipts from Revenue Producing	2.6	2.4	8.5%	5.2%
Motor Fuel Taxes	2.5	2.5	1.5%	2.7%
Federal Transportation	2.1	1.8	15.3%	3.8%
Licenses, Fees, Registration	2.1	1.9	9.3%	7.9%
Federal Other	10.9	19.4	-43.9%	4.3%
Total Revenue	108.1	113.0	-4.4%	5.1%

# #install.packages("openxlsx")
# library(openxlsx)
# 
# dataset_names <- list('rev_long' = rev_long, 'exp_long' = exp_long, 
#                       `Table 1` = expenditure_change2, `Table 2` = revenue_change2,
#                       'Table 4.a' = CAGR_revenue_summary_tot, 'Table 4.b' = CAGR_expenditures_summary_tot, 
#                       'year_totals' = year_totals)
# 
# write.xlsx(dataset_names, file = 'summary_file_FY2022_withTotals_Jan11.xlsx')

Export summary file with Totals

#install.packages("openxlsx")
library(openxlsx)

dataset_names <- list('Aggregate Revenues' = revenue_wide2, 
                      'Aggregate Expenditures' = expenditure_wide2, 

                      
                      'Table 1' = revenue_change_majorcats, #Top categories with yearly change, 23 yr cagr
                      'Table 2' = expenditure_change_majorcats,
                      
                      'Table 1a. AllCats' = revenue_change2,
                      'Table 2a. AllCats' = expenditure_change2,
                      
                      'CAGR Rev-MajorCats' = CAGR_revenue_majorcats_tot, # Categories Match Table 1 in paper
                      'CAGR Exp-MajorCats' = CAGR_expenditures_majorcats_tot, 
                                            
                     # 'Table 1-AllCats' = expenditure_change_allcats,  # All Categories by Year
                    #  'Table 2-AllCats' = revenue_change_allcats,
                      
                    #  'CAGR_Revenue-AllCats' = CAGR_revenue_summary_tot, 
                   #   'CAGR_Expenditures-AllCats' = CAGR_expenditures_summary_tot, 
                      
                      'Fiscal Gap' = year_totals,    # Total Revenue, Expenditure, and Fiscal gap per year
                      
                      'aggregated_totals_long' = aggregated_totals_long # all data in long format. Good for creating pivot tables in Excel
                      )

write.xlsx(dataset_names, file = 'summary_file_FY23_wTotals_Nov142023.xlsx')

Saves main items in one excel file named summary_file.xlsx. Delete eval=FALSE to run on local computer.

# #install.packages("openxlsx")
# library(openxlsx)
# 
# dataset_names <- list('rev_long' = rev_long, 'exp_long' = exp_long, 
#                       `Table 1` = expenditure_change, `Table 2` = revenue_change,
#                       'Table 4.a' = CAGR_revenue_summary, 'Table 4.b' = CAGR_expenditures_summary, 
#                       'year_totals' = year_totals)
# 
# write.xlsx(dataset_names, file = 'summary_file_FY2022.xlsx')

Drop COVID Dollars

If only sustainable revenues are included in the model, then the federal dollars from the pandemic response (CARES, CRSSA,& ARPA) should be excluded from the calculation of the fiscal gap.

The Fiscal Futures model focuses on sustainable revenue sources. To understand our fiscal gap and outlook, we need to exclude these one time revenues. GOMB has emphasized that they have allocated COVID dollars to one time expenditures (unemployment trust fund, budget stabilization fund, etc.). The fiscal gap, graphs,and CAGRs have been recalculated in the Drop COVID Dollars section below.

NOTE: I have only dropped revenue with a source name = Federal Stimulus Package. Federal money went into other funds during the beginning of pandemic. All additional money for medicaid reimbursements and healthcare provider funds were not considered “Federal Stimulus Package” in the data and were not dropped.

• fund 0628 is essential government support services. Money in the fund is appropriated to cover COVID-19 related expenses. It should be included in our analytical frame based on criteria 2 and6 — the fund supports an important state function about public safety, which would have to be performed even the fund structure were not existed. Public safety is supported by a combination of departments and boards, including IL Emergency Management Agency, which is the administering agency of the fund.

• Education Stabilization Fund
  

• ESSER 1, 2, and 3
  

• CSLFRF (State and Local CURE)
  

• Provider Relief Fund
  

• Coronavirus Relief Fund (CRF)
  

• Consolidated Appropriations Act 2020
  

• Families First Coronavirus Response Act
  

• Paycheck Protection Program and Health Care Enhancement Act

Need to recreate ff_exp and ff_rev totals without stimulus dollars.

# does not include rev_type == 58, medicaid dollars
# covid_dollars <- rev_temp %>% filter(covid_dollars==1) # check what was dropped

#covid_dollars %>% group_by(fy,rev_type) %>% summarize(receipts = sum(receipts)) %>% pivot_wider(names_from="rev_type", values_from = "receipts")


rev_temp <- rev_temp %>%  filter(covid_dollars==0) # keeps observations that were not coded as COVID federal funds



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

ff_rev<- left_join(ff_rev, tax_refund)

ff_rev <- mutate_all(ff_rev, ~replace_na(.,0))


ff_rev <- ff_rev %>%
  mutate(rev_02 = rev_02 - ref_02,
         rev_03 = rev_03 - ref_03,
         rev_06 = rev_06 - ref_06,
         rev_09 = rev_09 - ref_09,
         rev_21 = rev_21 - ref_21,
         rev_24 = rev_24 - ref_24,
         rev_35 = rev_35 - ref_35

         ) %>% 
  select(-c(ref_02:ref_35, rev_99, rev_76, rev_NA, ref_NA
            ))

## Error in `select()`:
## ! Can't subset columns that don't exist.
## ✖ Column `rev_NA` doesn't exist.

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 TAX" ,
    Category == "03" ~ "CORPORATE INCOME TAX" ,
    Category == "06" ~ "SALES TAX" ,
    Category == "09" ~ "MOTOR FUEL TAX" ,
    Category == "12" ~ "PUBLIC UTILITY TAX" ,
    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
                 T ~ "Check Me!"

  ) )%>% 
  mutate(Category_name = str_to_title(Category_name))

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) %>%
  mutate(exp_903 = debt_cost) %>%
  left_join(transfers) %>%
  mutate(exp_970 = exp_971 + exp_972  + exp_975 + exp_976)

ff_exp<- ff_exp %>% select(-c(debt_cost, exp_971:exp_976)) # drop unwanted columns

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 == "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 == "444" ~ "HUMAN SERVICES" ,
             Category == "448" ~ "Innovation and Technology", # AWM added fy2022
             Category == "478" ~ "HEALTHCARE & FAM SER NET OF MEDICAID", 
             Category == "482" ~ "PUBLIC HEALTH", 
             Category == "492" ~ "REVENUE", 
             Category == "494" ~ "TRANSPORTATION" ,
             Category == "532" ~ "ENVIRONMENTAL PROTECT AGENCY" ,
             Category == "557" ~ "IL STATE TOLL HIGHWAY AUTH" ,
             Category == "684" ~ "IL COMMUNITY COLLEGE BOARD", 
             Category == "691" ~ "IL STUDENT ASSISTANCE COMM" ,
             Category == "900" ~ "NOT IN FRAME",
             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))


#exp_long_nototals <- exp_long %>% filter(Category_name != "Totals")


aggregated_totals_long <- rbind(rev_long, exp_long) 

## Error in rbind(deparse.level, ...): numbers of columns of arguments do not match

Change plots:

year_totals2 <- 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` = round(Revenue - Expenditures)) %>% 
  arrange(desc(Year))
# creates variable for the Gap each year

year_totals2 # gap for FY22 changed to 2.3 billion

annotation_billions <- data.frame(
  x = c(2004, 2017, 2019),
  y = c(60, 50, 10),  
  label = c("Expenditures","Revenue", "Fiscal Gap"))

fiscal_gap1 <- year_totals %>%
  ggplot() +
  geom_hline(yintercept = 0) +
  geom_line(aes(x = Year, y = Revenue/1000), color = "Black", lwd=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 = "gray") +

  geom_text(data = annotation_billions, aes(x=x, y=y, label=label))+
    theme_bw() +
  theme(legend.position = "none", #axis.text.y = element_blank(),  
        #axis.ticks.y = element_blank(),
                                    axis.title.y = element_blank())+
    scale_linetype_manual(values = c("dashed", "dashed")) +
  scale_y_continuous(limits = c(-20, 120), labels = comma)+
    scale_x_continuous(limits=c(1998,2023))

fiscal_gap1

## Error: Discrete value supplied to continuous scale

fiscal_gap_droppedCURE <- year_totals2 %>%  
  ggplot() +
  geom_hline(yintercept=0)+
  
    geom_hline(yintercept = 0) +
  geom_line(aes(x = Year, y = Revenue/1000), color = "Black", lwd=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 = "gray") +
  
  geom_text(data = annotation_billions, aes(x=x, y=y, label=label))+
    theme_bw() +
  theme(legend.position = "none",# axis.text.y = element_blank(),
                                    #axis.ticks.y = element_blank(),
                                    axis.title.y = element_blank() )+
    scale_linetype_manual(values = c("dashed", "dashed")) +
  scale_y_continuous(limits=c(-20,120), labels = comma)+
    scale_x_continuous(limits=c(1998,2023))
  # # geom_smooth adds regression line, graphed first so it appears behind line graph
  # geom_smooth(aes(x = Year, y = Revenue), color = "gray", method = "lm", se = FALSE) + 
  # geom_smooth(aes(x = Year, y = Expenditures), color = "rosybrown2", method = "lm", se = FALSE) +
  # 
  # # line graph of revenue and expenditures
  # geom_line(aes(x = Year, y = Revenue), color = "black", size=1) +
  # geom_line(aes(x = Year, y = Expenditures), color = "red", size=1) +
  # geom_line(aes(x=Year, y = `Fiscal Gap`), color="gray") +
  # 
  # geom_text(data= annotation, aes(x=x, y = y, label=label))+
  # 
  # # labels
  #   theme_bw() +
  # scale_y_continuous(labels = comma)+
  # xlab("Year") + 
  # ylab("Millions of Dollars")  +
  # ggtitle("Illinois Expenditures and Revenue Totals, 1998-2022")

fiscal_gap_droppedCURE

Compare with and without federal COVID dollars:

library(gridExtra)

cowplot::plot_grid(fiscal_gap1,
                   fiscal_gap_droppedCURE, nrow=1, labels = c("With ARPA State CURE Funds", "Without ARPA State Cure Funds"))

## Error: Discrete value supplied to continuous scale

Revenue amounts in millions of dollars:

rev_long %>%
  filter(Year == 2023) %>%
  #mutate(`Total Expenditures`= sum(Dollars, na.rm = TRUE)) %>%
 # select(-c(Year, `Total Expenditures`)) %>%
  arrange(desc(`Dollars`)) %>%
  ggplot() + 
  geom_col(aes(x = fct_reorder(Category_name, `Dollars`), y = `Dollars`))+ 
  coord_flip() +
    theme_bw() +
      labs(title = "Revenues for FY2023")+
    xlab("Revenue Categories") +
  ylab("Millions of Dollars")

Forecasting attempt

First images use revenue that includes all federal stimulus packages. Revenue projections are skewed heavily due to the large amount of covid money flowing in the past 2 years.

## Revenues
year_totals2 <- year_totals2 %>% 
  arrange(Year)

#ts_rev <- year_totals %>% select(Year, Revenue ) %>% arrange(Year)

tsrev <- ts(year_totals2$Revenue, start ="1998", frequency = 1) # yearly data

# start(tsrev) # 1998, January
# end(tsrev)  ## 2022 
# summary(tsrev)
# plot(tsrev)
# abline(reg=lm(tsrev~time(tsrev)))


#### ARIMAs
mymodel <- auto.arima(tsrev, seasonal = FALSE)
# mymodel          # ARIMA (0, 1, 0) with drift
myforecastrev <- forecast(mymodel, h = 20)
#plot(myforecastrev,  xlab ="", ylab ="Total Revenue", main ="Chicago Revenue")




#### revenue chart
model_rev <- auto.arima(tsrev, seasonal = FALSE)
forecast_rev <- forecast(model_rev, h = 20)

q <- forecast(forecast_rev,  h = 20) %>% 
  autoplot() +
    ylab("Dollars (Millions)") +
  xlab("Year") +
  ggtitle("Forecasted Revenue") +
  theme_classic() +
  scale_y_continuous(labels = dollar )

summary(forecast_rev)

# annotation <- data.frame(
#   x = c(2027, 2032),
#   y = c(200000, 300000),  
#   label = c("$120 billion in 2027","$135 billion in 2032")
# )

annotation <- data.frame(
  x = c(2020, 2032),
  y = c(150000, 200000),  
  label = c("$120 billion in 2027","$135 billion in 2032")
)

q+ geom_label(data = annotation, aes(x=x, y=y, label=label), size = 3) +
  labs(caption = "after dropping federal covid dollars")


## Expenditures
tsexp <- ts(year_totals2$Expenditures, start = "1998", frequency = 1)
model_exp<- auto.arima(tsexp, seasonal = FALSE)
# model_exp            # ARIMA (0,1,1) with drift

forecast_exp <- forecast(model_exp, h = 20) 
#plot(forecast_exp, xlab ="",  ylab ="Total Expenditures", main ="Chicago Expenditures")

p <- forecast(model_exp,  h = 20) %>% 
  autoplot() +
    ylab("Dollars (Millions)") +
  xlab("Year") +
  ggtitle("Forecasted Expenditures") +
  theme_classic() +
  scale_y_continuous(labels = dollar )

summary(forecast_exp)

annotation <- data.frame(
  x = c(2027, 2032),
  y = c(130000, 100000),  label = c("$117 ± 20 Billion in 2027","$132 ± 26 Billion in 2032 ")
)

p + 
  geom_label(data = annotation, aes(x=x, y=y, label=label), size = 3) + 
  labs(title = "Forecasted Expenditures", 
  caption = "Projected values at 95% confidence interval. 
  Dark blue represents 80% liklihood of falling with that range, 
  light blue represents 95% liklihood of being in projected range.")



## Exp and Rev together

autoplot(tsexp) +
  #geom_line(tsexp)+
  #geom_line(aes(model_rev))+
  autolayer(forecast_rev, series = "Revenue") +
  autolayer(forecast_exp, series = "Expenditure)", alpha = 0.5) +
  geom_line(year_totals, mapping= aes(x = Year, y = Revenue))  + guides(colour = guide_legend("Forecast")) + 
  labs(title = "Forecasted Revenue and Expenditures", caption = "Revenue includes State and Local CURE Dollars")

Revenue forecasting using precovid trends:

Graphing the 3 federal revenue types together may be the most reliable since some COVID funding is still recorded in Federal Other and some are in other categories (like Disaster Response in FY2021). Need to look at more before using.

Tables with Totals

exp_totals <- ff_exp %>% rowwise() %>% mutate(exp_TOTALS = sum(across(exp_402:exp_970)))
rev_totals <- ff_rev %>%    rowwise() %>% 
  mutate(rev_TOTALS = sum(across(rev_02:rev_78)))

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"

  ) ) %>% 
  select(-type, -Category) %>%  # drop extra columns type and Category number
  group_by(Year, Category_name) %>%
  summarise(Dollars= round(sum(Dollars),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())

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 == "444" ~ "Human Services" ,
           Category == "478" ~ "HEALTHCARE & FAM SER NET OF MEDICAID", 
            Category == "482" ~ "PUBLIC HEALTH", 
            Category == "492" ~ "REVENUE", 
             Category == "494" ~ "Transportation" ,
             Category == "532" ~ "ENVIRONMENTAL PROTECT AGENCY" ,
             Category == "557" ~ "Tollway" ,
             Category == "684" ~ "IL COMMUNITY COLLEGE BOARD", 
             Category == "691" ~ "IL STUDENT ASSISTANCE COMM" ,
             Category == "900" ~ "NOT IN FRAME",
             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 Revenue Sharing",
          Category == "TOTALS" ~ "Total") #,T ~ "All Other Expenditures **")
           ) %>% 
  select(-type, -Category) %>% 
  group_by(Year, Category_name) %>% 
  summarise(Dollars= round(sum(Dollars),digits=2)) %>% 
  mutate(Category_name = str_to_title(Category_name))

expenditure_wide2 <- exp_long%>% 
  pivot_wider(names_from = Category_name, 
              values_from = Dollars) %>%
  #relocate("All Other Expenditures **", .after = last_col()) %>%
  relocate("Total", .after =  last_col())

# function for calculating the CAGR
calc_cagr <- function(df, n) {
  df <- exp_long %>%
    #select(-type) %>%
    arrange(Category_name, Year) %>%
    group_by(Category_name) %>%
    mutate(cagr = ((`Dollars` / lag(`Dollars`, n)) ^ (1 / n)) - 1)

  return(df)
}

# This works for one variable at a time
cagr_24 <- calc_cagr(exp_long, 24) %>% 
  # group_by(Category) %>%
  summarize(cagr_24 = round(sum(cagr*100, na.rm = TRUE), 2))

cagr23_precovid <- exp_long %>%
  filter(Year <= 2019) %>%
  calc_cagr(21) %>% 
  summarize(cagr_21 = round(sum(cagr*100, na.rm = TRUE), 2))



cagr_10 <- calc_cagr(exp_long, 10) %>% 
  filter(Year == 2022) %>%
  summarize(cagr_10 = case_when(Year == 2022 ~ round(sum(cagr*100, na.rm = TRUE), 2)))

cagr_5 <- calc_cagr(exp_long, 5) %>% 
  filter(Year == 2022) %>%
  summarize(cagr_5 = case_when(Year == 2022 ~ round(sum(cagr*100, na.rm = TRUE), 2)))

cagr_3 <- calc_cagr(exp_long, 3) %>% 
  filter(Year == 2022) %>%
  summarize(cagr_3 = case_when(Year == 2022 ~ round(sum(cagr*100, na.rm = TRUE), 2)))

cagr_2 <- calc_cagr(exp_long, 2) %>% 
  filter(Year == 2022) %>%
  summarize(cagr_2 = case_when(Year == 2022 ~ round(sum(cagr*100, na.rm = TRUE), 2)))

cagr_1 <- calc_cagr(exp_long, 1) %>% 
  filter(Year == 2022) %>%
  summarize(cagr_1 = case_when(Year == 2022 ~ round(sum(cagr*100, na.rm = TRUE), 2)))

CAGR_expenditures_summary_tot <- data.frame(cagr_1, cagr_2, cagr_3, cagr_5, cagr_10, cagr_24 ) %>% 
  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,"24 Year CAGR" = cagr_24 )

move_to_last <- function(df, n) df[c(setdiff(seq_len(nrow(df)), n), n), ]

CAGR_expenditures_summary_tot <- move_to_last(CAGR_expenditures_summary_tot, 29) 

#CAGR_expenditures_summary_tot <-   select(CAGR_expenditures_summary_tot, -1) 

CAGR_expenditures_summary_tot%>%   
  kbl(caption = "CAGR Calculations for Expenditure Categories" , row.names=FALSE) %>% 
     kable_classic()

calc_cagr <- function(df, n) {
  df <- rev_long %>%
    arrange(Category_name, Year) %>%
    group_by(Category_name) %>%
    mutate(cagr = ((Dollars / lag(Dollars, n)) ^ (1 / n)) - 1)

  return(df)
}

# This works for one variable at a time
cagr_24 <- calc_cagr(rev_long, 24) %>% 
     # group_by(Category) %>%
  summarize(cagr_24 = round(sum(cagr*100, na.rm = TRUE), 2))

cagr_10 <- calc_cagr(rev_long, 10) %>% 
  filter(Year == 2022) %>%
  summarize(cagr_10 = case_when(Year == 2022 ~ round(sum(cagr*100, na.rm = TRUE), 2)))

cagr_5 <- calc_cagr(rev_long, 5) %>% 
  filter(Year == 2022) %>%
  summarize(cagr_5 = case_when(Year == 2022 ~ round(sum(cagr*100, na.rm = TRUE), 2)))

cagr_3 <- calc_cagr(rev_long, 3) %>% 
  filter(Year == 2022) %>%
  summarize(cagr_3 = case_when(Year == 2022 ~ round(sum(cagr*100, na.rm = TRUE), 2)))

cagr_2 <- calc_cagr(rev_long, 2) %>% 
  filter(Year == 2022) %>%
  summarize(cagr_2 = case_when(Year == 2022 ~ round(sum(cagr*100, na.rm = TRUE), 2)))

 cagr_1 <- calc_cagr(rev_long, 1) %>% 
  filter(Year == 2022) %>%
  summarize(cagr_1 = case_when(Year == 2022 ~ round(sum(cagr*100, na.rm = TRUE), 2)))

CAGR_revenue_summary_tot <- data.frame(cagr_1, cagr_2, cagr_3, cagr_5, cagr_10, cagr_24) %>%   
  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,"24 Year CAGR" = cagr_24 )

CAGR_revenue_summary_tot <- move_to_last(CAGR_revenue_summary_tot,1)
CAGR_revenue_summary_tot <- move_to_last(CAGR_revenue_summary_tot,22)

CAGR_revenue_summary_tot %>% 
  kbl(caption = "CAGR Calculations for Revenue Sources", row.names = FALSE) %>% 
     kable_classic()

rm(cagr_1, cagr_2, cagr_3, cagr_5, cagr_10, cagr_24)

revenue_change2 <- rev_long %>%
  #select(-c(Category)) %>%
  filter(Year > 2020) %>%
  pivot_wider(names_from = Year , values_from = Dollars,   names_prefix = "Dollars_") %>%
  mutate(
    "FY 2022 ($ billions)" = round(Dollars_2022/1000, digits = 1),
        "FY 2021 ($ billions)" = round(Dollars_2021/1000, digits = 1),

#    "Change from 2021 to 2022" = round(Dollars_2022 - Dollars_2021, digits = 2),
         "1-Year Change" = round(((Dollars_2022 -Dollars_2021)/Dollars_2021*100), digits = 2)) %>%
  left_join(CAGR_revenue_summary_tot, by = c("Category_name" = "Revenue Category")) %>% 
    arrange(-`FY 2022 ($ billions)`)%>%
  #select(-c(Dollars_2021, Dollars_2021, `1 Year CAGR`:`10 Year CAGR`)) %>%
  rename( "24-Year CAGR" = `24 Year CAGR`, 
          "Revenue Category" = Category_name ) %>%
  select(-c(Dollars_2021, Dollars_2022, `1 Year CAGR`:`10 Year CAGR`)) 


revenue_change2 <- move_to_last(revenue_change2,8)
revenue_change2 <- move_to_last(revenue_change2,1)

revenue_change2 %>% 
  kbl(caption = "Yearly Change in Revenue", row.names = FALSE) %>% 
   kable_classic() %>%
    row_spec(23, bold = T, color = "black", background = "gray")



expenditure_change2 <- exp_long %>%
  #select(-c(type,Category)) %>%
  filter(Year > 2020) %>%
  pivot_wider(names_from = Year , values_from = Dollars,   names_prefix = "Dollars_") %>%
  mutate(
    "FY 2022 ($ billions)" = round(Dollars_2022/1000, digits = 1),
    "FY 2021 ($ billions)" = round(Dollars_2021/1000, digits = 1),

  #  "Change from 2021 to 2022" = Dollars_2022 - Dollars_2021,
         "1-Year Change" = round((Dollars_2022 -Dollars_2021)/Dollars_2021*100, digits = 2) )%>%
  left_join(CAGR_expenditures_summary_tot, by = c("Category_name" = "Expenditure Category")) %>% 
  arrange(-`FY 2022 ($ billions)`)%>%
  select(-c(Dollars_2022, Dollars_2021, `1 Year CAGR`:`10 Year CAGR`)) %>%
  rename( "24-Year CAGR" = `24 Year CAGR`, 
          "FY2022 Expenditure Category" = Category_name )

expenditure_change2 <- move_to_last(expenditure_change2, 1)

expenditure_change2 %>% 
  kbl(caption = "Yearly Change in Expenditures", row.names = FALSE) %>% 
  kable_classic() %>%
    row_spec(31, bold = T, color = "black", background = "gray")

#install.packages("openxlsx")
library(openxlsx)

dataset_names <- list('rev_long' = rev_long, 'exp_long' = exp_long, 
                      `Table 1` = expenditure_change2, `Table 2` = revenue_change2,
                      'Table 4.a' = CAGR_revenue_summary_tot, 'Table 4.b' = CAGR_expenditures_summary_tot, 
                      'year_totals' = year_totals)

write.xlsx(dataset_names, file = 'summary_file_FY2022_withTotals.xlsx')

Export summary file with Totals

dataset_names <- list('Aggregate Revenues' = revenue_wide2, 
                      'Aggregate Expenditures' = expenditure_wide2, 

                      
                      'Table 1' = expenditure_change2, #Top categories with yearly change, 23 yr cagr
                      'Table 2' = revenue_change2,
                      
                     # 'Table 4.a' = CAGR_revenue_summary_majorcats, # Categories Match Table 1 in paper
                     # 'Table 4.b' = CAGR_expenditures_summary_majorcats, 
                                            
                     # 'Table 1-AllCats' = expenditure_change_allcats,  # All Categories by Year
                    #  'Table 2-AllCats' = revenue_change_allcats,
                      
                      'Table 4.a-AllCats' = CAGR_revenue_summary_tot, 
                      'Table 4.b-AllCats' = CAGR_expenditures_summary_tot, 
                      
                      'year_totals' = year_totals,    # Total Revenue, Expenditure, and Fiscal gap per year
                      
                      'aggregated_totals_long' = aggregated_totals_long # all data in long format. Good for creating pivot tables in Excel
                      )

write.xlsx(dataset_names, file = 'summary_file_FY22_wTotals.xlsx')

Summary Tables - Largest Categories

The 10 largest revenue sources and 13 largest expenditure sources remain separate categories and all other smaller sources/expenditures are combined into “All Other _____”. 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.

• take ff_rev and ff_exp data frames, which were in wide format, pivot them longer and mutate the Category_name variable to nicer labels. Keep largest categories separate and aggregate the rest.

exp_totals <- ff_exp %>% rowwise() %>% mutate(exp_TOTALS = sum(across(exp_402:exp_970))) # creates total column too

rev_totals <- ff_rev %>% rowwise() %>% 
  mutate(rev_TOTALS = sum(across(rev_02:rev_78)))

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" ~ "Income Tax" ,
    Category == "03" ~ "Corporate Income Tax" ,
    Category == "06" ~ "Sales Tax" ,
    Category == "09" ~ "Motor Fuel Taxes" ,
 #   Category == "12" ~ "PUBLIC UTILITY TAXES, gross of PPRT" ,
  #  Category == "15" ~ "CIGARETTE TAXES" ,
 #   Category == "18" ~ "LIQUOR GALLONAGE TAXES" ,
 #  Category == "21" ~ "INHERITANCE TAX" ,
  #  Category == "24" ~ "INSURANCE TAXES&FEES&LICENSES, net of refunds " ,
   # 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, Registration" ,
   # 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 Reimbursements", 
    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 == "78new" ~  "ALL OTHER SOURCES" ,
   # Category == "79" ~   "COOK COUNTY IGT", #dropped
 #   Category == "98" ~  "PRIOR YEAR REFUNDS", #dropped
                
Category == "TOTALS" ~ "Total Revenue",
T ~ "All Other Sources **" # any other Category number that was not specifically referenced is cobined into Other Revenue Sources

  ) ) %>% 
  select(-type, -Category) %>%  # drop extra columns type and Category number
  group_by(Year, Category_name) %>%
  summarise(Dollars= round(sum(Dollars),digits=2)) 

# revenue_wide # not actually in wide format yet. 
# has 10 largest rev sources separate and combined all others to Other in long data format. 


# 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("All Other Sources **", .after = last_col()) %>%
  relocate("Total Revenue", .after =  last_col())


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 == "444" ~ "Human Services" ,
           #  Category == "478" ~ "HEALTHCARE & FAM SER NET OF MEDICAID", 
           #  Category == "482" ~ "PUBLIC HEALTH", 
           #  Category == "492" ~ "REVENUE", 
             Category == "494" ~ "Transportation" ,
           #  Category == "532" ~ "ENVIRONMENTAL PROTECT AGENCY" ,
             Category == "557" ~ "Tollway" ,
           #  Category == "684" ~ "IL COMMUNITY COLLEGE BOARD", 
            # Category == "691" ~ "IL STUDENT ASSISTANCE COMM" ,
           #  Category == "900" ~ "NOT IN FRAME",
             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 Revenue Sharing",
          Category == "TOTALS" ~ "Total Expenditures",
             T ~ "All Other Expenditures **")
           ) %>% 
  select(-type, -Category) %>% 
  group_by(Year, Category_name) %>% 
  summarise(Dollars= round(sum(Dollars),digits=2))

expenditure_wide2 <- exp_long%>% 
  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

# function for calculating the CAGR
calc_cagr <- function(df, n) {
  df <- exp_long %>%
    #select(-type) %>%
    arrange(Category_name, Year) %>%
    group_by(Category_name) %>%
    mutate(cagr = ((`Dollars` / lag(`Dollars`, n)) ^ (1 / n)) - 1)

  return(df)
}

# This works for one variable at a time
cagr_24 <- calc_cagr(exp_long, 24) %>% 
  # group_by(Category) %>%
  summarize(cagr_24 = round(sum(cagr*100, na.rm = TRUE), 2))

cagr23_precovid <- exp_long %>%
  filter(Year <= 2019) %>%
  calc_cagr(21) %>% 
  summarize(cagr_21 = round(sum(cagr*100, na.rm = TRUE), 2))



cagr_10 <- calc_cagr(exp_long, 10) %>% 
  filter(Year == 2022) %>%
  summarize(cagr_10 = case_when(Year == 2022 ~ round(sum(cagr*100, na.rm = TRUE), 2)))

cagr_5 <- calc_cagr(exp_long, 5) %>% 
  filter(Year == 2022) %>%
  summarize(cagr_5 = case_when(Year == 2022 ~ round(sum(cagr*100, na.rm = TRUE), 2)))

cagr_3 <- calc_cagr(exp_long, 3) %>% 
  filter(Year == 2022) %>%
  summarize(cagr_3 = case_when(Year == 2022 ~ round(sum(cagr*100, na.rm = TRUE), 2)))

cagr_2 <- calc_cagr(exp_long, 2) %>% 
  filter(Year == 2022) %>%
  summarize(cagr_2 = case_when(Year == 2022 ~ round(sum(cagr*100, na.rm = TRUE), 2)))

cagr_1 <- calc_cagr(exp_long, 1) %>% 
  filter(Year == 2022) %>%
  summarize(cagr_1 = case_when(Year == 2022 ~ round(sum(cagr*100, na.rm = TRUE), 2)))

CAGR_expenditures_majorcats_tot <- data.frame(cagr_1, cagr_2, cagr_3, cagr_5, cagr_10, cagr_24 ) %>% 
  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,"24 Year CAGR" = cagr_24 )

move_to_last <- function(df, n) df[c(setdiff(seq_len(nrow(df)), n), n), ]

CAGR_expenditures_majorcats_tot <- move_to_last(CAGR_expenditures_majorcats_tot, 1)
CAGR_expenditures_majorcats_tot <- move_to_last(CAGR_expenditures_majorcats_tot, 13) 


CAGR_expenditures_majorcats_tot%>%   
  kbl(caption = "CAGR Calculations for Largest Expenditure Categories" , row.names=FALSE) %>% 
     kable_classic() 


# Yearly change for Top 13 largest expenditure categories
expenditure_change2 <- exp_long %>%
  filter(Year > 2020) %>%
  pivot_wider(names_from = Year , values_from = Dollars,   names_prefix = "Dollars_") %>%
  mutate("FY 2022 ($ Billions)" = round(Dollars_2022/1000, digits = 1),
         "FY 2021 ($ Billions)" = round(Dollars_2021/1000, digits = 1),
         "1-Year Change" = percent((Dollars_2022 -Dollars_2021)/Dollars_2021, accuracy = .1) )  %>%
  left_join(CAGR_expenditures_majorcats_tot, by = c("Category_name" = "Expenditure Category")) %>% 
  arrange(-`FY 2022 ($ Billions)`)%>%
  mutate(`24 Year CAGR` = percent(`24 Year CAGR`/100, accuracy=.1)) %>%
  select(-c(Dollars_2022, Dollars_2021, `1 Year CAGR`:`10 Year CAGR`)) %>%
  rename( "24-Year CAGR" = `24 Year CAGR`, 
          "Expenditure Category" = Category_name )

expenditure_change2 <- move_to_last(expenditure_change2, 3) 

expenditure_change2 <- move_to_last(expenditure_change2, 1)

expenditure_change2 %>% 
  kbl(caption = "Yearly Change in Expenditures", row.names = FALSE, align = "l") %>% 
  kable_classic() %>%
    row_spec(15, bold = T, color = "black", background = "gray")

Top 10 revenue sources CAGRs and Yearly Change Tables:

##### Top 10 revenue CAGRs: ####


calc_cagr <- function(df, n) {
  df <- rev_long %>%
    arrange(Category_name, Year) %>%
    group_by(Category_name) %>%
    mutate(cagr = ((Dollars / lag(Dollars, n)) ^ (1 / n)) - 1)

  return(df)
}

# This works for one variable at a time
cagr_24 <- calc_cagr(rev_long, 24) %>% 
     # group_by(Category) %>%
  summarize(cagr_24 = round(sum(cagr*100, na.rm = TRUE), 2))

cagr_10 <- calc_cagr(rev_long, 10) %>% 
  filter(Year == 2022) %>%
  summarize(cagr_10 = case_when(Year == 2022 ~ round(sum(cagr*100, na.rm = TRUE), 2)))

cagr_5 <- calc_cagr(rev_long, 5) %>% 
  filter(Year == 2022) %>%
  summarize(cagr_5 = case_when(Year == 2022 ~ round(sum(cagr*100, na.rm = TRUE), 2)))

cagr_3 <- calc_cagr(rev_long, 3) %>% 
  filter(Year == 2022) %>%
  summarize(cagr_3 = case_when(Year == 2022 ~ round(sum(cagr*100, na.rm = TRUE), 2)))

cagr_2 <- calc_cagr(rev_long, 2) %>% 
  filter(Year == 2022) %>%
  summarize(cagr_2 = case_when(Year == 2022 ~ round(sum(cagr*100, na.rm = TRUE), 2)))

 cagr_1 <- calc_cagr(rev_long, 1) %>% 
  filter(Year == 2022) %>%
  summarize(cagr_1 = case_when(Year == 2022 ~ round(sum(cagr*100, na.rm = TRUE), 2)))

CAGR_revenue_majorcats_tot <- data.frame(cagr_1, cagr_2, cagr_3, cagr_5, cagr_10, cagr_24) %>%   
  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,"24 Year CAGR" = cagr_24 )

CAGR_revenue_majorcats_tot <- move_to_last(CAGR_revenue_majorcats_tot,1)
CAGR_revenue_majorcats_tot <- move_to_last(CAGR_revenue_majorcats_tot,11)

CAGR_revenue_majorcats_tot %>% 
  kbl(caption = "CAGR Calculations for Revenue Sources", row.names = FALSE) %>% 
     kable_classic() 


###### Yearly change summary table for Top 10 Revenues #####
revenue_change2 <- rev_long %>%
  #select(-c(Category)) %>%
  filter(Year > 2020) %>%
  pivot_wider(names_from = Year , values_from = Dollars,   names_prefix = "Dollars_") %>%
  mutate(
    "FY 2022 ($ billions)" = round(Dollars_2022/1000, digits = 1),
            "FY 2021 ($ billions)" = round(Dollars_2021/1000, digits = 1),

         "1-Year Change" = percent(((Dollars_2022 -Dollars_2021)/Dollars_2021), accuracy = .1)) %>%
  left_join(CAGR_revenue_majorcats_tot, by = c("Category_name" = "Revenue Category")) %>% 
    arrange(-`FY 2022 ($ billions)`)%>%
  #select(-c(Dollars_2021, Dollars_2021, `1 Year CAGR`:`10 Year CAGR`)) %>%
  mutate("24-Year Change" = percent(`24 Year CAGR`/100, accuracy=.1)) %>%
  rename("Revenue Category" = Category_name ) %>%
  select(-c(Dollars_2022, Dollars_2021, `1 Year CAGR`:`24 Year CAGR`)) 

revenue_change2 <- move_to_last(revenue_change2,5)

revenue_change2 <- move_to_last(revenue_change2,1)

revenue_change2%>% 
  kbl(caption = "Yearly Change in Revenue without Federal Stimulus Packages", row.names = FALSE, align = "l") %>% 
   kable_classic() %>%
    row_spec(12, bold = T, color = "black", background = "gray")

Export Summary Files

Saves main items in one excel file named summary_file.xlsx. Delete eval=FALSE to run on local computer.

#install.packages("openxlsx")
library(openxlsx)

dataset_names <- list('Aggregate Revenues' = revenue_wide2, # Top Categories aggregated, nice labels
                      'Aggregate Expenditures' = expenditure_wide2, 

                      
                      'Table 1' = expenditure_change2, #Top categories with yearly change, 23 yr cagr
                      'Table 2' = revenue_change2,
                      
                      'Table 4.a' = CAGR_revenue_summary_majorcats, # Categories Match Table 1 in paper
                      'Table 4.b' = CAGR_expenditures_summary_majorcats, 
                                            
                      'Table 1-AllCats' = expenditure_change_allcats,  # All Categories by Year
                      'Table 2-AllCats' = revenue_change_allcats,
                      
                      'Table 4.a-AllCats' = CAGR_revenue_summary_allcats, 
                      'Table 4.b-AllCats' = CAGR_expenditures_summary_allcats, 
                      
                      'year_totals' = year_totals,    # Total Revenue, Expenditure, and Fiscal gap per year
                      
                      'aggregated_totals_long' = aggregated_totals_long # all data in long format. Good for creating pivot tables in Excel
                      )

write.xlsx(dataset_names, file = 'summary_file_FY22_MajorCats_WithTotals.xlsx')