Post Wed Dec 15, 2010 11:55 am

Auto Accounting on Bank Statement Import

Transit Account: A GL item used to hold the value of payments received that have not yet been matched to Sales Orders, Invoices or Credit Memos. This account is credited with the total value of receipts shown on an imported Bank Statement. This account is debited with the line value of each receipt as it is matched to the Sales Order, Invoice or Credit Memo.

>>> A+M In case of payment a Transit Account works the other way around: it is used to hold the value of payments made that have not yet been executed by the bank (when we have created a payment file and sent it to the bank).

>>> RM: Understood.  I will update the document to include this explanation.

Proposed Solution:

  • Extend the current workflow to include a trigger for import of cleared items via the bank statement. The last point on the workflow (reconciliation) should be split into two events: Cleared and Matched.


Accounting Workflow:
Cleared (per Statement)

The Cleared event is driven by the cleared items shown on the Bank Statement

>>> A+M We propose that these workflows should be seperated. Is this case both workflows are more clear. The two workflows are:

1. cheque or cash (current behaivior)

2. wire transfer (new behaivior)

If we have two workflows graphic 1 to 3 could be simlyfied. In graphic 3 we only have column cleared. Column matched is a configuration option.

Also the user interfaces must be clearly seperated.

 >>> RM: Understood.  The best method for visualizing the configuration of the extended workflow and the use of a Transit Account is still under debate.  We will consider your suggestion.

This would then support a routine being triggered on importing a Bank Statement that reads

  • The Total Amount received

  • The Total Amount paid

  • The End Balance of the Bank Statement

>>> A+M There are some German Bank statement formats without total amount or end balance information, only statement items and a technical check sum.

 >>> RM: Understood.  The end balance may need to be deduced as it is required during the reconciliation process.

Similar accounting transactions can be generated for matches on payments, depending on the configuration of the payment method.

>>> A+M On the payment side the generated accounting transactions are:

1. DR liabilities  CR transit account at time of completing the payment proposal

2. DR transit account  CR bank account at time of reading the bank statement

 >>>RM: "time of reading the bank statement" is the "Cleared" event.  Please be aware that the "Cleared" event does not include matching logic.  Matching logic is contained  in the matching algorithm used, and the matching algorith is invocked by the matching event.  Therefore the second line can be driven by the "Matched" event, not the "Cleared" event.  Is this a problem?


>>> A: There is no matching needed at time of reading the bank statement. We have just matched the invoices when we created the payment proposal.

>>>RM1: Ah ha!  OK, no problem.  The timing of the second accounting event will be configurable, either as you descibe (Cleared event as total value) or per my suggestion (Matched event as line value).  We will take this into consideration.

The automatic matching algorithms must be extended to also search for matches in open invoices, orders and credit notes. Matches can be declared as either Strong or Weak matches, with different automation behind them. For example, it should be possible to accept all strong matches with a single click (after a review on screen). At this point the system should automatically process the receipt or payment against the matching document, updating that documents payment schedule with the payment received or made.

>>> A+M If there is no invoice found the algorithm should search after a business partner to match. We propose different matching algorithms to be defined: one for invoices and a second for business partners. In this case the matching algorithm is clearer.

If only a business partner is matched the accounting transactions are the same.

 >>>RM: Could you clarify the business process of matching to a business partner and not to a specific document?  How are invoices closed?  Surely this would require a seperate UI to be created supporting this process. 

Could you also clarify the statement "the accounting transactions are the same".  In most accounting regimes it is the matching of payment to a document that drives recognition of receipts. Until that point any unallocated amount is held on a seperate unallocated account. There is also the question of analytics.  Key metrics relating to collections would become difficult to interprete correctly (arguabley meaningless).

Could you also clarify how to manage Dunning if a payment has been recieved and matched to a business partner, but not yet matched to an invoice/

>>> A: how is Openbravo dealing with overpayments? These are also payments which can't be matched to an invoice but left as a credit at the business partner.

>>> RM1: We can give you a demo of the current functionality for managing overpayments, however you will need to test this process explicitely.  Right now I will assume that the mechanism used for managing operpayments will suffice. 

Concerning dunning: so long as we havn't matched a payment to an invoice all due invoices will be dunned. But this won't be the case. If there is an invoice for this business partner we will match the invoice before matching to the business partner himself.

>>> RM1: OK. We will leave this as a manual control that the Dunning Run not be triggered until all the invoices have been matched.

Design Notes:

>>> A+M The design notes could be much clearer and simplified if the two workflows are seperated as proposed.

>>>RM:We will review.

A Transit Account can only be applied to a Financial Account of type "Bank". It can not be applied to a Financial Account of type "Cash". This is because accounts of type "Cash" do not support the import of Bank Statements.

>>> A+M this is not true. It is a very common workflow in Germany to post the value which leaves the petty cash to be deposited to the bank account first to a transit account. So you are able to control the transit account and make sure that every EURO which left the petty cash appears on the bank account.

>>>RM: I believe what you are describing is the role of the intermediate account in the accounting workflow.  The fact is that in Openbravo accounts of the type "Cash" do not support the import of Bank Statements.

>>> okay, if the design notes only are for the new workflow, you are right!



Post Edited by rmorley at 12/15/2010 12:34

Post Edited by bochmann at 12/15/2010 13:21

Post Edited by rmorley at 12/15/2010 14:43

Post Edited by rmorley at 12/15/2010 14:50

Post Edited by rmorley at 12/15/2010 14:53