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.
| Rank | Customer | Balance | Running total |
|---|---|---|---|
| 1 | Sharma ji | ₹9,400 | ₹9,400 |
| 2 | Verma ji | ₹6,200 | ₹15,600 |
| 3 | Contractor Raju | ₹5,800 | ₹21,400 |
| 4 | Mrs Khan | ₹3,100 | ₹24,500 |
| 5 | Pandey ji | ₹2,700 | ₹27,200 |
| 6 | Joshi ji | ₹2,400 | ₹29,600 |
| 7 | Bansal ji | ₹1,900 | ₹31,500 |
| 8 | Neha | ₹1,500 | ₹33,000 |
| 9 | Dubey ji | ₹1,100 | ₹34,100 |
| 10 | Salim | ₹900 | ₹35,000 |
| 11 | Mrs Iyer | ₹700 | ₹35,700 |
| 12 | Tiwari 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
- Update the balances after the month's last payments are entered.
- Sort, add running totals, and note the top-three share.
- Write the share in a notebook. If it keeps rising, a few customers are becoming a risk.
- Compare against last month and note anyone who has moved up.
- Decide on three calls to make this week.
Useful ideas start with a clearer picture of your own spending.
Explore your reports
