1 / 62

TopCAATs

T. o. p. C. s. T. A. A. TopCAATs. a CAAT software that runs as an Add-in for Excel, containing over 130 tools, from statistical sampling routines to powerful fraud detection tools based in Excel and uses simple input forms. TopCAATs. Benefits. Time savings

hidi
Download Presentation

TopCAATs

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. T o p C s T A A

  2. TopCAATs • a CAAT software that runs as an Add-in for Excel, containing over 130 tools, from statistical sampling routines to powerful fraud detection tools • based in Excel and uses simple input forms

  3. TopCAATs Benefits • Time savings • Reduction in audit risk • Allows users to quickly and easily analyze any area of the accounts with minimal training

  4. TopCAATs Disadvantage • Faced with process limitation • In Excel 2003 and previous versions the maximum row limit is 65,534 rows • In Excel 2007 this has been increased to 1.1 million rows

  5. TopCAATs Ribbon in Excel 2007/2010

  6. TopCAATs Ribbon in Excel 2007/2010 TopCAATs Toolbar • includes various tools to help with day to day life in Excel and to help tidy up and manipulate source data.

  7. TopCAATs Ribbon in Excel 2007/2010 TopCAATs Toolbar • Format as • Number - custom number format can be set in Options • Date - custom date format can be set in Options • Currency - custom currency format can be set in Options

  8. TopCAATs Ribbon in Excel 2007/2010 TopCAATs Toolbar Manipulate Text A variety of tools to manipulate text, including changing the CASE, removing unwanted spaces, removing non-alphanumeric characters, and many others.

  9. TopCAATs Ribbon in Excel 2007/2010 TopCAATs Toolbar Insert Borders A variety of border options for your cells

  10. TopCAATs Ribbon in Excel 2007/2010 TopCAATs Toolbar Tick/Cross Inserts a "tick" or "cross" symbol into a cell using the included tick font (avoids having to use “Insert symbol”)

  11. TopCAATs Ribbon in Excel 2007/2010 TopCAATs Toolbar Wrap Text Toggles wrap text on/off for the current selection

  12. TopCAATs Ribbon in Excel 2007/2010 TopCAATs Toolbar Merge, Wrap and Autofit Merges the selected cells, wraps the text and autofits the cells to the appropriate height. When the built in excel function “Merge and Centre” is used, it is unable to auto fit the row height to ensure that all the data is visible, TopCAATs solves this

  13. TopCAATs Ribbon in Excel 2007/2010 TopCAATs Toolbar Debit/Credit Columns Splits an amount column into separate debit/credit columns, or combines debits and credits into a single column

  14. TopCAATs Ribbon in Excel 2007/2010 TopCAATs Toolbar Fill Cells Fill selected cells with fixed values, incremental values or random values. Can either prefix, append or replace existing values. Can also "fill from above", allowing you to repeat a value (e.g. supplier number or date) where it only appears once.

  15. TopCAATs Ribbon in Excel 2007/2010 TopCAATs Toolbar Fix Dates Every had a system report where Excel doesn't recognise the dates? This tool will automatically work out what format the source data is in, and covert it to dates Excel (and TopCAATs) can recognise and work with

  16. TopCAATs Ribbon in Excel 2007/2010 TopCAATs Toolbar Global Find and Replace Search for a value or text across the current workbook, all open workbooks or all workbooks in a folder. You can also choose to replace the found term if dersired

  17. TopCAATs Ribbon in Excel 2007/2010 TopCAATs Toolbar Add Date Column Creates a formatted date column, for example to identify the "week number" and "financial year", which can then be used to summarise by

  18. TopCAATs Ribbon in Excel 2007/2010 TopCAATs Toolbar Header and Footer Inserts a standard header and footer into the worksheet. Uses information stored in Engagement Details

  19. TopCAATs Ribbon in Excel 2007/2010 TopCAATs Toolbar Comment to Header Many of the tools store details about the options selected, source data, etc. as a comment in cell A1 (an audit trail). This button converts this comment into a document header, incorporating stored information from Engagement Details

  20. TopCAATs Ribbon in Excel 2007/2010 TopCAATs Toolbar Reverse Polarity Converts positive numbers to negative and vice versa

  21. TopCAATs Ribbon in Excel 2007/2010 TopCAATs Toolbar Swap Delimiters Converts numbers in the format “12.345,67” to “12,345.67” so Excel can recognize them as numbers

  22. TopCAATs Ribbon in Excel 2007/2010 TopCAATs Toolbar Move Minus Sign to Front This tool moves the minus sign in numbers that are displayed as “9,652.23-” from the back to the front, so Excel can recognise them as numbers (e.g. “-9,652.23”)

  23. TopCAATs Ribbon in Excel 2007/2010 TopCAATs Toolbar Convert to Values This converts any formulas in the current selection to their values

  24. TopCAATs Ribbon in Excel 2007/2010 TopCAATs Toolbar Extract Visible Cells Extracts all the visible cells on the sheet to a new sheet

  25. TopCAATs Ribbon in Excel 2007/2010 TopCAATs Toolbar Sheet Index Creates a clickable index sheet, either alphabetically or in orignial sheet order

  26. TopCAATs Ribbon in Excel 2007/2010 TopCAATs Toolbar Sheet Checker Check your source data for potential issues like merged cells, duplicate headers, total rows, blank rows/columns, etc. As well as identifying issues, the TopCAATs Sheet Checker can automatically fix most of them at the click of a button!

  27. TopCAATs Ribbon in Excel 2007/2010 Engagement Setup Engagement Details Stores information about your current engagement, so you don’t have to keep entering the same information in each tool Lead Scheduler Produces a set of lead schedules for the audit file

  28. TopCAATs Ribbon in Excel 2007/2010 Sampling Monetary Unit Sampling Estimate, Extract and Evaluate a statistically selected sample from a population Stratification Split data into bands containing ranges of values Random Sampling Extract a number of randomly sampled items Systematic Sampling Extract every nth item from the population

  29. TopCAATs Ribbon in Excel 2007/2010 Data Analysis Tools

  30. TopCAATs Ribbon in Excel 2007/2010 Data Analysis Tools Join Sheets - Joins two sheets together, based on a common matching column - although this sounds simple it literally has hundreds of uses and is one of the most useful tools in TopCAATs!

  31. TopCAATs Ribbon in Excel 2007/2010 Data Analysis Tools Append Sheets - Joins multiple sheets that all have the same column structure together. E.g. where you have separate reports for each sales person, each warehouse, etc.

  32. TopCAATs Ribbon in Excel 2007/2010 Data Analysis Tools Aging - Re-age a listing based on a variety of aging options, including custom aging bands

  33. TopCAATs Ribbon in Excel 2007/2010 Data Analysis Tools Quick summary - Quickly and simply summarise your data, based on criteria you specify

  34. TopCAATs Ribbon in Excel 2007/2010 Data Analysis Tools Advanced summary - Summarize your data in a variety of ways and perform statistical analysis on the results

  35. TopCAATs Ribbon in Excel 2007/2010 Data Analysis Tools Ledger split - Splits a report into several worksheets (e.g. split an inventory listing into a separate listing for each warehouse)

  36. TopCAATs Ribbon in Excel 2007/2010 Data Analysis Tools Column statistics - Get a detailed report of the data in each of your columns

  37. TopCAATs Ribbon in Excel 2007/2010 Data Analysis Tools Highlight Changes - Highlights any differences between 2 versions of a worksheet or workbook

  38. TopCAATs Ribbon in Excel 2007/2010 Data Analysis Tools Benford's Analysis - Perform Benford's Digit Analysis testing to identify unusual patterns in data

  39. TopCAATs Ribbon in Excel 2007/2010 Data Analysis Tools Exception report - Extracts any records that meet the criteria you specify (up to 3 criteria)

  40. TopCAATs Ribbon in Excel 2007/2010 Data Analysis Tools Specific Comments - Searches a worksheet or range for specific comments (e.g. “suspense”, “rainy day”, “unknown”, etc.)

  41. TopCAATs Ribbon in Excel 2007/2010 Data Analysis Tools Top and Bottom - Extracts the top and bottom records from a list

  42. TopCAATs Ribbon in Excel 2007/2010 Data Analysis Tools Gap detection - Look for gaps in a sequence (e.g. missing invoice or journal numbers)

  43. TopCAATs Ribbon in Excel 2007/2010 Data Analysis Tools Duplicate extraction - Extract duplicate records, based on the fields you specify

  44. TopCAATs Ribbon in Excel 2007/2010 Data Analysis Tools Same, Same, Different - Extract records where 2 fields are the same and a 3rd is different (e.g. same amount, same date, different vendor)

  45. TopCAATs Ribbon in Excel 2007/2010 Data Analysis Tools Outliers - Extract records where the amount significantly deviates from the mean

  46. TopCAATs Ribbon in Excel 2007/2010 Data Analysis Tools Out of mask - Extract records where a field does not match a pre-defined mask (e.g. bank account numbers/sort codes, National Insurance numbers, etc.)

  47. TopCAATs Ribbon in Excel 2007/2010 Data Analysis Tools Round Number Extraction - Extracts any records which have a rounded amount

  48. TopCAATs Ribbon in Excel 2007/2010 Data Analysis Tools Analytical Review - Compare two columns of numbers and calculate the absolute and percentage difference (e.g. current period and prior period)

  49. TopCAATs Ribbon in Excel 2007/2010 Options and Help

  50. TopCAATs Ribbon in Excel 2007/2010 Options and Help Options - Allows you to specify global options for TopCAATs

More Related