• Registered: 03/21/2013
  • Posts: 4

Topic: Debtors and Creditors reporting

Having just reached my year end, I am having an issue producing what I would have assumed would be a couple of key and straightforward reports - that show debtors/creditors at the year end.

Using the 'aged creditors' report, for example, with an end date of the year end, it seems that an invoice that was issued before the year end and paid after the year end does not appear, even in the 'current' column.  I assume this is because it has (now) been paid, but is this the right behaviour?  Or am I using the wrong report here? 

It is possible to do this manually using the 'Supplier balances' report, but it surely shouldn't be necessary to do this manually.  Isn't a running check on debtors/creditors something everyone needs?  Or do such reports only work like this if they are run at the time, and not retrospectively?

Am I missing something obvious here?

Many thanks

Paul

2 Reply by dls (edited by dls 08/16/2016 10:18:17 am)

  • dls
  • Senior Member
  • Offline
  • From: UK
  • Registered: 02/23/2010
  • Posts: 63

Re: Debtors and Creditors reporting

I too am having this problem.

I have been directly entering some payments and deposits from the banking and general ledger deposits and banking. these payments/deposits have no direct invoices/supplier invoices associated.  At the end of year this causes a problem with easy retrieval of Creditors/Debtors.

By outputting the Supplier/customer balances to an excel file with Show Balances set by running the formula on each entry line

=(IF(AND(allocated=0,(OR(AND(credit=0,charges<>0),AND(charges=0,credits<>0)))),balance,0)

By totalling all the lines output from this formula and subtracting this from the original balance total this gives the Creditors/Debtors.

Initial checks suggest this works without error.

This method of input of payments/deposits without invoice works for me in my system as is, minimising extra work etc. However is there any other tidier way? I'm using Cash Accounting. Auto generation of a SI would be helpful.

  • From: Singapore, India, Chennai
  • Registered: 05/02/2009
  • Posts: 6,089

Re: Debtors and Creditors reporting

@dls: In your method of entering payments, there is no "allocation" done from it to the actual invoice. Hence the ageing reports cannot be fully computed in the standard method used in reports.

Hope you are doing all this on the latest Git version 2.3.25+.

apmuthu's Website

4 Reply by dls (edited by dls 08/16/2016 05:45:15 pm)

  • dls
  • Senior Member
  • Offline
  • From: UK
  • Registered: 02/23/2010
  • Posts: 63

Re: Debtors and Creditors reporting

Sure I understand. It was definitely a mistake on my behalf as I was getting to grips with the system.

My problem is that I have used invoices/SI where necessary and not where not necessary. For the most part this has been ok, and in fact worked quite well for me, but I now see the problem!! The unallocated invoice data from previous years is left in the system giving an incorrect opening balance on the Supplier/customer balance reports. ( I also input much data in a previous year alongside my old system as a trial) I have closed previous years and have the balance sheet etc now set up ok. However I now wish to clear the remains of this earlier data from previous years affecting the Supplier balances and Customer balances.

I wish to find out the best way to do this without affecting anything else. (the years in question have been closed and deleted) Is it a case of phpmyadmin and mass deletion of earlier dated entries in sup_trans etc?  will this cause other damage??  Are there any other counter posts to the database?

Also going forward - will I have to input Supplier invoices using the GL entry part and the subsequent payment of these for every transaction to track the Creditors and Debtors more easily? That equates to a significant amount of extra work than I have been doing. I don't use the inventory part of FA hence not using SI for every purchase. Is it possible to use the system as I have been and tidy at the end of each year with perhaps a JE to adjust the Creditor/Debtor values?

Has anyone else experienced this problem? Any advice would be much appreciated.

  • From: Singapore, India, Chennai
  • Registered: 05/02/2009
  • Posts: 6,089

Re: Debtors and Creditors reporting

If you do not intend to track Supplier Invoices at all (and also not bother to enter them with individual line items as you do not want inventory), just pass journal vouchers for the Supplier invoice gross totals and make payments directly into the supplier accounts and use the general ledger to track account summary balances for the suppliers.

apmuthu's Website

  • dls
  • Senior Member
  • Offline
  • From: UK
  • Registered: 02/23/2010
  • Posts: 63

Re: Debtors and Creditors reporting

Problem is I'm using some items - something of a 'half-way house' - is it possible to clear the older data from the closed years out of the supplier/customer tables cleanly without interfering with anything else?

  • From: Singapore, India, Chennai
  • Registered: 05/02/2009
  • Posts: 6,089

Re: Debtors and Creditors reporting

Yes by using mock payments and contra journal entries on account.

apmuthu's Website

  • dls
  • Senior Member
  • Offline
  • From: UK
  • Registered: 02/23/2010
  • Posts: 63

Re: Debtors and Creditors reporting

@apmuthu: Thanks for the reply - Could you expand on that please, i'm still not quite sure how to go about it.

I have the opening balances all set for the required year. I just want to clear all the data from the supplier and customer tables prior to the required year which have no allocation or other side to allocate to. supplier data represents about 300 transactions (payments to). The only customer data to clear is one customer who self bills - therefore each transaction is a bank deposit -12 transactions in the year.

Many thanks

9 Reply by apmuthu (edited by apmuthu 08/20/2016 01:46:35 am)

  • From: Singapore, India, Chennai
  • Registered: 05/02/2009
  • Posts: 6,089

Re: Debtors and Creditors reporting

Pass a single journal voucher to contra all supplier and customer balances (preferably as of the last date of the previous financial year) like for example for a single supplier:

By Supplier 1 Cr $1200 (mock payment)
To Suspense (or Cash) Dr $1200 (mock receipt)
# Allocate above payment to outstanding Supplier Invoice
By Suspense (or Cash) Cr.  $1200 (mock refund)
To Supplier 1 Dr $1200 (mock refund receipt)

apmuthu's Website