Vendor Management System in Excel: Purchase Orders, Bills, Payments, Payable Ageing and Vendor Scorecard

By Ravindra Bagale · Last updated: 17 October 2026

Download the template: vendor-management-system-template.xlsx: a 7-sheet system with example data and real formulas (Read me, Setup, Purchase orders, Bills, Payments, Report, Dashboard). Yellow cells are inputs you can type over; green cells hold formulas; blue rows are headers and totals. Replace the example data with your own and every report and the dashboard recalculate.

Made-up data: every name, amount, business and date on this page is made up for practice. Cities are used only to make the examples feel real.

1. The business story

Bagale Engineering Works, as of 17 October 2026 (made-up vendors and amounts).

Bill due date = bill date + the vendor's payment terms; a payment is typed once against its bill number. For a simple personal version of due-date tracking see the bill payment reminder.

2. Build it sheet by sheet

Step 1. Setup: vendors and terms

1. On Setup, type the as-of date in B3. In A6:F10 list Vendor ID, Vendor, City, Category, Payment terms (days), Quality rating (1-5).
2. Keep the categories and payment modes as lists in columns G and H for drop-downs (drop-down lists).

Step 2. Purchase orders

1. On Purchase orders, type PO no, PO date, Vendor ID, Item, Amount, Expected by, Received on. The vendor name D5: =XLOOKUP(C5,Setup!$A$6:$A$10,Setup!$B$6:$B$10,"Check ID") (XLOOKUP).
2. Status I5: =IF(H5="",IF(G5<Setup!$B$3,"Late, pending","Pending"),IF(H5<=G5,"On time","Late")).
3. Days late J5: =IF(H5="",MAX(0,Setup!$B$3-G5),MAX(0,H5-G5)).

Step 3. Bills: due date, paid and ageing

1. On Bills, type bill no, date, vendor ID, PO no and amount. Due date G5: =B5+XLOOKUP(C5,Setup!$A$6:$A$10,Setup!$E$6:$E$10).
2. Paid H5: =SUMIFS(Payments!$D$5:$D$80,Payments!$B$5:$B$80,A5): all part payments against this bill (SUMIFS). Balance I5: =F5-H5.
3. Days overdue J5: =IF(I5>0,MAX(0,Setup!$B$3-G5),0) and ageing K5: =IF(I5=0,"Paid",IF(J5=0,"Not due",IF(J5<=30,"1-30",IF(J5<=60,"31-60","60+")))) (nested IF).
4. Colour 31-60 and 60+ red. B-201 from Amir Tools is 32 days overdue: 31-60.

Step 4. Payments

1. On Payments, one row per payment: date, bill no, amount, mode. The vendor C5 is found through the bill: =XLOOKUP(XLOOKUP(B5,Bills!$A$5:$A$60,Bills!$C$5:$C$60),Setup!$A$6:$A$10,Setup!$B$6:$B$10,"Check bill").
2. A bill paid in two parts gets two rows; the Bills sheet adds them up.

Step 5. Report: scorecard and ageing

1. On Report, per vendor: billed =SUMIFS(Bills!$F$5:$F$60,Bills!$C$5:$C$60,A5), paid, outstanding =C5-D5, overdue =SUMIFS(Bills!$I$5:$I$60,Bills!$C$5:$C$60,A5,Bills!$J$5:$J$60,">0").
2. POs =COUNTIF(...), on-time =COUNTIFS('Purchase orders'!$C$5:$C$60,A5,'Purchase orders'!$I$5:$I$60,"On time") and on-time % in I5 (COUNTIFS).
3. Score K5: =ROUND(50*I5+10*J5,0) (half delivery, half quality) and grade L5: =IF(K5>=80,"A",IF(K5>=60,"B","C")).
4. Ageing table from row 12: =SUMIFS(Bills!$I$5:$I$60,Bills!$K$5:$K$60,A13) and a COUNTIF of bills per bucket.

Step 6. Dashboard

1. KPI cards: billed A5 =Report!C10, outstanding B5, overdue C5, on-time share D5, open POs E5 =COUNTIFS('Purchase orders'!$A$5:$A$60,"<>",'Purchase orders'!$H$5:$H$60,"") and grade C vendors F5 (KPI cards).
2. Add a column chart of the ageing table and one of outstanding by vendor.

3. Formulas used

Every formula above is written in the template, so you can click a cell and see it in the formula bar. All values on this page were recalculated in LibreOffice with zero errors.

4. Common mistakes

1. Typing the due date by hand. Due dates should come from the vendor's terms; one typed date with a wrong month makes a bill look overdue or safe.
2. Changing the bill amount when paying part. Never reduce the bill. Add a Payments row; the balance falls by itself and the history stays.
3. Counting pending POs as late deliveries. A PO not yet received is Pending or Late, pending; the on-time % uses only received POs.
4. Ageing from the bill date. Ageing for payables counts days past the due date, not since the bill date; a bill with 45-day terms is not overdue on day 30.
5. Scoring only on price. Late material stops the floor. The score weighs delivery and quality; keep price in your purchase decision separately.

5. More real examples

Example 1: Restaurant buying vegetables daily. Use one PO per day per supplier and terms of 7 days; the ageing shows which supplier waits longest.

Example 2: Builder with 20 sub-contractors. Add a Site column to POs and Bills and a SUMIFS by site in the Report.

Example 3: Shop with credit suppliers. Keep only Bills and Payments; the ageing table alone tells you whom to pay this week.

Quick revision cards

Englishमराठीहिंदी

Quick revision

Each card shows formulas from this page, each with an example and its answer from the template. Scan the QR code on a card to open this lesson. All names and numbers are made up for practice.

More cards: Ravi Sir's Cards.

6. Frequently asked questions

How do I make an ageing report in Excel?

Compute days overdue = as-of date − due date for unpaid bills, then bucket with nested IF (Not due, 1-30, 31-60, 60+) and total each bucket with SUMIFS.

How do I handle part payments?

Log each payment with its bill number and use SUMIFS on the Payments sheet to total what is paid against each bill.

How is the due date calculated?

Bill date + the vendor's payment terms in days, looked up from Setup: =B5+XLOOKUP(C5,...terms...).

How do I score vendors?

Here score = 50 × on-time share + 10 × quality rating (1-5), out of 100; 80+ is A, 60-79 B, below 60 C. Change the weights to suit you.

Can I use this for customers instead of vendors?

Yes: rename Bills to Invoices and Payments to Receipts; the same ageing gives receivables.

7. Quick quiz

Ten questions on this page. Sign in to save your score.