Overdue receiveables

Today is nothing new if you are not paid on time, although it is not a pleasant experience at all. You’ve done the job, and now you need to put in the extra effort to get paid. Excel can allow you to, in a very simple way, gain insight into all overdue receivables; to find out how much invoices aren’t paid, what are the amounts, which customers and how many days are late with payments. Based on this information, you can review your relationship with your clients.

We will start with a table containing the number, invoice date, client name and amount. It is assumed that only unpaid invoices are found here. To calculate the delay we need to add another column in which we will enter the formula:

=TODAY()-B3

This formula subtract the date of invoice from today’s date, so based on it, we get information on how late the invoice is. If the customer has deferred payment, e.g. 15 days, only at the end the previous expression you should add the deferred payment period.

Next, we create two tables, the first to track the total number of overdue invoices and the second to track the amount of overdue invoices. The header on the left should be the name of the customer, and on the top side the period of delay. In the first cell of the table above, enter the expression:

=COUNTIFS($D$3:$D$202;$G4;$C$3:$C$202;H$3)

which calculates the number of overdue invoices. Next, you need to copy the formula to the remaining cells in the table. To calculate the amount of invoices that are not paid in time, in the second table, enter:

=SUMIFS($E$3:$E$202;$D$3:$D$202;$G12;$C$3:$C$202;H$3)

We will repeat the process of copying formulas. Now you just need to apply conditional formatting to both tables to highlight problematic invoices and you will easily find out which customers are problematic.