Debtors (Accounts Receivable) Ageing Schedule + Video

I updated this page on 29th December 2015 and updated the links to he video and the Excel files.

I recently ran a course on Debtor (Accounts Receivable) 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.

I have also put together a video that walks you through the functions and formulas part of this work book … with previews of the rest.

You can download the video from here 

If you want the excel file, download that from here: ageing_sched

Please note, I used Excel for the mac 2011 in this video … don’t worry as you can open it in Excel for Windows as normal.
Please let me have your feedback, to info@excelmaster.co.uk as mentioned at the end of the video.

Duncan Williamson