CiviCRM Community Forums (archive)

There has recently been an informal group formed to help progress CiviAccounts as a priority, in response to this blog post: http://civicrm.org/blogs/sarahgladstone/join-team-civiaccounts

There has been a bunch of emails circulating and JoeMurray has sensibly suggested we move that discussion here.

The good news is there is a bunch of detailed information about accounting concepts in the email thread that need not be replicated here!  If it will help, then it can be added to the wiki at some point or added into the discussion here.

The best place to start if you'd like to contribute to this discussion is reading and contributing to the wiki page above or its sub-pages.

At present, we are looking to break the project up into the following general sections and perhaps form teams around them:

1 UI/workflow (getting transaction data into CiviCRM - eg part payments/payment of multiple contributions at once/multiple contributions in one invoice/3rd party payments of a contribution/refunds/credits)

2 Data structure (storing data in a way that facilitates 1 & 3)

3 API (getting transaction data out of CiviCRM - integration with Quickbooks/Xero/MYOB etc)

My Community Builders Australia colleagues, Rui and Henare, and I, worked on item 2 at the beginning of the year, so that there is a new data structure for financial transactions in CiviCRM 3.2, which is discussed here: http://wiki.civicrm.org/confluence/display/CRM/Phase+1+Implementation+-+Data+Structure+Changes

It would be great to get feedback on whether people see any issues with the new data schema in CiviCRM 3.2, or if clarification is required.  A diagram would no doubt help clarify how the tables fit together, as well as explaining how the data could be stored for each of the key use cases - but I probably won't be able to do that within the next 24 hours.

I think that the schema in 3.2 will achieve our shared objectives from a data storage perspective, in a way that works in with the previous data schema as much as possible.  The uncertainty as I see it is more:

(a) Whether an entry should be recorded in the civicrm_financial_trxn table for each contribution/membership/event participant order/purchase, together with the applicable Income Account (based on Contribution Type at the time of purchase), or whether only payments into an Asset Account (eg bank account/unbanked money/credit card processor etc) are stored there.

If only payments and Asset Account transactions are stored in civicrm_financial_trxn, then the accounting system integration functions will need to go searching through contributions to find which ones have been paid, using the civicrm_entity_financial_trxn, find refunds (wherever stored), and look up what is the Contribution Type and corresponding accounting_code applicable to each Contribution/Refund at the time of export.  It may be that this is in practice how it would work anyway, for cash accounting entities, as their focus would be importing payments into their accounting systems and would only at that stage need to include which Income Accounts should have the corresponding credit applied, based on the Contribution Types included in the payment

(b) Irrespective of (a), whether an entry should be recorded in the civicrm_financial_trxn table for "pay later" transactions, against an Accounts Receivable Asset Account.

For the purposes of integration with an accounting system for entities working on an Accruals basis, I think (b) would be helpful, as the Accounts Receivable entry would be linked to the relevant pending contribution/membership/event participant fee, which would enable the corresponding accounting_code to be found for each Income item even if the Income itself is not stored in the civicrm_financial_trxn table.  For these organisations the system would need to find all the relevant (unexported) Accounts Receivable transactions in civicrm_financial_trxn, find the corresponding Contributions and their Contribution Type/account_no to record as Income, then find all unexported payments received and ensure that to the extent a payment is against a Contribution already included in an Accounts Receivable entry, that the Contribution is not counted as Income again.  In this use case it can be seen why it would be easier to just record the income in civicrm_financial_trxn at the time of purchase, regardless of whether payment is made then or later, so it is easier to export to the relevant Income Accounts without having to refer back to the Contributions, their Contribution Type and whether Payments have been made against them, at the time of export.

(c) whether an entry should be recorded in the civicrm_financial_trxn table as "Unbanked Money", when a payment received is entered into CiviCRM against a contact, so you can audit where the funds from all payments are sitting.

This would facilitate the two step process common in many NFP and business offices, of firstly recording the cash/cheques received against the relevant constituents, then secondly, banking the cash/cheques as a batch in another transaction.

The civicrm_financial_trxn and civicrm_entity_financial_trxn tables as outlined at the above wiki page, does enable a batch of transactions between constituents and the org, to be recorded as a single deposit transaction between the org and a bank (ie being a Debit to a "Bank Account" from the "Unbanked Money" account), which I think is one of the key things Joe, Paul and others are looking for.

Thanks to Sarah for stirring the pot and getting us all together to work on this! I look forward to your comments!

Eileen McNaughton wrote:

Quote

“Shawn Duncan: I'm wondering if there is a way, perhaps via a poll on civicrm.org, to discover what the mixture is in our existing customer base.  I'm thinking that we should implement the most widely used model first - even if it's the one unfamiliar to me!"

So, I think the Make-it-Happen initiatives may be the poll. Once we have a plan for how to set up a data structure to implement our short term goals without compromising future goals we need to flesh out the UI & components and then we have to look for sponsorship. I’m expecting people to vote with their $$$ for which solution/s they want first – although some things are required for both accounting methods – e.g. most of the use cases Sarah wrote up so we may well wind up running a fleet of campaigns like: 

 -         part-payments through the UI

 -         API for integration

 -         Reports & UI functionality relevant to cash accounting

 -         Reports and UI functionality relevant to accrual accounting

 -         Quickbooks integration

 -         Bank Reconciliations
 

There will be some cross-over and some steps will rely on others so we’ll have to be clever about it Seed funding for specific initiatives will probably be quite influential

Eileen

I think the UI will essentially be the same regardless of whether an org uses cash or accrual accounting, it is just the "engine" that will operate differently in terms of when an amount is recognised as Income and whether Accounts Receivable are included in the data feed for accounting system integration.

Either way you'll still need to be tracking which part of a contribution is paid or unpaid, using the entity_financial_trxn table to attribute payments, you just won't be recognising the contribution as Income or an Asset (Accounts Receivable) at the time of purchase, but rather the "engine" will wait until the payment is made before reporting the components of that payment as being different types of Income (currently based on the Contribution Type of the Contribution that is full or part paid) and Asset accounts (being the account the payment is "banked" to), eg "Event Income" and "PayPal Account" for an online transaction.

This gives rise to an expense category that will need to be considered - Transaction Fees - as the donor/auditors will expect the receipt to be for the full amount of the donation and for the full amount to be considered as Income, while in CiviCRM it currently seeks to record a Net amount. One option is for Civi to ignore Transaction Fees and leave that up to the accounting package to handle, otherwise the Transaction Fee expense will need to be included in the data feed for the accounting system.

In light of the above, I think we should be building this from the outset on the basis that there will be an $accounting_method variable that will either be "Cash" or "Accrual" and the UI will trigger slightly different actions on recording a "purchase" and processing a payment, depending on that variable.  If the functions to record a purchase and a payment are kept common across item types (eg contributions, events and memberships) then the two sets of logic should be pretty maintainable.

Andrew

Yes, there will need to be a Liability account for that....

So we will have the full suite of account types - Asset (money banked into account), Income (contributions), Expenses (Transaction Fees) and Liabilities (Tax)...

But don't worry folks, that doesn't mean I am suggesting we need a full accounting suite in CiviCRM!

Andrew

Some of the reports that a cash basis non-profit will need:

An Aging report, which can be viewed at: http://www.limowiz.com/detailedagingreport.aspx

It is an essential tool to aid in collecting overdue money. For example: Many people sign up for a paid event like a trip, and all make a initial deposit. 6 months later, the bookkeeper needs to see which of the people that took the trip still owe money.

Yes, there will need to be a Liability account for that....

So we will have the full suite of account types - Asset (money banked into account), Income (contributions), Expenses (Transaction Fees) and Liabilities (Tax)...

But don't worry folks, that doesn't mean I am suggesting we need a full accounting suite in CiviCRM!

Andrew

What are the advantages of maintaining these data structures in Civi rather than building an interface to pass the data that we naturally collect over to the accounting software?

I have a client that often has the following situation:

A parent is billed by the staff for a full tuition obligation, such as  for 3,000. for the school year.

Later on, due to financial hardship, the parent is granted a "scholarship" of 1,000, so the parent now only owes 2,000.

The staff records this in their previous A/C system as a "tuition adjustment" of 1,000. So the statement that is sent to the parent shows the original 3,000 obligation, the adjustment of 1,000. as well as their current balance of 2,000. (Plus any other financial activity for that time period. )   The staff also pulls a  report that shows how many scholarships were given out for a given time frame.


How can this be handled by the new CiviAccounts features?   One idea I had is this could be treated the same way as a partial refund, but use a different "ContributionType" than regular refunds.   

How would the new negative invoice be related to the original tuition invoice?