Two years ago, I thought Excel was a meatpacking plant somewhere in central Kansas. I was spreadsheet ignorant.
This was particularly startling because I had worked on stories with other reporters who had analyzed large amounts of data using Microsoft FoxPro. And years ago, I had even sorted and analyzed data in dBASE. But for a variety of reasons, spreadsheets were foreign to me.
Last year I began a new assignment covering local government. And I almost immediately saw the need to quickly add, subtract, order from highest to lowest, find the average or median and calculate percentage increases. I had to learn Microsoft Excel.
Now, I realize I risk ridicule here. Many readers of this publication are interested in how to write the shortest program to sort through millions of government records in Microsoft Access or FoxPro.
But I also know many local government reporters who find themselves too busy to regularly build spreadsheets for their stories. I've come to find those spreadsheets are essential in almost every story. In fact, they can help a reporter take more control of the beat and its stories by generating information - and stories - that no one else has.
What I will attempt to detail here is some of the key data sets on the local government beat, how I used them to build spreadsheets and the stories that resulted.
The questions that prompted my requests for the data or documents I used to build the spreadsheets are basic to most any beat.
How much does that government official make?
How much does that perk or program cost taxpayers?
Start with your local government's annual budget. It should break down expenses in a general way. Look for any interesting notes and be sure to compare that budget to previous years, to search for major changes.
Also be sure to obtain a copy of the annual management letter, normally done by outside auditors. It should detail problems in financial controls, reconciliation of accounts and other problems related to revenue collections.
The city budget also should total personnel costs. But you'll need to dig a bit deeper to find out which government employees have the best deals in the way of pay.
Personnel and payroll
You need basic salary information about everyone. That should include name, department, salary, overtime and any other pay. A quick and easy story could focus on how many officials earn more than $100,000. You might be surprised by what you find. In Kansas City, we found 60 city officials in the "$100,000 club."
Then focus on the overtime. Examine by departments. What are the trends over the years? Examine also by individuals. Did any employees double or greatly boost their annual pay with overtime?
Start with your local government's annual budget.
I obtained some of this information electronically. It was in a rather clunky form, however. It was organized by payroll date - so each employee had 26 entries per year. I imported the data to Excel, then created pivot tables on the names to group all payroll information. Then, I sorted in descending order.
Another angle to pursue on overtime is to look at how often city officials, including police and fire, are paid for overtime they don't actually work. In Kansas City, police are paid a minimum of four hours overtime for a callback, even if they only work one hour. The Star obtained a few weeks of the police department's callback sheets, which detailed the actual time worked versus the amount paid.
Using a spreadsheet, I logged each callback, the amount paid in overtime and the amount actually worked.
Perks
A number of basic, but interesting, city or county records will be a good starting point in determining the perks that your local government employees enjoy.
Early retirement programs are popular today as local governments attempt to trim budgets. Obtain a list of every employee, position, salary, years of service and monthly retirement payment. I used such a list, building most of it by entering the data myself, when about 350 employees opted for an early retirement program in Kansas City.
With the spreadsheet, I was able to detail who was receiving the largest monthly retirement payments, the average payment, the years of service lost to the city and losses by department.
And then there are cell phones. When I set up shop in city hall, I quickly found it difficult to find any official by leaving a message on their office phone. The best way to reach them, I found, was on their city cell phone. That, of course, prompted the question: How much does that cost taxpayers?
Using an open-records request, I obtained electronic data about each cell phone, its city user, department and total monthly bill for three months. We totaled the three months of bills by user, then ranked them.
We filed another open-records request, asking for detailed billing information about the top 25 users. That revealed that many, including two city council members, were abusing their phones or racking up exorbitant bills. Others were doing work on their cell phones, but they were on the wrong plans, regularly exceeding their allotted "free" minutes.
Take-home vehicles traditionally are another city or county government perk. We requested the number and assignments of vehicles by city department. The city auditor had studied this a couple years earlier, so we compared our update figures to those in the city audit, creating a spreadsheet by department to track the increases or decreases.
To flesh out the story a bit more, we also obtained logs of city employees assigned to vehicles, which showed how often they use the vehicles for something other than commuting to and from work.
Property records
Be sure to obtain copies of county assessment records. These could be a foundation for a number of stories. Who has had the highest or smallest increases in assessment?
Also ask for delinquent property records, which may tip you off about local developers and businesses in trouble. Who owes the most in back taxes? Do many property owners allow their property tax bills to go delinquent for two years or more before paying the bill?
In addition, cities must create inventories of their city property. Check the city's real estate lists, matching city-owned addresses against lists of code violators. I recently found that the city owned land on which illegal junkyards were being operated.
Other fun records
Inspection records of restaurants, swimming pools, day-care centers and even salvage yards can produce great stories. Plug into a spreadsheet the inspection dates, findings, investigator's names and other document information. Patterns will emerge that you might not have caught otherwise. Also, spreadsheets are a great tool for keeping the information close at hand.
I recently had fun by obtaining city water department listings of water use at city-owned sites, including the city zoo and numerous fountains. With that information in a spreadsheet, we could calculate the amount of "free" water going to city sites.
The city kept spreadsheets on its free water users, including their addresses and the amount of water per month. With that information, we calculated usage per hour for some fountains and compared it to the average monthly usage of a family of four. We also toured several of these sites with water officials to refine our calculations.
Then there was water lost through leaks. In some areas the department lost as much as half of its water and ended up collecting fees for only two-thirds of the water it had treated for distribution. Big leaks happen not only in Kansas City but also in almost every city east of the Mississippi River because of the age of the city water distribution systems.
Finally, The Star also recently obtained the parking records for municipal judges, who had long been accused of working short hours in the city courthouse.
For many courthouses, this idea of using parking records to mark their workweeks would probably not work. But for judges whose cases are all heard from the bench in a docket-style fashion and where little legal research is required, it seemed like a good way to determine how much time the judges were spending on city business in the courthouse.
For this story, though, I had to advance beyond spreadsheets, enlisting the support of Greg Reeves, the newspaper's FoxPro guru, to sort through the electronic swipes of judges, who used cards to come in and out of the courthouse parking garage.
With Reeves' help, I had moved beyond Excel. But it would never have been possible without those initial steps 18 months ago into the world of spreadsheets.