Unemployment Insurance Funds
Alea Wilbur
5/16/2022
knitr::opts_chunk$set(echo = TRUE, warning = FALSE, message = FALSE)
library(ggplot2)
library(readxl)
library(tidyverse)
library(data.table)
library(quantmod)
library(geofacet)
library(fredr)
library(sf)
library(usmap)
library(maps)
library(scales)
library(janitor)
library(RColorBrewer)
library(networkD3)
library(ggsankey)
library(ggalluvial)This descriptive comparison between US states looks at their unemployment trust fund balances before COVID began, after historic levels of unemployment benefits paid to residents, and if states used federal aid to replenish their trust fund balances.
ui_states<- read_excel("ui_state_comparison.xlsx", sheet = 1)%>% clean_names() %>%
mutate(state = state_name) %>% select(-yr)
ui_statesSummary Graphics
ui_states <- ui_states %>%
mutate(FederalFunds = (arpa_millions + cares_millions),
usedfed = ifelse(FederalFunds >0, "Fed. Funds Used", "Not Used"),
State = str_to_title(state_name))Outstanding Title XII Loans
Nine states borrowed from the federal government to continue their UI benefit provisions through the end of both 2020 and 2021. Illinois has consistently had the third highest outstanding federal debt. By January 1, 2022, most states had used all or part of their CARES and APRA funds to help ease the pressures on their UI programs. As of March 25, 2022, Illinois has allocated $2.7 billion of its APRA funds to address its outstanding UI debt to the federal government.
ui_states %>%
filter(year_end_federal_loans_to_state_reserve_funds_balance>0) %>%
ggplot() +
geom_bar(aes(x = year_end_federal_loans_to_state_reserve_funds_balance/1000000, y= reorder(State, year_end_federal_loans_to_state_reserve_funds_balance)), color = "grey", stat = "identity") +
geom_text(aes(x= #500,
year_end_federal_loans_to_state_reserve_funds_balance/1000000 + 1000,
y =reorder(State, year_end_federal_loans_to_state_reserve_funds_balance), label = format(round(as.numeric(year_end_federal_loans_to_state_reserve_funds_balance)/1000000, digits=0), big.mark = ",")))+
theme_classic() + labs(title = "Federal Loans to State Trust Funds as of 1/1/2022", x = "Millions of Dollars", y = "")
Source: State UI Trust Fund Solvency Report, 2021 and 2022.
Federal Aid to States
ui_states %>%
filter(FederalFunds>0) %>%
ggplot() +
geom_bar(aes(x = FederalFunds, y= reorder(State, +FederalFunds)), color = "grey", stat = "identity") +
theme_classic() +
labs(title = "Federal Funds allocated to UI Trust Funds", subtitle = "CARES & ARPA Funds Allocated, as of Jan. 1, 2022", caption = "Note: Not all States used pandemic federal aid for their unemployment insurance trust funds. \nAs of March 25, 2022, illinois allocated $2.7 Billion of its ARPA funds to partially pay back its outstanding Title XII loan. \nCARES and ARPA allocation data from the National Conference of State Legislatures.", x = " Millions of Dollars", y = "" )
UI Fund Balances
ui_states <- ui_states %>%
mutate(label_x = ifelse(balance_with_fed_money > 0, 200, -200), label_hjust = ifelse(balance_with_fed_money<0, 0.2, -0.2))
#hjust = 0.2 + as.numeric(balance_with_fed_money/1000000 > 0)
ui_states %>%
ggplot(aes(y= reorder(State, -balance_with_fed_money), x = balance_with_fed_money/1000000
, fill = usedfed
)) +
geom_bar(color = "white", stat = "identity") +
theme_classic() + #change font size of axis text
scale_fill_brewer(palette = "Paired") +
geom_text(aes(x = 0, label = State ,
hjust = #ifelse(as.numeric(balance_with_fed_money/1000000) > 0, 0.2, -0.2)),
as.numeric(balance_with_fed_money/1000000 > 0)), size = 3) +
geom_text(aes(x = balance_with_fed_money/1000000,
label = format(dollar(round(balance_with_fed_money/1000000, digits=0), big.mark = ",") ) ,
hjust = -0.05 + as.numeric(balance_with_fed_money/1000000 < 0)),
size = 2.7) +
labs(title = "UI Trust Fund Net Balance, Reserves net Debt as of 1/1/2022", x = "", y = "" ) +
scale_y_discrete(position = "right" )+
theme(
axis.text.y = element_blank(),
legend.position = "none",
axis.ticks.y = element_blank(),
axis.line.y.right = element_blank(),
axis.line.x = element_blank(),
axis.title.x = element_blank(),
axis.ticks.x = element_blank(),
axis.text.x = element_blank()) 
#+ annotate(geom="text", x=3, y=30, label="California", color="red")ui_states %>%
ggplot() +
geom_bar(aes(y= reorder(State, -balance_with_fed_money),
x = balance_w_o_fed_money/1000000, label = State),
stat = "identity", fill = "blue", color = "white", alpha = .5) +
geom_bar(aes(y= reorder(State, -balance_with_fed_money),
x = balance_with_fed_money/1000000, label = State),
stat = "identity", alpha = .5, fill = "dark blue", color = "white")+
# geom_text(aes(x = balance_with_fed_money/1000000, y= reorder(State, -balance_with_fed_money), label = State, nudge_x = 0.5) )+
labs(title = "UI Fund Balance", subtitle = "Before and After Federal Aid, as of 1/1/2022", x="Millions of Dollars", y = "") + theme_classic()
ui_long <- ui_states %>%
pivot_longer(cols = c(balance_with_fed_money, balance_w_o_fed_money),
names_to = "Balance",
values_to = "Balance_Dollars")
ui_long %>%
ggplot(aes(x = Balance_Dollars/1000000, y = reorder(State, Balance_Dollars)) ) +
geom_line(aes(group = State) )+
geom_point(aes(color = Balance), size=2) +
labs(title = "Balance Before and After Federal Aid", x = "Millions of Dollars" , y = "", caption = "Net fund balance calculations used current trust fund reserves (as of 1/1/2022) net Title XII loans from federal government. \nSource: Allocated federal relief fund data from the National Conference of State Legislatures.") +
scale_color_brewer(palette="Paired", labels = c("Pre-Fed Aid", "Post-Fed Aid"))+
theme_classic()+ theme(legend.position = "bottom" )
ui_long %>%
#filter(State != "New York" & State != "California" & State != "Oregon" & State !="North Carolina" & State != "Illinois") %>%
ggplot(aes(x = Balance_Dollars/1000000,
y = reorder(State, year_end_net_reserve_balance)) ) +
geom_line(aes(group = State) )+
geom_point(aes(color = Balance), size=2) +
geom_text(aes(x = ifelse(Balance == "balance_with_fed_money", as.numeric(Balance_Dollars)/1000000, NA), label = ifelse(Balance == "balance_with_fed_money", State, "")),
hjust = -0.2,
size = 2.8) +
labs(title = "Unemployment Insurance Trust Fund Net Balance Before and After Federal Relief", x = "Millions of Dollars" , y = "") +
scale_color_brewer(palette="Paired",
labels = c("Pre-Fed Aid", "Post-Fed Aid"))+
theme_classic()+
theme(
legend.position = "none" ,
axis.text.y = element_blank(),
axis.ticks.y = element_blank(),
axis.line.y.left = element_blank(),
axis.line.x = element_blank(),
# axis.title.x = element_blank(),
axis.title.y = element_blank(),
axis.ticks.x = element_blank())+scale_x_continuous(limits = c(-20000, 10000))
# axis.text.x = element_text(format = dollar)Not all states used ARPA or CARES funds on their unemployment insurance trust funds. Net fund balance calculations used current trust fund reserves (as of 1/1/2022) net outstanding. Title XII loans from federal government. Source: Allocated federal relief fund data from the National Conference of State Legislatures.
Reserves Per Person
ui_states %>%
ggplot() +
geom_col(aes(y=reorder(State, -reserves_per_person_with_fed_money), x = reserves_per_person_with_fed_money, ), alpha = .5, fill = "black", color = "white") +
geom_col(aes(y = reorder(State, -reserves_per_person_with_fed_money), x = reserves_per_person_without_fed_money), fill= "gray", alpha = .5, color = "white") +
scale_x_continuous(labels = comma)+
labs(title = "Per Person UI Reserves", subtitle = "Before and After Federal Aid", x = "Reserve Dollars per Person", y = "", caption = "Not all States used pandemic federal aid for their unemployment insurance trust funds. CARES and ARPA allocation data from the National Conference of State Legislatures. \nNote: As of March 25, 2022, illinois allocated $2.7 Billion of its ARPA funds to partially pay back its outstanding Title XII loan." )+theme_classic()
#pivot new way
ui_long <- ui_states %>%
pivot_longer(cols = c( reserves_per_person_with_fed_money, reserves_per_person_without_fed_money),
names_to = "Before_After",
values_to = "Dollars")
# dumbbell plot
ui_long %>%
ggplot(aes(x = Dollars, y = reorder(State, year_end_net_reserve_balance )) ) +
geom_line(aes(group = State)) +
geom_point(aes(color = Before_After,), size=4) +
labs(title = "UI Reserves per person", subtitle = "Balances/Population Before and After Federal Aid, as of 1/1/2022", x= "Dollars per Person", y = "", caption = "Net balance was divided by state popuulation to show average UI Trust Fund reserve levels without federal relief funds. \nSource: Allocated federal relief fund data from the National Conference of State Legislatures. Per person calculations based on 2016-2020 ACS population estimates.") +
scale_color_brewer(palette="Paired", labels = c("Pre-Fed Aid", "Post-Fed Aid"))+
theme_classic()+ theme(legend.position = "none" )
## Take 2
ui_long %>%
ggplot(aes(x = Dollars, y = reorder(State, Dollars))
# ifelse(Before_After == "reserves_per_person_with_fed_money", State, ""))
) +
geom_line(aes(group = State)) +
geom_point(aes(color = Before_After,), size=3) +
geom_text(aes(x = ifelse(Before_After == "reserves_per_person_with_fed_money", as.numeric(Dollars), NA), label = ifelse(Before_After == "reserves_per_person_with_fed_money", State, "")),
hjust = -0.3,
size = 2.8)+
labs(title = "UI Reserve Dollars per person",
#subtitle = "Reordered by Reserves per person after Federal Aid",
x= "Dollars per Person", y = "",
caption = "UI Trust Fund net reserve levels per person = Net balance / ACS population estimates. \nValues were calculated with and without federal relief for states that allocated federal funds to their trust funds. \nSource: Allocated federal fund data from the NCSL as of 1/1/2022.") +
scale_color_brewer(palette="Paired", labels = c("Pre-Fed Aid", "Post-Fed Aid"), direction = -1)+
scale_x_continuous(limits = c(-500, 1100))+
theme_classic()+ theme(legend.position = "none",
axis.text.y = element_blank(),
axis.ticks.y = element_blank(),
axis.line.y.left = element_blank(),
axis.line.x = element_blank(),
axis.title.x = element_blank(),
axis.title.y = element_blank(),
axis.ticks.x = element_blank())
order <- ui_long %>%
filter(Before_After == "reserves_per_person_with_fed_money" ) %>%
select(State, reserves_withfed = Dollars)
merge <- left_join(ui_long, order)
## Take 2
#ui_long %>%
merge %>%
ggplot(aes(x = Dollars, y = reorder(State, reserves_withfed))
# ifelse(Before_After == "reserves_per_person_with_fed_money", State, ""))
) +
geom_line(aes(group = State)) +
geom_point(aes(color = Before_After,), size=3) +
geom_text(aes(x = ifelse(Before_After == "reserves_per_person_with_fed_money", as.numeric(Dollars), NA), label = ifelse(Before_After == "reserves_per_person_with_fed_money", State, "")),
hjust = -0.3,
size = 2.8)+
labs(
x= "Dollars per Person", y = "") +
scale_color_brewer(palette="Paired", labels = c("Pre-Fed Aid", "Post-Fed Aid"), direction = -1)+
scale_x_continuous(limits = c(-500, 1100), labels=scales::dollar_format())+
theme_classic()+ theme(legend.position = "none",
axis.text.y = element_blank(),
axis.ticks.y = element_blank(),
axis.line.y.left = element_blank(),
axis.line.x = element_blank(),
axis.title.x = element_blank(),
axis.title.y = element_blank(),
axis.ticks.x = element_blank())
UI Trust Fund net reserve levels per person = Net balance / population. Values were calculated with and without federal relief for states that allocated federal funds to their trust funds. Source: Allocated federal relief fund data from the National Conference of State Legislatures as of 1/1/2022 and 2016-2020 ACS population estimates.
## Take 3
ui_long2 <- ui_states %>%
pivot_longer(cols = c( reserves_per_person_with_fed_money2, reserves_per_person_without_fed_money),
names_to = "Before_After",
values_to = "Dollars")
before <- ui_long2 %>%
filter(Before_After == "reserves_per_person_with_fed_money2" ) %>%
select(State, reserves_withfed = Dollars)
merge <- left_join(ui_long2, before)
#ui_long2 %>%
merge %>%
ggplot(aes(x = Dollars, y = reorder(State, reserves_withfed))
# ifelse(Before_After == "reserves_per_person_with_fed_money", State, ""))
) +
geom_line(aes(group = State)) +
geom_point(aes(color = Before_After,), size=3) +
geom_text(aes(x = ifelse(Before_After == "reserves_per_person_with_fed_money2", as.numeric(Dollars), NA), label = ifelse(Before_After == "reserves_per_person_with_fed_money2", State, "")),
hjust = -0.3,
size = 2.8)+
labs(
#subtitle = "Reordered by Reserves per person after Federal Aid",
x= "Dollars per Person", y = "") +
scale_color_brewer(palette="Paired", labels = c("Pre-Fed Aid", "Post-Fed Aid"), direction = -1)+
scale_x_continuous(limits = c(-500, 1100), labels=scales::dollar_format())+
# scale_y_continuous(labels=scales::dollar_format())
theme_classic()+ theme(legend.position = "none",
axis.text.y = element_blank(),
axis.ticks.y = element_blank(),
axis.line.y.left = element_blank(),
axis.line.x = element_blank(),
axis.title.x = element_blank(),
axis.title.y = element_blank(),
axis.ticks.x = element_blank())
Summary Tables
To be added.
Notes: *On March 25, 2022, Illinois Gov. JB Pritzker signed a bill of dedicating $2.7 billion ARPA funds to bring the state’s outstanding debt to approximately $1,800million. ** -1*[Column (2)-column (1)+column (3)] ***Column 4/Column (5) Source: UI trust fund balance and outstanding debt data are from the 2022 State UI Trust Fund Solvency Report https://oui.doleta.gov/unemploy/solvency.asp; federal relief funds data are from NCSL and NCSL; population data are from the 2015-2020 ACS 5-Year estimates
Maps
us_states <- map_data("state")
plot_usmap(data = ui_states,
values = "reserves_per_person_without_fed_money",
regions = "states") +
scale_fill_gradientn(colors = c("red","yellow","green3","forest green"),
limits = c(-500, 1150),
n = 5,
name ="Dollars per Person", label = scales::dollar)+
labs( title = "State UI Trust Fund Net Balance Per Person \nFederal Relief Funds (CARES & ARPA) Not Included \nAs of January 1, 2022",
caption = "Net Balance was divided by state population to show average UI Trust Fund reserve levels without federal relief funds.\n Source: State UI trust fund balance and outstanding debt data are from the 2022 State UI Trust Fund Solvency Report \nFederal relief funds data are from the National Conference of State Legislatures \n Population data are from 2016-2020 ACS Estimates") +
theme(legend.position = "right")
ui_states$without_fed_billions <- ui_states$balance_w_o_fed_money /1000000000
plot_usmap(data = ui_states,
values = "without_fed_billions" ,
regions = "states") +
scale_fill_gradientn(colors = c("red3", "red2", "red", "orange", "yellow","forest green"),
limits = c(-20, 5),
n = 5,
# breaks = c(-15000000000,-10000000000,-5000000000,0),
name ="Billions of Dollars", label = scales::dollar)+
labs( title = "State UI Trust Fund Net Balance \nFederal Relief Funds (CARES & ARPA) Not Included \nAs of January 1, 2022",
caption = "State UI Trust Fund reserve levels without federal relief funds.\n Source: State UI trust fund balance and outstanding debt data are from the 2022 State UI Trust Fund Solvency Report \n Federal relief funds data are from National Conference of State Legislatures") +
theme(legend.position = "right")
ui_states$withfed_billions <- ui_states$balance_with_fed_money /1000000000
plot_usmap(data = ui_states,
values = "withfed_billions" ,
regions = "states") +
scale_fill_gradientn(colors = c("red3", "red2", "red", "orange", "yellow","forest green"),
limits = c(-20, 5),
n = 5,
name ="Billions of Dollars", label = scales::dollar)+
labs( title = "State UI Trust Fund Net Balance \n After Federal Relief Funds (CARES & ARPA) \n As of January 1, 2022",
caption = "State UI Trust Fund reserve levels after federal relief funds.\n Source: State UI trust fund balance and outstanding debt data are from the 2022 State UI Trust Fund Solvency Report.\n Federal relief funds data are from National Conference of State Legislatures") + theme(legend.position = "right")
plot_usmap(data = ui_states,
values = "reserves_per_person_with_fed_money",
regions = "states") +
scale_fill_gradientn(colors = c("red","yellow","green3","forest green"),
limits = c(-500, 1150),
n = 5,
name ="Dollars per Person", label = scales::dollar)+
labs(title = "State UI Trust Fund Net Balance Per Person \n After Federal Relief Funds (CARES & ARPA) \nAs of January 1, 2022",
caption = "Net Balance after use of federal funds was divided by state population to show average UI Trust Fund reserve.\nSource: State UI trust fund balance and outstanding debt data are from the 2022 State UI Trust Fund Solvency Report;\nFederal relief funds data are from National Conference of State Legislatures; population data are from 2016-2020 ACS Estimates") +
theme(legend.position = "right")
ui_states <- ui_states %>%
mutate(FederalFunds = (arpa_millions + cares_millions) )
plot_usmap(data = ui_states, values = "FederalFunds", regions = "states") +
scale_fill_continuous(low = "white", high = "yellow", name = "Federal Relief Funds \n($ Milions)",
label = scales::dollar) +
theme_void()+
labs( title = "Federal Dollars Transfered to UI Trust Funds",
subtitle = "CARES & ARPA Federal Funds, as of January 1, 2020",
caption = "Not all states used pandemic federal aid for their unemployment insurance trust funds. \n CARES and ARPA alloations from the National Conference of State Legislatures.
\nNote: As of March 25, 2022 Illinois allocated $2.7 Billion in ARPA funds \n to partially pay back its outstanding Title XII loan.")
Other Stuff
Extra Maps
ui_states$region <- tolower(ui_states$state_name)
us_states_funds <- left_join(us_states, ui_states)
#ui_states$bin <- cut(ui_states$reserves_per_person_with_fed_money, breaks = c(0, .25, .5, .75, 1))
ggplot(data = us_states_funds,
mapping = aes(x = long, y = lat,
group = group, fill = balance_w_o_fed_money/1000000000 )) +
geom_polygon(color = "grey") +
# scale_fill_brewer(palette = "Greens", name = "Billions of Dollars of Reserves \n w/o Federal Funds", labels = c("0-10", "10-50", "50-100", "100-500",
#"500-1,000", "1,000-5,000", ">5,000")) +
scale_fill_gradientn( colors = c("red", "white", " forest green"),
# low = "red", high = "white",
name = "Billions of Dollars of Reserves \n w/o Federal Funds",
limits = c(-20, 20),
breaks = c( -10, 0, 10),
label = scales::dollar) +
theme_void()+
labs( title = "Unemployment Insurance Trust Fund Reserve Levels", subtitle = "CARES & ARPA Federal Funds Not Included", caption = "Unemployment Insurance Trust Fund reserve levels \n without CARES or ARPA funds. Data Source: BLS? update later")states
values = "reserves_per_person_with_fed_money", regions = "states", labels = TRUE) +
scale_fill_continuous(low = "red", high = "yellow", name = "Millions of Reserves \n w/o Federal Funds", label = scales::comma)+
labs( title = "Unemployment Insurance Trust Fund Reserve Levels", subtitle = "CARES & ARPA Federal Funds Not Included", caption = "Unemployment Insurance Trust Fund reserve levels without CARES or ARPA funds. Data Source: __________") plot_usmap() recognizes state names and merges it for you to geography polygons.
plot_usmap(data = ui_states,
values = "reserves_per_person_with_fed_money",
regions = "states") +
scale_fill_gradientn(colors = c("red", "white", "forest green"),
limits = c(-1000, 1050),
n = 7,
# breaks = c(-500, 0, 500),
name ="Dollars per Person", label = scales::dollar)+
labs( title = "Unemployment Insurance Trust Fund Reserve Dollars Per Person \n After CARES & ARPA Federal Funds", caption = "Reserves balance was divided by state population to show average Unemployment Insurance Trust Fund reserve levels after states used CARES or ARPA funds.\n Data Source: BLS? update later") + theme(legend.position = "right")
plot_usmap(data = ui_states,
values = "reserves_per_person_without_fed_money",
regions = "states") +
scale_fill_gradientn(colors = c("red", "white", "forest green"),
limits = c(-1000, 1050),
n = 7,
# breaks = c(-500, 0, 500),
name ="Dollars per Person", label = scales::dollar)+
labs( title = "Unemployment Insurance Trust Fund Reserve Dollars Per Person \n CARES & ARPA Federal Funds Not Included", caption = "Reserves balance was divided by state population to show average Unemployment Insurance Trust Fund reserve levels with CARES or ARPA funds.\n Data Source: BLS? update later") + theme(legend.position = "right")
ui_states<- ui_states %>%
mutate(achm_cat = average_high_cost_multiple) %>%
mutate(achm_cat =
case_when(
achm_cat == 0 ~ "0",
achm_cat < 0.75 ~ " Below 0.75",
achm_cat > 0.75 & achm_cat < 1.25 ~ "0.75-1.25",
achm_cat > 1.25 ~ "Over 1.25"
),
achm_ordinal =
case_when(
achm_cat == 0 ~ 0, #"0",
achm_cat < 0.75 ~ 1, #"1",
achm_cat > 0.75 & achm_cat < 1.25 ~ 2, #"2",
achm_cat > 1.25 ~ 3 #"3"
)
)
plot_usmap(data = ui_states, values = "reserve_ratio", regions = "states", color = "black") +
scale_fill_gradientn(colors = c("red", "white", "forest green"), name ="Reserve Ratio", limits = c(0, 5.1))+
labs( title = "Reserve Ratio", caption = "Reserve ratios calculated after states used CARES or ARPA funds") +
theme(legend.position = "right")
plot_usmap(data = ui_states, values = "average_high_cost_multiple", regions = "states", color = "black") +
scale_fill_gradientn(colors = c("red", "white", "forest green"), name ="AHCM",
limits = c(0, 2.5)
)+
labs( title = "Average High Cost Multiple",
caption = "AHCM after CARES or ARPA funds. \n Data Source: BLS? update later") +
theme(legend.position = "right")
plot_usmap(data = ui_states, values = "achm_ordinal", regions = "states", color = "black") +
scale_fill_gradientn(name ="AHCM",
#,
colors = c("red", "orange", "light green", "forest green"),
labels = c("0", "Below 0.75","0.75-1.25", "Over 1.25")
#low = "red", high = " forest green",
#limits = c(0, 2.5)
)+
labs(title = "Average High Cost Multiple",
caption = "AHCM after CARES or ARPA funds. \n Data Source: BLS? update later") +
theme(legend.position = "right")