Data Analytics: Pursuing Analysis in the Present Day October 18, 2012 James Overman - PowerPoint PPT Presentation

data analytics pursuing analysis in the present day october 18 2012 james overman n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Data Analytics: Pursuing Analysis in the Present Day October 18, 2012 James Overman PowerPoint Presentation
Download Presentation
Data Analytics: Pursuing Analysis in the Present Day October 18, 2012 James Overman

play fullscreen
1 / 57
Data Analytics: Pursuing Analysis in the Present Day October 18, 2012 James Overman
114 Views
Download Presentation
eric-gould
Download Presentation

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

- - - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

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

  2. 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

  3. 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

  4. 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

  5. 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

  6. 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

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

  8. 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

  9. 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

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

  11. 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

  12. 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

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

  14. 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

  15. 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

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

  17. 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

  18. 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

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

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

  21. Data Types Delimited

  22. Data Types Fixed Width

  23. Data Types Flat/Report:

  24. 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

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

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

  27. 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.

  28. 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

  29. 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

  30. 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…

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

  32. 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

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

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

  35. 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"

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

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

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

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

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

  41. 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

  42. 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

  43. 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

  44. 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

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

  46. 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

  47. 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

  48. 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

  49. 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

  50. 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