Aged Accounts Receivable Chart

Download Template

In this template you can generate a chart showing the history of a customer’s aged accounts receivable. This chart will show a breakdown by invoice age so it will be able to tell you a great deal in one picture: the customer’s total receivables by month, breakdown of the age of the receivables by month, how much sales is being done with the customer (this would be the current receivables), and whether the receivables are growing or declining. It could be a very useful tool in evaluating a customer’s credit worthiness and in helping detect potential problems.

The main input tab is the AllTransactions tab, columns A:E. Column D specifies the type of transaction and should either be PAYMENT or INVOICE. Column C (Date) relates to the date of the transaction – either  payment date or an invoice date. Columns F:H are formulas.

The other input is the Customers tab. You will need to enter all the customers onto here. The easiest way would be to copy the names from all transactions and just extracting unique value (see this post on how to do that). Note that the customer names here must match the names on the AllTransactions tab otherwise when you select a customer data may not populate correctly if the transaction data does not have a match for that customer name.

Once entered, you can go to the Aging Chart tab and select your customer from the drop-down menu and the chart will update:

It is a stacked column chart so in addition to just seeing overall receivables by month you can see their age makeup. This customer did not go past over 30 days so they don’t venture past the dark green shading. Now, my other customer, Bad Customer, has a lot more colour:

This customer has gone as high as 120+ so they have the full spectrum of the aging schedule on here. The closer the colour is to red, the older the receivable is. You can modify these colours to your liking.

The current period that I have the chart running for is from January 2016 until March 2017. You can change the starting period in cell B2 on the Summary tab and if you want to add more months then simply drag the last column’s cells from rows 1 to 8 into the next column so that the formulas will update.

Because there are no macros in this template, you will also need to update the chart range so that it includes the new months you have added. To do so, right-click on the chart and click select data and in the chart data range enter ChartData – this is a named range that will automatically select the furthest column.