Use this free accounts receivable ledger in Excel to enter all your business sales invoices to your customers and to track payments your customers make against them.
This ledger should only be used for customers whom you allow to buy products and services from you now but only have to pay for later.
If the customer buys something from you and pays for it immediately it is unnecessary to enter it into the workbook because they owe nothing to you.
Tap the download button which will import a copy of the Excel accounts receivable ledger to your device.
Below is a screenshot of the what the ledger looks like and further down are some explanations of how to use it.
After downloading the document and saving it to your computer you can enter your business name into the cell that says [your business name] - just type over the wording. There are some great tips in our computer filing system article to show you how to file your digital documents.
When you issue a sales invoice to your customer enter the following information into your ledger starting in Row 4 of the Debtors sheet:-
In our example below, the total of 210.00 is the overall sum of all invoices that are still due for payment. This amount reduces as payments are received or increases as new invoices are added.
The cells in the balance due column indicate how much is left to pay for each invoice - this is calculated automatically because of the formulas we have included. The formula takes the Total Invoice Due amount and subtracts any payments made already.
The columns headed Payment and Date are where you will enter each payment received from your customer and date the payment is received.
The Examples sheet in this template shows how to enter the invoice details and the payments, as well as credits and refunds.
Every time you issue an invoice to a customer enter it in the next available (blank) row on the Debtors sheet.
Every time a customer makes a payment, look through the Debtors sheet for their invoice and record the payment and the date it was received.
When a customer has fully paid you could hide the row containing the paid invoice to keep only the unpaid invoices to view.
Every time you enter new data to the spreadsheet, remember to Save before exiting unless your Excel settings automatically save the template.
My recommendation is to save the spreadsheet once a month with a new name.
For example, if it is the month of May and you are entering all May dated invoices then save your spreadsheet with the name Accounts Receivable - May 20xx.
When June starts save a fresh copy of this workbook and start entering your June sales invoices and any payments received. Call it Accounts Receivable - June 20xx
Keeping copies like this ensures that if you make any huge mistakes in your current spreadsheet you always have last month's version to start afresh again.
The ledger contains a Notes sheet where you can record all details of phone calls, emails or texts between you and the customer when you are following up any overdue payments.
This is something you need to do regularly (maybe even once a week) to avoid landing in financial difficulties because of unpaid sales invoices. As soon as you have updated the Debtors sheet with all the latest customer payments, run your eyes down the list and highlight the ones you need to contact about any overdue accounts.
First check your Notes sheet for what was said last time you contacted them. Then contact them again now and discuss appropropriately.
Once you have contacted them, write down on the Notes sheet what was said, when payment was promised etc. Diarise to follow up and check that a promised payment has been received by the due date. If not, contact the customer again and make another note on the Notes sheet.
This is not an easy or comfortable task and you may be better off outsourcing it to professionals in order to keep your relationship with your customer as intact and happy as possible.
Take a look at our Accounts Receivable Collections page that explains in more detail some examples of how to contact overdue customers.
You may also like to see Accounts Receivable Procedures for more information on how to initiate customer accounts and maintaining this system.
Leave me a comment in the box below.