How to Create Debtors Ageing Report in Excel Format

Get FREE Advanced Excel Exercises with Solutions!

Excel is a powerful software. We can perform numerous operations on our datasets using excel tools and features. There are many default Excel Functions that we can use to create formulas. Many banks and other financial institutions use excel files to store important data. They need to calculate various outputs and it’s easier to do that in excel. This article will show you the step-by-step procedures to create Debtors Ageing Report in Excel Format.


Download Practice Template

Download the following template to practice by yourself.


Introduction to Debtors Ageing

The tool by which business companies or financial institutions track the status of their accounts receivable is called Debtors Ageing. This is usually presented in a report format that groups outstanding invoices by customer and date range. Again, by accounts receivable, we mean the value of everything a debtor owes to a business in exchange for goods and/or services the business supplies. Moreover, companies set a credit limit and during this limit, debtors are supposed to pay their debts. Ageing starts when the payments get delayed upon this credit limit.


Step by Step Procedures to Create Debtors Ageing Report in Excel Format

In this article, we’ll show you the easy step-by-step guidelines to create a debtors ageing report in an excel worksheet. We’ll apply some useful functions to make our report dynamic. Therefore, go through the below steps carefully to complete the task.


STEP 1: Input Data in Excel

  • First, we need to input accurate information about Customers.
  • In this example, we’ll insert names and other essential data.
  • Look at the below picture where you’ll see the dataset.

debtors ageing report in excel format


STEP 2: Find out Due Date

  • Now, we’ll calculate the Due Date.
  • For this purpose, select cell I5.
  • Then, type the formula:
=D5+F5
  • Subsequently, press Enter.
  • Next, use AutoFill for completing the rest.
  • Thus, it’ll return the exact due dates.


Similar Readings


STEP 3: Determine Outstanding Amount

Similarly, you need to find the outstanding amounts. The debt amount that is yet to pay is the outstanding amount.

  • Firstly, click cell J5.
  • After that, insert the formula:
=E5-G5
  • Consequently, press Enter.
  • Lastly, apply AutoFill.
  • Hence, you’ll get the outstanding amounts.

debtors ageing report in excel format

Read More: How to Create Pay off Credit Card Debt Calculator in Excel


STEP 4: Calculate Days Past Due Date

In this step, we’ll determine the total number of days that have passed since the due date. We will insert the TODAY function here.

  • To do that, choose cell K5.
  • Here, input the formula:
=TODAY()-I5
  • Return the output by pressing Enter.
  • Afterward, choose the Number format for cell K5.
  • Therefore, it’ll return the number of days.

debtors ageing report in excel format

  • Subsequently, use the AutoFill tool to fill out the rest of the series.
  • However, press Delete for the K7 cell to clear the cell value.
  • This is because the customer has already paid his debts.
  • So, the dataset will look like the one displayed below.

debtors ageing report in excel format


STEP 5: Add Remarks

Moreover, we can add remarks to make the report more practical. The concerned persons take decisions according to these remarks. Hence, follow the below process to add remarks. This will make the report dynamic.

  • First of all, select cell L5.
  • Then, type the formula:
=IF(K5="","",IF(K5<90,"Contact Customer","Issue Warning"))
  • After that, press Enter to return the remark.
  • Next, use AutoFill to complete the rest.

NOTE: The IF function returns blank if K5 is empty. If K5 is below 90, it’ll give out Contact Customer. Otherwise, you’ll see the Issue Warning.

Final Output

In this way, we can create a Debtors Ageing report in Excel format. The following figure is our final output.


Conclusion

Henceforth, you will be able to create the Debtors Ageing Report in Excel Format following the above-described procedures. Keep using them and let us know if you have more ways to do the task. Follow the ExcelDemy website for more articles like this. Don’t forget to drop comments, suggestions, or queries if you have any in the comment section below.


Related Articles

Aung Shine

Aung Shine

My name is Aung. I have my B.Sc. degree in EEE. From now on, I will be working with Microsoft Excel and other useful software, and I’ll upload articles related to them. My current goal is to write technical contents for anybody and everybody that will make the learning process of new software and features a happy journey.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo