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.
Table of Contents
Download Free Accounts Receivable Template (Excel, OpenOffice Calc, and 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:
- Issue an Invoice.
- Record the invoice in the Accounts Receivable Ledger.
- Define Credit Period.
- 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
Accounts Receivable department undertakes the following functions: Account Receivable A/C Dr Cash or Bank A/C Dr Cash or Bank A/C Dr Sales Return A/C Dr Accounts Receivable is the amount received upon due date from customers. Hence, it is reported as a current asset in the balance sheet. Debtor days mean the average days to receive payments from clients. You can calculate Debtors Days using the following formula: Debtor Days = (accounts receivable/annual credit sales) * 365 days. Recoveries of bad debts are the amounts received by a business against the uncollectible written-off debts. They are reported as receivable under the category of Other Accounts Receivable. Bad debts decrease the accounts receivable amount. These bad debts are added to Operating Expenses in the income statements. Hence, they are contra assets. 1. Direct Write-off Method. Generally, a higher amount of sales leads to higher accounts receivable and thus increases our assets. But good sales amount with no timely collection leads to bad debts. Thus, it is necessary for a business to collect payments timely and reduce bad debts. Making Aging reports helps us to monitor long outstanding invoices which ensures timely follow-ups for collection with clients. Moreover, it helps us the business to design their credit policies. Accounts Receivable is the cash flow for any business. Irregularities in collection lead to lower cash flows. To ensure the smooth running of the business aging report helps the business to ensure proper cash flows. In addition to the above, businesses use accounts receivable as collateral for loans from financial institutions. Profit and Loss A/C DrWhich accounting functions does Accounts Receivable undertake?
1. Issue invoice for goods/services rendered.
2. Record the invoices to the respective debtor's account.
3. Compare invoices with the POs(Purchase orders)/Delivery Challans.
4. Issue Debit Note or Credit against any discrepancy.
5. Manage and ensure timely collection of payment against invoices based on credit limits.
6. Prepare outstanding accounts for debtors for long-due payments.
7. Record payments received from debtors.
8. Reconcile debtor's accounts weekly, biweekly, fortnightly, or monthly.
9. Prepares Aging Reports.How to make Accounts Receivable journal entries for Sale of goods or services?
To Sales A/C CrHow to make Accounts Receivable journal entries for payment received?
Account Receivable A/C CrHow to make Accounts Receivable journal entries for payments received with discounts?
Sales Discount A/C Dr
Account Receivable A/C CrHow to make Accounts Receivable journal entries for Sales Return?
Accounts Receivable A/C CrWhere are accounts receivables reported in a balance sheet?
What are calculate Debtor Days?
Under which account are Bad Debt Recoveries reported?
Why is Bad Debt a Contra Asset?
What are the 2 Methods to Record Bad Debts?
2. Allowance Method.What is the importance of Aging Report?
How to make closing journal entry for bad debts?
Bad Debts A/C Cr
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.
Please send us your queries or suggestions in the comment section below. We will be more than happy to assist you.
Pingback: Ready-To-Use Billing Statement Template - MSOfficeGeek