Transcript Data Analytics


Data analytics is the process of accessing, normalizing,
and modeling data with the intent of discovering useful
information
◦ Often consider a forensic tool
◦ Provides the opportunity to understand/learn more about an
entity
◦ Opportunity to incorporate unpredictability and efficiency
◦ Auditors don’t have to be experts on multiple systems
2


CAATS – Computer Aided Audit Tools or Computer
Assisted Audit Techniques
Mentioned in various AICPA SAS’s
 AU-C 315 “Understanding Entity & Its Environment…”
 .A7-.A9 – Analytical Procedures

SAS 99 – now incorporated in clarified SAS’s
◦ Management override
◦ Journal Entries
3

.A17 The use of computer assisted audit techniques
(CAATs) may enable more extensive testing of
electronic transactions and account files, which may be
useful when the auditor decides to modify the extent of
testing (for example, in responding to the risks of
material misstatement due to fraud). Such techniques
can be used to select sample transactions from key
electronic files, sort transactions with specific
characteristics, or test an entire population instead of a
sample.
4

.A29 The nature of the particular control influences the type of
audit procedure necessary to obtain audit evidence about whether
the control was operating effectively. For example, if operating
effectiveness is evidenced by documentation, the auditor may
decide to inspect such documentation to obtain audit evidence
about operating effectiveness. For other controls, however,
documentation may not be available or relevant. For example,
documentation of operation may not exist for some factors in the
control environment, such assignment of authority and
responsibility, or for some types of control activities, such as
control activities performed by a computer. In such
circumstances, audit evidence about operating effectiveness may
be obtained through inquiry in combination with other audit
procedures, such as observation or the use of CAATs.
5

Why we don’t:
◦
◦
◦
◦

It takes a lot of TIME!!!
Perceived difficulty
We’re all accountants
Entities don’t have the information needed
You have two options:
◦ Outsource it
◦ Get trained & get started
6

Can review 100% of transactions in a population or sub-population.

Can review large volume of transactions.

Search for patterns or unusual transactions.

Analyze vendors and amounts.

Identify gaps in sequence (purchase orders, invoice numbers)

Perform recalculations

Verify report (s) accuracy and completeness

Assist in identification of policy noncompliance, mistakes or errors in
processing (i.e. circumvent approval process, duplicate payments,
system calculation errors, etc.)
◦ But also can look at 100% with a different perspective
7

Understand the entity/information that you will be
analyzing
◦ Identify types of information captured by the entity



Brainstorm on potential issues where could
errors/misstatements occur
Set objectives/procedures
Acquire the data
◦ Determine if any additional data from other sources may be of
assistance in identifying red flags
◦ Talk with the entities IT (access and user roadblocks)
8

Incorporate in the audit process
◦ Understanding the entity
◦ Identification of risk
 Misstatements due to error or fraud
◦ Preliminary/Final analytical
 Efficient (scripting)
 Look at a deeper level
◦ Used as a response to risk identified previously
◦ Part of the cyclical process of risk identification/response in
audits
9







Excel
Access
Excel Add-ins (TeamMate Analytics, ACL, et.)
ACL
IDEA
IBM/Watson Analytics
Tableau
10

General Analysis
◦ Can be used for testing of Internal Controls/identify deficiencies
 Electronic approvals
 Segregation of duties
 Review(s)
◦ Can be used in preliminary, substantive, final analytics
◦ Ex:









Horizontal Analysis
Ratios
Trend analysis
Statistics
Aging
Benford’s Law
Duplicates
Gaps
Etc.
11
12
13
14


Sampling in itself is not effective;
Sampling can be improved;
◦ Assist in both Statistical/non-statistical sampling
◦ Assist in identification of Significant Items
◦ Stratification of populations
 $ dollar
 Categories – (e.g. expense codes, class of employees,
process/control $ thresholds)
15
Amount
Count
Percent of Count
Percent of
Field
Total Amount SS 50 * % of Field
<25,000.00
16,683
97.28%
20.71%
16,519,860.40
25,000.00 74,999.99
282
1.64%
14.79%
11,796,955.33
75,000.00 124,999.99
72
125,000.00 199,999.99
33
200,000.00 300,000.00
23
>300,000.00
57
0.33%
42.13%
33,615,676.86
21
17,150
100%
100%
79,784,530.50
50
Totals
10
8
0.42%
8.60%
6,862,057.34
4
0.19%
6.57%
5,241,967.19
3
0.13%
7.20%
5,748,013.38
4
16

Allows you to analyze data from multiple systems that
you possible would not be able to do.
◦ Comparison of Annual/Sick leave usage to Procurement Card
activity.
◦ Registered Sex offenders to State Medical payments for ED
medication.
◦ Suspension & Debarment list to Vendor Payments/Vendor
master file.
17

Are vendors and amounts reasonable.
- Large payments to new vendors
◦ Significant increase in use/amounts paid to a vendor
 Monthly/yearly analysis (count/amount)

Better than sampling in detecting fraud.

Match vendor addresses with employee addresses.
18

Recalculation of County Apportionment of Property
Tax Revenue

Recalculated medical payments to ensure amounts paid
complied with established rates.
◦ Identified unusual insurance payment amounts (0.01).
◦ Claims where it appears the payments were not credited prior
to the agency paying the claim.
◦ Wrong rates were paid.
19

Perform reconciliations
◦ Amounts reported on Financial Statements
◦ Subsidiary Ledgers to the General Ledger
◦ Additions/Deletions from Capital Asset Listings
◦ Detailed Data to Summary Reports
20

Data Entry/inconsistencies –
◦ Addresses
 PO Box 123, P.O. Box 123, P.O. Box # 123
◦ Vendor/Payee Names
 BOCC, Board of County Comm., Board of County Commissioners
 Chris Pembrook, Christopher Pembrook, Chris D. Pembrook

Analytic Software allow for easy clean-up
21

Promote audit efficiency
◦ Automate common test/procedures

Continuous Monitoring
◦ Script can be developed and deployed
 Automated

Non-power users can uses the work performed by
others more efficiently
22



Certain software packages provide audit/process logs
Allow for logs to be inserted into workpaper flow
ACL/Idea
◦ Importing of data does not allow data modification in software
23

P-Card programs are expanding:
◦ Promote efficiency in purchasing process
◦ Reduce cost
◦ Rebate ($)



Difficulty in monitoring
Resources for review & training may be lacking
Promotes Fraud, Waste, & Abuse
25

Perform Cardholder Analysis
◦ Identify unusual activity ($)
◦ Identify underutilization
◦ Identify individuals more prone to circumvent purchasing rules

Identification of Split Purchases
◦
◦
◦
◦
Splits based on Single Transaction Limits
Splits based on Purchasing limits
Identification of splits over multiple days
Collusion:
 Splits by departmental staff
26

Day of the Week Test
◦ Identifying weekend activity


Identification of Potential Budget abuse
Joining information from HR/Payroll
◦ Leave activity (Annual/Sick)
◦ Office Holiday’s/Office Closed

Analysis on Fuel purchases
◦ Level II use avg fuel rates to calculate anomalies
◦ Level III use actual detail on fuel purchases ($ & Gallons)
27

Creation of Keyword Search
◦ Create listing of items that are more incline to be personal
purchase or violation of policies
◦ Join listing with Level III data to identify red flag transactions

Identify high risk MCC Codes/Vendors
◦ Think “Headline in the Paper”
28
Monthly Activity
Day of the Week
DoW
Settlement_Amount COUNT1
Month
Average_Day Rank
Wed
510,495.96
3387
2086
1
Tue
469,187.67
2866
2086
2
Thu
434,689.09
2714
2086
3
$_Amount
COUNT1 Avg_Month
Rank
06
413,351.64
2342
1217
1
01
233,153.35
977
1217
2
10
215,235.85
1340
1217
3
05
210,911.48
1125
1217
4
02
200,571.66
1198
1217
5
09
197,809.81
1280
1217
6
Fri
416,700.26
2355
2086
4
Mon
317,074.15
1726
2086
5
Sat
151,037.25
1135
2086
6
08
193,707.93
1098
1217
7
Sun
71,869.96
2,371,054.34
422
2086
7
04
192,616.04
1224
1217
8
03
183,668.53
1101
1217
9
07
162,860.40
1049
1217
10
11
153,941.70
1032
1217
11
12
Total
113,225.95
530,028.05
839
1217
12
Total
29
CARDHOLDER_NAME Tran_Date CA_Dept
18
33
23
23
2/9/2012 Athletics
2/10/2012 Athletics
6/4/2012 Education
6/8/2012 Education
Total
Vendor_Name
Settlement_Amount
SINGLE_TRAN_LIMIT
Description
Athletic Supply Store
1,282.30
2500 Equipment for Player
Athletic Supply Store
1,340.30
2500 Equipment
Office Supply Store
327.26
750 Office Supplies
Office Supply Store
483.81
750 Office Supplies
$
3,433.67
30
More likely to Circumvent Purchasing Rules
ACCOUNT_NUMBER
max1
max2
min1
max_max2
max_min
COUNT1
Total
AVG_TRANS_AMT
Single_Amount_Limit
MAX_PRCT_STL
************0856
2,480.03
1,368.68
10.00 1.81
248.00
116 32803.42
271.02
2,499.00
99%
************9797
2,472.00
2,160.00
5.00 1.14
494.40
78 21573.6
276.58
2,499.00
99%
************2989
2,439.50
899.63
0.57 2.71
4,279.82
79 7309.34
92.13
2,499.00
98%
************5644
2,437.05
2,262.44
4.50 1.08
541.57
66 17361.16
210.97
2,499.00
98%
************7308
2,460.00
1,950.00
5.89 1.26
417.66
38 20394.23
536.69
2,499.00
98%
************0417
2,430.00
2,250.00
3.84 1.08
632.81
161 32142.13
199.64
2,499.00
97%
************7935
2,415.00
1,222.60
2.44 1.98
989.75
38 7654.38
201.43
2,499.00
97%
************9386
2,431.00
1,922.06
1.44 1.26
1,688.19
139 34445.9
247.73
2,499.00
97%
************9528
2,418.83
2,396.00
1.30 1.01
1,860.64
55 13313.85
240.78
2,499.00
97%
31

Easily establish comparisons to identify potential issues
◦
◦
◦
◦
Usage by Cardholder
MCC
Vendor
Ability to perform monthly, quarterly, yearly comparisons
32

Vendors and accounts payable

Expense reimbursements

Payroll

Journal Entries

Pension Census Data

Grants

And anywhere you have data!!!
34
Vendor Analysis

Analyze vendors population for recurring vendors (ex. utilities,
monthly services, etc.)

Establish trends or average for certain vendors.
◦ Also consider types of expenditures
 Supplies
 Construction Projects
 Equipment

Look for unusual payments above average.

Identify Vendors that may create a conflict of interest

Compare Vendor Master File address to Employee file
◦ Vend File: 320 My St. , 73160
◦ Clean data: 320 , 73160
HR file – 320 My Street, 73160
35
Vendor Analysis

Look for increases in amount and payment frequency
over time.

Look for professional services or consulting contracts.

Duplicate invoices or duplicate payments for same
services. (invoice #, Amounts, Sequential invoicing)

Multiple Vendors with Same Address
36
Vendor Analysis

Unusual vendors or payees

High dollar /low frequency payments

High frequency /low dollar payments
37
Expense Reimbursements

Reimbursements to employees
◦ Unusual activity
◦ Duplicate reimbursement
◦ Personal Use reimbursement

Travel reimbursements
◦ Duplicate claims for same expense
◦ Unallowable expenses
◦ Expense claimed but paid on procard
38
Payroll Analysis

Summarize payments by month for each employee.
Calculate approximate biweekly net based on approved
compensation.

Identify outliers or unusual payment amounts.

Look for unusual coding for small number of
payments.
39
Payroll Analysis

Look for extra payments. One time payments for “special
projects” or “additional duties” or “stipends” which were not
approved.

Look for Holiday bonuses!!!!!

Compare employees receiving payments to current employees.

Identify duplicate employees with same direct deposit account.

Inflated salaries or hours
◦ Statutory or board approved salaries for upper management
40
Journal Entries


One of the few specially required test
Improper Management Override.
◦ Also segregation of duties

Approach to testing Journal Entries:
◦
◦
◦
◦
◦
Who,
What,
When,
Where,
Why
41
Journal Entries

Who
◦

Summarize journal entries by the persons entering to determine if they’re authorized.
What
◦ Extract nonstandard or manual journal entries (versus system entries such as an accounts
payable ledger posting) for further analysis.
◦ Stratify size of journal entries based on amount (using the debit side of the transaction).
◦ Summarize journal entries by general ledger account to identify repetitive and unique
account sequences used in the journal entry (based on the first five debit and credit
account postings).
◦ Summarize general ledger activity on the amount field (absolute value of debit or credit)
to identify the top occurring amounts.

When
◦ Extract journal entries posted on weekends and holidays.
◦ Extract Journal entries posted an odd times (after hours, way early)
◦ 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.
42
Journal Entries

Where
◦ Extract journal entries made 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).
◦ Extract debits in revenue and summarize by general ledger account.

Why (Unusual Activity)
◦ Extract general ledger transaction amounts (debit or credit) that exceed the average
amounts for that general ledger account by a specified percentage. (Five times the average
is a good starting point.)
◦ Extract journal entries that equate to round/even multiples .
◦ Extract journal entries with key texts such as “plug” and “net to zero” anywhere in the
record.
◦ Extract journal entries that are made below set accounting department approval limits,
especially multiple entries of amounts below such limits.
 Also perform benford analysis
◦ Extract journal entries that don’t net to zero/balance (debits less credits).
43
Pension Census Data

New emphasis on Pension census data
◦ Gasb 67/68
◦ AICPA Whitepapers
 Cost-Sharing (Plan Auditor)
 Agent Multi Employer (Plan Auditor and Employer Auditor)
 Single Employer Plans (Employer auditor or auditor engage by the
plan)
44
Pension Census Data

Census Data:
◦ Comparison/Reconciliation of Prior Year to Current Year file:
 Identify Additions/deletion of participants
 Identify Null Fields
 Compare Participants information and identify changes:
 Salary







Age
Sex
Birthdate
Hire Date
Termination/Retirement Date
Marital Status
Job classification
45