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.

Updated: Kansas hotel guest tax collections

Kansas hotel guest tax collections presented in an interactive visualization.

Updated with data through January 2019.

Cities and counties in Kansas may levy a transient guest tax collection on hotel guests. It is sometimes called a bed tax or guest tax. The tax is collected as a percentage of total room revenue, not the number of rooms or the rate charged for rooms. While the Kansas Department of Revenue collects the tax, the proceeds are returned to the cities or counties, except for a two percent processing fee. In Wichita the rate is six percent.

Of note, while Wichita is the largest city in Kansas, Overland Park collects the most hotel guest tax. Of the largest markets in Kansas, Wichita is usually one of the lowest-growth cities.

Click here to access the visualization.

Example from the visualization. Click for larger.


Notes

Visualization: Kansas hotel guest tax collections

Kansas hotel guest tax collections presented in an interactive visualization.

Cities and counties in Kansas may levy a transient guest tax collection on hotel guests. It is sometimes called a bed tax or guest tax. The tax is collected as a percentage of total room revenue, not the number of rooms or the rate charged for rooms. While the Kansas Department of Revenue collects the tax, the proceeds are returned to the cities or counties, except for a two percent processing fee. In Wichita the rate is six percent.

In some cases, jurisdictions may levy additional taxes that may not be paid to the Kansas Department of Revenue. This is the case with the Wichita city tourism fee, which took effect on January 1, 2015. This tax of 2.75% is paid directly to the city1, so it doesn’t appear in KDOR figures.

Also, jurisdictions may change the tax rate. The Kansas Department of Revenue maintains a list of taxes charged. 2

The visualization has three views of data. One is a table of collections, including percent change from the previous year. A line chart shows the dollar amount of collections. A second line chart shows collections indexed to a common starting point. This is useful for comparing the relative change in guest tax collections. These line charts show data as the average of the previous 12 months.

Examples of nondisclosure.
This data does not represent all hotels in Kansas. Confidentiality rules prohibit disclosure when a jurisdiction has a small number of hotels. In the nearby example, the value “C” is reported for Sedgwick County, indicating such non-disclosure. Obviously, there are hotels in Sedgwick County. But considering hotels in Sedgwick County that are not located in cities like Wichita, the number is too small to report, based on confidentiality guidelines. Similarly, for small cities, data is probably not available to the public.

Click here to access the visualization.

For more visualizations, click here.

Guest tax collections in largest hotel markets in Kansas, indexed change. Click for larger.

Notes

  1. City of Wichita ordinance 49-745. Available at http://www.wichita.gov/CityClerk/OrdanicesDocuments/49-745%20TBID%20Fee%20Ordinance.pdf.
  2. Kansas Department of Revenue. Transient Guest Tax Rates, Effective Dates, and Number of Active Accounts. Available at https://www.ksrevenue.org/pdf/tgratesfilers.pdf.

Updated: Metro area employment and unemployment

An interactive visualization of labor force, employment, and unemployment rate for all metropolitan areas in the United States.

Updated with data through January 2019. Click here to learn more about the visualization and to access it.

Example from the visualization, showing Wichita compared to all U.S. metropolitan areas. Click for larger.

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.

Example from the visualization. Click for larger.

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.

Example from the visualization. Click for larger.

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.

Kansas school salaries. Click for larger.
Kansas school salaries on a per-student basis. Click for larger.
Example from the visualization. Click for larger.

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.

Example from the visualization. Click for larger.

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.

Wichita and national GDP. Click for larger.
The GDP figures are real, meaning adjusted for inflation. They are annual numbers through 2017. The release this week also includes revisions for the prior year. In the case of Wichita, the revision was significant, with a loss in GDP being revised to a gain. See Wichita economy shrinks, and a revision for details.

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.

Example from the visualization. Click for larger.
Example from the visualization. Click for larger.

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.

Example from the visualization. Click for larger.
Example from the visualization. Click for larger.

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.

Example from the visualization. Click for larger.

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.

Example from the visualization, showing Kansas and other states. Click for larger.


Notes

  1. 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.

Example from the visualization. Click for larger.

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.

Example from the visualization. Click for larger.


Notes

  1. United States Census Bureau. Quarterly Summary of State & Local Tax Revenue (QTAX). Available at https://www.census.gov/programs-surveys/qtax.html.
  2. 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.

Example from the visualization. Click for larger.

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.

Example from the visualization. Click for larger.


Notes

  1. City of Wichita. Comprehensive Annual Financial Report. Available at http://www.wichita.gov/Finance/PensionDocuments/2015%20Pension%20CAFR.pdf.
  2. 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.

Local government employment by state. Click for larger.
Local government employment in education. Click for larger.
For all local government employees, Kansas had 50.59 per thousand residents in 2016, higher than all states (and areas) but the District of Columbia and Wyoming. These employees had an annual payroll of $2,141.16 per resident. Ten states were higher.

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.

State population vs. local government employment per resident. Click for larger.

— Notes

  1. Wikipedia. Hawai’i Department of Education. Available at https://en.wikipedia.org/wiki/Hawai%27i_Department_of_Education.