Creating a customer statement of account

[jasonwood]

I am planning on developing my new database using A) an invoices database (mostly for increases in balance due), and : a payments database (mostly for decreases in balance due).

I am wondering if there will be any way to generate a statement of account that would list all invoices and payments in order by date. Since it would involve a listing of records from two databases, I'm not sure how to approach this.

Thanks!

[Ugo DI LUCA]

Hi Jason,

Payments are processed using a Line Item for Payments. The Invoice N

[jasonwood]

I don't think that helps me. Here's what the final product would look like.

Statement for client 1000:

2003/06/01 Invoice 10102 $1000

2003/06/01 Payment towards invoice 10102 - $500

2003/06/02 Invoice 10131 $2000

2003/06/12 Payment towards invoice 10102 - $500

2003/06/12 Payment towards invoice 10131 - $2000

Client balance due: $0

The only problem is that invoices and payments are two separate databases, so how do I list them interchangeably?

Sure payments relate back to their invoices, but I can't simply show the original invoice alongside each payment. Their may be more than one payment per invoice, plus they could be on totally different dates.

[kennedy]

Jason,

Here's the approach I designed for my system. Caveat: I haven't implemented this part of my system yet, so I may yet change my mind.

An "invoice" is when you ask someone to pay an amount... you invoice them for all that is due. What is due is the balance remaining from the previous invoice (which could be a credit), plus the InvoiceItems for each service or product they have purchased from me. They can then make a single payment against that Invoice. if they later make an additional payment, I will auto-generate a new Invoice for the remaining amount against which that Payment is applied.

This gives me a very regular structure; it gives me a natural Invoicing mechanism; and it implements the customer account right in the Invoice, preventing the situation you describe. I think it will work real well... but as I said, I haven't gotten around to implementing it yet. (I need help.)

So, Invoices will typically be generated whenever they purchase a set of InvoiceItems, whenever a billing cycle comes around and they have a balance due, and whenever a payment is received. The former case is obvious and expected. The second makes sense when you think about the fact that it is important to document each time you have sent out a request for money from that customer. And given that, the third only occurs when you get a second or unexpected payment.

You could allow an Invoice to have multiple payments, but then you'd need a third table: Payments. And the math would get way more complicated. That would be justified if multiple payments against a particular invoice was important to track that way. But do you really care which invoice the customer thought they were paying with a particular payment? Or do you just need the payment to get aplied to the account? For me, it is the latter. So, it much simplifies everything to have the Invoice both compute the balance due and record the single payment and the net after payment.

So, in terms of your records, your Invoice is my InvoiceItem. N of those are summed up by your Payment which is my Invoice. Tad confusing since we're using the same names for different things... but I hope you followed all that.

Hope that helped,

Brian

P.S. I sent you email yesterday.

[BobWeaver]

"Pinky, are you pondering what I'm pondering?"

"I think so Brain, but how do we get the chicken to wear the fishnet stockings?"

......errr.....actually what I was pondering is: Should invoiced amounts and payments be in separate files, or the same file? I've never come up with a good argument for one over the other. Maybe in your situation, you could use a single file for both. Otherwise, you would need some kind of join file.

[Ugo DI LUCA]

Jason,

The PaymentsLineItem I was pointing was in fact a PaymentsToInvoice s , just as Brian is planning it. If you were only keeping track of payments related to one invoice, there would be no need for a second Payment file.

Brian,

Go confident with the latter. It never failed in my system since I implemented it. It even works in a many many to many where my customer is also my supplier and my payments to him are balanced to what he owes me.

The only caveat is that it is totally relational at the moment, caclulating accross related records. I should switch to a scripted method, keeping the "calculation accross records" just for the "Set Field" script step.

[Ugo DI LUCA]

Bob said...

Should invoiced amounts and payments be in separate files, or the same file? I've never come up with a good argument for one over the other.

This is surely one of the more complex setting to decide. It also depends upon the kind of business and payment procedures.

But the business evolves and strict procedures are almost the best way to run your business dead. There's a tight balance though from "Accounting needs" and "Business Needs". That's where FileMaker comes with multiple solutions.

The best one, IMO, is the softer, adapted to any business evolvement. I'd go for a Many to many relationship even if 99,9% of my payments were to be made over each invoice.

Now, if it is for sure that payments won't be splitted on multiple payments...

[jasonwood]

hmmm...

lots to think about here.

I have a bit of an account background and I have a persistent voice in the back of my head that is keeping me from doing it all in the Invoices database. Now if only I could figure out why...

For some reason I feel it's really important to know which payments apply to which invoices.

Perhaps I'm making this too hard on myself. Rather than having Invoices for increases in balance, and payments for decreases, I could make "payments" into a "transactions" database.

In other words, whenever I process an invoice it would create a debit entry in "transactions", then each payment would take place as a credit entry in "transactions". The only thing to worry about is making sure that a script always runs to ensure a corrosponding transaction is created for each invoice.

Then the transactions database could easily be used to generate a statement.

[LaRetta]

Hi Jason,

I agree with you that "For some reason I feel it's really important to know which payments apply to which invoices." In fact, it is common business practice. You can't get a true aging for the accountants without tracking Invoice and invoice date vs. Payment and payment date.

If you use the 'apply to specific invoice' method, you will need to establish some business rules on exactly how any mismatch between payment and invoice is handled. It could be applied to the next invoice or, applied to the client's account by using an Adjustment GL#.

And many times, a client won't specify which invoice they are paying (or haven't returned the payment slip which shows the invoice number) and, if the amount doesn't match an existing outstanding invoice, you're in trouble. And sometimes a client will pay 'on account' a flat sum.

These are all business rules considerations more than design questions. Once these questions have been addressed, the structure and scripting will be much easier.

A meeting with Management and Accounting which addresses every possible scenario, documenting their decisions from that meeting, presenting (and getting approval) on the final invoicing rules, will save you from disaster.

For instance, when being audited, the first thing the auditors will ask is to see payments per invoice. If your design isn't structured in this way, you may run into problems.

LaRetta

[Ugo DI LUCA]

Hi Jason,

Your "Transactions" db would surely be a "PaymentsLine Item".

Now, the way you enter records in this File would not necessarily change its purpose.

Though, a scripted method would be more efficient as it doesn't involve cross calculations.

You may also "tag" an "Invoice Id field" in this file if the payment equally correspond to the Invoice Value. This way, you could have listed those invoices paid in once and those paid in several times.

But again, it won't change the purpose which is to have a "Statement of Account".

[jasonwood]

A meeting with Management and Accounting which addresses every possible scenario, documenting their decisions from that meeting, presenting (and getting approval) on the final invoicing rules, will save you from disaster.

I am management... and I am accounting. I'm also sales, purchasing, finance, and customer relations too! Doh!

This is why it helps so much for me to bounce ideas off the forum. I haven't made it completely through the thought process, but I'll be basing my new system on my last message.

Thanks for the insight!

[LaRetta]

Hi Jason,

"The only thing to worry about is making sure that a script always runs to ensure a corresponding transaction is created for each invoice."

My personal opinion, for what it's worth, is that Invoices and Payments should be kept separate. Your transaction db only needs to contain InvoiceID, PaymentID, CustomerID and the corresponding debit/credit GL#'s charged to pull everything together (+/-) for Statements. Transactions actually is your AR/GL. All other information can be referenced from your Invoices & Payments dbs.

Statements should be generated from Customers db joined to transactions.

Writing to Transactions can take place at any point but, if you only allow scripted 'new records', writing to Transactions can be scripted upon creation of a new record in either Invoices or Payments. After writing to Transactions, the related record should be flagged with a ProcessedDate and TransactionID for reference purposes. This can be one field (text) containing ProcessDate & " " & TransactionID.

When month-end is processed, the corresponding records in Invoices and Payments need to be frozen and modification of any kind disallowed.

But you also need to decide what to do with adjustments - people make mistakes! If an Invoice is incorrectly billed, an adjustment invoice should take place in Invoices (Invoice# & "A" for instance) because, otherwise, you won't get a true picture of sales. It's nit-picky but can make a difference over a year's period.

Payments will also have adjustments - a customer is given a discount or an invoice is written off. If an invoice is 'forgiven', you won't want to adjust the Invoice because that will throw off your sales (actual services provided).

You can that your record(s) are written to Transactions (processed correctly) by testing via relationship (using IsValid or Count) on InvoiceID and PaymentID). It's also good to run this validation check right before closing the month (and again at year-end). Transaction records should never be modified, added to directly, or have its records deleted. Invoices & Payments (and their corresponding adjustment records) flow in only. This means that you will need an 'internal' customer for adjustments not relating directly to a regular customer.

If you need to void an invoice, it should still remain in Transactions and an adjustment invoice (reversal) should be created. Auditors actually foam at the mouth if there is a missing invoice number (not a pretty sight).

I don't envy your task ... been there, done it. And I did it wrong and had to restructure it - real pain in the ___!!

AR is a complicated process - that's why most end up buying a third-party program but, the benefit of using FM is that the entire Agency data structure is related - which allows drill-downs to it's finite level.

I wish you the best with it.

LaRetta

[Ugo DI LUCA]

La Retta,

Agreed with the basis of this structure, which is also what I recommanded.

This kind of structure should also allow to distribute payments according to their typology, that is a down payment for any Customer Order would also be processed the same way as an Invoice Payment.

What render the scheme difficult to reproduct is the possible Many to Many relationship from One Invoice to Many Payments.

Here's what I'm doing :

When a Payment is to be registered, the user is prompted to enter the Customer Name and the Amount paid.

He is presented with a portal linked to the Line Item, summarizing each Customer Orders and Invoices "opened" for that customer.

A single checkbox allows to "Input" the payment to a particular invoice, or any customer order in case of a down payment.

If the amount is equal to an Invoice, this invoice is "closed".

If it is superior to the total amount of one particular invoice checked, the residual amount is automatically processed to the next one, so that the first invoice is "supposingly" closed. In fact, it will be definitely closed when the next invoice due will be covered by another payment.

Now, when running my month-end report adressed to each (professional), I still present the Customer with :

- List of Invoices Opened (even those paid with a superior Amount)

- Total Due

- List of Payments

Amount, checks N

[LaRetta]

Hi Ugo,

I also have checkboxes to select which invoices are being paid and it pre-fills the amount field (which Users can overwrite). User enters check# and total amount paid and a running total is displayed above portal in a count-down fashion. But I didn't have it set up to automatically apply the amount forward. Thanks for the idea!

There may be cases in which this wouldn't work. For instance, if a Customer disputes one invoice but pays the rest. In which case, the User would have to delete the payment from the subsequent invoice (or many invoices) - but I see that as a minor thing (?) I'll have to chew on that a bit before I decide, as our 'Customers' can be Medicaid and be paying 30 invoices for one customer in one whack and they frequently dispute an invoice!

As for presenting a Customer with more than one piece of paper showing their 'account activities (if I understood you right),' well ... we here in Oregon are tree-huggers, ya know! If a customer only has one invoice and one payment ... well, it could be a bit wasteful. And from a economic standpoint, these types of wastes iritate me. That's why I love FM.

But these things are more Interface considerations than Design issues. Structurally, we're talking about the same thing and that's what Jason wants to know about.

LaRetta

[Ugo DI LUCA]

La Retta,

My english limits...

The report is sent to the customer when he owes something. What is this called ?

About a customer disputing a payment, for the next invoice.

My invoices have a due date.

The portal is sorted by due dates.

If he is disputing a payment, I'll report the due date. Therefore,this invoice would be skipped, and the residual payment applied to another one.

There may be other method, as a flag.