Download Accounts Receivable With Aging Excel Template

Accounts Receivable Template is a document which records sale of services or goods by a company made on credit.

In other words, Account receivable Ledger records the credit invoices of a company to its debtors.

These receivables are our assets. Accounts receivable is listed as a current asset on the seller’s balance sheet.

The total amount of accounts receivable which a company allows to a debtor/customer is often limited by a credit limit. This limit is set by the company’s credit department.

These limits are based on the finances of the buyer and its payment history. Credit limits can be increased or decreased according to the market conditions.

Accounts receivable can be further subdivided into two; trade receivables and non-trade receivables.

Trade receivables are from a company’s routine sales activities. Non-trade receivables are all other receivables, such as amounts due from employees against loans etc.

A company can use its accounts receivable as collateral for loans from banks or other non-banking finance corporations.

I have created an Accounts Receivable Template with advanced Aging functions. With Accounts Receivable Template you can efficiently and easily record the sales entries.

The Accounts Receivable Template is useful for Accounts Assistant, Accountants, Audit Assistants etc.

Click here to Download the Accounts Receivable With Aging Excel Template.

This template generates customer-wise/debtor wise report in few clicks. Click on the drop-down menu and select the name of the debtor you want the report for and click OK. See image below:

You can download other accounting templates like Trial Balance, Cash Flow Statement and Balance Sheet.

Let’s discuss the template contents in detail.

Accounts Receivable Template Content

This template consists of two sections:

1. Header Section and
2. Data Input Section

1. Header Section

In Header section, right-hand side consists of Company name and name of Ledger of Accounts Receivable.

On the left-hand side, Current Balance of Accounts Receivable amount is given. Thus, Balance amount = Total of Sales Invoice – Payments Received.

2. Data Input Section

The data input section consists of the following subheading for data entry of Receivables:

Customer Name: Name of Debtor’s company or individual.

Invoice #: Invoice number of the Debtors.

Invoice Date: Date of invoice issued by Debtor

Invoice Amount: Amount of invoice in desired currency.

Payment Terms: Predefine the credit limit period. Here it is mentioned 30 days from the date of invoices issued.

Amount Received: Record amount if any amounts are received.

Date Received: Date on which payment is received.

Due Date: Due date is calculated by adding 30 days to date of invoice. If you want to set longer limits are just change the number of in Payment Terms column.

The Formula applied here is as below: =IF(Table2[[#This Row],[Invoice Date]]=””, “”, Table2[[#This Row],[Invoice Date]]+Table2[[#This Row],[Payment Terms]]).

Amount Outstanding: This column shows the balance amount after deducting payments received from the total amount of invoice.

The formula here is =Table2[[#This Row],[Invoice Amount]]-Table2[[#This Row],[Amount Received]]

Days Past Due Date: This column show number of days past to the Due date of payment or end of credit limit time. Thus, these number of days are calculated by deducting today’s date from the due date.

Formula Applied here is =IF(Table2[[#This Row],[Amount Outstanding]]=0,””,TODAY()-Table2[[#This Row],[Due Date]]).

Remarks: This column shows the status of the debtor. If the number of days past due date is 0, then the cell will be blank. If the number of days is below 90, the cell will display a message to Call Customer.

Similarly, if the maximum limit above due date is passed it will show the message as Bad Debt. In this template, the maximum limit is above 90 days from the due date.

The formula applied here is =IF(Table2[[#This Row],[Days Past Due Date]]=””, “”, IF(Table2[[#This Row],[Days Past Due Date]]<90, “Call Customer”, “Bad Debts”)).

Conclusion

The higher the accounts receivable the higher our assets will be. But Just making good sale is not enough. If the sale is made but collections are not timely our bad debts increases.

Thus, it should be kept in mind it should have the minimum amount of bad debts.

Note: All the subheadings in data input section are created with a table. Thus, it allows you to generate a debtor wise, date wise etc query/report as required.

You need to simply click on the drop down button in subheading and select the criteria of the report.

We thank our readers for liking, sharing and following us on different social media platforms, especially Facebook.

If you have any queries or questions, share them in comments below. I will be more than happy to help you.