I will be talking a lot about Transparency in my blogs, a word that is overused and even misused and abused in government. Yes, there are efforts made by governments and businesses to be transparent. Even the Texas State Comptroller awards programs are designed to promote transparency. Many of those efforts produce translucency more than transparency. I will try to explain and give examples in this blog and in many more to come. However, I must tell you something. I was handed a gift just a few days ago. While the current and past state comptrollers have promoted transparency in local government for many years, a new program has been launched called “Transparency Stars: Recognizing Local Transparency Achievements.”
The Texas Comptroller of Public Accounts is proud to announce the upcoming Transparency Stars program, recognizing local governments for going above and beyond in their transparency efforts. Our office will recognize government entities that accomplish the following:
• open their books not only in their traditional finances, but also in the areas of contracts and procurement, economic development, public pensions and debt obligations.
• provide clear and meaningful financial information not only by posting financial documents, but also through summaries, visualizations, downloadable data and other relevant information.
The emphasis on the last few words is mine. I would have to look, but I am pretty sure my writings and presentations on aspects of this subject started 20-25 years ago. Local governments are good at generating data. Operating data. But only in recent years have there been tools and efforts to turn operating data into management data. Some will recall that in addition to 1) summaries and 2) visualizations, I have emphasized 3) exceptions, 4) comparisons, 5) rankings, 6) drilldowns, 7) trends and 8) written explanations.
And even with data presentation improvements, the real test is to determine if the critical message is delivered and understood such as when the Mayor of Dallas pierces through the data and staff babble to declare, and I paraphrase, “the condition of our roadway system STINKS!”
An Example of Transparency Efforts: The Check Register
Many cities now publish an online listing of all of their checks. On July 19, 2015 I went online at the City of McKinney’s Web site and found they had a listing of checks back to 2011. Each year was in a separate file, with 2011 in a PDF format and the other years in Excel. Then I found that over $8 million were written to the bank card company handling the purchasing card program for the City. Of more concern than that to me were a number of vendor payments that were redacted. I expressed my concerns in an email letter to the Interim City Manager and Finance Director attached on that date.
To their credit, they now have an Interactive Financial Reporting Tool that came online in August 2015. As of a couple of weeks ago, they had the old version I was using and have now taken it down. Please note the new check registers and even the payroll register found at https://www.mckinneytexas.org/index.aspx?NID=213. The capability to see more information is truly impressive. They have broken down the PCard spending! They have also UN-redacted many (but not all) of the vendor purchases I had questioned. My compliments to the City of McKinney staff efforts to improve Transparency.
But Does This Effort Accomplish Transparency & Understandability?
The City is providing a check register that almost quadrupled the disclosure of details with over 215,000 pieces of data. I have worn out this quip, but I will continue to use it: as much as I love numbers, I cannot necessarily spot an anomaly by scanning pages of data by eye, even though the City’s sort feature makes it easier to do some ranking. One of the Comptroller’s criteria (downloadable) is of great benefit on the McKinney site. However, it is lacking in looking for the most critical areas of spending that will surface only when summarizing data, then sorting.
For operating data to become management data, somebody has to transform listings into summaries. Even then there must be an appreciation for the Pareto Principle (the 80/20 rule), which simply means I first want to examine the fewest number of (people, problems, items, etc.) that make up the largest impact (dollars, overtime, moral problems, etc). You would still want to see information portrayed for comparison purposes, such as over a number of years, to spot trends.
Excel Pivot Tables to the Rescue
To explain what I am describing, I have a link to an Excel spreadsheet that takes the good McKinney work and addresses the Comptroller’s new program to make it better. The first tab is the DOWNLOAD of the 215,193 expenditure records from 2010 through January 2016. The second tab, PIVOT BY PAYEE, is where you can see a drilldown that includes 1) the description the City provides and 2) the Fund it was charged to. All elements are sorted from the largest to the small dollars.
Please appreciate this point, whether you are a financial analyst or a city councilmember: The pivot tables have organized (summarized) 215,193 spending elements totaling $998,454,719 into a meaningful presentation that allows you, the reader, to scan the entire $1 billion picture quickly and then to drill down into details at your desired level of information. Folks, that’s powerful. That’s a giant step toward Transparency.
What Do We Do Now?
That’s simple. Read with curiosity. Ask questions. If you are a McKinney resident, this is your money. If you are a councilmember or community leader, learn about where the money goes. There’s a huge amount of discussion in closed meetings that you can’t be privy to, but when the money goes out the door – it’s your business. I can only think of one acceptable condition when the amount and purpose of money going out the door should not be disclosed – the garnishment of wages and payments related to child support. There may be more excuses for redactions but not in my book.
I do have several questions for my city council and staff after preparing this analysis and blog. Actually, I have an Open Records Request for some of the items and should be hearing back soon. Here is my challenge to you if you are a city official: why would you wait for somebody like me to surprise you with a question or perhaps even a finding that you should have already asked and known? The reason I say this is because the dreadful fact is that city officials don’t read their own information. Raise your hand if you have actually read your CAFR in depth. Heck, most cities have been producing a check register for years. As a council, are you looking at that information? The City of McKinney is averaging $104,000 on audit fees. Are you getting more than a drive-by explanation of what’s in it? More coming in a few weeks.
The reality is that the once unavailability of tools for digesting millions of records, slicing and dicing are now available to just about everyone. If you have Excel, you can download years of check registers and create pivot tables in a matter of minutes – no exaggeration.
The Excel spreadsheet is quite large, too big to attach. Therefore, you will need to download from www.citybase.net/downloads/mckinneycheckregisters.xlsm to see the details as well as the construction of the pivot tables. However, I have attached a PDF of the spreadsheet at the vendor/payee level. 
Oh, and regarding Professor Pareto , you will soon learn the importance of his rule when you continue to be faced with terabytes of data, a meeting agenda that just doesn’t seem to end, or which house repair you are going to tackle first? In this age of information overload, it is the only path to control and sanity. All you have to look at in the spreadsheet is the first 448 items (11.3%) of the vendors/payees that account for 96.4% of the near $1 billion of spending. LFM
Let's Talk About It, Lewis! 