How to Reconcile Accounts Receivable in Excel

A simple excel accounts receivable template will reconcile and track all your customer invoices and provide other valuable information. It will save you hours of time in reconciliation and will keep the most important information of your business current and available for decision making purposes.

Our downloadable accounts receivable template will keep track of the following information:

1. Total Sales
2. Accounts receivable balance
3. All your invoices
4. Due dates
5. Customer payments
6. All the information to calculate accounts receivable turnover

For a startup company or other small business that cannot afford a full accounting system the accounts receivable template will handle much of the work for you.

Download a FREE accounts receivable template

Having the right tool to keep track of accounts receivable is essential. Download our FREE Accounts Receivable Ledger Template to start tracking your receivables today.

This template is simple and straightforward, you can begin using it today. If you are not an excel whiz, you can use it as is, if you are comfortable with excel, you can modify it to meet your specific needs.

We find it is always helpful to start with something rather than creating your own from scratch.

What are Accounts Receivable?

There are two types of sales: cash sales and credit sales. Cash sales are sales you receive payment at the time of sale. Credit sales are sales you extend credit, or in other words, you allow the customer to pay in the future.

Accounts receivable is the total amount of money owed from customers you sold to on credit. It is important to keep track of the amount owed by each customer to ensure you receive payment. Accounts receivable has a tremendous effect on your cash flow, if you are slow to receive payment, it makes it very difficult to pay your own bills and employees.

How to reconcile accounts receivable in excel

Downloading the template is the first step, understanding the template is the second step. Once you have downloaded the template, review the following detailed instructions on how it works.

Template explanations

Templates are powerful tools, but may need to be customized for your specific business. This template provides lines for 100 invoices. You could add more if necessary. It also contains formulas that if overwritten will make the template not calculate as expected. Please note, any highlighted cell contains a formula and should not be changed. The only highlighted cell you should modify is B3 which is for your company name. You can change this cell. 

Cell A2

Is the name of the template accounts receivable ledger.

Cell A3

Is a space for the name of your company.

Cell G1

Is the accounts receivable balance. This is a formula field and calculates the sum of all individual customer balance due amounts shown in cells G5 to G104. This is how much money is owed to you. It is important to follow-up often with customers to ensure you are receiving payments and the accounts receivable number is kept low.

Cell G2

Is the total sales. This is a formula field and calculates the sum of all individual invoice amounts shown in cells F5 to F104. It is important to include all sales in the accounts receivable ledger even cash sales. If you leave off a sale, your total sales amount will be incorrect.

Cell I1

Is the current date. This is a formula field and will always reflect the current date. It is also used to determine if an invoice is past due. The Due date in column D is compared to the current date in cell I1 and if the due date is less than the current date the due date cell will change to a RED background.

Column A

Displays sequential numbers which can be used to identify how many invoices you have issued.

Column B

Is a space for the customer name. You should have a line for each invoice you issue. If you issue multiple invoices for a single customer, use a new line for each invoice.

Column C

Is the invoice date. You should always assign an invoice date to each invoice so both you and the customer know when it is due. This date will help you to calculate when the invoice is past due.

Column D

Is the invoice due date. This is critical. It reminds you and the customer when the invoice is due, it also helps you know when to send past due notices. The template has a unique feature in that the cell with the date will turn RED if the invoice is past due. It has been formatted to compare the date entered to the current date in cell I1 and if less than the current date turns the cell RED.

Column E

Is the invoice #. This helps you and the customer to associate a payment with an invoice. It also provides a number that is easily searched.

Column F

Is the total amount of the invoice. This is important as that is how much you need to collect. It also provides the information to calculate total sales. Total sales are calculated in cell G2 automatically. Please note, if you issue a cash sale, you immediately record a payment in column I.

Column G

Is the invoice balance due. If it is a new invoice sold on credit it should match the total amount of the invoice. If it is a cash sale and you have entered the cash payment it should be a zero balance. The formula works by taking the Invoice Amount in column F and subtracting any payments included in column I, K or M. If you accept or if customers make partial payments this template can handle the partial payments.

Column H

Is the first payment date. The date you receive a payment you should enter that date. If you did a cash sale, then the payment date should be the same date as the invoice date in column C.

Column I

Is the first payment amount. It can be a partial amount. Whatever amount is entered will be deducted from the INV Balance Due in column G.

Column J

Is the second payment date. The date you receive a second payment you should enter that date. Many times, customers pay in full so a second payment is not necessary.

Column K

Is the second payment amount. It can be a partial amount. Whatever amount is entered will be deducted from the INV Balance Due in column G.

Column L

Is the third payment date. The date you receive a third payment you should enter that date. Many times, customers pay in full so a third payment is not necessary. If you need more payment options, you could modify the template to include more payment columns.

Column M

Is the third payment amount. Whatever amount is entered will be deducted from the INV Balance Due in column G. If the customer has not paid the full amount by the third payment you will need to add more payment columns. And adjust the formula in column G.

Can I modify the accounts receivable template?

Yes, the template we provide is a simple excel spreadsheet. The cells are not protected or locked. If you are comfortable with excel you can modify the template to add more rows so you can include more invoices or add more columns for additional payments.

In addition, you would be able to calculate other statistics like accounts receivable turnover ratio by using the data in the template.

Good luck and smart accounting.