Transparent Utah – Trend Analysis Methodology
Trend Analysis – Methodology
This tool visualizes two core fiscal trends over time: The Yearly Rolling Sum and the Yearly Percent Change. These can be shown for any chosen entity/category. We define these in the following ways.
Yearly Rolling Sum – The sum of all transactions in the chosen category over the last year. This is not the fiscal year or the calendar year, but the last 365 days. Additionally, for aesthetic reasons, in order to smooth out some of the sharp edges on the graph, we have chosen to take the last two weeks of values and report the average of those instead of reporting each date as an exact value.
Yearly Percent Change – This value comes directly from the Yearly Rolling Sum. The Yearly Percent Change is the percent change value that can be calculated by comparing two Yearly Rolling Sums that are exactly one year (365 days) apart. So, if an entity’s Rolling Sum is $10,000 on 01/01/2021 and then $11,000 on 01/01/2022, then that is an increase of $1,000 over an original value of $10,000. So we would report a Yearly Percent Change for that date (01/01/2022) as a 10% increase. Negative percentages, likewise, would represent a decrease.
Smoothing Function – The green line running through these graphs is a GAM smoothing function. This is included only for aesthetic purposes – so that the eye can more easily track the trend over time. It has no additional statistical significance other than being the closest quadratic or cubic function fit to the data in the local neighborhood for any segment of the curve.
Note: Since it takes a year for the Yearly Rolling Sum to “level out” and then another year to have comparable data points for the Yearly Percent Change, it takes two years for us to have a useable visualization. So, if you see the graph has a start date of 07/01/2019, that means that this is based on data going back to 07/01/2017.We rely on data from the last 5 years, which permits visualizations are limited to the last three years.
In addition to these main visualizations, we also provide an option to show a simple linear model (based on a Least Squares Regression). This is determined using the Yearly Rolling Sum and shows, if the pattern holds, what the Rolling Sum and the Percent Change may look like over the next 6 months. This model defaults to using available data from the last year, but we allow the user to adjust the start date farther into the past or closer to the present if they so desire. Please take this model with a large grain of salt. It is meant as a possible starting point for prediction and not as an endpoint. Many factors impact financial predictions that are not modelled by metrics represented here.
Using this linear model, we also provide an estimation of the Yearly Percent Change value six months into the future from the most recent received data. (Note: The State’s data is updated daily, but data from other municipalities is only received on a quarterly basis, so there will be some lag time between the present date and what’s available. For example, data from the January through March quarter isn’t due until the end of April, so you may be looking at this tool in mid-April and only be able to see data up to the end of December from the previous year.)
In addition to the core visualizations, we show two tables. The Year-to-Date Totals table shows how much money has accrued in the chosen category since the start of the fiscal year. For previous years, the number shown represents how much the entity had reported in that category at this time in that fiscal year.
The Previous Fiscal Years Table, found below the first, reports the total values for that category for previous fiscal years. In addition to the totals, we also provide a Percent Change column that shows how much these sums have changed year to year. For categories that are sub-categories of Total Revenue, we also supply a column that shows what percentage of the Total Revenue this category makes up.
Data Collection and Curation
All data is filtered to only show transactions with posting dates greater than what is required to show three full years of data. For example: if we visualize transactions from fiscal years 2020, 2021, and 2022—then all data is filtered to only show transactions starting with fiscal year 2018 (or the date 07/01/2017).
State Level Data
Total Revenue and Total Expenditure are transactions where type in the database equals Revenue (RV) or Expense (EX), respectively and where the org4 column is not equal to “TRS Public Treasurers Investment.” The Public Treasurers Investment, just as it sounds, are funds invested by the Treasurer, and including this transactions would be “double counting”. Total Difference is just the difference: Total Revenue minus Total Expenditure.
Tax Revenues are all revenues where cat3 is defined as “Tax Revenue.” Sales Tax Revenue, Income Tax Revenue, and Other Tax Revenue are subcategories of Tax Revenues. Sales Tax Revenue is determined by filtering to those transactions where cat4 is either “Sales Tax Revenue” or “Restricted Sales Tax.” Income Tax Revenue is found by filtering where cat4 is either “Corporate Income Tax” or “Individual Income Tax.” Other Tax Revenue is the subcategory of Tax Revenues where cat4 is none of the following: “Sales Tax Revenue,” “Restricted Sales Tax,” “Corporate Income Tax,” “Individual Income Tax,” or “Sales Tax Clearing.” Note that the Sales Tax Clearing account is an intermediary account and including it would incorrectly inflate the totals.
City and County Level Data
Total Revenue and Total Expenditure are just transactions where type is “RV” or “EX,” respectively. Total Difference is the difference of revenue and expenditure.
Tax Revenue is filtered for by looking at the first four digits of the reported account code. A transaction is considered a Tax Revenue if these digits equal 3001. This and the subsequent definitions come directly from Utah’s Uniform Chart of Accounts. A transaction is considered a Sales Tax transaction when the first 6 digits of the account code equal 300103 or when the phrase “sales tax,” “sales and use tax,” or “sales/use tax” are used in any of the category or description columns. A transaction is considered a Property Tax transaction when the first 6 digits of the account equal 300101 or when the phrase “property tax” is used in any of the category or description columns. Other taxes are transactions that are subcategories of Tax Revenue but which do not meet the specifications outlined above for being a Sales Tax or a Property Tax.
School Level Data
Total Revenue, Total Expenditure, and Total Difference are defined in the same way as for Cities and Counties.
Subcategories of Total Revenue are defined for schools in a different way than other data sources. Specifically, we divide the revenues by source. Local Sources are defined by reported revenue codes that start with the number 1. State Sources are defined by reported revenue codes that start with the number 3. Federal Sources are defined by reported revenue codes that start with the number 4. And Other sources are defined by revenue codes that start with neither 1, 3, or 4. This is based on the Uniform Chart of Accounts for schools posted by the Utah State Board of Education.
Limitations
Please be aware that this tool is simply a reflection of what is currently being reported in the Transparent Utah database. There are many limitations to this database, because it is a collection of self-reported data. The entities who do the reporting may have made mistakes in what they uploaded or in how they reported the UCA codes for their transactions, which may affect what is being reported here. The Transparency database is raw and unaudited and while we do strive to ensure its accuracy in a variety of ways, there is still much that can be improved. Please visit our Data Quality Dashboard for more details about flags that we have raised for any specific entity’s data.
Occasionally, totals may not match up with internal figures or with an entity’s Annual Financial Report. This can be caused by differing accounting practices and is expected to some degree. In addition to this, this tool is also stricter about fiscal year placement for any transaction than the uploaded reports—which may exacerbate this issue. This was a necessary adjustment so that we could calculate daily totals for our core visualizations. For example, an entity is allowed to report a transaction for a fiscal year where the posting date is outside of that fiscal year. So, they may say they received $10,000 in revenue on 07/01/2021 and report it in their 2021 fiscal year (even though that fiscal year technically ended on 06/30/2021). Whereas this tool necessarily has to rely on the posting date to determine fiscal year, so we would consider that $10,000 to be part of their 2022 fiscal year. This doesn’t tend to cause large differences, but we thought it was worth mentioning here.