会计数据分析 课件 英文 第1、2章　会计和商业数据分析、数据的准备和清理

DataAnalyticsinAccountingandBusinessChapter1Wherewearenow1.DataAnalytics2.DataPreparationandCleaning3.ModelingandEvaluation4.Visualization5.TheModernAudit6.AuditAnalytics7.KeyPerformanceIndicators8.FinancialStatementAnalyticsObjectivesLO1-1Whatisdataanalytics?LO1-2Howdoesdataanalyticsaffectbusiness?LO1-3Whydoesdataanalyticsmattertoaccountants?LO1-4Whatisthedataanalyticsprocess?LO1-5Whatdataanalyticskillsdoaccountantsneed?LO1-6HandsonexampleoftheIMPACTmodel.IntheIMPACTcycle,we’renowgoingtolookatIdentifyingtheQuestions.(We’lldiscussthistoday.)IdentifythequestionsMasterthedataPerformtestplanAddressandrefineresultsCommunicateinsightsTrackoutcomesExhibit1-1TheIMPACTCycleWhatisDataAnalytics?LO1-1DataAnalyticsandBigDataDataAnalyticsistheprocessofevaluatingdatawiththepurposeofdrawingconclusionstoaddressbusinessquestions.EffectiveDataAnalyticsprovidesawaytosearchthroughlargestructuredandunstructureddatatoidentifyunknownpatternsorrelationships.BigDatareferstodatasetswhicharetoolargeandcomplextobeanalyzedtraditionally.Rememberthe3V’s:VolumereferstosizeVelocityreferstofrequencyVarietyreferstodifferenttypesDataAnalyticsandBigDataRememberthe3V’s:VolumereferstosizeVelocityreferstofrequencyVarietyreferstodifferenttypesQ:Howcouldabankusedataanalyticstounderstandcustomercreditworthiness?Howdoesdataanalyticsaffectbusiness?LO1-2Bythenumbers:85%ofCEOsputahighvalueonDataAnalytics.80%ofCEOsplacedataminingandanalysisasthesecond-mostimportantstrategictechnology.BusinessanalyticstopsCEO’slistofpriorities.DataAnalyticscouldgenerateupto$3trillioninvalueperyear.Q.Howcoulddataanalyticsbeusedtoreduceacompany’sovertimecosts?Whydoesdataanalyticsmattertoaccountants?LO1-3Howdoesdataanalyticsaffectauditing?Dataanalyticswillenhanceauditquality.Dataanalyticsenablesenhancedaudits,expandedservices,andaddedvaluetoclients.Externalauditorswillstayengagedbeyondtheaudit.Howdoesdataanalyticsaffectfinancialreporting?Betterestimatesofcollectability,write-downs,etc.ManagerscanbetterunderstandthebusinessenvironmentthroughsocialmediaIdentifyrisksandopportunitiesthroughanalysisofInternetsearchesHowdoesdataanalyticsaffecttaxes?TaxstrategyandplanningUnderstandingoftaxconsequencesofinternationaltransactions,investment,mergersandacquisitionsBetterorganizationoftaxtablesandothertaxdataQ.Whatpatternsmightanauditorfindthroughdataanalytics?Whatisthedataanalyticsprocess?1-4TheIMPACTmodelIdentifythequestionsMasterthedataPerformthetestplanAddressandrefineresultsCommunicateinsightsTrackoutcomesIdentifythequestionsMasterthedataPerformtestplanAddressandrefineresultsCommunicateinsightsTrackoutcomesExhibit1-1TheIMPACTCycleStep1:IdentifytheQuestionsUnderstandthebusinessproblemsthatneedtobeaddressed.Areemployeescircumventinginternalcontrolsoverpayments?Arethereanysuspicioustravelandentertainmentexpenses?Howcanweincreasetheamountofadd-onsalesofadditionalgoodstoourcustomers?Areourcustomerspayingusinatimelymanner?Howcanwepredicttheallowanceforloanlossesforourbankloans?Howcanwefindtransactionsthatareriskyintermsofaccountingissues?Whoauthorizeschecksabove$100,000?Howcanerrorsbeidentified?Step2:MastertheDataKnowwhatdataareavailableandhowtheyrelatetotheproblem.InternalERPsystemsExternalnetworksanddatawarehousesDatadictionariesExtraction,transformation,andloadingDatavalidationandcompletenessDatanormalizationDatapreparationandscrubbingTransactionsTransactionID[PK]CustomerID[FK]DateDescriptionAmountCustomerCustomerID[PK]NameAddressCityStateStep3:PerformtheTestPlanSelectanappropriatemodeltofindatargetvariable.ClassificationRegressionSimilaritymatchingClusteringCo-occurrencegroupingProfilingStep4:AddressandRefineResultsIdentifyissueswiththeanalyses,possibleissues,andrefinethemodelAskfurtherquestionsExplorethedataRerunanalysesStep5:CommunicateInsightsCommunicateeffectivelyusingclearlanguageandvisualizations:DashboardsStaticreportsSummariesStep6:TrackOutcomesFollowupontheresultsoftheanalysis.Howfrequentlyshouldtheanalysisbeperformed?Havetheanalyticschanged?Whatarethetrends?Q.Let’ssaywearetryingtopredicthowmuchmoneycollegestudentsspendonfastfoodeachweek.Whatwouldbetheresponse,ordependent,variable?Whatwouldbeexamplesofindependentvariables?Whatdataanalyticskillsdoaccountantsneed?LO1-5Accountantsneedtobeableto:Articulatebusinessproblems.Communicatewithdatascientists.Drawappropriateconclusions.Presentresultsinanaccessiblemanner.Developananalyticsmindset.Aswellasbecomfortablewith:DatascrubbinganddatapreparationDataqualityDescriptivedataanalysisDataanalysisthroughdatamanipulationDefineandaddressproblemsthroughstatisticalanalysisDatavisualizationanddatareportingQ.WhatotherskillsmightbeusefulinperformingDataAnalytics?Hands-onExampleoftheIMPACTModelLO1-6Step1:IdentifytheQuestionAssumeyouwanttogetaloantopayoffsomecreditcarddebt.LendingClubisapeer-to-peerlenderthatconnectsindividuallenderswithborrowers.UsetheIMPACTmodeltodeterminewhetheryou’relikelytogetaloan.“Givenmyborrowerprofile,canIexpectLendingClubtoextendaloantome?”Whatotherquestionsmightyouask?Step2:MastertheDataLendingClubisaU.S.-based,peer-to-peerlendingcompany,headquarteredinSanFrancisco,California.LendingClubfacilitatesbothborrowingandlendingbyprovidingaplatformforunsecuredpersonalloansbetween$1,000and$35,000.Theloanperiodisforeither3or5years.Dataavailable:Approvedloans(LoanStats)Rejectedloanstats(RejectStats)Step2:MastertheDataPersonalloanshavegrownsince2010.Themajorityareforrefinancing.Exhibit1-4LendingClubStatisticsExhibit1-5LendingClubStatisticsbypurposeStep2:MastertheDataRejectedStatsDataDictionary SampledatafromRejectedStatsAmountRequestedApplicationDateLoanTitleRisk_ScoreDebt-To-IncomeRatioZipCodeStateEmploymentLength10005/26/2007WeddingCoveredbutNoHoneymoon69310%481xxNM4years10005/26/2007ConsolidatingDebt70310%010xxMA<1year110005/27/2007Wanttoconsolidatemydebt71510%212xxMD1year60005/27/2007waksman69838.64%017xxMA<1year15005/27/2007mdrigo5099.43%209xxMD<1year150005/27/2007Trinfiniti6450%105xxNY3years100005/27/2007NOTIFYiInc69310%210xxMD<1year39005/27/2007ForJustin.70010%469xxIN2years30005/28/2007title?69410%808xxCO4years25005/28/2007timgerst57311.76%407xxKY4years39005/28/2007needtoconsolidate71010%705xxLA10+years10005/28/2007sixstrings68010%424xxKY1year30005/28/2007bmoore511068810%190xxPA<1year15005/28/2007MHarkins70410%189xxPA3yearsRejectStatsFileDescriptionAmountRequestedThetotalamountrequestedbytheborrowerApplicationDateThedatewhichtheborrowerappliedLoanTitleTheloantitleprovidedbytheborrowerRisk_ScoreForapplicationspriortoNovember5,2013theriskscoreistheborrower'sFICOscore.ForapplicationsafterNovember5,2013theriskscoreistheborrower'sVantagescore.Debt-To-IncomeRatioAratiocalculatedusingtheborrower’stotalmonthlydebtpaymentsonthetotaldebtobligations,excludingmortgageandtherequestedLCloan,dividedbytheborrower’sself-reportedmonthlyincome.ZipCodeThefirst3numbersofthezipcodeprovidedbytheborrowerintheloanapplication.StateThestateprovidedbytheborrowerintheloanapplicationEmploymentLengthEmploymentlengthinyears.Possiblevaluesarebetween0and10where0meanslessthanoneyearand10meanstenormoreyears.PolicyCodepubliclyavailablepolicy_code=1

newproductsnotpubliclyavailablepolicy_code=2Step3:PerformtheTestPlanPerformthreeanalysestopredictwhetherwereceivealoan:1.Thedebt-to-income(DTI)ratiosandnumberofrejectedloans2.Thelengthofemploymentandnumberofrejectedloans3.Thecredit(orrisk)scoreandnumberofrejectedloansStep3:PerformtheTestPlanForDTI,wesetbuckets:High=debt>20%ofincomeMid=debtis10-20%ofincomeLow=debt<10%ofincomeHereweseeaPivotTablewithresultsonRejectStatsStep3:PerformtheTestPlanForemploymentlength,wesetbucketsonnumberofyears.HereweseeaPivotTablewithresultsonRejectStatsStep3:PerformtheTestPlanForcreditscore,wesetbuckets:Excellent:800-850Verygood:750-799Good:700-749Fair:650-699Poor:600-649Verybad:300-599Step4:AddressandRefineResultsFromthePivotTableanalysis,wefindthatoftherejectedloans:82%haveeitherverybad,poor,orfaircredit48%hadahighDTIratio76%hadacredithistoryofoneyearorlessStep4:AddressandRefineResultsIfwelookatinteractionsofcreditscore

&DTI&employmentlengthinaPivotTable,weseetheyarefairlypredictive.Only89or645,414loanswererejectedwithfromthetopbucketsfromeach.Step5:CommunicateInsightsThePivotTablesprovideasimplevisual.Additionalvisualizationsortoolsmayprovidequickanalysisbythoseevaluatingtheloans.AnothergoalistosharetheresultsinplainEnglish:“IfIhavegoodcredit,lowdebt-to-income,andalongemploymentlength,itisverylikelythatmyloanwillbeaccepted.”Step6:TrackOutcomesExtendingthisanalysistofutureperiodswillhelpusdeterminewhetherthesefactorsholdtrueorifthereissomeshiftinthefuture.Weattempttousepastperformancetopredictfutureresults,butthatmaynotalwaysholdtrue.Whenfactorschange,werepeattheIMPACTcycle.Q.WouldyouexpectloansfromCaliforniatobemoreorlesslikelyapproved?Howcouldyoutestthat?SummaryWithdataallaroundus,businessesandaccountantsarelookingtoDataAnalyticstoextractthevaluethatthedatamightpossess.DataAnalyticsischangingtheauditandthewaythataccountantslookforrisk.Now,auditorscanconsider100percentofthetransactionsintheiraudittesting.Itisalsohelpfulinfindingtheanomalousorunusualtransactions.DataAnalyticsisalsochangingthewayfinancialaccounting,managerialaccounting,andtaxesaredoneatacompany.TheIMPACTcycleisameansofdoingDataAnalyticsthatgoesallthewayfromidentifyingthequestion,tomasteringthedata,toperformingdataanalysesandcommunicatingresults.Itisrecursiveinnature,suggestingthatasquestionsareaddressed,newimportantquestionsmayemergethatcanbeaddressedinasimilarway.Eightdataapproachesaddressdifferentwaysoftestingthedata:classification,regression,similaritymatching,clustering,co-occurrencegrouping,profiling,linkprediction,anddatareduction.Theseareexplainedinmoredetailinchapter3.Dataanalyticskillsneededbyanalytic-mindedaccountantsarespecifiedandareconsistentwiththeIMPACTcycle,includingthefollowing:Developananalyticsmindset.

Datascrubbinganddatapreparation.

Dataquality.

Descriptivedataanalysis.

Dataanalysisthroughdatamanipulation.

Defineandaddressproblemsthroughstatisticaldataanalysis.

Datavisualizationanddatareporting.DataPreparation

andCleaningChapter2Wherewearenow1.DataAnalytics2.DataPreparationandCleaning3.ModelingandEvaluation4.Visualization5.TheModernAudit6.AuditAnalytics7.KeyPerformanceIndicators8.FinancialStatementAnalyticsObjectivesLO2-1Howaredatausedandstoredintheaccountingcycle?LO2-2Howaredatastoredinrelationaldatabases?LO2-3Whatdoesitmeantoextract,transform,andload?IntheIMPACTcycle,we’regoingtolookatMasteringtheData.IdentifythequestionsMasterthedataPerformtestplanAddressandrefineresultsCommunicateinsightsTrackoutcomesExhibit1-1TheIMPACTCycleHowaredatausedandstoredintheaccountingcycle?LO2-1Understandthedatabylookingathowitisorganized.Datacanbefoundthroughoutvarioussystems.Inmostcases,youneedtoknowwhichtablesandattributescontaintherelevantdata.UnifiedModelingLanguage(UML)isonewaytounderstanddatabases.FGI_ProductProduct_Code[PK]Product_Description…Sales_SubsetSales_Order_ID[PK]Product_Code[FK]Customer_ID[FK]…CustomerCustomer_ID[PK]Customer_Name…Howaredatastoredinrelationaldatabases?LO2-2Relationaldatabasesensurethatdata:Arecomplete,orincludealldata.Aren’tredundant,sotheydon’ttakeuptoomuchspace.Followbusinessrulesandinternalcontrols.Aidcommunicationandintegrationofbusinessprocesses.Therearefourtypesofattributes.Primarykeysareuniqueidentifiers.Foreignkeysareattributesthatpointtoaprimarykeyinanothertable.Compositekeysareacombinationoftwoforeignkeysusedforlineitems.Descriptiveattributesincludeeverythingelse.SupplierTableSupplierIDSupplierNameSupplierAddressSupplierType1NorthernBreweryHomebrewSupply6021LyndaleAveS12HopsDirectLLC686GreenValleyRoad13TheHomeBrewery455E.TownshipSt.14ThePayrollCompany408N.WaltonBlvd2Examplesoftwotables,attributes,anddata.NoticethePK-FKrelationship.PurchaseOrderTablePONo.DateCreatedByApprovedBySupplierID(FK)178711/1/2017100110101178811/1/2017100510102178911/8/2017100210101179011/15/2017100510101SupplierTableSupplierID(PK)SupplierNameSupplierAddressSupplierType1NorthernBreweryHomebrewSupply6021LyndaleAveS12HopsDirectLLC686GreenValleyRoad13TheHomeBrewery455E.TownshipSt.14ThePayrollCompany408N.WaltonBlvd2Datadictionariesdefinewhatdataareacceptable.Foreachattribute,welearn:Whattypeofkeyitis.Whatdataarerequired.Whatdatacanbestoredinit.Howmuchdataisstored.SupplierTableDataDictionaryPrimaryorForeignKey?RequiredAttributeNameDescriptionDataTypeDefaultValueFieldSizeNotesPKYSupplierIDUniqueIdentifierforeachSupplier

Numbern/a10

NSupplierNameFirstandLastNameShortTextn/a30

FKNSupplierTypeTypeCodeforDifferentSupplierCategories

NumberNull101:Vendor2:MiscQ.Whatisthepurposeoftheprimarykey?Aforeignkey?Anon-keyattribute?Whatdoesitmeantoextract,transform,andload?LO2-2TheRequestingdataisaniterativepracticeinvolving5steps:Step1:Determinethepurposeandscopeofthedatarequest.Step2:Obtainthedata.Step3:Validatethedataforcompletenessandintegrity.Step4:Cleanthedata.Step5:Loadthedatafordataanalysis.Step1:DeterminethepurposeandscopeofthedatarequestAskafewquestionsbeforebeginningtheprocess:Whatisthepurposeofthedatarequest?Whatdoyouneedthedatatosolve?Whatbusinessproblemwillitaddress?Whatriskexistsindataintegrity(e.g.,reliability,usefulness)?Whatisthemitigationplan?Whatotherinformationwillimpactthenature,timing,andextentofthedataanalysis?Step2:ObtainthedataHowwilldataberequestedand/orobtained?Doyouhaveaccesstothedatayourself,ordoyouneedtorequestadatabaseadministratorortheinformationsystemsdepartmenttoprovidethedataforyou?Ifyouneedtorequestthedata,isthereastandarddatarequestformthatyoushoulduse?Fromwhomdoyourequestthedata?Wherearethedatalocatedinthefinancialorotherrelatedsystems?Whatspecificdataareneeded(tablesandfields)?Whattoolswillbeusedtoperformdataanalytictestsorproceduresandwhy?Step2:ObtainthedataThereareacoupleoptions:ObtaindatathroughadatarequesttotheITdepartment.Obtaindatayourself.ExampleStandardDataRequestFormSECTION1:REQUESTDETAILSRequestorName:RequestorContact

Number:RequestorEmailAddress:Pleaseprovideadescriptionoftheinformationneeded(indicatewhichtablesandwhichfieldsyourequire):Whatwilltheinformationbeusedfor?Frequency(circleone)One-OffAnnuallyTermlyOther:___________Formatyouwishthedatatobedeliveredin(circleone):Spreadsheet

WordDocumentTextFile

Other:____________RequestDate:RequiredDate:IntendedAudience:Customer(ifnotrequestor):ExampleStandardDataRequestFormSECTION2:TOBECOMPLETEDBYINFORMATIONSYSTEMSDEPARTMENTRequestNumberDateReceivedReceivedbyAssignedtoInitialreviewcomments

(discussionwithclient—revisionsrequired?agreementtoproceed?etc.)Workinprogresscomments

(additionalnotesandcommentsduringproductionofdata)SECTION3:COMPLETIONDETAILSDateCompleted

DateProvidedRevisionsRequiredFeedbackfromclient

(ifapplicable)ObtainthedatayourselfIfyouhavedirectaccesstoadatawarehouse,youcanuseSQLandothertoolstopullthedatayourself.Identifythetablesthatcontaintheinformationyouneed.Youcandothisbylookingthroughthedatadictionaryortherelationshipmodel.Identifywhichattributes,specifically,holdtheinformationyouneedineachtable.Identifyhowthosetablesarerelatedtoeachother.Step3:ValidatethedataforcompletenessandintegrityChancesarethedatayourequestisn’tcomplete.Beforeyoubegin,doalittleworktomakesureyourdataarevalid:ComparethenumberofrecordsComparedescriptivestatisticsfornumericfieldsValidateDate/TimefieldsComparestringlimitsfortextfieldsStep4:CleanthedataOnceyouhavevaliddata,thereisstillsomeworkthatneedstobedonetomakesureitisconsistentandreadyforanalysis:RemoveheadingsorsubtotalsCleanleadingzeroesandnonprintablecharactersFormatnegativenumbersCorrectinconsistenciesacrossdata,ingeneralStep5:LoadthedatafordataanalysisFinally,youcannowimportyourdataintothetoolofyourchoiceandexpectthefunctionstoworkproperly.Q.Whatarefourcommonissueswithdatathatmustbefixedbeforeanalysiscantakepla