Active Data for Excel

1. Active Data for Excel

2. Active Data for Excel • All-In-One add-in suite that brings advanced database like analysis, data mining, manipulation and conversion to Microsoft Excel • provides powerful dialog driven data analysis and manipulation features, many of which were up until now, only available in specialized forensic accounting and auditing tools that cost thousands of dollars per user.

3. designed for Excel users of all stripes and supports features such as summarize / pivot, worksheet join / merge / match / compare, gap / duplicate detection, stratification, digital analysis using Benford's law, aging (date analysis), database like queries on worksheets, statistical sampling (random, stratified random and monetary unit / PPS), cell cleansing and conversion as well as a variety of navigators that allow you to manage your workbooks, worksheets, selections and ranges. Other features include worksheet split by group or date, column split and concatenation, calculated columns, row tagging, dialog driven formula builders, global workbook find and replace, workbook indexing, hyper-linked table of contents generation, multiple-tables-at-once import from ODBC / SQL databases, one click worksheet duplication and popup statistics.

4. ActiveData for Excel enables you to easily analyze your data and provides new levels of control over your information from within the familiar Microsoft Excel environment.Powerful data analysis features let you merge, match and compare sheets; query, tag and select rows; sort, summarize, sub-total, split, sample, stratify; analyze duplicates (de-dupe) and detect gaps in sequenced items such as check (cheque) and invoice numbers; perform top and bottom performance analysis.Manage your workbooks. Create indexed table of contents sheets for all of your workbooks. Search and replace across all of your workbooks. Instantly switch to any worksheet using our built-in sheet navigator. Automatically open any number of workbooks on Excel Start up.Manage your files. Analyze the contents of any directory (local or network) by presenting the file and folder listings in a spreadsheet format, complete with hyperlinks. Isolate your disk-hungry folders by finding out which folders (directories) are consuming the most space.

5. Features 1.Workbook Features Workbook Navigator: Lets you rename, create, move, print or hide your sheets and open, close or save your workbooks. Global Workbook Find/Replace: This feature allows you to search for information across all of your workbooks and optionally replace occurances. Workbook History/Favorites: Shows you all of the workbooks that you have opened on your PC as well as when each workbook was accessed. You can tag workbooks as being "Favorites" and use this feature as a workbook launch pad if you find yourself opening the same group of workbooks everyday.Index Workbooks: Generates an index of all workbooks in a given location, be it a local hard drive or a networked share. The index includes metadata about each workbook as well as a hyperlinked listing of all sheets contained within each workbook.Revert: Revert is a function that returns your workbook to the last saved copy. Critical if you have made errors in revising a spreadsheet.Import: Import file and folder listings into a worksheet, complete with hyperlinked file names. Analyze disk usage and find out what's eating up all of your disk space. Import data from any ODBC compliant database including Oracle, mySQL, SQL Server, Sybase and others. Grab all desired tables from a database in a single step and then analyze it using ActiveData For Excel.

6. Workbooks

7. 2. Worksheet Features Merge Sheets: Take information from separate sheets and bring them together based on a key column to provide you with a new look in an existing sheet or in a whole new sheet. Match Sheets: Extract data by matching against ranges in other worksheets.Compare Sheets: Compare two sheets with a common key and then generate totals and counts for selected columns.Query Sheet: Extract records by querying by formula, using any combination of Excel formulas. Query by date, number or character string and post them to a new sheet or append them to the current sheet. Split Sheet: Split a sheet apart by specifying the group you want to isolate and analyze. You also have the ability to split your information out by date (by week, day, month, or day of the week). Sort to New Sheet: Sort your data by up to 6 groups – three more than Excel itself! -- and then select the columns you want to carry over into either a new sheet or appended to the sheet you’re working on. Sample: Randomly sample a specified number of rows within a range.

8. Worksheets

9. 3. Row based Features Tag Rows: Tag rows in a myriad of ways: Manually select the rows you want, Tag rows by specifying your criteria (e.g. < or > ); select columns then enter a range, specific values, case or punctuation; query by formula; match a range from one sheet against a column in the sheet you want analyzed; tag all your even and odd numbered rows; or tag gaps in your data.Clear Tags: Clear your tagged spreadsheet in one command.Invert Tags: Reverse tag all the rows that you have not tagged using one of the tagging mechanisms to give you a mirror image of your tags.Copy Tags: Take your tagged rows and copy them to a new or existing sheet, or to a new or existing workbook.Move Tags: Take your tagged rows and move them from your existing sheet to a new or existing sheet or to a new or existing workbook.Delete Tags: Take your tagged rows and delete them in one command.Find Tags: Click to go to your first tagged row. Find other tags in you spreadsheet in the same manner. Also find the next, previous, and last tags with one command

10. Rows

11. 4. Column based Features Arrange Columns: Format, rename, configure, move, split, delete or hide columns. Add Calculated Columns: Create a new calculated column by using ActiveData’s formula interface. Columns/Split Columns: Take a column of data and separate the parts you want into new columns. Separate numeric data from the left, right or middle, or split text columns by looking for delimiters such as spaces or commas.Column Properties: Provides the meta-data on the column you specify. The name, type, decimals and formula are listed for each row in the column.

12. Columns

13. 5. Cell Based Features Cells/Fill Selected Cells: Fill with fixed or incremental values, characters, dates, or numbers. Random fill with numbers or dates. Specify the Start and end values for incremental and random values. Fill random items by selecting ranges from your existing worksheet. Great for generating test data. Convert Selected Cells: Select cells and convert them in a number of ways including: Change to proper case; upper/lower case; remove spaces and non printing characters; or convert to values, dates, numbers, text. Format Selected Cells: Make each cell look the way you want it or according to a template that you design. This feature lets you define headers, footer and content defaults that can be applied to each sheet to provide a consistent look and feel for your reporting requirements. Selection Navigator: Manipulate any selection you make on a sheet. Move the selection cell by cell, extend or contract the selection, swap your selection copying and pasting with a single click

14. Cells

15. 6.Data Analysis Features Group Summary: Summarize your data, by up to three groups, in ascending or descending order.Top/Bottom Items: Sort in ascending or descending order, and list a group of the top or bottom itemsAge: Take your dated information and group and total it according to intervals you set.Strata: Takes your data and splits it into bands that you define. Get the totals and stats you need by the data groupings you want and then chart if desired.Duplicates: Find duplicates in your data and tag them. Once you have them identified you can remove them, put them on a new sheet or append them to an existing sheet.Gaps: Specify a range in a column or group and then look for items that spill over. Move them to a new sheet; append them to the existing sheet. Descriptive Statistics: Select a column, and get totals, averages, standard deviations, variances, medians, mediums, max, and mins

16. 7. Other Features Shortcuts: An additional navigation feature that allows quick and easy access to ActiveData features.Open Workbooks Menu: This feature presents all of the open workbooks and the sheets they contain in a pull-down menu for quick and easy navigation.Recent Commands: This feature lists the last eight ActiveData commands you used.Double Click Sort: Double Click on the top of any column and sort the column in ascending order, double click again and sort in descending order.ActiveData Online Updates: Search for updates feature that makes sure you always have the latest version.

17. Core data analysis functions

21. Fraud Detection with ActiveData for Excel eBook • Top Audit Tests Using ActiveData for Excel is an ebook that integrates directly into the ActiveData for Excel help menu. It is a manual is to assist auditors, fraud examiners, and management in implementing data analysis routines using ActiveData for Excel. Use these techniques to proactively detect organizational inefficiency, ineffectiveness, fraud and provide measurable cash recoveries. It walks you through 16 common audit tests within the accounts payable, accounts receivable and general ledger audit areas

22. Fraud Detection and Cash Recovery Using ActiveData for Excel is a CPE accredited self paced, self study course that walks you through how to use ActiveData for Excel as a fraud detection and cash recovery tool. The course also provides sample data to test your skills prior to using "live" company data.

23. Purpose • The purpose of this is fraud examiners in implementing data analysis routines using ActiveData for Microsoft Excel. This course is not expected to explain ActiveData or Microsoft Excel concepts at length but rather to provide guidance as to which of the product’s features can be used in fraud examination setting. It walks through 5 common fraud detection tests between the accounts payable, accounts receivable, and general ledger fraud detection areas. This book provides a small subset of tests that Fraud Examiners can use to implement proactive fraud detection techniques in their organizations.

24. This course will: • Introduce you to the powerful combination of ActiveData and Excel • Walk you through basic fraud detection and cash recovery reporting concepts • Map out each step for the most common of fraud detection, cash recovery and audit tests • Suggest fraud detection and audit procedures to perform on the resulting reports

25. Power of Excel as an Fraud Detection Software Microsoft Excel is a ubiquitous tool and it has achieved this status by allowing a user to ease into data analysis. Rather than require database training or other certifications, Microsoft Excel allows a user with any Windows experience to pick up and get started. Once started, users find spreadsheets easy to navigate and flexible enough to meet their particular needs. For example, a user may have a five-line spreadsheet to total some numbers or they may maintain their sales invoices. Either way, Excel can normally contract or expand to the job. For accountants and Dors, Excel has provided them a comfortable environment to work within. And for many data analysis tasks, Excel can easily do the job. Excel allows users to Sort, Subtotal, Filter, and Merge data. They can create calculated fields, as well as, perform statistical analysis such as regression. For all of the above reasons and more, Microsoft Excel has become an industry standard in managing and analyzing organizational data

26. Limitations of Microsoft Excel as an Fraud Detection Software • Although Microsoft Excel has many powerful features and can take on many of the features of an fraud detection software, it has its own set of limitations that are presented below: • Can only process 1 Million records (Excel 2007) or 65,536 records (Excel 2003 and below) of data which may be too small for some organizational databases. • Does not document the fraud specialist’s work in easy to access logs for later reference and storage (ActiveData for Excel helps with this) • Allows data to be changed in the spreadsheet. • Can only read a small subset of the complete types of data files available in digital format. For example, EBCIDIC files stored in IBM mainframes would need to be converted for use in Excel. • Has difficulty in performing some data management and analysis tests such as relating tables. Although it can be accomplished, it is an onerous task. • Does not have functionality specifically tailored to the auditor. For example, a sample can be calculated in just a couple of clicks with minimal training in specifically designed fraud detection software. In Excel, it can be done, but it does take some effort and guidance.

27. How ActiveData for Excel Compensates for Excel’s Limitations

29. The Steps To Developing An ActiveData for Excel Fraud Detection Test • Step 1 – Set Your Sights – As in any fraud detection, risk must be assessed with tests selected to mitigate that risk. • Step 2 – Ready Yourself – By running the ActiveData for Excel test with the sample data provided, you can get comfortable for the real thing, once you get the client’s data. • Step 3 – Get Data – The process does not have to be difficult and is a necessity if you ever expect to run the fraud detection test. • Step 4 – Run It For Real / Consider Other Tests – Now that you know how to run the test (based on playing with the sample data), all that is left is to run the test on the client’s data. As you run the reports, other report ideas are bound to come to mind (that may not be explained in this book). With the data analysis procedures learned from this publication, you should be able to mold different permutations to create the newly desired report.

30. Step 1 – Set Your Sights • To know where to perform fraud detection, you need to assess the risk of certain events occurring. Since risk can be an amorphous concept, it is generally helpful to quantify the risk. The below formula does just that: Likelihood * Impact = Risk Score

31. Once the top risk areas are identified, the following responses can be applied: • Prevent/Avoid Using Fraud Detection Software Reports – Develop responses before ever letting the threat occur thus improving on any company vulnerabilities. As it relates to using this document, certain reports may be run and reviewed prior to certain transaction types ever occurring. • Mitigate Using Detection Software Reports – Develop responses that reduce the risk to a more manageable level. As it relates to using this document, certain reports may be run and reviewed on a periodic basis. • Transfer – the risk could be transferred to a third party such as an insurance carrier

32. Step 2 – Ready Yourself In this publication, we have provided 5 reports across three major fraud detection areas. Hopefully, we have identified the precise test you hope to run. If not, the explained tests should get you close and teach you the needed data analysis concepts. Please note that these concepts have been reduced to ActiveData for Excel features in the section The Tests and Their Relation To ActiveData for Excel’s Features to help you make this comparison. With the test selected, go to the appropriate page in this publication and: • 1. Read the section Why Are We Running This Test and What To Do With The Results which provides the alpha and the omega of the test to be performed. • 2. Read the What Data Is Needed? section of the test to understand the data file and fields (columns) needed in order to run the test.

33. 3. Open the sample spreadsheet provided with this publication and walk through the steps explained in the test in order to get the final results. • 4. For any concepts that you are having trouble understanding, review the Getting Help With ActiveData for Excel document that is under the Help menu in ActiveData for Excel. • 5. Allow yourself time to understand the results of each test. Begin to imagine how your data will be represented in the reports. Ask yourself: •  Is the test providing me with enough information to prevent or mitigate the risk? •  Do I need to add more tests? •  Does the presentation of the report convey its message effectively?

34. Step 3 – Get Data With an understanding of the risk to be mitigated, the test to be performed, and the data required (as stipulated in the ―What Data Is Needed?” section of each fraud detection test) all that is needed is to obtain client data and walk through the same test steps. It is suggested that prior to requesting data that all expected reports be identified so that one request is made of the client. Getting data can be broken into the following logical process steps: • Step 3aMaking Arrangements with the Client to Obtain Data • Step 3bTransferring the Client’s Data • Step 3cVerifying the Data Received from the Client

35. Step 4 – Run It For Real Now that you: •  Have the data loaded in Excel, •  Know the data has been verified, •  Understand the ActiveData for Excel steps to perform based on this publication, All that is needed is to run the procedures on the real data by following the test steps explained in this book. Also, if you want to modify the report slightly or run a new report (based on a new direction that is identified with the data analysis), the concepts learned in this book should allow you to run these new procedures. It is like learning to hammer a nail. Once you can nail into one wooden board, that function can be applied to all types of wooden boards. Let’s take a couple of examples: • 1) Query Sheet – Once you learn how to filter out unwanted records from a paid invoice history file, the same function could be applied to filtering out unwanted customers from a customer table or inventory costs that are too high. • 2) Compare Sheets – After learning to relate a paid invoice file to a vendor file, you could just as easily relate a customer file to a invoice sales file or customer sale files for two separate years.

36. If you are in need of new areas to apply data analysis in a fraud detection environment, there are many tools available on the Internet such as: • AuditSoftware.Net (www.auditsoftware.net) - website devoted to the use of all audit software, primarily data analysis. Check out the ―How To Use Audit Software‖ section for a listing of areas and associated tools to jump start a data analysis program. • Proactively Detecting Occupational Fraud Using Computer Audit Reports (http://www.theiia.org/iia/index.cfm?doc_id=4248) - a free online document designed to assist internal auditors, fraud examiners, and management in implementing computer data analysis routines to improve occupational fraud prevention and detection. The full report provides: • A step-by-step process for implementing audit software from the assessment of risk to the ultimate application of software routines. • A comprehensive checklist of data analysis reports (over 300) that are associated with each occupational fraud category based on the ACFE's uniform occupational fraud classification system. • A report description and data file(s) needed to effectuate each identified report. • AuditNet (www.auditnet.org) - AuditNet is a great source of audit information and probably their strongest relevant toolset is their ―Auditors Sharing Audit Programs‖ section which is a library of various audit programs. These documents can be reviewed for relevant audit steps that could be automated using data analysis reports.

37. Neal Patrick De Mesa Danilo Cap-atan EstrellaFangon Biannca Alonzo Lorna Hizon

39. Reference • http://www.informationactive.com/ • http://www.informationactive.com/ad/downloads/1000/FraudDetectionWithActiveData.pdf • http://download.cnet.com/ActiveData-for-Excel/3000-2077_4-10285544.html • http://www.auditsoftware.net/documents/GeneralizedAuditSoftware.pdf