How to process bank statements

from http://www.nabble.com/How-to-process-bank-statements-td20294429.html

For the moment, just a more easy to read copy

On bank statements in The Netherlands we have

• (partial) payments by customers
• (partial) payments to suppliers
• payment of bank costs and others

Where and how do I process these in OFBiz?

Is The Netherlands the only country where payments of invoices and cost on
bank statements are booked?
How is this to be done in OFBiz?

Pierre

Hi Pierre

You are not alone - a lot of places use bank statement processing.

I looked at OFBiz accounts a few months ago and tried to work out how to process bank statements - so would be interested in any other replies that you get.

The main thing I found was that the GL account for the company cheque account doesnt display what the balance is (unless you use the trial balance or other reports) so can be difficult to trace what your closing balance was in line with your last statement.

One idea I tried was to look at using the Financial Accounts functionality as you can input an opening balance and have deposits and withdrawals just like you have on a bank statement. It's also a Payment Method Type for transactions. The Financial Accounts details can be displayed in Party Manager as part of the party record.

The thing I havent sorted out yet is how to mirror the transactions of the Financial Account into the equivalent GL account (Eg. General Checking Account) so that they stay in synch.

Not sure if this helps you at all but if there are other ways to process and track bank statements in OFBiz then I'd be keen to find out about them too.

Thanks
Sharan

Of course many banks also allow you to download an electronic
bank statement. Many use OFX for this, others CVS or something like
a spreadsheet. It would be nice to be able to
import that rather than re-type it all.

David

my thought is to import the bank statement, ie, the electronic
tranaction from the bank into it own transaction list, then link those
transaction against the accounting transactions, this would then have a
status in the banks transcatiions of reconciled.
you could use the same scheme for invoices from supplier, and shippers.

BJ

I have a few thoughts regarding this issue. Most of them came from analyzing
commercial accounting and financial software.

All companies use one or more bank accounts in their daily transactions.
Some of this transactions relate to their operations (sales and purchases).
Others relate to taxes and commisions from the bank or financial
institution.
There are automatic money transfers, credit payments, and all sorts of
monetary transaction.

Assuming that the bank almost never makes a mistake, it is a very smart
habit to keep the company books in sync with what the bank statement says.

I figured a set of requirements to satisfy a client who needs this
functionality:

1) There has to be a simple way to input the bank data into the system. As
far as I know, all banks let you download this data in some simple and
parseable format.
-> We need a different parser for each format.

2) The data has to be stored in a common structure in the system. At a
minimum this is needed:
-Bank Statement number/sequence
-Bank Statement date
-Current Balance (available and retained)
-Previous Balance
-Transaction Date
-Transaction description
-Document number
-Credit Amount
-Debit Amount
-> A common structure is needed to store the data from different banks

3) Each line in the bank statement must have a connection to an accounting
transaction item. This transactions items could exist already or have to be
created to match the bank statement.
-> An ui must exist to let a user enter a match between a bank statement
line and an accounting transaction item.
-> An ui must exist to let a user create a new transaction item with data
from a line in a bank statement.
-> When a match is made, the bank statement line must be disabled for a new
match.
-> It would be nice if the system could suggest possible matches.

4) The balances in the corresponding general ledger account and bank
statements must match.

5) FinAccount could be used to represent the bank account. Each bank
statement will be related to it's corresponding FinAccount.

6) The bank statement lines need the following possible states:

• CREATED
• RECONCILED

7) The bank statement need the following possible states:

• CREATED
• IN PROCESS
• RECONCILED

Daniel.

We would need a way to import the transaction data into OFBiz

Daniel,

Nice outline of requirements.

My thoughts on this are:
@1 Parsing
Not only importing and parsing of imported files has to be possible, but
also manual entering of bank statements. Some bank statements come only once
per period with not many line items. In that case the proces of downloading

• importing - reconciliation would be less user friendly than just entering
  the bank statement.

@2 Data storage
Some banks also include interest date and currency (incl conversion rate)
per line item on the bank statement.

@3 Bank statement line items
Proposing matches - this would be extremely user friendly. Proposed matches
could be based on bank ID of Customer/Supplier. The system could then
propose in outstanding invoices to select for further reconciliation.

The solution must also show the difference between the amount to be
reconciled (Bank Statement Current Balance / Bank Statement Previouse
Balance) and the reconciled transactions.

@8 Overviews (New)
An overview must exist that states unprocessed bank statements, consisting
of the bankstatements with status CREATED and IN PROCES, possible items
shown per bank statement are:
FinAccount ID,
Fin Account Name
Statement ID
Statement Date
Status
Unreconciled Amount

An overview must exist containing the reconciled bank statements (status
RECONCILED) for audit purposes.

Regards,

Pierre

(quoted from Daniel just above)
2) The data has to be stored in a common structure in the system. At a
minimum this is needed:
-Bank Statement number/sequence
-Bank Statement date
-Current Balance (available and retained)
-Previous Balance
-Transaction Date
-Transaction description
-Document number
-Credit Amount
-Debit Amount
-> A common structure is needed to store the data from different banks

Some bank statements also display intrest date and currence (incl.
conversion rate) per line item

once the
1) entities (current or new) have been determined.
2)the import high level services have been implemented,
3) the low level services that to the actual conversion can be written.
4) Secas and EEcas (if required) can be specific to banks for
reconciling the ofbiz transaction file against the bank totals, that
apply. these would be like third party(banks) drivers.
I have adopted using the Contact Mechs instead of properties to put the
bank info for. however that is not the way it is done in ofbiz at this time.

(from BJ just above)
I have adopted using the Contact Mechs instead of properties to put the
bank info for. however that is not the way it is done in ofbiz at this time.

What did you make this choice (just curious) ?

Jacques

1) it is easy for the user to put in and change info.
2) to me, that is what the info is, How to get to your bank account, or
contact the supplier, or how to get a credit on an order.
4) by making different types of Contact mechs, the program can decide
what contact mech to use.

BJ

BJ,

I can follow your reasoning. And it would be the simplest thing to do for
customers (Bill-to role?) and Suppliers.
Using ContactMechs for displaying bank related info in communications and
transaction documents (e.g. purchace and sales orders, invoices) would be
the easiest route to go.
But these details may not be editable by everyone who can just add
ContactMechs (preferrably restricted to persons in some kind of accounting
role).

But for the organisations using OFBiz (company and subsidiaries) this is a
bit more complex. A bank account is more than just a ContactMech. It is a
part of the accounting schemas and needs to be auditable....That is what the
transactions in the general ledger (and AP and AR, etc) are for.

A CFO doesn't care less what everybody does with ContactMechs as long as his
part of the system (FICO, AP, AR, etc) are in order and auditable.

Addendum

Is using attributes on a Party not a better idea. That way it also usable
for employees, who get paid electronically (the generic way to pay employees
in The Netherlands). The attribute should then be selectable in stead of
free text.

Addendum 2.

Bank account ID's are normalized per country. And international
harmonization is in effect. This should be taken into consideration when
defining a solution how to store bank account details.

Pierre

[But these details may not be editable by everyone who can just add
ContactMechs (preferably restricted to persons in some kind of accounting
role).]

yes remember that this is the company party that this is put into.
this then creates the party for the Bank or supplier and links them.
the services for this check the permissions to allow CRUD operations..

[ A CFO doesn't care less what everybody does with ContactMechs as long
as his part of the system (FICO, AP, AR, etc) are in order and auditable.]

no more than the consideration for a PCI audit and security. just
focused on other financial information.

This is at the third party software level.
the SECAS will check that the proper routing number(tied to Geo) and
account format for the bank.
the validation is to run a validation on the bank account for a $1.00.
the interaction between banks is based on agreements (entity) and a page
from the Trading partners data used currently. I brief review of the
Oagis looks like it has a similar structure.