會計數(shù)據(jù)分析 課件 第1章　會計和商業(yè)數(shù)據(jù)分析

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,auditorscanconsider100percentofthetrans