AuditDataAnalyticsChapter6Wherewearenow1.DataAnalytics2.DataPreparationandCleaning3.ModelingandEvaluation4.Visualization5.TheModernAudit6.AuditAnalytics7.KeyPerformanceIndicators8.FinancialStatementAnalyticsObjectivesLO6-1UnderstanddifferenttypesofanalysisforauditingandwhentousethemLO6-2UnderstandbasicdescriptiveauditanalysesLO6-3UnderstandmorecomplexstatisticalanalysesLO6-4UnderstandadvancedpredictiveandprescriptiveanalyticsWhenshouldyouuseauditdataanalytics?LO6-1DataAnalyticscanbeappliedtotheauditingfunctiontoincreasecoverageoftheaudit,whilereducingthetimetheauditordedicatestotheaudittasks.Naturerepresentswhyweperformauditprocedures.

Extentindicateshowmuchwecantest.

Timingtellsushowoftentheprocedureshouldberun.AuditDataAnalyticscanhelptoimprovethenature,timing,andextentofauditprocedures…IdentifytheProblem

Whatistheauditdepartmenttryingtoachieveusingdataanalytics?

Doyouneedtoanalyzethesegregationofdutiestotestwhetherinternalcontrolsareoperatingeffectively?

Areyoulookingforoperationalinefficiencies,suchasduplicatepaymentsofinvoices?

IdentifytheProblem(continued)

Areyoutryingtoidentifyphantomemployeesorvendors?

Areyoutryingtocollectevidencethatyouarecomplyingwithspecificregulations?

Areyoutryingtotestaccountbalancestotiethemtothefinancialstatements?MastertheData–Theauditdatastandardsprovideageneraloverviewofthebasicdataauditorswillevaluate,includingnotabletablesandfields.MastertheDataFieldNameDescriptionSales_Order_IDUniqueidentifierforeachsalesorder.ThisIDmayneedtobecreatedbyconcatenatingfields(e.g.,documentnumber,documenttype,andyear)touniquelyidentifyeachsalesorder.Sales_Order_Date Thedateofthesalesorder,regardlessofthedatetheorderisentered.Entered_By User_ID(fromUser_Listingfile)forpersonwhocreatedtherecord.Approved_By UserID(fromUser_Listingfile)forpersonwhoapprovedcustomermasteradditionsorchanges. Sales_Order_Amount_LocalSalesmonetaryamountrecordedinthelocalcurrency. ExcerptfromTable6-1PerformtheTestPlan–Commondataanalyticsprocedurescanbefoundincomputer-assistedauditingtechniques(CAATS)PerformtheTestPlanDescriptiveanalyticssummarizeactivityormasterdataonspecificattributes.

Diagnosticanalytics

lookforcorrelationsorpatternsofinterest.Predictiveanalytics

helpauditorsdiscoverhiddenpatternslinkedtoabnormalbehavior.Prescriptiveanalytics

makerecommendationsbasedonpastdata.Examplesofdescriptiveanalytics:Ageanalysis—groupsbalancesbydateSorting—identifieslargestorsmallestvaluesSummarystatistics—mean,median,min,max,count,sumSampling—randomandmonetaryunitExampleAuditProcedure:Analysisofnewaccountsopenedandemployeebonusesbyemployeeandlocation.Examplesofdiagnosticanalytics:Z-score—outlierdetectionBenford’slaw—identifiestransactionsoruserswithnontypicalactivitybasedonthedistributionoffirstdigitsDrill-down—exploresthedetailsbehindthevaluesClustering—groupsrecordsbynonobvioussimilaritiesExactandfuzzymatching—joinstablesandidentifiesplausiblerelationshipsSequencecheck—detectsgapsinrecordsandduplicatesentriesStratification—groupsdatabycategoriesExamplesofpredictiveanalytics:Regression—predictsspecificdependentvaluesbasedonindependentvariableinputsClassification—predictsacategoryforarecordProbability—usesarankscoretoevaluatethestrengthofclassificationSentimentanalysis—evaluatestextforpositiveornegativesentimenttopredictpositiveornegativeoutcomesExamplesofprescriptiveanalytics:What-ifanalysis—decisionsupportsystemsAppliedstatistics—predictsaspecificoutcomeorclassArtificialintelligence—usesobservationsofpastactionstopredictfutureactionsforsimilareventsExampleAuditProcedure:Analysisdeterminesprocedurestofollowwhennewaccountsareopenedforinactivecustomers,suchasrequiringapproval.Manyoftheseapproachescanbeautomatedwithgeneralizedauditsoftware,includingExcelandIDEA.AddressandRefineResultsDifferentmodelswillproducedifferentresults,forexample:-Highrisktransactions-Userswithconflictingroles-ExceptionstostandardprocedureAuditorswouldevaluatetheevidenceandcollaboratewithmanagementtoresolvetheissues.CommunicateInsights–Resultsmayappearinanauditdashboardandmaybeincludedinauditevidence.

TrackOutcomes–Evaluatedetectionandresolutionofexceptions.Periodicallyevaluatetheproceduresforeffectiveness.

Q.Compareandcontrastdescriptiveanddiagnosticanalytics.Howmightthesebeusedinanaudit?Whatdodescriptiveanalyticslooklike?LO6-2Descriptiveanalyticsareusefulforsortingandsummarizingdatatocreateabaselineorpointofreferenceformoreadvancedanalytics.Ageanalysisdeterminesthelikelihoodofpayment.BasicExcelformulasforevaluatingunpaidorders:Daysoutstanding=[Agingdate]–[Orderdate]Buckets=IF([Agingdate]–[Orderdate]<=30,[Amount],0)InIDEA:GotoAnalysis>Categorize>Agingandsetparameters.DaysoldTotal0-30154,32231-6074,53961-9042,200>9016,900Sortingvaluesbysmallestorlargestvaluesmayprovidemeaningfulinsight.InExcel:Home>FormatasTable,thenusedrop-downmenus.InIDEA:GotoData>Order>SortSummarystatisticsallowyoutoseetherelativesizeofavaluetoitspopulation.InExcel:Mean:=AVERAGE([range])Median:=MEDIAN([range])Minimum:=MIN([range])Maximum:=MAX([range])Count:=COUNT([range])Sum:=SUM([range])InIDEA:InthePropertiespaneontheright,clickFieldStatistics.Randomsamplingisusefulformanualevaluationofsourcedocuments.InExcel:EnableAnalysisToolPak.GotoData>Analysis>DataAnalysis.ClickSamplingandsetparameters.InIDEA:GotoAnalysis>Sample>Randomandsetparameters.Monetaryunitsamplingisusefulfortargetinglargertransactions.InExcel:Sortdata

andcalculatethecumulativebalance.Chooseasamplingintervalandsize.Godownthelist.InIDEA:GotoAnalysis>Sample>MonetaryUnit>Planandsetparameterstocalculatesamplesize.Q.Whattypeofdescriptiveanalyticswouldyouusetofindnegativenumbersthatwereenteredinerror?HowdoyouperformdiagnosticanalysesandBenford’sLaw? LO6-3Diagnosticanalyticsprovidemoredetailsintonotjusttherecords,butalsorecordsorgroupsofrecordsthathavesomestandoutfeatures.Z-scoresidentifyoutliersbycalculatingstandarddistancefromthemean.HighZ-scorevaluesrepresentoutliers.Ascoreabove3standarddeviationsisrare.InExcel:Calculatetheaverageandstandarddeviation.CalculatetheZ-score:=([value]–[mean])/[standarddeviation]Exhibit6-1Z-scoreshowstherelativepositionofapointofinterest.Benford’sLawidentifiesabnormaldistributionsoflargenumbers.InExcel:Extracttheleadingdigit=LEFT([Amount],1)Createafrequencydistribution=COUNTIF([Range],[Digit])(=[ActualCount]/SUM[ActualCount])Chartagainstexpected%Bonus:UsePivotTablestoidentifyindividualemployeeaveragesExhibit6-2Benford’slawpredictsthedistributionoffirstdigits.Benford’sLawidentifiesabnormaldistributionsoflargenumbers.InIDEA:GotoAnalysis>Explore>Benford’sLawExhibit6-2Benford’slawpredictsthedistributionoffirstdigits.Modernsoftwareallowsyoutodrilldownbyclickingthroughsummaryvaluestoviewtheunderlyingvalues.Exactandfuzzymatchingallowyoutojointablesoncompleteorpartialvalues.Examples:Exactmatch:Employee#14552=Employee#14552Fuzzymatch:234SecondAve

=234SecondAvenue

InExcel:DownloadandenabletheFuzzyLookupAdd-inforExcel.GotoFuzzyLookup>FuzzyLookupMatchtablesandcolumns.InIDEA:CurrentlyunavailablebydefaultSequencechecksareusedforlocatinggapsorduplicatetransactions.InExcel:=IF([secondvalue]–[firstvalue]=1,"","Missing")=SMALL(IF(ISNA(MATCH(ROW([range]),[range],0)),ROW([range])),ROW([Firstvalueinrange))Stratificationandclusteringareusedtogrouptransactionsorindividualsbysimilarcharacteristics.Q.Let’ssayacompanyhasninedivisions,andeachdivisionhasadifferentchecknumberbasedonitsdivision—soonestartswith“1,”anotherwith“2,”etc.WouldBenford’slawworkinthissituation?Howdoyouperformpredictiveandprescriptiveanalytics?LO6-4Predictiveandprescriptiveanalyticsprovidelessdeterministicoutputandmoreprobabilisticmodels,judgingthingslikelikelihoodandprobability.Regressionallowsanauditortopredictaspecificdependentvaluebasedonindependentvariableinputs.Classificationinauditingisgoingtobemainlyfocusedonriskassessment.Thepredictedclassesmaybelowriskorhighrisk.Whentalkingaboutclassification,thestrengthoftheclasscanbeimportanttotheauditor,especiallywhentryingtolimitthescope(e.g.,evaluateonlythe10riskiesttransactions).Sentimentanalysisenablesevaluationoftext(e.g.,annualreportore-mails)fordistributionsofwordsthatmaybeclassifiedaspositiveornegativeoutcomesortolookforpotentialbias.Appliedstatisticsincludeadditionalmixeddistributionsandnontraditionalstatisticsmayalsoprovideinsighttotheauditor.Artificialintelligencemodelsexpectedbehaviorbyevaluatingpastactionstakenbyauditorstopredictexpectedbehaviorinanunknowncase.Additionalanalysesareavailableinspecializ