The model that I'm describing below is for the allocation part of a much larger system. The concerns about collecting data on Contributions and other capital activity are addressed in another part of the application. Eventually after much review this data is merged together with portfolio income and expense activity that comes from another system into an integrated chart of accounts which is used for investor reporting. The key here is that the data must be aggregated over multiple periods, line items, and investors depending upon the reporting requirements. So the structure of the investor allocations drives the performance of the reporting system.
Funds have Investors, Chart of Accounts, Income Periods, and Line Item Values.
Income Periods are sequential and continuous by number starting at 0 and counting up. A period ending date is used to relate the period to the calendar time. Income Periods are the periods during which the fund's partnership agreement allows capital in or out, or marks its investments for purposes of calculating performance. Generally is monthly but it can be for whatever period the partners desire. Never daily (too expensive). You only need to calculate values for the partnership when capital can be exchanged. The values in between are irrelevant hence we mark time by Income Periods rather than a regular date. Income Periods may be open or closed but they may only be closed in sequence. No changes may be made to the related data of a closed period.
A Chart of Accounts is a hierarchy of Line Items such that any item is the sum of itself and all of its child items. The top level Line Item would contains an Investor's capital balance for the income period. The child line items show the details of the changes. This concept is key for how the accountants think about the relationships between the line items.
Line Items have a name and a specific allocation method that determines how the amount of the item is allocated to investors. Note that the allocation process is iterative and quite complex as many allocations depend upon the results of other allocations. Every allocation must store its results in the Investor Allocation table with a corresponding link to a Line Item Value and nowhere else. Some methods start with a value in the Line Item Value table, others calculate Investors' amount and put an aggregate in the Line Item Value, and others simply move data from the capital activity system into the appropriate Investor Allocation and Line Item Value.
Line Item Values hold the total amount of a Line Item for an Income Period that is to be allocated to investors. Note that a Line Item's allocation method may allocate to the investors by taking the total amount and distributing it among the investors, or the method may calculate each investor's amount and then put the sum in the total amount (for example certain fees calculated on each investor's balance, or the capital activity that is inserted from the capital activity tracking application). Line Item Values may only exist for Line Items that are in the Fund's Chart of Accounts. There is a chicken and the egg problem here as sometimes the Line Items Values is known before the allocations and sometimes after depending upon the allocation method. Note that the Line Item Values give the Fund's aggregate capital, income, and expenses without aggregating at the investor level. This can be important for reporting.
Investor Allocations hold the amount that a particular Investor received of the total amount in the Line Item Value. The key requirement here that the previous proposals have not addressed is that an Investor can receive one and only one allocation for a Line Item Value. So the primary key #InvestorId, #LineItemValueId, #Amount (id from a knot) would allow multiple amounts per investor from a Line Item Value which is
not allowed. My current solution is #InvestorId, #LineItemValueId, Amount attribute. But there appears this construct is not a valid form in Anchor Modeling. An investor is not required to receive an allocation for each Line Item Value and many times will not depending upon how an allocation method works.
Investors contain the Name of the investor and describe the legal components of the investor's agreement with the fund. Allocation methods use these values and other fund level settings to determine allocations between investors.
So the relations would be:
Fund 1-m Investor
Fund 1-m Income Period
Fund 1-m Line Item Value
Fund 1-1 Chart of Accounts
Chart of Accounts 1-m Line Item
Line Item 1-m Child Line Item (assume a hierarchy is enforced)
Line Item 1-m Line Item Value
Income Period 1-m Line Item Value
Line Item Value 1-m Investor Allocation
Investor 1-m Investor Allocation
Hope this explanation helps.
