Overcoming Multiple Systems Disorder with Data Analytics

Two Federal Agencies Turn to Specialists for a Cure

Analysts estimate the world’s data will grow 50 fold in the next five years. Federal government agencies already produce and house massive amounts of data, collected from a variety of sources. Most of this data is unstructured; lacking governing data architectures and deemed too large or raw for analysis.

Many federal agencies struggle with mining data out of separate systems to capture processes and search for anomalies to understand where internal controls are absent or weak. This disconnect also prevents management from monitoring critical business processes. Auditors are turning to data analytics to help bridge the gap between disparate systems. Is it working? Let’s explore two cases where it has proven successful.

When financial systems are not working properly to process travel obligations, payments, and advances, it’s time to call for help. A federal agency approached AOC Solutions, a financial management consulting firm known for using technology to optimize financial performance.

While the process itself was not material to the financial statement audit, it was cumbersome to plan, manage and execute. Managers lacked access to information they needed to identify problems, formulate plans resulting in $10s of millions of budget authority expiring unused each year.

Prescribed Solution

AOC Solutions worked onsite to document the travel process flow, event by event and system by system. Using IDEA data analysis software, AOC simultaneously analyzed five years of data from three separate systems to understand where business and accounting events occurred. The team worked to define the overall processes to address potential issues such as inaccurate financial data, non-compliant accounting, weak funds control, and absent management information.

AOC analyzed nine million records of human resource, travel general ledger data. Initial findings revealed several processes that produced transactions that were not captured in the management review. These were recorded in one or two of the systems, but not the other systems. The analysis also identified payments without recorded budgetary obligations and many budgetary obligations that did not result in payments. It showed the obligation amounts were generally skewed 30% lower than the actual payments, which created variances within the funds’ control environment. They also found many source records were regularly deleted each year and multiple duplicate source records that were treated as separate events.

Additionally, analysis of the general ledger (GL) data revealed that accounting had not complied with the Department of Treasury’s required GL transactions and accounts. The process owner was unable to see the significant number of process transactions within the financial system. AOC crafted a process reengineering plan to address each of the major issues in detail. The implementation included regression analysis, remediation and an automated subsidiary ledger to record, process and support process transactions.

Recovery

 
The results of these efforts were immediate and substantial. Travel obligations were recorded with reasonable estimates that allowed program managers to plan and allocate resources with greater accuracy. The agency was able to identify $15 million worth of invalid annual travel obligations, which had been recorded as valid at one point. Financial managers liquidated the obligations and reallocated the budget to other current year uses.

By using data analytics to automate the manual entry process for 75,000 annual transactions, the agency was able to reallocate five full-time equivalent staff positions to have them focus on higher-value work. The automated process also eliminated delays in recording transactions in the system, which eliminated a $30 million year-end journal voucher improving financial reporting.

Reports were provided weekly to financial and program managers, which allowed them to identify and respond to issues in a timely manner and maximize the use of limited budget authority, reallocated funds within the programs to maximize program performance during the year.

“Sometimes we are brought in to resolve a specific audit problem, and once that has been addressed, we look for opportunities to make the process more efficient and the program more effective,” said Talmadge Seaman, a consultant with AOC Solutions. “Data analytics is essential to see the ‘actual transaction story’ that is often hidden from managers and this allows us to go beyond the discrete accounting issue. As a result, managers can implement continuous monitoring and automation solutions that produce quantifiable savings in FTE and budget and substantial and measurable program performance improvements. The return on investment can be considerable and the possibilities are limited only by one’s imagination.”

Approximately 100 federal employees are responsible for entering and processing detailed information into two investigative database systems, which are not integrated. The manual process is not only time consuming, but prone to human error. Since information between the two systems cannot be compared, nor analyzed to search for errors, duplicates, and exceptions, the review process required more than 30 staff at two review levels to examine up to 25,000 records each year.

Prescribed Solution

Agency auditors reached out to the Audimation Services Solutions Development team to create an automated process, however, security restrictions prevented the use of live data to test the solution.

Using mock data, the Solutions Development team developed a custom IDEAScript, an object-oriented programming language designed to provide additional functions, routines, dialogs and processes for data analytics. The IDEAScript was programmed to import raw data from the information tracking system and compare it to data stored within an investigative database system. The script compares data from 74 fields to produce exceptions within minutes.

Recovery

The IDEAScript eliminated the need to review 100% of all manually entered data and allowed the team to focus only on exceptions. By improving the exception review process, the agency was able to reallocate 3 quality control staff members who were once responsible for second-level reviews and have them focus on more value-add work. Additionally, the need for the first-line review of the data entry by 25 field personnel was eliminated. More importantly, it significantly increased the accuracy of both systems and allowed the team to quickly identify and correct errors detected.

The agency currently uses IDEA data analytics for reporting, and for the first time, the team was able to pull data out of both systems to search for anomalies. It is imperative that the two disparate systems stay in sync and retain accurate records. Data analytics enabled the agency to easily convert PDFs into analytical documents for analysis, and produce detailed reports to compare data between both systems. With the IDEAScript in place, the team is now focused on improving the asset tracking process from start to finish and will rely on IDEA’s streamlining capabilities to help them accomplish their goals.

“When we were approached about the project, the client had struggled with their current process so long that they didn’t think a solution was even possible,” said Kurt Johnson, manager of Solutions Development with Audimation Services. “We were able to use data analytics in a way that brought the systems together, which saved them hundreds of man-hours to focus on the exceptions. We have seen analytics, particularly scripting, improve existing processes and operations – you just have to know where and how to apply it.”

While there is no cure-all for fully integrating disparate systems, data analytics offers some relief in bringing in different types of data into a single hub for analysis and review. Most purpose-build analytics tools accept data from ERP systems, legacy mainframes, Microsoft applications, and even flat and printed files such as PDFs. Analytics also simplifies the creation of scripts to automate repeatable processes to streamline tasks. If there is an ailment in working with big data or non-integrated systems, data analytics technology might just be the cure.