Tax and GST

A vendor-wise spend report: finding who gets most of your money

How to build a vendor-wise spend report for a quarter, rank your payees, and use it to plan renegotiation and cleaner records.

Editorial illustration for A vendor-wise spend report: finding who gets most of your money

A vendor-wise spend report adds up everything you paid each payee over a period, then ranks them from largest to smallest. For most small businesses a handful of vendors take most of the money, and the ranked list tells you where a renegotiation or a cleaner record is worth the effort.

What do you need to build it?

A list of every payment for the quarter with at least three columns: date, payee and amount. Bank and card statements are the best source since they are complete; add cash payments from your cash book. Pick a clear period, such as April to June, so quarters can be compared later.

How do you group payments by payee?

  1. Put all payments in one sheet. Column A: date, column B: payee, column C: amount.
  2. Clean the payee names first. The same dairy may appear as three different strings on a statement. Pick one name per vendor and rewrite the rest to match.
  3. List each unique vendor once in column E, starting at E2.
  4. In F2 enter =SUMIFS($C$2:$C$500, $B$2:$B$500, E2) and copy it down. This adds the amounts for each vendor.
  5. In G2 enter =F2/SUM($F$2:$F$30) and format as a percentage to see each vendor's share.
  6. Sort the vendor list by column F, largest first. In Excel or Google Sheets, select the vendor and total columns before sorting so the rows stay together.

A worked example: a cafe with fifteen vendors

Say a cafe paid ₹6,00,000 to fifteen vendors in a quarter. The five largest look like this.

VendorQuarter totalShare of spend
Dairy₹1,20,00020%
Coffee beans₹96,00016%
Bakery supplier₹84,00014%
Gas cylinders₹54,0009%
Packaging₹48,0008%

Together these five come to ₹4,02,000, which is 67% of the total. The other ten vendors share the remaining ₹1,98,000. These numbers are made up, but the pattern is common: a short head and a long tail.

What should you do with the ranked list?

  • Top of the list: decide which vendors are worth a conversation about rates, payment timing or a monthly arrangement. A small percentage improvement on a ₹1,20,000 vendor is worth more than a large one on a ₹2,000 vendor.
  • Middle: check whether two vendors supply the same thing and whether one could cover both.
  • Long tail: look for vendors you pay once a quarter. They are usually where missing bills and vague payee names hide, so tidy those records first.

Which vendors deserve a closer look?

Look for a vendor whose total jumped from the previous quarter, for payments split into several smaller ones, and for a payee name you do not recognise. None of these is wrong by itself, but each needs a note. Compare this quarter with the last using the same method so the comparison is fair.

What mistakes distort the report?

  • Mixing personal and business payments. Remove anything that is not business spend before totalling, or the top of your list will be wrong.
  • Counting a payment twice because it appears both in the cash book and in a bank withdrawal. A cash withdrawal is not a vendor; the vendors are whom you paid with that cash.
  • Leaving out card and UPI payments made by staff. Ask them for their records or reimbursement claims so the vendor total is complete.
  • Comparing a quarter that includes a festival rush with one that does not, without saying so. Add a short note beside any vendor whose total moved a lot.

What to do this week

  1. Collect the last quarter's payments in one sheet.
  2. Standardise the payee names.
  3. Total by vendor and rank.
  4. Mark the top five for a rate conversation and the oddest three for a records check.
SPENDIXAI JOURNAL

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

Explore your reports