open source audit software l.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Open Source Audit Software PowerPoint Presentation
Download Presentation
Open Source Audit Software

Loading in 2 Seconds...

play fullscreen
1 / 64

Open Source Audit Software - PowerPoint PPT Presentation


  • 1030 Views
  • Uploaded on

Open Source Audit Software . IIA District Conference Durham, NC 2/27/2009 Track 1 – Internal Audit Mike Blakley, EZ-R Stats, LLC. Objectives. Open source audit software – advantages / disadvantages Audit software functionality of four major software packages

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Open Source Audit Software' - flora


Download Now An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
open source audit software
Open Source Audit Software

IIA District Conference

Durham, NC

2/27/2009

Track 1 – Internal Audit

Mike Blakley, EZ-R Stats, LLC

objectives
Objectives
  • Open source audit software – advantages / disadvantages
  • Audit software functionality of four major software packages
  • SQLite - application in various audit areas
objectives cont d
Objectives (cont’d)
  • RAT-STATS - random sampling
  • "R" system and its applications
  • Cephes - basic functionality
  • Excel  open source software
what is open source software
What is open source software?
  • Source and binaries
  • Languages
  • Maintained by various persons
  • Support / development - volunteer basis
  • Licensing - GPL, Public Domain, etc.
advantages
Advantages
  • Transparency
  • Portability
  • Lower cost
disadvantages
Disadvantages
  • May require additional expertise
  • No slick front-end
  • Plain packaging
  • Support?
objectives7
Objectives
  • Open source software -, advantages/disadvantages

Next topic: Four Major Packages

four major packages
Four major packages
  • SQLite - database system
  • RAT-STATS - random sampling system
  • R - library of statistical and plotting routines
  • Cephes - mathematical and statistical routines
how excel fits in
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
recap of objectives
Recap of objectives
  • 1. Open source software -, advantages/disadvantages
  • 2. Four major software packages

Next topic is SQLite

overview
Overview
  • Developed in North Carolina!
  • Largest number of database installations
  • Public domain
  • Standards compliant - SQL92
  • Very fast, written in “C”
  • Zero installation

SQLite

example audit uses
Example Audit uses
  • Sample planning
  • Population statistics
  • Identification of duplicates
  • Match/merge
  • Benford's Law
  • Same, same, different
  • Data stratification
advantages13
Advantages
  • Cost effective - fastdatabase
  • No license cost
  • Simple to install
  • Portable
  • Standards compliant
disadvantages14
Disadvantages
  • Doesn't have every "bell and whistle"
  • Doesn't support every functionality
  • Basic system is “command line”
sqlite front ends
SQLite Front Ends
  • Excel
  • SQLite browser
  • Others
specific audit applications
Specific audit applications
  • White paper available which explains many of the topics
  • Article in EDPACS, June 2008
how to load data
How to load data
  • Load using manual "scripts"
  • Load with free software
  • Import from Excel, Access, text files
target audience
Target audience
  • Auditors
  • Audit Managers
  • Business Analysts
  • Researchers
  • Anyone working with large data volumes
screen shots of sqlitebrowser
Screen Shots of SQLiteBrowser

1. Identification of duplicates

2. “Drill down” (using where clause)

  • Population subtotals and basic statistics

Public domain

SQLite Database Browser

more information
More information
  • SQLite site – http://sqlite.org
  • EZ-R Stats – http://ezrstats.com
  • SQLite browser

http://sqlitebrowser.sourceforge.net/

wrap up objective 3
Wrap up Objective 3
  • What is SQLite?
  • What audit areas can it be used?
  • Data import

Next topic is Random Sampling

rat stats
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)
major functional areas
Major functional areas
  • 1. Random number generation
  • 2. Sample size determination
  • 3. Attribute sampling
  • 4. Variable sampling
  • 5. Types of sampling
    • stratified
    • unrestricted
    • other
how it works
How it works
  • Windows based (no Mac or Linux)
  • Simple to install
  • Some documentation
  • Works with Excel, Access and text files
advantages28
Advantages
  • Comprehensive
  • Withstood court challenges as to validity
  • Does all the computations
  • Provides basic documentation for work-papers
  • Easy to install
  • No license cost
disadvantages29
Disadvantages
  • Only certain confidence levels
  • Little transparency (FOIA)
  • Support?
screen shots
Screen Shots
  • Random numbers
  • Variable sampling
wrap up objective 4
Wrap up Objective 4
  • What is RAT-STATS?
  • Audit Areas
    • Random numbers
    • Attribute sampling
    • Variable sampling

Next topic is R

slide34
World-wide development
    • Statisticians
    • College Professors
  • Library of statistical routines
  • Extensive plotting and charting capabilities
  • R is `GNU S’
major functional areas35
Major functional areas
  • 1. Statistical computing
  • 2. Graphics
  • 3. Linear regression and modeling
  • 4. Statistical tests
  • 5. Time series analysis
  • 6. Data Classification
how it works36
How it works
  • Windows, Mac or Linux
  • Relatively simple to install
  • Extensive documentation
  • Works with
    • Excel, Access
    • text files
    • many databases (including SQLite)
audit areas
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
advantages38
Advantages
  • Comprehensive
  • Good charting and plotting capabilities
  • Extensive statistical functions
  • Easy to install
  • No license cost
disadvantages39
Disadvantages

User interface

Fairly steep learning curve

Support?

screen shots40
Screen Shots
  • Stepwise regression
  • Plot - confidence/precision intervals
wrap up objective 5
Wrap up Objective 5
  • What is R?
  • What audit areas can it be used to address

Next topic is Cephes

cephes
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
major functional areas45
Major functional areas
  • 1. Statistical computing
  • 2. Mathematical computations
  • 3. Probability
how it works46
How it works
  • Windows only
  • Relatively simple to install
  • Extensive documentation
  • Works as stand alone routines or can be called from Excel
audit areas47
Audit areas
  • Sample calculations
  • Random number generation
  • Sample planning
  • Population statistics
advantages48
Advantages
  • Reliable, extensive testing (IEEE)
  • Extensive statistical functions
  • Easy to install
  • No license cost
disadvantages49
Disadvantages

Support ?

example of probability functions
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
example of arithmetic and algebraic functions
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
screen shots52
Screen shots

1. Calculations with Excel VBA

2. Plot with confidence/precision intervals

wrap up objective 6
Wrap up Objective 6
  • What is Cephes?
  • Useful for evaluation of random samples, linear regression, etc.

Next topic is Excel as a platform

excel as an audit platform
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
activex data objects
ActiveX Data Objects

AuditNet

"End User Database Access Using Excel"

http://www.auditnet.org/articles/MB200803.htm

Example is use of SQLite

visual basic for applications
Visual Basic for Applications
  • Very extensive capabilities
  • Entire applications written in VBA
  • Powerful audit tool
  • Example library
calling external routines
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
com servers
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
advantages61
Advantages
  • Already widely used
  • Many "built-in" capabilities
  • Macro language VBA widely understood
disadvantages62
Disadvantages

Learning curve

Support?

wrap up objective 7
Wrap up Objective 7
  • Excel as an audit platform
  • Uses include:
    • database queries,
    • running R,
    • complex stat calculations
questions
Questions?

Contact info:

919-715-4791

Mike.Blakley@ezrstats.com

View this presentation