Relative size factor (RSF) is a test to identify anomalies where the largest amount for subsets in a given key is outside the norm for those subsets. This test compares the top two amounts for each subset and calculates the RSF for each. In order to identify potential fraudulent activities in invoice payment data, one utilizes the largest and the second-largest amounts to calculate a ratio based on purchases that are grouped by vendors. You may read more on this topic here.
Here is a 3 column dataset. The first column is Vendor Number, the second is Invoice number and last is invoice amount. There can be multiple invoices per vendor. The objective is to determine the highest invoice value for a vendor and divide that by the second highest invoice value for that same vendor to get a ratio. The same needs to be done for all vendors. An interesting case in the dataset below is Vendor_No V4439 – there are 2 instances of highest value for this vendor i.e. 25,378.30 and another 2 instances of second highest value i.e. 24,068.25. The RSF for this case will be 25,378.30/24,068.25. If there is no instance of second highest value for a vendor, then the result should be 0.
The expected result is:
I have solved this question with the help of the PowerPivot. You may download my solution workbook from here.
Compute Relative Size Factor per vendor
{ 2 Comments }