Business

Which customers owe you the most? Ranking udhaar in 10 minutes

Rank udhaar by customer in ten minutes: sort balances, flag the top three and see what share of the total they hold, with a made-up 12-customer list.

Editorial illustration for Which customers owe you the most? Ranking udhaar in 10 minutes

To see who owes you the most, list every customer's balance in one column, sort it from largest to smallest, add a running total, and divide each running total by the grand total. In the made-up list below, three of twelve customers hold 59 percent of all udhaar. That is the group to call first, and the whole exercise takes about ten minutes.

Step 1: build one customer-wise list

Open the khata and write each customer with a balance and the current balance only: name in one column, balance in the next. If a family has one account, use one line. If you work on a sheet, put the names in A2 to A13 and the balances in B2 to B13, with a heading in row 1.

Step 2: sort and add a running total

On paper, rewrite the list from largest to smallest. In Excel or Google Sheets, select the table and use the sort option on the balance column, largest to smallest. In column C, put the running total with =SUM($B$2:B2) in C2 and copy it down. In column D, put the share with =C2/SUM($B$2:$B$13) and format it as a percentage.

Here is a made-up list of 12 customers, already sorted. The total is ₹36,300.

RankCustomerBalanceRunning total
1Sharma ji₹9,400₹9,400
2Verma ji₹6,200₹15,600
3Contractor Raju₹5,800₹21,400
4Mrs Khan₹3,100₹24,500
5Pandey ji₹2,700₹27,200
6Joshi ji₹2,400₹29,600
7Bansal ji₹1,900₹31,500
8Neha₹1,500₹33,000
9Dubey ji₹1,100₹34,100
10Salim₹900₹35,000
11Mrs Iyer₹700₹35,700
12Tiwari ji₹600₹36,300

Step 3: read the top three

The top three balances are ₹9,400 + ₹6,200 + ₹5,800 = ₹21,400. Divided by ₹36,300, that is about 59 percent. So the first customer alone holds about 26 percent (9,400 / 36,300), the first two about 43 percent, the first three about 59 percent. The other nine customers together hold the remaining ₹14,900.

If you only have a printed list, circle the largest three and add them with a calculator, then divide by the total. You do not need the full table to see the pattern.

What should you do about the top three?

  • Call each personally this week instead of sending a standard message.
  • Ask for a specific part payment and a specific date, and write both down.
  • Check each one against your credit limit. If they are above it, pause fresh credit on that page until the balance comes down.
  • Look at the age as well: a large balance that is two weeks old is not the same risk as a large one that is five months old.
  • Review the same list again in a month to see whether the top three shrank.

Can you do this in Excel or Google Sheets without sorting by hand?

In Excel 365 or Google Sheets, =LARGE($B$2:$B$13,1) returns the largest balance, =LARGE($B$2:$B$13,2) the second, and =LARGE($B$2:$B$13,3) the third. To see the name for the largest, use =INDEX($A$2:$A$13, MATCH(MAX($B$2:$B$13), $B$2:$B$13, 0)). The share held by the top three is =(LARGE($B$2:$B$13,1)+LARGE($B$2:$B$13,2)+LARGE($B$2:$B$13,3))/SUM($B$2:$B$13). These work in older Excel versions too.

One caution before you rank: make sure one family is not hiding as two or three lines. If Sharma ji, his wife and his son each have a page, the household may be far higher on the list than any single line suggests. Merge such pages for this exercise, or at least mark them, so the ranking reflects who really owes you money and not how the khata happens to be written.

Do this every month

  1. Update the balances after the month's last payments are entered.
  2. Sort, add running totals, and note the top-three share.
  3. Write the share in a notebook. If it keeps rising, a few customers are becoming a risk.
  4. Compare against last month and note anyone who has moved up.
  5. Decide on three calls to make this week.
SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports