How to create an accounts receivable template

At the risk of sounding like a broken record (we’ve covered this a few times), the key to staying on top of your accounts receivables, implementing good credit control, and getting paid on time, is good administration.

We know it’s not flashy or exciting, but making sure you’ve got your admin right will help you get paid faster, avoid bad debt, allow you to accurately predict your cash flow, and even improve your relationship with your customers.

One of the easiest ways to manage your accounts receivables is to use a template that lets you deal with your invoices and payments in a clear, consistent, and easy to understand format.

To help you out, we’ve put together a guide to how to create your own accounts receivable template in either Microsoft Excel or Google Sheets.

What are my accounts receivable?

Before we get started on our template, we need to outline what we mean by accounts receivable.

Your accounts receivable is essentially a list of all of the invoices you’ve sent to your various customers and the payments that have been made. It includes vital details such as the dates you sent your invoice, invoice payment dates, payments received, and payments still outstanding.

Your accounts receivables are a cornerstone of your business accounting and provide the data that underpins certain vital accounting functions, including:

• Issuing accurate invoicing for goods and services.
• Verifying important details such as purchase order numbers.
• Chasing outstanding invoices.
• Marking invoices as settled after payment.
• Reconciling any disputes or discrepancies between payments made and invoices issued.

What is the accounts receivable process?

The process for using your accounts receivable is generally broken up into four steps:

Establish credit practices

The first step it to establish what, if any, credit practices you might have. Depending on the specifics of your business, you might allow customers to make purchases of goods or services on credit.

Such purchases, and the details associated with the credit arrangement being offered, are tracked and recorded through your accounts receivable.

Invoicing and payment

Your accounts receivable is where you track all the invoices you send to your customers, what payments have been received and which are still outstanding.

Tracking accounts

As part of the credit, invoicing and payment process, your accounts receivable is where you track any lines of credit offered, any outstanding payments and mark any received payments as settled.

Producing reports

Since your accounts receivable contains all the information on what invoices have been sent, what payments have been received, what lines of credit have you have open and what payments are outstanding, it is a hugely important source of data.

That data can be turned into reports that predict cash flow, analyse aged receivables, chart customer payment data, and provide other vital insights for your business.

Creating an accounts receivable template

When it comes to creating an account receivable template for your business, you have two choices.

Firstly, you can download a pre-made template, like this one from Chaser.

We’re a big fan of templates and how they can streamline your credit control process, which is why we offer a range of our own, from email templates for friendly late payment reminders to credit control and debt collection policy templates.

The other option is to create your own in a program like Microsoft Excel, Google Sheets or anything similar. To help you out, we’ve put together a step by step to creating your own simple but effective accounts receivable template.

Just follow the steps below:

• The first thing you’ll need to do is set up a new Google Sheets sheet. Set up columns as Invoice Numbers, Invoice Dates, and Due Dates in that order.

• Once you’ve done that, set up column F as Total Amount Due.

• You’ll then want to set up columns F through J as Payment Columns. The number of payment columns you’re going to need will depend on the specifics of your business and how you process your customer’s payments.

• At the end of your Payment Columns, you’ll want to add in a Balance Due We’ll use column J for this example

• Click and drag the formula down to the final customer account on the spreadsheet. This will auto-populate the full column with the difference between what the customer has paid and what is still outstanding. 

• Set up the cell at the bottom with the Outstanding Receivables Add in the =SUM formula for all the cells in the spreadsheet and this will add up all of the outstanding balances.

Get help from the professionals

If you’d like to hear how using Chaser can help you get control of your accounts receivable and implement the kind of credit control that help you get paid faster and more often, book a demo with us today