Published
Tuesday, August 4, 2009 - 06:21
Written by

We are finding that CiviCRM/accounts issues are becoming increasingly important for our clients, and Eileen's recent blogs and the discussion they are generating are a fantastic step towards helping find the best way for Civi to deal with financial transactions.

For example, I think it could be useful for Civi to ultimately develop functionality for maintaining simple bank accounts within CiviCRM, so that small organisations can maintain basic accounts without the headaches of integration with an external package.

Currently we have clients who are willing to rely on CiviCRM as being the central repository for the detail of all contributions, with a lump sum figure hitting their GL at the end of each month which can be reconciled against CiviCRM's records. This is largely reliant on the organisation having very good procedures in place to attend to banking on the same day as transactions are entered into CiviCRM, to make it easier for payments to be reconciled. It also requires some custom reports for reconciliation, and involves some headaches when cheques bounce.

For one client, we are starting to write some scripts to do automated bank reconciliations against contributions recorded in CiviCRM, which involves us writing one or more custom tables so that a whole group of contributions (cash and cheque) are bundled together into a bank deposit record linked to a specified bank account, against which the reconciliation can then be run. If the amount of a real cash/cheque deposit doesn't match against the aggregated total of CiviContributions in the relevant bank deposit record, then an exception report is generated so that the inaccurate bank deposit record can be rectified. We are also doing credit card integration with a bank and recording successful transactions in CiviContribute through the CiviCRM API, which are generally easier to reconcile as there is only one contribution per "deposit".

It would be interesting to hear whether people think having "Bank Accounts" (whether Bank, PayPal, Petty Cash etc) and a method for aggregating a batch of contributions as a deposit to a Bank Account, would be a valuable addition to CiviCRM (CiviAccounts is the obvious name for such a module). Of course there would also need to be a system for recording expenditure from the Accounts as well (whether as expenditure towards running a CiviEvent, a CiviGrant or otherwise).

An important aspect for the integrity of such a system is that once a contribution is reconciled, it should be difficult to delete/edit it in order to ensure CiviCRM stays in "sync" with the relevant bank account/s. This could be controlled through the ACL system.

To provide the level of ACL and simplicity of interface required for some of these processes, currently clients are asking us to write separate interfaces that use the API to submit payments as CiviContributions and simultaneously add those contributions to a bank deposit record in a separate table.

If this is something people are interested in seeing within CiviCRM, then we can look at contributing the seeds of a basic accounts system for Civi (even if initially only table structures) and seeing where this discussion takes us!

Comments

Since membership fees, event fees, donations, gift aid and grants contstitute about 90% of our club income (and are all CiviCRM-type income), it would certainly be helpful if our treasurer could use CiviCRM for accounts too. The more fully featured the better, of course, but even basic functionality would be very welcome.

i'm afraid that if another non-trivial feature is added, CiviCRM might try to do too many things at once. if these accounting features become part of the CiviCRM package, they'll have to be taken into consideration each time anything is done to any of the other core features.

why not develop this accounting package as a separate add-on module?

I definitely think this should be a Component that can get switched on or off under Administer CiviCRM.

Some of the data structure I propose in Scoping out basic CiviAccounts/CiviBanking, however, would need to be followed through the core of CiviCRM. For example, the civicrm_receipt and civicrm_contribution_receipt tables would be needed to enable making multiple contributions with one payment or vice versa. But I think that is a good thing and not a bad thing as some others are looking out for that functionality anyway.

The civicrm_deposits and civicrm_bank_accounts tables would be independent from the core.

A general ledger/ ERP is a very complex thing. Creating one (no matter how simplified) just isn't very smart for a whole lot of reasons.

Having said that, I do think CiviCRM has to clean up and standardize the transaction data model to make integration into accounting/ERP systems as easy as possible. There are two steps, IMHO, here:
(1) Define the "CRM" transactions... an order for an event, a promise to pay (pledge), etc and facilitate moving a single transaction type for orders into accounting/ERP systems. When new transactions pop up (direct payments as part of a case management system, for example, follow the same standard).
(2) Define a payment. Apply that payment universally to all CRM transactions.

Then pick a reference accounting system ( probably quickbooks) and implement the integration. If that works, integration with most accounting systems is possible.

What accounting package would be appropriate for your clients?

(2) Define a payment. Apply that payment universally to all CRM transactions.

One might argue that Contributions are the defined payment. I can think of some qualifications I might have in saying that but I'd rather hear yours.

best example I can give is a pledge and a donation.

A pledge has the transaction (i.e. a pledge) and the payment to match that transaction (i.e. fulfill a pledge)

With a donation the transaction and the payment are both encapsulated in the same object since if you have cash in hand via a credit card transaction or check, there is no need for the user to think about these two things separately.

If CiviCRM had a concept of payments for every financial transaction (either exposed or hidden in the UI), then ALL transactions (current and future) would behave the same way smoothing the path for accounting system integrations.

It's entirely possible I'm missing a use case here, but I "think" that we do instantiate a contribution record for any / all payments received in CiviCRM. This includes pledge payments - each received pledge payment results in a contribution record being recorded.

Our clients would need something web based as they have people all around the country that would be making deposits to different bank accounts.

It would become very tricky if they all have to have a username and password to xero or some other separate and complicated accounts package.

All I am thinking about at this stage is a simple receipting/banking/expenditure recording system, definitely not ERP.

ACL is another piece that I haven't analysed in scoping this basic functionality in my subsequent post:

Scoping out basic CiviAccounts/CiviBanking

For our small business (an automotive repair business) we use an industry specific ERP. The flow for receipting money goes like this:

1) When money comes in we receipt it against the customer (and match it against the transaction/s it applies to).

2) For defined receipt types (credit card, cash, cheque) the payments receipted appear in the banking screen. For direct credits they go straight to the cash book

3) For cheques & cash we print out a banking summary, match our takings against it, and include the slip with our deposit that we take to the bank.

4) When we reconcile the accounts we see a total payment in the bank account that should equal the days transactions. We tag the transactions equalling that total and then click to transfer them to the cashbook.

5) If the banking in the banking screen doesn't match the amounts actually deposited we have a discrepancy to follow up.

6) Finally we reconcile the cash book. In our ERP this is in an external screen but it could equally be in an external system.

It seems to me that this is the process you are seeking to incorporate into CiviCRM? A CiviBanking module so to speak?

Yes, in many ways it could start as CiviBanking.

I started outlining in more detail how I think this could work here, before realising that I should put it in a new blog post, which I have just posted here:

Scoping out basic CiviAccounts/CiviBanking.

I think CiviCRM should serve as an "Accounts Receivable" module. I should be able to use CiviCRM to easily find all money paid or owed to my organization. Currently 90% of income comes from events, classes, donationts, and member fees.

But right now I cannot even record multiple payments for a single (expensive) event registration or membership fee.

Full-blown accounting features sounds nice, but I'm not sure how we could justify the expense since QuickBooks is now offering a free web-based version for small businesses. Unfortunately none of the web-based QuickBook versions support imports. I have a hunch QuickBooks will be adding import capabilities in a future version.

On the other hand, it would simplify life for the end-user if all financial information was in one system, i.e. a single source of truth.

Here is what I see as needed in CiviCRM:

- Evaluate all pain-points for an organization using CiviCRM and QuickBooks. Then work to eliminate or minimize those pain points.
- Allow multiple payments to be made on the frontend and backend for any donation/membership/event.
- Make it very easy for an end-user to see everything they owe ( for a membership, event, pledge and/or donation ). Ideally this would be part of the "dashboard" exposed on the front-end.
- Make it very easy for an end-user to make a payment for something they owe.
- Make it very easy for staff to see everything that is owed to the organization, and which items are "overdue". Perhaps an event fee is not considered overdue until 2 days after the event.
- Allow one person to make payments for another. ( Such as I register 3 people for an event, and choose "pay later" ) How can I easily pay later on the website?
- Allow a staff member to create an email or PDF invoice for a contact who owes money, without any action by the end-user. ( For example, John Doe rents part of our building for a wedding reception. The staff responsible for booking the reservation needs a way to record that John Doe owes $500. for the rental. Since John Doe is a contact in CiviCRM, the staff should be able to record this "pay later" contribution. ( Contribution type would be set as "building usage fee" )

- Allow a small organization to use CiviCRM without the need for a seperate accounting package.

Some examples of (Windows-based) membership packages that include light-weight accounting:
http://www.mm2000.net/
http://www.chaverware.com/
http://www.rakefet.com/

I have touched on some of your points in the following blog post:

Scoping out basic CiviAccounts/CiviBanking.

Once that data structure is in place, I think a lot of the reports you are looking for could be generated through CiviReport.

I haven't addressed how the payment options you are looking for could be delivered though.

i think CiviAccounts is critical. This module would very significantly make a difference at a couple of non-profit organizations I have CiviCRM running as the legacy database for. Very important. Not just because of the functionality, but because of the changes that tightening up the data model with respect to invoices and multiple payments. particularly for non-profit organizations, both of these effects would be welcomed.

once we are click the preview for the comment, when it loads, it pushes the 'submit' or 'save' or whatever buttons, as well as the editing forms, way down below the end of the lowest right-hand column block.