Here are some useful Microsoft Excel formulas and tools which I have found to be useful in fraud investigation. These tips and tools assume some level of understanding of Excel, but are far below the level of using macros and other advanced features.
Creating a Time-line in Excel
I have identified two sources for this. First if you’re working on an older version of Excel you can use the templates at Vertex42 LLC or Mr. Excel.
If you are using a version of Excel that is the 2007 or later, you can use the template that is included in the software. For a tutorial on the function there is one available on the Microsoft website.
Link Analysis in Excel
There are a couple of ways of doing this. The simplest way is to set up a spreadsheet with set of columns that reflect things such as Person/Entity Name, Address 1, Address 2, City, Province/State, telephone number, fax number, etc. After you have populated your worksheet you can then use the Pivot Table function to see the links that may exist for example, it might identify entities that share the same fax number or address.
The Pivot Table function is found under the Data menu and although it does not provide a graphical representation (it is text/chart based), it still effective to identify links between contact points. There is commercial software available that will do a much better job of providing graphical representations of link analysis charts but on simple files or if you are on a budget that does not allow you to purchase more expensive software this solution can help.
The key is to ensure that your data is entered in exactly the same format. For example if you are entering phone numbers you must choose between the format (xxx) xxx-xxxx and the format xxx-xxx-xxxx (without the brackets on the area code). You cannot do some data entry in one format and some in the other or the pivot table will not identify the links.
In order to achieve this consistency you may wish to create sub-lists on other spreadsheets linked to drop-down menus in your main sheet. This is accomplished using the Validation function in Excel which is also under the Data tab.
One solution which I have not yet had the opportunity to test is called NodeXl which is a graphical link analysis tool built on top of Microsoft Excel. I have come across a few references on the web from sources which I regularly review, including Analysts Corner and http://www.kdnuggets.com/software/social-network-analysis.html.
Comparing Two Lists
Ever have to compare two lists of items, such as a list of vendors or a list of social network friends to see if a value exists on both lists? Check out the post on IACA Analyst Toolbox site’s by an individual named Michael Chesbro. The formula in the output column is: =VLOOKUP(A1:A10,$D$1:$D$7,1,FALSE).
You can add some conditional formatting on your output column to make the results of interest a little more vibrant.
The Find Function
This is about as simple as it gets but so very helpful. You access this function by pressing “Ctrl f”. A search box will appear that lets you search through your spreadsheet (or workbook) for a specific term. For example if you have a list of vendors in a spreadsheet and you need to see if “bad guy co.” is on your list of vendors, this is the function that you need. It is not all that useful in small lists because you can manually scan the list faster than using the function.
Another example is when you want to see how similar terms are used across multiple spreadsheets within a work book. Say for instance that you are aware that a specific payment of interest is called “fundraiser”, you may search through the entire workbook for other payments labeled “fundraiser” to see where they appear. To do this you simply hit Ctrl F, click on Options and select “within workbook.”
The caveat here is the same as the caveat on many of the other tips provided. Your data format must match the format of the entries in the worksheet or you are not going to necessarily find what you are looking for (pardon the pun).
A work-around for this is using only a part of the search term which you are likely to find included in your source data. Rather than searching for “bad guy co” which may be listed as “badguyco” or “thebadguycompany” on your list of vendors, you may just want to search for the term “guy” (without quotes of course).
Separate Terms
If for whatever reason you need to separate whole terms into separate parts which is called parsing, the “Text to Columns” function under Data on the toolbar is very useful.
For example, let’s assume that you imported some HTML code into Excel from a website you were reviewing. After sorting a few different ways you end up with a series of rows that look something like:
Column A
id=abcd
id=defg
id=lkjl
id=lkjn
For your analysis you would like to use only the id values rather than including the “id=”. To do this simply access the Text to Columns function, click other and put the equal sign in the “other” space provided. This would end up producing the following:
Column A
id
id
id
id
Column B
abcd
defg
ikjl
lkjn
Other sources
If you are a Certified Fraud Examiner and you’re interested in learning more Excel tips and tricks I would also suggest that you review the “Fear Not the Software” articles from various issues of Fraud Magazine. The articles are written by Richard B. Lanza (and occasionally other contributors) and are very insightful.
Mandatory Disclaimer: Evince Services, Inc. is in no way related to Microsoft, but Excel is a commonly used software and therefore a possible low-cost solution for some reader’s problems. NodeXL®, Excel® and Microsoft® are registered trademarks of Microsoft Corporation. Mr. Excel® is a registered trademark of Tickling Keys, Inc.
