Analysis of Total Cash Balances in Kansas School Districts
Cash balances statewide and by Unified School District for years 2007-2022
Kansas school districts report their total “Cash Balance” to the state every July 1. A separate article will explore other fund balances that contribute to this total cash balance.
Reproducible Research
Complete data provenance is provided. Instructions below show how to download the data from the Kansas Department of Education, or use files already downloaded and stored on GitHub. Electronic notebooks using the R language document all processing and analysis steps, which are also stored on GitHub. This article is an overview of the process.
Data files and R Studio Notebooks can be found on GitHub in the Cash-Balance folder.
Authoritative Data Source
Start on this Kansas Department of Education Data Central page:
Comparative Performance and Fiscal System (CPFS).
Make these selections:
Select report format: Excel
All Unified School Districts
General Fund
USD Budget Summary - Cash Balance
School Year: pick one “2007 - 2008” through “2021-2022 (Budgeted Data)”
Press “Display Report”
Save each of the 15 Excel files using a name such as yyyy-yyyy-Cash-Balance.xls
Data Exploration
Several explorations provide insights about the data. Notebook 00-Explore-Extraction.html provides the details.
Section 5.3 of the notebook shows USD Names are not unique statewide, since there is a “Blue Valley” school district in both Johnson and Riley counties.
The distribution of the USD Cash Balances is roughly log-normal, which means the values are so broad ($53 thousand to $117 million) they cannot be shown on a linear scale without distorting the lower values.
Here are selected quantiles and other stats related to the plot above:
There were 295 school districts in the 2007-2008 school year.
The min (Q0) and max (Q100) values were $52,725 [USD 432, Victoria (Ellis County)] and $117,434,404 [USD 259, Wichita (Sedgwick County)].
The median (Q50) value over all 295 districts was $1,448,033. The interquartile range, which is the middle 50%, ranges from $847,654 (Q25) to $2,785,958 (Q75).
The sum all the Cash Balances for all 295 school districts adds up to a staggering $1.2 billion!
“Long” vs “Wide” versions of the data
Working with the original 15 files in analysis can be a bit of a pain, but it’s fairly easy to combine data from all these files into “long” or “wide” Excel files. Notebook 10-Extract-Cash-Balances.html shows how this is done.
Data in a “long” format is an easier starting point using many of R’s ggplot2 plotting functions, but data in a “wide” format is often easier for humans to review.
Find these files on GitHub:
long format: Cash-Balances-by-School-District-LongFormat.xlsx [4309 data records]
wide format: Cash-Balances-by-School-District-WideFormat.xlsx [300 data records]. Useful to see cash balance time series for each USD.
Separate long and wide versions of quantiles by year are in additional files. These files will be used in graphics below.
Total Cash Balances from All School Districts
Graphics summarizing the Total Cash Balances were created using notebook 20-Plot-Cash-Balances-Summary.html.
KSDE data show that in 2007 all the school districts collectively had about $1.2 billion in their cash balances, but that grew to a staggering $2.4 billion in 2021. The numbers for 2022 are not yet available.
Notes:
Total Cash Balances were the sums of Cash Balances of all Unified School Districts in Kansas.
There were 295 school districts in 2007-2008, 293 in 2008-2009, and 286 from 2010-2011 till present.
Values of yearly July 1 Cash Balances by school district, as well as totals of all districts, can be viewed in file Cash-Balances-by-School-District-WideFormat-Edited.xlsx.
The chart shows Gov. Sam Brownback was fiscally responsible in not allowing school district cash balances to grow under his watch, unlike any of the other governor.
Has Gov. Laura Kelly questioned why four Kansas school districts need over $100 million in their cash balance? In particular, why does USD 259 in Wichita need a cash balance of over $230 million?
Variability of Cash Balances Across Kansas School Districts
The graphic above shows the sum of all school districts in the state, but how do the USD Cash Balances vary by school district? Is the pattern the same or different for large vs small school districts.
Notes:
The “smallest” values were negative in 2010-2011 and 2013-2014 and cannot be included as part of a log scale..
In 2010-2011 USD 473, Chapman (Dickinson County), showed a deficit of $18,473,393 on 7/1/2010 for Capital Outlay (fund 16), and a Total USD Cash Balance deficit of $16,107,103. See files Cash-Balances-2010-2011.xls and 2010-2011-Cash-Balance.xls.
In 2013-2014 USD 475, Geary County Schools, showed a deficit of 33,976,147 on 7/1/2013 for Federal (fund 07), and a Total USD Cash Balance deficit of $10,663,094. See files Cash-Balances-2013-2014.xls and 2013-2014-Cash-Balances.xls.
Quantile values (0.0, 0.1, 0.25, 0.5, 0.75, 0.9, 1.0) by School Year can be found in file Cash-Balance-Totals-and-Quantiles-by-School-Year-WideFormat.xlsx.
Related
The plots above were included in this article: