Fraud Analysis Techniques Using ACL BECOMEASUBSCRIBER! Didyoupurchasethisproductfromabookstore? Ifyoudid,it’simportantforyoutobecomeasubscriber.JohnWiley&Sons,Inc.maypublish,onaperiodicbasis,supplementsandneweditionsto reflectthelatestchangesinthesubjectmatterthatyouneedtoknowinordertostaycompetitiveinthisever-changingindustry.Bycontactingthe Wileyofficenearestyou,you’llreceiveanycurrentupdateatnoadditionalcharge.Inaddition,you’llreceivefutureupdatesandrevisedorrelated volumesona30-dayexaminationreview. IfyoupurchasedthisproductdirectlyfromJohnWiley&Sons,Inc.,wehavealreadyrecordedyoursubscriptionforthisupdateservice. Tobecomeasubscriber,pleasecall1-877-762-2974orsendyourname,companyname(ifapplicable),address,andthetitleoftheproductto: mailingaddress: SupplementDepartment JohnWiley&Sons,Inc. OneWileyDrive Somerset,NJ08875 e-mail: [email protected] fax: 1-732-302-2300 ForcustomersoutsidetheUnitedStates,pleasecontacttheWileyofficenearestyou: Professional&ReferenceDivision JohnWiley&Sons,Ltd. JohnWiley&SonsAustralia,Ltd. JohnWiley&Sons(Asia)Pte.,Ltd. JohnWiley&SonsCanada,Ltd. TheAtrium 33ParkRoad 2ClementiLoop#02-01 22WorcesterRoad SouthernGate,Chichester P.O.Box1226 SINGAPORE129809 Etobicoke,OntarioM9W1L1 WestSussexPO198SQ Milton,Queensland4064 Phone:65-64632400 CANADA ENGLAND AUSTRALIA Fax:65-64634604/5/6 Phone:416-236-4433 Phone:44-1243-779777 Phone:61-7-3859-9755 CustomerService:65-64604280 Phone:1-800-567-4797 Fax:44-1243-775878 Fax:61-7-3859-9715 Email:[email protected] Fax:416-236-4447 Email:[email protected] Email:[email protected] Email:[email protected] Fraud Analysis Techniques Using ACL David Coderre JohnWiley&Sons,Inc. Thisbookisprintedonacid-freepaper.∞ Forgeneralinformationonourotherproductsandservices,ortechnical support,pleasecontactourCustomerCare.DepartmentwithintheUnited Copyright(cid:2)C 2009byJohnWiley&Sons,Inc.Allrightsreserved. Statesat800-762-2974,outsidetheUnitedStatesat317-572-3993orfax 317-572-4002. PublishedbyJohnWiley&Sons,Inc.,Hoboken,NewJersey. PublishedsimultaneouslyinCanada. Wileyalsopublishesitsbooksinavarietyofelectronicformats. Somecontentthatappearsinprintmaynotbeavailableinelectronic Nopartofthispublicationmaybereproduced,storedinaretrievalsystem, books. ortransmittedinanyformorbyanymeans,electronic,mechanical, photocopying,recording,scanning,orotherwise,exceptaspermitted FormoreinformationaboutWileyproducts,visitourWebsiteat underSection107or108ofthe1976UnitedStatesCopyrightAct,without www.wiley.com. eitherthepriorwrittenpermissionofthePublisher,orauthorization throughpaymentoftheappropriateper-copyfeetotheCopyright ClearanceCenter,Inc.,222RosewoodDrive,Danvers,MA01923, LibraryofCongressCataloging-in-PublicationData: 978-750-8400,fax978-646-8600,oronthewebatwww.copyright.com. RequeststothePublisherforpermissionshouldbeaddressedtothe Coderre,DavidG. PermissionsDepartment,JohnWiley&Sons,Inc.,111RiverStreet, FraudanalysistechniquesusingACL/DavidG.Coderre. Hoboken,NJ07030,201-748-6011,fax201-748-6008. p.cm. Includesindex. LimitofLiability/DisclaimerofWarranty:Whilethepublisherandauthor ISBN978-0-470-39244-7(paper/cd-rom) haveusedtheirbesteffortsinpreparingthisbook,theymakeno 1.Fraud. 2.Fraudinvestigation. 3.Fraud–Prevention. 4.Auditing, representationsorwarrantieswithrespecttotheaccuracyorcompleteness Internal–Dataprocessing. I.Title. ofthecontentsofthisbookandspecificallydisclaimanyimplied HV8079.F7C6272009 warrantiesofmerchantabilityorfitnessforaparticularpurpose.No 657(cid:3).45028553–dc22 warrantymaybecreatedorextendedbysalesrepresentativesorwritten 2009010846 salesmaterials.Theadviceandstrategiescontainedhereinmaynotbe suitableforyoursituation.Youshouldconsultwithaprofessionalwhere PrintedintheUnitedStatesofAmerica appropriate.Neitherthepublishernorauthorshallbeliableforanylossof profitoranyothercommercialdamages,includingbutnotlimitedto special,incidental,consequential,orotherdamages. 10 9 8 7 6 5 4 3 2 1 Contents Preface......................................................ix CustomizingScripts......................................11 AboutThisToolkit........................................ix Customizing:AnExample..............................12 WhoShouldUseThisToolkit?.............................ix CreatingYourOwnFraudApplication......................13 WhatIsIncludedinThisToolkit?...........................ix FurtherReading..........................................14 SystemRequirements.....................................ix HowtoUseThisToolkit...................................ix Chapter1:StartandMenu............................16 InstalltheFraudToolkitApplication........................x LaunchingtheFraudToolkitTests..........................16 HowThisBookIsStructured...............................x StartingtheFraudToolkitApplication......................16 ScriptCodeConventions..................................xi PlacementofStartandFraudMenuScripts.................17 Flowcharts...............................................xi HowtheScriptsWork....................................17 Modifications/Updates ................................... xii Start....................................................17 ContactingtheAuthor....................................xii FraudMenu.............................................18 Acknowledgments........................................xii LogFiles................................................18 ExitingaScript .......................................... 20 Introduction........................................2 WorkingwithouttheFraudMenu..........................20 UsingDataAnalysistoDetectFraud.........................2 Fraud:RisksandCosts.....................................2 Chapter2:CompletenessandIntegrity .................. 22 WhyDoPeopleCommitFraud?............................2 CheckingforBlanksandDataTypeMismatches.............22 WhyUseDataAnalysisSoftware?...........................3 RunningCompletenessandIntegrity.......................23 IdentifyingFraud..........................................4 HowtheScriptsWork....................................24 ProactiveFraudInvestigation...............................5 CarriageReturns.........................................24 BenefitsofDataAnalysiswithCAATTs......................6 UnderstandingtheVerifyCommand.......................24 AboutScripts.............................................6 UnderstandingtheGroupCommand.......................25 WhatIsaScript?........................................6 DeletingTemporaryVariables,Fields,andFiles............26 BenefitsofScripts.......................................6 VariablesforComplete...............................27 PreparingScriptsforUse.................................7 ReviewandAnalysis......................................27 CopyingScripts.........................................7 CaseStudy:GeneralLedgerAccountsUnaccountedFor......29 CopyingTableLayouts...................................7 WorkingwithScripts....................................8 Chapter3:Cross-Tabulation...........................30 LaunchingtheFraudToolkitApplication..................8 OrganizingYourDatatoFindTrends.......................30 RunningtheScripts.....................................9 RunningCross-Tabulation................................30 Filtering .............................................. 10 BenefitsofCross-Tabulation...............................32 v Contents HowtheScriptsWork....................................33 Chapter5:Gaps .................................... 52 ChallengesofCross-Tabulation..........................34 IdentifyingTransactionsMissingfromaSequence...........52 X-AxisLabels............................................34 RunningGaps...........................................52 Workspaces..............................................35 HowtheScriptsWork....................................53 DeletingTemporaryVariables,Fields,andFiles............36 DeletingTemporaryVariables,Fields,andFiles............54 VariablesforCross Tabs..............................36 ReviewandAnalysis......................................54 ReviewandAnalysis......................................36 CaseStudy:FreeCalls .................................... 55 CaseStudy:NotEnoughClients...........................37 CaseStudy:CallingCards.................................37 Chapter6:DataProfile...............................56 EstablishingNormalValuesandInvestigatingExceptions.....56 Chapter4:Duplicates................................38 RunningDataProfile.....................................56 FindingHigher-RiskItems................................38 HowtheScriptsWork....................................57 Payroll................................................38 FlowofDatainDataProfile...............................59 AccountsPayable......................................38 DataProfileTestParameters...............................59 RunningDuplicates......................................38 DeletingTemporaryVariables,Fields,andFiles............61 HowtheScriptsWork....................................40 ReviewandAnalysis......................................62 TheRoleofSubscripts....................................40 Statistics ................................................ 62 CaseStudy:DuplicatePayments...........................42 Stratify..................................................63 Dup DialogScript........................................43 RoundAmounts,ExactMultiples,andFrequentValues ...... 65 IfStatements ............................................ 43 RoundAmounts:Multiplesof5,10,25,or100..............65 Dup Multiple Keys1......................................44 ExactMultiplesof... .....................................66 MacroSubstitution.......................................44 FrequentlyUsedValues...................................66 KeyChange..............................................45 ProfilingwithCharacterFields.............................67 DefineField.............................................46 ItemswiththeMostExactMultiples........................67 LENGTH()andHEX()...................................46 Least/MostUsedItems....................................68 DeletingTemporaryVariables,Fields,andFiles............46 CaseStudy:ReceiptofInventory...........................69 ReviewandAnalysis......................................47 CaseStudy:ExactMultiples...............................71 CheckingforDuplicates .................................. 47 FilteringandDrillingDown...............................71 PayrollExample..........................................48 FilteringbeforeProfiling................................71 AccountsPayableExample................................50 FilteringafterProfiling.................................72 vi Contents Chapter7:RatioAnalysis ............................. 74 ACLCommands........................................102 PinpointingSuspectTransactionsandTrends...............74 DISPLAYCommand..................................102 RunningRatioAnalysis...................................74 SETCommand.......................................102 HowtheScriptsWork....................................78 DELETECommand...................................103 Max/Max2andMax/MinRatios...........................78 OPENCommand.....................................103 TwoFields:Num field1/Num field2Ratio.................80 CommandsforDefiningRelationships..................103 DeletingTemporaryVariables,Fields,andFiles............80 BasicACLCommands.................................103 ReviewandAnalysis......................................81 IFCommand.........................................104 CaseStudy:DormantbutNotForgotten....................84 Variables...............................................104 CaseStudy:DoctoredBills................................85 User-DefinedVariables................................106 UsingSystemandUser-DefinedVariables ............... 106 Chapter8:Benford’sLaw ............................. 88 DEFINEField/Expression................................106 IdentifyingAnomalousData...............................88 Workspaces.............................................107 UnderstandingBenford’sLaw ............................. 88 SharingWorkspaces...................................109 IdentifyingIrregularities..................................88 Scripts.................................................109 RunningBenfordAnalysis.................................89 WhatIsaScript?......................................109 RunningBenfordCustomAnalysis.........................90 CreatingScripts ...................................... 109 CreatingtheCustomDistribution.......................90 CommentingScripts..................................113 DivisionbyZero.......................................91 COMMENT.......................................113 TestingagainsttheCustomDistribution..................92 END..............................................113 HowtheBenfordScriptsWork ............................ 93 EditingScripts........................................113 StandardBenfordAnalysis..............................93 RunningScripts......................................114 BenfordCustomAnalysis...............................95 RUNACLScriptfromDOS..............................115 DeletingTemporaryVariables,Fields,andFiles............96 SavingaScripttoa.BATfile..............................115 ReviewandAnalysis......................................96 InteractiveScripts.......................................117 CaseStudy:SigningAuthority.............................98 ACCEPTCommand.....................................117 FurtherReading..........................................98 DialogBoxes............................................118 AddingSelectionstoDrop-DownandProject Chapter9:DevelopingACLScripts......................100 ItemLists............................................119 Introduction............................................100 MacroSubstitution......................................120 DataAnalysis:GenericApproach.........................100 EditingDialogBoxes....................................120 vii Contents Subscripts..............................................122 Chapter10:UtilityScripts............................138 SpecialUsesforSubscripts.............................122 Auto Execute...........................................138 RepeatingaScript..................................123 Extract Values..........................................139 ErrorTrapping.....................................123 EndingBalanceVerification..............................140 ConsolidationExercise...................................124 Running Total..........................................142 AdvancedACLScriptingTechniques......................125 MaximumandMinimumValues ......................... 143 GROUPCommand.....................................125 SimpleGROUP.......................................126 ConditionalGROUP..................................126 Appendix:ACLInstallationProcess................................145 NestedGROUP.......................................128 LOOPandOFFSET()....................................132 Glossary...................................................149 ApplicationsMenu......................................135 BuildinganApplicationMenu............................137 CreatingSubmenus ..................................... 137 Index.....................................................155 viii