Transparent Utah | Data Quality Dashboard (BETA)

Overview

Download

Background

This dashboard was built to flag errors and quality concerns in Transparency reports submitted to the Office of the State Auditor. Our team has developed algorithms and metrics to pinpoint issues in the data that cause confusion for users of Transparent Utah. This dashboard is meant to be another useful tool for entities who submit to Transparent Utah to understand how the data they have submitted looks to the public. This tool can also help speed up the resolution of data quality issues since the visualizations and explanations can help uploaders understand how to rectify their scores.

Why is data quality important?

Bad data can confuse and impair decision making. Transparent Utah data is accessed by tens of thousands of Utahns every month. The data submitted to Transparent Utah is also used by the Utah State Legislature, the Federal Census Bureau, the Office of the State Auditor, and a variety of other research and government organizations.

What are the data quality scores?

Data Quality Scores

This section shows the data quality scores for ALL years and for ALL local government entities that submit to Transparent Utah. Each score is on a scale of 0-100%, with 0% being a poor score and 100% being a perfect score.

Filter a Government Level (optional)

Most Recent Fiscal Year Only

This section shows the data quality scores for the 2021 fiscal year and for ALL local government entities that submit to Transparent Utah. Click the "Get FY 2021 Only Scores" button to populate the score information below.

Get FY 2021 Only Scores

Filter a Government Level (optional)

Report Card

Download

Background

This page reports the selected entity's data quality scores. The goal is to have all the bars with a score of 100%.

Missing Reports Score

Download

Background

"Missing Reports" is a metric that checks if an entity is missing any required Transparency reports. This is the first metric to evaluate, because if there are missing quarters/years of data, then the missing data will impact expected amounts calculated in the other scores.

Note: This score only checks missing Transparency reports. To get a reporting status for other reports, please visit the compliance report.

Rubric

Missing Reports?

Rectify Score?

If you manage the financial reports for this entity, then you can improve this score by uploading your Transparency reports to https://reporting.auditor.utah.gov/. If a quarter of data did not have any transactions, then please contact one of our analysts at the Office of the State Auditor to give your entity a reporting exemption.

Contact:

Alexander Nielson alexnielson@utah.gov
Kramer McCausland kmccausland@utah.gov

Duplicate Reports Score

Download

Background

"Duplicate Reports" detects if there is duplicated data in the Transparency reports (also called batch uploads) the entity has submitted to Transparent Utah.

This metric uses algorithms to flag duplicated reports. Please note that not every transaction of a quarter is checked, but rather the report's totals. Even if the entity has a score of 100% there could still be duplicated transactions that were not flagged by our algorithms.

Rubric

Explanation

Uploads Summary

This table has many columns, so make sure to scroll to the right to see all the information.

Rectify Score?

If you manage the financial reports for this entity, then you can improve this score by correcting and/or deleting Transparency reports. Please review the detailed solution for each flag below.

Possible Duplicates

If your batch is flagged as a possible duplicate, you have two options to resolve the flag:

Delete the duplicated batch. Usually, the older (see upload date column) of the two batches should be deleted since most duplications are due to corrections/replacement batches.
If you believe your file was erroneously flagged, please contact one of our analysts and we can discuss setting up an exemption for that file. A common incorrect flag is for payroll reports which split up wages and benefits.

Payroll Posting Date Years Do Not Match Fiscal Year

Recreate the report with the correct posting date and fiscal year. Remember, the posting date should reflect the last date of the entity's fiscal year (e.g., 12/31 or 6/30).

Batch Has Multiple Fiscal Years

If your batch has multiple fiscal years, please split up the file by fiscal year and reupload. If you need help splitting up an old file, please reach out to our team for help.

Contact:

Alexander Nielson alexnielson@utah.gov
Kramer McCausland kmccausland@utah.gov

You can delete batches in the state reporting sytem found at https://reporting.auditor.utah.gov/.

AFR Comparison Score

Download

Background

Rubric

Charts

Rectify Score?

Ensure the validity of your Transparent Utah reports for the flagged fiscal years. Confirm there are no duplicate reports and that all data has correct Uniform Chart of Account codes. If there are still issues please reach out to one of our analysts so we can help determine why the AFR and TU are inconsistent. If there is a valid reason we will grant an exemption for your entity.

Contact:

Alexander Nielson alexnielson@utah.gov
Kramer McCausland kmccausland@utah.gov

You can delete batches in the state reporting sysytem found at https://reporting.auditor.utah.gov/.

UCA Validity Score

Download

Background

"UCA Validity" is a measure of how well the entity's data map into the Uniform Chart of Accounts (UCA). There are two uniform charts of accounts that are accepted:

Office of the State Auditor - Uniform Chart of Accounts - used by all entities except State of Utah, Higher Education, and LEAs.
Utah State Board of Education - Uniform Chart of Accounts - used by Local Education Agencies.

Rubric

Explanation

UCA Summary Table

This is table has many columns. Sroll to the right to see more columns.

Rectify Score?

To improve this score, you must correct the flagged/invalid UCA codes in all years of data. The primary goal is to improve your system going forward, and then we can discuss the best way to improve historical data. If you have access to historic years of data, we would like you to rebuild those reports with the correct UCA. If you do not have access to old years of data, we will work with you to find a solution. We will either correct the data for you, or grant an exemption.

You can find the list of valid OSA Uniform Chart of Accounts at this link
You can find the list of valid USBE Uniform Chart of Accounts at this link

Below is an explanation for each of the different types of flags/errors.

Contact:

Alexander Nielson alexnielson@utah.gov
Kramer McCausland kmccausland@utah.gov

You can delete batches in the state reporting sysytem found at https://reporting.auditor.utah.gov/.

Vendor Name % Score

Download

Background

"Vendor Name %" is a metric which compares the rate of vendor, payee, and employee names that have a value assigned to them. If the transaction has a value of "Not Applicable", "Not Provided", or "REDACTED" the transaction is considered to not have "detail". While there are valid reasons to not have a vendor name, like a journal voucher, most expenses are expected to have a valid vendor name. Each entity is compared against similar sized entities to confirm whether the percentage of transactions with detail is sufficiently high enough when compared to similar entities.

Rubric

Expense

Cluster Comparison

This plot compares the selected entity with entities of a similar size and government type. It visualizes other entites on the cluster and how well they have included detailed vendor names. The higher the percentage, then the higher the proportion of money that has a detailed vendor's name associated with it.

Black - Represents the selected entity's percentage of money that has a vendor name with detail. Hover over the black dots to get information about that fiscal year.
Green - The upper line is the maximum and the lower line is the median. Any black dots in this area receive a 100% for that year.
Orange - The upper line is the median and the lower line is is 25th percentile. Any black dots in this area receive a 50% for that year.
Red - The upper line is the 25th percentile and the lower line is the minimum. Any black dots in this area receive a 0% for that year.

Cluster Comparison Table

Vendor statistics

This table shows a further breakdown of vendor information for each year

The selected entity is too large to have a table of all vendors across all years displayed in the browser. Contact one of our analysts to get a customized report for your entity emailed to you.

Revenue

Cluster Comparison

This plot compares the selected entity with entities of a similar size and government type. It visualizes other entities on the cluster and how well they have included detailed payer names. The higher the percentage, the higher the proportion of money that has a payer's name associated with it. The majority of entities do not include a payer name, so it is not an issue if this this score is low.

Black - Represents the selected entity's percentage of money that has a vendor name with detail. Hover over the black dots to get information about that fiscal year.
Green - The upper line is the maximum and the lower line is the median. Any black dots in this area receive a 100% for that year.
Orange - The upper line is the median and the lower line is is 25th percentile. Any black dots in this area receive a 50% for that year.
Red - The upper line is the 25th percentile and the lower line is the minimum. Any black dots in this area receive a 0% for that year.

Cluster Comparison Table

Payer statistics

This table shows a further breakdown of payer information for each year. Most entities leave this field empty.

The selected entity is too large to have a table of all payers across all years displayed in the browser. Contact one of our analysts to get a customized report for your entity emailed to you.

Payroll

Cluster Comparison

This plot compares the selected entity with entities of a similar size and government type. It visualizes other entities on the cluster and how well they have included detailed employee names. The higher the percentage, the higher the proportion of money that has an employee's name associated with it. While some employee names can be protected, this should be a small percentage of the entity's workforce.

Black - Represents the selected entity's percentage of money that has a vendor name with detail. Hover over the black dots to get information about that fiscal year.
Green - The upper line is the maximum and the lower line is the median. Any black dots in this area receive a 100% for that year.
Orange - The upper line is the median and the lower line is is 25th percentile. Any black dots in this area receive a 50% for that year.
Red - The upper line is the 25th percentile and the lower line is the minimum. Any black dots in this area receive a 0% for that year.

Cluster Comparison Table

Employee Name statistics

This table shows a further breakdown of employee information for each year.

The selected entity is too large to have a table of all employees across all years displayed in the browser. Contact one of our analysts to get a customized report for your entity emailed to you.

Job Title

Missing Job Title Table

All Years

This section does not contribute to the score. It is just a helpful aggregate of all years.

Expense Pie Chart

Revenue Pie Chart

Payroll Pie Chart

Rectify Score?

Expense/Revenue/Payroll Score

These scores can be improved by adding a value other than "Not Applicable", "Not Provided", or "REDACTED" to the vendor/employee name field. A new batch will have to be submitted and the older one will need to be deleted for the score to improve.

Job Title Score

All employees must have a job title in the compensation report. Locate the batches that have employees without job titles and resubmit that batch so that job titles exist for all employees.

Exemption

If you believe your entity has been inaccurately flagged or there is a legitimate reason for a lack of detail, please contact one of the analysts to grant an exemption.

Contact:

Alexander Nielson alexnielson@utah.gov
Kramer McCausland kmccausland@utah.gov

You can delete batches in the state reporting sysytem found at https://reporting.auditor.utah.gov/.

reasonable

Yearly Total Change Score

Download

Background

"Yearly Total Change" is a metric which checks for large changes (spikes) in the year to year totals for expenditure, revenue, and payroll. There are two sections, 1) a section which checks all funds and 2) a section which does not include capital expense funds. Only section 1 contributes to an entity's score.

Rubric

All Funds

This section aggregates all fund types and looks for spikes. This is in contrast to the following section which compares Non Capital funds to avoid spikes due to construction.

Expense Yearly Totals

Revenue Yearly Totals

Payroll Yearly Totals

No Capital Funds

While this section does not contribute to the score, it can help analysts detect if a spike was due to a capital expense or some other data quality issue.

No Capital - Theoretical Rubric

Expense Yearly Totals

Revenue Yearly Totals

Totals per Student

This section also does not contribute to the score, it can help analysts detect if any spikes were due to an increase or decrease in student counts at a charter school.

Fund Type

All Funds

Exclude Capital Funds

Transaction Type

Expense

Revenue

Payroll

Expense vs Wages Total

This section also does not contribute to the score, it is used by our analysts to confirm if the totals in the payroll report match the total payroll expenses from the expense/revenue report.

Rectify Score?

The score can be improved by removing the spike of data. Common causes of the spikes are due to duplicate batches, so first confirm there is no duplicate or partial duplicate reports. If there are no duplicate batches, then investigate that year's data and check if there are any transactions with an amount that have a typo.

If you cannot determine why there is a spike please contact one of our analysts below.

Request an Exemption

If there is a legitimate reason for the spike in revenue/expense/payroll, please contact on of our analysts and we can grant an exemption for that year.

Contact:

Alexander Nielson alexnielson@utah.gov
Kramer McCausland kmccausland@utah.gov

You can delete batches in the state reporting sysytem found at https://reporting.auditor.utah.gov/.

Payroll Consistency Score

Download

Background

"Payroll Consistency" is a metric that identifies major changes in an employee's compensation.

The change must be larger than 30k to count as a medium or major difference.
"Major Difference" - occurs when the change is greater than 75% for both the previous and following year.
"Medium Difference" - occurs when the change is greater than 50% for both the previous and following year.

Rubric

Employee Check Section

Select from the drop-down list below to examine an employee's compensation. Flagged employees will be at the top of the list.

Rectify Score?

The score is improved by removing the spike for all flagged employee's compensation data. Common causes of the spikes are due to missing or duplicate batches, so first confirm there is no "duplicate" or "partial duplicate" or "missing" reports. If there are no duplicate batches, then investigate that year's data and check if there are any transactions with an amount that have a typo.

If you cannot determine why there is a spike please contact one of our analysts below.

Request an Exemption

If there is a legitimate reason for the spike in compensation, please contact on of our analysts and we can grant an exemption for that employee and year.

Contact:

Alexander Nielson alexnielson@utah.gov
Kramer McCausland kmccausland@utah.gov

You can delete batches in the state reporting sysytem found at https://reporting.auditor.utah.gov/.

Reversals Score

Download

Background

"Reversals" is a metric which compares the percentage of transactions that have a reversal (are negative). In Transparent Utah, both revenues and expenses are reported as positive unless there is a reversal in the General Ledger. This can cause issues since many entities have either revenue or expense negative for balance sheet calculations. This metric helps to flag any entities that may have accidentally kept data as negative while exporting from their system. Each entity is compared against similar size and government types to confirm whether the percentage of transactions with detail is sufficiently high enough when compared to similar entities.

Rubric

Expense

The bar chart shows the percentage of money that is a reversal by fiscal year.

The selected entity is too large to have a table of all reversals across all years displayed in the browser. Contact one of our analysts to get a customized report for your entity emailed to you.

Cluster Comparison

This plot compares the selected entity with entities of a similar size and government type. It visualizes other entities on the cluster and how much of their transactions' money is reversed. In general, the lower the better.

Black - Represents the selected entity's percentage of money that has is reversed. Hover over the black dots to get information about that fiscal year.
Green - The upper line is the median and the lower line is the minimum. Any black dots in this area receive a 100% for that year.
Orange - The upper line is the 75th percentile and the lower line is the median. Any black dots in this area receive a 50% for that year.
Red - The upper line is the maximum and the lower line is the 75th percentile. Any black dots in this area receive a 0% for that year.

Cluster Comparison Table

Revenue

The bar chart shows the percentage of money that is a reversal by fiscal year.

The selected entity is too large to have a table of all reversals across all years displayed in the browser. Contact one of our analysts to get a customized report for your entity emailed to you.

Cluster Comparison

Black - Represents the selected entity's percentage of money that has is reversed. Hover over the black dots to get information about that fiscal year.
Green - The upper line is the median and the lower line is the minimum. Any black dots in this area receive a 100% for that year.
Orange - The upper line is the 75th percentile and the lower line is the median. Any black dots in this area receive a 50% for that year.
Red - The upper line is the maximum and the lower line is the 75th percentile. Any black dots in this area receive a 0% for that year.

Cluster Comparison Table

Payroll

The bar chart shows the percentage of money that is a reversal by fiscal year.

The selected entity is too large to have a table of all reversals across all years displayed in the browser. Contact one of our analysts to get a customized report for your entity emailed to you.

Cluster Comparison

Black - Represents the selected entity's percentage of money that has is reversed. Hover over the black dots to get information about that fiscal year.
Green - The upper line is the median and the lower line is the minimum. Any black dots in this area receive a 100% for that year.
Orange - The upper line is the 75th percentile and the lower line is the median. Any black dots in this area receive a 50% for that year.
Red - The upper line is the maximum and the lower line is the 75th percentile. Any black dots in this area receive a 0% for that year.

Cluster Comparison Table

Rectify Score?

Expense/Revenue/Payroll Score

These scores can be improved by confirming all transactions are positive unless a reversal. Then a new report can be submitted to the state reporting system.

Exemption

If you believe your entity has been inaccurately flagged or there is a legitimate reason for a lack of detail, please contact one of the analysts to grant an exemption.

Contact:

Alexander Nielson alexnielson@utah.gov
Kramer McCausland kmccausland@utah.gov

You can delete batches in the state reporting sysytem found at https://reporting.auditor.utah.gov/.

USBE Consistency Score

Download

Background

USBE Consistency is a metric of how well the data in Transparent Utah (TU) matches the data reported to USBE. If the totals match (within an acceptable material difference), then it is likely that the data submitted to TU is correct.

If the USBE total is higher than TU's total, then this might imply that the submissions to TU are incomplete or data is missing.

If the USBE total is lower than TU's, then this might imply that data is duplicated in TU

Rubric

Charts

TU Total Expenditure VS. USBE Total Expenditure by Fiscal Year

Rectify Score?

Ensure the validity of your transparent utah reports for the flagged fiscal years. Confirm there are no duplicate reports and that all data has correct Uniform Chart of Account Codes. If there are still issues, please reach out to one of our analysts so we can help determine why the AFR and TU are inconsistent. If there is a valid reason, we will grant an exemption for your entity.

Contact:

Alexander Nielson alexnielson@utah.gov
Kramer McCausland kmccausland@utah.gov