CiviAccounts

[Eileen]

Hi all,

I just want to draw your attention to this since it may not get the attention it deserves snaffled away on the WIKI

http://wiki.civicrm.org/confluence/display/CRM/Finance+and+Accounting?focusedCommentId=27230459#comment-27230459

[Andrew Perry]

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!

[Andrew Perry]

Shawn Duncan wrote:

Two thoughts:

Another way to say with words what I'm trying to say with my diagram is that we should first focus and agree on an abstract interface design to civi monetary data - what concepts should we model. Then we'll know how to structure the implementation.

Also, I was clearly coming from the cash basis perspective - the result of 20 years of non-profit management with orgs that universally used cash basis accounting.  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!

- Shawn

One of the challenges I ran into (like a brick wall) is that because we are wanting to model Income/Expenditure/Asset/Liability concepts for the purposes of integrating with accounting systems, it is necessary to dig into those accounting concepts to a degree. eg The fact that for the purposes of exporting transactions to double-entry accounting software:

(a) an item of Income must have a corresponding increase in an Asset (either money in the bank or Accounts Receivable under an accrual accounting system)

(b) under a cash accounting system only the parts of contributions that have been paid will be recorded as Income, so there are no official "Accounts Receivable" (although the unpaid parts of contributions still need to be recorded to enable collection activities).

As a result, in developing the changes to the data schema in 3.2, we needed to try and make the schema as flexible as possible to accommodate the anticipated data requirements of accounting software, based on our understanding of accounting principles.

In terms of Cash vs Accrual, I think a configuration option within CiviCRM so that users can choose whether Income should be recorded when a purchase is made or the account paid would be helpful.  Even if you are using the Cash method, storing an "Account Receivable" amount when a contribution is "purchased" but not paid will aid in reporting within CiviCRM, even if those amounts are ignored in the data feed to the accounting package.

From the API side, what would now be really helpful is to draw on the experience of Sarmeesha and the dharmatech team, with their work around Quickbooks, to see what a data feed into a package like Quickbooks should look like (and to what extent it differs based on whether the organisation is using Cash or Accural Accounting).  This would then help with Shawn's first point.

Regards

Andrew

[Andrew Perry]

Eileen McNaughton wrote:

“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

[Eileen]

The other area that will be problematic is GST/VAT / Sales tax - at the data level we probably need to at least include a field to hold this amount

[Andrew Perry]

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

[Eileen]

OK, So just reading what Father Shawn said

Code: [Select]

Also, I was clearly coming from the cash basis perspective - the result of 20 years of non-profit management with orgs that universally used cash basis accounting
Can we flesh out what organisations using a cash perspective require - my take on it is that their external system needs to access:

1) all details of all invoices - but only the paid ones (e.g breakdown of items on it against accounting codes / who). Either in detail or aggregate. (They may not refer to them as invoices but as a set of one or more grouped transactions)
2) details of monetary transactions - probably mostly for reconciliations

I'm not sure what else? Presumably they can query our API which will return this for external purposes & the reconciliation interface is not specifc to accounting type?

[SarahG (FountainTribe)]

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.

[Eileen]

Cool - so, that's not specific to cash-basis but basically it is a report based on whatever we define as an invoice (sorry I haven't had time to chase up the DB side of this @ all this week)

[FatherShawn]

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?

[Eileen]

Hi Father Shawn,

Although we can't prevent Andrew from talking in accountant-ese the focus at this point is not on building account types into the system and any discussion of them should be seen in the context of 'are we building the system in a way that would not close the doors on any possible future development in this area'

The focus is definitely on representing payments, invoices, invoice line-items, refunds, part payments

[SarahG (FountainTribe)]

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.   

[Eileen]

I think the scholarship would show as a negative invoice of $1000 which would reduce the overall amount owing

[SarahG (FountainTribe)]

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

[Eileen]

Hmm - good point. I have been trying to nut through something slightly similar - I want to get Andrew's thoughts on that.

Andrew, once again this is the idea that the status of a particular participant record is depending on it being paid - and in this case that is determined across more than one invoice