Build a Supplier Payment Run Proposal

Paying suppliers is usually done in runs — weekly or twice a month — and each run starts with a proposal: which invoices to pay this time, how much in total, and what is being held back and why. Accounts payable assembles it from the open invoice listing, a finance manager reviews and approves it, and only then are the payments released. Done by hand in a spreadsheet it is a filtering exercise with traps: an invoice that falls due two days after this run but before the next one gets paid late; a 2% early-payment discount expires unnoticed; a supplier's credit note is forgotten and they are overpaid; a disputed invoice slips through. This page builds the proposal with pandas from an open-invoices export, applies those rules explicitly, fits the run to the cash available, and writes a workbook that an approver can check quickly. It belongs to Building Finance and Operations Reports.

Prerequisites

pip install pandas openpyxl

The input is an export of open payables — one row per invoice or credit note with supplier, due date, amount, payment terms and an on-hold flag for disputed items — plus a supplier sheet with bank details. Any purchase ledger can produce something similar. This run is paid on Friday 2 October 2026 and the next run is on Friday 16 October.

Step 1 — Load and work out discount deadlines

import re
import pandas as pd

ap = pd.read_excel("ap_open_items.xlsx", sheet_name="Open items")
suppliers = pd.read_excel("ap_open_items.xlsx", sheet_name="Suppliers")
PAY_DATE, NEXT_RUN = pd.Timestamp("2026-10-02"), pd.Timestamp("2026-10-16")

def discount_terms(terms):
    m = re.match(r"(\d+(?:\.\d+)?)/(\d+) net \d+", str(terms))
    return (float(m.group(1)) / 100, int(m.group(2))) if m else (0.0, None)

terms = ap["Terms"].map(discount_terms)
ap["Disc %"] = [t[0] for t in terms]
ap["Disc deadline"] = [d + pd.Timedelta(days=t[1]) if t[1] else pd.NaT for d, t in zip(ap["Invoice date"], terms)]
ap = ap.merge(suppliers, on="Supplier", how="left")
print(ap[["Supplier", "Document", "Due date", "Amount", "Terms", "Disc deadline"]].to_string(index=False))

Terms like "2/10 net 30" mean 2% off if paid within 10 days of the invoice date, otherwise the full amount within 30. The deadline is computed per invoice from its date, which is the number that matters for this run. Joining the supplier sheet brings in bank details, so a missing IBAN can be caught before anything is proposed.

Step 2 — Decide what goes in this run

Each line gets a decision and a reason. An invoice is paid now if it falls due before the next run — waiting would make it late — or if paying now earns a discount that would be lost by waiting:

def decide(row):
    if row["On hold"]:
        return "exclude", "on hold (disputed)"
    if pd.isna(row["IBAN"]):
        return "exclude", "no bank details on file"
    if row["Amount"] < 0:
        return "pay", "credit note — offset against invoices"
    if pd.notna(row["Disc deadline"]) and PAY_DATE <= row["Disc deadline"] < NEXT_RUN:
        return "pay", f"take {row['Disc %']:.0%} discount (expires {row['Disc deadline']:%d %b})"
    if row["Due date"] < NEXT_RUN:
        return "pay", "overdue" if row["Due date"] < PAY_DATE else "due before next run"
    return "defer", f"due {row['Due date']:%d %b}, after next run"

ap[["Decision", "Reason"]] = ap.apply(decide, axis=1, result_type="expand")
ap["Discount"] = (ap["Amount"] * ap["Disc %"]).where(ap["Reason"].str.startswith("take"), 0.0).round(2)
ap["To pay"] = (ap["Amount"] - ap["Discount"]).where(ap["Decision"] == "pay", 0.0)
print(ap[["Supplier", "Document", "Decision", "Reason", "To pay"]].to_string(index=False))

Comparing with the next run date rather than today is the core of a payment run: an invoice due on 10 October must be paid on 2 October, because by 16 October it would be six days late. The Acme invoice from 28 September is not due until 28 October, yet it is paid now — its 2% discount expires on 8 October, before the next run, and 2% for paying three weeks early is worth more than holding the cash. The Fjord IT invoice, on 1/10 terms, is paid early for the same reason. Disputed invoices and suppliers without bank details are excluded with the reason spelled out, so nobody has to ask why.

Step 3 — Net per supplier and fit the cash limit

Payments go to suppliers, not invoices, so total the lines per supplier — credit notes reduce the payment — and check the total against the cash treasury has made available:

CASH_LIMIT = 30000.00
pay = ap[ap["Decision"] == "pay"]
by_supplier = (pay.groupby(["Supplier", "IBAN"], as_index=False)
               .agg(Lines=("Document", "count"), Earliest_due=("Due date", "min"), Discount=("Discount", "sum"), Payment=("To pay", "sum"))
               .sort_values("Earliest_due"))
room, in_run = CASH_LIMIT, []
for amount in by_supplier["Payment"]:
    in_run.append(amount <= room)
    room -= amount if amount <= room else 0
by_supplier["In run"] = in_run
print(by_supplier.drop(columns="IBAN").to_string(index=False))
print(f"proposed {by_supplier.loc[by_supplier['In run'], 'Payment'].sum():,.2f} of {by_supplier['Payment'].sum():,.2f}; "
      f"discounts taken {by_supplier.loc[by_supplier['In run'], 'Discount'].sum():,.2f}")

Acme's payment is its two invoices less the discount on one and less its 350 credit note. Suppliers are taken in order of their earliest due date, and each is included if it still fits in the remaining cash, so the most overdue are paid first and a large payment that does not fit does not block smaller ones behind it. Anything that does not fit is marked out of the run rather than silently dropped, so the approver can decide whether to find more cash or let it wait. Here 25,035.10 is proposed to five suppliers, taking 95.60 of discounts; Evergreen Facilities' 12,400 does not fit in what is left of the 30,000 and is flagged for the approver. Other priority rules — strategic suppliers first, or smallest suppliers first — are just a different sort key.

Step 4 — Write the proposal workbook

The approver needs four views: the payments by supplier, the invoice lines behind them, everything excluded or deferred with reasons, and a place to sign:

from openpyxl import load_workbook
from openpyxl.styles import Font, PatternFill

path = "payment_run_2026-10-02.xlsx"
detail_cols = ["Supplier", "Document", "Due date", "Amount", "Discount", "To pay", "Reason"]
with pd.ExcelWriter(path, engine="openpyxl", date_format="dd mmm yyyy", datetime_format="dd mmm yyyy") as xw:
    by_supplier.to_excel(xw, sheet_name="Proposal", index=False)
    pay[detail_cols].to_excel(xw, sheet_name="Lines", index=False)
    ap.loc[ap["Decision"] != "pay", ["Supplier", "Document", "Due date", "Amount", "Decision", "Reason"]].to_excel(xw, sheet_name="Not paid", index=False)

wb = load_workbook(path)
ws = wb["Proposal"]
n = ws.max_row
ws[f"E{n + 2}"], ws[f"F{n + 2}"] = "Total in run", f'=SUMIFS(F2:F{n},G2:G{n},TRUE)'
ws[f"E{n + 3}"], ws[f"F{n + 3}"] = "Cash limit", CASH_LIMIT
for c in (f"E{n + 2}", f"F{n + 2}"):
    ws[c].font = Font(bold=True)
for row in ws.iter_rows(min_row=2, max_row=n):
    if row[6].value is False:
        for cell in row:
            cell.fill = PatternFill("solid", fgColor="FFF4CE")
for col in "EF":
    for r in range(2, n + 4):
        ws[f"{col}{r}"].number_format = "#,##0.00"
sign = wb.create_sheet("Approval")
for r, label in enumerate(["Payment run", "Pay date", "Prepared by", "Approved by", "Date approved"], start=1):
    sign[f"A{r}"] = label
sign["B1"], sign["B2"], sign["B3"] = "Supplier payments", PAY_DATE.to_pydatetime(), "Accounts payable"
sign["B2"].number_format = "dd mmm yyyy"
for ws_ in wb.worksheets:
    for col in ws_.columns:
        ws_.column_dimensions[col[0].column_letter].width = max(12, min(40, max(len(str(c.value or "")) for c in col) + 2))
wb.save(path)
print(wb.sheetnames)

The total on the proposal sheet is a SUMIFS formula over the rows marked in the run, so if the approver flips Evergreen's In run cell to TRUE, the total updates and can be compared with the cash limit beneath it. Suppliers outside the limit are shaded. The Not paid sheet lists every excluded or deferred line with its reason — the first thing an auditor or a chasing supplier will ask about. The approval sheet records who prepared and approved the run; protecting it and the proposal against later edits is covered in Lock Cells and Protect a Sheet with openpyxl. Once approved, the payment file for the bank is generated from the same proposal, never retyped.

Common pitfalls

• Selecting by today's date. Use the next run date, or invoices due in between are paid late.
• Missing discounts. Compute each invoice's discount deadline and compare it with the next run.
• Forgetting credit notes. Net them against the supplier's invoices.
• Paying disputed invoices. Respect the on-hold flag and show the exclusions.
• Dropping lines silently at the cash limit. Flag them for the approver.

Conclusion

A payment run proposal selects every invoice that would be late or lose its discount if left to the next run, excludes held invoices and suppliers without bank details with the reason recorded, nets credit notes per supplier and fits the result to the cash available in due-date order. Writing proposal, lines, exclusions and an approval sheet into one workbook — with formula totals the approver can adjust — gives finance a run they can check and sign off in minutes.

Frequently asked questions

Which invoices should go into a payment run? Invoices due before the next run date, invoices whose early-payment discount expires before the next run, and the supplier's credit notes, excluding held invoices and suppliers without verified bank details.

How do I calculate an early-payment discount deadline in pandas? Parse terms such as 2/10 net 30 into a percentage and a number of days, and add the days to each invoice date.

How should credit notes be handled in a payment run? Include them as negative lines and total per supplier, so the payment is reduced by the credit.

What if the payment run exceeds the cash available? Sort suppliers by priority, usually earliest due date, take the cumulative total and flag those beyond the limit for the approver to decide.

Related

• Up one level: Building Finance and Operations Reports — finance reports from Python.
• Build an Accounts Receivable Aging Report — the customer side of the ledger.
• Build a Cash Flow Forecast Report — planning the cash the run uses.
• Build a Credit Control Dunning Letter Run — chasing what customers owe.