If you look at your aged ledger for the past twelve month ends you can calculate what percentage of each bucket you collected every month. This will give you a clear indication if your collections are getting better or worse. For example if your Gross sales for June were €14,789 and the June Ledger is:
Balance Current 1 month 2 months 3 months+
29,058 12,356 7,832 1,235 7,635
If your July gross sales were €15,276 and ledger looks like:
Balance Current 1 month 2 months 3 months+
29,995 12,989 8,256 1,752 6,998
There are a few deductions you can make:
June invoices paid in June amount to 2,433 (14,789 – 12,356) or 16%
July invoices paid in July amount to 2,287 (15276 – 12989) or 15%
You collected 4,100 (12,356- 8256) of June money in July or 33%
You collected 6,080 (7,832- 1,752) of your May money in July or 77%
You collected 1,872 (1,235+7,635-6,998)of your April+ in July or 21%
You can also see the very prompt payers, your cash or prepay amount each month, in this case account for around 15%. If this trend is consistent throughout the year you can factor these %’s into your target.
The next call you have to make is are you going to do the same as last year or better or worse depending on the economic conditions and what you are prepared to allow. Unless you are going to something drastic like engage The Credit Coach to help you reduce your debtors balance, work on the basis that you will perform in the coming year at the same level as you did last year.
Then all you have to do is start with your current ledger as follows:
July August September October
Op Bal 29,058 29,995 x y
Sales 15,276 per budget per budget per budget
Cash 14,339 per %’s per %’s per %’s
Cl Bal 29,995 x y z
This way you can build up an accurate forecast of what you can expect to collect every month, and build in some margin for slow payers as long as you match your figures back to the previous year’s actual achievements. Check the Days sales outstanding for each month to make sure the figures make sense before you present them.
A word of advice: if you are asked to prepare a budget, never over estimate what cash you are going to collect, as long as the targets are accepted at the start and delivered month after month, everyone will be happy.
From a reporting perspective, when you group your customers into their payment terms: say half pay the end of month following, half pay the month after that you can create a perfect ledger - for July it would look like:
July perfection:
Balance Current 1 month 2 month 3 month+
19,167 12,989 6,178 0 0
Perfection is defined as 15% paying in the current month, 50% of the balance paid at the end of the following month and 50% paid the month following that.
You can now see that there is 10,828 outstanding that shouldn’t be there and that is what you have to address from a management perspective: Why is that money outstanding? Is it disputes? Is it slow payers? Is it unofficial agreements? If you have payment plans in place your 3 month+ column could have a reducing balance and as long as they are abiding by the terms of the agreement I would include these figures as within terms.