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

Download Presentation

Open Source Audit Software

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.

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 • SQLite - application in various audit areas

Objectives (cont’d) • RAT-STATS - random sampling • "R" system and its applications • Cephes - basic functionality • Excel 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.

Disadvantages • May require additional expertise • No slick front-end • Plain packaging • Support?

Objectives • Open source software -, advantages/disadvantages Next topic: 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 • 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 • 1. Open source software -, advantages/disadvantages • 2. Four major software packages Next topic is SQLite

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 • Sample planning • Population statistics • Identification of duplicates • Match/merge • Benford's Law • Same, same, different • Data stratification

Advantages • Cost effective - fastdatabase • No license cost • Simple to install • Portable • Standards compliant

Disadvantages • Doesn't have every "bell and whistle" • Doesn't support every functionality • Basic system is “command line”

Specific audit applications • White paper available which explains many of the topics • Article in EDPACS, June 2008

How to load data • Load using manual "scripts" • Load with free software • Import from Excel, Access, text files

Target audience • Auditors • Audit Managers • Business Analysts • Researchers • Anyone working with large data volumes

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 • SQLite site – http://sqlite.org • EZ-R Stats – http://ezrstats.com • SQLite browser http://sqlitebrowser.sourceforge.net/

Wrap up Objective 3 • What is SQLite? • What audit areas can it be used? • Data import Next topic is Random Sampling

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 • 1. Random number generation • 2. Sample size determination • 3. Attribute sampling • 4. Variable sampling • 5. Types of sampling • stratified • unrestricted • other

How it works • Windows based (no Mac or Linux) • Simple to install • Some documentation • Works with Excel, Access and text files

Advantages • Comprehensive • Withstood court challenges as to validity • Does all the computations • Provides basic documentation for work-papers • Easy to install • No license cost

Disadvantages • Only certain confidence levels • Little transparency (FOIA) • Support?

Wrap up Objective 4 • What is RAT-STATS? • Audit Areas • Random numbers • Attribute sampling • Variable sampling Next topic is R

World-wide development • Statisticians • College Professors • Library of statistical routines • Extensive plotting and charting capabilities • R is `GNU S’

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 works • Windows, Mac or Linux • Relatively simple to install • Extensive documentation • Works with • Excel, Access • text files • many databases (including SQLite)

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

Advantages • Comprehensive • Good charting and plotting capabilities • Extensive statistical functions • Easy to install • No license cost

Disadvantages User interface Fairly steep learning curve Support?

Wrap up Objective 5 • What is R? • What audit areas can it be used to address Next topic is 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 areas • 1. Statistical computing • 2. Mathematical computations • 3. Probability

How it works • Windows only • Relatively simple to install • Extensive documentation • Works as stand alone routines or can be called from Excel

Audit areas • Sample calculations • Random number generation • Sample planning • Population statistics

Advantages • Reliable, extensive testing (IEEE) • Extensive statistical functions • Easy to install • No license cost

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