Audimation Services has been acquired by Caseware International Learn More.


Blog Image

New Perspectives (AHIA) Article on Data Analysis Challenges

Filtering Letters on Chalk Board

Executive Summary

When it comes to using data analysis in place of manual audit processes, the benefits clearly outweigh the challenges. From increased productivity and efficiency to improved risk assessment, data analysis is well worth the effort. Internal audit shops of all sizes struggle with data-related challenges including accessing data, inconsistent data formats from various sources, excessive file size and data import and export issues. Training and time are also required to overcome learning curve issues. This article provides some best-practice approaches to overcoming these challenges, including data acquisition solutions, effective strategies for working with data gatekeepers and available resources to help save time and effort in using data analysis. 

In the middle of difficulty lies opportunity. Data analysis benefits clearly outweigh the associated challenges. Increased productivity, efficiency and improved risk assessment make data analysis well worth the effort. What we must ask ourselves is, “Can we use data analysis technology to help us do something in a matter of minutes that may have taken us several hours of manual work to complete in the past, and improve the quality of our work as a result?”

Data analysis technology has been available for 25 years, and some auditors are inclined to use what’s worked for them in the past rather than reach beyond their comfort zones. However, with data growing exponentially, manual audit techniques are no longer an efficient or effective option for small- and medium-sized audit functions. The use of data analysis tools has evolved from a luxury to a necessity required by internal audit functions of all sizes in an effort to keep up with our data-abundant world. While there may be challenges, there are also opportunities to directly improve the value internal audit brings to our organizations.

Top Challenges of Audit Professionals

Most internal auditors will tell you their never-ending challenge is doing more with less, which presents an excellent case for using technology to simplify complex or manual tasks. Using data analysis technology can significantly reduce audit time, improve audit quality and automate analysis routines that can be used again for similar tasks in the future. However, the use of technology presents its own set of challenges.

The use of data analysis tools has evolved from a luxury to a necessity by internal audit functions of all sizes.

During 2011, Audimation Services, Inc. surveyed an extensive database of auditors, accountants, financial professionals and consultants in all industry sectors. The purpose was to find out what challenges they have encountered in using data analysis, and what techniques have brought the most value to their organizations. Of the nearly 500 professionals who provided valid responses, more than half (57 percent) cited data-related challenges including access to data, standardization of data, large file sizes and data import and export issues.

While 16 percent said they have not experienced any challenges using data analysis, 18 percent cited learning curve issues or training needs. About 5 percent stated they struggle with time-related issues such as eliminating false positives, lack of internal resources to resolve exceptions and learning to use the full capabilities of data analysis tools.

The remaining survey respondents (4 percent) cited internal issues such as lack of support from management, unwillingness to change from manual to automated audit practices (e.g. a manual review or printed comparisons related to areas such as accounts payable, payroll and governmental exclusions) and budget constraints.

Biggest Challenge: Getting the Data

The elusive goal of internal audit functions is obtaining valid data, in a consistent format, from different systems and groups within the organization and importing it into a data analysis tool. This process takes collaboration, cooperation and trust between internal audit and other departments to gather the required data needed to conduct a thorough audit. Data gatekeepers are often a barrier to acquiring data.

Frequently, there is the ongoing struggle between internal audit and multiple IT system administrators managing different systems within the organization.However, both groups are concerned about security and data integrity issues. In addition, there are complexities with the systems themselves. From massive ERPs to homegrown legacy systems, data can come in multiple formats and conditions that need to be resolved prior to the data analysis phase.

Data Acquisition Solutions

According to the Institute of Internal Auditors (IIA) Global Technology Audit Guide (GTAG): Data Analysis Technologies, the use of data analysis technologies requires the support and commitment of an organization’s IT department.

The chief audit executive (CAE) should engage in planning with IT management (CIO or peer level) up front to highlight the overall data analysis strategy, sought-after benefits and data access requirements of the internal audit department. Data access protocols should be established up front, and any risks identified relating to the access, sharing, or storage of sensitive data should be addressed. To ensure the effective acquisition of data files, consider the four following strategies:

Set data-gathering goals – The internal audit team must start with the simple but important question, “Is there electronic data available for analysis and mining to meet the objectives of the audit?” Start with the type of data that needs to be analyzed: master file data, transactional-level data or maybe even free-form text.

Communicate – Inform the system specialists about the exact sources where the data you require is stored within the organization. The assessment team needs to be sure they are using the same data that the business unit uses. By tracing the data from source files to the business processes that use the data, the assessment team can verify the same data was used. Good questions to ask include:

  • Is the data in the form needed for the analysis?
  • Are all of the necessary data elements available?
  • Is it possible to join data tables with a key data field such as an employee ID number?

Remind the data gatekeepers that the gathered information will be imported into a data analysis tool, which offers the ability to ‘lock’ the data to be certain it is not deleted or manipulated. When possible, request that data extractions from corporate files are provided in ASCII format with a delimited digit. This will help to mitigate format consistency issues.

According to a study conducted by Gartner, for every hour invested in training, it reaped 5.75 hours of increased productivity.

Data organization and formatting – Some organizations receive data from a variety of sources including ERP systems, third-party data aggregators and system administrators with varying systems. Some IT teams will internally develop tools to map, validate and cleanse data from various sources.

Formatting inconsistencies caused by data input errors, system variations, blanks or other issues should be evaluated and corrected as necessary (scrubbed). Identify inconsistencies, then develop strategies to fix, group, isolate or eliminate them.

Data analysis offers the ability to extract an unlimited number of files.

In some data analysis tools, when a file is imported and the date, number, time or virtual fields contain invalid data, the invalid fields are marked in red as “Error.”

In another example, cross-checking addresses can be problematic due to the variations in spelling and punctuation found within the data. Address XChecker isolates duplicate addresses between two databases, by distilling the addresses to just numbers and then comparing the data without considering the spelling and punctuation variations. The IIA GTAG recommends isolating “bad data” from the main analysis and subsequently investigating it to see if the “bad data” substantially impact the overall assessment of the audit.

Most available data analysis technologies offer the ability to import files stored in pdf, text, spreadsheet, or other formats. These capabilities continue to improve with new version releases in both speed and accuracy.

Supported users of data analysis tools can seek importing and exporting assistance by email or phone with the software providers’ help desk teams. Most help desk technicians will walk you through each step of the import, and for larger or more complex imports, consulting services may save time.

Managing large data volumes – Another advantage data analysis offers is the ability to extract an unlimited number of files, while spreadsheets handle a limited number of rows. Data analysis tools also allow the auditor to analyze entire data populations, rather than just a sample.

This provides greater insight into the organization’s operations. For extremely high volumes of data, some internal audit functions may consider server-based platforms which are faster and offer an extra layer of security.

A Second Challenge: Learning Curve

Lack of knowledge and the need for training rank high on the list of challenges internal auditors experience. Staying updated on technical best practices, including using and developing tests to apply to the control environment, can be a daunting task without proper training or guidance. Survey respondents cited the following learning curve challenges:

  • Knowing when and where to apply data analysis, and when to use other tools
  • Learning to use the features and functionality data analysis tools offer
  • Creating formulas or scripts to automate manual processes
  • Lacking time and/or resources for training, or forgetting what was learned during training


Learning Curve Solutions

The use of technology requires training or the time-consuming process of trial and error. Resources are available in abundance to help you overcome these challenges.

Take a tool belt approach – Sometimes staying within your comfort level can be beneficial. For smaller, less complex tasks, spreadsheets can be used to perform analysis. While they do not offer unlimited file size or data “locking” capabilities, they work just as well for sorting, searching and summarizing data.

The use of data analysis tools does not have to be overly complex to be effective.

In a small generalist audit department, internal auditors may have to rely on subject matter experts or co-sourcers for both their expertise and preferred tools, while prioritizing and reconciling with departmental time and dollar budget constraints. Complex portions of the healthcare revenue cycle might be one area where this could happen.

Learning to use data analysis effectively – According to a study conducted by Gartner, for every hour invested in training, it reaped 5.75 hours of increased productivity. From public courses offered throughout the U.S. by software providers and industry groups such as the IIA and AHIA, to low-cost online courses hosted by groups such as AuditNet, there are learning opportunities available for all levels.

Social media has helped connect auditors in new ways to exchange information, collaborate and network. Industry-specific forums help fellow auditors discuss specific issues and offer best practices— search blog sites and LinkedIn to identify and join these forums as a resource.

Good, old-fashioned in-person networking also provides opportunities to gain additional insight into data analysis techniques and scripts. If not already a member, consider joining at least two professional organizations and attend their monthly meetings to hear from local experts, and meet fellow auditors. Most data analysis software providers offer user groups that are affordable to attend and provide networking opportunities.

For those who prefer to go it alone, there are self-study materials, free webinars, how-to videos, case studies with answer keys and an assortment of books available on various topics. In 2011, Wiley released Mastering IDEAScript: The Definitive Guide, by John Paul Mueller, which provides instruction for the use and development of scripts to simplify audit work.

A Third Challenge: Time Constraints

Time is something everyone can use more of. We all face the same basic time constraint obstacles, such as more work than resources, deadlines and conflicting schedules. Whether you are struggling with using data analysis tools effectively, eliminating false positives, or sorting through exceptions, the trick is to simplify your approach.

The use of data analysis tools does not have to be overly complex to be effective. Collaborate with others in your organization, and when a solution to a specific analysis problem is found, document it. Work together to develop a library of scripts and equations that can be reused by the audit team and work towards a continuous auditing process. Continuous auditing can streamline repeated or manual processes to quickly identify anomalies while freeing staff to:

  • Focus on more pressing issues
  • Work toward resolutions
  • Tighten controls


Time Constraint Solution

Simple criteria in some basic areas can garner beneficial results. Take a longterm rather than a short-term approach by investing the time to achieve optimal productivity. Proper communication with internal stakeholders to keep them updated on internal audit function objectives, areas of focus and outcomes will often take patience. Rather than becoming overwhelmed by the entire data analysis process, break it down into smaller, more digestible parts. Have a plan and focus on one area at a time. Creating checklists of areas to analyze is a good start.

You may want to consider partnering with an outside expert for assistance.

If you are struggling to determine which areas in your organization might best benefit from the application of data analysis techniques, talk with the major data and business process owners in your organization to help develop a custom approach. You will build relationships, earn respect, gain better understanding of processes, and deliver more value.

Tapping into Expert Resources

Never underestimate the power (and time savings) of seeking help outside of your organization when you need it. Whether you are struggling with a complex file import or need help familiarizing yourself with a data analysis feature, use the resources available to you. Contact the data analysis tool provider’s help desk, ask an associate within or outside of your department, or reach out to a fellow auditor via an online forum. Chances are they will gain something in helping you as well.

In some instances, you may want to consider partnering with an outside expert for assistance. Most technology providers offer partner programs to give customers direct access to consultants who have mastered the use of their product, have the specific technical expertise and can provide customization services. These individuals are often listed on the technology provider’s website and can serve as expert resources to save valuable time and effort.



While challenges abound, do not forget that opportunity does as well. Current internal audit standards have called for the use of data analysis for many reasons, but mainly because it offers the opportunity for internal audit functions to add value by improving efficiency and effectiveness.


Scott Smith is an Account Manager at Audimation Services where he works with fraud specialists, internal auditors and financial professionals in the healthcare industry. Previously he was co-owner of a software development company in the Seattle area. Scott has many years’ experience as a systems analyst and software developer. You can reach him by telephone at (832) 327-2065 or by email at ScottS@

Jon Mueller, CPA, CIA, CHFP, is the Director of Internal Audit at the Houston County Healthcare Authority d/b/a Southeast Alabama Medical Center. He has six years of experience in public accounting and 15 years in the internal audit profession in banking and healthcare industries and performed one year of financial analysis/controllership duties in the banking industry. You can reach Jon by telephone at (334) 712-3244 and by email at [email protected].

Best Practices , CaseWare IDEA , Data Analytics

Posted By

By Audimation Team

Related Posts
Date Fields Importing into IDEA as Character Type Fields
Aug 02 Why are my date fields importing into IDEA as character type fields?   If you are importing from Excel, the date column needs to be formatted as a date t...
Report Reader Updates in IDEA 10
Feb 15 The functionality in Report Reader remained unchanged between IDEA versions 9 and 10.  However, many of the icons in the toolbar have a new look.  Use...
Cannabis Industry Using Data Analytics to Reduce Risks & Maximize Profits
Nov 05 Imagine taking a job as an internal auditor in a rapidly-expanding, highly-lucrative business, with the best intentions to help stakeholders identify and manage...

This website has been designed for modern browsers. Please update. Update my browser now