# Thread: DSO Calculation (Excel 2003 SP1)

1. ## 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

2. ## Re: DSO Calculation (Excel 2003 SP1)

Does the attached do what you want? If you need it to be more flexible, a user-defined VBA function would probably be easier than formulas.

3. ## Re: DSO Calculation (Excel 2003 SP1)

Does this do what you want?

Steve

4. ## 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

5. ## 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>

6. ## 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 user-defined 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

7. ## 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 user-defined function.

8. ## 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,J18-K18))

Copy I10 to I11:I12
Then you can delete rows 22:24

You only need the intermediate calcs in rows 18:20 now.

Steve

9. ## 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

10. ## 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

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•