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

Loading in 2 Seconds...

  share
play fullscreen
1 / 64
Download Presentation

Open Source Audit Software - PowerPoint PPT Presentation

flora
1060 Views
Download Presentation

Open Source Audit Software

- - - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

  1. Open Source Audit Software IIA District Conference Durham, NC 2/27/2009 Track 1 – Internal Audit Mike Blakley, EZ-R Stats, LLC

  2. Objectives • Open source audit software – advantages / disadvantages • Audit software functionality of four major software packages • 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 • Transparency • Portability • Lower cost

  6. Disadvantages • May require additional expertise • No slick front-end • Plain packaging • 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 - fastdatabase • 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 • Excel • SQLite browser • Others

  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 • Auditors • Audit Managers • Business Analysts • Researchers • Anyone working with large data volumes

  19. Screen Shots of SQLiteBrowser 1. Identification of duplicates 2. “Drill down” (using where clause) • 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 browser http://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 • Random numbers • 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 interface Fairly steep learning curve Support?

  40. Screen Shots • Stepwise regression • 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 only • Relatively simple to install • Extensive documentation • Works as stand alone routines or can be called from Excel

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

  48. Advantages • Reliable, extensive testing (IEEE) • Extensive statistical functions • Easy to install • No 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