Year: 2014

Every day I have clients contacting me regarding their divorce and property settlement. I approach these clients with a number of different ‘hats’. Before I dedicated myself to family law, I was (and still am) a data mining specialist mostly analysing large volumes of data from some of the largest corporations in Australia, New Zealand and the United States.

What strikes me as strange with family law matters is that I am often presented with boxes and boxes of printed documents. This includes bank statements, loan statements, credit card statements and many other schedules containing important data.

What I find unusual is that all this information would have originally been held in a  financial institutions’ database. It is then printed, photocopied by the lawyers, submitted to the family court and then eventually provided to myself. Many lawyers when provided with electronic evidence such as bank statements will print these documents, file them in a folder and then refer to them in this format (and this is how the majority of Courts and Tribunals require evidence to be submitted). However it’s my opinion, that the approach adopted by litigants in relation to managing data in many instances either prolongs a dispute or is a fundamental reason that a property settlement goes to the expense of a final hearing.

So what can we do to prevent this from occurring? Increasingly I am having all the financial records to a family law property settlement be initially inputted into a spreadsheet (in a particular format, which can then be uploaded into a database) so that our primary means of analysing the information is undertaken on a computer (usually in Excel or Microsoft Access). Each document is is given a unique reference number and this number is printed on the actual document (or in the case of original documents, it is noted on the outside of a plastic sleeve and the original document is kept within the sleeve). When we enter the transaction into the spreadsheet or database, the unique reference number on the page is also entered and this document reference number is always added to any exports, queries or reports which are then submitted to the Court, if required.

I have found that for nearly every client of mine that has taken this approach we realise that this methodology successfully takes account of the issue which was the reason for originally undertaking this data entry exercise but in nearly all cases, the ability to quickly analyse the information means that when an unrelated issue crops up during the litigation process, we have the data already organised in a way that means it is very easy to then run an additional query (often only taking less than a minute) which answers a question which would not have been solvable had we not inputted the data in this form.

I wonder how many disputes could be settled and the time that could be saved if more divorce property settlements were approached in this way?

One of the common concerns of individuals in family law property proceedings is finding hidden assets. Again if we approach the data in the right way, we can quickly ascertain if there are sums of money that have been transferred from a “known” bank account to an “unknown” bank account. This is only possible if we have entered all the transactions of the parties into an electronic form and into a format that can be easily analysed.

What other sources of information do we simply print, stick in a folder and then refer to? One that comes to mind are the financial statements for a business. In many divorce / property settlements one party has an interest in a business and we are often called upon to provide an expert opinion as to the value of this interest. Again, having the financial statements of the business in an electronic form can make a significant improvement to how a divorce property settlement is managed.

In an unrelated inheritance dispute, I recall that simply by importing all the Balance Sheet and Profit and Loss Statements into a single spreadsheet, with each column representing a year of trading, it was possible to greatly reduce the complexity of the dispute and it allowed us to obtain the clarity of mind that was needed to properly assist the client.

So many parts of our lives are run from a database (we even manage our friendships through Facebook, which is held in a database). When it comes to the division of assets in a divorce property settlement, a well thought out approach to managing the data will provide tremendous benefits to the parties that are in dispute.

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.

 

Rushmore Group (and its predecessors) have been based in North Sydney since we setup a boutique forensic accounting business in 2006.

We are still just a 5 minute walk from North Sydney Station.  There is also plenty of on street parking directly in front of our building.

Our new address is:

Level 3, Suite 301
110 Pacific Highway
North Sydney NSW 2060
Tel: 1800 454 622

Although the majority of our clients are spread across the country, we look forward to welcoming you to our new office and assisting you with a forensic accounting, business valuation or related matter that you may have.

For more information, please don’t hesitate to contact us on toll free 1800 454 622.

 

 

Yesterday I was on the Gold Coast to meet with a client and discuss a forensic accounting matter. One of the advantages of offering a national forensic accounting service is that it gets me out on the road and talking to clients and seeing them face to face.

Another aspect of travelling around Australia is that it allows me time to take time out and think of the “big issues” facing our clients and our business in general. Yesterday my thoughts kept returning to automation, processes and how forensic accounting can benefit from this.

Many practitioners approach forensic accounting from an ad-hoc perspective and expert reports are built up in layers over the process of weeks or even months. In many engagements this is the right approach, however I wonder how much “knowledge” that is built up in one report is actually transferred into the next report and subsequent expert reports. I wonder if with automation, computer programming and databases one could build up the foundations of a report in a much faster, more accurate and ultimately more efficient manner.

I can see a future where the forensic accountant sits down at a computer and builds a report not beginning in a narrative “report” style approach but rather opening a software program and entering data, selecting pre-formatted sections and building the report up in a “database” and then nearing completion exporting the report to the narrative form.

Using computer programming and automated processes is currently used by Rushmore Forensic to initiate business valuation reports and one of the benefits of this approach is that we can pass on the savings in time to our clients. This is approach has meant that we can offer a business valuation report for a fixed fee of $4,990 plus GST. The same report without automation and processes would be more than $8,000.

Yesterday’s trip has inspired me to invest more time in building automated solutions that ultimately will result in a better expert accounting report at a lower cost to our clients.

Welcome to the brave new world of forensic accounting!

Happy Chinese New Year and welcome to the year of the horse.

We look forward to assisting individuals, small businesses and corporates with expert forensic accounting and business valuation advice.

According to Chinese astrologers:

• People born in the Year of the Horse will have plenty of energy this year but they should also watch out for their temper towards the end of the year.
• The energy flowing from the horse can easily turn negative and into conflict.
• This year people will stand for their principles, so be prepared to fight for something you believe in; and
• Be prepared for surprises.

As a forensic accountant involved in all aspects of financial disputes, litigation, fraud, divorce, and property settlements we can relate to this advice. Working across many types of dispute we find that conflict, fighting for what you believe in and surprises are all in a day’s work for a forensic accountant!

Gung Hei Faat Choi!