Download Accounts Receivable Excel Template

Accounts Receivable Template is a ready-to-use template in Excel, Google Sheet, and Open Office Calc to manage customer invoices and payments.

Just insert your company name at the top and start using the template.

Additionally, you can record payments and also get invoice-wise outstanding and total accounts receivable outstanding.

Furthermore, it consists of multiple payment columns against each invoice so that you can also record part payments against invoices.

What is Accounts Receivable?

As per Wiki

Accounts receivable is a legally enforceable claim for payment held by a business for goods supplied and/or services rendered that customers/clients have ordered but not paid for. These are generally in the form of invoices raised by a business and delivered to the customer for payment within an agreed time frame.

In simple terms, accounts receivable ledger consists of the list of debtors to whom we have rendered a service or supplied goods along with details like invoice number, date of invoice, date wise payments received and outstanding receivables from a particular client or all in general.

Role of Accounts Receivable

Accounts receivable handles the following functions:

1. Issue proper invoice against goods or services rendered.
2. Record invoices into the respective customer account.
3. Match the prices with the Purchase order or Delivery Challan.
4. Report mismatch if any and issue debit/credit note against it.
5. Ensure timely collection against invoices as per payment terms or contracts.
6. Prepare the list of outstanding invoices that are due for long.
7. Report settle invoices against collection done.
8. Reconcile invoices and payments statements between company and customer.

In addition to the above, the accounts receivable department also verifies purchases and prepares aging analysis reports apart from customer management.

Accounts Receivable Process

Accounts receivable is a 4 step process:

• Establishing credit practices.
• Invoicing customers.
• Tracking Accounts Receivable.
• Accounting for Accounts Receivable.

In addition to the above, the accounts receivable department coordinates with the Sales department to ensure a smooth collection of payments as well as saving the company from bad debts or long outstandings.

Accounts Receivable Journal Entries

Journal Entries for Sales

Account Receivable A/C – Debit

To Sales A/C – Credit

Journal Entries For Payments Received

Cash or Bank A/C – Debit

Account Receivable A/C – Credit

Journal Entries For Payments Received With Discounts

Cash or Bank A/C – Debit

Sales Discount A/C – Debit

Account Receivable A/C – Credit

The sum of both credit amounts must be equal to the debit amount.

Journal Entries For Sales Return

Sales Return A/C – Debit

Accounts Receivable A/C – Credit

Accounts Receivable Template

We have created a simple and easy Accounts Receivable Template with predefined formulas and formating. Record date wise invoice and their respective payments and it’s done. The template shows invoice outstanding as well as total outstanding accounts receivable at any given point of time.

Excel Google Sheets Open Office Calc

Click here to Download All Accounting Excel Templates for Rs 199.

Important Note: To edit and customize the Google Sheet, save the file on your Google Drive by using the “Make a Copy” option from the File menu.

Furthermore, when invoices are outstanding for longer periods, they refer to accounts receivable aging. You can download and use our ready-to-use Accounts Receivable Template With Aging.

You can download other accounting templates like Cash Book, Petty Cash Book, and Purchase Return Book.

Let us understand the contents of the template in detail.

Contents of Accounts Receivable Template

This template consists of 2 sections: the Header section and the Data Input section.

Header Section

Header section consists of the following:

Row 1 = Enter Company/Business Name. Cells A1 to K1. Merged cells.

Row 2 = Heading of the sheet. Cells B2 to K2. Merged cells. Headings can be put as Accounts Receivable Ledger or Debtors Ledger.

Row 3 = Multiple headings as numbered below:

1. The subheading for the Current Date in cell A3.
2. Next cell B3 will reflect the current date of the system using the formula =TODAY(). The date will automatically change according to the system date. You don’t need to change it every time and this saves your time.
3. The subheading for Total Receivable in E3.
4. In F3, the total receivable amount to date is displayed from the data below. The formula used here is =SUM(F5: F23).

Data Input Section

Dat Input section consists of the following:

Row 4 = This row consists of subheadings for data input. For example, see the table below:

Invoice Date, Invoice number, Customer name, Total Amount of Bill, payment 1, and payment 2 and their respective date are details to be put while entering data.

For the Due date, you can set a function that automatically calculates several days for you and show the exact date of payment.

For this, you need to set a function like this = cell address which contains date + Number of days for allowed. Here in our sheet, it is 60 days.

Thus, the formula entered is =A5+60. Copied to all cells below by simple copy-paste, this will apply the same settings to all cells.

Moreover, it calculates the balance by deducting the total amount of invoices minus the total of all the payments. It uses the following formula: =D5-SUM(H5+J5+L5+N5+P5+R5+T5+V5+X5+Z5+AB5+AD5)

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

If you have any queries please share in the comment section below. We will be more than happy to assist you.

Frequently Asked Questions