connections 2013 change your thinking - integrating analytics into the audit process final

4/12/2013

1

© ACL Services Ltd.

© ACL Services Ltd.

Jim Kaplan CIA CFE AuditNet LLC

Richard Lanza CPACash Recovery Partners LLC

ACL Connections 2013Change Your Thinking - Integrating Analytics into the Audit Process

4/12/2013

2

3© ACL Services Ltd.ACL | Connections 2013

President and Founder of AuditNet®, the global resource for auditors

Auditor, Web Site Guru,

Internet for Auditors Pioneer

Recipient of the IIA’s 2007 Bradford Cadmus Memorial Award.

Author of “The Auditor’s Guide to Internet Resources” 2nd Edition

About Jim Kaplan, MSc, CIA, CFE


Richard B. Lanza, CPA, CFE, CGMA

• Over two decades of ACL usage in building automated audit and cost recovery tools

• Winner of the 2nd Annual ACL User Challenge

• Wrote the ACL publication on how to use the product in 101 ways (101 ACL Applications)

• Has written and spoken on the use of audit data analytics for over 15 years. Recently was a contributing author of:

• Global Technology Audit Guide (GTAG #13) Fraud in an Automated World – Institute of Internal Auditors.

• Data Analytics – A Practical Approach - research whitepaper for the Information System Accountability Control Association.

• Cost Recovery – Turning Your Accounts Payable Department Into a Profit Center – Wiley and Sons.

• AuditSoftwareVideos.com – Training website devoted to audit analytic training

• Received the Outstanding Achievement in Business Award by the Association of Certified Fraud Examiners for developing the publication Proactively Detecting Fraud Using Computer Audit Reports as a research project for the IIA

Please see full bio at www.richlanza.com

About Rich Lanza, CPA, CFE, CGMA

Page 4

4/12/2013

3


Gain an appreciation of their success and pitfalls when planning data analytics.

Understand some common approaches to overcoming obstacles to planning data analytics based on case studies from companies and surveys conducted by the presenters

See how analytics can maximize the annual audit plan and better ensure focus is placed on organizational risk.

Outline an effective data request process to ensure complete and accurate extractions of data every time.

Learn how planning analytics can be integrated into top audit areas by using statistical analytics and scoring approaches.

Learning Objectives

Page 5


IPPF Standard 1210.A3

Internal auditors must have sufficient knowledge of…available technology based audit techniques to perform their assigned work

The Auditor’s Role

Page 6

4/12/2013

4


Professional Guidance

Page 7


Categories of Audit Software

AutomatedIssues

Tracking

ElectronicWork Papers

RiskAssessment

ContinuousControls

Monitoring

Anti Fraud

GovernanceRisk

Compliance

Audit Management

Audit Resource

Scheduling

DataAnalysis

Page 8

4/12/2013

5


Over 550 auditors responded

More than 70% reported using data analysis software

85% of those using reported purchasing specifically for data analysis

68% reported use to improve audit plan sometimes or always

33% Ad Hoc Beginner (Excel) 37% Intermediate (Excel, ACL, IDEA)

73% use audit staff for data analysis (no outsourcing)

44% use ACL, 33% use Access , 25% IDEA

43% major reason for not using on all audits - staff not trained

75% said greatest benefit - able to review entire population

84% performance objectives/compensation not tied to use

59% indicated would use data analytics if audit programs included steps

58% indicated would use if a script library were available or if vendors provided a lite version of their software

2012 Survey: Using Data Analysis Software

Page 9


Planning Data AnalyticsStatistics / Guidance

4/12/2013

6


External auditors, internal auditors, fraud examiners, or finance?

Using ACL in over half your audits?

What percentage of your company’s data have you mapped?– 20%– 50%– Over 80%

Quick Attendee Survey (Show of Hands)

Page 11


AuditNet – 2012 Data Analysis Software Survey – Why Are You Not Using D.A.?

Page 12

4/12/2013

7


(Company) Size Doesn’t Seem to MatterFor Implementation Rates

Page 13

AuditNet – 2012 Data Analysis Software Survey


Data Analytics in the Audit PlanAuditNet Nov. 14, 2012 Webinar (Over 350 attendees)

Page 14

4/12/2013

8


Planning the Audit Guidance – # 1 and #2Deep Analytics to Plan Audits – Minimal Guidance

Page 15


Planning the Audit Guidance - Understanding Risks (AU 314) Mention of Automated Tests

Page 16

4/12/2013

9


http://www.nysscpa.org/trustedprof/107/tp11.htm

Malpractice?


PCAOB – Audit Evidence Standard #15

Page 18

4/12/2013

10


Education Prepares

Organization Needs-Wants-Pays

Regulation Requires

Imagination Transforms

What Drives the Data Auditor?

Page 19


Use data analytics on almost every audit – Brainstorm the use data analytics in the audit planning process– Drop an audit and instead plan 10% for “data fun” across all audits / Understand the

process and benchmark it for future reviews– Make it part of annual objectives to use the product– Start looking at recent regulations that are mentioning D.A.s use

Find cost savings to pay for the usage & Track it & Promote it– Risk assess the general ledger – stratify by month by account J/E risk scores– Search for cost recovery opportunities with vendors and improved business methods– Sell the need for increased software and education

» Also sell the cost/benefit of ACL to other development platforms (especially for prototyping).

Overcoming Obstacles

Page 20

4/12/2013

11


Plan on education and make it routine– Meet every three weeks on analytics – no matter what– Build a team of champions to coach the team– Get everyone else involved…..even if it is with Excel to start– Make it part of annual objectives to learn the product

Establish data channels to all (material) parts of the organization– Have more freedom to extract data as needed– Obtain quarterly feeds of data– Obtain data from all locations once a year for audit planning

Overcoming Obstacles

Page 21


Effective Data Import Process

4/12/2013

12


Asking for data before understanding reporting needs

Settling for lesser quality data extracts from the company system

Not knowing what is possible within the tool to import and normalize data

Not documenting (videotaping) past ways to obtain data

Not asking for data early enough

Not obtaining data routinely and building the expectation (UK Bribery Office)

Top Data Import Mistakes

Page 23


AuditNet Nov. 14, 2012 Webinar (Over 350 attendees)Data Request is Sent Prior…..

Page 24

4/12/2013

13


Integrating Analytics into Top Audit Areas


Where Are You Using Data Analytics?

Page 26

AuditNet – 2012 Data Analysis Software Survey

4/12/2013

14


Types of Analytical Tests (Substantive Test)

Page 27

1. Trend Analysis

2. Ratio Analysis

3. Reasonableness Tests

4. Regression Analysis

“Analytical Procedures – Audit Guide”, AICPA, March 2012


PCAOB – Audit Evidence Standard #15Other Substantive Tests

Page 28

> Observation > Inquiry> Confirmation> Recalculation> Re-performance

4/12/2013

15


Audit Objectives / Controls to Scripts

Page 29


How the Scripts Align to Objectives

Page 30

4/12/2013

16


Fraud Risk Assessment Sub-Categories

31


Page 32

See the IIA’s website at www.theiia.org

Proactively Detecting Fraud Using Computer Audit Reports - IIA Research Paper

4/12/2013

17


When

Extract journal entries posted on weekends and holidays.

Extract journal entries relating to the prior year that were made just immediately following a fiscal-year end.

Summarize journal entry credits and debits processing by day, month, and year.

Where

Extract journal entries made to suspense accounts and summarize by the person entering and corresponding account numbers.

Extract journal entries to general ledger accounts known to be problems or complex based on past issues (errors of accounting in journal subsequently corrected by accounting staff or auditors) at the company or the industry in general.

Extract debits in revenue and summarize by general ledger account. Summarize journal entries by the persons entering to determine if they’re authorized.

Specific Tests Based on the 5 Ws

Page 33


Integrating Analytics into The Audit Plan – Specific ACL Cases

4/12/2013

18


Stratification by Month and Relations Between Years

Page 35


Stratification By Month Secret Code

Page 36

4/12/2013

19


Transactional Scoring


Modern tests (round numbers, duplicates, missing fields) identify thousands of ‘suspicious’ transactions, usually about 1 in 5 of all transactions get a ‘red flag’

Historically at least 0.02 – 0.03 % of all transactions have real problems, such as a recoverable over-payment

So roughly 0.00025 / 0.2 = 0.00125 or 1 in 800 ‘red flags’ lead to a real problem.

Imagine throwing a random dart at 800 balloons hoping to hit the right one!!!

The Sampling “Problem” Bottom Line Numbers

Page 38

4/12/2013

20


The result is a sampling methodology that is now based on

Risk as you define it mathematically in a score

Transactional Scoring

Page 39


Transactional ScoringSummaries on Various Perspectives

Summarize by dimensions (and sub dimension) to pinpoint within the cube the

crossover between the top scored location, time, and place of fraud based on the combined judgmental and statistical score

Page 40

4/12/2013

21


Combining the Scores ACL Code (by supplier number below but this could be record number)

Page 41


Severity To Value

Page 42

4/12/2013

22


GeoMapping Scores – BatchGeo

Page 43


General Ledger Account Analysis and Unique Journal Entry Test

4/12/2013

23


Cost Recovery


A/P and G/L Review Factors– Accounts that are sole sourced– Accounts that have too many vendors– Categories that map to the “recovery list”– Assess to industry cost category benchmarks– Top 100 vendors– Trend analysis over time – Trend analysis by vendor (scatter graph)

Purchase Order / Price List– Match to invoice payments to assess price differences– Strategic sourcing vendor review

Cost Recovery Opportunity Tests

Page 46

4/12/2013

24


Accounts Payable

Audit Fee Benchmarking

Advertising Agency

Document Fleet

Freight

Health Benefits

Lease

Media

Order to Cash

Profit Opportunities Outweigh Analytic Costs

Proactive Fraud Detection Project Fraud Real Estate Depreciation Sales & Use Tax / VAT / R&D

tax Strategic Sourcing Telecom Travel and Entertainment Utilities

Page 47


Integrating Analytics into the Overall Audit Plan

4/12/2013

25


Obtain data at least once a year for use in the planning exercise– Basic data may all that is needed from key systems and locations

Run a series of tests across each location’s data– Consider focusing on general ledger given its broad exposure to the business– Consider using risk scoring in a consistent fashion across the company

Prototype a few local audit toolkits each year– Most cost effective way to develop and ACL is cheaper than almost any other

development platform for finance/audit professionals– Identify “low hanging fruit” for cost savings and improvements.

Considerations for Overall Audit Planning


Analytic Command CenterProposed Implementation Visual

Internal Audit’sAnalytic Command Center

Analytics1. Accounts Payable

2. Accounts Receivable3. Financial Statement

4. General Ledger5. Inventory

6. Payroll7. Revenue

Data Mart

Local Analytic Toolkit

Feedback from all locations

Recovery Auditors

Shared Services

4/12/2013

26


Useful LinksTraining | Ideas | Graphics

www.auditnet.org

www.auditsoftwarevideos.com

www.aicpa.org

www.pcaobus.org


connections 2013 change your thinking - integrating analytics into the audit process final

Technology

acl services

use acl

acl publication

use of audit data analytics

acl applications

audit guidance

automated audit

data analysis software