Iowa Measures CARES Act Funds

The state of Iowa has built a strong foundation as a data-first government. With internal and public data programs such as the open data portal and state checkbook, more data than ever is available for state staff and the residents to access, analyze, and use for decision-making.

When the state received more than $4.26 billion in funding from the CARES Act, officials needed to move quickly to get dollars into the hands of people in need and root the entire process in transparency.

Building on the Foundation of Data-First Government

The challenge was how to create a simple way to show the public how much the state had been awarded, where the dollars were allocated, and how those funds were expended.

“We were dealing with a very short timeframe, so we wanted to stand up applications and tools that we already had at our disposal,” said Scott Vander Hart, state data administrator.

Vander Hart combined lessons learned from the American Recovery and Reinvestment Act (ARRA) reporting back in 2009 with his experience using Tyler's Data & Insights Open Checkbook to rapidly shorten the time to value. Within three weeks, Iowa launched a Pandemic Recovery Report, powered by Tyler’s turnkey Data & Insights application suite.

“This time, the most challenging part was figuring out how we wanted to account for funds in the accounting system,” Vander Hart said. “There was no standard way to report on federal grants, and that’s why we leaned on what we did with ARRA.”

The main difference was that back when preparing for ARRA reporting, Vander Hart put in 80-hour workweeks to build out the reporting system and public views of the data. This time, he can maintain the entire app with about a 30-minute weekly commitment.

“The way we set it up isn’t all that different from what we normally capture on vendor payments for our Open Checkbook,” Vander Hart said. “For the CARES Act, we’re collecting more detail than what we’ll have to provide the U.S. Treasury on the Coronavirus Relief Fund, which is a good thing and helps us avoid needing to go back and capture information we don’t have.”

Piloting CARES Act Financial Reporting

Iowa’s Pandemic Recovery Report creates a single source of truth that enables legislators, elected officials, and the public to access detailed financial information about CARES Act aid funneling into the state.

“A lot of this is thinking through the process of what you want it to look like internally,” Vander Hart said. “After that, everything was easy. Tyler's [Data & Insights] applications are easy enough to configure that you can modify it to what you need.”

Iowa created several datasets on Tyler's Enterprise Data Platform to support how officials wanted to track and report federal funds used for COVID-19 response and recovery. These datasets included information on state agencies, federal assistance listings, federal funds awarded, state receipts and expenditures, vendor payments, secondary recipients, and sub-recipient awards.

Vander Hart pulls the information together using a few SoQL queries to limit the amount of information people have to provide. One query, for example, looks up the agency names so the state has a better — and more consistent — way to report that information out.

Those queries then produce a number of reports based on the datasets, including the following:

• Total COVID-19 expenditures, and a breakdown by state agency, then unit name, then object class name; and calendar quarter awarded
• Total federal funding awarded, and breakdown by awarding federal agency, then federal program; state agency; and calendar quarter awarded
• Total federal funding received, and a breakdown by awarding federal agency, then federal program; state agency, then unit name; and calendar quarter awarded
• Total federal funding expended, and a breakdown by awarding federal agency, then federal program; state agency, then unit name, then object class name; and calendar quarter awarded
• Total number of vendors paid
• Map of vendors filterable by vendor name, federal agency, federal program, and state agency. The flyout labels show the vendor name and address, federal program, state agency, total award (if available), award description (if available), total payments, and dates of first and last payments made.

In addition, robust extract, transform, and load capabilities in the platform, helped standardize and clean data coming from different sources. The biggest learning curve was accessing and standardizing accounting funding data so it could be extracted.

“In our accounting system, the department names aren’t necessarily how people recognize those agencies,” Vander Hart said. “I can get our department codes, do a get-data-transform, and pull in a label I’ve established in the system so I can provide consistency and something that’s a little more public friendly.”

This approach was the fastest way to clean the data — “making the change in the accounting system wasn’t realistic,” he said.

Vander Hart recognized, from his work in 2009, the value of creating a process and structure across the organization to help scale this effort. He implemented new guidelines that define the roles and responsibilities of state agencies and sub-recipients, based on his experience with ARRA. This foundation addresses how funds are accounted for and reported, the data structure and schema associated with required reporting, and, among other items, elements to include in an interactive pandemic recovery report.

When data is updated, Vander Hart sends out requests to various agencies’ financial managers to confirm it and provide an opportunity to reconcile the dashboard and department reports to make sure nothing is overlooked.

“We had been doing that with the Open Checkbook and other expenditures for a while, so we knew where we might run into issues and could plan ahead,” Vander Hart said. “We’ve got a lot of eyes looking at the data.”

A Data Solution for all Stakeholders

Data analysts and everyday members of the public may have different abilities when it comes to navigating a database, but at the end of the day, their questions are often the same. They want to know where the money is going.

Iowa’s solution enables users of all technical abilities to find answers and get insights from the data. Even more, the app gives site users access to the underlying data.

When site visitors check the finance app, the first view brings together summary information and links to check out the Finance Explorer and Payment Explorer apps. The page defines what covered funds are, helping people to better understand what’s included in the aid package. The app also pulls in unemployment data and economic indicators to provide a more holistic view of the state’s recovery progress.

“We’re not having to spend much in terms of extra time to report this information,” Vander Hart said. “And, this puts us in a good place to report to the U.S. Treasury on Coronavirus Relief Fund dollars.”

While the coronavirus situation continues to evolve, the Pandemic Recovery Report provides clarity and transparency into the state’s approach.

“In terms of feedback, it’s been positive,” Vander Hart said. “We’re continually looking at ways in which we can make the information more understandable. We’re adding annotations to explain how dollars are being used and enhancing the site as we go.”

Iowa is offering innovative data capability to state staff, local government leaders, and the public. This work gives them all a hand in recovering from the pandemic together. Throughout this effort, Iowa has worked together with Tyler to find the best ways to build on its data-first strategy.