How to create an accounts receivable template

An accounts receivable template lets you manage your client invoices from month to month in a consistent, simple format. Read our guide below to find out what should be included in a typical accounts receivable spreadsheet template and how to create your custom template in Excel.

What is accounts receivable?

The accounts receivable ledger is an integral part of your business’s financial records, alongside statements like the balance sheet and income statement. Accounts receivable ledgers list all customers or debtors for a company, along with necessary information like the invoice dates and numbers, dates of payments received, and outstanding receivables.

The accounts receivable department handles everything related to invoicing and receiving payments for goods or services. Its functions include:

• Issue accurate invoices for goods or services rendered.

• Record invoices into customer accounts.

• Verify purchase orders and invoices.

• Ensure invoices are collected according to payment terms.

• Report settled invoices after collection.

• Reconcile discrepancies between invoices and customer payments.

An accounts receivable template must include sections for all of these functions to be recorded each month in a clear format. Accounts receivable departments also prepare aging analysis reports, drawing from this payment data.

How does the accounts receivable process work?

There are four key steps to the accounts receivable process:

1. Establish credit practices for the company.

2. Invoice customers or clients for services rendered.

3. Track accounts receivable in a logical format.

4. Follow all accounting procedures for recording accounts receivable.

Accounts receivable will also handle bad debts and work with the rest of the business to ensure payments are collected in a smooth, timely manner.

Accounts receivable template format and components

To facilitate the steps outlined above, an accounts receivable template format will include some core sections. Any journal entries in accounting related to the payment of goods and services should go into the accounts receivable ledger.

Here’s a screenshot of a free accounts receivable template in Excel:

As you can see, it lists transactions in order by date, alongside when the payment is due. Additional components included in a generic free accounts receivable template in Excel like this one include:

• Invoice number

• Customer name

• Amount due

• Late fee (if applicable)

• Total paid

• Date of payment

• Outstanding payment

This gives a clear indication of what the customer has paid and what is still owed to the company, along with all relevant dates.

Create an Excel spreadsheet template for accounts receivable

Although you can download a free accounts receivable template, it’s relatively easy to create your own. This is because there are only a couple of formulas involved, so you’ll simply need to set up some basic columns and rows with the relevant information. Here’s how to create your own Excel spreadsheet template for accounts receivable:

1. Set up a new Excel sheet with columns for ‘Invoice Dates’ in column A, ‘Invoice Numbers’ in column B, and ‘Due Dates’ in column C.

2. Add another column (E) showing ‘Total Amount Due’ along with payment columns in F through I. The number of payment columns needed will depend on how your business processes payments from customers.

3. Add a ‘Balance Due’ column in column J, or wherever your payment columns have ended.

4. In the first cell under the ‘Balance Due’ label (we’ll use J3 as an example), you can create the following formula: E3-SUM(F3:I3). This will show you the difference between what the customer has paid and what is still owed. Drag this formula down to the final customer account on the spreadsheet to auto-populate it through the full column.

5. Finally, create an ‘Outstanding Receivables’ cell at the bottom to tally up all of the balances due. You can then add in a formula =SUM of all the cells in the spreadsheet.

Remember, this is only an example of a typical accounts receivable template format: the particulars will depend on your own business.

We can help

GoCardless helps you automate payment collection, cutting down on the amount of admin your team needs to deal with when chasing invoices. Find out how GoCardless can help you with ad hoc payments or recurring payments.