Share this content
1
755

How can I calculate debtor reciepts

Calculating debtor receipts from debtor days

Hi,

I'm sure this question is asked a lot but i'm tying myself in knots trying to get a decent debtors calculation for a budgeted balance sheet and cashflow.

I usualy use goal-seek in excel to predict the receipts figure based on set debtor days. So if the b/f figure is £2,000,000 and sales in the period were £800,000 and debtor days are 77, I would use goal seek to set the debtor days cell to '77' by changing the receipts cell in the calculation.

I'm just wondering if there is a more scientific approach to this?

I started to figure out the phasing of the £2,000,000 and then tried to figure out when the monthly balances would be paid. So, assuming 77 days, the £2m would be £779K from Month 12 (prev year), £779K from Month 11 and the remaining £441K from Month 10 (assuming that the £2m is made up of 30 days + 30 days + 17 days with the 17  days being the oldest). At the end of month 1 (new year) you would have these balances...

Age Balance % Days
30-60 779,221 39% 30
60-90 779,221 39% 30
90-120 441,558 22% 17
  2,000,000 100% 77
       

I then tried to figure out what would be received in month 1. 

Assuming everything that's older than 77 days, It should be all of the 90-120 row (£441,558) plus 13 days worth of the 60-90 row (£779,221 * (13/30)). This is equal to 30 days worth of the opening balance (£2,000,000 * (30/77)).

When I drag this formula accross the year I get varying debtor days - when I use the goal seek method I get fixed debtor days and a more varied receipts figure.

Again, is there a better way to do this? ...am I even close? ...are there other factors that I'm missing?

 

 

 

Replies

Please login or register to join the discussion.

10th Jul 2017 14:45

You're going to need the spread of payments.

Your model seems to assume that 30/77 of the money comes in during Month 1, same in Month 2 and the rest in the first 17 days of Month 3.

77 days is an average and the money will likely come in during Months 2, 3 and 4. Real data - even calculated over a sample period - will help you allocate the receipts.

Thanks (1)
By tom123
10th Jul 2017 15:04

Great to see a management accounts question on here!.

I will watch with interest.

Are you able to produce retrospective debtor's ageing reports?

I can do that from my software, and then tend to work on percentages paid in each month following, by monitoring the period balances over time.

For example:

84% of my current month balance will be paid by month 1.
10% will be paid by month 2, and the remainder by month three.

My debtor days remains stubbornly at 55, whatever happens.

Thanks (1)
avatar
to tom123
10th Jul 2017 15:59

Yes, I can get historical balances.
I think you've hit the nail on the head there. I need a separate working for payment phasing (probably per department).

Thanks (0)
avatar
11th Jul 2017 11:05

So, with the help of the above comments I decided to start looking at creating a detailed schedule of receipts based on actual receipt times.

Unfortunately, it turns out that the accounts package isn't able to give me the data that I need to determine the time between invoice and payment (it's a legal CMS with a VERY basic accounting package bolted on). Basically, the receipt details do not contain an invoice ref so there's no way of tying up an invoice with a receipt. Also, as this is a law firm, there's not always a receipt, sometimes invoices are absorbed by surplus client funds and the office receipt is actually a sweep up done by accounts staff periodically.

I think I will have to continue with the 'goal-seek' method until we're able to upgrade to a 'decent' accounts package.

Thanks (0)
to Numberwang
11th Jul 2017 11:32

Numberwang wrote:

So, with the help of the above comments I decided to start looking at creating a detailed schedule of receipts based on actual receipt times.

Unfortunately, it turns out that the accounts package isn't able to give me the data that I need to determine the time between invoice and payment (it's a legal CMS with a VERY basic accounting package bolted on). Basically, the receipt details do not contain an invoice ref so there's no way of tying up an invoice with a receipt. Also, as this is a law firm, there's not always a receipt, sometimes invoices are absorbed by surplus client funds and the office receipt is actually a sweep up done by accounts staff periodically.

I think I will have to continue with the 'goal-seek' method until we're able to upgrade to a 'decent' accounts package.

Or you could just guess. Or estimate as we prefer to call it.

Your very basic error is to take a 77 day debtor period and interpret that as debtor receipts come in evenly over the first 77 days after the end of the month of invoice. It means nothing of the sort.

Thanks (0)
avatar
to lionofludesch
11th Jul 2017 11:48

yep - I get that now, the 77 is just a proportion of annual sales where the annual sales equates to 365.

...and the receipts profile is something completely different.

Thanks (0)
avatar
11th Jul 2017 11:57

" there's no way of tying up an invoice with a receipt"

Well I can't believe that's actually true, otherwise you've got absolutely no way of knowing what's in your trade debtors listing and due from each client.

It just means that you've got to do a little more work in order to find the numbers you want, by examining the office receipts on each day, you'll have to use whatever method is available (even if the firms using old manual "chits") to then trace that back to the original invoice, then note the debtor days from that. Repeat for each day over the month. And then perhaps for a few other recent months, if it's not too time-consuming, or if the last month isn't a typical one.

If the firm draws down funds from the client account on a periodic basis, then even a 'decent accounts package' won't be able to help you anymore than your existing one.

A useful bit of financial advice for the firm, other than the budget work that you're doing, could be to get the fee-earners to go through each and every client ledger one by one and establish what funds are available to draw down in respect of the firms fees, sounds like the firm could be sat on a decent level of funds. Once that one-off exercise is complete then get the fee-earners/managers to review their case files every month at least, and close down old client matters promptly, which would of course please the accountant when undertaking their SRA work too!

As this is a law firm, do they only do 1 type of work? My concern would be that if for example they undertake industrial disease work then debtor days could be 1,000, whereas RTA work via the portal could be 20 days, litigated RTA work could be 400 days, and of course this only relates to recoverable disbursements, the actual debtor days on fees may be only 1 day, commercial work may be 30 days, conveyancing 1 day, CFA work could be absolutely anything. So you may end up with 10 different debtor days, as it relates to different types of work. Trying to establish 1 debtor day figure could be a little misleading.

Would be interesting to know the mix of fee types.

Thanks (0)
avatar
to Sheepy306
11th Jul 2017 12:24

Sheepy306 wrote:

" there's no way of tying up an invoice with a receipt"

Well I can't believe that's actually true, otherwise you've got absolutely no way of knowing what's in your trade debtors listing and due from each client.


It's an unbelievably bad system from the 90's.
From a database point of view, receipts lines only contain the matter ref and don't contain a reference for the invoice that it's paying. So, when looking at the matter as a whole, you can see the total balance and every transaction within but there isn't a direct payment allocation.
The Invoice line in the database does get updated when a payment is made, but it only gets a 'paid' flag and an amount that was paid (whereas a modern system would also include the payment transaction number so that the payment can be linked to the invoice).
The Aged Debt is basically an aged listing of all invoices where the 'paid' flag <> 0 and the paid amount <> 0.

There are two many transactions to tackle this manually but I can calculate debtor days per department and use that as a basis of forecasting the new year.

We do work in the following areas..
- Residential conveyancing
- Commercial Conveyancing
- Remortgages
- Civil Litigation
- Insurance costs
- Wills & Probate
- Employment law
- Commercial law

So we do have a diverse range of debtor days, each worthy of a separate calculation.

UPDATE - while digging around in SQL I have just found that each invoice line does have a 'date paid' field, so that's that problem solved!

Thanks (0)
By tom123
to Numberwang
13th Jul 2017 15:22

At least you have a chance, then, of manipulating the data 'outside' the software, so to speak.

If I couldn't do that (via ODBC queries in my case) I think I would run a mile..

Thanks (0)
avatar
to tom123
13th Jul 2017 15:34

Absolutely!
I wouldn't be able to do anything without SQL/ODBC. The standard reports in the system are inadequate to say the least...and a lot of them don't work.

...I was told "we don't do balance sheets" when I started! It still sends a shiver down my spine!

Thanks (1)
13th Jul 2017 13:27

I would

a) forecast each month's sales invoicing (i.e. forecast turnover plus increase in deferred income less increase in accrued income) = net sales invoiced plus VAT
b) using historic data, estimate the % of each months sales invoicing that is collected in the current month, 1 month later, 2 months later, etc.
c) apply the typical collection profile to the forecast sales invoicing to get your forecast of debtor collections.
d) ignore the debtor days as it is not appropriate to this type of forecast.

Thanks (2)
avatar
14th Jul 2017 02:06

You've already separately forecast your monthly sales (excl VAT), and have what I believe is a reasonable methodology for forecasting the EOM debtors balance (though you should add VAT to the amounts calculated from the VAT exclusive sales, as it is the inclusive amount that is invoiced and outstanding until paid.)
With the above values you should be able to derive this month's receipts as last month debtors balance plus this month sales less this month debtors balance.

Thanks (0)
Share this content