Blogger
Share this content
0
14
35824

Debtor & Creditor Days Calculation

Hi,all.

As someone relatively new to corporate banking, and with no accountancy qualification experience, how do I calculate Debtor and Credit Days for say month 1, month 2, month 3 etc. of a company's financial year? Obviously over a 12 month period this seems straight forward - e.g. Debtor Days / Sales x 365, but what calculation should I input to Excel for other periods. Taking 25% for 3 months seems too simple, and I wonder if I should be looking at a TTM period for more accuracy? Any guidance would be much apprectiated.

Replies

Please login or register to join the discussion.

12th Aug 2012 06:25

Think what you are saying

"how do I calculate Debtor and Credit Days for say month 1, month 2, month 3 etc. of a company's financial year?"

What does that mean?

Thanks (0)
avatar
12th Aug 2012 10:30

Hi, Peter.

Apologies if I was unclear in what I was trying to establish.

Basically, we receive monthly management accounts from each of our clients, and I've been tasked with creating an Excel spreadsheet to allow the monthly, year to date and trailing twelve month figures to be recorded  -  an ongoing "runner", if you like.

The spreadsheet also has to incoporate a calculation for Debtor, Creditor and Stock days.

What I'm trying to establish is the best way to do this for periods other than financial year end  -  i.e. what is the company's Debtor Days at say half year.

Does that make sense?

Thanks (1)
12th Aug 2012 10:41

You are always looking at 12 months

At any date that you have to report Debtor and Creditor Days you need to use the sales/expenses (including VAT) in the previous 12 months and the debtors/creditors on the date in question.

Thanks (0)
avatar
12th Aug 2012 11:02

Thanks, Peter.

Your help is clarifying what I need to do is greatly appreciated.  Thanks again.

Thanks (0)
12th Aug 2012 11:09

Steven

My pleasure.

Thanks (0)
avatar
12th Aug 2012 15:15

Not always the case

Peters method works but I think is a simplification. Better to use countback.

Compare the debtors with the most recent month(s) sales as this gives a more accurate view. Eg, debtors of £30K and gross sales of £30K in last month would mean debtor days of 30, irrespective of the total sales for the year.

This matches the debtors most closely to the actual sales giving rise to them It doesn't work too well when a there are some large but v slow moving debtors though

Thanks (0)
avatar
12th Aug 2012 16:11

Hi, Zara.

Please let me see if I understand this.

(Debtors £30k / Sales £30k) x 365 / 12 (presumably?) = 30.4 days  -  would that be correct?

If I assume that the above is the monthly figures for January 2012, and February 2012 figures are Debtors £40 and Sales £35k, is Debtor Days for February then:

(Debtors £40k / Sales £35k) x 365 / 12 = 34.7 days ?

If so, and if that gives the Debtor Days for the individual month, what about Peter's comments of calculating this on a 12 month period.  If I have Management Accounts to February 2012 and the Company's FYE is say 30th September, does this mean (as suggested by Peter) that my Debtors would be taken as at end February 2012 but Sales would need to be based on a trailing 12 month basis to give me a more representative calculation - e.g.

(Debtors £40k / TTM Sales of say £400k) x 365 = 36.5 days?

How I wish I became an Accountant before I entered banking !!!!

Thanks (0)
avatar
12th Aug 2012 16:44

clarification

Steven1970 wrote:

Hi, Zara.

Please let me see if I understand this.

(Debtors £30k / Sales £30k) x 365 / 12 (presumably?) = 30.4 days  -  would that be correct?

If I assume that the above is the monthly figures for January 2012, and February 2012 figures are Debtors £40 and Sales £35k, is Debtor Days for February then:

(Debtors £40k / Sales £35k) x 365 / 12 = 34.7 days ?

If so, and if that gives the Debtor Days for the individual month, what about Peter's comments of calculating this on a 12 month period.  If I have Management Accounts to February 2012 and the Company's FYE is say 30th September, does this mean (as suggested by Peter) that my Debtors would be taken as at end February 2012 but Sales would need to be based on a trailing 12 month basis to give me a more representative calculation - e.g.

(Debtors £40k / TTM Sales of say £400k) x 365 = 36.5 days?

How I wish I became an Accountant before I entered banking !!!!

Yes, except that sales are shown nett of VAT and debtors include VAT, so sales of £25K give rise to debtors of £30K (25*1.2).

Also I would pick the countback period dependent upon the usual range of debtor days. Eg if debtor days are usually in the region of 45-60 days, I would compare the debtors with the last two months sales (and divide by 6 not 12), but remember to adjust for VAT. As I said beforeit does not work too well in situations where there are a few large but sluggish debtors, and is better suited to situations where debtors pay in a similar period. Also Peters method is just a simplification, and can be used where you dont have detailed monthly sales info.

EDIT: It works really well for highly seasonal businesses, where Peters simplified method would come a bit unstuck.

Thanks (1)
12th Aug 2012 16:46

My view

You have the information for a year so why not use it?

Working out the Debtor days based on one month is likely to be misleading if monthly sales  are not similar. Why add an unnecessary complication?

Remember to use sales + associated VAT!

Thanks (0)
12th Aug 2012 16:52

Highly seasonal businesses

I take the point about highly seasonal businesses, but I don't think you should be calculating to not show seasonality. What I think you should be doing in these cases is not to change how you calculate but compare your ratios, etc. to the same period a year previously. You would compare sales to previous years not previous months so I would calculate everything the same but compare to previous years.

Thanks (1)
By tom123
12th Aug 2012 16:56

Consistency

Hi Steven

 

As you can see from the answers given, there are a number of different approaches, depending on the level of detail of information available.

I would recommend including a comment in your report about the method of calculation you have chosen, so that the reader of the file is aware.

Thanks (1)
avatar
12th Aug 2012 17:28

Peter, Zara, Tom  -  many

Peter, Zara, Tom  -  many thanks to each of you for taking the time to respond.  It's greatly appreciated and has certainly educated me over the course of today.  I have to say, that this is one of the most helpful sites I come across which is all down to you guys.  Thanks again!

Thanks (1)
avatar
13th Aug 2012 13:03

Steven

If you have any clients who sell large 'stuff', such as machinery, or who otherwise take substantial deposits from their own clients (and invoice them) make sure that deposit debtors are seperated out from the rest; otherwise you get wild figures.

Thanks (1)
avatar
14th Aug 2012 10:55

Thanks, Richard.

Thanks (0)