Want to Excel in your Family Law Case?
I’m talking about Microsoft Excel, and I’m going to convince you that spreadsheets are your best friend in any property settlement case.
Why not use an Excel spreadsheet to list your disclosure documents (and the other party’s!), rather than doing it in Word?
The benefit of preparing the List in Excel is you can quickly and easily re-order the documents by date, type, title etc.
Additionally, with the move towards e-disclosure, hyperlinks can be included in spreadsheets to allow quick access to documents directly from the List. If your case is big enough to warrant the use of third-party e-discovery service (like Law in Order), you will most likely be receiving your List of Discoverable Documents in Excel format.
E-discovery platforms, like Relativity, will also ultimately produce a List in Excel.
I am also aware of some clever people who have used Microsoft Access to build their own e-discovery databases (rather than purchasing a licence to commercial e-discovery tools). Microsoft Access is essentially a very sophisticated spreadsheet, and again, when extracting data from it to produce a List, it is likely to be Excel format.
If you are going to compile your List of Discoverable Documents in Excel format, I recommend that you include the following information in each column (at the very least):
- Document Number (you may also like to make the document number a hyperlink to a PDF copy of the document)
- Description of document (title)
- Document type
- Date of document
- Date added to List
- Source (where did the document come from)?
- Date document copied to other party
- Review notes (you would need to remove these before sending the List to the other party)
All property settlement cases have at their heart a Schedule of Assets and Liabilities
The Schedule of Assets and Liabilities is one of the first documents that should be prepared in a financial case. If maintained throughout the matter, it will save a tremendous amount of time.
The Schedule should, from the outset, be prepared in Excel. It is a living document that should be updated throughout a case as new evidence or information comes to light.
Use formulas in Excel to do the heavy lifting. If done right, you should only need to update values in one location in the spreadsheet, and those changes should carry throughout. This is useful when calculating overall percentage divisions and cash payments.
It is also very useful when modelling a range of settlement options, and comparing the differences between each.
For each value in the spreadsheet, include a notation as to the currency date (ie, at what date was that value current). I also recommend noting the document which evidences the value, and the best way to do this is to refer to the document number in either party’s List of Discoverable Documents.
I recommend listing the assets and liabilities in the Schedule in the same order as they appear in the Form 11 Application for Consent Orders. Create sections in your schedule for each class of asset and liability. For example:
I could go on and on, but the best policy is to play around and discover as you go.
Use Excel to help you analyse financial disclosure
Ever had that sinking feeling when you have a pile of bank statements that you need to sort through in order to look for suspicious transactions?
You could go through each statement manually, and try and uncover suspicious transactions or patterns in spending.
You could also give the statements to a forensic accountant and tell them to go for it (but this is a costly process).
There are now several programs available that can convert PDF bank statements into Excel spreadsheets. You could also have your law clerk manually input the data into Excel if the bank statements are of poor quality and cannot be converted. If your client wishes to save on costs, they may wish to do the data input themselves.
Once the statements are in Excel format, you can start to do some pretty cool things:
- Apply data labels, and start filtering: Only want to see those transactions to “Cayman Islands Bank a/c 5332AAZ” which you have noticed appear a couple of times is your quick skim of the statements? Apply data labels to each column in your spreadsheet, and then apply filters so that you see only those transactions to “Cayman Islands Bank a/c 5332AAZ”. (this is the part where you lean back in your chair in astonishment because the total at the bottom of the spreadsheet reads $851,836.17).
- Consider creating a Pivot Table: These little creatures are a topic all in themselves, so I’m not going to go any further in this article. But check them out. They are really easy to use if you have structured the underlying data properly, and you can get some pretty cool analysis out of them.
- Graph it: Want to compare spending habits pre-separation with post-separation. Graph it. A picture speaks a thousand words.
- Apply conditional formatting: Only want to see transactions that are greater than $20,000 in value? Apply conditional formatting to the column containing the values, and tell Excel to highlight in red all cells equal to or greater than $20,000.
I haven’t explained the “how to” for any of the above, but if you follow the links you will be taken to pages with step-by-step instructions for each task.
And just on that, if you ever want to do something in Excel, but can’t figure out how – just google it. This is especially so if you are looking for a particularly gnarly formula like: “Only sum the values in Column A if the text ‘Red Apples’ appears next to the value in Column B” or “highlight all dates that are less than 30 days away from today’s date”.
The best way to learn how to use Excel is to jump in and start creating. Be careful though, it can turn out to be a bit of a time warp once you get started. If you or your firm don’t have the Microsoft Office suite then Google Sheets can be a good alternative solution.
For advice on financial matters call DS Family Law on 08 9486 1766 or visit our Property Settlement page.
The above information is general in nature and is not specific advice for your situation. If you have questions about how the information contained this article may apply to your situation, you must seek independent legal advice.