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 » Microsoft Excel Training and help » DSO Day Sales Outstanding in Excel

DSO Day Sales Outstanding in Excel

resolvedResolved · Urgent Priority · Version 2003

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

Costin has attended:
Powerpoint Intermediate Advanced course

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: Pricing & availability

Mon 17 Oct 2011, 21:40 replyReply Trainer 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...

DSO calculations.xls

Tue 18 Oct 2011, 09:38 replyReply Delegate 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:49 replyReply Trainer 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

Attached files...

DSO calculations_V2.xls

 

Please browse our web site to find out more about
excel+vba+training and other Microsoft training courses.

Excel tip:

Hiding and unhiding columns using the keyboard

CTRL + 0 hides your columns and CTRL + SHIFT + ) unhides them although you would need to highlight the column letters either side as per normal

View all Excel hints and tips



Excel training
Course rating
4.7 stars - based on 18023 reviews
Microsoft Certified Partner Institute of Leadership and Management - Certified Courses Learning and Performance Institute - Accredited Training Provider Security Seal verified by visa, mastercard securecard

Connect with us: Google+ · Facebook · Twitter · LinkedIn · Pinterest

2nd Floor, CA House, 1 Northey Street
Limehouse Basin, London, E14 8BT
United Kingdom