I have a $10k receivable in 2009 and $6k is collected giving a 60%
collection ratio at the end of 2009. In 2010, the remaining $4k is
collected. Looking at performance over a 2-yr period, 100% of the
receivable is collected. How do I measure the performance in year 2?
Is it 1) $4k divided by $4k ($4k = $10k minus $6k collected in yr 1)
for a 100% collection ratio or, 2) should it be $4k divided by $10k?
You can define it any way you wish, since this appears to be an ad hoc
metric, nothing that I recognize as standard or common, albeit not to
say a bad one.
But since you wrote "performance __in_year__ 2", I would expect it to
be computed by #1, namely: annualReceivablesCollected /
annualInvoicedSales.
You also wrote "performance over a 2-yr period". That is a separate
measure, IMHO, which I would compute separately and differently,
namely: totalReceivablesCollected / totalInvoicedSales, where "total"
refers to the entire period (e.g. 2 years).
Lastly, what is the formula to calculate year 2? If there are more
than 2 years involved, how does the formula change?
It is difficult to provide a formula if you do not provide specifics,
i.e. cell references for relevant data.
Suppose A20 and A30 contains annual receivables collected and annual
invoiced sales for year 1, B20 and B30 for year 2, etc.
The annual percent collected for column A is
=A20/A30
formatted as Percentage. You can copy the formula across columns B, C
etc.
The cumulative percent collected is, starting in column A:
=SUM($A$20:A20) / SUM($A$30:A30)
As you copy the formula across columns B, C etc, the relative
references A20 and A30 change, but not the absolute references $A$20
and $A$30, thereby computing cumulative sums. For example, the
numerator becomes SUM($A$20:B20).
PS: For standard measures of receivables, consider the Receivable
Turnover Ratio and Days Sales Outstanding, as well as Receivables
Aging.
RTR = annualInvoicedSales / annualAverageReceivables
DSO = 365 / RTR
where annualAverageReceivables is typically (beginningReceivables
+endingReceivables)/2.
Using my previous example with the added assumption that A10 contains
beginningReceivables for year 1 for example, the formulas for the
annual metrics would be:
RTR, in A32: =2*A30 / (A10+A20)
DSO: =365 / A32
In order to bullet-proof those formulas for the unlikely instances
where invoiced sales are zero or both beginning and ending receivables
are zero, consider the following:
RTR, in A32: =IF(A10*A20 = 0, 365, 2*A30 / (A10+A20))
DSO: =IF(A30 = 0, 365, 365/A32)
I'm not saying these are "good" metrics. I think they were invented
by Bob Crachit to give Ebenezer Scrooge a hasty metric so that Bob
could get home to Christmas dinner in time ;-).
But they are standard metrics used in financial analysis.
Receivables Aging is something else altogether. It goes beyond the
scope of your simple question.
