During the last two SEC Audits I “enjoyed” with my managers, the SEC Auditors requested a spreadsheet containing the “value of each client’s account at end of period that was used for purposes of calculating its advisory fee for the most recent billing period.” How do you retrieve this information in a spreadsheet?
The bad news
The bad news is that you cannot simply re-create the last bills because the numbers in PortfolioCenter will most likely have changed. It is very common to have new data days and months after the fact adjust the previous quarter end.
The good news
The good news is that you can easily meet this request with a little extra planning each billing cycle. Here are the main options:
Option 1: Archive a backup of your PortfolioCenter database with your bills. On the downside, this option is the most labor intensive. But, on the upside, it gives you a rock-solid “alibi” to justify your fee calculations. By restoring the backup, you can actually re-create the bills.
Option 2: Give the auditors a PDF. Typically, all the information the auditors require is on the client bills produced by PortfolioCenter. If you don’t care about making the auditor’s task easy (e.g. you put them in a closest without heat or air conditioning), you can hand them a PDF archive and make them do the legwork. This option assumes you have formatted your invoices properly and archived a PDF of the final invoices.
Option 3: Archive a customized Portfolio Summary Report with your invoices.
How to create a summary report
To implement option 3:
- Create a Smart Set that captures your bills. Depending on your setup, you could use any of several Smart Sets to meet this goal. My favorite is to create a set of every account and group that has a billing specification which looks like this:
- Now customize your report: Open PortfolioCenter, Global Reports, Performance Reports, Portfolio Summary
- Change the settings of the Portfolio Summary report to include the Value and the Billing Specification and save it.
After your bills are final,
- Print a copy of your bills to a single PDF.
- Run this modified Portfolio Summary report on your billing set.
- Save it to PDF and then save it to CSV.
- Archive the two PDFs and the CSV with the PDF of your invoices.
- Done! You now have everything you need in one place.
Just remember to save these reports when you finalize your bills to ensure the data you save matches the data you used for billing.
Photo used here under Flickr Creative Commons.