Alternative Uses for Data Analytics Tools
Download
Report
Transcript Alternative Uses for Data Analytics Tools
Who left the CAATs out – Alternative
Uses of Data Analytics Tools
Tim Smith, CPA CISA, CISSP
March 28, 2013
The Corporate Caveats
The concepts presented are my own and do not
represent LPL Financial or LPL Financial Internal
Audit.
2
What we are going to cover
CAATs revisited
How can they be used in new ways
Why auditors need to learn to use them
What tools exist
CAATs Close-up
Looking at security with CAATs
Some IDEA functions for new tricks
Some IDEA / CAATs success stories
3
A few things to use CAATs for
Validating data entry dates / times / users to identify
postings or data entry times that are inappropriate or
suspicious.
Classification to find patterns and associations among
groups of data elements.
Gap testing to identify missing numbers in sequential
data.
Joining different data sources to identify
inappropriately matching values such as names,
addresses, and account numbers in disparate systems.
4
Before you ask IT – ask yourself
What are you trying to test?
Controls
Metadata
Reports
Data
Transaction Details
Reports
Data
Reports
Off-the-shelf
Custom / ad-hoc
5
Metadata in an accounting system
Non-financial fields discussing the
Who
What
When
How
About the fields in the records comprising financial
information
Together, these data can provide a diagnostic view
of the accounting system
6
What might we need to look at
Retroactively
Transaction data – especially between systems
Transaction metadata
Module or journal entries
Logs
Prospectively
System access
Program change management
7
Working with system access
information
Larger software vendors are targeting the small to medium
enterprise space – SAP, Oracle, Microsoft. As a result, many
businesses have access listings containing thousands of
lines
System access information can be complex – very granular,
with difficult formats
Data may cover multiple menu layers and multiple
modules within an application
Therefore, it is vital to gain a understanding of basic access
information structure and what you want to test before
starting
8
A few systems with complex
security reports
Oracle Financials
SAP (SmartExporter)
Microsoft Dynamics – Great Plains
Sage MAS 500
ADP Enterprise HR (EV5)-- Formerly PeopleSoft
HRMS
9
MS Great Plains v10 security model –
four levels
Security Operations refers to access to all windows,
tables, reports and miscellaneous permissions
A Security Task is a set of Security Operations
required to perform a specific task
A Security Role combines multiple Security Tasks
required to perform a specific role
Each User and Company combination can have
multiple Security Roles assigned to it
10
Complex Access From a higher
level – viewed From the role
11
Unexpected functions within the
roles
12
What are the tools?
Excel – row limitation (was 65K lines, now 1m or so);
data easily changeable
Access – data also easily changed; might also hit a size
limitation (1GB for pre 2003; 2-3 GB now)
SQL Server – again, data changeability; probable need
for programming knowledge (SQL)
Specific CAATs software packages
ACL – Audit Command Language
IDEA – Interactive Data Extraction and Analysis
13
Key functionalities of IDEA
Profiling the data
Extractions
Gaps and Duplicates
Adding a new field
Smart Analyzer (an Add-on module)
Joining Databases
14
CAATs success stories 1
GAO report 02-406
Significant internal control weaknesses in
Education’s payment processes and poor
physical control over its computer assets
made the department vulnerable to and in
some cases resulted in fraud, improper
payments, and lost assets.
15
CAATs success stories 2
Assisted a Federal agency evaluate
problems with its accounting system,
taking it from a disclaimer in year 1 to a
qualified balance sheet in year 2 to a
clean opinion in year 3.
16
MS GreatPlains
17
IIA 10/10/2012
18
RACF security – User Attributes
19
iSeries – Display Object
20
Report Reader
Can be used with formatted text files
Can be used with non-picture PDF files
Create a template that can be used for future
files of similar construction
Crucial for work with non-columnar reports
or reports with header / trailer information
to be ignored
21
Smart Analyzer – built in tests
Tests Looking at the Metadata
Journal Entries Posted on Weekends
Journal Entries Posted on Specific Dates and Times
Journal Entries by User
Journal Entries with Specific Comments
22
Joining databases - concepts
Primary
1001 Lagos
1002 Cairo
1003 New York
1004 Paris
1005 Berlin
1006 Sydney
1007 Toronto
1008 Durban
1009 London
All records from
secondary is not
included -> select
secondary file as
primary file
Secondary
1004 France
1004 China
1006 Australia
1007 Canada
1008 South Africa
1009 UK
1010 Brazil
1011 Austria
1012 Peru
No matches in Secondary
Note that ‘1005 Berlin’ also will
be included and no empty
columns from secondary
database will be included
All records from Primary
note that ‘1004 China’ will
not be included
Matches Only
note that ‘1005 Berlin’ and
‘1004 China’ will be excluded
No matches in Primary
Note that ‘1004 China’ will
NOT be included and
empty record from primary will
be add to these 2 columns
All records in both files
23
Joining databases - results
All records from both files
All records from Primary
1001
Lagos
0
1001
Lagos
0
1002
Cairo
0
1002
Cairo
0
1003
New York
0
1003
New York
0
1004
Paris
0
1004
France
1004
Paris
1004
1004
China
1005
Berlin
0
1006
Sydney
1006
1007
Toronto
1008
1009
1005
Berlin
0
1006
Sydney
1006
Australia
1007
Toronto
1007
Canada
1008
Durban
1008
South
Africa
1009
London
1009
UK
0
1010
Brazil
0
1011
Austria
0
1012
Peru
Matches Only
France
1004
Paris
1004
France
Australia
1006
Sydney
1006
Australia
1007
Canada
1007
Toronto
1007
Canada
Durban
1008
South
Africa
1008
Durban
1008
South
Africa
London
1009
UK
1009
London
1009
UK
No Primary
No Secondary
0
1010
Brazil
0
1011
0
1012
1001
Lagos
Austria
1002
Cairo
Peru
1003
New York
1005
Berlin
24
CAATs Success Stories 3
Determined the extent of data changed by an A/R
manager modified data to awards for efficient A/R
management
Discovered numerous instances of cash awards where
the same person proposed, approved, and received.
25
MAYHEM…..and CAATs
The authors describe manipulating a major financial
accounting systems used by corporations large and
small (Great Plains) to show the importance of good
information security and accounting controls.
They identify information security and accounting
controls needed to detect these types of attacks.
http://www.securestate.com/Research%20and%20Inn
ovation/Pages/Tools.aspx
In this time of reduced resources….don’t leave the
CAATs out.
26
Questions or Comments?
27
27
Contact Information
Tim Smith
[email protected]
619-929-1221
28