Data Analytics: Pursuing Analysis in the Present Day October 18, 2012 James Overman

Data Analytics: Pursuing Analysis in the Present DayOctober 18, 2012James Overman

Agenda • What areData Analytics? • Brief History • Why Data Analytics and CAATs Usage • Key Data Concepts • Data Analysis Software • Performing Analytics • Working with Multiple Data Files • Sampling • Extracting, Exporting & Reporting • Challenges and Best Practices

Agenda • What are Data Analytics? • Brief History • Why Data Analytics & CAATs Usage • Key Data Concepts • Data Analysis Software • Performing Analytics • Working with Multiple Data Files • Sampling • Extracting, Exporting & Reporting • Challenges and Best Practices

What are Data Analytics? • Data Analytics is the process of inspecting, cleaningand molding data with the end goal of reporting useful information that is not readily accessible, supporting decision making and suggesting conclusions based upon factual evidence. • Analytics vs. Analysis

Who uses Data Analytics? • Whether you realize it or not, everyone uses data analytics in some way • Application of Data Analytics are endless… • If the data is available, analytics/analysis can be performed

Applications/Uses of Data Analytics include: • Budgetary Evaluation • Financial Reporting • Projections • Fraud • Payroll • Accounts Payable • Invoicing • Fixed Assets • Accounts Receivable • Vender Master File • Valuation • Inventory Control • Human Resources/Benefits • Transaction Analysis

What is Data Analytics? • Its all about your PERSPECTIVE!!!

History of Data Analytics • Applications of Data Analytics can be found throughout world history: • Census: • Ancient Egypt & Greece(Predating 1500BCE) • First US Census in 1790 by Federal Marshals • Multiple Uses from Population Analysis to Tax Revenue Estimation

History of Data Analytics • Profit/Tax Calculation & Budgetary Analysis • Ancient China, Mesopotamia, & Middle Ages • Analytics via usage of the Abacus • Inventory Analysis • Revenue Estimation

Why Data Analytics? • Technology is a Tool we can utilize • Application of all types of Data • Purely Analytical Framework • Our time is important to us

CAATs Usage CAATs? What are CAATs??? • The use of software to automate audit or business processes that would otherwise need to be performed manually

Computer Assisted Auditing Techniques (CAATs) • Increase Audit/Analysis Efficiency • On Average, CAAT programs can be developed in 30% - 40% less time than it takes to manually perform the task. • In subsequent years, the same CAAT programs can be modified and run in 30% - 50% less time than the initial year, creating even greater efficiency and effectiveness

Computer Assisted Auditing Techniques (CAATs) Decreased Risk • CAATs can lower your detection risk by • Testing 100% of a population • Increasing the granularity of data tested • Minimizing the potential for human error • Quantify the financial impact of business decisions, accounting practices and internal controls

Computer Assisted Auditing Techniques (CAATs) Can you think of any examples of testing where CAATs can be useful?

Computer Assisted Auditing Techniques (CAATs) • Completeness of Populations • Random Sampling • Deceased individuals receiving payments • Duplicate Payments • Invalid Accounts • Incorrect Calculations of financial statement line items • Journal Entry Testing • Vendor/Master File Testing • A/P & A/R liquidations • Searching for potential suspense accounts

Data Types What are some ways data files can be received?

Data Types What are some ways data files can be received? dbf Excel Access XML • Delimited • Fixed Width • Flat/Report Files • EBCDIC

Data Types Delimited

Data Types Fixed Width

Data Types Flat/Report:

Data Types Others: Access *.mdb Each table contains fields and records Excel *.xls Each spreadsheet contains fields and records Database *.dbf May be original to the source system or exported as such from Excel, Monarch and a number of other tools EBCDIC "External Binanry Code Decimal Interchange Code – a standard 8- bit code, primarily mainframe

Data Types What are some ways data fields can be received within a data file?

Data Types What are some ways data fields can be received within a data file? - Character - Numeric - EBCDIC Text - Date - Packed - ASCII Text

Key Data Concepts What is data validation? - In computer science, data validation is the process of ensuring that a program operates on clean, correct and useful data. - Performing data validation gives the users a comfort that the data they are using are reasonable and reliable.

Types of Data Validation Validations can be directly related to the data or based upon business rules Business-Specific Validation Data-Format Validation Below are some general methods used for data validation Check Digits Format or Picture Check Presence Check Batch Totals Range Check Hash Totals

Data Analysis Software There are many types of software out that can perform a multitude of tasks, here are just a few: • Microsoft Excel • ACL (Audit Command Language) • IDEA (CaseWare Product) • Hundreds more…

Data Analysis Software What's the difference? • Ease of Use • Limitation of data input • Limitation on Customization • Pre-Saved "Scripts" • Documentation or "Logs"

Performing Analytics Keys to a successful analysis: • Data Validation • Preparation of the data before analysis • Use only the data required • Document as you go

Organizing and Summarizing Data • Creating Filters • Logical Operators • =, <>, >, <, >=, <= • AND • OR • NOT • Building Expressions

Organizing and Summarizing Data Sorting: Giving the reader a clear view • Ascending or descending order • Output to new file • Sort on multiple fields if necessary • "Quick Sort"

Organizing and Summarizing Data Summarize: • Text or date Fields • Generates Record count and numeric total for each distinctive valueor key field

Organizing and Summarizing Data Classify: • Similar to Summarize • One Key Field • No presort • % of Count and % of Amount

Organizing and Summarizing Data Stratify: • Summarize numeric field into buckets • One or more fields can be accumulated

Organizing and Summarizing Data Crosstab or Cross-tabulate: • Summarizes data in rows in columns • Accumulates numeric amounts • Similar to Pivot Table in Excel

Organizing and Summarizing Data Aging: • Aging analysis of the input data file • Select interval "Buckets" based upon cutoff selected

Working with Multiple Data Files Append • Files must have identical table layout • Can be done within a Data Analysis Program or within Command Prompt • Done to merge two files together

Working with Multiple Data Files Join • When data has the same "key" or "primary" field tables can be joined together • The Key or Primary fields must be the same type of data and same length • Types of Join functions include: Match, Primary, Secondary or Unmatched

Sampling Why do we sample? Auditor Perspective: Business Perspective:

Why do we sample? Both perspectives are very similar: • Test Controls • A-123 requirements • Verify Accuracy of Transactions • Test for Fraud • Duplicate payments • Estimates • Monetary Sampling

Sampling Types of sampling? • Random Sampling • Within Data Analysis Programs a random seed is generated & documented • Should be created with scripts for documentation purposes in order to validate "random" aspect of sample

Sampling Types of sampling? • Dollar Unit Sampling • Method of selecting records where the likelihood of selection is proportional to the value of the field: the larger the field, the more likely it will be selected. • Within ACL you must input Confidence Level, Population, Materiality and Expected Error Rate

Extracting, Exporting and Reporting Extracting: • All data analysis software can extract tables, or excerpts of tables, into other tables • Gets rid of copy and paste • Only extracts the fields and records that you want

Data Analytics: Pursuing Analysis in the Present Day October 18, 2012 James Overman