Algoritm to reconcile accounts....

[xklbr]

I have a simple system to register the payment of the invoices of my clients, but then there is a problem with one of my main customers.
--> I send my products to my customer.
-> Once every two months I see a deposit in my bank account with my client's name, but not knowing the invoices.
--> I must -using only the amounts- figure out wich invoices they paid me.. This is "easy" because the invoices are generally of very different amounts. and there is -almost every time- only one combination of invoices that will result in the complete ammount of the payment. For the exceptions, I can treat them like they are.. exceptions.
-->That takes me about 2 hours. some times more.
--> Then every 4 or 6 months I go to a meeting with my client to reconcile our accounts. to see which invoices he hasn't payed yet.
So I just can't use a LILO process. (pay with the money the 1st N invoices, until its full)

So ... I need a function with these parameters: array(or RS) of inovices and the amounts of each one, and the amount of the payment...
and the function must return an array or rs telling me wich of the invoices are the ones that have been covered by the payment. if there are more than one combination is not a problem, just having the first match.


And this one "little" problem has me working for over than 1 week.

 

[sillysod]

tell them to send you a remittance advice, problem solved

do they always pay the oldest invoices first? or do they py one from 2 months ago ad one from last week.

How many invoice are you talking about roughly

 

[chiph]

If they ever have a duplicate amount on an invoice, or if they ever partial-pay an invoice, there's no algorithm to help you.

Even if the invoice amounts are all different, this problem is what's called NP Complete. For small quantities of data, it's not too bad. But once the number of invoices gets "large", then CPU processing time goes up exponentially. I think your problem is a simple case of the knapsack problem, where you have to pack x items in a knapsack, and not leave any empty space. Which doesn't make it any easier to solve. :-(

You'll need them to tell you which ones they paid via a remittance advice, like sillysod said.

Chip H.


____________________________________________________________________
Click here to learn Ways to help with Tsunami Relief
If you want to get the best response to a question, please read FAQ222-2244 first

 

[sillysod]

well our accounts package at work, Sun Financials
allows us to "auto allocate"

it simply orders the transactions by date and allocates them one by one until it either matches the allocation ammount OR it goes over it. If it goes over it stop and shows an out of balance figure

This is great if a debtor reliably pays oldest invoices first

but things are never that simple

We mainly use it for allocating a VAT payment off, where one payment needs to allocate several thousand individual VAT transactions in a quarter, by definition the VAT payment will always allocate off the VAT transactions so for this instance it works excellently.

Where it me I'd have to insist on a remmittance from the debtor, its standard practice and saves you alot of time.

I guarentee you that one day you will misallocate and when that happens tracking down the error 6 months later when your debtor phones up to say that his creditor account doesnt reconcile to your statement will cause you a right headache.