I am a finance consultant for small nonprofits ($300k to $3million). I am working with a client now who issues customer credits and sells gift certificates, but they haven't devised a good way to track either. Their balance sheet shows liabilities in both, but I'm sure they aren't right. I'm trying to determine the best way to calculate a reasonable total for customer credits and how to alter the system to begin tracking correctly as they move forward:

Here is the situation: The organization sells events / experiences. The price point is $100-$400, so there are many small transactions. The customer's records are kept in a Filemaker database. When a customer signs up for an experience, it's entered in the customer's record as follows:


Experience name: "Great Experience"
Price: $350
Memo:

When the experience is canceled and the customer is given a refund, the following is entered into the customer's filemaker record:

Experience name: "Great Experience"
Price: -$350
Memo: Refunded to credit card

And, when the customer chooses to take a credit instead of a refund the entry looks like this:

Experience name: "Great Experience"
Price: -$350
Memo: Credit

So, as you can see, the difference between a refund and a credit is simply a note in the memo field. They are not discernible in Filemaker without looking at each customer individually, so there is no way to do a cumulative report. (The same is true for gift certificates.)

To make up for this lack of information, the finance team has been entering the name of EVERY customer into Quickbooks and recording the refund or credit. Quickbooks, therefore, can give a cumulative total, but this much data entry is inefficient and annoying, and there is still no way to reconcile Quickbooks with Filemaker.

There are two problems here. The first is calculating how much credit is outstanding now, and the second is devising a way to track it in the future.

To calculate what is outstanding now, I thought I'd start by getting a sense of how accurately Quickbooks and Filemaker match. As a test, we looked up in Filemaker 20 customers that Quickbooks says have a credit. Of those 20, 8 did not match the data in Filemaker.

I wouldn't be able to say for sure which system is more reliable. My initial assumption was that Filemaker is more accurate since they are dealing with the actual customer and are the first point of entry, but as we were going through each of the 20 customers, the registrar would say things like, "Oh! this wasn't entered correctly," and "Oh, I see. They used to do it this way, but now we do it a different way."

Is there an acceptable way to calculate a ballpark and do a journal entry to "right" the balance sheet? I'm sure that whatever I do will be more acceptable if there's a system put into place that will accurately track in the future. Ideas for that? Filemaker is slated to be replaced, but it will likely be 2011 or after.

- mary