Ready-to-use Accounts Receivable Excel Template

Accounts Receivable Template in Excel, OpenOffice Calc, and Google Sheets to record & manage client-wise account receivable ledger with aging.

Just insert your invoices in Accounts Receivable Ledger and the template automatically saves the debtor’s receivable data in their respective file. It uses the Advanced Filter Function as well as predefined macros.

Moreover, this template also helps you prepare month-wise, date-wise, amount-wise, and client-wise reports.

The template consists of multiple payment columns. Thus, the user can record partial payments or full payments against each invoice.

Download Free Accounts Receivable Template (Excel, OpenOffice Calc, and Google Sheet)

Microsoft Excel OpenOffice Calc Google Sheet

Additionally, you can also download other HR templates such as Salary Slip Excel Template India, Salary Slip Format UAE,  Simple Salary Sheet, and Employee Salary Sheet depending on the company requirement.

You can also download a fully automated Payroll Template With Attendance to easily perform all your payroll tasks.

In case, you want to customize any of the above templates feel free to contact us. You can hire us for our services on Fiverr or directly contact us at info@msofficegeek.com.

How To Use Accounts Receivable Template?

Accounts Receivable Template is a fully automated AR Ledger to record the invoices issued for goods supplied or services rendered.

If you are new to accounts receivable, you can learn more about it in our “Frequently Asked Question” at the end of this article.

4 simple steps to record invoices in this fully automated Accounts Receivable Ledger:

1. Issue an Invoice.
2. Record the invoice in the Accounts Receivable Ledger.
3. Define Credit Period.
4. Record date-wise payments against each invoice.

That’s all the user needs to do. As the template consists of recorded macros, it automatically transfers the invoice data to the respective debtor’s ledger sheet.

Moreover, the template consists of predefined formulas. It automatically calculates the following for you:

• Due date of the invoice.
• Outstanding amount against each invoice.
• The number of days the invoice is outstanding above the due date.
• Displays a call to action against each invoice.

If the invoice is outstanding above the credit period, the remark column will show “Aging” and if the invoice is outstanding below the credit period, it displays “Call”.

You can download this sheet in three different spreadsheet programs; Microsoft Excel, Google Sheets, and OpenOffice Calc. Instructions to use each type of spreadsheet program are given in the below section.

Components of Accounts Receivable Excel Template

The template consists of 3 files: Accounts Receivable Ledger and 2 Debtor Ledgers.

Accounts Receivable Ledger

The accounts receivable ledger consists of the following heads.

Invoice Date: Insert the date of invoice.

Month: This column consists of predefined formula. It displays the month using the TEXT Function. This column is useful for generating month-wise reports.

Invoice Number: An invoice number is a unique number series issued to customers. Insert the numbers of the invoicing system in your company.

Debtors Name: Insert the name of the customer/debtor.

Invoice Amount: Enter the invoice amount.

Credit Period: Usually, the credit period differs based on customer relations. Hence, enter the number of days allowed for credit to the respective customer.

Due Date: This column consists of pre-defined formula. Hence, it auto-calculates the due date based on the credit period.

Payment Date: Date on which the first payment is received.

Amount Received: Amount sent/received/deposited by the debtor against the respective invoice.

There are 5 payment columns with their respective dates. Hence, you can record payments accordingly.

Outstanding: This column consists of pre-defined formulas. It calculates the payment/payments against each invoice and displays the outstanding amount.

Days: Days column displays the number of days passed above the due date.

Remarks: Remarks column also consists of a pre-defined formula. It is blank if there is no outstanding. If the days are below the due date it displays “Call” whereas if the days are above the due date, it displays “Aging”.

Usually, every company allows its debtor/customer credit limit. The company sets this limit is based on customer relationships. The payments delayed upon this credit limit are called aging.

Customized Reports

The template consists of filter columns. Hence, you can generate month-wise, date-wise, amount-wise, and client-wise reports.

All you have to do is to click on the down arrow key beside each heading, select your criteria and click “OK”. The sheet displays the desired output. If that down arrow key doesn’t display, go to Data Tab and click on the “Filter” icon under Sort and Filter.

For example, if you want a month-wise report, click on the arrow beside the “Month” heading. Select the month for which you want the report and click “OK”.

Similarly, you can prepare customer-wise, credit period-wise, due date-wise, outstanding-wise, and days outstanding-wise reports.

Apart from the excel file version, these same features are also available in OpenOffice Calc and Google Sheet files. Thus, you can easily make all these reports in those file formats also.

Debtor’s Ledger

The template consists of Debtor’s ledger. In the excel version of the Accounts Receivable Template, we have configured it with a macro using the Advanced Filter option. Thus, it automatically fetches the data according to the given criteria.

Whenever the user makes an entry in the master Accounts Receivable File, it will fetch the data in the debtor’s file. Kindly note that to run the above macro, the columns headings in both the sheet must be same.

At the top left corner, the debtor’s criteria are given. Based on that the file extracts the data. Just change the name of the Debtor and it will display the data of the respective debtor.

Currently, the displays data for Aakash Traders. Thus, when we change the criteria to Sundar Brothers Ltd, it displays the data of Sundar Brothers.

In the OpenOffice Calc format of the Accounts Receivable Template, you need to run the macro to transfer the data from the Accounts Receivable Template sheet to Debtor’s Sheet. See the image below:

 

The Google Sheet Format of Accounts Receivable Template automatically transfers data from the Accounts Receivable Template sheet. It uses the Filter Function of Google Sheets to transfer data. You can also use the Query Function available in Google Sheets. See the image below:

If you want to make different files for each debtor, you need to configure using a macro with Advanced Filter. We can do it for you as a paid job. You can contact us at info@msofficegeek.com.

To know more about Accounts Receivables, check out the FAQ section below.

Frequently Asked Questions

If you like this article, kindly share it on different social media platforms so that your friends and colleagues can also benefit from the same. Sharing is Caring.

We also design customized templates according to your needs. You can hire us for our services on Fiverr or directly contact us at info@msofficegeek.com.