Now updated with data through the third quarter of 2018, quarterly state and local government tax collections. Data is from the United States Census Bureau and groups data by tax category. Click here to access.
State tax collections updated
Tag Archives: Visualizations
Following are visualizations of data. Many are interactive and created using Tableau Public. In some cases I’ve recorded myself using the visualization to tell a story, and all you have to do is watch.
Kansas agency expenditures
Data regarding State of Kansas agency spending presented in an interactive visualization.
The source of this data is KanView, the Kansas transparency portal, through its download center. Data from multiple years are combined into one database. Data starts with fiscal year 2011.
This visualization is experimental. I would appreciate feedback on views of this data that would be useful.
Click here to access the visualization.
Visualization: Kansas agency expenditures
Data regarding State of Kansas agency spending presented in an interactive visualization.
The source of this data is KanView, the Kansas transparency portal, through its download center. Data from multiple years are combined into one database. Data starts with fiscal year 2011.
Of this data, KanView advises: “Agency expenditure data is available by Agency Fund Type, Agency Primary Expenditure Accounts, and by Agency Program.” The various views of the visualization show this data arranged in these ways.
Regarding accounts, KanView offers this explanation:
State expenditures are classified at a primary, intermediate, and detail level account codes. These classifications facilitate the various levels of reporting detail required for budgetary, financial, management, or other reports.
Seven primary expenditure classifications are prescribed. Intermediate classifications are assigned within each primary classification. Within each intermediate classification is the detail classifications used to code accounting transactions.
The prescribed primary classifications are:
- Salaries and Wages: Amounts paid to, or on behalf of, elected or appointed state officials and employees.
-
Contractual Services: Payments for communications, freight and express, printing and advertising, rentals, repairing and servicing, employee travel expense reimbursement, utilities, and professional or other services.
-
Commodities: Payments for consumable supplies, maintenance materials and parts, and other miscellaneous purchases.
-
Capital Outlay: Payments for machinery, equipment, land, vehicles, buildings and other major purchases.
-
Grants, Claims and Shared Revenue: Disbursements for grants, claims, shared revenue and other related disbursements where the disbursing agency does not receive a direct service or tangible asset.
-
Debt Service: Payments of principal, interest and service charges on borrowed money.
-
Non-Expense Items: Disbursements for refunds, advances, investments and other disbursements not properly classified as governmental expenditures.
-
Expense Transfers: Agency use of transfer account codes is generally only on interfund transactions between state agencies. Transfers move cash from one fund to another fund within the State Treasury.
Regarding functions:
The function view identifies expenditures for high-level activities and programs of the State, upon selecting a high-level function, specific programs within this function can then be displayed for a particular state agency.
Major State Functions include: General Administration, Human Resources, Education, Public Safety, Agriculture and Natural Resources, Highways and Other Transportation, Health and Environment, Economic Development, Lottery, and Universities.
For funds, KanView explains:
A “fund” is the fundamental unit of accounting designed to demonstrate legal compliance and to aid financial management by segregating transactions related to certain governmental functions or activities. Fund View initially displays the major fund types denoting the high-level purpose of the underlying funds as classified in the statewide accounting and reporting system. Upon selecting a major fund type, specific funds can then be displayed for a particular state agency. Each agency fund is further segregated by individual accounts (for budgetary or other legal requirements) which provides an additional level of classification.
Valid major fund types and descriptions are:
- State General Fund: The primary operating fund of the State. It accounts for all financial resources of the State except those required to be accounted for in another fund. The state general fund is primarily supported by tax revenue.
-
Special Revenue Funds: Funds established for specific purposes normally specified by state statutes, or in the case of federal grants, for purposes specified by the federal government. These funds are primarily supported by user fees or grants.
-
Capital Projects Funds: Funds established to account for the acquisition and construction of major capital facilities other than those financed by proprietary funds and trust funds.
-
Debt Service Funds: Funds established to account for the accumulation of resources and the payment of long term debt principal and interest.
-
Enterprise Funds: Funds established to account for activities that are generally of a business nature where goods or services a sold to the general public or similar customer groups.
-
Internal Service Funds: Funds established to account for goods and services provided to other state agencies or internal departments on a cost-reimbursement basis.
-
Trust and Agency Funds: Trust funds contain monies received, held, and disbursed by the State acting as a trustee, agent, or custodian. Agency funds contain monies collected by the State as an agent and disbursed to other governments, businesses or individuals.
-
Component Units Funds: Funds of component unit(s). A component unit is a legally separate organization for which the primary government is financially accountable.
This visualization is experimental. I would appreciate feedback on views of this data that would be useful.
Click here to access the visualization.
For more visualizations, click here.
Visualization: Kansas vendor transactions
Data regarding State of Kansas payments to vendors.
The source of this data is KanView, the Kansas transparency portal, through its download center. Data from multiple years are combined into one database. Data starts with July 1, 2010.
KanView says this data shows “payments made to vendors from all state agencies and include the following key data elements: fiscal year, agency (Business Unit), account description, funding, vendor name, document number, payment date and amount.”
This visualization is experimental. I would appreciate feedback on view of this data that would be useful. The visualization may be slow to respond, as it holds 7.3 million rows of data.
Click here to access the visualization.
For more visualizations, click here.
Updated: Kansas tax receipts
Kansas tax receipts by category, presented in an interactive visualization.
The Kansas Division of the Budget publishes monthly statistics regarding tax collections. I’ve gathered these and present them in an interactive visualization. Updated with data through October 2018.
Click here to learn more and access the visualization.
Kansas school salaries
An interactive visualization of Kansas school salaries by district and category.
This visualization holds salaries of Kansas school superintendents, principals, and teachers. The visualization shows the average for each of these categories for each school district. The values are adjusted for inflation to the most current year values. Some data is presented on a per-pupil basis using full-time equivalent student counts.
The visualization includes both tables and charts. The source of the data is Kansas State Department of Education for salaries and enrollments, United States Bureau of Labor Statistics for price levels, and author’s calculations.
Click here to access the visualization.
Visualization: Kansas school salaries
An interactive visualization of Kansas school salaries by district and category.
This visualization holds salaries of Kansas school superintendents, principals, and teachers. The visualization shows the average for each employee category for each school district. The visualization includes both tables and charts. Some data is presented on a per-pupil basis using full-time equivalent student counts.
The values are adjusted for inflation to the most current year values.
KSDE says this data includes contracted salaries, including fringe benefits. More information from KSDE is available for superintendents, principals, and teachers.
Salary data is from Kansas State Department of Education. Inflation-adjusted data calculated using Consumer Price Index, all items, 1982-84=100 (series CUUR0000SA0) from U.S. Bureau of Labor Statistics. Visualization created using Tableau Public.
Click here to use the visualization.
For more visualizations, click here.
GDP by metropolitan area and component
An interactive visualization of gross domestic product by metropolitan area and industry.
The Bureau of Economic Analysis, an agency of the United States Department of Commerce, gathers data about economic output, known as gross domestic product. The visualization presented here presents this data in tabular and graphic form.
A nearby example from the visualization compares Wichita metro GDP growth to that of the nation’s metropolitan areas.
Click here to access this visualization.
Visualization: GDP by metropolitan area and component
An interactive visualization of gross domestic product by metropolitan area and industry.
The Bureau of Economic Analysis, an agency of the United States Department of Commerce, gathers data about economic output, known as gross domestic product. The visualization presented here presents this data in tabular and graphic form.
The GDP figures are real, meaning adjusted for inflation. They are annual numbers through 2017.
Source of data is Bureau of Economic Analysis, an agency of the United States Department of Commerce.
Click here to access this visualization.
For more visualizations, click here.
Kansas tax receipts
Kansas tax receipts by category, presented in an interactive visualization.
The Kansas Division of the Budget publishes monthly statistics regarding tax collections. I’ve gathered these and present them in an interactive visualization.
In the nearby example from the visualization, we can see the rising trend in individual income taxes, due to the tax increase passed by the Kansas Legislature.
Click here to learn more and access the visualization.
State government employees in Kansas
Kansas has more state government employees per resident than most states, and the trend is rising.
Each year the United States Census Bureau surveys federal, state, and local government civilian employees. 1 The amount of payroll for a single month (March) is also recorded. In this case, I’ve made the data for state government employees available in an interactive visualization.
For 2016, Kansas had 17.90 full-time equivalent state government employees per thousand residents. This ranked 15th among the states. These employees resulted in payroll cost of $979 per resident, which is 21st among the states.
Nearby is an example from the visualization showing state government employment count (full-time equivalent) per thousand residents for Kansas and some nearby states. It shows total employment, and in addition, education employment and hospital employment. (Since nearly all employees in Kansas elementary and secondary schools are employees of local government, not the state, the employees shown are working in higher education. See below for visualizations of local government employees.)
Two things are evident: The level of employment in Kansas is generally higher than the other states, and the trend in Kansas is rising when many states are level or declining. This data counters the story often told, which is that state government employment has been slashed.
If we look at data for state and local government employees, the conclusions are nearly the same.
Click here to learn more and access the visualization.
There are separate visualizations for local government employees only, and also for state and local government employees together. Click on state and local government employment of local government employment by state and function.
—
Notes
- United States Census Bureau. Annual Survey of Public Employment & Payroll (ASPEP). Available at https://www.census.gov/programs-surveys/apes.html. ↩
Visualization: State government employment by state and function
An interactive visualization of state government employment, grouped by state and function.
These are state government employees only. Local and federal government employees are not included.
Source of data is United States Census Bureau, Local Government Employment and Payroll Data: March 2016. The program’s page is Annual Survey of Public Employment & Payroll (ASPEP).
In the visualization, I’ve multiplied the March payroll number by 12 to produce an approximation of annual payroll. Using each state’s population for each year, I’ve also computed the annual payroll on a per-resident basis and the number of full-time equivalent (FTE) employees per thousand residents.
Click here to access the visualization.
For more visualizations, click here.
Kansas state and local taxes
Among nearby states, Kansas collects a lot of taxes, on a per-resident basis.
The United States Census Bureau collects data from the states regarding tax collections. Some data is available for each quarter subdivided by category.
From the first quarter of 2011 to the first quarter of 2018, Kansas and its local governmental units collected an average of $681 per quarter per resident in taxes. Of nearby states and a few others, Arkansas and Iowa had higher values, and Iowa is higher by only one percent.
Some states had lower values, such as Colorado at $565 per quarter per resident (17.0 percent less than Kansas), Texas and Missouri both at $486 (28.6 percent less), and Florida at $470 (31.0 percent less).
To learn more about this visualization and create your own, click here.
Visualization: Quarterly state and local government tax collections
State and local tax collections presented in an interactive visualization.
The United States Census Bureau provides tax collection data for states and local governments on a quarterly basis. The data is provided by category such as total taxes, sales taxes, personal income taxes, etc. 1
The Bureau describes the program as:
The Quarterly Summary of State and Local Government Tax Revenue provides quarterly estimates of state and local government tax revenue at a national level, as well as detailed tax revenue data for individual states. The information contained in this survey is the most current information available on a nationwide basis for government tax collections.
While the state data records are ultimately from state government sources, the classification of taxes among the different categories is entirely the responsibility of the Census Bureau. Therefore, tax classification might not reflect the actual classification or presentation as requested by the various state government respondents. 2
This data is not adjusted for inflation. Some taxes, such a property taxes, show large seasonal variations.
I’ve gathered the data and have made it available in an interactive visualization. There are several views of the data, represented by the tabs at the top. You may select a time frame, the states that appear, and the tax categories that appear.
Click here to access the visualization.
For more visualizations, click here.
—
Notes
- United States Census Bureau. Quarterly Summary of State & Local Tax Revenue (QTAX). Available at https://www.census.gov/programs-surveys/qtax.html. ↩
- United States Census Bureau. About Quarterly Summary of State & Local Tax Revenue. Available at https://www.census.gov/programs-surveys/qtax/about.html. ↩
Wichita checkbook updated
Wichita spending data presented as a summary, and as a list.
As part of an ongoing transparency project, I asked the City of Wichita for check register data. I’ve made the data available in a visualization using Tableau Public. This visualization is updated with data through August 31, 2018.
To learn more about this data and use the visualization, click here.
Visualization: Wichita check register
Wichita spending data presented as a summary, and as a list.
As part of an ongoing transparency project, I asked the City of Wichita for check register data. I’ve made the data available in a visualization using Tableau Public. Click here to access the visualization. To access a simple list in csv format, click here.
For more visualizations, click here.
Analyzing this data requires a bit of local knowledge. For example, there is a vendor named “Visit Wichita” that started to receive monthly payments in March 2015. What about payments for January and February? Those were made to a vendor named “Go Wichita,” which changed its name to “Visit Wichita.”
Similarly, there are payments made to both “Westar Energy” and “Westar Energy — EDI.” These are the same entities, just as “Visit Wichita” and “Go Wichita” are the same entity. To the city’s credit, the matching pairs have the same vendor number, which is good. But resolving this requires a different level of analysis.
Also, the purpose of payments may not be evident from the payee’s name. For example, “State Street” is the payee that has received the most money over the time period covered by this data. It is a custodial bank for the city’s retirement systems. 1
Of note, there are many checks issued in amounts $20 or less. Bank of America has estimated that the total cost of sending a business check ranges from $4 to $20. 2
It is by now routine for governmental agencies to post spending data like this, but not at the City of Wichita. Upon inquiry, city officials told me that the present financial management system “does not include many modern system features such as an ‘open checkbook.’” An “open checkbook” refers to a modern web interface where citizens can query for specific data and perhaps perform other analysis. An example is Denver’s open checkbook.
We’ve been promised a modern system for many years.
While the next-generation Wichita financial system will probably have such a feature, there’s no reason why citizens can’t experience some of the benefits now. The spreadsheet of spending data could easily be posted on the city’s website on a monthly basis. People like myself will take that data and make it more useful. The city has demonstrated that it is able to post documents to its website, so there is no reason why this should not be happening.
—
Notes
- City of Wichita. Comprehensive Annual Financial Report. Available at http://www.wichita.gov/Finance/PensionDocuments/2015%20Pension%20CAFR.pdf. ↩
- Wall Street Journal. U.S. Companies Cling to Writing Paper Checks. Available at https://www.wsj.com/articles/u-s-companies-cling-to-writing-paper-checks-1394494772. ↩
Local government employment in Kansas
Kansas has nearly the highest number of local government employees per resident, compared to other states.
Considering elementary and secondary education, Kansas had 30.03 such employees per thousand residents. This was higher than all states but Vermont and Wyoming. The payroll for these employees was $1,150.85 per resident, with eleven states above Kansas.
Kansas is a small state in terms of population. Might small states have higher needs for employees on a per-resident basis? A plot of employees vs. population shows nearly no relationship between the two.
These are local government employees only. State and federal government employees are not included.
Of note, Hawaii has no local employees in elementary and secondary education, as it has one school district which is run by the state. 1
The source of this data is the United States Census Bureau. I’ve gathered it and placed in in an interactive visualization. Click here to learn about the visualization and use it to make your own charts and tables.
— Notes
- Wikipedia. Hawai’i Department of Education. Available at https://en.wikipedia.org/wiki/Hawai%27i_Department_of_Education. ↩
Visualization: Local government employment by state and function
An interactive visualization of local government employment, grouped by state and function.
These are local government employees only. State and federal government employees are not included.
Source of data is United States Census Bureau, Local Government Employment and Payroll Data: March 2016. The program’s page is Annual Survey of Public Employment & Payroll (ASPEP).
In the visualization, I’ve multiplied the March payroll number by 12 to produce an approximation of annual payroll. Using each state’s population for each year, I’ve also computed the annual payroll on a per-resident basis and the number of full-time equivalent (FTE) employees per thousand residents.
Click here to access the visualization.
For more visualizations, click here.
Visualization: School spending in the states
School spending in the states, presented in an interactive visualization.
The Elementary/Secondary Information System (ElSi) is a project of National Center for Education Statistics (NCES). NCES is “the primary federal entity for collecting and analyzing data related to education in the U.S. and other nations. NCES is located within the U.S. Department of Education and the Institute of Education Sciences.”1 Here is data from ElSi regarding per-pupil revenue and spending in the states.
Near the end of this article are definitions of each measure. There are measures for total expenditures and total current expenditures. The major difference is that the current expenditures measure does not include the cost of construction of schools and the expense of debt associated with that.
Of note, the values for “United States” are the average of the values for the states, computed with equal weight without regard for the total spending or number of students in each state.
As of the date of publication, data was available through the school year ending in 2015.
Since these data series cover substantial periods of time, I’ve also used the Consumer Price Index2 to adjust the figures for the effects of inflation. Each measure has a companion whose name starts with “i.” This is the value adjusted for inflation, based on the CPI. You may choose to view the values as reported by ElSi, which are in current dollars. These are the values not adjusted for inflation. Or, you may use the “i.” measures, which are in constant dollars.3
This data is presented in an interactive visualization created using Tableau Public. There are three views of this data, accessed by tabs along the top. To access the visualization, click here.
For more visualizations, click here.
Definitions of measures
Total Revenues (TR) per Pupil (MEMBR) [State Finance]
Total revenues per student ate the total revenues from all sources (tr) divided by the fall membership as reported in the state finance file.
Total Expenditures (TE11+E4D+E7A1) per Pupil (MEMBR) [State Finance]
This is the Total Expenditures (Digest) divided by the fall membership as reported in the state finance file. The Total Expenditures (Digest) is the subtotal of Direct State Support Expenditures for Private Schools (e4d).
Total Current Expenditures for Public El-Sec (TE5) per Pupil (MEMBR) [State Finance]
This is the total current expenditures for public elementary and secondary education (te5) divided by the fall membership as reported in the state finance file. The Expenditures for equipment, non-public education, school construction, debt financing and community services are excluded from this data item.
Local Revenues (STR1+R2) per Pupil (MEMBR) [State Finance]
Local revenues per student are the total of all local revenue categories (strl and r2) divided by the fall membership as reported in the state finance file. Local revenues are raised and allocated by local governments.
State Revenues (R3) per Pupil (MEMBR) [State Finance]
State revenues per student are revenues received by the LEAs from the state (r3). divided by the fall membership as reported in the state finance file.
Federal Revenues (STR4) per Pupil (MEMBR) [State Finance]
Federal revenues per student are federal revenues (str4) divided by the fall membership as reported in the state finance file.
—
Notes
- National Center for Education Statistics. About us. nces.ed.gov/about/. ↩
- United States Bureau of Labor Statistics. Consumer Price Index. www.bls.gov/cpi/. ↩
- The U.S. Census Bureau explains: ” Constant-dollar values represent an effort to remove the effects of price changes from statistical series reported in dollar terms. The result is a series as it would presumably exist if prices were the same throughout as they were in the base year-in other words, as if the dollar had constant purchasing power.” Current versus Constant (or Real) Dollars. www.census.gov/topics/income-poverty/income/guidance/current-vs-constant-dollars.html. ↩
Visualization: Kansas school spending by district
An interactive visualization of spending by Kansas school districts.
The accompanying visualization holds both nominal dollar amounts and amounts adjusted for inflation to reflect 2018 dollars. Data includes state aid, local aid, federal aid, and total spending for each school district, per pupil. The visualization includes both tables and charts.
Spending and revenue data is from Kansas State Department of Education. Inflation-adjusted data calculated using Consumer Price Index, all items, 1982-84=100 (series CUUR0000SA0) from U.S. Bureau of Labor Statistics. Values are adjusted to 2018. Visualization created using Tableau Public.
Click here to use the visualization.
For more visualizations, click here.
