dso day sales outstanding
Request a callback

We'll call during UK business hours

Name:
Number:
0207 987 3777

Forum home » Delegate support and help forum » » DSO Day Sales Outstanding in Excel

DSO Day Sales Outstanding in Excel

Resolved · Urgent Priority · Version 2003

Mon 17 Oct 2011, 09:53Delegate Costin said...

Costin has attended:

DSO Day Sales Outstanding in Excel

Do you have a template or do you know an excel formula for calculating debtor days outstanding (financial DSO)?

I have found the below explanation on the net but I am not sure if the formula at the end is correct.

"If you average \$30,000 a month in sales (\$1,000 per day), and your A/R Balance is \$30,000, then your DSO – A/R is \$30,000 / \$1,000 or 30 days.

If your A/R Balance is \$60,000, then your DSO – A/R is \$60,000 / \$1,000 or 60 days.

When you think about it, it makes sense: if it takes 60 days on average to collect the money, then at the end of two months of selling \$30,000 a month - from a standing start - you would have built up an Accounts Receivable

balance of \$60,000. Each month after that, you would collect \$30,000 from the 2nd prior month, and make another \$30,000 in sales, so that your Accounts Receivable balance would stay at a constant \$60,000.

Of course, neither Sales nor Accounts Receivable stay constant. The trick is to choose a time period over which to calculate Daily Sales. One month is too short, and a year too long – I favor a three month time period. Then you can calculate the DSO – A/R each month by taking the A/R Balance and dividing by the last three months worth of Sales, converted to a Daily Sales rate by dividing by 90.

Excel
Assume monthly values are in columns A, B, C, etc. You can calculate DSO – A/R starting in column C:

Sales
Accounts Receivable
DSO A/R in column C = C2/((A1+B1+C1)/90) in column D = D2/((B1+C1+D1)/90)"

For upcoming training course dates see:

Mon 17 Oct 2011, 21:40Trainer Rodney said...

RE: DSO Day Sales Outstanding in Excel

Hello Costin,

Thank you for your question regarding DSO - Day Sales Outstanding in Excel.

The calculation required for this is as follows:

Debtors(AR) x365 (days) / Turnover(Sales)

This calculation is based on the annual figures of a company.

If you want to calculate the days outstanding on a monthly basis then you use the same formula, but use the Outstanding AR / Month's Sales * 30

I have attached a sample file for you to look at.

Test it... I hope this is what you are looking for.

I hope this resolves your question. If it has, please mark this question as resolved.

If you require further assistance, please reply to this post. Or perhaps you have another Microsoft Office question?

Have a great day.
Regards,

Rodney
Microsoft Office Specialist Trainer

Attached files...

Tue 18 Oct 2011, 09:38Delegate Costin said...

RE: DSO Day Sales Outstanding in Excel

Hi Rodney,

Thank you for your attached file that I agree with.

I found as well on the internet the following complex DSO excel formula but I can not get around the formula in cell M7 or M8:

http://ashishmathur.com/Documents/Day%20sales%20outstanding.xls

Would you be able to prepare a template with the formula in cell M7 working that I only need to input figures in cells B7 to K7.

As the above formula considers only 10 months, I need to insert 2 more columns (2 extra months) so I can do it for a full financial year.

Thank you very much,

Costin

Tue 18 Oct 2011, 18:49Trainer Rodney said...

RE: DSO Day Sales Outstanding in Excel

Hello Costin,

I had a look at the file you were given and have decided that the method being used here is too complicated. I don't believe that you need to complicate things when the answer is relatively simple. Follow the accounting ratio principle of A/R divided by Total Sales * number of days depending on which month your calculation takes place.

I have added a revised version of the last workbook I sent you. See what you think.

I hope this resolves your question. If it has, please mark this question as resolved.

If you require further assistance, please reply to this post. Or perhaps you have another Microsoft Office question?

Have a great day.
Regards,

Rodney
Microsoft Office Specialist Trainer

Excel tip:

If you copy a web address the only way to paste into the address field of the hyperlink box is to use CTRL + V. Right click paste does not work.

View all Excel hints and tips

Training courses
London & UK

Live dates & prices »

On-site training

Get a quote »

Training information:

 Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions What does 'Resolved' mean? Any suggestions, questions or comments? Please post in the Improve the forum thread.

Excel training
 Course rating 4.7 stars - based on 25745 reviews