NumbersFinance-580

Q.We use PortfolioCenter billing to compute our management fees and the Management Fee Export to upload our fees to Schwab.  That process is fairly quick and painless.  But I spend hours each quarter trying to ensure that there is sufficient cash in our accounts to deduct management fees.  Do you know of any way PortfolioCenter can help with this verification process?  Checking manually is very time consuming.

A. Good question.  PortfolioCenter can help verify available cash for management fees.  All it takes is a little spreadsheet savvy and familiarity with the PortfolioCenter exports.

First, verify that your bills are accurate:  post and reconcile all data, ensure you have complete prices, and that correct billings specs and billing account numbers are assigned to all accounts and groups.

Then use the Update History button on the Billing tab to calculate all management fees.

Finally, review the results for errors or changes from the previous quarter.  I assume these tasks are part of your normal quarterly billing checklist.

When you’re satisfied your fees are accurate, here’s how PortfolioCenter can help verify each account has sufficient cash to deduct management fees:

1.  If you use a broker interface with a direct management fee export (e.g. Schwab or TD Ameritrade), use the Export Management Fees tool to create a CSV file of your management fees.

If your broker interface does not have a direct management fee export, you’ll need to export the Global Billing Summary report to CSV, but make sure you understand its limitations.

2.  Use the Portfolio Export in the Export Wizard to export the total cash in each account.  You’ll probably need to edit the columns on the Portfolio Export to make it more useful to the task at hand.  For example, I formatted mine like this:

TotalCashExport

which produced the following CSF file:

CashExport

To compare the 2 CSV files, open both CSV files in a same spreadsheet and sort the data by account number.

I put the Portfolio Export data in 1 tab of a spreadsheet and the Management export data in another tab of the same spreadsheet.  Then I used LOOKUP  to add the Total Cash for each account to the management fee data tab.  With a little conditional formatting, the problems are highlighted.  The results look like this:

CashCompare

In my example above, the data in Columns A-D were produced by the Management Fee Export.  The data is Column E is pulled from the PortfolioExport Tab by a LOOKUP command.  Column F is Column E minus Column B.

Contact me for a copy of this spreadsheet

 

Photo taken by Leonid Mamchenkov and used here under Flickr Creative Commons.