In many types of litigation the evidence is often in the form of bank statements, credit card statements and other loan statements. Forensic accountants often need to analyse these types of documents and transactions and form an opinion as to the underlying purpose of the transaction.

Recently I was provided with more than 120 pages of bank statements and was faced with the possibility of having someone enter these transactions in an Excel spreadsheet manually. Just prior to sending this work for data entry I noticed that the statements had been generated straight from a computer system into PDF document and I was able to copy and paste the contents of the PDF document into an Excel spreadsheet.

Once I got the data into an Excel spreadsheet I realised that each transaction had been pasted into a single cell of the spreadsheet. This included the date, the description of the transaction, the amount and the balance of the account.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

I then realised that if I could find some logical “markers” in the string of text that applied to nearly all the transactions I would be able to write some computer code that would extract each of the key components out of one line of text and place each piece of relevant data into an appropriate column (or field).

Having the data in this type of relational data format meant that further analysis could be performed very quickly as the need arose.

I found three commonalities across more than 1,300 rows of data that i could use to write a VBA script to analyse the data. A short time later, I tested the code and confirmed that out of around 1,300 lines of data I only needed to manually correct less than 10 lines of data.

 

 

 

 

 

In addition to saving the client a substantial amount of time and the inherent inaccuracy of manually entering data I think that this is a great example of where forensic accountants can bring in skills from other disciplines such as computer programming and database administration to assist our clients in property settlement, divorce, inheritance disputes and in commercial litigation.

I often come up against other forensic accountants with more years of experience in forensic accounting however I find that faced with the same situation their first response is often to print the statements out in hard copy form, whole punch the document, label the folder and then try to mark up the documents using a pen and sticky post-it notes.

In contrast, the approach that I try and take is to approach the matter in a holistic sense where each transaction is effectively “post it noted” but in a database form. This means that as the population of transactions increases with other information being provided to me, I can incorporate this additional information in my analysis and as the amount of information increases the complexity actually decreases. This is because with more transactions I’m able to obtain a clearer picture of what has transpired but if the data is not in an analysable format then the connections between key pieces of information are actually less likely to be made as additional information is obtained.

In any litigation matter or any corporate matter where there are large quantities of data it can really “pay dividends” to think of how the information is structured, how it is processed and how you manage your data to obtain the best result in a given situation.

The next challenge I faced was that the bank statements provided to me had the description of the transaction across multiple lines. This meant that the description text was occurring both before and after the transaction amount and account balance. Instead of trying to extract two description fields from a single line of text, I took the opposite approach and extracted the amount and balance field out of the text fields. Looking at the problem from a different angle, meant that I was able to come up with a simple and elegant solution to what would have been a messy and potentially labour intensive process.

By this stage, I had cleaned up the data so that each row of data was one a single line and I had extracted the amount and balance from this string of text into separate fields. I was then able to extract the date of the transaction from the start of the line of text using some relatively simple excel formulas (e.g. =len(), =left() and =right() formulas).

I was now at the stage where I had the date field in its own column, the description in a single column, the amount in a single column and the balance of the account in a single column. I then added two additional fields, being Category and Financial Year. The idea of adding a Category to each transaction is that I am trying to group similar transactions in the same way. This means that If I have say 300 transactions for Bank Fees across ATM fees, BPay fees and other types of fees I can categorise these as simply “bank fees”. This again reduces the amount of data that the user of the information needs to digest.

In my experience, unless the dispute is about bank fees, then bank fees are a relatively small part of the data and are usually not the subject of the dispute. This means that they can be aggregated together as one category and if additional detail is needed then this detail can be included as an Appendix in the expert forensic accounting report.

As I have described above, I started with the base four fields from the source bank statement being date, description, amount and account balance. I have then enhanced the data with a Category field and also the financial year in which the transaction occurred. The financial year column is useful as the analysis can then be merged with other financial data such as Profit and Loss Statement and other financial year based information.

I mentioned above that I use a Category for each transaction. I generally try and restrict the number of categories to around 12 to 15 different types of categories, however more or less can be used depending on the type of forensic accounting report that is being prepared. Where I have an amount transferred from say Account A to Account B. I will generally categorise this transaction as “Internal Transfer”. If I have then been provided with other bank statements that contains the “B” side of the transfer, then I will categorise the associated transaction as “Internal Transfer” as well. This means that when the data is added together, both sides of the same transaction effectively net each other off and when I sum the amount in a Pivot Table or database query then the sum of the Internal Transfers should add to zero.I use this technique as a check mechanism to ensure that I have actually matched off transactions that are really two sides of the same transaction.

If there are “transfers” to what I describe as an “Unknown Account” (that can’t be matched against another transaction), then this raises the possibility of a bank account that has not been disclosed in the proceedings and is an excellent technique to indirectly find hidden assets.

I include the balance of the account in my analysis. Although the balance of the account is generally not as important as the Amount of the individual transaction, I often use the Balance field or column as a check sum to ensure that the data entry has occurred properly. The balance of the account on one line of data can be subtracted from the balance on the immediately preceding line of data to obtain a calculated amount of the transaction. The result of this calculation can then be compared to the amount of the transaction that has been entered into the Amount field this ensures that the data entry has been undertaken accurately.

Generally at this stage I run a number of preliminary reports analysing the data summed by category and financial year that the transaction occurred in. Often there can be additional cleaning and processing of the data at this point of the process to ensure that the report is generating the information that is required for the expert forensic accounting report.

Another field that is important to add to the database is a Reference field. Generally I like to give each document or page of each document a unique number. Each transaction sourced from a particular page is given that unique reference number. It’s important to add this number into the database for each transaction so that when this information is used in different reports, the source of the data can be quickly ascertained. Users of the report can check the source of the data back to original documentation.

The process described in this article is a “behind the scenes” snapshot of a typical data entry, data cleaning and money-tracing process. Mostly clients of forensic accountants only see the output of this process.

Simplifying thousands of transactions into clear and precise reports which then form the basis of a report is one of the satisfying parts to being a forensic accountant.

Further Information

We provide services to corporations, law firms and individuals in Sydney, Brisbane, Melbourne, Adelaide, Perth and across Australia. If you would like further information about using our forensic accounting services for a divorce, litigation, or other forensic accounting matter, then please contact us for an obligation free discussion on 1800 454 622.