[GNC] Disconnect between balance on client list and account statement

[Adrien Monteleone]

That is a challenge. I don’t speak Spanish, but those translations via translate.google seem to check out, though I’m a little fuzzy on the first two, but since the first one has Clientes as plural and that report indeed does report on all customers, and the second as Estado does transalate to Report, I’d say you’ve identified them correctly. To be sure I’d have to check the translation file. (If this is a problem, I’ll dig it out, but I think these are correct)

For completeness:

The Customer Overview shows for all customers:

Company Name, Customer Number, Address 1, Address 2, Phone + any additional columns you add. (Balance is one of the possibilities)
Nothing in this list is clickable. But you can right-click a customer and perform a few maintenance functions as well as run a Customer Report.

The Receivable Aging report shows (by default) only for customers with non-zero balances:
Company Name (as a clickable link), Current owed balance, and the balance due that is aged 0-30 days, 31-60 days, 61-90 days, 91+days, and the total, along with a total line for all customers for each aging period and a grand total. The totals for each customer are clickable. That brings you also, to a Customer Report.

The Customer Report is a table report one line for each invoice and payment with:

Date, Due Date, Reference Number, Type (invoice or payment), Description, Debits, Credits, and Amount.

At the bottom there is a Period Totals line, a Total Debit or Total Credit line (depending on which it is) and an aging summary which should repeat the info from the aging report, but just for this customer. This is what I would refer to as a ‘Customer Statement’ but is termed ‘Customer Report’ in GnuCash. In the Reports > Business menu, it is probably the first Customer report on the list. The ‘Customer Summary’ is something else entirely and is designed to show chargebacks from Vendor invoices for particular jobs.

The Customer Report also has a header with the Customer’s address and contact info.

---------

It is possible when you moved the payment, you needed to re-apply it to the proper invoice. If you attempt to ‘Process Payment’ for this customer, do any unpaid invoices appear? Do any pre-payments appear? Also, I’m not sure an income:returns account was the right account to use. The payment should have been received to an asset. The invoice itself was booked against income. Processing a payment this way would reduce income erroneously. If this would throw off your bank balances, then that real payment is somewhere in the history. (otherwise, your account would be short by the amount of the payment) Rather than entering it again, you need only assign it as a payment to the invoice. (use the right-click menu on the payment transaction - this will be similar to ‘Process Payment’)

Also, you mention you experimented with a credit note. Did you zero out and unpost the credit note? If you had posted it and applied it as payment to an invoice, it might still be applied there and so you aren’t seeing an invoice to be paid. I would recommend creating a placeholder customer, say CustomerABC, and assign the credit note to this placeholder after you unpost it. Delete all lines in the credit note. Zero or blank anything you can that GnuCash will allow and enter placeholder info for the rest. Do NOT repost the credit note. (it can’t be deleted either, but it can be reused and reassigned later if you need one.)

I’d go back over the customer’s history from whatever other records you have and walk through each invoice and each payment and make sure they all line up. At some point, you’re going to find either a payment in your cash/checking that wasn’t assigned as a payment to an invoice, or that the credit note made a mess still to be cleaned up, or a duplicate invoice.

Again, be sure your start and end dates on the reports you are comparing are identical. (to make sure the same transactions are included in the totals) The Customer Overview (presumably Previsión de Clientes) Balance column may or may not be correct. I never use it though mine does agree with the Aging Report. If the other two reports (Aging Report and Customer Report) agree with each other, I’d go with those and not worry about the overview.

Finally, you note that when you moved the payment this resulted in a credit balance for the customer. But if the customer is paid up as you noted in the first post, their balance should be zero, neither credit nor debit. Really, I’d delete that payment entirely, find the actual payment in your asset history, and apply *that* transaction as a payment as noted above. IF this payment occurred before the opening date of your book, then there’s another solution for that.

Regards,
Adrien


> On Aug 14, 2018, at 10:40 PM, Roger Oliver <roger at olivermx.net> wrote:
> 
> Adrien,
>  
> A challenge here is that the GnuCash I am using is in Spanish, that is the accounting terms and reports and in Spanish so I’m guessing at what the names are in English. I couldn’t figure out which report was the aging report because of that. The best I can guess is the following:
>  
> Previsión de Clientes = Customer overview
> Estado de cliente = Customer Report 
> Calendario de cobros pendientes = Receivable aging 
>  
> This last may or may not be the receivable aging report. They are not in the same order on the menu. 
>  
> I tracked down an open invoice that had not been paid. Somehow in the switch to GnuCash from Quicken we missed a payment. Posting a payment to a current asset cash/checking account would have messed up those balances. Nevertheless, that is what I did by accident. 
>  
> I moved that payment elsewhere, to an income/returns account so it would not effect the cash balances. That fixed the balance on the Customer Overview but left a credit balance on the Customer Report. The calendario de cobros pendients (Receivable aging?) report shows the customer with the same credit balance as the Customer Report/Estado de Cliente even though the Customer overview shows the correct balance. 
>  
> I had tried this before with the same result. I even tried issuing a credit note using it to pay the open invoice and got the same result. Either the Customer Overview shows the correct balance or the Customer Report shows the correct balance but I can’t get them to match. All the other customers match. I’m missing something but can’t figure it out for the life of me.
>  
> Thanks for the effort,
> Roger
>  
> Message: 8
> Date: Tue, 14 Aug 2018 20:30:31 -0500
> From: Adrien Monteleone <adrien.monteleone at lusfiber.net>
> To: Gnucash Users <gnucash-user at gnucash.org>
> Subject: Re: [GNC] Disconnect between balance on client list and
>                 account statement
> Message-ID: <91A3E255-86DA-47DB-92EE-528EC976BF17 at lusfiber.net>
> Content-Type: text/plain;             charset=utf-8
>  
> I think this is fixable. You should not need to create a new customer account.
>  
> By ?Customer Statement? I?m presuming you mean the ?Customer Report? correct?
>  
> If so, change the dates to reflect the entire time she?s been a customer. If everything is paid and there are no remaining pre-payments (deposits) it should balance to zero. You can check for remaining deposits by trying to Process Payment on her account. If any pre-payment still shows up, then it has not been applied. (same with any invoices that show up)
>  
> Now, run a Reports > Business > Receivable Aging report. It should show she has a zero balance. If not, double check the options to make sure the ?To? date matches the end date of the Customer Report. (usually ?Today?) These numbers *should* match what is shown on the Customer Overview Balance column.
>  
> If when these dates match, you still have non-zero balances, then there are one or more invoices posted for her that aren?t paid. You?ll have to track them down. Do an invoice find with her as the customer and check them all to make sure they are paid. (one possibility is you might have a duplicate somewhere.)
>  
> If the balances are zero when the dates match, but non-zero when you extend the ?to? past ?today? then you?ve posted a future invoice to her account and your report option for the Aging Report (and possibly the overview does this as well) is set to that later date. She might be current ?today? but based on what is posted, does (or will) owe $1500.
>  
> So the end result is there is likely a misapplied payment or not-applied payment, or a future dated invoice. If you posted and un-posted an invoice, it is possible the payment was not successfully re-applied and you need to re-associate it to the proper invoice.
>  
> Regards,
> Adrien
>  
> > On Aug 14, 2018, at 7:38 PM, <rmomxtx at gmail.com> <rmomxtx at gmail.com> wrote:
> > 
> > I have a customer who ended the last fiscal year (school year) with a zero
> > balance owed but still shows owing $1500 in the customer overview but her
> > statement shows zero balance. I use the customer overview to manage overdue
> > accounts by sorting according to the balance due. In her case it should be
> > zero but isn't. If I give her a credit note the customer overview will show
> > the correct amount but the statement will be off by the same amount. She
> > paid ahead last year and I posted the total amount she paid against the
> > invoice due at that time leaving a balance in her favor. I paid each
> > following invoice with the credit balance. In every other case this works
> > perfectly. 
> > 
> > 
> > 
> > I can't find where the error is or how to correct it.  If I can't figure out
> > how to correct it, one option is to deactivate her customer account attached
> > to last year's invoices and create a new account for her and start all over
> > this year.