Middle Management CONFERENCE Audit Tools for data analysis

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

15. CAFR Utility

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

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

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

19. powerpivot

20. DEMO

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

22. Word 2010

23. Word 2010

24. Word 2010

25. Adobe Professional

26. Adobe professional

27. Adobe professional

28. Adobe mail merge

29. Excel dashboard

30. Excel dashboards

31. Excel dashboard

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

33. 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 )

34. Excel dynamic data

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

46. Collections

47. Cash/Check Analysis

48. Deposits per Month

50. Questions