Police tend to fear math more than they fear violence. You know your buddies will jump in and help you if a suspect is stomping you by the side of the road, but not if a stack of bank statements is working you over at your desk. We tend to be cowards in the face of bookkeeping, and white-collar criminals exploit it.

These cases need not be intimidating. Most can be rendered simple with some purposeful organization. We’ll start by organizing bank statements.

Tools for the Task

First are the tools. If you don’t already have them, Microsoft Excel and Adobe Acrobat Pro are going to make your life a lot easier.

Excel is a spreadsheet program and spreadsheets are the backbone of these cases. It comes in the Microsoft Office suite at a pretty reasonable price, and most agencies already have it. Adobe Acrobat Pro is a portable document format (PDF) program that allows you to manipulate PDFs. There is a free version that allows you to read PDFs, but the Pro version allows you to edit documents. And for our purposes, the “Pro” version has Optical Character Recognition (OCR), which is unbelievably helpful when searching through thousands of pages of documents.

(Note: There may be free or lower cost alternatives to Excel and Adobe, but I’m not familiar with them.)

If you find yourself buried in a stack of raw bank statements, your first step is to contact the financial institution where they came from. Lots of financial institutions can export their statements directly into Excel spreadsheets. Go the easy route if you can.

(If they can’t, or won’t, there is commercially available software that can export a lot of the data from most bank statements into an Excel spreadsheet. The software tends to be expensive and it requires a fair amount of technical knowledge to use. The one I am familiar with is BankScan [www.bank-scan.com], but there are others as well.)

Excel 101

If you’re not familiar with Excel, here’s a quick summary. Start by opening it up. The blank screen that just opened up is called a workbook and you can save it under whatever name you like. The tabs at the bottom of the screen that say “sheet1” “sheet2” and “sheet3” are called worksheets, and you can rename them by right clicking on them. The squares on the grid of a worksheet are called cells. Columns of cells run up and down and are labeled with letters. Rows run side to side and are labeled with numbers. If you press “Tab,” you will move over one column. If you press “Enter,” you will move down one row.

Right above the grid is a field labeled “fx” that displays what you have typed into the currently highlighted cell. You can also type formulas in that field so that Excel will do calculations, but we’ll cover that on another day.

Start by typing the following in any four adjacent cells across a row:
Date
Check #
Amount
Payee

Now highlight those four cells and the four cells immediately below them by left-clicking and dragging over the area. At the top of the workbook are a series of tabs. Click the “Insert” tab, which is second from the left. Now below that you will see “table,” which is also second from the left. Click it. Check the little box that says “My table has headers,” then “OK.”

Now you have a table and you can start entering data. Every time you hit tab on the last cell of a table it will add another row.

Those four categories are the basic information you need to understand most financial crimes. You can add more columns to tailor the table to your specific case. If there are multiple signers on a checking account, add a column for the signer. If the memo line is important, add a column for that.

To add a column, just type a column name in the cell right next to your existing table and Excel will add it automatically. You will only be able to sort the data by the columns you have, so include whatever information you think is important. But you also have to enter all of the information by hand, so be pragmatic.

Sometimes you will encounter a bank statement that you can cut and paste neatly from a PDF to Excel. It almost never works out in the exact format described above, but it can be a huge timesaver. Data entry is a slog, so consider any shortcut you can find.

Important: If you’re going to farm out the data entry to a secretary or an intern, be clear about how you want it entered. Do you want spending entered as negative numbers and deposits as positive numbers? Or the other way around? Do you want the date listed on the check, or do you want the date the check cleared the bank? What reference number are you using for deposits? Even if you are farming out the bulk of the data entry, I recommend that you enter a month or two yourself so you can sort out those formatting issues and anticipate the questions that are going to arise.

Conclusion

That should give you plenty to work on for the time being. Next time we will get into some techniques for analyzing the data once you have it organized.