98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View ReviewsForum home » Delegate support and help forum » Microsoft Excel Training and help » DSO Day Sales Outstanding in Excel
DSO Day Sales Outstanding in Excel
Resolved · Urgent Priority · Version 2003
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)"
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...
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
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...
Training information:
See also:
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
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Quick Absolute Cell ReferencesWhen entering cell references in a formula you probably click the cell, or you may type in the cell reference. If you require any of the 4 variations, press the F4 key now before you press Enter to toggle around the relative and absolute entries ($signs). |