1060 Views

Download Presentation
## Open Source Audit Software

- - - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - - -

**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.**Advantages**• Transparency • Portability • Lower cost**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”**SQLite Front Ends**• Excel • SQLite browser • Others**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?**Screen Shots**• Random numbers • Variable sampling**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?**Screen Shots**• Stepwise regression • Plot - confidence/precision intervals**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**Disadvantages**Support ?**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