In my previous blogs I wrote about two proposed reconciliation models – replication and relocation – and showed how they could work with credit cards and other PSP payment methods. In this blog I want to explore the different options for implementing the triangle of reconciliation. In contrast to the earlier blogs in this series, this one will be a bit more technical in nature.
Let’s take another look at the models and the triangle as a whole. To recap:
- With relocation, the payment data is stored in CRM which provides aggregated journal entries based on reporting dimensions to the accounting system (AS).
- With replication, the payment data is stored in both the CRM and AS.
Both models have three data exchanges: bank-to-CRM, bank-to-AS and CRM-to-AS. I won’t address the bank-to-AS exchange as this is different for every AS and is usually already in place. The bank-to-CRM exchange is dependent on the CRM being used. In the case of Salesforce, FinDock provides this connection, along with the tools to translate the bank transactions into CRM data.
My main focus here will be on the CRM-to-AS exchange. We’ll explore a number of different options and examine the pros and cons. Before that, we need to take a step back and look at the requirements for reporting and balanced journal entries. These requirements, alongside the reconciliation model, have a significant impact on which solution is the best fit.
Requirements
A lot can be said about what reporting dimensions (RD) should be included and whether or not they are truly essential, but for the sake of simplicity, I have boiled the dimensions down to two levels of complexity:
| Low complexity RD | High complexity RD |
| Payment Method Payment Processor Payment Processor Account Donor type1 |
Everything in Low + Originating Campaign Fund restriction Donation type 2 Custom dimensions |
1 Donor type can be a private donor, major donor, company, other NGO, etc.
2 Donation types, such as a legacy donation, contract based donation, etc., can impact revenue recognition.
Reporting dimensions provide context to a transaction and ensure transactions are assigned to the correct ledgers. This is the basis for generating the various cross-sectional reports required by the AS.
Balanced vs unbalanced journal entries
In double-entry bookkeeping, ledgers need to have balanced debit and credit journal entries. Debit means a transfer to that account, while a credit means a transfer from that account. With Assets such as cash a debit transaction thus means an increase in value, this is different when dealing with revenue, but that is beyond our scope here.
Now, these journal entries don’t have to be a 1:1 match in terms of record counts. It is perfectly possible to have one credit entry to balance out two or more debit entries, as long as the total amounts match. Consider the following example.
We receive two donations, 100 euros for a project and 50 euros unrestricted cash. Both donations come in as bank transfers on the same day. The unbalanced journal entry would be:
| Date | Ledger | Debit | Credit |
| 03-03-2021 | Projects | 100 | |
| 03-03-2021 | Cash | 50 |
This only accounts for the debit side of the equation. To balance the journal entry, we would need to add a credit entry against the donations. Like this:
| Date | Ledger | Debit | Credit |
| 03-03-2021 | Donations received | 150 |
In this way, the accounting system knows that 100 euros from the bank account is now assigned to a project. The other (unrestricted) 50 euros remains in Cash. This is of course a somewhat simplified view as it leaves out all other reporting dimensions and nuances.
The unbalanced vs. balanced journal entries requirement is determined largely by what kind of AS is being used and the complexity of its model. Some systems can automatically balance journal entries against other ledger accounts based on reporting dimensions in the journal entry itself. If this is not possible or undesirable, balanced journal entries are needed.
While this is not overly complex, not every communication implementation between the CRM and AS is suitable for that requirement, hence the need to put this in the “high complexity” category. Now let’s have a look at some of the technical solutions we can use and their ability to fulfil each of these requirements.
Salesforce Reporting
One of the obvious candidates for reconciliation is Salesforce reporting. Never underestimate what you can do with well designed reports in Salesforce. However, this reporting is mostly suitable for the replication model where there is much more of an emphasis on comparison rather than data exchange.
A report is created based on the Payment object which is part of the FinDock data model. The reporting dimensions are implemented as groupings in the report, creating subtotals which can be easily compared to the ledger balances and transactions in the AS.
However, due to the limits on groupings in reports, only low complexity reporting dimensions can be supported. In addition, since the report only contains the payments from CRM, the journal entries are inherently unbalanced.
Salesforce reporting can be a valuable tool in the reconciliation process, but if you process a significant number of payments or use the relocation model, it is not going to be enough.
Supported models:
Replication
Supported Journal entries:
Unbalanced
Supported RD complexity:
Low
Implementation difficulty:
Low
Pros
- Easy to implement
- Easy to use
- Easy to adjust to new criteria
- Doesn’t consume any data storage
Cons
- Large data volumes might pose a challenge
- Limited ability to implement reporting dimensions
- Journal entries are not persistent, but only exist in the report
- Corrections to earlier entries require manual action
Salesforce Reporting Snapshots
Salesforce Reporting Snapshots allows you to automatically store aggregated information from reports into a custom object. As such, it has the advantages of Salesforce Reporting. Additionally, since the information is persistently stored, additional snapshot reporting based on the custom object itself can be used to create the credit amounts needed to balance the journal entries.
The Salesforce Reporting Snapshots provide a flexible and configurable way to implement reconciliation. However, it can also be tricky to configure and maintain. All in all a great tool for specific use cases, but the lack of easy ways to correct snapshots makes it delicate.
Supported models:
Relocation, Replication
Supported Journal entries:
Unbalanced, Balanced
Supported RD complexity:
Low
Implementation difficulty:
Medium/High
Pros
- Point and click configuration
- Can run automatically
- Relatively easy to adjust to new criteria
- Journal entries are persistent
- Consumed data storage is easily cleaned
Cons
- Large data volumes might pose a challenge
- Limited ability to implement reporting dimensions
- Very hard to redo the aggregation if something went wrong
- Corrections to earlier entries require manual action
Custom Batch Apex
If you have complex reporting dimensions or huge amounts of data to process, batch apex might be the way to go. By iterating over all payments since the last run, it becomes trivial to create the various journal entries and balance them out, storing everything in a custom object.
However, as with all apex code, it will require additional work in terms of test classes and deployment. (Side note: make sure to use the decimal type to add the various amounts rather than the imprecise double.)
There is almost no challenge in Salesforce that can’t be overcome by carefully applied apex. This is also true for creation of journal entries. The powerful concept of batch apex allows processing of huge amounts of data with as little or as much reporting dimension complexity as necessary. However, not everyone is able to create or maintain apex code, and since reporting dimensions might change, this is a necessary skill to have close at hand.
Supported models:
Relocation, Replication
Supported Journal entries:
Unbalanced, Balanced
Supported RD complexity:
High
Implementation difficulty:
High
Pros
- Very robust
- Easy to use once build
- Can work with huge amounts of data
- Can support a huge number of reporting dimensions
- Journal entries are persistent
- Consumed data storage is easily cleaned
Cons
- Requires Apex Programming knowledge to build and maintain
- Corrections to earlier entries require either manual action or depend on field history settings
- Changing reporting dimensions typically requires changing code
Accounting Subledger by Salesforce
Finally, we then get to the first and only product offering on this list. Accounting Subledger (ASL) from Salesforce.org. It is far more flexible and powerful than any of the other discussed options.
Initially, ASL only supported the Nonprofit Success Pack data model, but with the March ‘21 update, custom data model support is available. This allows administrators to easily configure journal entry creation based on any object in Salesforce, including FinDock Payments and Installments.
Accounting Subledger is by far the most versatile option in this list – good maintainability, automated corrections and support for basically any combination of reporting dimensions.
Supported models:
Relocation, Replication
Supported Journal entries:
Unbalanced, Balanced
Supported RD complexity:
High
Implementation difficulty:
Low
Pros
- Very robust
- Easy to use
- Continues development and improvement
- Can work with large amounts of data
- Can support a large number of reporting dimensions
- Journal entries are persistent
- Build-in export formats for a variety of accounting systems
Cons
- Requires subscription
- Consumed data storage is not easily reclaimed
Conclusion
Every use case is of course unique. There is no one correct answer to the question, “What is the best tool to create the journal entries?” Interestingly with Salesforce, the addition of ASL to the mix gives us buy vs. build options.
While ASL is no doubt a powerful tool, it might be overkill for smaller organizations. If the Salesforce reporting features don’t suffice, certainly have a look at ASL to see if its a good match before exploring off-platform solutions. Even though they might not come with a price tag directly, creation and maintenance of those solutions isn’t free by any means.
I hope this blog gave you some insight into the different options out there to create the journal entries necessary for the CRM to AS connection. If you have thoughts or comments on this topic, or even have a great solution which I didn’t cover here, let me know!