Results 1 to 10 of 10
  1. #1
    2 Star Lounger
    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

  2. #2
    Plutonium Lounger
    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 user-defined VBA function would probably be easier than formulas.

  3. #3
    WS Lounge VIP sdckapr's Avatar
    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

  4. #4
    WS Lounge VIP sdckapr's Avatar
    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

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

  6. #6
    2 Star Lounger
    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 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. #7
    Plutonium Lounger
    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 user-defined function.

  8. #8
    WS Lounge VIP sdckapr's Avatar
    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,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. #9
    WS Lounge VIP sdckapr's Avatar
    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

  10. #10
    2 Star Lounger
    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

Posting Permissions

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