I manage a group of individuals who manage receivables. They are judged
on 10 and 30 day ratios as a percentage of total receivables. What I
struggle with is removing those account balances from the appropriate
buckets when they otherwise have no control over the management of the
receivable (division management dictates terms on balance, i.e., they
give the customer 3 months to pay the receivable item). At this
juncture, since the analyst has no control over the receivable, my goal
is to remove it from the numbers as though it never occured.

In other words, if the receivable item is in the 10-day bucket (< 10
days old), do I reduce both the 10-day balance and the total receivable
by the value of the item? Do I do nothing with the 30-day balance? What
do I do if the receivable is 30-day? It just never seems to work out
right. Can someone please provide me with a fail-safe methodology for
this pratice? I want to be fair and they expect and deserve the same.

Thank you very much!!

--

It may be me, but I understand very little of this post. Could you clarify
it by giving a couple of worked examples, the data, and the expected
results?

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Juan Valdez" wrote in message
...
I manage a group of individuals who manage receivables. They are judged
on 10 and 30 day ratios as a percentage of total receivables. What I
struggle with is removing those account balances from the appropriate
buckets when they otherwise have no control over the management of the
receivable (division management dictates terms on balance, i.e., they
give the customer 3 months to pay the receivable item). At this
juncture, since the analyst has no control over the receivable, my goal
is to remove it from the numbers as though it never occured.

In other words, if the receivable item is in the 10-day bucket (< 10
days old), do I reduce both the 10-day balance and the total receivable
by the value of the item? Do I do nothing with the 30-day balance? What
do I do if the receivable is 30-day? It just never seems to work out
right. Can someone please provide me with a fail-safe methodology for
this pratice? I want to be fair and they expect and deserve the same.

Thank you very much!!

--

Hello Juan,

Here is an example (send me an email if you need the sample file):

Let us assume the account names and their date information is in
Sheet1, cells A1:C6:
Account A Account B Account C
16/04/2006 28/03/2006 10/05/2006
28/04/2006 31/03/2006 09/05/2006
12/05/2006 11/05/2006 04/05/2006
30/03/2006 12/05/2006 14/04/2006
02/04/2006 21/04/2006 01/03/2006

Sheet2 shows employees' responsibilities and performance in cells
A1:F19:
Date: 38851

Employees Account Responsibilities 10 Days
Employee 1 Account A
Employee 2 Account A Account B
Employee 3 Account B Account C
Employee 4 Account C

Employees Account Responsibilities 30 Days
Employee 1 Account A
Employee 2 Account A
Employee 3 Account B
Employee 4 Account C

Performance Overview Total Accounts # <= 10 Days # <= 30 Days % <= 10
Days % <= 30 Days
Employee
1 =acc_per_emp_within_days($A16;$A$4:$Z$13;Sheet1!$A $1:$C$6;99999) =acc_per_emp_within_days($A16;$A$4:$Z$7;Sheet1!$A$ 1:$C$6;10) =acc_per_emp_within_days($A16;$A$4:$Z$7;Sheet1!$A$ 1:$C$6;30) =C16/$B16% =D16/$B16%
Employee
2 =acc_per_emp_within_days($A17;$A$4:$Z$13;Sheet1!$A $1:$C$6;99999) =acc_per_emp_within_days($A17;$A$4:$Z$7;Sheet1!$A$ 1:$C$6;10) =acc_per_emp_within_days($A17;$A$4:$Z$7;Sheet1!$A$ 1:$C$6;30) =C17/$B17% =D17/$B17%
Employee
3 =acc_per_emp_within_days($A18;$A$4:$Z$13;Sheet1!$A $1:$C$6;99999) =acc_per_emp_within_days($A18;$A$4:$Z$7;Sheet1!$A$ 1:$C$6;10) =acc_per_emp_within_days($A18;$A$4:$Z$7;Sheet1!$A$ 1:$C$6;30) =C18/$B18% =D18/$B18%
Employee
4 =acc_per_emp_within_days($A19;$A$4:$Z$13;Sheet1!$A $1:$C$6;99999) =acc_per_emp_within_days($A19;$A$4:$Z$7;Sheet1!$A$ 1:$C$6;10) =acc_per_emp_within_days($A19;$A$4:$Z$7;Sheet1!$A$ 1:$C$6;30) =C19/$B19% =D19/$B19%

(substitute ";" by "," - my German Excel takes ";" as param separator)

The UDF acc_per_emp_within_days:

Option Explicit

Function acc_per_emp_within_days(sE As String, _
rAR As Range, rAD As Range, lD As Long) As Long
Dim coll As New Collection
Dim lngSum As Long, lngIndex As Long
Dim lngFound As Long
Dim i As Long, j As Long
Dim s As String, r As Range

If rAR.Columns.Count < 2 Then
acc_per_emp_within_days = CVErr(xlErrValue)
Exit Function
End If

ReDim sA(1 To rAR.Columns.Count - 1) As String

On Error Resume Next

'Collect account names the employee is responsible for
For i = 1 To rAR.Rows.Count
If rAR.Cells(i, 1) = sE Then
For j = 2 To rAR.Columns.Count
If Not IsEmpty(rAR.Cells(i, j)) Then
s = rAR.Cells(i, j)
Err.Clear
lngFound = coll("X" & s)
If Err.Number < 0 Then
lngIndex = lngIndex + 1
coll.Add lngIndex, "X" & s
sA(lngIndex) = s
End If
End If
Next j
End If
Next i

'Count account details for the employee within lD
For i = 1 To rAD.Columns.Count
Err.Clear
lngFound = coll("X" & rAD.Cells(1, i))
If Err.Number = 0 Then
For j = 2 To rAD.Rows.Count
If Not IsEmpty(rAD.Cells(j, i)) Then
If rAD.Cells(j, i) + lD = Range("As_of_Date") Then
lngSum = lngSum + 1
End If
End If
Next j
End If
Next i

acc_per_emp_within_days = lngSum

End Function

Sheet2 values are then:
Date: 14/05/2006

Employees Account Responsibilities 10 Days
Employee 1 Account A
Employee 2 Account A Account B
Employee 3 Account B Account C
Employee 4 Account C

Employees Account Responsibilities 30 Days
Employee 1 Account A
Employee 2 Account A
Employee 3 Account B
Employee 4 Account C

Performance Overview Total Accounts # <= 10 Days # <= 30 Days % <= 10
Days % <= 30 Days
Employee 1 5 1 3 20 60
Employee 2 10 3 6 30 60
Employee 3 10 5 7 50 70
Employee 4 5 3 4 60 80

HTH,
Bernd