Debtors’ (Accounts Receivable) Ageing Schedule in Excel
12/12/2011
I am preparing for a course that I will be running next week and since the major topic is Debtor Management, I felt it would be good to develop an Excel file to prepare an ageing schedule.
Once I had started I decided I could prepare a variety of solutions:
- using functions and formulae
- using conditional formatting
- using a Pivot Table
- using Excel Tables
Here are some screenshots that help to illustrate what I have done and after the screenshots, I show you some of the formulae I used in the development of this file.
In any case, this workbook does two things:
it allows pupils to consider a real ageing schedule albeit with a simplified example and to change the payment boundaries: for example, invoices owing between 1 and 30 days then 31 and 60 days … you can change it to 1 – 25 days then 26 – 50 days … and any range of days you like. This will help you to explore the credit control strategy and so on. It leads in to cash forecasting and budgeting too.
To determine whether we are dealing with an entry between 1 and 30 days, 31 and 60 days … =IF(AND(TODAY()-$B14>K$14,TODAY()-$B14<=L$14),$D14,”") … this gives the answer in values
to show the same results but in terms of days …
=IF(AND(TODAY()-$B14>K$14,TODAY()-$B14<=L$14),TODAY()-$B14,”")
Showing the status of a debtor’s balance: overdue or current …
=IF(TODAY()>C14,”Overdue”,”Current”)
Similarly, finding Overdue and Current allowing for the payment of an account …
=IF(D14>0,”",IF(TODAY()>C14,”Overdue”,”Current”))
Then there are the functionalities of Conditional Formatting and Excel Tables
The Pivot table, which I don’t show in screenshot form above even includes the suppression of zeroes.
An apparently simple problem but with some interesting programming to do.
Duncan Williamson
