After exporting the expenditures and revenues that were related to Refund funds (identified by searching for string containing “refund” and then also identifying the fund number because, in early years, data requested did not have fund labels associated with fund numbers. Yes, you could do just the fund numbers in the command, but if anything changes, looking for strings that contain “refund” would hopefully alert the coder that something new existed which would otherwise be more difficult if only using fund numbers).
Source Code
---title: 'Tax Refunds'format: html: toc: true code-fold: true df-print: paged---# Tax Refunds {#sec-taxrefunds}```{r setup, warning=FALSE, message=FALSE}library(tidyverse)library(haven)library(formatR)library(lubridate)library(scales)library(kableExtra)library(ggplot2)library(readxl)library(tidyverse)library(data.table)library(janitor)library(cmapplot)theme_set(theme_classic() )knitr::opts_chunk$set(echo = TRUE, warning = FALSE, message = FALSE )current_year <- 2025 # fiscal year, not calendar yearpast_year=current_year-1rev_temp <- read_csv(paste0("../../Fiscal Futures IGPA/Fiscal-Future-Topics/data/FY", current_year, " Files/rev_temp.csv"))exp_temp <- read_csv(paste0("../../Fiscal Futures IGPA/Fiscal-Future-Topics/data/FY", current_year, " Files/exp_temp.csv"))``````{r}#| column: pageexp_temp %>%mutate(obj_type =str_sub(obj_seq_type, 1, 1),obj_2dig =str_sub(obj_seq_type, 1, 2),obj_of_exp =str_sub(obj_seq_type, 1, 3),obj_of_exp =str_pad(obj_of_exp, width =4, "right", pad ="0"),obj_expenditure_label =case_when( obj_of_exp =="9900"~"9900 - One-Time Rebates", obj_of_exp =="9910"~"9910 - Income Tax Refunds", obj_of_exp =="9920"~"9920 - Other Tax Refunds", obj_of_exp =="9930"~"9930 - Other Refunds", obj_of_exp >9930~"9931 & Up - Other Refunds") ) %>%filter(#fy == 2023 & object >=9000#& object <=9930# & agency == 492 ) %>%summarize(Refunds =round(sum(expenditure)), .by =c(fy, obj_expenditure_label )) %>%pivot_wider (names_from ="obj_expenditure_label", values_from ="Refunds") %>%arrange(fy) ``````{r}tax_refund_long <- exp_temp %>%# fund != "0401" # removes State Trust Fundsfilter(fund !="0401"& (object =="9900"| object=="9910"|object=="9921"|object=="9923"|object=="9925")) %>%# keeps these objects which represent revenue, insurance, treasurer,and financial and professional reg tax refundsmutate(refund =case_when( object =="9900"~"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_06: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." )``````{r}#| column: page#| label: tbl-DOR-taxrefunds#| tbl-cap: Only refunds from Department of Revenue# only income tax refund funds from Department of Revenuerev_refundfund <- rev_temp %>%filter(agency!="799"&!rev_type %in%c("99", "98", "72") & fund =="0278"& agency =="492") %>%reframe(Refund_Rev =sum(receipts), .by = fy)rev_temp %>%filter(agency!="799"& fund =="0278"&!rev_type %in%c("99", "98", "72")) %>%reframe(Refund_Rev =sum(receipts), .by =c(fy, source_name_AWM)) %>%pivot_wider(names_from = source_name_AWM, values_from = Refund_Rev) %>% kable %>%kable_classic()exp_refundfund <- exp_temp %>%filter(agency!="799"& fund =="0278"& agency =="492") %>%reframe(Refund_Exp =sum(expenditure), .by = fy)exp_temp %>%filter(agency!="799"& fund =="0278") %>%reframe(Refund_Exp =sum(expenditure), .by =c(fy, sequence, object)) %>%pivot_wider(names_from = sequence, values_from = Refund_Exp) %>% kable %>%kable_classic()exp_temp %>%filter(agency!="799"& fund =="0278"& object >=9000) %>%reframe(Refund_Exp =sum(expenditure), .by =c(fy, obj_seq_type)) %>%pivot_wider(names_from = obj_seq_type, values_from = Refund_Exp) %>% kable %>%kable_classic()exp_temp %>%filter(agency!="799"& fund =="0278"& object >=9000) %>%# mutate(wh_approp_name = ifelse(is.na(wh_approp_name), "INCOME TAX REFUNDS",# ifelse(wh_approp_name == "INCOME TAX REFUND (RETURN DD)", "INCOME TAX REFUNDS (RETURN DD)", wh_approp_name) )#) %>% reframe(Refund_Exp =sum(expenditure), .by =c(fy, wh_approp_name)) %>%pivot_wider(names_from = wh_approp_name, values_from = Refund_Exp) %>%mutate(TotalRefund =sum(`INCOME TAX REFUNDS`+`INCOME TAX REFUNDS (RETURN DD)`+`INCOME TAX REFUNDS (UNITARY)`+`INCOME TAX REFUNDS (NON-UNI)`+`ONE TIME TAX REBATES`, na.rm=TRUE ) ) %>% kable %>%kable_classic()left_join(rev_refundfund, exp_refundfund) %>%mutate(Refund_diff = Refund_Rev - Refund_Exp) %>% kable %>%kable_classic()``````{r}#| layout-ncol: 2#| column: page# get all individual and corporate income tax revenue items income_rev <- rev_temp %>%filter(!rev_type %in%c("99", "98", "72")) %>%filter(rev_type %in%c( "02", "03" ) )rev_temp %>%# no transfers, bond proceeds, prior year refundsfilter(!rev_type %in%c("99", "98", "72")) %>%filter(rev_type %in%c( "02") ) %>%group_by(fy) %>%summarize("Ind. Inc. Receipts, gross refunds & PPRT"=sum(receipts))rev_temp %>%filter(!rev_type %in%c("99", "98", "72") & fund !="0278"& rev_type %in%c( "02") ) %>%group_by(fy) %>%summarize(" Ind. Inc. Receipts, gross PPRT, net Refunds"=sum(receipts))``````{r}#| label: tbl-taxrefunds-newcode-revenue#| column: screen-insettax_refunds_rev <- rev_temp %>%filter(!rev_type %in%c("99", "98", "72")) %>%filter(str_detect(fund_name_ab, "REFUND") |# search string for refund, just in case fund %in%c("0121", "0278", "0378") #| # refund funds# source %in% c("1905", "1906", "1923", "2138") # amnesty rev sources )tax_refunds_rev %>%#filter(rev_type_name == )group_by(fy, rev_type, source, source_name_AWM ) %>%arrange(fy) %>%summarize(refunds_rev =round(sum(receipts)) ) %>%pivot_wider(id_cols =c(rev_type, source, source_name_AWM), names_from = fy, values_from = refunds_rev) %>%arrange(rev_type)tax_refunds_rev %>%group_by(fy, rev_type, source, source_name_AWM, fund ) %>%arrange(fy) %>%summarize(refunds_rev =round(sum(receipts)) ) %>%pivot_wider(id_cols =c(rev_type, source, source_name_AWM, fund), names_from = fy, values_from = refunds_rev)# expenditure Objects related to refunds refund_expenses <- exp_temp %>%filter( #agency == 492 | (object >=9900& object <=9941) & expenditure >0) refund_expenses %>%group_by(object, fy) %>%summarize(exp =round(sum(expenditure))) %>%pivot_wider(names_from ="fy", values_from ="exp")# excellist <- list(income_rev, tax_refunds_exp, tax_refunds_rev)# writexl::write_xlsx(excellist, "taxrefunds_check.xlsx")``````{r}#| label: tbl-taxrefunds-newcode-exp#| column: screen-insetexp_temp <- exp_temp %>%mutate(wh_approp_name =if_else(obj_seq_type =="99100008", "INCOME TAX REFUNDS (UNITARY)", if_else(obj_seq_type =="99100108", "INCOME TAX REFUNDS (NON-UNI)", if_else(obj_seq_type =="99100208", "INCOME TAX REFUNDS (RETURN DD)", as.character(wh_approp_name)), as.character(wh_approp_name))))tax_refunds_exp <- exp_temp %>%filter(agency !=799) %>%filter(str_detect(fund_name_ab, "REFUND")| fund %in%c(#"0121", "0278"# "0378", "0380", "0671" ) )tax_refunds_exp %>%group_by(fy, fund, fund_name_ab) %>%summarize(refunds_exp =sum(expenditure)) %>%pivot_wider(id_cols =c(fund, fund_name_ab), names_from = fy, values_from = refunds_exp)# Matches IOC# Includes the $1 billion one-time abatement in FY 2023tax_refunds_exp %>%group_by(fy, obj_seq_type, wh_approp_name) %>%summarize(refunds_exp =sum(expenditure)) %>%pivot_wider(names_from = fy, values_from = refunds_exp)``````{r}#| label: tbl-taxrefunds-groupedtables#| column: screen-inset#| include: FALSEtax_refunds_exp %>%filter(agency !="799") %>%group_by(fy, appr_org, org_name, fund, fund_name_ab) %>%summarize(refunds_exp =sum(expenditure)) %>%pivot_wider(id_cols =c(appr_org, org_name, fund, fund_name_ab), names_from = fy, values_from = refunds_exp)tax_refunds_exp %>%filter(agency !="799") %>%group_by(fy, object, fund, fund_name_ab) %>%summarize(refunds_exp =sum(expenditure)) %>%pivot_wider(id_cols =c(object, fund, fund_name_ab), names_from = fy, values_from = refunds_exp) %>%arrange(object)# excellist <- list(tax_refunds_exp, tax_refunds_rev)# writexl::write_xlsx(excellist, "taxrefunds_check.xlsx")```After exporting the expenditures and revenues that were related to Refund funds (identified by searching for string containing "refund" and then also identifying the fund number because, in early years, data requested did not have fund labels associated with fund numbers. Yes, you could do just the fund numbers in the command, but if anything changes, looking for strings that contain "refund" would hopefully alert the coder that something new existed which would otherwise be more difficult if only using fund numbers).
