Distribution Characterization on Reports

[elliots]

Mark,

Did a custom report showing all distributions (between). All of my investments are in brokerage accounts with default cash investments and transactions are downloaded. All cash dividends (which remain in the account) are show in the Div Dist (OK), Withdrawn Dist (?), and Dist Dist (?) columns and nothing shows in the Retained Dist column. Further, I have the report set to sub-total by sub-portfolio and all the totals appear to be significantly off.

Elliot

[Mark]

Hi Elliot,

Each line item is displayed relative to that line item. For example, if a stock pays a dividend, and the money goes into the default cash account, the stock line will look like a withdrawn dividend, but at the portfolio level this will look like a retained dividend. The dividend is external to the stock, but not the portfolio. For this reason, the withdrawn/retained columns will usually not add up. The dividend column should add up though.

[elliots]

Mark,

This is all jumbled together so I'll put everything here in one place.

First, I really like the Executive Summary Report, but . . . (You knew there would be a but).

I'm having a problem identifying underlying transactions that feed into some report totals, as well as some definitions.

To start with, I have a sub-port for a client containing only sub-portfolios corresponding to the physical accounts in the real world (probably your standard setup) and a sub-portfolio for fees.

The client mails a check (from non-managed/tracked funds) to one of his accounts. This is recorded as a purchase in the default cash investment of the appropriate sub-portfolio. This is an EXTERNAL cash flow (outside to inside) to the client and is a CONTRIBUTION. One of the investments in the sub-portfolio pays a cash dividend and a purchase transaction is recorded in the default cash investment. This is internal to the sub-portfolio (and the client) and is NOT a contribution. How does FM tell them apart -- it appears that they all show up in the Contribution total on the Executive Summary Report. Same thing on the Withdrawal side -- there seems to be no way to tell an external withdrawal (check mailed out to the client) vs. a purchase within the portfolio from the cash investment. So what we wind up with on the Executive Summary Report is large numbers for Contributions and Withdrawals that the client will not understand. (The net of the 2 of course affects the ending balance).

I ran a Transaction Report at the client sub-portfolio level and turned off all the filters EXCEPT Show Only External Transactions (Cont/Withd). I got no transactions. Yet there was a (large) legitimate (external) contribution during the period. (Again, it's not clear to me how FM can tell the difference between external and internal cash flows unless you are doing all kinds of checking [matching cash purchases with distributions on amount and date?].

My logic is as follows: If we draw a line around all of a client's sub-portfolios under management (which is the client level sub-portfolio), CONTRIBUTIONS are cash and securities going across that line from outside, and WITHDRAWALS are cash and securities going across that line from inside to out (which includes Foreign Tax Paid and Fees, but these could be broken out separately).

There's a line Withdrawn Distributions -- how can I see where that's coming from? On a Transaction Report there's a whole bunch of numbers as Withdrawn Distributions and they seem to be the same as Total Distributions and Distributed Distributions -- I'm guessing it's the net of something, but how can I reconstruct the final answer?

The Pie Chart is a nice addition, but . . . It appears that you've picked the 5 largest slices on whatever allocation model is selected which is a reasonable choice and I can understand the limit of 5 slices based on space available, but it sure would be nice to have more slices and let me pick the slices to show, but I understand that's probably asking too much.

Elliot

[Mark]

Hi Elliot,

You understand correctly, but it sounds like for some reason your transactions are not being identified as I would expect. When Fund Manager determines whether something is internal or external it looks to find a matching transaction (amount and date) in the default cash account. So, if you buy $1000 worth of stock, you'll have a buy of $1000 in the stock, and a sell of $1000 in your default cash account. Both of these will be considered internal. If you have a sell in your cash account, and no other corresponding buy in another investment, then this is considered external. To quickly see everything external, create an Investment Transactions report, and turn on "Include Only Portfolio External Transactions". You do also still need to leave on everything under the "Filter By Transaction" list if you want to see all types of external transactions. If you turn all of those off, you won't see anything. It sounds like that is maybe what happened earlier. Running this report will allow you to identify all Contributions, Withdrawals, and Withdrawn Distributions. Again, it is key that you have matching transactions in your investments and the default cash account, in order for FM to determine that these are internal.

Thanks for the feedback on the pie chart, I understand it would be nicer to allow you to customize this maximum.

[elliots]

Mark,

I still am having problems. I've run the Investment Transaction report at the client level with External Only and all the Transaction types checked. I am seeing transfers between sub-portfolios (NOT external) and admin type junk from Scottrade related to the internal fund sweeps (offsetting buys/sells in the cash account) which should NOT be external. I'm also seeing some securities purchases where the date of the sell in the cash account is offset by settlement date delay. Also, some of the management fee transactions are not showing. Let me have your email and I'll send you the report (and the data files if you'd like).

The point is, if I were the client looking at the Exec Summary report I'd be puzzled by the Contribution and Withdrawal numbers -- they in no way jive with what I'd expect.

Elliot

[Mark]

Hi Elliot,

Yes, transfers between sub-portfolios would show up here. Maybe we need a transaction level property to allow you to specify that it is an internal transaction, and override our auto-determination routine... How would you like to see the determination of internal/external be determined?

As it is today, you must have matching transactions in whatever you specify to be your default cash account. If you're using a sweep account, you may prefer to make that your default cash account, so when money goes out for a purchase, and later goes in from the cash account, everything is matched.

I'll send you an e-mail, so you can reply to that with your report/data files.

[elliots]

Mark,

If you are only looking for offsetting B/S's with the same date in the default cash investment you are also categorizing all cash distributions as external (I guess that you could say, it's as though they mailed the client a check and he re-deposited it. The key thing, for the Exec Summary, is that CONTRIBUTIONS and WITHDRAWALS should be the money crossing that line between the Client's external assets and the managed portfolio, and it should make sense when looking at the report. These are relatively rare events and they could be tagged (manually?) as EXTERNAL for purposes of the report. They are cash/security deposits and withdrawals, fees withdrawn, and foreign taxes paid.

As an example, with the 1 client I'm looking at, the Exec Summary is showing contributions of 180,000 and withdrawals of about 130,000 (the fees are showing, and there's another number for Withdrawn Distributions that I'm still trying to decompose. In actuality the client made a contribution of 40,000 (it seems to be the net of the Cont and Withds shown) -- so he will wonder about those other larger numbers.

I have made the "sweep" account the default cash account. Earlier there were many bookkeeping transactions (offsetting B/S) coming down from Scottrade in the downloads and I left them alone. I probably should delete them (although they would show as internal and not affect the report so maybe it's not worth the trouble) -- they aren't coming down any more.

I've got to leave now, will email stuff and some more Q's later today.

Elliot

[elliots]

Mark,

Let me just add that relatively rare (EXTERNAL) events are subjective. For me, with large (many investments) dividend paying stock portfolios, rare may be different than for smaller accounts with say mutual fund monthly purchase programs. Also, the mutual funds I hold in the brokerage accounts show cash distributions and new purchases rather than reinvested dividends.

ELliot

[Mark]

Hi Elliot,

If you have a cash dividend, shouldn't you also have a deposit into the default cash account on the same day for the same amount? If so, the cash dividend will not be considered external. FM looks for matching deposits into the default cash account for distributions as well as buys/sells. When you import from Scottrade, are you using the default cash account feature in FM? At the bottom of the preview transactions dialog there is an option to "automatically record corresponding entries in default cash account". If you have that option on, every stock buy/sell or dividend should get a matching entry recorded in the default cash account, marking it as internal. Only external deposits/withdrawals from the cash account should not have a matching transaction.

Maybe we need to have an alternate way of determining internal versus external. The idea of the current method was to make it easy on the user, so you don't have to manually tag transactions. Maybe it would be good to add an option to control how FM determines internal/external, with 3 choices, like:

1) Automatic
2) Automatic unless specified
3) Internal unless specified otherwise

and then every transaction would have a new property giving you these choices:

1) <auto-determined>
2) Internal
3) External

Basically, today we use option 1 & 1. This would give the user more control, but also add complexity that will add some confusion, which is never good. I'll have to give this some thought. If you have any ideas/thoughts on this, please let me know.

[elliots]

Mark,

Another aside, what's internal and external depends on context. Since I do all my reporting at the client level, transfers between client accounts are internal. If someone is reporting at the account level, the are external. This of course complicates the transaction identification issue. Again, from my perspective it would be easy to manually mark (the rare) external transactions.

Elliot

[Mark]

Hi Elliot,

Good point... This adds to the reasons for making it more user-adjustable, since not everyone may want the same rules.