AICPA National Governmental Accounting and Auditing Update

1
AICPA National Governmental Accounting and
Auditing Update

• Hands-On CAATS
• August 21, 2007
• Matthew Johnson, CPA, CISA, CGFM
• mjohnson_at_cottoncpa.com

2
Hands-On CAATS

• the practice of using computers to automate or
  simplify the audit process.

3
Hands-On CAATS

• Computer-based tools that permit auditors to
  increase their personal productivity as well as
  that of the audit function. CAATTs Other
  BEASTS for Auditors, by David G. Coderre 1998,
  Global Audit Publications
• The power of CAATs
• The Department of Agriculture has 85,000
  employees. How long would it take auditors to
  manually search payroll records to identify
  duplicate payments by searching for duplicate
  social security numbers?

4
Hands-On CAATS

• For today, we will look at
• Word processing
• Spreadsheet
• Database
• Statistical sampling
• Data mining
• Real time testing programs
• Integrated audit software
• Data analysis
• Artificial intelligence/expert systems

5
Data Analysis

• Useful for identifying errors, misappropriation
  of assets and fraudulent financial reporting.
• Allows limitless number of analytical
  relationships to be assessed
• within large databases
• comparing large databases.
• Identifies anomalies.
• Further (human) investigation is almost always
  needed.

6
Data Analysis

• Access and Excel
• Interactive Data Extraction and Analysis (IDEA)
• Audit Command Language (ACL)
• Windows-based and user friendly
• Requires creativity and imagination
• Supplements--but does not replace--intelligent
  audit work

7
Data Analysis Techniques

• Filters
• Sorts
• Statistics
• Gaps
• Duplicates
• Aging
• Confirmations
• Samples

Classification Summarization Stratification Join
and Define Relationships Trend Analysis Regression
Analysis Parallel Simulation Digital Analysis
8
Data Analysis - Filters

• Only shows certain transactions that I might be
  interested in. (i.e. Filter out the static
  inherent in large databases.)
• Show me all invoices approved and paid on days
  when the boss was not in the office.
• Show me all transactions initiated on Sundays and
  holidays.

9
Data Analysis - Sorts

• Puts the data into an order that is easier to
  analyze (e.g. Aged Accounts Payable).
• Sort by vendor name or
• Sort by vendor address or
• Sort by vendor telephone number or
• Sort by EFT account number.

10
Data Analysis - Statistics

• Average value, standard deviation, highest/lowest
  value, etc.
• Show me all vendor payments that are more than
  two standard deviations from the mean.
• Show me every unit price for product A that is
  more than 1 standard deviation from the mean.

11
Data Analysis - Gaps

• Used when we have pre-numbered forms or
  transactions.
• Show me all missing checks.
• Show me missing health insurance claims.
• Show me missing purchase orders.
• Show me missing vouchers for benefits.

12
Data Analysis - Duplicates

• Used in situations when duplicates should not
  occur.
• Show me all payroll transactions in the pay
  period that have duplicate
• Payees
• SSN
• Payment addresses
• EFT transfer destinations

13
Data Analysis - Aging

• We all use aged A/P and A/R listings.
• AGE function can calculate the number of days
  between two date fields.
• Show me the time elapsed from when an item was
  purchased and placed in inventory and when it was
  disposed of as scrap or due to obsolescence.
• Show me the time elapsed between award of a major
  contract and the contracting officers purchase
  of a condo in the Bahamas.

14
Data Analysis Expressions Calculations

• Can be used to test for and detect errors in
  accounting software.
• But, can also be used to detect outside
  intervention into an otherwise okay system.
• Recalculate units x unit price and show me all
  cases where the result does not equal the value
  in the extended amount field.
• Recalculate vacation accruals for every payroll
  transaction and show me every case where the
  result differs from what was recorded.

15
Data Analysis - Classify

• Counts the number of unique values in a selected
  character field(s) and the corresponding totals
  of other numeric fields.
• Show me how many hysterectomy procedures have
  been performed, by sex
• Female 127
• Male 3
• sex field blank 12

16
Data Analysis - Summarize

• Similar to CLASSIFY, but sorts data by specified
  field and provides a detailed listing of
  transaction information for all records in that
  classification.
• Show me all transactions for vendor A, B, C.
• Show me all payroll transactions for employee A,
  B, C.
• Show me all employee expenses transactions for
  employee A, B, C.

17
Data Analysis - Stratify

• Groups transactions into specified ranges of
  values (strata)
• Show me numbers of contracts within certain
  strata
• 0 - 45,000 27
• 45,001 - 49,000 12
• 49,001 - 50,000 425
• 50,001 - 100,000 5
• 100,001 - 1,000,000 3

18
Data Analysis Join Define Relation

• JOIN combines data from two databases into a
  single database.
• DEFINE lets you specify the relationship of
  interest.
• Show me employee expense claims by employees who
  were on vacation/sick/holiday leave on the day
  the expense was incurred.
• Show me all instances in which a person made a
  DNC/RNC campaign contribution within 60 days of
  getting a Federally-guaranteed loan.

19
Data Analysis Trend/Regression Analysis

• Trend analysis looks at historical trends that
  data exhibit.
• Regression analysis uses historical data to
  predict what future values will be.
• Looking at electricity or water usage trends will
  show anomalous changes.
• Regression analysis of electricity or water usage
  will predict what the usage should be in the
  current or future periods.

20
Data Analysis Testing Payroll

• Databases involved
• Payroll
• Personnel
• Time attendance
• Employee directory
• Terminated employee directory
• Valid SSN listings
• Leave and vacation
• Employee expenses

21
Data Analysis Testing Payroll

• Duplicates
• Payees on same date
• Same or similar names
• SSNs
• Addresses
• Telephone numbers
• Direct deposit account numbers
• Work locations
• Work telephone numbers

22
Data Analysis Testing Payroll

• Comparisons Paycheck, but ...
• No
• time attendance
• pension or other
• leave ever taken
• expenses
• valid SSN
• or scarce, personnel data
• Terminated employee
• Not in employee directory deductions

23
Data Analysis Testing Payroll

• Employees with P.O. Box addresses
• Wages inconsistent with job classification
• Payroll disbursements above certain amounts (by
  pay period or cumulative)
• Premium pay above certain amounts
• Benefit contributions but not on payroll
• Pay date precedes employment date
• Overtime with other activity indicators

24
Data Analysis Testing Purchases

• Databases involved
• Vendor
• Personnel
• Dun Bradstreet
• Employee directory
• Terminated employee directory
• Employee expenses
• Inventory
• Accounts payable accounts receivable

25
Data Analysis Testing Purchases

• Duplicate
• disbursement amounts
• invoice numbers/dates
• disbursements on same date
• Disbursement to vendor not in vendor database
• Vendor name/address/phone same as employee
  name/address/phone
• Vendor name similar to employee name

26
Data Analysis Testing Purchases

• Purchases inconsistent with inventory
• Missing purchase request (PR) , purchase order
  (PO) , receiving report (RR)
• Names on PR, PO, or RR are
• missing or
• the same or
• not in employee database
• Vendor address is P.O. Box
• Invoices pay to address different from address
  in vendor or contract database

27
Data Analysis Testing Purchases

• Vendor with no telephone number or contact name
• Invoice numbers from same vendor in unbroken
  sequence
• Invoice amounts just below higher-level-approval-r
  equired threshold
• Multiple vendors with same address, telephone,
  contact person
• Vendors with similar or similar-sounding names

28
Benfords Law

• Benford's Law (which was first mentioned in 1881
  by the astronomer Simon Newcomb), states that if
  we randomly select a number from a table of
  physical constants or statistical data, the
  probability that the first digit will be a "1" is
  about 0.301, rather than 0.1 as we might expect
  if all digits were equally likely. In general,
  the "law" says that the probability of the first
  digit being a "d" is

29
Benfords Law

• This implies that a number in a table of physical
  constants is more likely to begin with a smaller
  digit than a larger digit.

30
Benfords Law

• A Digital Analysis Technique
• The first digits of numbers are not randomly
  distributed.
• Distribution of first four digits
• 1 -- 30.1
• 2 -- 17.6
• 3 -- 12.5
• 4 -- 9.7

31
Benfords Law
Frequency
First Digit
32
Benfords Law

• Invented, contrived, or manipulated numbers do
  not follow Benfords Law.
• Doesnt apply to all data sets.
• e.g. Sets of data with numbers that have imposed
  ceilings or floors such as IRA contributions or
  hourly wage rates

33
Benfords Law

• So what?
• Use Benfords Law to identify anomalous
  transactions in
• Accounts payable and expenses
• Accounts receivable and sales
• Refunds
• Payroll
• Estimations in the general ledger

34
Purchase Order Amounts
Frequency
First Digit
35
Benfords Law - Example

• Do It Yourself
• For a hands-on introduction to Benford's law,
  open The Wall Street Journal and pick a random
  starting point in the stock tables for the two
  major exchanges. Tabulate the first digits of the
  daily volume (in hundreds) for 100 stocks. About
  50 of the numbers on the list should start with a
  1 or a 2. Only about 5 numbers should start with
  a 9just as Benford's law would predict.

36
Hands-On CAATS

• Application of Computer Assisted Audit Techniques
  Using Microcomputers, Canadian Institute of
  Chartered Accountants, 1994 www.isaca.org
• CAATTs Other BEASTs for Auditors, David G.
  Coderre, Global Audit Publications, 1998
  604/669-4225 or www.acl.com
• Fraud Detection Using Data Analysis Techniques
  to Detect Fraud, David G. Coderre, Global Audit
  Publications, 1999 604/669-4225 or www.acl.com
• Digital Analysis Using Benfords Law, by Mark
  Nigrini, 2000, Global Audit Publications

37
Hands-On CAATS

• 101 ACL Applications A Toolkit for Todays
  Auditors, Richard B. Lanza, CPA, Global Audit
  Publications, 1999 604/669-4225 or www.acl.com
• About Benfords Law Ive Got Your Number, Mark
  J. Nigrini, Journal of Accountancy, May 1999
• About ACL www.acl.com
• About IDEA www.audimation.com

38
Hands on CAATS

• City of West Linn Oregon
• Annual Budget of 30 million
• 100 150 employees
• City Finance Director Elam Sandoval Magkamit
• September 27, 2000 March 29, 2005 embezzled
  1.4 million via checks payable to Magkamit
  Consulting and Larry Magkamit Magkamit
  Consulting.

39
AICPA National Governmental Accounting and
Auditing Update

• Hands-On CAATS
• August 21, 2007
• Matthew Johnson, CPA, CISA, CGFM
• mjohnson_at_cottoncpa.com