Aged Receivables - Separate Invoice Date from Payment Date in Aged Summary

[STUARTXL]

Hi,

Attached a severely cut down version of an Aged Receivables spreadsheet I put together; first tab is the Aged Receivables Summary and the second contains the detail behind said summary.

The issue I'm having is being able to allocate an over-payment of invoices to the correct ageing period. Note that an over-payment of $10,940 has been made by Client 2 (refer cell F13 on 'Aged Debtors Summary' tab). This is currently listed under the 30 days column, indicating that the over-payment took place between 30 and 60 days before the statement date. Note however that statement date (cell B2 of the same tab) is listed as 29/02/2016. Also note that the over-payment (cell H16 on the 'Detail' tab) took place on 22/02/2016, therefore the over-payment (on the summary) should fall under the 'Current' category (current means between 0 and 29 days of statement date - the definitions of each period are given in the table of the summary tab)

The reason the over-payment doesn't fall under the current category is because those figures in the 'Current' category are being pulled through from Column K from the 'Detail' tab and the dates that are used to allocate those Column K figures are the invoice dates (listed in Column E on the 'Detail' tab). I'm sure there is an easy way of remedying this but I'm on Excel meltdown at the minute

Aged Receivables Listing - Upload to EF.xlsx

[JohnTopley]

Are you saying that if there is a "Payment Date" it should override the "Invoice date" in your formula?

[STUARTXL]

No. Although this would work theoretically, it only works on the assumption that all clients make payments equal to the exact invoice amount. In practice, they can make overpayments and underpayments. So if a client were to be invoiced $10,000 for example and this invoice was 90 days old, then on day 91 that client makes a payment of $9,000, there is still $1,000 outstanding that is 90 days old. If we were to override the invoice date with the payment date, that would mean the $1,000 would then be re-allocated to 'Current'.

So the invoices need to be allocated to their own category ('Current', '30 Days' etc.) based on the invoice date, such that if a payment is made which does not entirely cover the invoice amount, the outstanding invoice amount still falls into the correct category based on the invoice date.

And if an over-payment is made, the amount of that payment which completely offsets the invoice should be allocated to the invoice date, so as to remove the invoiced amount from whichever category it sits in. Then, the remainder of the payment i.e. the surplus, should be allocated to it's own category, based on the date of payment.

[STUARTXL]

Bump no response

[JohnTopley]

Is this the logic:

If there is an overpayment in ANY period (current, 30 day etc) then for ALL periods we need to test against "Payment Date" rather than "Invoice Date".

If so, can we test against the TOTAL line to determine which set of dates to use: I,e. if total negative (one or more overpayment) then use "Payment date" else use "Invoice Date"?

If it is more complex than this ( and suspect it is!) then VBA might be required.

[STUARTXL]

Hi John,

Thanks for your help on this. The logic is:

- Each line on the detail tab shows a single invoice amount ('Gross' column), respective invoice date ('Date' column) as well as a payment amount ('Paid' column), payment date ('Payment Date' column) and due amount which represents the difference between invoice amount and payment amount. The due amounts are shown in column J but are separated by client in column K & L
- If the due amounts in column K & L are positive, then all or part of an invoice is outstanding and the balance should be allocated according to the invoice date
- If the due amounts in column K & L are negative, then an over-payment has occurred and the balance should be allocated according to payment date

At this stage, I am totally unfamiliar with VBAs and need to study these. Thankfully the above logic is sufficient to satisfy the immediate needs of the target audience of this spreadsheet, but your suspicions are correct, the spreadsheet will eventually need development if it is to accommodate more complicated scenarios e.g. duplicate payments for the same invoice spanning different periods etc. and VBA's will almost certainly be needed to achieve this. Just to be clear, the above logic is all I need for now.

Thanks once again.

[JohnTopley]

I'll have to digest this: you use SUMIFS based on the start/end date of the various periods against DUE amount but there is no test for positive/negative values so it is how to incorporate such a test in a formula. For Client 2 there presumably could be positive amounts i.e. overpayment less than total outstanding?

My head hurts too!.

[STUARTXL]

You've got it exactly right. And yes, client 2 could make over-payments on specific invoices whilst their overall balance remains a net positive/outstanding figure. To use an example, client 2 could have been issued 3 invoices (1 x $3,000, 1 x $4,000 and 1 x $5,000) on 15 January 2016. The total of these invoices ($12,000) would then fall under the 30 day category, assuming the statement date is 29/02/2016. But assuming the client makes a payment of $3,050 in relation to invoice 1, the negative amount (over-payment) of $50 should appear in the current column, whilst the outstanding amount on the two other invoices ($9,000) should remain in the 30 day column.

My attempt at incorporating the positive/negative test is below (this formula is specific to cell G13 on the summary tab) but for some reason it isn't picking up the overpayments:

=SUMIFS(Table1[Client 2],Table1[Date],"<="&DATE($S$5,$R$5,$Q$5),Table1[Date],">="&DATE($S$6,$R$6,$Q$6),Table1[Client:],"Client 2",Table1[Client 2],Table1[Client 2]>0)
+
(SUMIFS(Table1[Client 2],Table1[PAYMENT DATE],"<="&DATE($S$5,$R$5,$Q$5),Table1[PAYMENT DATE],">="&DATE($S$6,$R$6,$Q$6),Table1[Client:],"Client 2",Table1[Client 2],Table1[Client 2]<0))

[JohnTopley]

Try

In G13

=(SUMIFS(Table1[Client 2],Table1[Date],"<="&DATE($S$5,$R$5,$Q$5),Table1[Date],">="&DATE($S$6,$R$6,$Q$6),Table1[Client:],"Client 2",Table1[Client 2],">0"))+(SUMIFS(Table1[Client 2],Table1[PAYMENT DATE],"<="&DATE($S$5,$R$5,$Q$5),Table1[PAYMENT DATE],">="&DATE($S$6,$R$6,$Q$6),Table1[Client:],"Client 2",Table1[Client 2],"<0" ))

Result is

-10940

[STUARTXL]

Hi John,

I've only just managed to try this out. It works perfectly. Thank you.

[JohnTopley]

Thanks for the feedback. The old brain was rather slow on the uptake but you post #8 was the trigger so good team effort!