Account Receivable Excel Template

  • Version
  • 13'986 Downloads
  • 54 KB File Size
  • January 4, 2022 Updated
  • 1 Number of comments
  • Rating
table of content

Account Receivable (AR) is the amount of payment due to goods delivered or services used but haven't been paid yet. It is usually referred to outstanding invoices. In accounting, balance sheet report includes this term as part of company's assets since it is a legal obligation for particular customers to pay the debt.

Account Receivable Spreadsheet

If a company has receivables, it means the company provides credit as payment method to customers/clients for purchasing products/using services. Particular company gives this privilege to boost their sales. Also, it is to avoid cash or direct payment for specific transactions, especially for frequent customers. In trading businesses, it applies to distributor/resellers who purchase product in regular basis. In service businesses, it is given to clients who subscribe for monthly services, like electricity, gym memberships and internet subscription.

Account receivables are becoming issues when customers/clients are paying their invoices late. For small companies, it could affect their cash flow situation. Common causes are quality of delivered products/services which are not as good as being promised.

Some companies measure it by applying specific Key Performance Indicator (KPI) to help them finding the problems and improve it.

Common KPIs are :

1. Days Sales Outstanding (DSO)

DSO provides insight into collections during one point in time, usually periods of less than a year. DSO also is a measurement of time, how long it takes for you to collect payment after issuing an invoice.

2. Average Days Delinquent (ADD)

3. Collection Effectiveness Index (CEI)

CEI compares how much money was owed to the company and how much of that money was actually collected in the given time period, usually one year.

4. CEI vs DSO

5. Accounts Receivable Turnover Ratio (ART)

The ART measures how many times your company turns accounts receivable into cash during a period.

This Account Receivable template below is an Excel spreadsheet to help you managing your invoices and respective receivables. You can analyze them using KPIs parameters above.

Account Receivable Spreadsheet

Payment Terms

Define your payment terms here. It is to simplify due date calculation in account receivable worksheet. Do not remove “Fixed Date” row on top of the table. When you select that, you can type due dates for specific invoices.

Customer List

Type your customers/clients ID as well as their name. You can add more columns in this worksheet.

Invoice Log

There are Invoice Issued and Invoice Paid tables.

Account Receivable - Invoice Log

In Invoice Issued table, you can type and select :

  • Invoice date
  • Invoice no
  • Customer name
  • Amount
  • Payment terms (If you select “Fixed Date”, you can type specific date in Fixed Date column
  • Due Date

Then, in Invoice Paid table, you can type any amounts that are being paid as well as respective payment dates. You can only put summary of your invoices in this table. If you are looking a spreadsheet to manage your invoices, you may try our Invoice Creator template.

Account Receivable Log

Reference date is based on today's date. You can replace it with any dates if you want to measure account receivable for specific period.

You don't have to do anything here. It will show you all customers/clients receivables from 1-30 days until more than 90 days.

On top of the table you can see the total amount of the receivables for those periods.

Account Receivable Aging Chart

There is a simple account receivable aging chart you can see as summary of your current company receivables.

Account Receivable - Aging Chart

You can modify the chart or get other values for further analysis. For example, you can create chart to visualize all five KPIs above and add your own company's KPIs.

You can modify this account receivable spreadsheet to suit your needs.

How useful was this template?

Click on a star to rate it!

Average rating 4.9 / 5. Vote count: 43

No votes so far! Be the first to rate this template.

We are sorry 🙁

Help us improve!

How we can improve this template?

Account Payable

54 KB / 9247 downloads
Break Even Analysis

13 KB / 12414 downloads
Cost of Goods Manufactured Schedule

1.3 Mb / 8800 downloads
Merchandising Business Accounting

137 KB / 9427 downloads
Accounting Journal Templates

110 KB / 17870 downloads
General Ledger Template

51 KB / 15376 downloads
Petty Cash Excel Template

43 KB / 13970 downloads
View Comments