1 / 50

Middle Management CONFERENCE Audit Tools for data analysis

Middle Management CONFERENCE Audit Tools for data analysis. St. Paul, MN. AGENDA. Audit Tools for Data Analysis  YTD Utility  G/L Entries Utility  CAFR  Power Pivot  PDF Compare Excel Dashboard Reporting  Formulas  Graphs  Dynamic Graphs Effective graphs in reports

lucie
Télécharger la présentation

Middle Management CONFERENCE Audit Tools for data analysis

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Middle Management CONFERENCEAudit Tools for data analysis St. Paul, MN

  2. AGENDA • Audit Tools for Data Analysis •  YTD Utility •  G/L Entries Utility •  CAFR •  Power Pivot •  PDF Compare • Excel Dashboard Reporting •  Formulas •  Graphs •  Dynamic Graphs • Effective graphs in reports •  Report Examples

  3. YTD UTILITY • Data background • File layout format • Excel macros VBA • Utilities Purpose

  4. YTD UTILITY

  5. YTD UTILITY

  6. YTD UTILITY

  7. YTD UTILITY • This allows our auditors to look at every transaction that is entered into the counties General Ledger system in a short period of time • Creates regular sample & Single Audit Sample • Provides menu options to filter the data into different data types

  8. Ytd utility • Balance sheet • Reconciliations • Operation statement • Budget Import • Address comparison • Fraud Tests

  9. General Ledger Utility • File layout format • Excel macros VBA • Utilities Purpose

  10. General Ledger Utility

  11. General Ledger Utility • Compares payroll for employees being paid out of multiple payroll lines items • Compares general ledger records to payroll records • Employee Payroll Summary

  12. CAFR Utility • Data background • File layout format • Excel macros VBA • Utilities Purpose

  13. CAFR Utility

  14. CAFR Utility

  15. CAFR • Excel Workpapers • Draft statements • Materiality • Major Fund Determination

  16. Why vba macros? • Macros allows simple tasks to be repeated • VBA is built-into office products • Allows consistency for the whole staff • Easy programming language to learn

  17. Custom Toolbar • Custom tickmark • Creates shortcuts to commonly used items • Updates links • Check links

  18. powerpivot

  19. DEMO

  20. Compare two documents • PDF Compare • Microsoft Word Compare • PDF Mail Merge

  21. Word 2010

  22. Word 2010

  23. Word 2010

  24. Adobe Professional

  25. Adobe professional

  26. Adobe professional

  27. Adobe mail merge

  28. Excel dashboard

  29. Excel dashboards

  30. Excel dashboard

  31. Excel DASH Board reporting • Formulas • small; index; rank; sumif; named ranges • Dynamic data • data connection; macro refresh data • Building the graphs

  32. Excel Dash board reporting • RANK Function • Returns the rank of a number in a list of numbers. • RANK(number, NamedRange) • SUMIFS Function • Adds the cells in a range that meet multiple criteria. • Sumifs(sum_range, criteria_range1, criteria1, criteria_range2, criteria2) • SMALL Function • Returns the smallest value in a data set. • SMALL(NamedRange, k) • INDEX Function • Returns the reference of the cell at the intersection of a particular row and column. • INDEX(reference, row_num, column_num, area_num )

  33. Excel dynamic data

  34. Graphs http://www.visual-literacy.org/periodic_table/periodic_table.html

  35. Collections

  36. Cash/Check Analysis

  37. Deposits per Month

  38. Questions

More Related