Numerous invoices on orders - averaging by chronology

[vangogh228]

We have a billing scheme in which we partial-bill ceratain customers based on certain events: 30% when they sign the contract, 30% a number of days later, 30% when we ship the goods and the last 10% upon installation. This is a common process, known in many indistries as 'progress billing.'

What I am trying to do is compare the collection performance on the invoices based upon which trigger sent them. For example, I'd like to find the average and standard deviation (plus some percentiles) of the time it takes to collect invoices that are sent... when we ship, let's say.

I have a table that shows order number, invoice number, invoice date, amount and payment date. So, I can have the order number show up to 4 times with a chronology of invoice dates.

My question is this: How do I group for analysis all the FIRST invoices, the SECOND ones, etc...?

Thanks for any help! I can do this in excel... but I was given this extract of 100000+ lines, and hope to do it all in Access. Thanks again.

 

[gemma-the-husky]

so how are your progress claims structured? are they in a seaprate table, or do you have spreadsheet=like columns in your project table?

 

[The_Doc_Man]

It seems to me that the best way to do this is to have a billing table that links back to an invoice and shows a code (could be 1, 2, 3, 4 ... that simple) for the first, second, third, and final bill status. You would be able to include dates in the billing table for bill issued, pmt. received - if that's what you wanted. NOTE by way of clarification - to do this "right" you would have four billing entries per invoice. I am NOT suggesting that you have one entry per invoice and update it. This latter design quickly leads to normalization issues and hides payment / credit history. You might not want that.

Since a lot of your billing information could be easily derived from the original invoice, all you would need in this table might be the invoice number, code (and these two would be a compound prime key), date billed, date paid, amount billed/paid. Fairly small. UNLESS you allow customers to make partial payments not exactly matching the billed amount. Like, if your customers have more than one bill outstanding (say, each one in different progress) and they give you just one check for ALL currently due bills, it is up to you to sort that out. This would then fall back to carefully designing your billing model before attempting to apply the payments. Particularly if they owed $10K and you only got $9.5K.

The way my former employer handled this was to always apply the newest payments to the oldest outstanding bills, in bill-date order, until the money was used up. We kept running totals of bills ordered by date due, included a check-mark for "paid" for anything that a payment covered, and if a payment DIDN'T cover everything that was due, we didn't mark the item as paid. We left the unused amount in a special "synthesized" partial payment and marked the oldest unsatisfied bill as "partial pay." That way we minimized our customer's late fees.

However, your business model might also define that a payment is always marked as "towards invoice # nnnn" - in which case you apply the payment only to that invoice. But the same problem could apply if it was a partial.

(Or they could just say the same thing the IRS says... if you don't pay your bill in full then the penalty applied to the bill is based on the original amount, not the unpaid balance.)

Needless to say, this is a tricky part of any business model and your management team needs to carefully define the rules before you attack the code and queries required to support this problem.