Download
1 / 35
400 likes | 643 Views
CAAT SOFTWARE FOR EXCEL . CAAT software is software that is used by auditors and accountants to help them perform testing on financial data. The key benefits of using CAAT software are that: it can greatly improve efficiency (often saving many hours a week),
E N D
CAAT SOFTWARE FOR EXCEL CAAT software is software that is used by auditors and accountants to help them perform testing on financial data. The key benefits of using CAAT software are that: it can greatlyimprove efficiency (often saving many hours a week), itreduces risk; and improves the quality of the audit.
TOPCAATs TopCAATs is a CAAT software that runs as an Add-in for Excel, containing over 100 tools. The tools range from statistical sampling routines to powerful fraud detection tools. Being based in Excel and using simple input forms, TopCAATs is immediately familiar and one of the easiest CAAT software to learn. TopCAATs includes over 130 tools and features, all designed to make life easier for Auditors and Accountants.
TOPCAATs ribbon in Excel 2007 In Excel 2007/2010 all the tools appear on the TopCAATs ribbon bar. In Excel 2003 the tools are split between the TopCAATs Toolbar and the TopCAATs menu.
TOPCAATs TOOLBAR The TopCAATs toolbar includes various tools to help with day to day life in Excel and to help tidy up and manipulate source data. 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 Manipulate Text- A variety of tools to manipulate text, including changing the CASE, removing unwanted spaces, removing non-alphanumeric characters, and many others. Insert Borders -A variety of border options for your cells Tick/Cross - Inserts a "tick" or "cross" symbol into a cell using the included tick font (avoids having to use “Insert symbol”)
TOPCAATs TOOLBAR Merge, Wrap and Auto fit - Merges the selected cells, wraps the text and auto fits 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. Debit/Credit Columns - Splits an amount column into separate debit/credit columns, or combines debits and credits into a single column. 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.
TOPCAATs TOOLBAR Fix Dates - Every had a system report where Excel doesn't recognize the dates? This tool will automatically work out what format the source data is in, and covert it to dates Excel (and TopCAATs) can recognize and work with. 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 desired. Add Date Column - Creates a formatted date column, for example to identify the "week number" and "financial year", which can then be used to summarize by. Header and Footer - Inserts a standard header and footer into the worksheet. Uses information stored in Engagement Details.
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. Reverse Polarity - Converts positive numbers to negative and vice versa. Swap Delimiters - Converts numbers in the format “12.345,67” to “12,345.67” so Excel can recognize them as numbers. 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 recognize them as numbers (e.g. “-9,652.23”).
TOPCAATs TOOLBAR Convert to Values - This converts any formulas in the current selection to their values. Extract Visible Cells - Extracts all the visible cells on the sheet to a new sheet. Sheet Index - Creates a clickable index sheet, either alphabetically or in original sheet order. 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.
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.
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.
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. 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. Aging - Re-age a listing based on a variety of aging options, including custom aging bands. Quick summary - Quickly and simply summarize your data, based on criteria you specify. Advanced summary - Summarize your data in a variety of ways and perform statistical analysis on the results.
DATA ANALYSIS TOOLS Ledger split - Splits a report into several worksheets (e.g. split an inventory listing into a separate listing for each warehouse). Column statistics - Get a detailed report of the data in each of your columns. Highlight Changes - Highlights any differences between 2 versions of a worksheet or workbook. Benford's Analysis - Perform Benford's Digit Analysis testing to identify unusual patterns in data. Exception report - Extracts any records that meet the criteria you specify (up to 3 criteria). Specific Comments - Searches a worksheet or range for specific comments (e.g. “suspense”, “rainy day”, “unknown”, etc.)
DATA ANALYSIS TOOLS Top and Bottom - Extracts the top and bottom records from a list. Gap detection - Look for gaps in a sequence (e.g. missing invoice or journal numbers). Duplicate extraction - Extract duplicate records, based on the fields you specify. Same, Same, Different - Extract records where 2 fields are the same and a 3rd is different (e.g. same amount, same date, different vendor). Outliers - Extract records where the amount significantly deviates from the mean. 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.)
DATA ANALYSIS TOOLS Round Number Extraction - Extracts any records which have a rounded amount. Analytical Review - Compare two columns of numbers and calculate the absolute and percentage difference (e.g. current period and prior period). Options - Allows you to specify global options for TopCAATs.
OTHER TOOLS/FEATURES Multi Drill Down- Allows you to perform a Show Detail on multiple cells in a pivot table at the same time (either exporting the results to separate sheets, or to a single sheet). Rename Show Detail Reports - When you "Show Detail" in a Pivot Table, Excel renames the sheet numerically, e.g. Sheet4, Sheet5, etc. TopCAATs can automatically rename the new sheet with information about the cell you double clicked. Double Click to Sort- Allows you to sort your data by double clicking in row 1 (can be turned off in the options).
OTHER TOOLS/FEATURES TopCAATs Statistics - Provides statistics about the selected cells on the right click menu. Selecting a statistic stores the value to the clipboard, so you can easily paste it to another cell or even another document/program (e.g. copy the total of the selected cells and paste into Word). Tick Font - A font consisting of a wide variety of ticks that can be used to mark your data. The font is globally available, so you can use it in other applications like Word or PowerPoint too.
SECTION MODULES TopCAATs includes 7 “Section Modules” each specifically targeted to an area of the accounts. Prepare Sheets - This prepares a worksheet for input into the section modules. 7 SECTION MODULES: • Non Current Asset Section Module • Asset additions • Asset category summary • Recalculate straight line depreciation • Recalculate reducing balance depreciation • Zero or negative net book value • Accumulated depreciation > net book value
SECTION MODULES • Inventory Listing Section Module • Aging • Recalculate inventory balance • Calculate inventory turnover (by value and quantity) • Zero or negative unit cost • Zero or negative quantity • Large inventory amounts (by value and quantity) • Inventory received around a specific date • Inventory location summary • Last sales price lower than cost • Total sales value lower than cost of goods sold • Trade Receivables Ledger Section Module • Account over given amount • Accounts with credit balance • Accounts over credit limit • Accounts with a rounded balance
SECTION MODULES • Trade Receivables Invoice Section Module • Aging • Duplicates • Invoices over a given amount • Credit transactions • Invoices posted on specific days • Invoices posted on specific dates • Transactions around a specific date • Transactions posted between 2 times • Transaction summary by user • Rounded amounts • Trade Payables Ledger Section Module • Account over given amount • Accounts with debit balance • Accounts over credit limit • Accounts with a rounded balance
SECTION MODULES • Trade Payables Listing Section Module • Aging • Duplicates • Invoices over a given amount • Debit transactions • Invoices without purchase orders • Invoices posted on specific days • Invoices posted on specific dates • Transactions around a specific date • Transactions posted between 2 times • Transaction summary by user • Rounded amounts
SECTION MODULES • General Ledger/Journal Listing Section Module • Duplicate journals • Missing journals • Non balancing journals • Journals over given amount (by journal line or total journal debits) • Transactions posted on day of week • Transactions posted on specific dates • Transactions posted between 2 times • Rounded amounts • Multiple users, same journal • Summary by user • Summary by period • Summary by source • Summary by period and account • Summary by source and account • Summary by user and account • Summary by period and source
USES Other than the reduction in audit risk, the other key benefit of TopCAATs is the time savings, which can be substantial. Our research shows that TopCAATs will save an auditor 2-5 hours every week on average! Take a look at these examples
USES Most auditors already use CAATs to some extent – simply using Excel to check that a ledger casts is using a CAAT, but specialized CAATs packages are often not used, or their full potential is rarely realized. This is often because other products are so complicated that even with substantial training; staff are reluctant to use them. They are also extremely costly and it can cost a small fortune to buy licenses and train staff At Reinvent Data, we understand that auditors and accountants come from a wide variety of backgrounds, and may not have a strong IT skill set. TopCAATs allows users to quickly and easily analyze any area of the accounts, from an invoice or sales listing, to the general ledger and journal listings, with minimal training The seamless integration with Microsoft Excel ensures a familiar interface and the colour coded user forms and step by step guidance gives TopCAATs the shortest learning curve of any CAATs software on the market
USES The benefits don’t stop at the junior staff – TopCAATs reports are all in a standard format, making them easy to review, saving managers time as well. Each tool contains an audit trail, detailing what analysis has been run (the section modules even have a screenshot showing the parameters entered) allowing complete re-performance of the work undertaken if required. Also, with your junior staff using TopCAATs, you’ll spend less time coaching on the job (as TopCAATs is so intuitive and simple) freeing up your valuable time to focus on the key audit issues
USES INTERNAL AUDITORS • CAATs have similar benefits for internal auditors as they do for external auditors. • The uses of TopCAATs for an internal auditor are almost endless, but here are a few examples: • find duplicate payments • check payroll records for invalid national insurance numbers • perform Benford's testing to identify fraudulent data sets • Furthermore, as the reports from TopCAATs are all in a standard format, it makes benchmarking between divisions, locations or subsidiaries easy
Accounting Department If you work in an accounts department, you probably find yourself performing the same tasks each week or month and you probably use Excel on a daily basis. Many of the TopCAATs tools can be used to automate these repetitive tasks, allowing them to be performed in a fraction of the time. It also reduces the chance of errors being made. The TopCAATs tools are so easy to use that you'll be producing meaningful reports, that help you perform your job better in no time at all! The wide variety of tools, and inbuilt flexibility make up for a number of shortcomings in many mainstream accounting packages – ever thought to yourself “I wish I could produce a … report”, well there's a good chance you can using standard reports and TopCAATs The toolbar will speed up your daily Excel use – whether it's quickly applying formats, extracting visible cells to a new sheet, converting formulas to values or inserting a header and footer, TopCAATs has got it covered Our ability to customiseTopCAATs to suite your specific needs means it truly can become the ultimate support tool for your accounts department. For example, we can include custom reports which clean and analyse standard output from your accounting software, then convert it into the format you want, all at the click of a single button! If you've got a task that takes 30 minutes every day, we can create a button that do it in 30 seconds.
Financial Management As a finance manager you are responsible for the companies accounting records. You need to be constantly aware of issues that may impact the financial position of the company, and need to have confidence in the underlying numbers. To do this, you need good quality information, presented in an appropriate manner. TopCAATs allows you to quickly and easily generate reports that show you the information you need to run your finance department. TopCAATs allows you to perform high level analysis, and then drill down to the details on areas of interest. Suspicious and unusual transactions can be identified in seconds, allowing further investigation. TopCAATs can be used to perform a wide variety of internal controls, strengthening your control environment, helping to prevent and detect mistakes, errors and fraudulent activity TopCAATs can also be particularly useful when approaching the annual audit – it can identify the areas and transactions that the auditors are likely to ask questions about. This allows you to prepare in advance of the audit, lowering the pressure on your staff during the audit, and facilitating the auditors work (this may even give you a case for fee renegotiation!)
Fraud Examiners • One of the key parts of any fraud examiner's job is substantial amounts of data analysis – which is exactly what TopCAATs is designed for. Many of the tools are specifically designed to look for suspicious transactions, and identify potential fraud. • Unfortunately, fraud is fairly common place in today's world – it is widely acknowledged that the majority of companies experience at least one fraud a year. Studies have shown that the average company can expect to loose 2-5% of gross revenue to fraud. This can often amount to substantial sums, and can even be the difference between success and failure of a business. • With the large volumes of transactions within a company it is almost impossible to analyse these without the use of computers. Fraud examiners need a suite of high performance, simple to use tools, which is exactly what TopCAATs delivers. • Here are some examples of how TopCAATs can be used to detect fraud • Using “Join 2 Sheets” to look for matches between the employee master file and vendor master file (by bank account number, address, telephone number, etc) • Use “Benford's test” to perform digital analysis to check the source data is “natural” and not manually manipulated • Search for rounded transactions • Use “Outliers” to look for above average purchase price for stock lines (which may indicate a 'kick back' scheme)
GROUP 2 BSA 4B
