audit software: efficient and effective use
TRANSCRIPT
1
Open Source Audit Software
IIA District ConferenceDurham, NC2/27/2009
Track 1 – Internal Audit
Mike Blakley, EZ-R Stats, LLC
2
Objectives
1. Open source audit software – advantages / disadvantages
2. Audit software functionality of four major software packages
3. SQLite - application in various audit areas
3
Objectives (cont’d)
• RAT-STATS - random sampling
• "R" system and its applications
• Cephes - basic functionality• Excel open source
software
4
What is open source software? Source and binaries Languages Maintained by various persons Support / development -
volunteer basis Licensing - GPL, Public Domain,
etc.
5
Advantages
1.Transparency2.Portability3.Lower cost
6
Disadvantages
1. May require additional expertise
2. No slick front-end3. Plain packaging4. Support?
7
Objectives
• Open source software -, advantages/disadvantages
Next topic: Four Major Packages
8
Four major packages
• SQLite - database system• RAT-STATS - random sampling
system• R - library of statistical and plotting
routines• Cephes - mathematical and
statistical routines
9
How Excel fits in
Audit tests on data in SQLite RAT-STATS - Excel
workbooks R has an Excel interface Run R scripts from Excel Cephes routines can be called
directly from Excel
10
Recap of objectives
1. Open source software -, advantages/disadvantages
2. Four major software packages
Next topic is SQLite
11
Overview
Developed in North Carolina! Largest number of database installations Public domain Standards compliant - SQL92 Very fast, written in “C” Zero installation
SQLite
12
Example Audit uses
Sample planning Population statistics Identification of duplicates Match/merge Benford's Law Same, same, different Data stratification
13
Advantages
Cost effective - fast database No license cost Simple to install Portable Standards compliant
14
Disadvantages
Doesn't have every "bell and whistle"
Doesn't support every functionality
Basic system is “command line”
15
SQLite Front Ends
ExcelSQLite browserOthers
16
Specific audit applications
White paper available which explains many of the topics
Article in EDPACS, June 2008
17
How to load data
Load using manual "scripts"
Load with free software
Import from Excel, Access, text files
18
Target audience
AuditorsAudit ManagersBusiness AnalystsResearchersAnyone working with large data
volumes
19
Screen Shots of SQLiteBrowser
1. Identification of duplicates
2. “Drill down” (using where clause)
3. Population subtotals and basic statistics
Public domain SQLite Database Browser
20
Identification of Duplicates
21
“Drill down” with where clause
22
Population Statistics
23
More information
SQLite site – http://sqlite.org
EZ-R Stats – http://ezrstats.com
SQLite browserhttp://sqlitebrowser.sourceforge.net/
24
Wrap up Objective 3
What is SQLite? What audit areas can it be used? Data import
Next topic is Random Sampling
25
RAT-STATS
Federal HHS in San Francisco, with assistance from several universities
Comprehensive Widely used in the health care industry Has withstood court challenges Are others, such as EZ-Quant (DOD)
26
Major functional areas
1. Random number generation 2. Sample size determination 3. Attribute sampling 4. Variable sampling 5. Types of sampling
stratified unrestricted other
27
How it works
Windows based (no Mac or Linux)
Simple to install Some documentation Works with Excel, Access
and text files
28
Advantages
Comprehensive Withstood court challenges
as to validity Does all the computations Provides basic documentation
for work-papers Easy to install No license cost
29
Disadvantages
Only certain confidence levels
Little transparency (FOIA) Support?
30
Screen Shots
1. Random numbers2. Variable sampling
31
Random numbers
32
Variable sampling
33
Wrap up Objective 4
What is RAT-STATS? Audit Areas
Random numbers Attribute sampling Variable sampling
Next topic is R
34
World-wide development Statisticians College Professors
Library of statistical routines
Extensive plotting and charting capabilities
R is `GNU S’
35
Major functional areas
1. Statistical computing 2. Graphics 3. Linear regression and
modeling 4. Statistical tests 5. Time series analysis 6. Data Classification
36
How it works
Windows, Mac or Linux Relatively simple to install Extensive documentation Works with
Excel, Access text files many databases (including SQLite)
37
Audit areas
Excellent capabilities for regression
Does step-wise regression (quite costly in other packages)
Sample planning Population statistics Charting/plotting as part of audit
planning
38
Advantages
Comprehensive Good charting and
plotting capabilities Extensive statistical
functions Easy to install No license cost
39
Disadvantages
User interfaceFairly steep
learning curveSupport?
40
Screen Shots
1. Stepwise regression2. Plot - confidence/precision
intervals
41
Stepwise regression
42
Confidence Intervals
43
Wrap up Objective 5
What is R? What audit areas can it be used to
address
Next topic is Cephes
44
Cephes
Federal Department of Energy at Oak Ridge Laboratories
Library of mathematical and statistical routines (400+)
Adaptation of earlier versions in FORTRAN
Translated into C and Visual Basic
Highly reliable and extensively tested
45
Major functional areas
1. Statistical computing
2. Mathematical computations
3. Probability
46
How it works
Windows onlyRelatively simple to installExtensive documentationWorks as stand alone
routines or can be called from Excel
47
Audit areas
Sample calculationsRandom number generationSample planningPopulation statistics
48
Advantages
Reliable, extensive testing (IEEE)
Extensive statistical functions
Easy to installNo license cost
49
Disadvantages
Support ?
50
Example of probability functions Chi square distribution Complemented Chi square Inverse Chi square Normal distribution Inverse normal distribution Poisson distribution Inverse Poisson distribution Student's t distribution
51
Example of Arithmetic and Algebraic functions Square root Long integer square root Cube root Evaluate polynomial Round to nearest integer value Truncate upward to integer Truncate downward to integer Absolute value
52
Screen shots
1. Calculations with Excel VBA
2. Plot with confidence/precision intervals
53
Calculations with Excel VBA
54
Plot with confidence/precision intervals
55
Wrap up Objective 6
What is Cephes? Useful for evaluation of random
samples, linear regression, etc.
Next topic is Excel as a platform
56
Excel as an audit platform
Extensive capabilities, generally underused
Can be integrated with open source software
ActiveX Data Objects (ADO) Visual Basic for Applications (VBA) Calling external routines COM Servers
57
ActiveX Data Objects
AuditNet
"End User Database Access Using Excel"
http://www.auditnet.org/articles/MB200803.htm
Example is use of SQLite
58
Visual Basic for Applications
Very extensive capabilitiesEntire applications written in
VBAPowerful audit toolExample library
59
Calling external routines
Can be used to build scripts Then executed by external
applications Excel - Shell command Provides ability to perform a variety
of tasks, such as charting and plotting using R running database queries
60
COM Servers
Makes routines directly accessible to Excel using "CreateObject"
Cephes library is an example Many free com servers available Simplifies Excel by
"compartmentalizing" program logic
61
Advantages
Already widely usedMany "built-in" capabilitiesMacro language VBA widely
understood
62
Disadvantages
Learning curve
Support?
63
Wrap up Objective 7
Excel as an audit platformUses include:
database queries, running R, complex stat calculations