How I Prepare for Quarterly Account Reviews
What do farmers and advisors have in common?
They both plant and they both harvest.
In the financial advisory business, we plant seeds (market our services) and harvest (gain new clients). There is one additional item I’d like to add. We also service our clients. It is this area that I’d like to discuss in this posting.
I am preparing to conduct my third quarter account reviews which will occur during the first two weeks of October. At the same time, I am putting in place a system to help keep track of each client’s financial planning renewal period. That’s a lot to manage for a one-and-a-half person shop (the half is my wife who works with me part time). In preparation for the portfolio reviews, and to help me stay abreast of each client’s allocation (by account and in aggregate), I have created another spreadsheet. Let’s look at the three parts of the spreadsheet: the input, the calculation, and the output.
The Output
There are several pages which include the broad allocation for all accounts, the broad allocation for the individual accounts, an equity analysis, a fixed income analysis, a look at the alternative investments, a list of each investment, which includes their gain/loss and percentage of the account, and a page for each account which shows how far the portfolio has drifted from the model.
With this, I can easily determine what changes need to be made by sub-allocation. In other words, how much to add or subtract from Large Value, Intermediate Term Bonds, etc. It is an extremely helpful tool.
The Input
The input contains the client’s name, the date, a list of accounts, and the model portfolio assigned to each. I export a particular page with the account holdings from the Web site of my custodian, TradePMR, to Excel and paste it into my spreadsheet. I also export a list of every mutual fund, ETF, and individual stock in my practice from Morningstar’s Office Edition to Excel and paste it into the spreadsheet.
Let’s move on to the calculation section.
The Calculation
In the beginning, I would manually categorize each holding in each account, which was a painstakingly arduous process. I now use Excel’s VLookUp formula to look up the ticker symbol and retrieve the broad and subcategory for each holding.
Oh, I also incorporate the clients’ outside accounts such as their 401(k)s, etc.
Be cautious when using VLookup() in Excel, particularly when your values (ticker symbols) are critical in calculating the output (asset allocation).
The syntax of the formula is VLookup( value, table_array, index_number, not_exact_match ). For the optional parameter “not_exact_match,” be 100% certain that you specify FALSE. If you do not specify anything and leave the parameter blank, VLookup() will return an approximate match to your input value (your ticker symbol).
Returning the approximate match instead of an exact match can be disastrous to your rebalancing sheet!
Also, you can embed VLookup() inside the ISNA() function to provide better alerts when the input ticker symbol is not found (e.g. a security transferred via ACAT that didn’t make it into your Excel page of all securities). Conditional formatting works very well in this case to highlight cells by a certain color to easily identify errors.
Hi Bill,
Thanks for the comments. The VLookup works well. I did find that the ticker symbols must be in alpha order or it will not work consistently. I’ll check out the ISAN() formula. I’ve never used it.
Hi Mike,
You have mentioned in your blog that you use Crystal Ball and
Excel to do your client planning. Have you found a template for
Crystal Ball or have you created your own input format to get the
reports you provide? From your comments I have just starting looking
into that software. Is there a source you recommend to get up to speed
on that the software can do for advisors?
Thanks,
Chuck