1 / 64

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. Open source audit software – advantages / disadvantages Audit software functionality of four major software packages

flora
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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

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

More Related