1 / 9

Debtors’ Ageing Schedule

http://excel2007master.com/. Debtors’ Ageing Schedule. Duncan Williamson excelmaster December 2011. The purpose of a Debtors’ Ageing Schedule is to show how old one or more debtors’ invoices are. Usually, the invoices are shown as being, for example, From 1 to 30 days old

jude
Download Presentation

Debtors’ Ageing Schedule

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. http://excel2007master.com/ Debtors’ Ageing Schedule Duncan Williamson excelmaster December 2011

  2. The purpose of a Debtors’ Ageing Schedule is to show how old one or more debtors’ invoices are. Usually, the invoices are shown as being, for example, • From 1 to 30 days old • From 31 to 60 days old • … What is an Ageing Schedule?

  3. The reason why companies prepare and use ageing schedules is because the longer it takes a customer to pay their invoices the greater the chance there is that they won’t pay. • So, the more they know about their debtors the better! What is an Ageing Schedule?

  4. In this workbook I demonstrate FOUR ways of arriving at an ageing schedule for your debtors or accounts receivable • using functions and formulae • using conditional formatting • using a Pivot Table • using Excel Tables Aims of the WorkBook

  5. 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 Some of the Formulae

  6. To determine whether we are dealing with an entry between 1 and 30 days, 31 and 60 days … • to show the same results but in terms of days … • =IF(AND(TODAY()-$B14>K$14,TODAY()-$B14<=L$14),TODAY()-$B14,””) Some of the Formulae

  7. What about when an invoice is paid … ageing schedule? • =IF(D14>0,"",IF(AND(TODAY()-$B14>L$14,TODAY()-$B14<=M$14),$E14,"")) Some of the Formulae

  8. Using CONCATENATION for column headings • =K14&" - "&L14&" Days” … 1 – 30 Days … • BUT … Some of the Formulae

  9. Using CONCATENATION for column headings • BUT … with an Excel Table concatenation does not work with column headings Some of the Formulae

More Related