I have a spreadsheet showing sales and outstanding debts month-by-month. 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 (1873-1020)/1065*31.

Month Debtor Sales Days Desired result
Jan-08 2569 1203 31 --
Feb-08 2614 1228 29 --
Mar-08 2471 1003 31 66
Apr-08 2561 1181 30 70
May-08 2416 1065 31 66
Jun-08 1873 1020 30 55

Any help would be greatly appreciated.

Tim

Hi Tim,

To calculate the days sales outstanding, you can use the following formula:

=Days Outstanding + ((Debtor - Sales)/Sales per Day)

Here's how you can apply this formula to your example:

1. In cell D2, enter the formula: =31+((C2-B2)/B2*31)
2. Copy the formula down to cells D3:D6.

This will give you the desired result for each month.

Let's take the example of June 08. The debtor is 1873 and the sales are 1020. Since the debtor is greater than June's sales, all the days in June must be outstanding. So, we add 30 days to the formula. Then, we subtract the sales from the debtor and divide it by the sales per day (which is 1065/31). This gives us 0.52. Multiplying this by 31 gives us 16.12. Adding this to 30 gives us the desired result of 55.

Tim,

With your table in cells A1:E7, in F7 I get 55 if I use the formula

=ROUND(D7+(B7-C7)/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 month-by-month. 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 (1873-1020)/1065*31.

Month Debtor Sales Days Desired result
Jan-08 2569 1203 31 --
Feb-08 2614 1228 29 --
Mar-08 2471 1003 31 66
Apr-08 2561 1181 30 70
May-08 2416 1065 31 66
Jun-08 1873 1020 30 55

Any help would be greatly appreciated.

Tim

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
Jan-08 2,501 1203 31 #REF! #REF!
Feb-08 2,446 1228 29 #REF! #REF!
Mar-08 2,323 1003 31 2 62
Apr-08 2,419 1181 30 2 67
May-08 2,299 1065 31 2 63
Jun-08 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,($E7-1)*-1,0):$D7)+($B7-SUM(OFFSET($C7,($E7-1)*-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 re-write 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+(B7-C7)/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 month-by-month. 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 (1873-1020)/1065*31.

Month Debtor Sales Days Desired result
Jan-08 2569 1203 31 --
Feb-08 2614 1228 29 --
Mar-08 2471 1003 31 66
Apr-08 2561 1181 30 70
May-08 2416 1065 31 66
Jun-08 1873 1020 30 55

Any help would be greatly appreciated.

Tim

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
Jan-08 2,501 1203 31 #REF! #REF!
Feb-08 2,446 1228 29 #REF! #REF!
Mar-08 2,323 1003 31 2 62
Apr-08 2,419 1181 30 2 67
May-08 2,299 1065 31 2 63
Jun-08 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,($E7-1)*-1,0):$D7)+($B7-SUM(OFFSET($C7,($E7-1)*-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 re-write 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+(B7-C7)/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 month-by-month. 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 (1873-1020)/1065*31.

Month Debtor Sales Days Desired result
Jan-08 2569 1203 31 --
Feb-08 2614 1228 29 --
Mar-08 2471 1003 31 66
Apr-08 2561 1181 30 70
May-08 2416 1065 31 66
Jun-08 1873 1020 30 55

Any help would be greatly appreciated.

Tim

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
Jan-08 2,501 1203 31 #REF! #REF!
Feb-08 2,446 1228 29 #REF! #REF!
Mar-08 2,323 1003 31 2 62
Apr-08 2,419 1181 30 2 67
May-08 2,299 1065 31 2 63
Jun-08 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,($E7-1)*-1,0):$D7)+($B7-SUM(OFFSET($C7,($E7-1)*-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 re-write 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+(B7-C7)/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 month-by-month. 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 (1873-1020)/1065*31.

Month Debtor Sales Days Desired result
Jan-08 2569 1203 31 --
Feb-08 2614 1228 29 --
Mar-08 2471 1003 31 66
Apr-08 2561 1181 30 70
May-08 2416 1065 31 66
Jun-08 1873 1020 30 55

Any help would be greatly appreciated.

Tim