library(tidyverse)
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(cmapplot)
theme_set(theme_classic() )
knitr::opts_chunk$set(echo = TRUE, warning = FALSE, message = FALSE)
exp_temp <- read_csv("./data/exp_temp.csv")
rev_temp <- read_csv("./data/rev_temp.csv")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).
tax_refund_long <- exp_temp %>% # fund != "0401" # removes State Trust Funds
filter(fund != "0401" &
(object == "9900" | object=="9910"|object=="9921"|object=="9923"|object=="9925")) %>%
# keeps these objects which represent revenue, insurance, treasurer,and financial and professional reg tax refunds
mutate(refund = case_when(
object == "9900" ~ "FY23_Rebates",
fund=="0278" & sequence == "00" ~ "02", # for income tax refund
fund=="0278" & sequence == "01" ~ "03", # tax administration and enforcement and tax operations become corporate income tax refund
fund == "0278" & sequence == "02" ~ "02",
object=="9921" ~ "21", # inheritance tax and estate tax refund appropriation
object=="9923" ~ "09", # motor fuel tax refunds
obj_seq_type == "99250055" ~ "06", # sales tax refund
fund=="0378" & object=="9925" ~ "24", # insurance privilege tax refund
(fund=="0001" & object=="9925") | (object=="9925" & fund == "0384" & fy == 2023) ~ "35", # all other taxes
# fund=="0001" & object=="9925" ~ "35", # all other taxes
T ~ "CHECK")) # if none of the items above apply to the observations, then code them as CHECK
exp_temp <- left_join(exp_temp, tax_refund_long) %>%
mutate(refund = ifelse(is.na(refund),"not refund", as.character(refund)))
tax_refund <- tax_refund_long %>%
group_by(refund, fy)%>%
summarize(refund_amount = sum(expenditure, na.rm = TRUE)/1000000) %>%
pivot_wider(names_from = refund, values_from = refund_amount, names_prefix = "ref_") %>%
mutate_all(~replace_na(.,0)) %>%
arrange(fy)
tax_refund %>%
pivot_longer(c(ref_02:ref_35, ref_FY23_Rebates), names_to = "Refund Type", values_to = "Amount") %>%
ggplot()+
theme_classic()+
geom_line(aes(x=fy,y=Amount, group = `Refund Type`, color = `Refund Type`))+
labs(title = "Refund Types",
caption = "Refunds are excluded from Expenditure totals and instead subtracted from Revenue totals") +
labs(title = "Tax refunds",
caption = "Rev_type codes: 02=income taxes, 03=corporate income taxes, 06=sales tax, 09=motor fuel tax,
24=insurance taxes and fees, 35 = all other tax refunds." )
# remove the items we recoded in tax_refund_long
# exp_temp <- exp_temp %>% filter(refund == "not refund")
# manually adds the abatements as expenditure item and keeps on expenditure side.
# otherwise ignored since it is in fund 0278 and
exp_temp <- exp_temp %>%
mutate(in_ff = ifelse(object == 9900, 1, in_ff))State pension contributions are largely captured with object=4431. (State payments into pension fund). State payments to the following pension systems:
Modify exp_temp and move all state pension contributions to their own group (901).
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
237827 242 6 15 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)) +
theme_classic()+
geom_col(aes(fill = pension)) +
labs (title = "Pension expenditures",
caption = "1 = State contributions INTO pension funds.
3 = Purchase of Investments anomoly in 2010 and 2011.
4 = pension stabilization fund")+
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
237827 240 15 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() +
theme_classic()+
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() +
theme_classic()+
labs (title = "Pension Expenditures")
Drop all cash transfers between funds, statutory transfers, and purchases of investments from expenditure data.
transfers_drop <- exp_temp %>% filter(
agency == "799" | # statutory transfers
object == "1993" | # interfund cash transfers
object == "1298") # purchase of investments
transfers_drop # items being dropped, # always check to make sure you aren't accidently dropping something of interest.
exp_temp <- anti_join(exp_temp, transfers_drop)
exp_tempCoding healthcare costs was quite difficult. Over the years, State employee healthcare has been within Central Management Bureau of Benefits and Healthcare & Family Services.
If observation is a group insurance contribution, then the expenditure amount is set to $0 (essentially dropped from analysis).
Agency 416 had group insurance contributions for 1998-2005 and 2013-present. Agency 478 had group insurance contributions from 2006-2012.
FY2021 and FY2022 contributions coded with object = 1900 (lump sum) for some reason??
#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_costsexp_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)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:
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.
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")
transfers_long %>%
group_by(agency_name, group, fy) %>%
summarize(expenditure = sum(expenditure, na.rm=TRUE) )%>%
ggplot() +
geom_line(aes(x=fy, y = expenditure, color=agency_name)) +
theme_classic()+
theme(legend.position = "bottom", legend.title=element_blank())+
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 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. 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).
State CURE 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.
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, CFS, HFS, human services, public health
T ~ as.character(group))
) %>%
mutate(group = case_when(
agency=="478" & (appr_org=="01" | appr_org == "65" | appr_org=="88") & (object=="4900" | object=="4400") ~ "945", # separates CHIP from health and human services and saves it as Medicaid
agency == "586" & fund == "0355" ~ "945", # 586 (Board of Edu) has special education which is part of medicaid
# OLD CODE: agency == "586" & appr_org == "18" ~ "945", # Spec. Edu Medicaid Matching
agency=="425" | agency=="466" | agency=="546" | agency=="569" | agency=="578" | agency=="583" | agency=="591" | agency=="592" | agency=="493" | agency=="588" ~ "941", # public safety & Corrections
agency=="420" | agency=="494" | agency=="406" | agency=="557" ~ as.character(agency), # econ devt & infra, tollway
agency=="511" | agency=="554" | agency=="574" | agency=="598" ~ "946", # Capital improvement
agency=="422" | agency=="532" ~ as.character(agency), # environment & nat. resources
agency=="440" | agency=="446" | agency=="524" | agency=="563" ~ "944", # business regulation
agency=="492" ~ "492", # revenue
agency == "416" ~ "416", # central management services
agency=="448" & fy > 2016 ~ "416", #add DoIT to central management
T ~ as.character(group))) %>%
mutate(group = case_when(
# agency=="684" | agency=="691" ~ as.character(agency), # moved under higher education in next line. 11/28/2022 AWM
agency=="692" | agency=="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, "data/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 how individual items have been coded before they are aggregated into larger categories.
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!
# 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)))) For an deeper look at federal revenue to Illinois, ?sec-covid-federal-funds.
#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_recessions(xformay = "numeric",text = FALSE, update_recessions = recessions)+
geom_line(aes(x=fy, y=receipts,color=rev_type_name)) +
theme_classic() +
scale_y_continuous(labels = comma)+
labs(title = "Federal to State Transfers",
y = "Millions of Dollars", x = "") +
theme(legend.position = "bottom", legend.title = element_blank() )
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.
NOTE: The code chunk below only drops revenue sources with the source name of “Federal Stimulus Package” (which is the State and Local CURE revenue). Additional federal money went into other funds during the beginning of pandemic. Many departments saw increased grants and received other funds (e.g. funds)
rev_temp <- rev_temp %>% mutate(covid_dollars = ifelse(source_name_AWM == "FEDERAL STIMULUS PACKAGE",1,0))
rev_temp %>% filter(source_name_AWM == "FEDERAL STIMULUS PACKAGE") %>%
group_by(fy) %>% summarize(Received = sum(receipts))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:
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# revenue types to drop
drop_type <- c("32", "45", "51",
"66", "72", "75", "76", "79", "98", "99")
# drops Blank, Student Fees, Retirement contributions, proceeds/investments,
# bond issue proceeds, interagency receipts, cook IGT, Prior year refunds.
rev_temp <- rev_temp %>% filter(!rev_type_new %in% drop_type)
# keep observations that do not have a revenue type mentioned in drop_type
table(rev_temp$rev_type_new)
02 03 06 09 12 15 18 21 24 27 30 31 33
177 132 852 134 590 269 47 1421 473 79 665 130 141
35 36 39 42 48 54 57 58 59 60 63 78
691 5328 9487 2939 32 1283 6679 646 233 105 5321 11712 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_") # 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(rev_temp, "data/all_revenue_recoded.csv")
write_csv(exp_temp, "data/exp_fy23_recoded_02April2024.csv")
write_csv(rev_temp, "data/rev_fy23_recoded_02April2024.csv")ff_rev <- rev_temp %>%
group_by(rev_type_new, fy) %>%
summarize(sum_receipts = sum(receipts, na.rm=TRUE)/1000000 ) %>%
pivot_wider(names_from = "rev_type_new", values_from = "sum_receipts", names_prefix = "rev_")
# ff_rev<- left_join(ff_rev, tax_refund)
#ff_rev <- left_join(ff_rev, pension2_fy22, by=c("fy" = "year"))
#ff_rev <- left_join(ff_rev, eehc2_amt)
ff_rev <- mutate_all(ff_rev, ~replace_na(.,0))
#
# ff_rev <- ff_rev %>%
# mutate(rev_02 = rev_02 - ref_02,
# rev_03 = rev_03 - ref_03,
# rev_06 = rev_06 - ref_06,
# rev_09 = rev_09 - ref_09,
# rev_21 = rev_21 - ref_21,
# rev_24 = rev_24 - ref_24,
# rev_35 = rev_35 - ref_35
#
# # rev_78new = rev_78 #+ pension_amt #+ eehc
# ) %>%
# select(-c(ref_02:ref_35, rev_99, rev_NA, rev_76
# #, ref_CHECK#, pension_amt , rev_76,
# # , eehc
# ))
#
# ff_rev
#noproblem <- c(0) # if ref_CHECK = $0, then there is no problem. :)
#
# if((sum(ff_rev$ref_CHECK) == 0 )){
#
# ff_rev <- ff_rev %>%
#
# mutate(rev_02 = rev_02 - ref_02,
# rev_03 = rev_03 - ref_03,
# rev_06 = rev_06 - ref_06,
# rev_09 = rev_09 - ref_09,
# rev_21 = rev_21 - ref_21,
# rev_24 = rev_24 - ref_24,
# rev_35 = rev_35 - ref_35
# ) %>%
# select(-c(ref_02:ref_35, rev_99, rev_76, ref_CHECK ))
# }else{"You have a problem! Check what revenue items did not have rev codes (causing it to be coded as rev_NA) or the check if there were refunds that were not assigned revenue codes (tax_refunds_long objects)"}
ff_rev %>% mutate_all(., ~round(.,digits=0))Since I already pivot_wider()ed the table in the previous code chunk, I now change each column’s name by using rename() to set new variable names. Ideally the final dataframe would have both the variable name and the variable label but I have not done that yet.
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 %>% mutate_all(., ~round(., digits = 0))Create exp_970 for all local government transfers (exp_971 + exp_972 + exp_975 + exp_976).
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(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 # not labeled# exp_temp %>%
# group_by(year, group) %>%
# summarize(sum_expenditure = round(sum(expenditure)/1000000)) %>%
# arrange(year) %>%
# pivot_wider(names_from = "group", values_from = "sum_expenditure")
#
aggregate_exp_labeled <- exp_temp %>%
group_by(year, group_name) %>%
summarize(sum_expenditure = sum(expenditure)/1000000) %>%
arrange(year) %>%
pivot_wider(names_from = "group_name", values_from = "sum_expenditure")
aggregate_exp_labeled %>% mutate_all(., ~round(., digits = 0))Create total revenues and total expenditures only:
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.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))
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, "data/expenditures_recoded_long_FY23.csv")
#write_csv(rev_long, "data/revenue_recoded_long_FY23.csv")
# combine revenue and expenditures into one data frame
aggregated_totals_long <- rbind(rev_long, exp_long)
aggregated_totals_long %>% mutate(`Dollars (Millions)` = round(Dollars, digits = 0)) %>% select(-Dollars) %>%
select(Year, Category_name, `Dollars (Millions)`, type, Category)
write_csv(aggregated_totals_long, "data/aggregated_totals.csv")year_totals <- aggregated_totals_long %>%
group_by(type, Year) %>%
summarize(Dollars = sum(Dollars, na.rm = TRUE)) %>%
pivot_wider(names_from = "type", values_from = Dollars) %>%
rename(Expenditures = exp,
Revenue = rev) %>%
mutate(`Fiscal Gap` = Revenue - Expenditures)
# %>% arrange(desc(Year))
# creates variable for the Gap each year
year_totals %>%
mutate_all(., ~round(., digits = 0)) %>%
kbl(caption = "Fiscal Gap for each Fiscal Year ($ Millions)") %>%
kable_styling(bootstrap_options = c("striped")) %>%
kable_classic() %>%
add_footnote(c("Values include State CURE dollars (SLFRF)"))| Year | Expenditures | Revenue | Fiscal Gap |
|---|---|---|---|
| 1998 | 31243 | 32030 | 787 |
| 1999 | 33846 | 33966 | 119 |
| 2000 | 37343 | 37051 | -292 |
| 2001 | 40360 | 38286 | -2074 |
| 2002 | 42068 | 37922 | -4145 |
| 2003 | 42613 | 38453 | -4160 |
| 2004 | 53026 | 42612 | -10414 |
| 2005 | 45362 | 44306 | -1056 |
| 2006 | 48062 | 46170 | -1892 |
| 2007 | 51131 | 49494 | -1637 |
| 2008 | 54173 | 51643 | -2530 |
| 2009 | 56751 | 51466 | -5285 |
| 2010 | 59270 | 51197 | -8073 |
| 2011 | 60423 | 56303 | -4120 |
| 2012 | 59864 | 58422 | -1442 |
| 2013 | 63287 | 63102 | -185 |
| 2014 | 66966 | 65269 | -1697 |
| 2015 | 69941 | 66590 | -3351 |
| 2016 | 63931 | 64155 | 223 |
| 2017 | 71728 | 63660 | -8068 |
| 2018 | 74973 | 73015 | -1958 |
| 2019 | 74406 | 74638 | 232 |
| 2020 | 81606 | 80589 | -1017 |
| 2021 | 92888 | 95206 | 2318 |
| 2022 | 100069 | 116061 | 15992 |
| 2023 | 111974 | 111774 | -200 |
| a Values include State CURE dollars (SLFRF) |
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")
)
annotation_nums <- data.frame(
x = c(2023, 2022, 2023),
y = c(95, 120, -5),
label = c("Exp = $112.0","Rev = $111.8", "-$0.2")
)
## Dashed line versions for expenditures:
library(cmapplot)
fiscal_gap <-
ggplot(data = year_totals, aes(x=Year, y = Revenue/1000)) +
geom_recessions(text = FALSE, update = recessions)+
# geom_smooth adds regression line, graphed first so it appears behind line graph
geom_smooth(aes(x = Year, y = Revenue/1000), color = "gray", alpha = 0.7, method = "lm", se = FALSE) +
# scale_linetype_manual(values="dashed")+
geom_smooth(aes(x = Year, y = Expenditures/1000), color ="rosybrown2", linetype = "dotted", method = "lm", se = FALSE, alpha = 0.7) +
# line graph of revenue and expenditures
geom_line(aes(x = Year, y = Revenue/1000), color = "Black", size=1) +
geom_line(aes(x = Year, y = Expenditures/1000, linetype = "dashed"), color = "red", lwd=1) +
geom_line(aes(x = Year, y = (`Fiscal Gap`/1000)), color = "darkgray", lwd = 1) +
geom_hline(yintercept = 0) +
geom_text(data = annotation, aes(x=x, y=y, label=label))+
# labels
theme_classic() +
theme(legend.position = "none")+
scale_linetype_manual(values = c("dashed", "dashed")) +
scale_x_continuous(expand = c(0,0)) +
# 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 <-ggplot(data = year_totals, aes(x=Year, y = Revenue/1000)) +
geom_recessions(text = FALSE, update_recessions = recessions)+
geom_line(aes(x = Year, y = Revenue/1000), color = "Black", lwd = 1) +
geom_line(aes(x = Year, y = Expenditures/1000), color = "red", linetype = "dotted", lwd = 1) +
geom_line(aes(x = Year, y = (`Fiscal Gap`/1000)), color = "darkgray", lwd=1) +
geom_text(data = annotation, aes(x=x, y=y, label=label))+
geom_text(data = annotation_nums, aes(x = x, y = y, label = label), size = 3) +
theme_classic() +
theme(legend.position = "none")+
# scale_linetype_manual(values = c("dashed", "dotted")) +
geom_hline(yintercept = 0) +
scale_y_continuous(labels = comma)+
scale_x_continuous(expand = c(0,0), limits = c(1998, 2024) ) +
xlab("Year") +
ylab("Billions of Dollars") +
ggtitle("Illinois Expenditures and Revenue Totals, 1998-2023")
fiscal_gap2
fiscal_gap2 <-ggplot(data = year_totals, aes(x=Year, y = Revenue/1000)) +
geom_recessions(text = FALSE, update = recessions)+
geom_line(aes(x = Year, y = Revenue/1000), color = "Black") +
geom_line(aes(x = Year, y = Expenditures/1000), color = "red", linetype = "dotted", lwd = 1) +
geom_line(aes(x = Year, y = (`Fiscal Gap`/1000)), color = "darkgray", lwd=1) +
geom_text(data = annotation, aes(x=x, y=y, label=label))+
geom_text(data = annotation_nums, aes(x = x, y = y, label = label), size = 3) +
theme_classic() +
theme(legend.position = "none",
panel.background = element_rect(fill='transparent'), #transparent panel bg
plot.background = element_rect(fill='transparent', color=NA), #transparent plot bg
panel.grid.major = element_blank(), #remove major gridlines
panel.grid.minor = element_blank(), #remove minor gridlines
legend.background = element_rect(fill='transparent'), #transparent legend bg
)+
# scale_linetype_manual(values = c("dashed", "dotted")) +
geom_hline(yintercept = 0) +
geom_hline(yintercept = 0) +
scale_y_continuous(labels = comma)+
scale_x_continuous(expand = c(0,0),
limits = c(1998, 2025), breaks = c(1998, 2000, 2005, 2010, 2015, 2020, 2023) ) +
xlab("Year") +
ylab("Billions of Dollars")
ggsave(plot = fiscal_gap2, filename= "./paper-figures/Figure1-fiscalgap.eps", width = 6, height = 4)
ggsave(plot = fiscal_gap2, filename= "./paper-figures/Figure1-fiscalgap.png", width = 6, height = 4)Expenditure and revenue amounts in billions 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`/1000), fill = "red"))+
coord_flip() +
theme_classic()+
theme(legend.position = "none") +
labs(title = "Expenditures for FY2023") +
xlab("Expenditure Categories") +
ylab("Billions 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`/1000)))+
coord_flip() +
theme_classic() +
theme(legend.position = "none") +
labs(title = "Revenues for FY2023")+
xlab("Revenue Categories") +
ylab("Billions 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`/1000), fill = "rosybrown2")+
coord_flip() +
theme_classic() +
labs(title = "Expenditures for FY2023") +
xlab("") +
ylab("Billions 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`/1000), y = `Dollars`/1000), fill = "dark gray")+
coord_flip() +
theme_classic() +
labs(title = "Revenues for FY2023") +
xlab("") +
ylab("Billions of Dollars")
Changes in Categories - 2022 to 2023
rev_dotplot <- rev_long %>%
filter(Year == "2023" | Year == "2022") %>%
mutate(Year = as.character(Year)) %>%
ggplot(aes(x = Dollars/1000, y = reorder(Category, Dollars))) +
geom_line(aes(group = Category) )+
geom_text(aes(x = ifelse(Year == "2023", as.numeric(Dollars)/1000, 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 = "Billions 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())+
scale_x_continuous(limits = c(0, 35), labels = comma)
rev_dotplot
ggsave("./paper-figures/Figure3-revenue-dotplot.png")
ggsave("./paper-figures/Figure3-revenue-dotplot.eps")
exp_dotplot <- exp_long %>%
filter(Year == "2023" | Year == "2022") %>%
mutate(Year = as.character(Year)) %>%
ggplot(aes(x = Dollars/1000, y = reorder(Category, Dollars))) +
geom_line(aes(group = Category) )+
geom_text(aes(x = ifelse(Year == "2023", (as.numeric(Dollars)/1000)+1, 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() ) +
scale_x_continuous(limits = c(0, 35), labels = comma)
exp_dotplot
ggsave("./paper-figures/Figure4-expenditure-dotplot.png")
ggsave("./paper-figures/Figure4-expenditure-dotplot.eps")
annotation <- data.frame(
x = c(2013, 2018, 2013),
y = c(16, 10, 5),
label = c("Individual Income Tax", "Sales Tax", "Corporate Income Tax")
)
top3 <- rev_long %>%
filter(Category == "02" | Category == "03" | Category == "06")
top3 <- ggplot(data = top3, aes(x=Year, y=Dollars/1000))+
geom_recessions(text = FALSE, update = recessions)+
geom_line(aes(x=Year, y=Dollars/1000, color = Category_name)) +
geom_text(data = annotation, aes(x=x, y=y, label=label)) +
theme_classic() +
scale_x_continuous(expand = c(0,0)) +
scale_y_continuous(labels = comma) +
scale_linetype_manual(values = c("dotted", "dashed", "solid")) +
theme(legend.position = "none") +
labs(title = "Top 3 Own Source Revenues",
subtitle = "Individual Income Taxes, Sales Tax, and Corporate income taxes",
y = "Billions of Nominal Dollars")
top3
Sales Tax - online retailers
Not edited or double checked. Randomly looked into online retailers recently and didn’t finish thoughts on it. Just general notes pulled together while looking into online sales tax.
Law was passed in 2018 that required out of state retailers to pay the 6.25% state sales tax. The Rebuild Illinois law expanded the law to require remote retailers to charge all state and local retailers occupation taxes beginning in July 1, 2020. Before Jan. 1 2021, only state sales taxes were required to be collected (related to South Dakota v Wayfair court decision). Now required to pay state and local tax based on where product is delivered.
“On June 28, 2019, Public Act 101-0031, the”Leveling the Playing Field for Illinois Retail Act,” was signed into Illinois law and on December 13, 2019 an amendment to the Act was signed into law in Public Act 101-0604. In an effort to create more equity between remote sellers and local brick-and-mortar retailers, the new law requires remote sellers without a physical presence in the state and marketplace facilitators (e.g., Amazon and Walmart) to collect both state and local sales taxes effective January 1, 2021.” CivicFed.org
Requires remote sellers and marketplace facilitators to collect and remit the state and locally-imposed Retailers’ Occupation Tax (ROT) for the jurisdictions where the product is delivered (destination sourcing) rather than collecting and remitting solely the state use tax.
Illinois’ State sales tax rate is 6.25%, of which 5.0% of the sales tax revenue goes to the State, 1.0% goes to all municipalities, including Chicago, and the remaining 0.25% goes to the counties. However, Cook County’s 0.25% share of the State sales tax is distributed to the Regional Transportation Authority.
“The amended”Leveling the Playing Field for Illinois Retail Act” was passed by the General Assembly on November 14, 2019, to require both Remote Retailers and Marketplace Facilitators to collect and remit the state and locally-imposed Retailers’ Occupation Tax (ROT, aka sales tax) for the jurisdictions where the product is delivered (its destination) starting January 1, 2021.”- Illinois Municipal League
Marketplace Facilitators, like Amazon, were required to collect Use Tax on sales starting January 1, 2020
Other sellers required to collect state and local sales tax on sales on January 2021.
There is a state tax rate of 6.25% and Illinois municipalities may impose an additional local sales tax called the Retailer’s Occupation Tax.
For remote sellers, the state tax rate is referred to as “use tax” and for intrastate sellers, “ROT” simply means sales tax.
The ROT is measured upon the seller’s gross receipts and the seller is statutorily required to collect the use tax from their customers.
source 0482 is State ROT-2.2%
ILGA info - leveling the playing field went into effect on July 1 2020 which is the beginning of FY21
## State Retailers Occupation Tax.
rev_temp %>% filter(source == "0481") %>%
group_by(fy, source_name_AWM) %>% summarize(revenue=sum(receipts))rev_temp %>%
filter(source == "0481") %>%
group_by(fy, source_name_AWM, fund_name_ab) %>%
summarize(revenue=sum(receipts))%>%
arrange(-fy, -revenue)%>%
pivot_wider(names_from = "fy", values_from="revenue")rev_temp %>%
filter(source == "0481") %>%
#group_by(fy, source_name_AWM, fund_name_ab) %>%
# summarize(revenue=sum(receipts)) %>%
ggplot(aes(x=fy, y=receipts))+
geom_recessions(text = FALSE, update_recessions = recessions)+
geom_line(aes(color=fund_name_ab))+
geom_vline(xintercept = 2018)+
geom_vline(xintercept = 2021)+
theme_classic()+
scale_x_continuous(expand = c(0,0))
labs(title="State Retailers' Occupation Tax, Source 0481",
caption = "Leveling the Playing Field went into effect for Amazon on January 1, 2020(mid-FY21)
and for other remote retailers starting January 1, 2021 (mid-FY22)")$title
[1] "State Retailers' Occupation Tax, Source 0481"
$caption
[1] "Leveling the Playing Field went into effect for Amazon on January 1, 2020(mid-FY21) \n and for other remote retailers starting January 1, 2021 (mid-FY22)"
attr(,"class")
[1] "labels"### Remote Occupation Tax
# STATE ROT-2.2%
rev_temp %>%
filter(source == "0482") %>%
group_by(fy, source_name_AWM) %>%
summarize(revenue=sum(receipts))rev_temp %>%
filter(source == "0482") %>%
group_by(fy, source_name_AWM, fund_name_ab) %>%
summarize(revenue=sum(receipts))%>%
arrange(-fy, -revenue)%>%
pivot_wider(names_from = "fy", values_from="revenue")rev_temp %>%
filter(source == "0482") %>%
#group_by(source_name_AWM) %>%
#summarize(revenue=sum(receipts)) %>%
ggplot(aes(x=fy, y=receipts))+
geom_line(aes(color=fund_name_ab))+
geom_recessions(text = FALSE, update_recessions = recessions)+
geom_vline(xintercept = 2018)+
geom_vline(xintercept = 2020)+
theme_classic() +
scale_x_continuous(expand = c(0,0)) +
labs(title="State Retailers' Occupation Tax",
subtitle = "Large increases due to Leveling the Playing Field Act & Online shopping during pandemic")
rev_temp %>%
filter(source == "0482") %>%
group_by(fy, source_name_AWM, fund_name_ab) %>%
summarize(revenue=sum(receipts)) %>%
ggplot()+
geom_line(aes(x=fy, y=revenue, color=fund_name_ab))+
geom_vline(xintercept = 2018) +
geom_vline(xintercept = 2021) +
#geom_recessions(aes(x=fy, y=receipts)+
theme_classic()+
scale_x_continuous(expand = c(0,0)) +
labs(title="State ROT - 2.2%",
subtitle = "Large increases due to Leveling the Playing Field Act & Online shopping during pandemic??",
caption = "State tax began being collected for remote retailers based on
destination beginning in Leveling the Playing Field went into effect for Amazon on January 1, 2020(mid-FY21) and for other
remote retailers starting January 1, 2021 (mid-FY22)")
rev_temp %>%
filter(source == "0482" | source == "0481") %>%
group_by(fy, source_name_AWM) %>%
summarize(revenue=sum(receipts)) %>%
ggplot()+
geom_line(aes(x=fy, y=revenue, color=source_name_AWM))+
geom_vline(xintercept = 2018)+
geom_vline(xintercept = 2021)+
#geom_recessions(aes(x=fy, y=receipts)+
theme_classic()+
scale_x_continuous(expand = c(0,0)) +
labs(title="State ROT - 2.2% & ",
subtitle = "Large increases due to Leveling the Playing Field Act & Online shopping during pandemic??",
caption = "Leveling the Playing Field went into effect for Amazon on January 1, 2020(mid-FY21)
and for other remote retailers starting January 1, 2021 (mid-FY22)")
As of Feb. 6 2023, Source 481 Retailers Occupation Tax has collected $9.3 billion already. FY22 had $14.7 million. Around half goes to the General Revenue Fund.
ownsource_rev <- rev_long %>%
filter(!Category %in% c("57", "58", "59")) %>%
group_by(Year) %>%
summarize(Dollars = sum(Dollars))
ownsource_rev
# ownsource_rev %>%
# ggplot()+geom_line(aes(x=Year, y=Dollars)) +
# labs(title = "Own Source Revenues", subtitle = "Total own source revenue", y = "Millions of Dollars")fed_rev <- ff_rev %>% select(fy, rev_57, rev_58, rev_59) %>%
mutate(fed_total = rev_57+rev_58+rev_59)
annotation <- data.frame(
x = c(2013, 2015),
y = c(50, 25),
label = c("Own Source Revenue", "Federal Revenue")
)
annotation_nums <- data.frame(
x = c(2023, 2023),
y = c(50, 25),
label = c("Own Source Revenue", "Federal Revenue")
)
ownsource_graph <- ggplot(ownsource_rev, aes(x=Year, y=Dollars/1000)) +
geom_recessions(text = FALSE, update_recessions = recessions)+
geom_line(data = ownsource_rev, aes(x=Year, y=Dollars/1000), color = "Red") +
geom_line(data = fed_rev, aes(x=fy, y=fed_total/1000), color = "Black") +
geom_text(data = annotation, aes(x=x, y=y, label=label))+
scale_y_continuous(labels = comma)+
scale_x_continuous(expand = c(0,0)) +
theme(legend.position = "none")+
theme_classic()+
labs(title = "Own Source Revenue and Federal Revenue, 1998-2023",
y = "Billions of Dollars")
ownsource_graph
# ggsave(plot = ownsource_graph, file = "Figure4.eps")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",
T ~ "CHECK ME"
) ) %>%
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 ~ "CHECK ME"
# 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("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:
# 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() %>%
row_spec(31, bold = T, color = "black", background = "gray")| Expenditure Category | 1 Year CAGR | 2 Year CAGR | 3 Year CAGR | 5 Year CAGR | 10 Year CAGR | 25 Year CAGR |
|---|---|---|---|---|---|---|
| Aging | 17.50 | 9.85 | 9.02 | 7.66 | 2.10 | 7.72 |
| Agriculture | -0.61 | 14.81 | 7.18 | 0.98 | 2.43 | 0.81 |
| Bus & Profession Regulation | 5.87 | 6.39 | 5.75 | 3.65 | -1.72 | 1.64 |
| Capital Improvement | 52.57 | 19.39 | 28.00 | 23.57 | 2.05 | 3.80 |
| Central Management | 17.75 | 7.01 | 4.64 | 6.16 | 4.02 | 4.76 |
| Children And Family Services | 25.99 | 10.03 | 8.40 | 7.78 | 3.33 | 0.78 |
| Community Development | 7.71 | -5.46 | 34.18 | 24.63 | 4.34 | 4.79 |
| Corrections | 13.46 | 2.80 | 3.47 | -2.65 | 3.13 | 2.21 |
| Debt Service | -2.89 | -1.86 | 0.07 | -0.20 | 0.80 | 5.73 |
| Elected Officers | 7.97 | 5.71 | 6.16 | 4.81 | 4.51 | 3.83 |
| Employment Security | -1.39 | -9.04 | 4.63 | 2.75 | 0.17 | 1.62 |
| Environmental Protect Agency | -5.68 | -4.43 | -5.01 | -7.42 | 1.38 | 2.78 |
| Healthcare & Fam Ser Net Of Medicaid | 13.37 | 4.29 | 6.79 | 0.77 | 0.93 | 5.46 |
| Human Services | 21.34 | 16.10 | 13.77 | 10.19 | 4.64 | 3.29 |
| Judicial | 20.70 | 8.56 | 8.59 | 7.29 | 5.65 | 3.37 |
| K-12 Education | 9.86 | 9.86 | 9.15 | 7.04 | 5.35 | 4.35 |
| Legislative | 66.45 | 41.14 | 27.74 | 18.72 | 5.76 | 5.19 |
| 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.42 | 5.22 | 4.99 | 4.85 | 2.81 | 1.95 |
| Other Boards & Commissions | 33.54 | 14.97 | 15.85 | 8.46 | 1.53 | 5.11 |
| Other Departments | 16.22 | 8.65 | 8.38 | 8.06 | 6.03 | 9.36 |
| Public Health | -8.38 | -9.79 | 11.07 | 15.13 | 7.76 | 6.44 |
| Public Safety | 1.18 | -6.87 | 5.37 | 14.12 | 7.58 | 5.69 |
| Revenue | 66.68 | 34.85 | 39.16 | 41.42 | 22.02 | 8.14 |
| State Employee Healthcare | -0.09 | 2.08 | 0.23 | -12.49 | 3.28 | 5.82 |
| State Pension Contribution | 5.06 | 10.12 | 8.85 | 9.54 | 9.06 | 10.52 |
| Tollway | -9.89 | -1.78 | -0.41 | 5.23 | 7.14 | 6.78 |
| Transportation | 15.81 | -0.81 | 9.06 | 8.02 | 2.11 | 4.02 |
| University Education | 12.52 | 7.50 | 5.01 | 4.60 | 0.98 | 0.81 |
| Total | 11.90 | 9.79 | 11.12 | 8.35 | 5.87 | 5.24 |
# 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 (Ordered Alphabetical)", row.names = FALSE) %>%
kable_classic() %>%
row_spec(23, bold = T, color = "black", background = "gray")| Revenue Category | 1 Year CAGR | 2 Year CAGR | 3 Year CAGR | 5 Year CAGR | 10 Year CAGR | 25 Year CAGR |
|---|---|---|---|---|---|---|
| Cigarette Taxes | -6.72 | -7.49 | -2.64 | 0.53 | -0.87 | 2.12 |
| Corp Franchise Taxes & Fees | 4.40 | -15.99 | 2.27 | 1.66 | 0.97 | 2.62 |
| Corporate Income Taxes | 4.56 | 32.28 | 42.32 | 24.88 | 8.95 | 7.16 |
| Federal Medicaid | 6.09 | 7.28 | 13.44 | 8.44 | 9.32 | 7.47 |
| Federal Other | -43.87 | 8.68 | 3.88 | 13.39 | 5.67 | 4.35 |
| Federal Transportation | 15.30 | -5.74 | 5.83 | 5.82 | 2.17 | 3.78 |
| Gifts And Bequests | 13.17 | 18.34 | 31.73 | 11.70 | 10.40 | 11.50 |
| Individual Income Taxes | -4.28 | 2.74 | 8.89 | 6.19 | 4.35 | 5.37 |
| Inheritance Tax | -16.68 | 5.71 | 21.07 | 7.02 | 5.53 | 2.83 |
| Insurance Taxes&Fees&Licenses | 8.56 | 2.25 | 11.34 | 3.29 | 3.82 | 6.65 |
| Licenses, Fees & Registrations | 9.25 | 2.24 | 13.23 | 11.07 | 6.18 | 7.94 |
| Liquor Gallonage Taxes | -1.16 | 0.67 | 1.47 | 1.32 | 1.23 | 7.09 |
| Lottery Receipts | 11.88 | 2.46 | 10.37 | 4.32 | 1.23 | 2.53 |
| Medical Provider Assessments | 9.50 | 3.60 | 5.58 | 13.15 | 9.46 | 8.41 |
| Motor Fuel Tax | 1.66 | 3.82 | 3.48 | 13.44 | 7.39 | 2.76 |
| Motor Vehicle And Operators | -0.01 | -2.84 | 3.08 | 1.49 | 0.89 | 3.08 |
| Other Taxes | 12.99 | 36.08 | 25.80 | 14.72 | 16.51 | 7.90 |
| Public Utility Taxes | 2.23 | 2.66 | 0.44 | 0.16 | -0.43 | 0.76 |
| Receipts From Revenue Producing | 8.47 | 5.70 | 5.99 | 0.89 | 2.69 | 5.21 |
| Riverboat Wagering Taxes | 9.37 | 40.61 | 2.32 | -4.22 | -3.82 | 2.05 |
| Sales Taxes | 4.84 | 8.04 | 9.75 | 6.67 | 4.71 | 3.30 |
| All Other Sources | 50.65 | 44.03 | 24.31 | 14.21 | 10.57 | 6.08 |
| Total | -3.69 | 8.35 | 11.52 | 8.89 | 5.88 | 5.13 |
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")| Revenue Category | FY 2023 ($ billions) | FY 2022 ($ billions) | 1-Year Change | 25-Year CAGR |
|---|---|---|---|---|
| Individual Income Taxes | 25.3 | 26.4 | -4.28 | 5.37 |
| Federal Medicaid | 20.2 | 19.0 | 6.09 | 7.47 |
| Sales Taxes | 16.2 | 15.5 | 4.84 | 3.30 |
| Federal Other | 10.9 | 19.4 | -43.87 | 4.35 |
| Corporate Income Taxes | 10.5 | 10.0 | 4.56 | 7.16 |
| All Other Sources | 4.1 | 2.7 | 50.65 | 6.08 |
| Motor Fuel Tax | 2.6 | 2.5 | 1.66 | 2.76 |
| Receipts From Revenue Producing | 2.6 | 2.4 | 8.47 | 5.21 |
| Federal Transportation | 2.1 | 1.8 | 15.30 | 3.78 |
| Gifts And Bequests | 2.1 | 1.9 | 13.17 | 11.50 |
| Licenses, Fees & Registrations | 2.1 | 1.9 | 9.25 | 7.94 |
| Lottery Receipts | 1.6 | 1.4 | 11.88 | 2.53 |
| Motor Vehicle And Operators | 1.6 | 1.6 | -0.01 | 3.08 |
| Other Taxes | 1.6 | 1.4 | 12.99 | 7.90 |
| Public Utility Taxes | 1.4 | 1.4 | 2.23 | 0.76 |
| Cigarette Taxes | 0.8 | 0.8 | -6.72 | 2.12 |
| Insurance Taxes&Fees&Licenses | 0.7 | 0.6 | 8.56 | 6.65 |
| Inheritance Tax | 0.5 | 0.6 | -16.68 | 2.83 |
| Liquor Gallonage Taxes | 0.3 | 0.3 | -1.16 | 7.09 |
| Riverboat Wagering Taxes | 0.3 | 0.3 | 9.37 | 2.05 |
| Corp Franchise Taxes & Fees | 0.2 | 0.2 | 4.40 | 2.62 |
| Medical Provider Assessments | 4.1 | 3.7 | 9.50 | 8.41 |
| Total | 111.8 | 116.1 | -3.69 | 5.13 |
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 - All FF Categories, Ordered from Largest to Smallest Expenditure Amount", row.names = FALSE) %>%
kable_classic() %>%
row_spec(31, bold = T, color = "black", background = "gray")| 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.06 | 10.52 |
| Other Departments | 5.7 | 4.9 | 16.22 | 9.36 |
| Transportation | 5.3 | 4.6 | 15.81 | 4.02 |
| Revenue | 3.1 | 1.9 | 66.68 | 8.14 |
| State Employee Healthcare | 3.0 | 3.0 | -0.09 | 5.82 |
| University Education | 2.5 | 2.3 | 12.52 | 0.81 |
| Debt Service | 2.0 | 2.0 | -2.89 | 5.73 |
| Tollway | 1.9 | 2.1 | -9.89 | 6.78 |
| Corrections | 1.7 | 1.5 | 13.46 | 2.21 |
| Public Safety | 1.7 | 1.7 | 1.18 | 5.69 |
| Children And Family Services | 1.6 | 1.3 | 25.99 | 0.78 |
| Community Development | 1.5 | 1.4 | 7.71 | 4.79 |
| Aging | 1.4 | 1.2 | 17.50 | 7.72 |
| Central Management | 1.4 | 1.2 | 17.75 | 4.76 |
| Elected Officers | 1.1 | 1.0 | 7.97 | 3.83 |
| Public Health | 0.8 | 0.8 | -8.38 | 6.44 |
| Capital Improvement | 0.7 | 0.4 | 52.57 | 3.80 |
| Environmental Protect Agency | 0.6 | 0.7 | -5.68 | 2.78 |
| Judicial | 0.6 | 0.5 | 20.70 | 3.37 |
| Healthcare & Fam Ser Net Of Medicaid | 0.4 | 0.4 | 13.37 | 5.46 |
| Employment Security | 0.3 | 0.3 | -1.39 | 1.62 |
| Natural Resources | 0.3 | 0.3 | 10.42 | 1.95 |
| Other Boards & Commissions | 0.3 | 0.2 | 33.54 | 5.11 |
| Bus & Profession Regulation | 0.2 | 0.2 | 5.87 | 1.64 |
| Legislative | 0.2 | 0.1 | 66.45 | 5.19 |
| Agriculture | 0.1 | 0.1 | -0.61 | 0.81 |
| Total | 112.0 | 100.1 | 11.90 | 5.24 |
The 10 largest revenue sources and 15 largest expenditure sources remain separate categories and all other smaller sources/expenditures are combined into “All Other Revenues (Expenditures)”. These condensed tables are typically used in the Fiscal Futures articles. They were manually created in past years but this hopefully automates the process a bit until final formatting stages.
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 combined 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 & 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, 14)
CAGR_expenditures_majorcats_tot%>%
kbl(caption = "CAGR Calculations for Largest Expenditure Categories" , row.names=FALSE) %>%
kable_classic() %>%
row_spec(17, bold = T, color = "black", background = "gray")
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, 4)
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(17, bold = T, color = "black", background = "gray")| Expenditure Category | 1 Year CAGR | 2 Year CAGR | 3 Year CAGR | 5 Year CAGR | 10 Year CAGR | 25-Year CAGR |
|---|---|---|---|---|---|---|
| Children & Family Services | 25.99 | 10.03 | 8.40 | 7.78 | 3.33 | 0.78 |
| Community Development | 7.71 | -5.46 | 34.18 | 24.63 | 4.34 | 4.79 |
| Corrections | 13.46 | 2.80 | 3.47 | -2.65 | 3.13 | 2.21 |
| Debt Service | -2.89 | -1.86 | 0.07 | -0.20 | 0.80 | 5.73 |
| Human Services | 21.34 | 16.10 | 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.18 | -6.87 | 5.37 | 14.12 | 7.58 | 5.69 |
| Revenue | 66.68 | 34.85 | 39.16 | 41.42 | 22.02 | 8.14 |
| State Employee Healthcare | -0.09 | 2.08 | 0.23 | -12.49 | 3.28 | 5.82 |
| State Pension Contribution | 5.06 | 10.12 | 8.85 | 9.54 | 9.06 | 10.52 |
| Tollway | -9.89 | -1.78 | -0.41 | 5.23 | 7.14 | 6.78 |
| Transportation | 15.81 | -0.81 | 9.06 | 8.02 | 2.11 | 4.02 |
| University Education | 12.52 | 7.50 | 5.01 | 4.60 | 0.98 | 0.81 |
| All Other Expenditures ** | 14.36 | 6.62 | 8.02 | 6.78 | 4.16 | 5.66 |
| Total Expenditures | 11.90 | 9.79 | 11.12 | 8.35 | 5.87 | 5.24 |
| 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.3 | 4.6 | 15.8% | 4.0% |
| Revenue | 3.1 | 1.9 | 66.7% | 8.1% |
| State Employee Healthcare | 3.0 | 3.0 | -0.1% | 5.8% |
| University Education | 2.5 | 2.3 | 12.5% | 0.8% |
| Debt Service | 2.0 | 2.0 | -2.9% | 5.7% |
| Tollway | 1.9 | 2.1 | -9.9% | 6.8% |
| Corrections | 1.7 | 1.5 | 13.5% | 2.2% |
| Public Safety | 1.7 | 1.7 | 1.2% | 5.7% |
| Children & Family Services | 1.6 | 1.3 | 26.0% | 0.8% |
| Community Development | 1.5 | 1.4 | 7.7% | 4.8% |
| All Other Expenditures ** | 14.0 | 12.2 | 14.4% | 5.7% |
| Total Expenditures | 112.0 | 100.1 | 11.9% | 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() %>%
row_spec(12, bold = T, color = "black", background = "gray")| Revenue Category | 1 Year CAGR | 2 Year CAGR | 3 Year CAGR | 5 Year CAGR | 10 Year CAGR | 25-Year CAGR |
|---|---|---|---|---|---|---|
| Corporate Income Tax | 4.56 | 32.28 | 42.32 | 24.88 | 8.95 | 7.16 |
| Federal Medicaid Reimbursements | 6.09 | 7.28 | 13.44 | 8.44 | 9.32 | 7.47 |
| Federal Other | -43.87 | 8.68 | 3.88 | 13.39 | 5.67 | 4.35 |
| Federal Transportation | 15.30 | -5.74 | 5.83 | 5.82 | 2.17 | 3.78 |
| Income Tax | -4.28 | 2.74 | 8.89 | 6.19 | 4.35 | 5.37 |
| Licenses, Fees, Registration | 9.25 | 2.24 | 13.23 | 11.07 | 6.18 | 7.94 |
| Medical Provider Assessments | 9.50 | 3.60 | 5.58 | 13.15 | 9.46 | 8.41 |
| Motor Fuel Taxes | 1.66 | 3.82 | 3.48 | 13.44 | 7.39 | 2.76 |
| Receipts from Revenue Producing | 8.47 | 5.70 | 5.99 | 0.89 | 2.69 | 5.21 |
| Sales Tax | 4.84 | 8.04 | 9.75 | 6.67 | 4.71 | 3.30 |
| All Other Sources ** | 14.47 | 14.13 | 13.76 | 6.89 | 4.75 | 4.30 |
| Total Revenue | -3.69 | 8.35 | 11.52 | 8.89 | 5.88 | 5.13 |
###### 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,5)
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")| Revenue Category | FY 2023 ($ billions) | FY 2022 ($ billions) | 1-Year Change | 25-Year Change |
|---|---|---|---|---|
| Income Tax | 25.3 | 26.4 | -4.3% | 5.4% |
| Federal Medicaid Reimbursements | 20.2 | 19.0 | 6.1% | 7.5% |
| Sales Tax | 16.2 | 15.5 | 4.8% | 3.3% |
| Federal Other | 10.9 | 19.4 | -43.9% | 4.3% |
| Corporate Income Tax | 10.5 | 10.0 | 4.6% | 7.2% |
| Medical Provider Assessments | 4.1 | 3.7 | 9.5% | 8.4% |
| Motor Fuel Taxes | 2.6 | 2.5 | 1.7% | 2.8% |
| Receipts from Revenue Producing | 2.6 | 2.4 | 8.5% | 5.2% |
| Federal Transportation | 2.1 | 1.8 | 15.3% | 3.8% |
| Licenses, Fees, Registration | 2.1 | 1.9 | 9.2% | 7.9% |
| All Other Sources ** | 15.3 | 13.3 | 14.5% | 4.3% |
| Total Revenue | 111.8 | 116.1 | -3.7% | 5.1% |
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_DATE_2023.xlsx')Saves main items in one excel file named summary_file.xlsx. Delete eval=FALSE to run on local computer.