Articles Nikhil Kenjale, CA Jun 12, 2023
Today’s data analytics technologies allow internal auditors to provide assurance based on analyzing full data populations. To do so, auditors must learn fundamental techniques from obtaining data to interpreting the findings.
Recently, the CAE of a large manufacturing company in India automated repetitive audit practices by developing a set of queries to identify process deviations in the organization’s enterprise resource planning (ERP) system. These queries examine all the data in the ERP system, rather than just a sample, enabling auditors to quickly find exceptions and validate them with process owners.
The basics of data analytics are changing for internal auditors. Today’s tools enable auditors to provide assurance based on analyzing full data populations, even from large, enterprisewide systems. Auditors can harness these off-the-shelf and customizable tools by learning some basic concepts.
The data internal auditors need is driven by the audit’s scope and should relate to the processes or operations with highest risk. When requesting data from business units, auditors should specify the columns, details, and time period they need to examine. Auditors also must indicate the type of data file they need. Typically, auditors can work with structured data more easily than unstructured data, which requires more data cleaning and normalization. In the age of big data, auditors may need more advanced tools to open complex files containing huge amounts of data. For example, Microsoft Excel files are limited to 1.04 million rows of data.
Additionally, auditors need to understand how the data is accumulated, stored, and controlled. Quality and expectations about data are dependent on the organization’s ERP system and the level of internal controls the organization has. For example, if the organization has a standard ERP system with primary controls that address all relevant financial statement assertions, auditors may expect to find certain values in the given population.
Finally, if the data was extracted using a purpose-built query or customized report, auditors must assess whether the report logic is appropriate. In some cases, internal auditors may need help from a data specialist.
Internal auditors may receive data that is not ready to be analyzed. They must prepare it by removing blank rows and columns, and fixing data that is in the wrong column.
Cleaning data also can facilitate avenues of analysis. One example is converting passive data, such as a date in a text field, into active data for analysis. Converting incorrectly entered data in the date field to the correct format enables auditors to perform date-related analysis. Auditors can generate monthly and quarterly summaries by deriving months out of the date field and assigning them to the corresponding quarter.
Cleaning data also helps auditors derive certain values from the data. For example, if the data includes the transaction date and general ledger date, auditors can add a column to show the difference between those dates, which may reveal delays in recording transactions.
At this stage, internal auditors can start statistical testing. The choice of tools and techniques depends on the audit’s purpose and budget, and the type and size of data.
Seeing the range of values — maximum and minimum — gives auditors a flavor of the data. For example, when auditors apply filters to a sales report, the negative amounts they find may indicate the data contains returned goods. Auditors should validate this finding with management.
Statistical measures of central tendencies — such as mean, median, and mode — demonstrate the average values in a population, which can enhance auditors’ understanding of a population. For example, the average sales price charged for a product during a year can provide an understanding of sales. If data samples resemble attributes of the average population, it enhances assurance. Conversely, attribute values that are above or below the average attributes may require further analysis.
While averages are helpful, auditors need to understand the elements that can distort the average value. For example, if a product’s average price of $45 is far different from the list price, auditors should check if the population contains elements such as debit notes or invoices where a price increase was billed to the customer after the sale because of a fluctuation in supply costs. Auditors should stratify the population based on unique data attributes such as product type, time (if the sales are subject to seasonal variations), geography, and document type (invoice, debit note, credit note, cancellation, and free supplies).
A one-sided view of data may not give auditors a complete understanding. Auditors should look at data from all possible angles using pivot tables. For example, pivot tables can enable auditors to combine multiple views of sales such as geography and products.
Narrative, or descriptive, searches can help auditors understand intricacies hidden in the data. For example, searching for specific words such as “double,” “reverse,” “rectified,” and “delay” may reveal information about the hygiene of the process. This search coupled with user names tells auditors who is doing what and how frequently. Auditors can summarize search observations by tagging them separately in the data. This type of search is a good way to spot fraud indicators.
Auditors who are familiar with musical rhythm can correlate it with a rhythm hidden in the data. Mode is a statistical value about the frequency of a variable. If auditors notice certain line items are repeated monthly in the sales report, it could be a monthly fixed billing to a client such as for monthly maintenance. Finding and tagging these frequently occurring elements reduces the need to analyze them individually, which can save time. This is the benefit of finding a rhythm in the data.
The final step in using analytics is making sense of the trends revealed by the data. Framing a good observation is an art requiring judgment and a systematic approach. Exceptions and outliers auditors discover could be genuine business transactions executed in a particular context with due authorizations, a one-off error, or intentional fraud. Hence, auditors should gather as many facts as possible about these outliers and relate them to the audit objectives in an unbiased way.
Knowing the basics of data analytics can help internal auditors make valuable observations, meet stakeholder expectations, and enhance their relevance to the organization. To apply analytics, auditors must have a strong understanding of the organization’s processes, knowledge of the client’s business to judge what to expect from the data, a systematic and disciplined approach, patience, and skepticism. Auditors can enhance these capabilities by learning how to use advanced tools and techniques. Above all, they should never give up in their quest to find insights from the data.
Data Literacy Certificate: A Journey to Data Analytics
GTAG: Data Analysis Technologies