DATA ANALYTICS IV

1
DATA ANALYTICS IV

• M. Popowits
• Actg 537

2
Gap Detection

• All documents and transactions should be
  sequentially numbered
• Gaps in document or transaction numbers are a
  sign of something amiss

3
Gap Detection

• Finding Gaps is a unique problem. We are looking
  for records that are not there, not in the data
  set.
• Small gaps (lt 50 records) in data set should
  represent missing documents or transactions
• Large gaps usually indicate a flawed data set
  that must be discarded

Demo
4
Duplication Tests

• Many frauds leave traces in the form of abnormal
  duplication
• Duplication of digits in numbers
• Like lots of 49s when 50 is a control
  threshold
• Duplication of record fields in a file
• Like duplicate payments of the same invoice

5
Number Duplication Test

• The Benford digit tests are a high level
  overview of possible data anomalies
• This is a basic test, it can be done for every
  data set

6
Number Duplication Test

• Now we want to drill down to find out what
  specific numbers caused spikes, as on the
  FirstTwo Digits Test

7
Number Duplication Test

• So which numbers are duplicating to cause the
  spikes? Do a Frequency query

Demo
8
Duplicate Records Tests

• These next tests search for abnormal record
  duplications by looking at certain fields in the
  records
• In our A/P file we are looking for errors
  duplicate payments of invoices

9
Duplicate Records Test (Entire Record)

• Entire record test searches for two records that
  are identical across all fields
• You can use a special query wizard in Access

Demo
10
Duplicate Records Test (Same-Same-Different)

• Same-Same-Different (SSD) test looks for records
  that have many field values that are the same and
  one that is different.
• Same invoice date
• Same invoice amount
• Different Vendor number

11
Duplicate Records Test (Same-Same-Different)

• Powerful test for errors, especially in A/P
• Sometimes the wrong vendor will be paid first,
  then the correct vendor will be paid second
• Companies are very susceptible to errors when the
  vendor file includes duplicate entries for the
  same vendor
• Sometimes in A/P, the one vendor will be listed
  in the vendor file multiple times with multiple
  vendor numbers

12
Duplicate Records Test (Same-Same-Different)

• The number of Same fields you include in the
  query depends upon the size of you data file
• In large data files, SSD or SSSD might return
  1000s of matches, so you might do SSSSD
• A match doesnt necessarily mean an error, but
  the more fields that are the same in two
  different records, the higher the likelihood that
  you do have an error

Demo
13
Duplicate Records Test (Same-Same-Different)

• SSD requires a three step process
• Query 1 SSSorD
• Query 2 SSS
• Query 3 SSSorD SSS SSD

14
Duplicate Records Test (Same-Same-Same)

• Same-Same-Same (SSS) test looks for records that
  have certain field values that are the same.
• Same invoice date
• Same invoice amount
• Same Vendor number

Demo
15
Duplicate Records Test (Same-Same-Different)

• SSD requires a three step process
• SSSorD
• SSS
• SSD

3,662 records 2,455 records 1,207 records
16
ENDDATA ANALYTICS IV

• M. Popowits
• Actg 537