Math/DBF Problem

[Charles Hoens]

This is really not a problem, per se, but rather a feature I have seen in another accounting program and would like to duplicate in A5.

Consider a table of invoices with various amounts. The user is asked to apply a payment to these amounts, however the user cannot tell whether the payment is for one, two, three or more invoices. What would be a method whereby A5 would perform the best selection while allowing the user the ability to still make changes?

An example: Invoice table A, has five invoices in it with the following amounts: $100, $200, $300, $400, $500. If the user receives a payment for $500., then the "best" solution would be to apply it all to the $500. invoice, however the user might want to apply it to the $200 and $300 invoices, so it might be best to suggest the solution, but allow the user to do a confirm.

This issue might seem simplistic, however I have a client who regularly receives payments from the State with some amount on the payment and then has to look through dozens of invoices, trying to do the calculations. He literally is sitting at the computer with a calculator trying different combinations to arrive at a solution.

I think that this is more a Math problem than a dbf problem, but perhaps someone has an idea on this?

Thanks!!

[GGandhi]

Re: Math/DBF Problem

I am not an accounting expert, but the best posting should be FIFP, first in, first paid.
the oldest invoices should be paid first, unless otherwise stated in the payment.
this will avoid conflict, that other party may claim that the payment was recorded to later date to make the older one delinquent.

[DaveM]

Re: Math/DBF Problem

I tend to agree with Govindan.
each invoice "should" have a date and there is generally a reason to have that date. Although the last invoice may still have a balance. That needs to be accounted for in the programming with a balance remaining on that invoice that was partially paid so it shows remaining when a next payment comes in. Any receipts sent should also reflect that partial amount as still due as well.

[GGandhi]

Re: Math/DBF Problem

yes the invoice that is partially paid should be left as an open item.
when the payment is received start applying from oldest and cover the entire balance on that item before moving on to next oldest till all the payment is exhausted. this system will keep the account current for most part. and this needs to noted in the company policy as well, so there is no discussion.

[Tom Cone Jr]

Re: Math/DBF Problem

Charles,

I have handled this situation like this:

I use a payment hdr table and a payment_details table, in a one to many set. The header gets information about the payment (date, payor, amount, chk number etc) and the details contains a single record for each "split-payment" that's assigned to an invoice. User begin by entering the hdr record, and then uses a lookup to select the invoices to be paid. As each is selected a new row is entered in the payment details table showing how a portion of the overall payment is to be applied. A running total is displayed. And the "remaining to be applied" balance of the payment is shown. When the remaining to be applied amount is zero then a button is pushed to commit the payment "transaction" and the open balances on each invoice are adjusted accordingly.

[Charles Hoens]

Re: Math/DBF Problem

Thanks to all, however I probably need to be more clear.

I am essentially doing what Tom Cone suggested. I set up a one-to-many set. The payments come in by consumer and the child table is the invoices outstanding. Each has a date and other information, etc. I just didn't want to complicate the question. It sounds simplistic to just apply a partial payment to the oldest payment, but accounting doesn't work that way. Payments must be applied to the correct invoice, as there are many times where one invoice, because of it's peculiarities, may not want or need to be paid. If you paid invoices that way, you would lose track of what items were paid for and which ones were not.

Consider, for a moment, you are a customer of a lumber yard. Over time, you purchase materials for Jobs 1 through 5, each for differing amounts and at different times. When you send payment to the lumber yard, you want to ensure that they are applying your funds correctly to each invoice, not just in bulk. The lumber yard too, wants to ensure correct application so that they can close out invoices while tracking those that may still be open.

QuickBooks has a function that does this application of payments, but how it works, I have no idea. It can look at hundreds of invoices and select the likely payment applications. The user can then confirm or change that application. Also, I should add, that most consumers tell the provider (lumber yard, for example) where they want their payment applied and to what invoices. The complexity in this case is that the State simply provides a name and the user has to do the math from there.

Sorry for the long explanation.

[MoGrace]

Re: Math/DBF Problem

Hi Charles,
The payment form in QB is not revealing all that is going on. I might be able to get some insight from my 01 transaction import or Export program. I'll let you know if I find anything.

[aschone]

Re: Math/DBF Problem

Holy smokes! I would cringe if given this assignment. First off we do this 100x a day. We handle it with a business rule. If you want invoice XYZ paid first then send in payment with that notice. Otherwise we post payments to the oldest invoice first moving forward. In the end basic math rules will state the the ending balance is always the same regardless of if invoice 1 is paid first versus invoice 2.

However, since you asked, I would handle your coding in an multi-dimenstional array. The first dimension would hold the permutations count. The max number of permutations would be the factorial of the number of open invoices. I would also add a true/false variable to this level of the array. This true/false would represent if the permutation worked or not. The next level of the array would store the open invoice and the amount. If the payment amount is applied across invoices in such a way as to pay off each invoice completely and not any remaining monies then the permutation would be valid and the true/false in the previous level would be marked as true.

This is a brute force method, based on the number of open invoices it can quickly get exponentially large and time consuming to compute. I would hate to see the user have to check 1000 permutations to find the right one that fit the payment allocation. Just think with only 8 open invoices you have a potential of 40,320 possibilities.

Maybe to cut down on the possibilities you could have a rule that says you pay the biggest invoice off first then apply remaining monies across the other invoices paying each invoice off entirely. Only valid permutations are those that each invoice is paid in full. However this akin to the old invoice paid first rule. What if the client isn't paying the largest balance invoice first.

So you could go with the smallest invoice first but again the client may be contesting the smallest invoice.

This is why we created a business rule to apply to the oldest first working forward. If the client contests a paid invoice we can subtract the monies from that invoice and re-apply them else where.

To solve this problem you will first need to create business rules otherwise your possible permutations will be way too big.

[GGandhi]

Re: Math/DBF Problem

If you want invoice XYZ paid first then send in payment with that notice. Otherwise we post payments to the oldest invoice first moving forward.

thank you Andrew for concurring, see post #2 and #4.
appreciate it.

[Charles Hoens]

Re: Math/DBF Problem

I like the ideas - don't like having to solve this one. However I tend to be a brute force guy and I think that's the way this is going to get done. The one good piece of news I got was that for any one customer there were generally only 10-20 open invoices. So I'll see what can be done.

It's kind of like the Russians when they constructed their first A-Bomb. They had the luxury of knowing it "could" be done!

Gandhi, I wish it were otherwise!

Thanks!

[Ray in Capetown]

Re: Math/DBF Problem

A firstline method I have had in use succesfully for 15 years in Open account bookeeping - receivable account (Debtors)
that easily eliminates the major percentage of "imbalanced" allocations and in most cases even eliminated manual flagging (the term I use in my system) - before resorting to the solutions and suggestions mentioned here..

1. Apply the payment to the balance due to get a current account balance
2. Start at the bottom (newest) to count only invoices to reach the balance
3. Flag all remaining invoices AND payments
- the unflagged invoices are outstanding.

In the few remaining cases employ whichever of the strategies works in your environment.
I leave all the questioned allocations open and hope the customer calrifies - or the system asks them to on the next statement - to reduce the number of lines they have to reconcile