Report CopyRight/DMCA Form For : Fraud Detection With Activedata For Excel Ebook
Although Microsoft Excel has many powerful features and can take on many of the features of an fraud detection software it has its own set of limitations that are presented below Can only process 1 Million records Excel 2007 or 65 536 records Excel 2003 and
ActiveData for Excel Fraud Detection eBook,ActiveData for Excel Fraud Detection eBook. Copyright 3,What You Receive with this eBook 4,Purpose of the Publication Learning Objectives 4. About The Authors 5,Excel Pros and Cons 7,Power of Excel as an Fraud Detection Software 7. Limitations of Microsoft Excel as an Fraud Detection Software 7. How ActiveData for Excel Compensates for Excel s Limitations 8. How Key Data Analysis Tasks Are Met With Active Data for Excel 9. How To Get Started Running The Fraud Detection Tests 11. The Morning Of Reality 11, The Steps To Developing An ActiveData for Excel Fraud Detection Test 11. Step 1 Set Your Sights 12,Step 2 Ready Yourself 12. Step 3 Get Data 13,Step 4 Run It For Real 16,Data Files Included With This Publication 17. Why Look for Fraud in Accounts Payable 18,1 Above Average Payments To A Vendor 19. 2 Employee to Vendor Address Match 22,Why Look for Fraud in Revenue 26. 3 Missing Unusual Customer Information 27,Why Look for Fraud in General Ledger 30. 4 Stratify General Ledger Detail Information 31, 5 Identify Nonstandard Journal Entries Made After Year End 34. 2009 InformationActive Inc Michelle Shein and Richard Lanza 2. ActiveData for Excel Fraud Detection eBook, InformationActive Inc Michelle Shein and Richard B Lanza. No part of this publication may be reproduced in any form without permission in writing from Michelle Shein and Richard B. Limitation of Liability Disclaimer of Warranty, The authors have used his best efforts in preparing this publication and is not responsible for any errors or omissions They make. no representations or warranties with respect to the accuracy or completeness of the contents of this document and specifically. disclaim any implied warranties of merchantability or fitness for any particular purpose and shall in no event be liable for any. loss of profit or any other financial or commercial damage including but not limited to special incidental consequential or. other damages, ActiveData for Excel is the trademark of InformationActive Inc ACL Audit Command Language and Access Command. Language are trademarks of ACL Services Ltd IDEA is the trademark of Caseware IDEA Ltd Excel and Access are the. trademarks of Microsoft All other trademarks are the property of their respective owners. 2009 InformationActive Inc Michelle Shein and Richard Lanza 3. ActiveData for Excel Fraud Detection eBook,What You NEED with this eBook. The following test data files are included with this eBook and can be found as. SampleFraudData zip file on the web page for this eBook The zip file contains the following. files that are the ones that are referenced in the test descriptions that follow in this book. General Ledger Data xls Listing of detailed journal entries posted to a general ledger. PayablesData xls Listing of invoice level detail of paid invoices as well as a list of. purchase orders a vendor masterfile and an employee masterfile. RevenueData xls Listing of sales invoices listing of payments associated to the. invoices as well as an old and new customer masterfile. As well you will need a copy of ActiveData for Excel to do the exercises in this book If you. don t have a copy you can download one by selecting the Download button on the web page for. this eBook, For any other information please contact InformationActive Inc. info informationactive com,1 613 569 4675 x184,Purpose of the Publication Learning Objectives. The purpose of this course is fraud examiners in implementing data analysis routines using. ActiveData for Microsoft Excel, This course is not expected to explain ActiveData or Microsoft Excel concepts at length but. rather to provide guidance as to which of the product s features can be used in fraud examination. setting It walks through 5 common fraud detection tests between the accounts payable accounts. receivable and general ledger fraud detection areas. This book provides a small subset of tests that Fraud Examiners can use to implement proactive. fraud detection techniques in their organizations, InformationActive has other more in depth resources that are available for sale via their website. www informationactive com specifically, Top Audit Tests Using ActiveData for Excel is an ebook that integrates directly into the. ActiveData for Excel help menu It is a manual is to assist auditors fraud examiners and. management in implementing data analysis routines using ActiveData for Excel Use these. techniques to proactively detect organizational inefficiency ineffectiveness fraud and provide. measurable cash recoveries It walks you through 16 common audit tests within the accounts. payable accounts receivable and general ledger audit areas. 2009 InformationActive Inc Michelle Shein and Richard Lanza 4. ActiveData for Excel Fraud Detection eBook, Armed with this document users can no longer say that audit software is difficult to use For. now even a tool as simple as a spreadsheet can and should be used as an audit software With. the curtain exposed users can now focus their time on the more critical activities of creatively. dreaming up new tests and improving their business intelligence. Fraud Detection and Cash Recovery Using ActiveData for Excel is a CPE accredited 8 Hours. of NASBA approved CPE self paced self study course that walks you through how to use. ActiveData for Excel as a fraud detection and cash recovery tool The course also provides. sample data to test your skills prior to using live company data. This course will, Introduce you to the powerful combination of ActiveData and Excel. Walk you through basic fraud detection and cash recovery reporting concepts. Map out each step for the most common of fraud detection cash recovery and audit tests. Suggest fraud detection and audit procedures to perform on the resulting reports. Note This course is also available for sale via the ACFE bookstore. If you would like to provide feedback on the document we welcome and encourage it as we plan. to complete later versions Please provide your feedback via Email at. info informationactive com or questions richlanza com or PR1OR1TY optonline net. About The Authors, Michelle Shein is a highly skilled instructor with over twenty years of technical training. experience With her proficiency in both teaching and the use of desktop PC products she has. taught Fraud detection experts discovery skills to uncover fraud using the technology of. Microsoft Access and Excel, Ms Shein is the President of PR1OR1TY Computer Training Services Inc Since 1990 the. training corporation has been providing training services and PC consulting to corporate clients. helping to build the PC skills of many corporate teams With over twenty years of professional. training experience Ms Shein has taught for numerous clients including Morgan Stanley. Merrill Lynch AICPA Chubb Kraft Nabisco Comcast Toys R Us AIG AT T Bank of New. York Johnson Johnson Ciba Gigy Sandoz Barr Labs Dress Barn Bell Core Telcordia and. As a professional PC trainer for numerous years Ms Shein has taught classes in many of the. popular PC desktop products Ms Shein has specialized in teaching Microsoft Project. Microsoft Excel and Microsoft Access users as well as specializing in developing Access. applications for her client s data storage and analysis needs. Ms Shein earned a Bachelor and Master s degree in education from the State University of New. York in Fredonia New York She has used her educational and psychology background in. developing rewarding training sessions for both the advanced learner and PC user as well as for. the reluctant learner and novice PC user, 2009 InformationActive Inc Michelle Shein and Richard Lanza 5. ActiveData for Excel Fraud Detection eBook, Another product Ms Shein and Mr Lanza have co authored is the ACFE Access Training. Auditing Payables for Fraud CD series, Michelle Shein can be reached through the following means. Phone 1 973 331 1414,E mail www PR1OR1TY Optonline net. Website www PR1OR1TY com,www MichelleShein com, Rich Lanza CPA CFE PMP enables organizations in the use of technology to 1 generate. cash recoveries 2 stop profit leaks 3 move away from control issues and 4 work towards. process improvements With automated report systems and personalized coaching Rich helps. companies get quality results in minutes This is done by maximizing the technology companies. already have and showing professionals how to become info magicians He is the author of. numerous publications and training courses in ACL IDEA Access ActiveData and Excel. While he has over 15 years of experience and is a recognized leader in the use of technology. Rich also founded of AuditSoftware Net a free website devoted to using technology for. generating bottom line results This website recently started providing a free diagnostic service. to help companies better implement their fraud detection software technology To contact Rich. receive his free e newsletter get a free diagnostic or to order his products e mail him at. rich richlanza com, 2009 InformationActive Inc Michelle Shein and Richard Lanza 6. ActiveData for Excel Fraud Detection eBook,Excel Pros and Cons. Power of Excel as an Fraud Detection Software, Microsoft Excel is a ubiquitous tool and it has achieved this status by allowing a user to ease into. data analysis Rather than require database training or other certifications Microsoft Excel. allows a user with any Windows experience to pick up and get started Once started users find. spreadsheets easy to navigate and flexible enough to meet their particular needs For example a. user may have a five line spreadsheet to total some numbers or they may maintain their sales. invoices Either way Excel can normally contract or expand to the job. For accountants and Dors Excel has provided them a comfortable environment to work within. And for many data analysis tasks Excel can easily do the job Excel allows users to Sort. Subtotal Filter and Merge data They can create calculated fields as well as perform statistical. analysis such as regression For all of the above reasons and more Microsoft Excel has become. an industry standard in managing and analyzing organizational data. Limitations of Microsoft Excel as an Fraud Detection Software. Although Microsoft Excel has many powerful features and can take on many of the features of. an fraud detection software it has its own set of limitations that are presented below. Can only process 1 Million records Excel 2007 or 65 536 records Excel 2003 and. below of data which may be too small for some organizational databases. Does not document the fraud specialist s work in easy to access logs for later reference. and storage ActiveData for Excel helps with this,Allows data to be changed in the spreadsheet. Can only read a small subset of the complete types of data files available in digital. format For example EBCIDIC files stored in IBM mainframes would need to be. converted for use in Excel, Has difficulty in performing some data management and analysis tests such as relating. tables Although it can be accomplished it is an onerous task. Does not have functionality specifically tailored to the auditor For example a sample. can be calculated in just a couple of clicks with minimal training in specifically designed. fraud detection software In Excel it can be done but it does take some effort and. 2009 InformationActive Inc Michelle Shein and Richard Lanza 7. ActiveData for Excel Fraud Detection eBook, How ActiveData for Excel Compensates for Excel s Limitations. While ActiveData for Excel does not compensate for all limitations the below table outlines. where it does compensate as well as supporting explanations for when it does not. Manually How Does ActiveData for Excel,Compensated Compensate For The Limitation What. Excel Limitation Other Considerations Should Be Noted. For Uncompensated Limitations, Can only process 1Million or 65 536 records of N ActiveData for Excel does not allow for more. data which may be too small for some than the Excel limitation of rows in a. organizational databases spreadsheet but this may be a large data file for. most organizations Further other data analysis,tools i e Microsoft Access can be used to pare. down files for easier data analysis in Excel, Does not document the auditor s work in easy to Y ActiveData for Excel provides a log of each. access logs for later reference and work paper processed step as a comment in cell A1 of the. storage worksheet created via ActiveData for Excel. processing Then using the Index Sheets,function a summary page of all comment fields. can easily be created for review in essence,creating an audit log of all work performed with. the spreadsheet data, Allows data to be changed in the spreadsheet N Any text file can be opened and changed For. example a comma separated text file can be,opened in Word changed and then passed on to. the fraud detection specialist Further analysis,programs that are read only do allow. calculated fields which could be used to create a,new invoice amount field for example. Can only read a small subset of the complete N While not compensating for this limitation this. types of data files available in digital format For has become less of an issue as most middleware. example EBCIDIC files stored in IBM working with IBM mainframes or other older. mainframes would need to be converted for use in computing platforms allow data files to be. Excel converted to a text format Most database,software provides the option of exporting data. into a Microsoft Excel format, Has difficulty in performing data analysis and Y ActiveData for Excel provides over 100 data. management tests such as relating tables analysis and management features that automate. Although it can be accomplished it is an onerous functions that may be possible in Excel albeit. task extremely difficult from a technical perspective. Does not have functionality specifically tailored Y The over 100 ActiveData for Excel features are. to the fraud detection specialist For example a mostly tailored to the fraud detection specialist. sample can be calculated in just a couple of clicks and accountant but there are many additional. with minimal training in a specifically designed features that just make Excel plain old easy to. fraud detection software In Excel it can be done use For example you can use ActiveData for. but it does take some effort and guidance Excel to reorganize columns in an Excel. spreadsheet with just a few clicks rather than,inserting and cutting pasting column data one at. 2009 InformationActive Inc Michelle Shein and Richard Lanza 8. ActiveData for Excel Fraud Detection eBook, How Key Data Analysis Tasks Are Met With Active Data for Excel. The table below compares two popular fraud detection focused data analysis products for their. out of the box functionality of with ActiveData for Excel These features will be practically. applied to a host of fraud detection settings later in this publication. Data Analysis Active Data,Features Description for Excel. Append Combines two files with identical fields into a. Merge single file An example would be to merge two. years worth of accounts payable history into one, Audit Log Maintains a documentation log of all procedures. performed on a data file, Calculated Created a calculated field which can use a. Field function such as ABS for the absolute value of. Functions the field using data within the file For. example the net payroll pay to an employee,could be recalculated using the gross pay field. and deducting any withholding taxes, Cross Tabulate Cross Tabulate lets you analyze character fields. by setting them in rows and columns By cross,tabulating character fields you can produce. various summaries explore areas of interest and,accumulate numeric fields. Digital Completes digital analysis tests i e Benford s. Analysis Law, Duplicates Identifies duplicate items within a specified field. in a file For example this report could be used,to identify duplicate billings of invoices within. the sales file, Export Creates a file in another software format e g. Excel Word for testing An example would be,to export customer address information to Word. for Mail Merge ing to customer confirmation, Extract Filter Extracts specified items from one file and copies. them to another file normally using an if or,where statement Examples include extracting. all balances over a predefined limit, Gaps Identifies gaps within a specified field in a file. 2009 InformationActive Inc Michelle Shein and Richard Lanza 9. ActiveData for Excel Fraud Detection eBook,Data Analysis Active Data. Features Description for Excel,For example identify any gaps in check. Index Sort Sorts a file in ascending or descending order. An example would be sorting a file on social,security number to see if any blank or. 999999999 numbers exist, Join Relate Combines specified fields from two different. files into a single file using key fields This, function is used to create relational databases on. key fields It can also be done in an unmatched,fashion to identify differences between data. Sample Creates random or monetary unit samples from a. specified population, Statistics Calculates various statistics on a selected. numeric field,Summarize Accumulates numerical values based on a. specified key field An example would be,summarizing travel and entertainment expense. amounts by employee to identify unusually high,payment amounts. Please note that this is standard functionality within Excel. 2009 InformationActive Inc Michelle Shein and Richard Lanza 10. ActiveData for Excel Fraud Detection eBook, How To Get Started Running The Fraud Detection Tests. The Morning Of Reality, It s 9 00 a m and you awaken in a room filled with a PC a data file and Microsoft Excel But. wait just when you thought everyone has forgotten your existence you come across a note left. by your supervisor that reads Test It This may be taking things a little too far but haven t we. all at some time in our data reporting careers been faced with a similar situation Wouldn t it. have been helpful from a productivity and self confidence standpoint to own a book that would. walk you through basic reporting concepts, map out each step for the most common of fraud detection tests. equip you with sample data providing a glimpse of the resulting report prior to the. crunch time situation, suggest fraud detection procedures to perform on the resulting report. Therefore this publication gives you all the information you need to produce fraud detection. reports immediately for the most common accounting areas accounts payable accounts. receivable and the general ledger Report objectives fraud detection steps and the functional. instructions are included for each application To help practice the concepts sample data is also. provided giving you an immediate hands on experience. The Steps To Developing An ActiveData for Excel Fraud Detection. To get you started you need to put your first foot forward if you ever expect to run fraud. detection tests This section explains the four step process summarized below. Step 1 Set Your Sights As in any fraud detection risk must be assessed with tests selected to. mitigate that risk, Step 2 Ready Yourself By running the ActiveData for Excel test with the sample data. provided you can get comfortable for the real thing once you get the client s data. Step 3 Get Data The process does not have to be difficult and is a necessity if you ever. expect to run the fraud detection test, Step 4 Run It For Real Consider Other Tests Now that you know how to run the test. based on playing with the sample data all that is left is to run the test on the client s. data As you run the reports other report ideas are bound to come to mind that may not. be explained in this book With the data analysis procedures learned from this. publication you should be able to mold different permutations to create the newly desired. 2009 InformationActive Inc Michelle Shein and Richard Lanza 11. ActiveData for Excel Fraud Detection eBook,Step 1 Set Your Sights. To know where to perform fraud detection you need to assess the risk of certain events. occurring Since risk can be an amorphous concept it is generally helpful to quantify the risk. The below formula does just that,Likelihood Impact Risk Score. So in order to determine where first to perform fraud detection it is best to lay out all of the. potential areas i e accounts payable duplicate payments fraudulent payment inflated sales. etc and assign a likelihood percentage between 0 and 100 and the potential impact which. should be stated as a dollar amount whenever possible To ease this process and avoid minutia. it may be useful to set general parameters for impact i e up to 500 000 500 001 to. 1 000 000 and over 1 000 000 and likelihood 10 50 75 and 90 After each fraud. detection type is scored they can be prioritized from highest to lowest fraud risk score. Once the top risk areas are identified the following responses can be applied. Prevent Avoid Using Fraud Detection Software Reports Develop responses before. ever letting the threat occur thus improving on any company vulnerabilities As it relates. to using this document certain reports may be run and reviewed prior to certain. transaction types ever occurring, Mitigate Using Detection Software Reports Develop responses that reduce the risk to a. more manageable level As it relates to using this document certain reports may be run. and reviewed on a periodic basis, Transfer the risk could be transferred to a third party such as an insurance carrier. Therefore the goal of this step is to determine the precise tests to run which will aid you in the. Step 2 Ready Yourself, In this publication we have provided 5 reports across three major fraud detection areas. Hopefully we have identified the precise test you hope to run If not the explained tests should. get you close and teach you the needed data analysis concepts Please note that these concepts. have been reduced to ActiveData for Excel features in the section The Tests and Their Relation. To ActiveData for Excel s Features to help you make this comparison. With the test selected go to the appropriate page in this publication and. 1 Read the section Why Are We Running This Test and What To Do With The Results which. provides the alpha and the omega of the test to be performed. 2 Read the What Data Is Needed section of the test to understand the data file and fields. columns needed in order to run the test, 3 Open the sample spreadsheet provided with this publication and walk through the steps. explained in the test in order to get the final results. 4 For any concepts that you are having trouble understanding review the Getting Help. With ActiveData for Excel document that is under the Help menu in ActiveData for. 2009 InformationActive Inc Michelle Shein and Richard Lanza 12. ActiveData for Excel Fraud Detection eBook, 5 Allow yourself time to understand the results of each test Begin to imagine how your. data will be represented in the reports Ask yourself. Is the test providing me with enough information to prevent or mitigate the risk. Do I need to add more tests, Does the presentation of the report convey its message effectively. Step 3 Get Data, With an understanding of the risk to be mitigated the test to be performed and the data required. as stipulated in the What Data Is Needed section of each fraud detection test all that is. needed is to obtain client data and walk through the same test steps It is suggested that prior to. requesting data that all expected reports be identified so that one request is made of the client. Getting data can be broken into the following logical process steps. Step 3a Making Arrangements with the Client to Obtain Data. Step 3b Transferring the Client s Data, Step 3c Verifying the Data Received from the Client. Step 3a Making Arrangements with the Client to Obtain Data. You should meet with the appropriate client personnel generally the primary contact for the. fraud detection activity and a key contact in information systems to make arrangements to. obtain the data Matters to be discussed include,Specific data needed. Types of files needed Common file types include Comma delimited format Tab. delimited format Microsoft Access format and of course the Microsoft Excel format. Record layout of the file The fraud detection specialist should arrange to get copies of. the record layout which is a simple definition of each data field and where the fields are. positioned in the data file,Timing of the transfer. Method of transfer See Step 3b below, Arrangements for verification information see Step 3c below. Step 3b Transferring the Client s Data, There are many ways to transfer data to your computer for analysis depending on the client s. system architecture Examples of possible data transfer methods include. E mail file attachements,USB Memory Stick,FTP or network transfers. Web storage i e www xdrive com, 2009 InformationActive Inc Michelle Shein and Richard Lanza 13. ActiveData for Excel Fraud Detection eBook, The first three methods are more likely to be used for small PC systems The last four methods. are more likely to be used on larger systems LANs minicomputers or mainframes However. since we will be using Microsoft Excel in our processing will likely be more moderately sized. up to 1 million rows for Excel 2007 and therefore one of the first three methods will likely. The first two methods are more likely to be used for small PC systems The last five methods are. more likely to be used on larger systems LANs minicomputers or mainframes However. since we will be using Microsoft Excel in our processing the files should stay relatively small. making Email a preferable option for sending data, The results of this discussion should be formalized into a request letter as shown below. 2009 InformationActive Inc Michelle Shein and Richard Lanza 14. ActiveData for Excel Fraud Detection eBook,IS Manager. ABC Company, As part of our investigation we will be performing certain tests in the X fraud detection area using data. extraction software, As we discussed today we require the X file be available for us on X X XXXX We believe the following. fields are required from the file for the period X X XXXX to XX XX XXXX. List Fields Here, If you believe after looking at the reports we expect to process Appendix A that we will need more. data fields besides those listed above please provide these fields in the file extraction Also if it would. be easier we can receive the entire files from which we can extract and define our desired fields. We will need this file in an ASCII file format for importing into Microsoft Excel Therefore any text file. format will be acceptable tab delimited comma delimited fixed length and Microsoft Excel To assist. in downloading the file to our PC we prefer that the file be provided on a CD ROM or Emailed to us. We would like to receive the first 100 records of the data file printed out as well as a record count for the. file We will be using this information to confirm the proper transfer of the data to our system. Please contact us if you are unclear as to the source or significance of any of the items requested. Thank you for your assistance,Page 2 of Request Letter. Appendix A Expected Reports To Produce,Report Name Expected. Completion,List reports here List desired,completion. 2009 InformationActive Inc Michelle Shein and Richard Lanza 15.