1 / 38

Data Capture and Analysis

Data Capture and Analysis. In Support of Performance Audits. GA Dept of Audits and Accounts. Outline. Introduction and Overview Perception/Changes Projects Tools Used Demo of ActiveData for Excel. DOAA Organization Chart. Organization of DOAA. State Auditor Russell W. Hinton

charlene-durham
Download Presentation

Data Capture and Analysis

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. Data Capture and Analysis In Support of Performance Audits GA Dept of Audits and Accounts

  2. Outline • Introduction and Overview • Perception/Changes • Projects • Tools Used • Demo of ActiveData for Excel GA Dept of Audits and Accounts

  3. DOAA Organization Chart

  4. Organization of DOAA • State Auditor Russell W. Hinton • Department has staff of 350 people • There are 10 Divisions • Performance Audit Division has 31 employees ( 10 % of the Department) • IT Division has 16 employees • A lot of Professional Staff - 73 with Advanced Degrees and 142 hold Certifications such as CPA, CISA, CISSP GA Dept of Audits and Accounts

  5. Performance Audit Division The purpose of a performance audit is to: determine the degree to which state funded programs and activities are accomplishing their goals and objectives; provide measurements of program results and effectiveness; identify other means of achieving the goals and objectives; evaluate efficiency in the allocation of resources; and assess compliance with laws and regulations. GA Dept of Audits and Accounts

  6. Performance Audit Division Division staff chose the lion symbol and the acronym "ROAR" while developing their Division mission statement.  Each letter of the word "ROAR" represents a different aspect of the job they do. GA Dept of Audits and Accounts

  7. IT Division The Information Technology (IT) Division's primary responsibility is supporting the technical needs of the other divisions within the Department of Audits. The goals of the IT Division are to: • Customer Service - Provide timely and accurate technical support • Application Development - Develop and maintain applications to enhance the audit process • Resource Availability -Research and evaluate potential tools to increase efficiency and effectiveness • Education - Provide IT training to educate staff in security and the use of computers and software applications • Security - Ensure the security of Information Technology resources by implementing equipment and tools to provide a stable and secure IT environment GA Dept of Audits and Accounts

  8. Confidentiality of Data • DOAA has a law 50-6-29. Power to compel production of evidence that says … the state auditor shall have access to inspect, compel production of, and copy confidential information in any form unless the law making such information confidential expressly refers to this Code section and qualifies or supersedes it in that particular instance….. GA Dept of Audits and Accounts

  9. 50-6-29. Power to compel production of evidence. Statute text • For the purpose of more completely discharging the duties resting upon him or her and to discover the truth and to make his or her reports truthful in all matters handled by him or her, the state auditor is empowered to conduct hearings, to summon witnesses, to administer oaths, to take the testimony of such witnesses, and to compel the production, inspection, and copying of documentary evidence, including without limitation evidence in electronic form and documentary evidence that is confidential or not available to the general public, at such time and place as he or she may designate for the purpose of investigating and determining the conduct and record of the employees and officials of any department of the state government. Notwithstanding any other provision of law, the state auditor shall have access to inspect, compel production of, and copy confidential information in any form unless the law making such information confidential expressly refers to this Code section and qualifies or supersedes it in that particular instance. When the audit or special examination of the state auditor is concluded, the Department of Audits and Accounts shall redact, destroy, or return to the custodial agency all confidential information except that information which the state auditor determines is necessary to retain for audit purposes or to disclose for other public purposes. For audit purposes, the state auditor may retain such confidential information in working papers as is minimally necessary to support findings and to comply with generally accepted governmental auditing standards. The state auditor may also disclose confidential information to other officers independently entitled to its receipt, such as for law enforcement purposes. Except as stated above in this Code section, confidential information in the hands of the state auditor shall have the same confidential status as it does in the hands of the custodial entity, and the state auditor shall protect its confidentiality with at least the care and procedures by which it is protected by the custodial agency or substantially equivalent care and procedures. GA Dept of Audits and Accounts

  10. Performance Audit Division Perception: MoreQualitative : Use of Technical expertise and professional judgment Less Quantitative – Use of Statistical and numerical analysis More Soft controls and less Hard controls GA Dept of Audits and Accounts

  11. Performance Audit Division • With Performance Audits – no opinion given but findings written • Not same audit each time – no template to use • Case by Case – have to understand each Program you review • Sometimes you cannot tie data directly to a figure if it is program data GA Dept of Audits and Accounts

  12. Yellow Book Changes GAO issued a listing of major changes in the July 2007 revision of Government Auditing Standards. Changes should be effective for July 1, 2008. One of the changes for Performance Audits was a section on information systems controls for the purpose of assessing audit risk and planning the audit. (Para. 7.23 through 7.27) GA Dept of Audits and Accounts

  13. Yellow Book Changes • 7.24….Auditors should obtain a sufficient understanding of information systems controls necessary to assess audit risk and plan the audit within the context of the audit objectives. • 7.25 Audit procedures to evaluate the effectiveness of significant information systems controls include (1) gaining an understanding of the system as it relates to the information and (2) identifying and evaluating the general controls and application controls that are critical to providing assurance over the reliability of the information required for the audit. • 7.26 … Depending on the significance of information systems controls to the audit objectives, the extent of audit procedures to obtain such an understanding may be limited or extensive. In addition, the nature and extent of audit risk related to information systems controls are affected by the nature of the hardware and software used, the configuration of the entity’s systems and networks, and the entity’s information systems strategy. GA Dept of Audits and Accounts

  14. Yellow Book Changes • 7.27a.The extent to which internal controls that are significant to the audit depend on the reliability of information processed or generated by information systems. • 7.27c. The relationship of information systems controls to data reliability: To obtain evidence about the reliability of computer-generated information, auditors may decide to evaluate the effectiveness of information systems controls as part of obtaining evidence about the reliability of the data. If the auditor concludes that information systems controls are effective, the auditor may reduce the extent of direct testing of data. GA Dept of Audits and Accounts

  15. Performance Audit Division • Changes: • Cannot just assess risk at High • Programs that Performance Auditors review may not be functioning correctly if the information coming from the system is wrong • Had to understand controls • More program data was being captured electronically at entities thereby allowing auditors to look at the data GA Dept of Audits and Accounts

  16. Performance Audit Division • Hiring staff that have a better understanding of computers and data analysis • Looked at toolset (added ActiveData for Excel, HTMLDB and tablets). Already used ACL. • Before – PAO just took what entity data they were given- no guarantee data was correct. Now – can recreate and make sure what entity is saying is really what it means. GA Dept of Audits and Accounts

  17. IT Division Involvement • Loading Data into Enterprise Database • Putting in a format that the auditors can then analyze themselves • Optimizing the data for efficient queries • Assisting in data mining techniques • Assisting in more complex SQL queries • Communication is Key • Relationships with the Right People (IT Directors/CIO) • Technical Advice(in terms that Performance Auditors could understand) • DOAA DBA talking directly to entity’s DBA GA Dept of Audits and Accounts

  18. Summary of Several Audits with IT Assistance • Professional Standards Commission – looking at how many people are certified. Found out that fake/testing data was put into the system. Used some of the data. Could not perform some things that PAO wanted to do because of the data. • Colleges & University – Out of State Tuition – looking at amount of revenue lost for non residence students paying resident tuition. There was a massive amount of information. GA Dept of Audits and Accounts

  19. Summary of Several Audits with IT Assistance • Governor’s Education Study Commission – looking at comparing the total teaching positions that are funded to the total teaching positions actually involved in service delivery and also looking at the number of students in each class. • Purchasing Cards – audit of purchasing card use and Performance Audits (PAO) suspected fraudulent activity. GA Dept of Audits and Accounts

  20. Technology Tools Used • ACL • ActiveData for Excel • APEX (HTMLDB) – Oracle Database • Selenium – Web Capture Tool GA Dept of Audits and Accounts

  21. Data to Analyze Process Overview Leveraging Oracle’s database we are able to bring in large datasets in a secure reliable manner which can be optimized if necessary for performance Then we can optimize queries to reduce the data volume for the analysis in which data sets may be extracted and used in other tools like ACL and ActiveData for Excel

  22. ACL – a lot of people use – GA Dept of Audits uses a lot of ACL in 3 of our larger divisions we have about 155 stand alone license and a 10 user network concurrent license – PAO still uses ACL on a limited basis. • Problems with PAO using ACL • PAO analyst does not use on a regular basis – reduced knowledge of program over time • Training new staff is expensive and time consuming • Not an intuitive program • PAO chose to look at an easier program to use – ActiveData for Excel GA Dept of Audits and Accounts

  23. ActiveData Features ActiveData for Excel enables you to easily analyze your data and provides new levels of control over your information from within Excel • Analysis Functions • Group Summary: Summarize your data, by up to three groups, in ascending or descending order. • Top/Bottom Items: Sort in ascending or descending order, and list a group of the top or bottom items. • Aging: Take your dated information and group and total it according to intervals you set. • Stratification: Takes your data and splits it into layers that you define. Get the totals and stats you need by the data groupings you want and then chart if desired. GA Dept of Audits and Accounts

  24. ActiveData Features • Duplicates: Find duplicates in your data and tag them. Once you have them identified you can remove them, put them on a new sheet or append them to an existing sheet. • Gaps: Specify a range in a column or group and then look for items that spill over. Move them to a new sheet; append them to the existing sheet. • Descriptive Statistics: Select a column, and get totals, averages, standard deviations, variances, medians, mediums, max, and minimums. • Import: Import data from a wide variety of data sources including any ODBC compliant database. GA Dept of Audits and Accounts

  25. ActiveData Features • Worksheet Functions: • Merge Sheets: Take information from separate sheets and bring them together based on a key column to provide you with a new look in an existing sheet or in a whole new sheet. • Compare Sheets: Compare two sheets with a common key and then generate totals and counts for selected columns. • Sample: Randomly sample a specified number of rows within a range GA Dept of Audits and Accounts

  26. ActiveData Features • Column Based Functions • Columns/Split Columns: Take a column of data and separate the parts you want into new columns. Separate numeric data from the left, right or middle, or split text columns by looking for delimiters such as spaces or commas. GA Dept of Audits and Accounts

  27. ActiveData Features • Cell Based Functions • Cells/Fill Selected Cells: Fill with fixed or incremental values, characters, dates, or numbers. Random fill with numbers or dates. Specify the start and end values for incremental and random values. Fill random items by selecting ranges from your existing worksheet. • Convert Selected Cells: Select cells and convert them in a number of ways including: Change to proper case; upper/lower case; remove spaces and non printing characters; or convert to values, dates, numbers, text. GA Dept of Audits and Accounts

  28. Oracle’s APEX (HTMLDB)Application Express • Packaged with the Oracle database and is a web application tool kit to help in collaboration efforts. • Web based https: access to query and work with large data sets. REF LINK:http://www.oracle.com/technology/products/database/application_express/html/what_is_apex.html

  29. Main Working Area

  30. Data Query and SQL Tools • Data can be queried in several ways depending on the staff skill set, SQL Commands, pre canned scripts, or use of the query builder • These scripts can be saved and shared in a collaborative web environment so others can start to leverage existing knowledge

  31. Query Builder

  32. Data Loading Tools The Loading options enable a user to load data from many formats which can create virtual private databases for individuals to share or keep private. You can append information and or create new tables with out the assistance of a Database Administrator.

  33. Data downloading You can extract smaller datasets back from Oracle just by clicking the download link

  34. Performance Audits Purchase Card Audit We employed a Data Capture and Payback tool which enabled us to capture web traffic and replay while changing the values.

  35. Selenium – Web Application Testing Tool • FireFox Plug in • Allows capture of web traffic from client machine to web server (html) • Allows replay with parameterization of values • Other tools: Junit, JMeter Ref: http://www.junit.org/index.htm

  36. The Not to be Mentioned Gift Card Company • The no-named system did not employ any brute force crack attempts against an account security code plus a valid account number. • We were able to see the complete transaction history

  37. Demo of ActiveData for Excel GA Dept of Audits and Accounts

  38. Looking Forward • Continue to use data analysis/capture in audits • Tablets (just ordered 25 new Toshiba tablets) • Auditors can use voice recognition software on interviews • Use more forms with tablets to document systems • Electronic Workpapers ( DOAA moving from AS/2 to CaseWare for financial audits – looking at other programs for Performance Audits) GA Dept of Audits and Accounts

More Related