Account Receivable Excel Template

• Version
• 14'591 Downloads
• 54 KB File Size
• January 4, 2022 Updated
• 1 Number of comments
• ★★★★★

  ★★★★★
  Rating

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.

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.

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.

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.