Balance Sheet Reconciliation

In ERPNext, it is currently possible to reconcile only receivable and payable account through Payment Reconciliation tool, in which, internally the system will create gl matching pair called Payment Ledger Entry.

But in some cases, user still need to reconcile other type of balance sheet account as well. For example, tax entries that need to reconcile with tax paid to revenue department.

With the requirement, this module provides a separated reconciliation option for any selected BS account's GL Entries.

Installation

$ cd frappe-bench
$ bench get-app https://github.com/kittiu/bs_reconcile
$ bench install-app bs_reconcile

Usage Guide

There are a lot of use case and balance sheet account that we can put this reconciliation tool into use. In this guide, we will use a VAT account to demonstrate.

Account Setup

Open Chart of Account and edit VAT and check Allow Reconcile.

Note: When GL Entries of this account is created, they will be marked, is_reconcile = True and residual amount to be reconciled = debit-credit.

Create transactions with amount to be reconciled

Lets test by create a sales invoice with VAT (100 + 7 = 107). The GL Entry for VAT will created with initial residual = 7 to be reconciled.

Note: Let's create couple more sales invoices for demonstration

Balance Sheet Reconciliation

To reconcile, open Balance Sheet Reconciliation window, select Reconcile Account = VAT and click on Get Unreconciled Entries. Unreconciled Entries section will list all open items.

In the picture, there are some open credit entries but still no open debit entries to reconcile. Click Reconcile button now will just show the warning message.

For a simplified sample, create a journal entry with Debit VAT = open reconcile amount.

Back to Balance Sheet Reconciliation again, now they are ready to reconcile.

Select sum debit equal to sum credit and click on Reconcile button. Once reconciled, system will create Partial Reconcile Entries to keep track of reconciliation.

Now, Partial Recocile Entry (report view) shows each GL/Voucher reconcile pair.

In case of full reconcile (no residual left), system will issue Full Reconcile Number, for which purpose is to link each group of reconciled GL Entries together.

Unreconcile GL Entries

In the opposite way, user can also unreconcile the already reconciled entries. The unreconcile process can occur in various ways,

1. Document cancellation
2. Delete reconcile pair, Partial Reconcile Entry
3. Delete Full Reconcile Number which in turn delete related Partial Reconcile Entries
4. Manual unreconcile selected GL Entry

Whatever the way used, internally, it is the Partial Reconcile Entry that has been removed and reconcile data gets updated.

Remarks

• This method of reconciliation use Partial Reconcile Entry doctype to keep track of reconciled amount at the GL Entry level.
• As such, GL Entry now has section that display its unreconciled amount (residual).
• Full Reconcile Number keeps track of all GL Entries that make up a full reconcile group.
• In this guide, we only do manual reconcile. But in case GL Entry has against_voucher, this can be automatic. This case is true for Invoice to Payment.

License

MIT