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.