Retrospective SunSystems Aged Reporting - the historic problems and the solution

Running an historic aged debtors or creditors report has always been an issue for SunSystems. The problem is that the standard way of excluding items that have been paid is to exclude those with a 'Paid', 'Allocated' or 'Correction' marker. While this is fine when running reports for the current period, it fails when running reports for previous periods as transactions allocated against receipts in future period will be excluded when then shouldn't be.

For example, an invoice issued and posted to accounting period 01/2017 is paid by a receipt posted in period 02/2017. The invoice and receipt are then allocated against each other. Running the standard aged reports for period 02/2017 will produce the correct result, however running it for period 01/2017 will exclude the transaction when it should be included. The effect of this is that running aged reports for any period except the current one is very likely to produce a report that does not agree back to the total outstanding as per your balance sheet.

Historically, the advice is that these reports are 'point in time' reports and therefore you should run them at the end of the accounting period before moving forward to the next period. However, this is not really satisfactory as it means that users cannot begin allocating receipts in the new period until the prior period has been finalised, and it doesn't help in the situation where the auditors have asked for an aged report as at the year-end (which by then is several months ago) and the report wasn't run at the time.

The crux of the problem is that you need to take into account not only the allocation marker but also the accounting period of the transaction the original invoice was allocated against. In the above example, you need to know that the receipt was posted in period 02/2017 to decide where the invoice posted in 01/2017 should be included. This is not possible using regular Vision Q&A report or using reports created in the standard SunSystems report designer. There are however some workaround that may help, and one definitive solution.

The Solution - SSRS

Using SSRS gives the ability to query the data directly. This means you can design a query that looks at the posting period of the receipt as well as the original invoice (using the allocation reference which is assigned at the time of posting the allocation to assertain the other side of the allocation).

In this example, an invoice in period 01/2005 has been paid and allocated in period 02/2005. Running the report for period 01/2005 includes the transaction, with an allocation marker of 'A' for allocated.

On the standard Sun reports this transaction would be excluded, meaning the aged report would not agree with the balance on this account for period 1. Running it for period 02/2004 removes the transaction (as it has been allocated against a receipt in this period) but includes new transactions posted in this period.

Period 2

See here for more information about SSRS. Sun 6 users will already have all they need to run such a report, other Sun users may still be able to do so - again see the link above for more information about this.

The only limitation of using this approach is that if one allocation includes multiple invoices and receipts (or payments in the case of creditors) then it will have to take the period of the latest receipt to determine whether to include all the transactions or not. This is necessary as in this scenario it's not possible to determine which receipts are attached to which invoice, only that they net off as a whole. In everyday use I don't believe this is a situation that would arise, but may happen if receipts and invoice have been bulk allocated (most likely if an account hasn't been allocated properly historically and some has had to go in an allocated many periods worth of transactions).

Potential Workaround - Using the Allocation Period

When you post an allocation in Sun, it gives it an allocation date of the current system date and an allocation period of the current period. You can in Q&A use this to determine whether a transaction should be included. In the above example, the allocation of the receipt should be done when the accounting period is set to 08/2017. You can then create a Q&A formula to include only transaction that have either a blank allocation period or one greater than the period you are reporting on. This will work, but does mean the users must be aware of the current accounting period when doing their allocations. This field is not amendable, and so if a mistake is made users will have to unallocate and reallocated with the correct current period which can cause problems.

If you have any questions or comments about aged reporting in Sun, or you think the above report may be useful for your organisation get in touch at andrew@zoanda.com.

Back to the home page