Most of the action is over at: Accounting integration improvements

Prior discussion and development of proposals for improving support for financial tracking and accounting integration in CiviCRM is below.

General discussion of questions and issue around CiviAccounts is happening on the forum over at: http://forum.civicrm.org/index.php/topic,12264.0.html

Remembering that this is a wiki page with multiple contributors the text may not always seem to flow (sourire)

This page has a lot of the use cases that were originally anticipated would be put here: CiviAccounts Functional Requirements (Should we move them all there, noting that authorship of different bits will then be incorrectly attributed, and then separately create a CiviAccounts Civi-Make-it-Happen Campaign page once the different use cases to be targeted are clearer on the CiviAccounts Functional Requirements page?)

State of Play

CiviCRM 3.2 includes a range of Data Schema changes as a result of Phase 1, discussed in the Completed Phases section below.

We are now working on a range of additional use cases to discuss how these can best be accommodated in the new Data Schema, or what additions/changes may be desirable, to facilitate integration with an accounting package for the majority of users of CiviCRM (whether they use a Cash or Accruals method of accounting).

Completed Phases

Phase 1 - Separating payments from obligations/contributions

Phase 1 of the "CiviAccounts Project" was to separate the recording of payments from obligations/contributions, so that there can be one payment for multiple obligations/contributions and multiple payments for one obligation/contribution (ie partial payments for events, memberships, pledges and donations).

This phase involved extending the existing civicrm_financial_trxn table.  Changes made that were included in CiviCRM 3.2 are in the following page.

Phase 1 Implementation - Data Structure Changes

Phase 2 & Phase 3 Implementation - Data Structure Changes

Future Phases

Phase 2 - User interface/experience

Phase 2 of the "CiviAccounts Project" is to develop the user experience for separating payments from contributions/obligations to pay, using the work done on the data schema in Phase 1, with amendments to the data structure as required.

The following page was created for much of this detail (Proposed Phase 2 Implementation - User experience), but for now, the more high level thoughts and use cases are set out below.

Phase 3 - CiviAccounts API

Once CiviCRM is storing data consistently, it is then possible to build the API.  It is important, however, to ensure that the requirements for the API are understood at the outset, so as to ensure the data entered through the UI is stored appropriately.  It is proposed that the requirements for the API be developed here: Proposed Phase 3 Implementation - API

Priorities for the planned CiviAccounts Civi-Make-it-Happen campaign

Overview goal: All features that are expected parts of a standard Accounts/Receivable ( A/R ) system.

These items are ordered by order of importance: 

1) Get many-to-many transactions to obligations working. ( Both in the database as well as the UI. Many-to-many includes the following use cases:  

 - One check/credit card transaction that needs to be allocated to many obligations ( such as event fees, member dues, donations, etc ) (eg Staff/bookkeeper gets a single check, that can be applied to multiple pledge payments, event fees or membership obligations.  The bookkeeper should be able to change the allocation if needed. ( For example, 50% of the check is originally applied to an event fee. Later on, it isrefunded for the event and instead applied to a membership fee. )

- One obligation that will be fufilled by many transactions. Also known as partial payments. So someone could make a deposit for an expensive event or member dues, and pay it off over time. 

- Ideally, an administrator would generate an "invoice" for a website member.  This invoice would be a comprised of one or more pending contributions.  The administrator would generate the invoice in PDF or HTML format so that it could be emailed to the website member (there are already custom mail merge tokens that can be used in CiviCRM 3.1 or better to produce statements. A sample statement ( as a PDF ) is attached to this wiki document)

 

Detailed Use cases that need to be supported:  

- Within the staff area of CiviCRM, a staff person ( such as the bookkeeper ) who is sitting with a stack of checks, has a screen to record checks, and allocate various amounts to different obligations. For example: one check for $200 could be allocated as $50 for an event deposit ( the event total price may be $300) , another $50 could be a voluntary donation, and the last $100 is a partial payment for their annual membership dues. The checks would be from a variety of contacts. The bookkeeper must be able to handle this task without being forced to jump around to various contact screens. ( The headache in the CiviCRM version 3.2 is that the bookkeeper must first bring up the contact summery screen for the right contact, then click the "pledge" tab and find the right link for "Record payment." Then they must bring up the contact record for the next contact, and repeat these steps. If recording 50 checks from 50 different contacts, this is very time-consuming. ) 

- Re-allocation of previously applied amounts. Continuing on the previous use-case, the person cancels their participation on the event, and now wants the $50 ( originally for the event deposit ) reapplied towards their member dues. 

- A staff member should be able to record a returned payment, such as when someone withdraws their child from school or cancels an event registration. ( The staff would need to issue a check or credit card refund outside of CiviCRM, since CiviCRM is not an Accounts/Payable system.  ) . This payment would be a separate record in the CiviCRM database (as the original payment is unchanged). Another example here is a check that bounced. In order to cater for invoice based accounting the original contribution record should also remain but a credit contribution should be created & linked with the event (this provides full tracking of payments too). The credit may be less than the contribution (partial refund). Organisations with different accounting requirements may still prefer just to cancel contributions and it may be that a separate drupal permission for 'cancel contributions with attached payments' is required to allow organisations to enforce accounting rules if they wish.

- The staff person should be able to batch-register people for a paid event or membership, or donations or pledge, which results in a unpaid financial obligation for all those contacts. ( Since CiviCRM already supports batch actions for event registrations and membership creation, those existing screens can be enhanced. ) 

- The contact should be able to see all financial activity, unpaid obligations, and scheduled payments on their contact dashboard.  So if a person ( such as a member ) logins into the website,they can see all their financial activity and current unpaid balance.

- The contact should be able to make a payment online that covers multiple obligations ( either in part or in full ). Such as they make a deposit for school tuition, plus a fundraising dinner, and part of their member dues in a single credit card transaction.  A common situation with parents paying for schools is they pay an initial deposit before the school year starts, then make several large payments at the end of each semester.   

- The staff can email or print a statement that shows a summery of all obligations, money received, open balance, and amount due right now. ( i.e. the statement that is attached to this wiki article that uses my custom mail merge tokens. )

- When the staff creates any type of  financial obligation for a contact, they can set up the planned schedule ( such as a pledge of $2400, with payment expected every month for 12 months.    ( This already exists in the pledging area of CiviCRM)  

- Plus all the use-cases detailed in the partial payment child document attached to this wiki page. 

2) Better tax-handling. Currently each contribution type is either tax-deductable or not. In the world of nonprofits, this is not sufficient.  Some events of $100 may have a fair market value of $30, so only $70 is tax-deductable. An annual membership of $2000 may have a fair market value of $500.   

Use cases that need to be supported:

- An event is setup with the fees being $100 for an adult, $50 for a child.  The fair market value of the adult meal is $30, so only $70 is deductible. The fair market value of the child's meal is $20, so only $30 is deductible.  

- An event is set up with a complex price set. Each item in the price set has a different fair market value. 

- Different membership types and dues are set up, with each type having a different fair-market value.  ( For example, some membership type may include use of the swimming pool and therefore has a higher market value than a lower membership level. ) 

- The staff should be able to email or print a year-end statement that shows all their financial activity for that calendar year, with tax-deductible amounts split out into its own section. ( This is needed by the donor in order to claim those amounts on their U.S. tax returns ) 

Plus all the use-cases described in the fair-market value child document attached to this wiki page. 

3) Make it easier to reconcile income against deposits. ( one bank deposit may include many smaller checks. ) 

- NEED DETAILS

4) Simplify and automate exporting relevant data to QuickBooks and other widely used general ledger programs. 

5) Create additional financial reports that are part of typical Accounts/Receivable (A/R) systems.

- an aging report

- a report that shows money that is expected, but not yet received, grouped by contribution type. 

6) Third-party payments, shared obligations

use-case 1 :  A parent registers their 2 children for school ( a CiviEvent ) and elects to pay the tuition obligation with an initial deposit, followed by 12 monthly payments. The parent pays the initial deposit and first 3 monthly payments as expected.  Then for month 4 and 5, the staff gets a surprise check from the grandparents to cover the tuition payment for month 4 and 5.   The grandparent expects to get a tax-letter and receipt for their payments. However, the obligation for the parent would be reduced. 

I know that we serve organizations in all kinds of tax-jurisdictions. However, in the United States the grand-parent is not eligible for a "tax-letter" as donations that benefit a single individual are not in fact donations:  

In this case the staff recording the check from the grandparents should have the option to indicate if this particular payment is tax-deductible or not. This requirement overlaps a bit with the above part 2 titled "Better tax handling"

use-case 2:  A person signs up for membership that is an annual fee of $3600.  They elect to pay this obligation in monthly payments of $300. They pay the first 6 months of payments as expected. Then their parents mail a check to cover 1 or more months. The parents expect a tax-receipt for the check they sent. However, the membership obligation for their adult child would be reduced.  

use-case 3: A divorced mother registers her 2 children for school.  She will be responsible for 50% of the tuition, and the children's father will be responsible for the other 50% of the tuition. 

Case for an API approach

The next section is written on the assumption that rather than focussing on writing the integration the focus will be on developing an API interface to allow multiple integrations which may or may not be built into the core. The assumption is that CiviCRM itself may change the way it stores it's data (e.g. to accomodate multiple payments against one event) but the changes will not be visible to the integration.

The API should return results both as a multi-dimensional array and as XML.

Accounting data concepts

If CiviCRM is able to make data available that maps to the key Accounting data concepts then this allows interested parties to develop for their favoured accounting package. I am not envisaging that CiviCRM would provide 'double entry accounting of any sort' - that is the role of an accounting system. It should provide relevant data instead. The data concepts that are important in an accounting system are
* Debtors & creditors
* Invoices & credit notes
* Payments
* Credit matching

Accounting Vocabulary

The word "invoice" can mean different things in different contexts or audiences. 

To the person who owes money to a nonprofit, they may call the piece of paper they get in their mailbox ( or via email inbox  ) once a month an "invoice". If it is for a single new amount owing, it is indeed an invoice. However, documents that summarize all financial activities for the previous month that are generally sent out are technically a "statement" or "statement of account". They  list all their financial obligations, transactions, and a calculated current total balance. 

To the bookeeper at the nonprofit, an "invoice" means one unique financial obligation toward the nonprofit (more details in 2) Invoices / Credit Notes section below).  One person may have multiple invoices: $50 for an event, $1200 for a pledge, $2400 for a different pledge, and $3000 for annual member dues. This means that individual has a total of $6650 of obligations to the non-profit. 

Accrual basis accounting = Recording income as accounts receivable when earned and recording debts as accounts payable when they are incurred.  In this system, if someone registers for an event and chooses "pay later", this obligation would need to be shown in the general ledger. When the check/cash/credit card is received, this "adjustment" transaction would need be sent to the general ledger. If the person cancels their event participation in the event, then another adjustment transaction would need to be sent to the general ledger.  

Cash basis accounting  Method of accounting in which expenses are recorded when cash is paid and revenue is recorded when cash is received.  In this case, if a person registers for an event and chooses "pay later", nothing would be shown on the general ledger. When the check/cash/credit card is received, then the information would show on the general ledger.   If the person cancels their event participation before they have paid, then nothing needs to be sent to the general ledger. As this example shows, cash basis is generally simpler for nonprofits.  

For CiviCRM to know which transactions need to be sent to the accounting package/general ledger program, CiviCRM will need to know if the organization is using accrual basis or cash basis accounting.

1) Debtors and Creditors.

These are essentially just contact records filtered according to whether or not they have engaged in a contribution or outgoing. The exisitng getContact API may be sufficient but the following are likely to be required (potentially as search fields)

2) Invoices / Credit notes -

An invoice is issued to indicate a payment is or will be due from the perspective of the issuer. Payment of invoices may occur at the time the invoice is generated (like an on-line donation) or it might take place in installments over years (for a pledge that has been invoiced). Accounting systems can be set up so that payments of certain types can only be entered if they are applied to invoices. Generally this is the case when the accounting system is also being used to track client accounts for those kinds of payments. In some of these approaches it can be appropriate to create invoices when a payment is received in order to allow the financial tracking to work for that class of funds. Pending invoices will not have payments matched against them yet, even if a batch of post-dated cheques may be on hand since they won't be entered until the appropriate period. A recurring contribution can be set up a series of invoices (appropriate when there is no ongoing commitment (ie no pledge) and it could be stopped at any time) or as a series of payments against a single invoice (eg monthly payments to pay for an annual membership or a multi-period pledge). Depending on the accounting approach adopted, pledges can be accounted for either as assets in the form of future receivables or booked as income in the form of current receivables in the period in which they are received.

A credit note is a decision to cancel some or all of a commitment. At the moment CiviCRM doesn't deal with these very well as there is no good way of recording a partial credit/ refund. From an accrual method of accounting point of view the original invoice should stand but a credit note (of the same or a lessor amount) should be raised. Currently CiviCRM just shows the contributions as cancelled. This should be 'opaque' to the API and if CiviCRM changes it should be invisible to the accounting system.

The accounting system may wish to engage with individual invoices or aggregate amounts. Contributions already have a field for accounting code but I think this is insufficient as I have always used this plus two additional fields in the accounting systems I have used - e.g. I can categorise transaction in my accounts system by account code, project code & job code giving me a range of reporting options. Xero refers to these extra custom fields as 'tracking fields'. I would suggest that at least two should be part of the information returned from and API call. In addition, line items in transactions, such as elements in price sets, need to have full accounting information available since not all line elements in a transaction are necessarily the same account (see 5) Enabling Contribution Type to be specified at line item level section below).

Invoices should have at least 3 available reference fields - one for the processor, one for any banking reference and one for the accounting system.

The Xero API page is useful for considering what sort of data might be in an invoices API. Note that they differentiate between incoming and outgoing using a single field 'ACCPAY' vs 'ACCREC' which is a good option for allowing future extensibility.

http://blog.xero.com/developer/api/Invoices/

Note that a multidimensional array would include line items.

3) Incoming & Outgoing payments

One or more payment might be matched against any invoice. The payment/s could be negative or positive. (e.g. someone overpays and gets a refund, aka a credit note). A payment could cover more than one invoice.

I actually believe the most logical way to represent payments and invoices in CiviCRM is to move all actual payment data to the civicrm_financial_txn table - not just credit cards. There is already a table which could be one-to-many linking this with the contributions table. Conceptually the contributions table would then be 'invoices' and the financial_txn table would be payments. But I recognise the change may be unrealistic. On the other hand, this change would lay the groundwork for multiple payments against one event & partial refunds.

4) Credit matching information (ie. a table that connects the two)

Credit matching is the process of linking commitments to pay with actual payments. It is a fundamental concept in accounting systems and a separate table would hold the linkages. This may not be a separate API. Xero has one payments API:http://blog.xero.com/developer/api/Payments/

5) Enabling Contribution Type to be specified at line item level

For accounting purposes it is often important to be able to indicate what monies are received for. CiviCRM supports an arbitrary number of Contribution Types and the definition of an Accounting Code for each one. It would be beneficial to be able to indicate the Contribution Type for each line item, rather than at Contribution Page level. This will involve changing the data schema, a number of Administration pages for CiviContribute, CiviEvent, and CiviMembership, and several reports.

The RNAO is intending to work on implementing this feature. See Enabling Contribution Type to be specified at line item level to assist in developing the specification.

6) Changing and Cancelling Financial Transactions

This is a more focused version of items 2 and 3 above.

CiviCRM should have payment processors that support refunds or reversals of charges in order to automate changes and cancellations of charges and ensure the financial information is accurate in CiviCRM. This would allow higher level functionality to be developed that will be very useful for administrators.

CiviCRM should be changed so that changes and 'deletions' of payments do not change the original records but post auditable 'difference' transactions that adjust the original transaction appropriately. Reversing a charge, as when a prepaid event registrant can no longer attend, should result in a second refund transaction being posted. In some cases, for example, registering for events with pricing for optional sub-events like luncheons, banquets, or per-session fees, it is very time-consuming to administer changes and cancellations of registrations. After a change, 'difference' transactions would be posted into CiviCRM that included line items that reversed previous charges no longer appropriate (eg refunding cost of luncheon no longer desired) and that added charges for new items (eg for a dinner banquet now desired). Where the changes resulted in a net difference in the total amount owing, an email would be issued in cases where payments were to be made offline, and a refund or new charge would be posted to a payment processor otherwise.

The RNAO is intending to work on implementing this feature, perhaps in a phased manner concentrating on meeting their priority needs first. They intend to implement a Chase PaymenTech plugin upon which the higher-level functionality could sit. See Changing and Cancelling Financial Transactions to assist in developing the specification.

Batches for Data Entry and Financial Auditing

A standard accounting practice for dealing with large volumes of payment transactions is to create batches that summarize anywhere from a few to a few hundred individual transactions. Several of Joe Murray's clients batch together cheques that have been received. Some payment processors also only report online transactions in terms of batches, e.g. all of the transactions for a day are reported together as a single batch and are included in a single transfer to your account.

A batch tends to correspond to a bank deposit. Sometimes there is a need to produce a list of cheque amounts that are included in the deposit to save them writing out a long deposit slip (especially one that might have different data than the system gets), but other times it is sufficient to have just the number of items like cheques and the total value of all items recorded on the deposit slip. Each batch needs a unique batch number that the system should generate. This is the idea of tagging a bunch of transactions as matching a particular banking entry. A financial batch API would return a multidimensional array with each batch (corresponding to a transaction at the bank) having several transactions as part of it. Depending on the integration this might be used or the payments API might be used.

Advantages of this kind of financial workflow include:

Normally a batch is opened, data entry proceeds, there may be a need to make additions and corrections of typos over the course of a few days, then once the batch is finalized it is closed and posted, and no further changes are allowed to it.

A user interface for this functionality would allow a financial admin person like a clerk to create a batch, have the system generate a unique batch id, and allow the user to enter two pieces of information at a minimum: the total number of items, and the total amount of all the items. Line item data entry needs to allow existing contacts to be referenced easily as the contributor, and also allow new contacts to be added easily. Usually there would be other information recorded, namely what the payment is for. This might require just selecting a value from a custom select field for a mailing, or possibly include a bunch of functionality discussed elsewhere on this page that would assign the incoming payment to outstanding items on a statement for a client.

How many batches do orgs have open at a time?

Classes and Subclasses

 In many double-entry accounting packages, notably QuickBooks, support the concept of classes and subclasses.  Classes and subclasses have many potential uses and is a useful tool that many accountants/bookeepers make use of to help organize their financial information.  For example, a nonprofit may have a "Clergy Fund" and within that fund there is a restricted class and an unrestricted class. 

In any transfer of data from CiviCRM to QuickBooks ( and other accounting packages), the class/subclass information needs to be part of the data transfered from CiviCRM. 

Scoping considerations

Integration with an accounting package will necessarily require specified transaction data to be stored by CiviCRM, so that it can be exported to or otherwise accessed from within the third-party accounting package (whether Quickbooks or otherwise).

In deciding what transaction data to store and how to store it, a review of Open Source accounting packages is useful.  In this process it is worthwhile considering whether a CiviAccounts "Component" should be built to use the API or data structure/store of an existing accounting package, so that people who want more full blown accounts systems can install that accounts package alongside CiviCRM.

If an API is used, this creates a dependency that may cause maintenance issues in future and make it more difficult for basic users to implement the CiviAccounts Component.

If a shared data structure/store is used instead of an API, there may not be a dependency (as installing the full accounts package would be optional), but it would still create maintenance issues as the accounting package develops independent of CiviCRM.

A nice feature in either case would be for the design to provide for interchangeable "Accounting Connectors", like interchangeable Payment Processors contributed by the CiviCRM Community, that will bridge CiviAccounts with accounting packages that are in demand (or a basic "inbuilt" CiviAccounts data store - which may be the place to start).  Having "Accounting Connectors" will make the job more complicated at the outset but provide greater flexibility.  Either approach would also create a need for cross-domain knowledge to be gained and maintained within the CiviCRM community in order to keep up with developments on the Open Source accounting side (which has its pros and cons).

CiviCRM is currently focused on tracking Contributions, rather than expenses.  It is therefore proposed that the first phase of this project should focus on developing the appropriate data structures to store Contribution/Pledge information and Payments/Accounts Receivable information.

Accounts packages currently being focused on

A comparison of accounting packages can be found on Wikipedia.

Packages for which interest has been shown so far are

Quickbooks Integration - using .iif (Intuit Interchange Format) files exported from CiviCRM and imported into QuickBooks (and some other packages too) - see technical spec for them at http://support.quickbooks.intuit.com/support/Articles/HOW12778

Xero

MYOB

If you have a view on any existing Open Source accounting packages, please make them known!

GnuCash

Data Model

Here is an image of the current draft data model: