Published
2010-01-04 23:24
This is really a continuation of previous blogs on CiviCRM accounting integration. I now have some basic integration bewteen CiviCRM and Xero working and decided to do a screencast.
Screencasts are hardly my preferred medium - especially now that I've tried to make one but I thought it might be useful for people to see what a CiviCRM integration with the accounting package Xero would look like. The screencast doesn't show you any CiviCRM - just Xero and is more intended to give people an idea of what the day-to-day reality of it is.
screencast
This is a pretty limited integration with a fair bit of hard coding and a narrow focus on event registrations but it's enough to cut down the work for our next block of classes pretty substantially.
In getting this far the issues I had were in many senses fairly predictable as several have been discussed before but it did throw some light :
- invoices. As per previous discussions I have elected to treat contribution records as 'invoices' and the 'invoice number' I have used is the contribution_id.
- part-payments. CiviCRM doesn't deal with part-payments at all. The way we are dealing with these is to leave them sitting unreconciled in Xero until the full payment is made. Keeping the totals in Xero and CiviCRM equal is important in order for us to be able to do a quick check to make sure everything has been accounted for and recorded correctly. Twice a year we must have everything reconciled for our GST returns but most of the time we can afford to have orphans in Xero. Unreconciled banking transactions in Xero are very visible so easy to keep track of.
- Multiple participants. This one I haven't actually sorted yet. I was expecting there to be a record for each participant in the civicrm_participant_payment table linking it to the relevant civicrm_contribution record but there isn't. I guess I need to first find all the records with an entry in the civicrm_contribution.registered_by_id and construct a multi-line invoice for those before doing my query to upload all the 'normal' invoices. This is slightly difficult as I had been using the contribution record to tell me whether the invoice was already uploaded and I will have to do a few steps to find the contribution record to figure out if it is uploaded.
- Changes in fee paid. For one reason or another people often pay a different rate than the actual class fee. This could be if they have a good reason why they can only do part of the course (e.g. leaving town) and they would agree this rate with the Executive Officer and transfer the agreed amount into our bank account. CiviCRM doesn't allow you to change the amount for a participant record to a custom amount but our Administrator has been manually changing the contribution amount to match the amount paid. We have been checking our event income using the event aggregate custom search and since I wrote it I took the value from the contribution amount not the participant records. However, the new CiviReports are really nice and they are based on the participant record so unless we can make those values accurate we can't use them. Since we are updating the contribution via code now it is a small add-on to also update the participant record. Thus the logic we are using is that we adjust any invoices in Xero before approving them. We query existing invoices daily to see if they have been paid. If Xero returns the status 'PAID' then the amount in Xero is considered to be the correct amount and the contribution and partipant record amount are overwritten.
- where to record the xero invoiceID. Not really a biggie but I wasn't sure about using the existing transaction ID field as the payment processors tend to populate it and I wanted a field that would be NULL if not populated by my script so that ruled out invoiceID. At the moment I am using the check_number field as we never use this.
- Payments received. It would seem to make sense to record payments received into the bank account & downloaded from Xero in civicrm_financial_trxn. This would make it somewhat like it's own payment processor - not sure if there is any reason not to.
- Multiple credit card payments. We seem to get multiple contribution records when someone tries to pay by credit card (page hosted off-site) but doesn't complete the transaction and then goes back in and tries again. This isn't new. Ideally they could go back into the event and pick up their existing half finished participation record and complete it (either re-try the credit card or change it to pay later & get the bank account details) but it doesn't work like this. This just means manually deleting the extra invoices as well as the extra contribution records.
- The main issue for us is that we prefer to leave invoices in an unapproved state until the money is received because often those who choose 'pay later' ... don't. When the payment comes in we thus need to find and approve the invoice before matching. So, top of my Xero wish list is an easy way to find, adjust & approve submitted or draft invoices from the bank reconcilliation screen. Approved invoices show up as income in the accounting system so many non-profits would probably not want to approve 'pay later' contributions until they receive them.
- It was also clear that getting contacts reconciled with Xero could be a bit of a drag if there were already several in there. If a contact already exists in Xero you must pass it a valid Xero contactID when putting up a new invoice for a contact. Unfortunately if you pass up an xml with 20 transactions and one is wrong it will put the other transactions into Xero but not return the invoice ID or correct contactID for the transactions making the problem worse. We have reconciled (manually) our two sets of contact records and will only create new records in CiviCRM from now on. Sigh, well hopefully there is something of use in all of that. We have something working but it's still very Alpha Eileen
Filed under
Comments
There are two things that are great about this blog post, Eileen! The first is that by giving us insight into your work in progress we get to see approaches that are outside our own usual workflows which brings its own kind of inspiration. Since my work has been exclusively with religious non-profits, invoices have never been a part of our workflow. Although paid events are a small part of our enterprise, most of our accounting is about contributions and programs - what I believe industry calls profit centers or cost centers. We need to know the fund to which the income should be assigned. In accounting terms, that means that we need to be able to assign portions of a deposit to different income accounts in our chart of accounts. When events are paid online, we record a deposit with the full event fee assigned to the event income and associated bank fee as an expense. We enter this sort of aggregated data into our accounting package and let Civi track the details.
What additional controls or opportunities does the use of invoices present for non-profits or advocacy groups?
The second thing that's great is that it shows one of the primary benefits of open source - if the feature doesn't exist one can either create it or have it created!
Thanks Father Shawn,
As you perhaps observed we are using Invoices here as a method of coding and reconciling bank transactions rather than in the more traditional sense of debtor management (through sending out invoices & statements). We are leaving them in an unapproved state until we receive the money so they don't appear as debtors and receive statements and the transactions don't hit the General Ledger and Financial statements until we are sure they will be paid.
If we were recording pledges I expect we would approve them (as I understand pledges are recorded as income at the point they are received) but find some way to ensure we didn't send out inappropriate statements if we were managing follow-up through CiviCRM.
This approach is primarily about saving us administrative time. From my experience when journalling in aggregate amounts you often have slight discrepancies and have to choose between spending time tracking them down or accept the discrepancies. About 5% of our income but about 50% of our 'splash cash' (money left after our costs to splash around) comes from being on the ball with the discrepancies.
My basic rules for for keeping accounting manageable and not missing out on money are:
1) Record everything accurately at the transactional level. "Look after your pennies and the pounds will follow".
2) Do all reconciliations as promptly as possible - they longer you leave it the harder it gets
3) If you make a mistake always reverse out - never try to adjust. i.e. if you enter 2 instead of 3 do a reversal for 2 & re-enter three. Don't try to adjust by one - that way lies madness. (not relevant here but I'm so used to harping on about it I can't leave this rule out).
So, in general I find that by putting accurate transactional data into our accounting package I keep the workload down and the accuracy up. In a larger organisation the advantages can include having less work done by the more qualified (expensive) accountants and more by the (cheaper but terribly undervalued) administrative staff and a division of work between accounting staff and other admin staff.
The job of reconciling the bank account has to be done and because these transactions are automatically imported into Xero on a nightly basis integrating with Xero is effectively the same as integrating with our bank account. The process of reconciling is sped up and the civicrm contributions are confirmed automatically without any further intervention.
Other advantages will depend on your accounting package but as you will have seen using Xero's AJAX-y interface is rather quicker than CiviCRM's so doing the reconciliation in Xero is much quicker than receipting money into CiviCRM. Also, most accounting systems allow you to do drill down from generalised account codes to specific transactions so a positive spin off is the level of information that can be seen within your accounting package.
If we received lots of donations I would do a similar thing with contributions as there is little difference between contributions & registrations in this context (different account codes but not much else). However, the way in which money is received might change my approach. We receive all our money directly into our bank account (either by internet banking or credit card or by instructing people to go into the bank) so our bank account data is the focus for us. I haven't thought much about the implications of receiving money by cash / cheque.
Your last line hit the critical difference in our approach: How the money comes in. Your approach makes total sense when the money is going directly into your bank and you are seeking to track all of those independent deposits. I'm at the opposite end - it's mostly offered in person and the bulk of that is once a week. The contributions and the aggregate deposit are counted and reconciled as a batch.
Also, the only sense in which we count pledges as income when they are offered is in budgeting: annually and year to date. In accounting income vs. expenses it's not income until there is a contribution.
You are right. You don't care about the make-up of your weekly bank deposits too much as they are an aggregate & all coded to the same thing.
You just need decent cash handling systems & to receipt it into CiviCRM as appropriate (although I'm struggling to reconcile my image of the collection plate being passed around with someone sitting receipting into CiviContribute)
it seems like multiple or partial payments might be looked at as pre-programmed recurring payments. so the work on recurring payments is done, and i wonder what would happen if that code was rehashed... so in other words, instead of the customer saying 'i want to pay this much a month for 2 years', we would tell the client 'you can either pay this much a month for 2 years or...'.
I work for a charity that would greatly benefit from having Xero integration on our CiviCRM platform. Do you have any code you can share?
Thanks in advance
Send me a pm or find me on IRC - NB people don't normally see comments on old blogs