The Data Analytics Conundrum

Studies after study have shown that data analytics is more effective and efficient at detecting risk, and identifying control weaknesses, non-compliance, and inefficient business processes.  Chief Audit Executives (CAEs) have repeated stated that data analysis expertise is a much needed skill in internal audit, and IIA surveys of software over the past 10-15 years have rated data extraction, data analysis and analytical software as critical tools for effective audit organizations.   Why then do more than half of the internal audit organizations still rate their analytic capability as poor or needing improvement?

I have been an internal auditor for 30 years and have been a user and advocate of analytics for 28 of those.  (It was during the first two years of auditing that I realized I could do a better job by analyzing data.)  I have been asked hundreds of times, “How can we develop and maintain an analytics capability?”  Too often CAEs give up without even trying (“we are doing good audits now, why change things?”; or only make a feeble attempt at it (“let’s get a programmer right out of college and have them develop analytics for us.”)

The reality is change is difficult.  As auditors we are constantly making recommendations to help others improve, change, do more, etc. but we ourselves stick with traditional auditing tools and techniques.  Perhaps what is needed is a taste of our own medicine.

In order to successfully implement analytics and integrate data analysis in the audit process you must have a formal development and implementation plan.  The plan must address the need for sufficient people (appropriate level and number), technology (primarily software) and processes (you need to change the way you currently perform audits).  It must also have a project manager who will be held accountable for delivery on the plan, clear objectives, milestones, and a reporting requirement – to the CAE and/or the audit committee (but only if they fully support the adoption of analytics).

Analytics also requires an understanding of the business processes, the data and supporting them, and a solid grasp of internal auditing processes and requirements (e.g. application of the IIA standards).  None of these will be provided by junior level audit or programming resources.  Rarely will all these skills exist within one individual; and they might not already exist in your audit organization.  Rather than being an impediment, this should be seen as an opportunity: an opportunity to obtain the right resources and task them with a clear objective.  If you are lucky and have the appropriate type of resources in your organization – this is ideal.  Existing resources should already know the business processes and have the internal audit skills, and perhaps have some analytical capabilities.  However, they will need to be supported by training and software, and given sufficient time to develop the skills and implement the functionality.  Most importantly, they will need to be dedicated to analytics.  Otherwise you end up pulling valuable resources away from other priorities and tasking them with something in addition to what they are already doing; or settle for a subset of the required skills.  In either case, it is a recipe for failure.

A statement I hear often is, “We are a small audit organization, and we can’t afford to dedicate a person to analytics”.  It is usually used as a rationale for not using data analytics.  My response is something along the lines of “Because you are small does that mean you can afford to be less efficient and effective?”  The reality is, unless you are using analytics, you are not addressing risk, testing controls, examining compliance and improving business operations to the extent that you could be.  If you are going to decide not to use data analytics, at least make it an informed decision.  Examine the costs and benefits and then decide.  Don’t simply look at your existing resources, which are most likely being used to the maximum, and decide that you can’t take on anything else.  It is not a question of doing more with the same resources.  Ask yourself if there are things that you don’t need to be doing or if they are better ways to do what you need to do.  Also look at what you are not doing and determine the value-added if you could do those things.  Then decide if you can afford not to be using data analytics.

I also get asked about audit software, “which package should I use?”  This is something that should be decided based on your requirements and your short- and long-term plans for analytics.  I encourage you to fully utilize the existing capabilities such as standard reports and you can definitely start with Excel, but don’t be limited by what you have – think about what you need.  Find out what other audit organizations are using.  For more than 10-15 years, the IIA magazine, Internal Auditor, conducted a survey of software usage and the results were printed in the August issue.  The results have consistently shown that ACL is the most used audit software for data extraction, data analysis, and fraud prevention and detection in the world and has been for almost 15 years.  It is the software I use, so I may be biased, but just because you are biased doesn’t mean you are wrong.

In conclusion, you should be using data analysis.  You will need to plan and manage your adoption of analytics.  It will take time, resources, and technology.  It has to be integrated in the audit process (planning, conduct and reporting) and developed with an understanding of the business processes and the underlying data.  It is easy to do wrong, but worth doing right.

Why did I title this “The Data Analysis Conundrum”?  Because I don’t understand why we are still talking about the “Why, How, and What” of data analytics and not simply getting on with the job.  Stop asking questions about analytics – get off the fence and actively pursue it.  The successful implementation of analytics will add significant value to the internal audit function and your ability to support the goals and objectives of senior management.

Year 31 – Incomplete Property Tax Data

Even though I am retired, I am still getting to do interesting analysis – perhaps even more so because I get to pick and chose want I want to do.  In this case, I was asked to assist with an audit being performed by a municipal audit function.  A large city had an internal audit group that was interested in auditing revenue – primarily from property taxes.  One of the key objectives was to determine the completeness of the property tax data (i.e. were all properties included in the property tax data base?).

I suggested examining other external data sources including: water meter, building permit, and hydro data.  The city was not responsible for issuing and collecting on hydro bills so only the first two sources were available – but even that was a good start.

Using water meter data we were able to identify all addresses with water meters and calculate the water usage.  We had to be sure to use the correct information because the data also had billing address which could be different from the address where the meter was located.  The data also had the meter install date and the first and last date the meter was read.

The property tax data also had a billing address and the physical – building location – address.  But, before we could compare the property tax data with the water meter data we had to normalize the address fields.  Even the address entries with each system were inconsistent, with variations such as: “St.”  or “St” or “Street”;  and “Unit 12 123 Main St” or “Unit #12 123 Main St” or “12 – 123 Main St” or “123 Main St Unit 12” or “123 Main St Unit #12”.

In the end we were able to match the water meter data with the property tax data to identify properties with buildings that were not paying property tax.  While there were only a couple of hundred properties identified this still represented a total of over $1M in missing revenue.

The next source of data to test the completeness of the property tax information was the building permits.  Building permits were issued for new building and for major renovations on existing building.  The data including the address, the permit issue date, the inspection date, and status (e.g. completed).  Again, the only common field was the address so we had to clean the data before doing the comparison.

Using the permit and property tax data we were able to identify new and existing buildings that were not paying property tax.

The next risk that we decided to address, after looking at the data, was the timeliness of the property tax data.  We had notice significant delays in the time between the issuance of the permits and the inspection – sometimes several years.  There were also long periods between the water meter install and the date of the first reading.  We also noticed inspection and water meter reading dates that were years into the future.

The auditors determined that some of the dates were important and had a bearing on the amount of property tax paid in the year.  For example if a building was not completed until July 1st, then property tax was only 50% of the yearly amount.  The start date for tax calculation was the date of the final inspection.  Delays in the inspection meant less property tax and dates into the future meant no property tax was collected.

Again, there were only a few hundred issues with dates, but the results included one case of fraud and hundreds of thousands in lost revenue.

In the end the audit identified issuing around completeness of the property tax data, resulting in loss revenue, and made recommendations that would integrate the separate data sources.  We also identified data entry control weaknesses with respect to key dates and made recommendations which include: range checks and aging reports.


Lessons-Learned: even though the audit had a very specific objective – assess the controls ensuring the completeness of the property tax data – there was a multi-phased approach.  During the initial planning phase the idea of testing completeness by comparing property tax data to other data sources led to a data analysis approach that had not initially been considered.  Despite challenges finding fields to use for the Relate command, perseverance and use of scripts from ACL scriptub such as ”Address Clean” made the task easier.

Another important lesson was the examination of the data in the meter and building permit data prior to performing the Relate in ACL.  It is important to assess the integrity and to understand the data before performing the Relate.  Had we simply performed a Relate using the address field and not examined the data first, we would not have identified issues with respect to the date fields.

When using and source data, even if only to test completeness of another system, you should analyze the source data.  High-level summaries such as Classify, and simple Aging and Statistics on date fields can be very informative and may identify additional risks to be addressed during the audit.  Not only did we identify control weaknesses around key date fields – and a fraud involving a deliberate future dating of inspection data in exchange for kickbacks – but the high-level summaries identified duplicate records which had to be addressed before we could perform a proper Relate.

Simply having access to data provides an opportunity to assess risks and controls in the system.  My consulting contract was over, so I am not sure what happened after I left, but I image that the auditors will be examining the building permit and water meter processes in future audits.  Based on our limited review of the data, there are control weaknesses and fraud risks in these business processes that should be examined.

Note: In thinking back on the analysis performed, I wish I had performed an additional analysis examining unit and apartment numbers for addresses with multiple units.  Units or apartments that are not in the property tax data (e.g. Apt 101, 102, 103, 104, 105, 201, 202, 203, 205  – possibly missing Apt 204; or Unit 10, 12, 18 – possibly missing 14 and 16) could be instances of incomplete data.  I do realize that this would likely involve more coding and manual follow-up to eliminate the false positive, but it could be worthwhile.

Adding Value to Compliance Audits – part2

The following posts is part 2 of “Adding Value to Compliance Audits”

Given a good understanding of the current level and sources of risk, the next step is to look at the requirement for, and the adequacy and effectiveness of, the control to mitigate the risk.  This requires an understanding of the cause and source of the risk and the operation of the control.  Is the control still required?  Does the current control address the root cause?   Are there better ways to mitigate the risk?  By answering these questions the audit may identify unnecessary controls, ineffective controls, or identify better controls to address the current risk.  All of which may reduce the cost of compliance, while improving risk mitigation.  Recommendations such as automating a control can save time and effort and been seen as a real value add.

The next step would be to verify that the control activities are being performed (i.e. compliance).  However, you are not done yet.  If you find non-compliance it is still not sufficient to recommend “Do A”.    Audit recommendations should address the root cause.  Identifying a lack of compliance is not the same as determining why management is not complying (i.e. determining the cause).  Was management aware of the requirement? Are they capable of complying? Are there compensating controls that have been implemented?

In order to determine the cause of non-compliance asking “Why” (usually several times) is often sufficient to determine the cause.  You should also determine the impact of non-compliance.  Then instead of “Do A” audit can provide a rationale and make a recommendation that actually assists management in complying.

The next step is to ensure that you are doing the audit right – this means maximizing your use of all your resources available to you, including analytics.   Data analytics can be defined as the application of analysis techniques to understand business processes, to identify and assess risks, to test controls, to assess efficiency and effectiveness, and to prevent, detect and investigate fraud.  Data analytics techniques, ranging from simple categorization and stratification to sophisticated predictive and prescriptive models, can assist organizations in focusing their risk responses on the areas in which there is a higher risk – including compliance risk.

Existing levels of risk can be assessed and trends identified to determine if the risk is increasing or decreasing.  For example, environmental compliance could examine spills (number and quantity), cleanup costs, and lawsuits (quantity and value); production compliance could examine material, personnel, maintenance and operational costs.  By examining measures over several months or years, a trend line can be produced to assess the effectiveness of mitigation efforts and identify emerging risks.

Rather than relying solely on substantive tests, the effectiveness of controls can also be tested with analytics.  In addition, you can look at trends that will have positive or negative effects on compliance.  For example, environmental compliance can examine the control over the purchasing of hazardous materials – ensuring that the purchase quantities match requirements – thereby avoiding environmental compliance issues around disposal.   Compliance with hiring practices could review staffing methods, staffing rates (by gender, by race, etc) to ensure proper procedures are being followed and address employment equity requirements before they become non-compliance issues.

Sometimes compliance with a poor control can increase risk and dysfunctional behaviour; and cultural issues can make enterprise-wide compliance difficult for global companies and increase risk.  Doing the right compliance audit – not simply “did we do A” and doing it efficiently and effectively can result in significant value to the organization and remove the “got ya” stigma of compliance audits.  However, it requires auditors to re-look at the compliance-related risk and controls and use analytics.

Richard Chambers, President of the IIA, identified his top ten imperatives of the decade which highlight the challenges that auditors must face to provide value to management.  These include enhancing proficiency with data mining and analytics; providing assurance on risk management effectiveness; and enhancing and leveraging a continuous focus on risk.   These challenges can be applied to all types of audits from compliance to operational.  He encouraged auditors to look at business objectives, risks to the achievement of objectives and design audits that provide assurance over the governance, risk management and control/compliance frameworks put in place by management.  A compliance audit should not be any different: it should identify and assess risk; and examine the effectiveness and efficiency on the controls to mitigate the risk.   By doing so, it will add-value to the company as well as provide assurance to senior management.

Accounts Payable Example

In an Accounts Payable audit there was a requirement to formally authorize invoices for payment by stamping and signing the original invoices.  The stamp and approval verified that goods/services had been received in accordance with the contract and that the invoice could be paid.  Falsifying this approval had serious legal repercussions – including up to 5 years imprisonment.

The audit covered numerous accounts payable offices spread across the globe.  As a part of the audit we verified that invoices had been properly approved i.e. stamped and signed by the authorized approval authority.  At several locations we noted that the invoices were not being properly authorized (stamped and signed).  But the reasons for non-compliance differed.  In one small office (AP1) they were unaware of the requirement.  We identified an underlying problem with corporate communication of financial regulations, including a lack of translated procedures.  In another office (AP2), they had been told by the legal department that the stamp that was being used did not contain the appropriate wording and they should immediately stop using the stamp and obtain the official corporate stamp with the correct wording.  The local A/P manager had been trying for months to obtain an official corporate stamp – he even showed us numerous emails – to no avail.  At another location (AP3) they had converted to electronic invoices and authorization – so they were no longer stamping and signing invoices.

A compliance audit that did not ask “why” might easily have issued the simple recommendation “stamp and sign all invoices” – adding zero value to the A/P process.  Adding value to this compliance audit would have had very different recommendations.

Starting with the risk: The control was put in place to ensure that we were not paying for goods/services we did not receive; and that goods/services were of the quality, quantity, and price agreed to in the contract.  Given the nature of decentralized contracting, the risk still existed and a control was required.

The second step would have been to determine if the control was effective and efficient.  At AP1, the control was not working because of a problem in the corporate communication area – we had acquired a new overseas operation and regulations had not been translated.  This required a different recommendation; one that would address the root cause – corporate communication – and did not penalize the local A/P manager.

At AP2 non-compliance was attributed to a breakdown between the legal and finance departments.  Legal was reviewing all official stamps and finance was responsible for updating, revising and supplying them.  Unfortunately, the two departments were not coordinating their work and finance was unaware of the problem with the invoice authorization stamp.  This recommendation addressed the communication between departments.

At AP3, the physical stamping and signature of the invoice had been replaced by an automated approval.  Recommending compliance with the current regulation would be ludicrous.  However, the automated controls needed improvement to verify the authority of the person providing the electronic approval.  As a result, a recommendation was made to address the weakness in the automated control.

The result of the compliance audit improved the corporate communication processes, interdepartmental activities, and IT controls.  The recommendations were seen as having value – much more than “Stamp and sign all invoices” would have received.

In addition, the audit of the efficiency and effectiveness of the A/P process can benefit from the use of analytics.  The controls over approval can easily be done by matching the electronic approval with a database of approvers.  Examining actions by users can identify instances where separation of duties was not achieved.  Totals by payment terms or payment method can quickly highlight inefficient practices or even fraud.  The resulting recommendations can improve compliance and reduce business risks while adding value.