adamico's picture
From adamico rss RSS  subscribe Subscribe

Implementing Data Analysis and Extraction Tools such as ACL 

Implementing Data Analysis and Extraction Tools such as ACL

 

 
 
Tags:  payroll software  business process software  business analysis software 
Views:  83
Published:  November 18, 2011
 
0
download

Share plick with friends Share
save to favorite
Report Abuse Report Abuse
 
Related Plicks
Farm Business Planning Farm Business Planning

Farm Business Planning Farm Business Planning

From: mandicm
Views: 705 Comments: 0
Farm Business Planning Farm Business Planning
 
SYNNEX CORP10K_021307

SYNNEX CORP10K_021307

From: avio
Views: 447 Comments: 0
SYNNEX CORP10K_021307
 
Halfpricesoft.com Responds to Data-protection Needs of Payroll Software Users

Halfpricesoft.com Responds to Data-protection Needs of Payroll Software Users

From: jeffhr01
Views: 196 Comments: 0
(1888PressRelease) ezPaycheck payroll software adds new data-backup feature to secure data in a password-protected file on a external drive, CD/DVD disc, or remote server. Learn more at http://www.halfpricesoft.com
(more)

 
Financial Information Analysis

Financial Information Analysis

From: ristiid
Views: 175 Comments: 0

 
Download It

Download It

From: astau
Views: 179 Comments: 0

 
See all 
 
More from this user
Making Software in China

Making Software in China

From: adamico
Views: 368
Comments: 0

Saxon Equileather Ladies Zip Up Paddock Boot

Saxon Equileather Ladies Zip Up Paddock Boot

From: adamico
Views: 375
Comments: 0

XBAM Tutorial (June 08)

XBAM Tutorial (June 08)

From: adamico
Views: 520
Comments: 0

Developing Compliance Programs, Managing Crisis and ...

Developing Compliance Programs, Managing Crisis and ...

From: adamico
Views: 109
Comments: 0

Classroom_Control_H O.doc

Classroom_Control_HO.doc

From: adamico
Views: 601
Comments: 0

Paper Presentation On B.Tech, M.Tech Ece Ieee 802.11b Wi Fi™ Wireless La Ns

Paper Presentation On B.Tech, M.Tech Ece Ieee 802.11b Wi Fi™ Wireless La Ns

From: adamico
Views: 573
Comments: 0

See all 
 
 
 URL:          AddThis Social Bookmark Button
Embed Thin Player: (fits in most blogs)
Embed Full Player :
 
 

Name

Email (will NOT be shown to other users)

 

 
 
Comments: (watch)
 
 
Notes:
 
Slide 1: Implementing Data Analysis and Extraction Tools such as ACL By Kate Head, Audit and Investigations Manager University of South Florida Most audit managers believe they have neither the time nor expertise to implement a data analysis tool over the entire department or organization. While there is an “implementation cost,” much of the time spent understanding the financial applications and building a collaborative relationship with the information technology (IT) staff will increase the effectiveness of your entire audit operation. In addition implementation costs are partially offset by selling the value-added concept of continuous monitoring to operational management which ultimately reduces audit time. A brief discussion of the benefits gained by the audit shop is included in Chart A. Steps to implementation There are five basis steps to implementing a DATA ANALYSIS TOOL:  Gain an understanding of systems and data;  Develop a collaborative relationship with the Information Technology staff;  Obtain training on the software (informal or formal);  Import data and test the software; and  Assimilate the DATA ANALYSIS TOOL into the audit model. Many times auditors are asked to be involved in new system implementation teams for financial systems. The auditors are often called in after the evaluation of the product has occurred and a relationship has been established with the “owner” of the product. In many instances, key implementation staff has already been trained on how to use the software, and test data has been loaded into a non-production version of the product for testing. Auditors are asked to assist with the process of assimilating the product into the current work process. As a result, many audit staffs have an understanding of the basic model for new system implementation but lack an overall understanding of how to perform these steps. Implementing a DATA ANALYSIS TOOL into audit operations is identical to implementing a new financial system for the organization, except it does not necessarily have organization-wide implications. Auditors should learn from these experiences and make sure that the users (in this case, auditors) and information technology support personnel are involved in the implementation process. ©2002 USF OIG
Slide 2: Step One: Gaining an understanding of the systems and data. Identify the applications The first step is to evaluate the financial system that will interact with the data analysis tool. A good start is to identify those systems audited most. This is likely to be the financial systems (payroll, purchasing, accounts receivable, accounts payable, etc.), but also may incorporate critical operational systems such as client databases--patients, students, and customers, donor information, or even warranty claims. Start with the basics and ask the name of the software, manufacturer, and version in use. Determine if there are plans to install upgrades or replace these systems in the next year. Ask what platform (mainframe, client server, or PC based) and database structure (Legacy, Ramis, Oracle, FoxPro, Access etc) is used. Next determine what reporting tools or methods are being utilized (Data Warehouse, FOCUS, ACCESS, CRYSTAL application specific tools). Often, the files generated for reporting purposes are in a user-friendlier file structure than the original database and, therefore, are easier to import into a data analysis tool. Research the application It is now time to do what many auditors do best. -Do the homework. Treat this part of the process as if you were performing the preliminary phase of an audit and need to understand the risks associated with the function you are reviewing. Use the Internet as a resource to obtain information about the systems, the data base structure, and reporting tools. Most vendors have web pages and “white papers” on their sites. A “white paper” is a non-technical article about the application. These articles are helpful to get general information regarding how the software functions. They usually compare themselves to other similar applications with which you may be more familiar. Remember that these are “sales pitches,” and therefore are unlikely to discuss the applications weaknesses. They do, however, often discuss problems in the underlying database structures that they have supposedly overcome. It is an easy way to learn the issues. There are other resources, such as IT audit.org, which has a series of articles for the new EDP auditor as well as articles related to the newer applications and database structures. These articles provide good information on the weaknesses of systems as well as a good overall understanding of technology related issues. ©2002 USF OIG
Slide 3: Since IT audit.org is sponsored by ACL (as well as others), there are often good examples on how to implement data analysis tools into the audit department as well. Talk to system “owners” Talk to the operational users and determine how they are reporting on the data in these applications. Obtain a list of queries, views, and reports used by them. A query is a program that extracts data from one or more sources (often tables) and collects it in a machine-readable file that is used for reporting purposes. A query may provide the information to the screen, a printer, or to a machinereadable file called a view. Views are often “temporary” in that they are generated at the request of the user by launching a query. The benefit of a view is that you can use a reporting tool (to select some or all of the data elements in a view to generate a report from. If a view is developed efficiently, many users can use the same view for a variety of unrelated reports by choosing different report elements. Since you can also report on these views, it is important to understand what views are available and what data elements they contain. Using a view to import information into a data analysis tool can eliminate the need for the audit staff to import multiple tables and then join them to obtain the needed information or create new queries. Creating new queries requires an understanding of the applications query tools. A machine-readable report is a query using another reporting tool that has been saved permanently. Reports are less efficient to use than views or queries since you must “strip” the formatting from a report to import the underlying data into a data analysis tool. In addition, reports often summarize data, and the detail behind the accumulated numbers cannot be obtained. The benefit of queries, views, and report files is that they already exist and will not require additional effort on behalf of the IT staff beyond granting access to these files. Know your data. We now understand our application, know what reporting tools are available, and have a list of queries, views, and reports and their data elements. Now what? The next step is to learn about the other data available in the database that you may want to gain access to. Once a collaborative relationship has been developed with the IT staff, they will be able to add additional data elements that may be needed to existing views, or create a view specifically for the audit department. ©2002 USF OIG
Slide 4: Ask the data owner for a data dictionary. This is a report of what data exists in an application and were it can be located. In a mainframe (legacy) based environment, this will indicate the file location and the field character location. Since these databases are a continual stream of characters on a single line, the data elements (lets say last name) will appear in characters X to XX (maybe 9 to 21). Fields are usually a fixed length (in this case 13 characters). The dictionary would also tell the type of data (alphabetical, numerical, or alphanumeric) and the format of the data (for instances 2 decimal points this data will be needed when importing the data into the data analysis tool, because if you were not to tell the tool that the data had two decimal points 112.52 would import in as 11,252. Client server applications tend to be relational databases where data is maintained in various tables. The data dictionary will tell in which table the data can be found (frequently more than one in the case of key fields), the data element's name (i.e. LNAME), as well as a description of what is contained in the field. The location is not needed since most are OBDC compliant, and a software driver will locate the field using the field name and table (or view) the auditor provided. The PC based application is similar to a Client server application, but instead of a table name a file name and field name are received. Often the data dictionary will define codes used to reduce the field size (such as H is hourly and S is salaried). This list of codes will be invaluable when filtering the data. I think it can be seen how valuable the information learned in this step will be to the performance of all audit projects. Step Two: Develop a Collaborative Relationship with IT Now that you have become familiar with your applications, understand their strengths and weakness, know the reporting tools, queries, views and reports available, as well as other available data, it is time to meet with the technology staff responsible for maintaining the application. There should be two separate individuals: the database administrator (DBA) and system administrator (Sysadm). It is important to the control environment that the sysadm control application security and the DBA maintain the system by performing backups, upgrades, correcting rule tables, etc. Both functions should not be performed by one individual. You need to meet with both the sysadm and the DBA, though you are more likely to work with the DBA on an on-going basis. Just as we ask general counsel for legal advice, you should rely on the sysadm and DBA for technical advice. Introduce them to your plans to use the data analysis tool to extract, analyze, and report on data. Be prepared to educate them on the package. It is likely that they have never heard of this form of general audit software. They will want to know that the company is reliable, and that the software is an industry-wide standard to ensure that it will not negatively affect the application. Explain to ©2002 USF OIG
Slide 5: them why you have selected this package and are not willing to use existing reporting tools since they normally are hesitant to support yet another application. Offer to let them talk to a vendor support staff member and provide them with the technical support line number. Make sure that they know that this tool will reduce the audit department's dependency on them for information and will reduce their workload. Make sure they understand that the software is a read-only tool and that it releases from the database when the query is complete. This will ensure that you will not negatively affect the database performance for other users. Discuss the needs for system access and the related database drivers. Often, access to the application is granted separately from reporting capabilities, particularly if access is at the database level. Seek advice on the best way to access, store and maintain data. Talk to IT about securing “confidential” data. Let them know your plans to utilize current queries, views, and reports when ever possible. Ask them to visit the audit department to review current hardware (especially storage capacity) and conductivity structure. All audit departments do not connect to their organization's computing systems in the same way. IT may have to change the way the audit department connects in order to ensure confidential transfer of data, or to allow a new method of file transfer. They may ask you to increase security over your network to ensure the protection of confidential data. What is important is that IT understands how committed the audit department is to ensuring that all of their concerns are addressed. This will not only make it easier to get access privileges but will establish the audit department as a control conscious area that values their expertise in determining the appropriateness of IT-based controls. You may discover that auditors and system administrators share many of the same concerns, issues, and frustrations when it comes to controlling the IT environment. It is often not the system administrators who won't establish adequate controls due to customer service concerns, but rather the application users. You will find that auditors can be the system administrator’s and DBA’s biggest allies on the control front, and they will begin to invite the audit department to security meetings to support them in addressing control concerns. The problem is often a lack of understanding of the audit function by the IT staff, and a lack of understanding of why certain controls are not implemented by the audit staff. ©2002 USF OIG
Slide 6: Step Three: Obtain training on the software Understanding basic functionality It is time to get hands on feeling of the functionality of the data analysis tool. Chances are your department received a demonstration of the product prior to purchasing the software. Maybe some of the audit staff even attended a training class, which at this point was probably a couple months ago. I would suggest you spend a day (that’s all it takes) to do the workbook tutorial that came with the software. Using the CD with sample data files, replicate the exercises in the workbook. If you are familiar with the software, this will be quick and you will be sure you're using the software properly since you are coming up with the same answers as in the book. Set aside enough time so you are not interrupted and can get through the workbook. Now find the reference guide and set it next to your computer since you will need this for your next training exercise. Importing Data Things are now getting really exciting since you are now ready to import your own data into the data analysis tool. This is part of your training since our only objective is to learn how to import data into the software. Start easy. I would not recommend that you start with a mainframe-based application (legacy, Ramis) or with a report file. Both will require the use of a data definition wizard to define the data. Start with a simple file structure. Maybe you have an ACCESS, Excel, or FOCUS file that was provided for another project. Import this into the data analysis tool and confirm the successfully import of the data. Next get a little braver and import an OBDC compliant database (Banner, Peoplesoft, FoxPro, SAP, or even ACCESS) that you have not seen before. Try importing a query or view used by another area. Remember, it is not important whether this is the specific data you need but rather that you are able to do the import successfully. By now, you should have overcome the fear of the system, and may even want to take a look at how a mainframe-based or report file might look when you import it into the data analysis tool. System testing Now it is time to start doing some meaningful testing. Select a database you are reasonably familiar with. Payroll or expenditure files work well since we generally are familiar with the data in these databases. You may even want to select an audit project you are working on or select a specific task to complete. An example might be to obtain a file of all payroll incurred by Department A. You can use the data to identify the types of employees (salaried vs. hourly), number ©2002 USF OIG
Slide 7: of employees, and value of payroll. You then may want to use the software to select a sample for testing, and then export the sample to an Excel spreadsheet for traditional testing. While this certainly does not use the data analysis tool to the fullest it does allow you to gain confidence in the product since the audit will verify the accuracy of data imported. You could then play with the data you imported to get a feel for what other things the software could do for you. You could recompute gross pay or check for excessive pay rates. You could filter the data to identify employees with the same home address (potential nepotism issue). You could identify employees with out-of-cycle pay increases to test underlying support. Your imagination is the limit. Assimilate Data Analysis Tools into your audit model Most of the steps above were probably performed by a supervisor or line manager and not by the audit director or chief audit executive (CAE). I would hope that the audit manager or supervisor was either the one performing these steps or was coordination the implementation very closely with one of there senior auditors. No major system implementations are effective without the support and involvement of key management. Since audit managers normally establish the audit plan, approve audit scopes, and make staff assignments their involvement in this project is essential. Now it is time for the implementation team to brief the CAE on the completion of testing (pre-production), and the plans for putting the system into production. Since most CAE's want a plan laid before them, I suggest the implementation team brainstorm on how to integrate data analysis tools into your audit, management advisory, and investigation projects. Read a book on implementing CAATS (such as CAATS and other BEASTS or Fraud Detection using Data Analysis, both by David Coederre). Look at some of the promotional materials available from the vendor for your industry and the case studies that have been prepared on the vendor’s web site. Join a users group or list serve such as http://tampabayiia.org/ACL.htm. Talk to other more experienced users. These are all wonderful ways to get idea. Meet with your audit staff on upcoming assignments and ask them what type of testing they envision doing. Suggest ways that we could use the data analysis tool to accomplish those steps. Read my subsequent article on assimilating data analysis into your audit environment. The most important concept here is to 1) have a plan for implementation, 2) get CAE buy-in to the product, and 3) demonstrate to audit staff a confidence in the product and a desire to utilize the tool. If the audit manager or supervisor demonstrates a lack of understanding, a lack of conviction, and/or a lack of ©2002 USF OIG
Slide 8: enthusiasm for the product then the implementation is likely to fail. Make sure that the audit staff sees the implementation as a "win" to them in terms of not only pleasing their supervisors but increasing their professional skill set and making their jobs easier. Good Luck. ©2002 USF OIG
Slide 9: Departmental Wide benefits gained during the implementation process  Learning and understanding the systems used by your organization is a cost of running a viable audit shop and is not directly related to the implementation of DAT. You must have an understanding of fiscal operations and the monitoring of those operations (many times through reporting on the database) in order to perform a risk assessment to be used in audit planning.  The time spent building a collaborative relationship with your IT department is also a crucial part of any audit operations. The increased use of automated systems, e-commerce, digital signatures, encryption, and all the other new IT related tools has made it imperative that you and your IT staff are on the same team. Many times IT personnel are your biggest supporters when it comes to convincing management of the need for additional procedures or the adaptation of current procedures in the technology area. The problems caused by inadequate controls almost always affect them in terms of increased work load, system down time, computer programming, and special reporting requests.  Continuous monitoring is simply developing automated processes designed to identify high-risk transactions, or transactions which do not comply with your established business rules. By allowing automated tools to search large databases of transactions for these items, you can identify ineffective controls, errors, and irregularities. It allows auditors and managers to concentrate monitoring activities on those transactions likely to cause problems later on. An example of continuous monitoring would be to identify invoices that are just below the limit for additional review or bidding. These invoices could then be reviewed for sequential numbers (invoice splitting) or abnormal frequency (more likely problems since we wanted to avoid the extra scrutiny). If you embrace the concept of continuous monitoring, what will eventually occur is outsourcing of some of your risk management to the operational areas by teaching them how to identify potential problems areas and transactions. This will reduce your “audit workload” and allow you to better control risk in your operations. Plus, this continuous monitoring philosophy helps management to see you more as a “value added” function, and less as a compliance function, since they do the monitoring, not you. ©2002 USF OIG

   
Time on Slide Time on Plick
Slides per Visit Slide Views Views by Location