Calculating days sales outstanding
Bernie,
Thank you very much for this code.
I was trying to avoid VBA to keep things simple for other users of the
spreadsheet! Obviously I failed, as my formulae are impenetrable and the
custom function is very elegant and more robust, so thanks again.
I did add a myMonth parameter to replace mySale.Offset(i, 1).Value with
Day(DateSerial(Year(myMonth.Offset(i)), thus eliminating a column offset
that could have upset the function in future.
Tim
p.s. the final function looked like this, if anyone ever needs it:
Function DebtorDays(myDebt As Range, mySale As Range, _
myMonth As Range, Optional per As String) As Integer
Dim i As Integer
Dim SaleSum As Double
Dim days As Integer
If per = "" then per = "D"
i = 1
SaleSum = 0
While SaleSum < myDebt.Value
i = i + 1
SaleSum = SaleSum + mySale.Offset(i).Value
If Per = "D" Then
days = days + Day(DateSerial(Year(myMonth.Offset(i)), _
Month(myMonth.Offset(i)) + 1, 0)) * _
IIf(SaleSum < myDebt.Value, 1, _
1 + (myDebt.Value  SaleSum) / _
mySale.Offset(i).Value)
End If
Wend
DebtorDays = IIf(Per = "D", days, i)
End Function
"Bernie Deitrick" wrote:
Tim,
As a matter of fact, I AM glad I asked ;)
If you don't mind using VBA, copy this code into a regular codemodule of the workbook, and use it
like this (on row 7, then copied up and down):
To get the months:
=BackSum(C7,B7,"M")
To get the days:
=BackSum(C7,B7,"D")
'Code he
Function BackSum(mySale As Range, _
myDebt As Range, _
Per As String) As Integer
Dim i As Integer
Dim SaleSum As Double
Dim days As Integer
i = 1
SaleSum = 0
While SaleSum < myDebt.Value
i = i + 1
SaleSum = SaleSum + mySale.Offset(i).Value
If Per = "D" Then
days = days + mySale.Offset(i, 1).Value * _
IIf(SaleSum < myDebt.Value, 1, _
1 + (myDebt.Value  SaleSum) / _
mySale.Offset(i).Value)
End If
Wend
BackSum = IIf(Per = "D", days, i)
End Function
HTH,
Bernie
MS Excel MVP
"Tim Green" wrote in message
...
Bernie,
Thanks for your help.
The problem is that I don't know how many months back I need to go to cover
the value of sales that are outstanding for the month in question. Some
accounts have long payment terms or are behind, so there may be several
months outstanding.
I have now got this working as follows:
Month Debtor Sales Days Months Debtor days
Jan08 2,501 1203 31 #REF! #REF!
Feb08 2,446 1228 29 #REF! #REF!
Mar08 2,323 1003 31 2 62
Apr08 2,419 1181 30 2 67
May08 2,299 1065 31 2 63
Jun08 1,834 1020 30 1 54
The formula in the June row and Months column is as follows:
=IF($C7$B7,0,IF(SUM($C6:$C7)$B7,1,IF(SUM($C5:$C7 )$B7,2,IF(SUM($C4:$C7)$B7,3,IF(SUM($C3:$C7)$B7, 4,IF(SUM($C2:$C7)$B7,5,"Problem"))))))
The result of this formula is the number of full months' sales included in
the outstanding balance. In this case, only the current month's sales are
outstanding in full.
The formula in the Debtor days column is as follows:
=IF($B7<$C7,$B7/$C7*$D7,SUM(OFFSET($D7,($E71)*1,0):$D7)+($B7SUM(OFFSET($C7,($E71)*1,0):$C7))/OFFSET($C7,($E7)*1,0)*OFFSET($C7,($E7)*1,1))
This formula takes the full number of days for the months in the previous
column. It then subtracts the sales from those months from the current
outstanding debt to get the total to pro rate for the previous month. i.e. if
I have 3 and a bit months' sales outstanding, I'll take the number of days in
the previous 3 months, subtract those 3 months' sales from the current
debtor, then pro rate the remainder against the 4 month's sales to calculate
the number of days from the 4th month.
I'm now happy this setup works, though it is extremely clunky, and I don't
like using offset, as now if I add a column the formula will fall over. Ho
hum.
The remaining problem now is if I get a debt older than 5 months. Is there a
way to rewrite that first formula to give me the number of full months'
sales represented by the current debtor, regardless of how long ago it was? I
only stopped at 5 months because I hit the nesting limit.
I bet you're glad you asked now!
Thanks,
Tim
"Bernie Deitrick" wrote:
Tim,
With your table in cells A1:E7, in F7 I get 55 if I use the formula
=ROUND(D7+(B7C7)/C6*D6,0)
but I don't get all of your other desired results....
Wanted My Formula
66 66 This one works...
70 73
66 65
55 55 This one works....
HTH,
Bernie
MS Excel MVP
"Tim Green" wrote in message
...
I have a spreadsheet showing sales and outstanding debts monthbymonth. I
need a formula to calculate how many days' sales the outstanding debt
represents.
I need to do this by countback. e.g. for June 08 below, the calculation is
30 days (because debtors is greater than June's sales, so all the days in
June must be outstanding) plus (18731020)/1065*31.
Month Debtor Sales Days Desired result
Jan08 2569 1203 31 
Feb08 2614 1228 29 
Mar08 2471 1003 31 66
Apr08 2561 1181 30 70
May08 2416 1065 31 66
Jun08 1873 1020 30 55
Any help would be greatly appreciated.
Tim
