Methods (to our Madness): Using Data to Tell the Story of a Debt Problem

Periodically, we will highlight some of the methods used in a recently released audit. Every performance audit is unique and can require creative thinking and methodologies to answer our audit objective. Some of these methods could be replicated or present valuable lessons for future projects.

Sometimes it takes a number to get the point across. And sometimes it takes actually doing the work to show that the work can be done.

These were two of the big takeaways from a recent conversation I had with Jamie Ralls, principal auditor at the OAD and project lead for a recently released performance audit on debt collection: Oregon Needs Stronger Leadership, Sustained Focus to Improve Delinquent Debt Collection.

Vendor offset analysis showed potential savings of at least $750,000 a year

Jamie conducted an analysis for the audit on vendor offset. Vendor offset is when a state matches a list of debtors that owe the state money to a list of vendors that the state pays money to for services. Then instead of paying money to the vendors for services, the state intercepts those payments and applies it to the debt. This is something that 40 other states do, but Oregon did not do at the time of the audit.

Jamie looked at what Oregon could have collected had it used vendor offset. The result: At least $750,000 a year.

Limitations in the data resulted in a cautious estimate

The $750,000 a year estimate was likely low considering that the list of debtors was incomplete from a statewide perspective. The Department of Revenue maintained the list and it did not include debt held at other agencies. Additionally, due to the complexity of the analysis, the team only calculated debt and payments by year. An ongoing monthly calculation would have produced a greater collection amount.

Lessons learned: document along the way

Jamie said that if she could go back she would have been better about documenting all of the steps she took in the analysis as she went along. She was so caught up in the excitement of the work that she did not always stop to document everything. She then had to go back and retrace some of her work.

Using data to tell the story of a debt problem

When I asked Jamie why the audit team did this specific analysis, she said that paying vendors who owe money to the state has been a long-standing problem. The Audits Division had first recommended vendor offset in 1997. However, in the past our office had only talked about it anecdotally.

Being able to show the extent of the problem through data analysis had a big impact. Actually going through the methodology also demonstrated that doing vendor offset was technically possible. During the course of the audit, in part due to testimony from the audit team showing this analysis, the Oregon Legislature passed Senate Bill 55. SB 55 requires the Oregon Department of Revenue to do a vendor offset.

Breaking down the methodology

Here is a step-by-step look at how Jamie analyzed vendor offset:

  1. She took a list of approved vendors from the Oregon Department of Administrative Services and a list of debtors from the Oregon Department of Revenue. She matched the lists based on tax id numbers. She found 9140 debtors who were approved as vendors to receive payment from the state. These vendors owed a total of $67 million in debt.
  2. Next, she pulled queries in the Statewide Financial Management Application (SFMA) to find and export records of all payments to these vendors for the time period of 2011 to 2014.
  3. She then summarized the debt by each year and summarized the payments each year.
  4. She took the debt for the first year (2011) and subtracted the payments for the following year (2012). If a balance of debt remained, it was rolled over to the next year (2012) to create a rolling balance of debt.
  5. For each year, the amount of debt that could have been collected through payments in the following year was also calculated and rolled forward, to create a rolling balance of what the state could have collected.
  6. She computed $3 million in debt that could have been collected, or an average of $750,000 a year.

 

Caroline Zavitkovski, OAD Senior Performance Auditor, MPA

Caroline Zavitkovski, OAD Senior Performance Auditor, MPA

Auditing and Methodology Data Wonk Featured