If you're a small organization with minimal or straightforward income from CiviCRM, you probably are happy with using CiviCRM income reporting for the purpose it was originally designed, i.e. for helping you engage with your constituents.
But if you are larger, use a payment processor, and your reporting needs are more complex (e.g. a political party that needs to report income rather carefully), then you will have run into the challenge of reconciling payment processor income in CiviCRM against your bookkeeping system and bank account. This is especially true if you are using a payment processor that deposits money into your bank account somewhat erratically in batches. In that case, matching up which of your contribution payments in CiviCRM are being deposited into your bank account, and when, becomes very laborious.
We've had a couple of organizations looking for help with this, so the latest iATS Payments Extension comes with a new report to partially help with this problem.
First, here's a better description of the problem.
1. Typically, income categorization is done in an automated or semi-automated way in CiviCRM. So for example, which income is assigned to special funds, or which is actually taxes paid that needs to be separated out.
2. Your bookkeeping relies on your bank account statements to reflect the income collected via CiviCRM.
3. Your monthly income as reported in CiviCRM will only rarely exactly match the income showing up in your bank account.
The reasons it might not match are numerous (administrator error, chargebacks, and differing timezones are the three main ones), and the only way to really get it right is to be able to identify which of the payments showing in CiviCRM actually ended up in the bank, and when.
Things like cheques, or anything that is just reported in CiviCRM but doesn't transact in CiviCRM, is relatively easy to match up. But payments that go through a payment processor can be very hard to match because they will get bundled up and deposited at a time later than that showing up in CiviCRM. For example, in Canada, these are often bundled by credit card type each day.
The approach that we use (and what the clients were doing previously, manually) is to make use of the payment processor reports as an interim layer of reconciliation. Specifically, if we can match up the appropriate contributions in CiviCRM against those recorded in the payment processor, then the batches getting deposited into the bank account should be reconcilable.
Note that this strategy assumes that the payment processor is only used with CiviCRM, and it can only reconcile the income in CiviCRM that corresponds to the income going through the payment processor, but in cases where for example the occasional manual payments get put through a payment processor, this strategy will help identify them.
The way the payments in the processor reports get matched against the entries in CiviCRM is with the use of the contribution "invoice ID", a globally unique and immutable (and illegible to people ...) string assigned to each contribution. Because that string is passed to the payment processor and stored there, it can also be used to match back against the CiviCRM payment information. There's also a separate transaction id that is generated at the payment processor and sent back to CiviCRM, which is also useful.
Ready to try it out? It's just a slightly altered version of the standard contribution report, with extra fields that pull in matching information from the iATS journal tables. It'll be included in the next iATS extension release (1.6.2) which is currently in beta - feel free to try it out, or you can wait for the official release. Documentation forthcoming on the extension wiki.
