Prepayments not showing in Debtor (Clients) Ageing Report

Continuing the discussion from Debtors ageing report re:Payment on account:

Glen, I have a couple of clients that have overpaid and those payments are held on their client accounts as prepayments, however the Debtor (Clients) Ageing Report does not show these in the CR Balance column. (that this topic covers)

Can the “Sales > View clients > Client Management” page show these over-payments as a negative in the Account Balance column?

The ageing reports are primarily designed to show ageing debtor/creditor invoices, they just happen to show any payment on account balance too, but only for those cases where there are aged invoice balances.

So what you’d need to do is in the Client Management screen go to Advanced Search and select the check box to show credits. You can then order the credit column by size and see all your clients with account credits, largest to smallest.

Finding Account Credits

That’s what I have to do but it’s s pain when checking clients accounts on Afinity as overpayments do not show anywhere without searching, On Sage they were included in the clients account balances.

We keep payment on account balances separate from the invoice balances so it’s easy to distinguish the two. You can view these two separate totals by going into the client detail screen:

You can also see all unallocated payments for the whole account on the Payment View screen:

If you need a point-in-time total of prepayments, the historic invoice report will show this.

I’m happy to consider ways we can improve this, if you can detail a more convenient place to show this type of information.

Keeping over-payments separate for both sales and purchases is good at the client/supplier account level.
But with over-payments being ignored in the “My Dashboard” “Balance at a Glance” both those balances are giving give the wrong impression of what is really owed.

Currently they can show £0 for "money owed to you), and £0 for “money you owe”, yet you may be owed or owe lots, due to over-payments by clients, or over-payments to suppliers.

The Dashboard should included over-payments for the two “Balance at a Glance” amounts in Red&Green

Also in Client management the “Account Balance” should include this also give the wrong impression that nothing is owed by or to the business.

Here is an example, The Dashboard show £0 for both owe and owed so no need to look further…

When you view Client Management all clients show an account balance of £0 again all is well nothing needs checking…
Yet when you use the search and tick display the 3 columns, then sort them to show highest credits we see that we actually owe clients money. Here is the top one:

The above screenshot shows the total invoiced as £248.56, total received was £369.13 leaving a credit of £120.57 (which was an over-payment) but the Account Balance is £0, when its actually £-120.57

If over-payments were included as above the dashboard would first alert you, and then the client/supplier management would show you which clients/suppliers accounts to view their full record of invoices and overpayments

I think if we suddenly started showing client prepayments under the dashboard “Money You Owe” total that would confuse a lot of people. Most regular QF users wouldn’t see client payments on account as being owed, even though technically they are a liability.

We could possibly separate it out onto another dashboard heading, but there’s a bit of work involved there so it would be better to get more support from other users on this before we looked at extending this.

Perhaps it’s a bit more confusing in the sense that the label is “Account Balance” when actually “Invoice Balance” would be more accurate.

Rather than expanding the default dashboard totals couldn’t you just expand what you can get the customised dashboard totals to show?

That also crossed my mind “Invoice Balance” is a better description
and if you could add the prepayments to the dashboard that would suffice
maybe something like this with appropriate links to the source of the amounts

and maybe make the credit column in the Client & Supplier Management pages a default setting
so the whole picture shows without having to do an advanced search

We already have enhanced dashboard totals, but these pull in nominal balances. If we were to do this we’d need to extend the logic here to cover prepayments which aren’t distinguished by nominal code. This means it needs to do separate computations to work out the prepayment balances.

It’s definitely possible, just a bit of work involved and it’s not something that I’ve seen requested on the forum before… I may be wrong though!

Some of the above discussion was discussed in the thread here:

Hi Glenn/Mathew

Having seen the historical invoices report (recently), which seems to be more or less duplicating 'outstanding invoices; under SALES.

I feel the historical invoices is really a debtors report (the one that I have been asking for for a long time!).

The report is called historical invoices, but these are historic OUTSTANDING invoices only + the prepayments.
Which in accountant’s books is a debtors report (nothing to do with ageing) As you said, this report gives you the breakdown for the sales control account - which is exactly what a debtors report should be about.

Would the QF team consider renaming this report to Debtors report?

I am also wondering about the feature below, will it be implemented? Thanks

Many thanks

It’s not quite a duplication of the outstanding invoices view as it can be modified to show debtor / creditor position at specific dates.

Yes I agree it probably should be renamed, although “Debtor / Creditor Report” would be more fitting I think.

Regarding the statement, I did mention the following.

We may be able to achieve this in a slightly different way. One approach would be to include a separate box under the itemised section to list out any unspent prepayments, e.g. dates and amounts.

I don’t recall seeing a specific reply to that, but would that be helpful?

Hi Glenn, I think I replied to your point but maybe not very clearly.
A breakdown of prepayments which includes date of prepayment, amount and the client name will be VERY helpful.

However, one of the break down in my screenshot was showing that it was something from BANK. This i think is not helpful. This issue can be replaced if under the tagging of the refund, there is an option to allocate the refund to a client account. The system can then populate this refund int he above summary to a client account rather then BANK.

Thank you

Perfect, thanks!


OK we will look into this shortly. We are tied down for the next few weeks with updating some of our hardware and network infrastructure. After this we should be able to take a closer look.

That will be great. Thank you

i agree that some of these changes would be beneficial…

i see all the same issues…


1 Like