Hi All,
I'm using this function to calculate DSO
Function DSO(FS1, FS2, FS3, FS4, FS5, FS6, FS7, FS8, DAYS1, DAYS2, DAYS3, DAYS4, DAYS5, DAYS6, DAYS7, DAYS8, DEBT)
If DEBT - FS1 > 0 Then DSOC = DSOC + DAYS1
If DEBT - FS1 > 0 Then BAL1 = DEBT - FS1
If DEBT - FS1 <= 0 Then DSOC = ((DEBT / FS1) * DAYS1)
If BAL1 - FS2 > 0 Then DSOC = DSOC + DAYS2
If BAL1 - FS2 > 0 Then BAL2 = BAL1 - FS2
If BAL1 - FS2 <= 0 Then DSOC = DSOC + ((BAL1 / FS2) * DAYS2)
If BAL2 - FS3 > 0 Then DSOC = DSOC + DAYS3
If BAL2 - FS3 > 0 Then BAL3 = BAL2 - FS3
If BAL2 - FS3 <= 0 Then DSOC = DSOC + ((BAL2 / FS3) * DAYS3)
If BAL3 - FS4 > 0 Then DSOC = DSOC + DAYS4
If BAL3 - FS4 > 0 Then BAL4 = BAL3 - FS4
If BAL3 - FS4 <= 0 Then DSOC = DSOC + ((BAL3 / FS4) * DAYS4)
If BAL4 - FS5 > 0 Then DSOC = DSOC + DAYS5
If BAL4 - FS5 > 0 Then BAL5 = BAL4 - FS5
If BAL4 - FS5 <= 0 Then DSOC = DSOC + ((BAL4 / FS5) * DAYS5)
If BAL5 - FS6 > 0 Then DSOC = DSOC + DAYS6
If BAL5 - FS6 > 0 Then BAL6 = BAL5 - FS6
If BAL5 - FS6 <= 0 Then DSOC = DSOC + ((BAL5 / FS6) * DAYS6)
If BAL6 - FS7 > 0 Then DSOC = DSOC + DAYS7
If BAL6 - FS7 > 0 Then BAL7 = BAL6 - FS7
If BAL6 - FS7 <= 0 Then DSOC = DSOC + ((BAL6 / FS7) * DAYS7)
If BAL7 - FS8 > 0 Then DSOC = 0
If BAL7 - FS8 <= 0 Then DSOC = DSOC + ((BAL7 / FS8) * DAYS8)
DSO = DSOC
End Function
Now it seems pretty simple to me, but I have a few glitches.
Firstly when the first calculation takes place If the Debt is less then the FS1 then DSOC should be calculated. at Whatever is in DAY1 (which is 30) x the decimal value returned. Instead it just returns 0.
I think this is because of the very last part where it finishes with
If BAL7 - FS8 > 0 Then DSOC = 0
Logic would tell me that it shouldn't even get to that part as none of the calculations cause it too.
This is my first ever attempt at working with functions, so it's a little confusing for me.
Any help or advise?
I've uploaded a sheet with the details.
Column G has the Function in it.
thanks
Bookmarks