1 / 69

Data Mining

Agenda. Introduction and Overview of Course (5 min)Introduction of Panelists

jesse
Download Presentation

Data Mining

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 Mining Using the computer to audit efficiently and effectively

    2. Agenda Introduction and Overview of Course (5 min) Introduction of Panelists – (5 min) Panelist presentations & brief questions and answers (18 min each) How to get the data ODBC set up overview of handout (15 min) Setting up and running a query in MS Access

    3. Overview Pragmatic use of CAATs in audit How to Implement CAATs Pragmatic use of CAATs in audit Data Mining Continuous Monitoring Auditing (selecting samples) with the computer How to implement CAATs. Software used Data sources Key players / resources to use short cuts tips and trapsPragmatic use of CAATs in audit Data Mining Continuous Monitoring Auditing (selecting samples) with the computer How to implement CAATs. Software used Data sources Key players / resources to use short cuts tips and traps

    4. Introduction Computer Assisted Audit Techniques Increase audit efficiency and effectiveness Provide focused audit sample selection with increased numbers of material findings Provide for continuous monitoring We will review basic concepts, present and discuss specific uses and results at various campuses, and provide an overview of how to configure a Windows computer to access an Oracle or other database. Pragmatic use of CAATs Coarse Objectives: Consider the benefits of CAATs. Practical understanding of how to implement CAATs. Know what has been successful Know what CAATs are in use at UC campuses Know who to contact for expert advice or assistance.Pragmatic use of CAATs Coarse Objectives: Consider the benefits of CAATs. Practical understanding of how to implement CAATs. Know what has been successful Know what CAATs are in use at UC campuses Know who to contact for expert advice or assistance.

    5. Vocabulary Glossary of terms CAATs – Computer Assisted Audit Techniques ODBC - Open Database Connectivity Software Driver Client DRIVER: A driver is software that works to communicate between an operating system and a peripheral. CLIENT: Software which enables one computer to "talk" with the software of another computer. www.wpunj.edu/irt/onlinehelp/glossary.htm DRIVER: A driver is software that works to communicate between an operating system and a peripheral. CLIENT: Software which enables one computer to "talk" with the software of another computer. www.wpunj.edu/irt/onlinehelp/glossary.htm

    6. Each Panelist’s Presentation Brief Overall Description Technical Description Results and Impact Difficulties or surprises Future plans

    7. Address Match Query Brief Overall Description Audit Objective: Identify fraudulent or bogus vendors in our FIS vendor database. CAAT Objective: identify vendors who were using mail box addresses as their primary business address. Audit time: 300 hours. CAAT Time: 40 hours (with follow-up testing on positive matches). Why CAAT was used: Mail box business address are a red flag for fraud and may also be used by employees who set up companies in violation of conflict of interest laws and policies. The addresses were obtained from the Yellow Pages listing for “Mail Receiving and Forwarding”. The addresses were obtained from the Yellow Pages listing for “Mail Receiving and Forwarding”.

    8. UCSC Address Match Query Technical Description Type of database: Oracle 9.i Data extraction technique: SQL query run against FIS production database Software used: MS Access, Oracle Universal Installer, ODBC driver. Examples of queries or scripts

    9. Address Match Query Technical Description – Query example MS Access Design View

    10. Address Match Query Technical Description – Query example SQL View

    11. Results and Impact We found one employee who had an independent company doing business with the University who had not been evaluated for conflict of interest. We later modified this query to identify two $500 checks mailed to the address of a Student Government Officer who supervised two interns who did not receive their payments. The supervisor had fraudulently completed a State 204 vendor data record for the interns listing her home address. University police contacted her at New York State University and she provided restitution of $1000. The interns received their payments. The local district attorney declined to prosecute because she paid restitution and was out of state.

    12. Future plans Will use this CAAT again? We just used this query last week to verify that no checks were mailed to the home address of a Temporary employee who was charged with embezzlement by her former employer We are also use this query to compare employee (PPS) address to vendor addresses in our FIS.

    13. Payments over 50K Brief Overall Description Audit Objective: Identify payments that may be lacking proper contracts, purchase orders, and/or compliance with Fair Political Practices Act competitive bidding requirements. CAAT Objective: Identify Payments over $50,000 with no purchase order. Audit time: 60 hours + continuous monitoring. CAAT Time: 60 hours. Why CAAT was used: Business Contracts Office raised concerns that some agreements were not appropriately evaluated and approved by their office. The addresses were obtained from the Yellow Pages listing for “Mail Receiving and Forwarding”. The addresses were obtained from the Yellow Pages listing for “Mail Receiving and Forwarding”.

    14. Payments over 50K Technical Description Type of database: Oracle 9.i Data extraction technique: SQL query run against FIS production database Software used: MS Access, Oracle Universal Installer, ODBC driver. Examples of queries or scripts

    15. Payments over 50K Technical Description MS Access Design View

    16. Payments over 50K Technical Description – SQL View

    17. Payments over 50K Query Results Example

    18. Results and Impact We identified one series of payments for totaling over $15 million with no Purchase Order, no written contract, and poorly documented competitive bidding in the vendor selection We identified one additional payment that should have been processed as a purchase order and approved by a buyer in central purchasing. Our FIS management started running a similar monthly report for our Central Purchasing and Business Contracts Office to use for continuous monitoring purposes.

    19. Future plans Will use this CAAT again? This has become a standard campus report. We use queries of our FIS system to select audit samples and to obtain background information for audits. Queries with transaction details (text) can be used to identify specific words. We have used one query to identify transactions were one division repeatedly bought wine with State funds. This query is also useful to identify payments that are miscoded or not properly tax reported based on key words or account codes. We have a query that identifies payments approved on-line by the payee (FIS does not prevent this systematically) We use queries of our FIS system to select audit samples and to obtain background information for audits. Queries with transaction details (text) can be used to identify specific words. We have used one query to identify transactions were one division repeatedly bought wine with State funds. This query is also useful to identify payments that are miscoded or not properly tax reported based on key words or account codes. We have a query that identifies payments approved on-line by the payee (FIS does not prevent this systematically)

    20. UCSD 1 Brief Overall Description The Audit Objective – To identify and test key payroll and procurement card metrics for possible fraudulent transactions The Objective of the CAAT – To develop a pilot program using ACL and other available tools (Excel, Access, etc.) The Size of the Audit - 200 – 300 hours Percentage of time or Hours for CAAT – approximately 50%

    21. UCSD 2 Brief Overall Description Why CAAT was used – CAAT = Audit Process with Audit Software Planning – Stratifications, analytical reviews, and exception reporting on key indicators (using 100% of the data) Internal Control – Verify data and application, identify “dirty” data, review selected exception reports Substantive Procedures – Research exception reports, minimal sampling, use audit software

    22. UCSD 3 Brief Overall Description Why CAAT was used – - Increase audit efficiency and effectiveness by accessing data directly and generating desired reports - Perform more detailed or more frequent testing

    23. UCSD 4 Technical Description Type of database - Flat files Data extraction technique - IT department downloaded selected files from the Data Warehouse - Directly downloaded from the Web - ODBC (Open Data Base Connectivity) Software used - ACL, Excel, Access,

    24. UCSD 5

    25. UCSD 6 Technical Description - Payroll Example of payroll records with overtime pay codes - Extracted of selected employees with overtime pay codes - Stratified records by a defined range - Focused on the high /exceptional overtime hour records - Reviewed time reports for legitimate reasons

    26. UCSD 7

    27. UCSD 8

    28. UCSD 9 Technical Description – P Card Example of review procurement transactions performed 10 days prior to the card cancellation date - Calculated the days between the transaction date and card cancellation date - Stratified records by calculated field - Reviewed related files (employee records, transaction records, etc)

    29. UCSD 10

    30. UCSD 11

    31. UCSD 12 Results and Impact Actions by Management Requested review for other continuous auditing tests Dollars recovered by UC N/A Actions by others Modified monthly review criteria

    32. UCSD 13 Results and Impact Apply variety of ways for continuous auditing model, including more frequent audits, use of audit software, reporting tools, data mining, and embedded monitors Changes in Audit Model Strategic auditing Auditing by exceptions - alternate materiality Wider sets of data – quantitative, qualitative

    33. UCSD 14 Difficulties or surprises Problems - Getting Data Identify necessary data (financial / non-financial) - Integrity - Classifying data Location - Database management systems - Data warehouses

    34. UCSD 15 Difficulties or surprises Problems - Analyze Data Focus on the selected tests Prepare a list for future possible tests Problems - Using the Software Use ACL 8.3

    35. UCSD 16 Difficulties or surprises Lessons Learned Develop methodologies and technologies Understand data flow though business processes Data integrity, reliability, and controls

    36. UCSD 17 Future plans Will use this CAAT again? - DEFINITELY Now will be looking other CAAT Opportunities - Identify opportunities for audit to use existing tools

    37. UCOP Example – Honoraria Payments Audit Objective: Analyze and test, as necessary, UCOP (payables) honoraria payments to ensure that: Payments appear to be reasonable and appropriate. Honoraria coded payments were appropriately classified and approved. CAAT Objective: Determine whether (payables) Honoraria payment was made to an active UC employee. Audit budget: 240 hours total (120 for CATT portion) Why CAAT was used: Look at all transactions for the period and Target the audit fieldwork. Need for additional data. “Large” UC employee data count.

    38. Technical Description UCOP Honoraria CAAT Type of database: Flat files and database. Data extraction technique: UCOP IT provided employee data. Downloaded payables data. Software used: ACL, Excel, and Hummingbird. Data integrity check: Verified that ACL file load of UC employee data records resulted in a count of 263,740. Example of queries or scripts: Joined (match) payable ID and payroll ID. Results: 27 matches found.

    39. UCOP Honoraria CAAT Results and Impact Actions by Management: Changed to consistently ask about UC employment. Dollars recovered by UC: N/A Actions by others: Policy clarified that UC employment status must be checked. Other Results or Impacts

    40. Difficulties or surprises Problems Getting Data: Production UC payroll data stored in various DB2 table at multiple UC sites is updated continuously. Summarized UC personnel data maintained at UCOP (Data warehouse - Sybase) is updated monthly and is generally two months behind. Directory level payroll/personnel data (UCFY/ Benefits), maintained at UCOP (Operational Sybase DB), is updated daily. Attempted to obtain from data warehouse by campus that was time consuming for untimely data. In the end, obtained data (SSN, employee ID, and employment status) from UCOP IT. Also found that One-time vendor payable process stored information such as Tax ID in a different file; IT provided a custom extract. Analyzing Data: Used a common practice of creating match fields to overcome differences in ID formats. Using the Software: Obtained data counts and totals for data integrity checking. Other: Due to data privacy concerns, payable data no longer displayed Tax ID (SSN); obtained override. UCOP IT wanted a “protected transmission process” which resulted in personal pickup of the data on a CD.

    41. Future plans Will use this CAAT again? - Yes Will look for other CAAT Opportunities as possible with availability of data.

    68. Summary Capsulate what has been presented Identify ways to apply training Request feedback on training session

    69. Where to Get More Information List other training sessions List books, articles, electronic sources List consulting services, other sources

    70. Data Mining Using the computer to audit efficiently and effectively

More Related