Accounts Receivable... how do YOU do it?

[jasonwood]

I'm trying to decide what the best method would be to organize invoices and payments to have a reliable and flexible accounts receivable system. I'll tell you about what my little mind came up with... perhaps you could share what you have done...

Here's my idea...

Invoices database contains fields for invoiceTotal, invoicePaymentTEMP, invoicePayments, invoiceBalanceDue

When you receive a cheque, you'd go to a "payments database"

You'd enter the client number and the amount of the cheque in the payments database, which would relate back to the invoices, and show them in a portal with newest first. The portal rows would show invoiceTotal, invoiceBalanceDue, invoicePaymentsTEMP ("payment entry"), and invoicePayments "Total Received". It would only allow entry into invoicePaymentsTEMP.

Then you click a script "Process Payment", the summary of invoicePaymentsTEMP would be compared with the amount of the cheque... if they're the same it will "process" the payments by adding the invoicePaymentsTEMP to the "real" invoicePayments, and then clearing the invoicePaymentsTEMP field.

[jeffer]

And here's my idea...

I have a database (invoices) with only an invoice number, client number, some numbers like total of order including and excluding VAT, a field with the paid ammound. When the paid ammount is lower that the total of the order...a calc field will present a 1. With that 1 i build a relationship that shows those records in a portal of an other database. When i select a record in that portal i can input a ammount in the paid ammount field. When that ammount is the same as the total of the order...my calc field will return a 0 and won't appear in the portal annymore. When it's more...it will create a new record in invoices with a negative ammount. When the customer has a new invoice...that negative ammount is subtracted from the ammount of the new invoice. When it's less...it will be seen as a 1rst/2nd paymend and the infoce stays open.

I hope that it's clear for you...i'm not very good in explaining these things:(

jeff

[jasonwood]

That makes sense... your method uses the same basic principle as mine

[BobWeaver]

In the long run, I think it's best to keep a customer balance in your customer file. The balance will be equal to total of all invoices minus total of all payments. This way you don't have to keep track of which invoice is paid or partly paid; or, if the customer made a mistake in the payment amount, what to apply it to.

Having a customer balance field also makes it easier to add finance charges on late payments. On a specified date each month, you make sure all customer payments are entered. Then you find all records that have a positive balance. For each of these, create a finance charge item which is equal to the balance times the monthly interest rate. This will be a charge item just like an invoice (you can even create it in your invoice file). Finally, add in the current month's invoices to create a final month end balance. Print the customers' bills, and mail them out. The interest automatically compounds from month to month, and you have a record for every charge (including interest) that appears in your customers' accounts.

[jasonwood]

This is great. You guys are helping to stimulate so many ideas!

Here are some of my revised ideas based on your feedback...

I agree that I would want to have a customer balance in the client file, but it would be mostly an FYI thing (and to ensure customers don't go over their credit limits).

I like the idea of keeping invoice by invoice records of receivables because that's often how clients will keep them. Often when payments are received, invoice numbers are referenced. I figure we should probably keep track of this so that we can assist in identifying where they went wrong (if that happens)... Eg: you missed invoice 23445... maybe it got lost in the mail... would you like us to fax you a copy?

I gave this some more thought today and I considered having both a payments database (one for each cheque rec'd) and a payment line items database (for each invoice paid).

In the payment database you enter cheque (check) amount, cheque number, name on cheque, date on cheque, etc... then in line items (portal) you enter invoice numbers you want to pay. This will lookup balances due on these invoices. Your payments go in a payment field, and when you click the "Process Payment" button, once it is confirmed that the totalPayments = chequeAmount, the adjustments would be applied to the invoices and the record would be locked.

Sure it's a few extra files but it seems worth it...

1) you would have an accurate record of exactly when various invoices were paid (and credits used);

2) you have extensive information about each payment;

3) you could allow a customer to pay an invoice from another customers account (eg: a company might pay for a personal purchase of a staff member that was later authorized to fall under business expenses); and

4)you could apply finance charges on an invoice by invoice basis while taking into account the date past due for each invoice.

[kennedy]

I am still playing with my design for this... but right now I am leaning towards:

Customer record contains account balance, based on list of all invoices/payments.

InvoiceItem is something purchased, the computed price, and the agreed-to sale price (IOW, overrides might be applied here).

Invoice contains multiple InvoiceItems and sums them, adds in the account balance for total invoiced, and then allows a payment to be applied. The majority of our business is "pay first", so allowing simplicity in 1:1 common-case seems good. Thus, the net applied to the account is the total invoiced minus the payment on that invoice...

Later payments, payments that aren't made at the time of invoice, are applied by creating an Invoice with no InvoiceItems. Thus, I don't add an additional file for payments... I just use the same ol' Invoice file.

Note, then, the account balance is essentially just the net for the last invoice for that account.

That's my current design... haven't implemented it yet though. Soon. Any issues I should consider?

[jasonwood]

That was precisely what my first idea was. I can't remember if there was any major reason why I changed direction, other than the fact that I liked the idea of tracking receivables on an invoice by invoice basis, and wanted clients to be able to pay invoices on other clients accounts (it doesn't come up often, but it's a possibility).

[BobWeaver]

I see nothing wrong with using a single file for invoices and payments. It simplifies getting the customer balance.

I don't like the idea of applying interest charges on a per invoice basis. I've seen several different schemes for doing this. They were complicated, unreliable, not very good at compounding the interest, and they didn't produce data that was easily transportable into an accounting program. If you think about it, interest has little to do with the invoice. It is simply a function of how much money the customer owes you and how long it has been owed. I would base it on the outstanding balance every month.

[Ugo DI LUCA]

I use a balance paiement in my Client DB.

As a first idea, I started with an account received in my Customer Order DB, and when invoicing, as the Customer Order N