DSO Days Sales Outstanding

[santhoshlk]

Dear frnds...

I am looking for a simple tool to calculate the DSO (Days Sales Outstanding).

Can anyone help me...

I can come up with few ideas, but still need to get some guides from u huyz.

thanks in advance

 

[santhoshlk]

Thanks a lot Andrew....

It works... and i think Iam in right path.

I just uploaded my workings
https://app.box.com/s/ifnlpvbljst9gsy038dl

where i stucked is formulas in cell C6 to C8 it gives error, since the data format in A6 to A8 is differs from

can you pls help me

 

[Andrew Poulsom]

In A6 copied down:

=EOMONTH(A5,-2)+1

 

[santhoshlk]

oh my god...... such a simple formula made my day easier...

thanks a lot, let me continue in day calculation formula

 

[santhoshlk]

In A6 copied down:

=EOMONTH(A5,-2)+1

is it possible to know how this formula works

 

[Andrew Poulsom]

The EOMONTH part returns the last day of the month 2 months before the date in A5. Adding 1 (day) returns the first of the next month.

 

[santhoshlk]

thanks Andrew

pls find attached my latest... working on days calculation

pls refer the Example so u may get a clear picture

https://app.box.com/s/ifnlpvbljst9gsy038dl

is it possible to have this calculation in a single formula or in a VBA coding returning the value in a pop up window

thanks in advance, and still searching in net for a coding

 

[Andrew Poulsom]

Does this work for you?

=IF(E5=0,0,ROUND((IF(C5,MIN(E5,C5)/C5,1)*B5)+(((E5-C5)>0)*IF(C6,(MIN((E5-C5),C6)/C6),1)*B6)+(((E5-SUM(C5:C6))>0)*IF(C7,(MIN((E5-SUM(C5:C6)),C7)/C7),1)*B7)+(((E5-SUM(C5:C7))>0)*IF(C8,(MIN((E5-SUM(C5:C7)),C8)/C8),1)*B8),0))

 

[santhoshlk]

Hats off... Andrew.

Works .... gr8... thanks a lot

 

[santhoshlk]

Dear Andrew,

can u pls explain me how and logic this formula works, if i needed to modify in future

thanks in advance

 

[Andrew Poulsom]

What don't you understand about it? You can use the Evaluate Formula tool to see how it works.