Whenever a firm is done with its sales target, the next thing in the queue is how long it will take to get the receivables from the customers. Accounts Receivable and hence Accounts Receivable Aging is important because they form an important part of the Cash Conversion Cycle for a company and is critical to maintain the liquidity of the company as well as to avoid cost of short term debt taken from the banks to main liquidity.
Another aspect of monitoring Accounts Receivable Aging is that the longer it is taking to get them back, the more are the chances to not to get them at all – or creation of bad debt. So keeping in view all this it is always important to keep track of the accounts receivable.
From aging we mean that how many days have passed since the receivable has been due. As we sale goods, we keep track of when the sales have been made, when the payment has been due and what is the amount that is due for a particular sales party.
In this post of ours, we will develop a template that can be used for most of the businesses for aging report preparation purpose. It will be a ready to use template, just key in the data and it will start working. So let’s start with the template!
Setting up the Sheet:
We have setup a sample sheet that contains various fields that are required. You may add more field and details to the sheet as per your requirement. For now we have Sales Dates, Party Name, Invoice No., and Amount. Our sample data contains data of multiple months so that we can see how aging works over the months.
Setting up the Summary Sheet for Aging:
The summary sheet consists of party name right size of the table and the days over the header row of the table. The header row consists of bracket of days passed and the amount is summed up underneath to give total value aged.
The table has total row as well to give the total aging amount for a particular bracket for example for the bracket 0~30 days the amount 527843/- where as for the same period the amount if 82622/-. In this table the net period is 30 days. This sets the bracket the for all the periods. We can change it as per our requirement say 15 days or 45 days either or to any other value.
Setting up the Summary Sheet for Aging as Percentage:
Quite often the absolute values are not that useful – we have to revert to percentages. In this case we are interested in outstanding amount as percentage of total outstanding amount. This will help us understand how individual supplier is behaving and will help to spot the area where most of our funds are blocked. For this case we will set up a separate sheet showing percentage values.
The subtotal at the bottom gives bracket wise fund used and the row total gives the total percentage of blocked fund attributed to a supplier form. This gives us a hint to speed up our action for that particular supplier. As a cross check measure, the percentage must always to 100% or 1 to give the correct answer.
A Sample Analysis of the Aging Sheet:
Referring to the sheet, the biggest chunk of out standing goes to Party A. it is taking around 14.52% of the total out standing. It is not that the Party A is solely responsible for large outstanding money, it is followed closely by Party G, H ,B and F. with percentage varying between 13~11 percent.
Out of these parties, the party A, D and G have largest amount of outstanding percentages in the bracket 151-180. This shows an outstanding of about 3 months i.e. bills have not been paid for last three months which shows potential of creation of bad debt. In contrast the Party F has longest outstanding in the range of 181-210 days but amount is relatively small (around 0.49%).
With all this information, we are set to layout the credit terms to our suppliers, supplier A needs immediate revision of credit term or we need to speed up the recovery process. Similar action should be directed to D and G who are following A in outstanding.
Additional Stuff – Understanding Formulas:
In case you decide to add more fields, you will have to adjust the formulas. So here is a brief guide of how formulas are working.
The Aging Summary Report (Absolute Figures):
=SUMPRODUCT(($B6='Sht1'!$D$5:$D$303)*('Sht1'!$G$5:$G$303>='Sht2'!C$4)*('Sht1'!$G$5:$G$303<='Sht2'!C$5)*('Sht1'!$F$5:$F$303))
In this formula the term $B6 refers to the party name and the expression $B6='Sht1'!$D$5:$D$303 equates the Party name to the party names in the data list. The comparison will return a binary array. The second comparison will be made when will check if aging days fall between certain time bracket and we will evaluate the brackets:
('Sht1'!$G$5:$G$303>='Sht2'!C$4)*('Sht1'!$G$5:$G$303<='Sht2'!C$5)
Here the term ‘Sht2’!C$4 is the starting date and >= sign shows that values must be greater then or equal to the starting value of the bracket. ‘Sht2’!C$5 represents the end date and the values must be small then the upper value of the bracket. The multiplication of these two arrays again produces a binary array that multiplies with the previous array produced from the party comparison. The result of this multiplication is final multiplied with the invoiced amount to give the total sum out standing for a particular party of a given bracket of days.
Using subtotal() function in this sheet ensure if the data is filtered for a particular party the sums are not changes. This means that sheet is well designed to accommodate the filter option.
The aging summary sheet as percentage:
It works on the following formula:
='Sht2'!C6/'Sht2'!$L$15
This formula simply divided the individual values in the table with the total of the table. These values are formatted as percentage from Excel menu. The sum is again 100% that is validates our figures are correct. The row total gives the total percentage of amount that is consumed by a certain supplier and the footer row gives the bracket wise total percentage.
Summary:
Accounts Receivable Aging Report is a very important report and this template makes its management very easy. You can amend the sheet to suit your need and more details to it. Please down the sample file from this link.