Results 1 to 10 of 10
Thread: DSO Calculation (Excel 2003 SP1)

20051220, 22:05 #1
 Join Date
 Jul 2004
 Location
 Sumner, Washington, USA
 Posts
 112
 Thanks
 0
 Thanked 0 Times in 0 Posts
DSO Calculation (Excel 2003 SP1)
I need a formula to calculate DSO (Days Sales Outstanding) using the methodology illustrated on the attached worksheet. The top sample shows a single calculation with explanation. Below that is an example of how it really works, with each row requiring different ranges to meet the criteria established in the methodology. The cells highlighted in pink are the ones I hope to replace with a single formula that works for any range of values, be it two months or fifteen months worth of entries.
As always, many thanks!
Karyl

20051220, 23:36 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: DSO Calculation (Excel 2003 SP1)
Does the attached do what you want? If you need it to be more flexible, a userdefined VBA function would probably be easier than formulas.

20051220, 23:44 #3
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: DSO Calculation (Excel 2003 SP1)
Does this do what you want?
I added some intermediate formulas.
Steve

20051220, 23:48 #4
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: DSO Calculation (Excel 2003 SP1)
You seemed to have a similar approach as I took, though you do not seem to allow for changing when December data is added...
Steve

20051220, 23:55 #5
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: DSO Calculation (Excel 2003 SP1)
Your approach is more flexible than mine. <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>

20051221, 00:28 #6
 Join Date
 Jul 2004
 Location
 Sumner, Washington, USA
 Posts
 112
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: DSO Calculation (Excel 2003 SP1)
I knew you guys would be able to come up with something! Give me a couple days to digest what you've done (Excel is not my native language, so it takes me awhile), and I'll let you know how it works out.
Do you think a userdefined function would be a better approach? That is an acceptable alternative if it would be better or easier, as long as someone continues to point me in the right direction. The approach that made the most sense to me was some type of "Do Until" loop on the range, but I wasn't sure if Excel supported that type of functionality.
Many thanks!
Karyl

20051221, 00:35 #7
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: DSO Calculation (Excel 2003 SP1)
I'd recommend testing Steve's approach first  if it does what you want you don't need a userdefined function.

20051221, 12:50 #8
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: DSO Calculation (Excel 2003 SP1)
I thought of an improvement with less intermediate calcs:
Move J22:J24 to K18:K20
In I10 put in the formula:
=INDEX(B18:H18,K18+1)/INDEX(B14:H14,K18)*INDEX($B$1:$H$1,K18)+SUM(OFFSET ($B$1,0,K18,1,J18K18))
Copy I10 to I11:I12
Then you can delete rows 22:24
You only need the intermediate calcs in rows 18:20 now.
Steve

20051221, 13:19 #9
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: DSO Calculation (Excel 2003 SP1)
If you want to avoid the intermediate calcs, you can add this routine to a module:
<pre>Option Explicit
Function DSO(rARWIP As Range, rRev As Range, _
rDays As Range)
Dim iLast As Integer
Dim iStart As Integer
Dim i As Integer
Dim dRevSum As Double
Dim iDays As Integer
i = rARWIP.Count
If i <> rRev.Count Or _
i <> rDays.Count Or _
Application.WorksheetFunction. _
Count(rARWIP) = 0 Then
DSO = CVErr(xlErrNA)
GoTo ExitRoutine
End If
Do While IsEmpty(rARWIP.Cells(i))
i = i  1
Loop
dRevSum = rARWIP.Cells(i).Value
iDays = 0
Do While dRevSum  rRev.Cells(i).Value > 0
iDays = iDays + rDays.Cells(i).Value
dRevSum = dRevSum  rRev.Cells(i).Value
i = i  1
If i = 0 Then
DSO = CVErr(xlErrNA)
GoTo ExitRoutine
End If
Loop
DSO = dRevSum / rRev.Cells(i).Value * rDays.Cells(i).Value + iDays
ExitRoutine:
Set rARWIP = Nothing
Set rRev = Nothing
Set rDays = Nothing
End Function</pre>
And put in I10
=DSO(B10:H10,B14:H14,$B$1:$H$1)
and copy I10 to I11:I12 and it should be what you want...
Steve

20051230, 07:54 #10
 Join Date
 Jul 2004
 Location
 Sumner, Washington, USA
 Posts
 112
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: DSO Calculation (Excel 2003 SP1)
Sorry for not getting back to you sooner. Other commitments, then Christmas and a bad cold, put this project on hold until tonight.
Anyway, I added the function to my client's sheet, and I used it to do her November calculations over again. Out of 30 rows, 23 were exact matches to her values and seven were different, from as little as a fraction of a day to as much as slightly over four days. So, now I'm sending it back to her to recheck her formulas on those seven. I suspect hers contain errors, since they were manually generated, but there might also be something about those seven that break the function. Once we've figured that out, I'll get back to you.
Thanks again for offering to help with this. The UDF is what I was hoping to be able to offer to her as a solution. I just didn't have a clue about how to make it work.
Karyl