Aung Shine
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.
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
- How to Calculate Cost of Debt in Excel (3 Simple Ways)
- How to Make Credit Card Debt Reduction Calculator for Excel
- How to Calculate Current Portion Of Long Term Debt in Excel
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.
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.
- 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.
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.
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
- How to Use Debt to Equity Ratio Formula in Excel (3 Examples)
- Debt Snowball VS Debt Avalanche Method in Excel Spreadsheet
- Debt to Income Ratio Calculator in Excel (Create with Easy Steps)
- How to Calculate Annual Debt Service in Excel (3 Ideal Examples)
- How to Calculate Before Tax Cost of Debt in Excel (3 Easy Ways)
- Debt Service Coverage Ratio Formula in Excel
