Paste your open vendor invoices into ChatGPT and get back a full AP aging dashboard — vendor-level buckets, DPO, early-payment discount flags, and a payment priority score, all formula-driven.
ROLE You are an AP analyst building a vendor payment aging dashboard in Excel. OBJECTIVE Take my open vendor invoice register and produce an aging report with invoice-level detail, vendor-level summaries, early-payment discount flags, DPO calculation, and payment priority scoring. DATA Report date: [e.g. 11/07/2025] Date format: DD/MM/YYYY COGS for the period: $[amount] over [number] days Open vendor invoices (columns: Vendor, Invoice #, Due Date, Amount Outstanding, Terms): [Paste your open invoices here — one row per invoice, no paid invoices] AGING RULES - Current: Due Date is on or after Report Date (not yet due) - 1–30 days: 1 to 30 days past Due Date - 31–60 days: 31 to 60 days past Due Date - 61–90 days: 61 to 90 days past Due Date - 90+ days: more than 90 days past Due Date DISCOUNT LOGIC - If terms include an early-payment discount (e.g. 2/10 Net 30) and the discount window is still open, mark "Disc Available" and calculate the dollar savings. If the window has passed, mark "Disc Expired." PRIORITY SCORING - High: 90+ days overdue OR amount > $30,000 and 30+ days overdue OR marked Critical Vendor - Medium: 31–60 days overdue, 61–90 days overdue (unless already High or Critical Vendor), or 1–30 days and amount > $20,000 - Low: Current or 1–30 days with amount ≤ $20,000 CONSTRAINTS - Calculate Days Overdue as Report Date minus Due Date. Negative = Current. - Do NOT age from the invoice date — age from the due date. - Do NOT invent invoices, vendors, or amounts. OUTPUT FORMAT 1. INVOICE DETAIL: Vendor | Invoice # | Due Date | Amount | Days Overdue | Bucket | Discount Status | Priority. Sorted by priority, then age. 2. VENDOR SUMMARY: Vendor | Total AP | Current | 1–30 | 31–60 | 61–90 | 90+. 3. DPO: Total AP ÷ (COGS ÷ Days). Show the calculation. 4. AGING SUMMARY: Total AP by bucket. Total overdue vs current. 5. DISCOUNT OPPORTUNITY: Invoices with open discount windows and total savings. Format as tab-separated tables ready to paste into Excel.
Replace all bracketed sections with your real data, then paste the prompt into ChatGPT.
I want to find every open early-payment discount opportunity in my AP right now, so I can decide whether to pay early. Invoice date: [today's date] Open vendor invoices with discount terms (columns: Vendor, Invoice #, Invoice Date, Amount, Terms e.g. "2/10 Net 30"): [Paste your invoices with discount terms here] Available cash for early payment this week: $[amount] For each invoice, calculate whether the discount window is still open, the dollar value of the discount if paid now, and rank the opportunities by savings-to-cash-used ratio so I can decide which to take given my available cash.
Missed discount windows are one of the most common — and most invisible — costs in AP management.
My AP priority scoring treats all overdue invoices the same regardless of how critical the vendor is. I want to weight critical vendors higher. Open vendor invoices (columns: Vendor, Invoice #, Due Date, Amount, Days Overdue, Critical Vendor [Y/N]): [Paste your invoices here, with a Y/N column for whether each vendor is sole-source, a key logistics partner, or otherwise operationally critical] Rebuild the priority score so that any invoice marked Critical Vendor receives High priority regardless of age or amount, unless it is already Current. Explain the new priority for each invoice in one short phrase.
A 15-day-late invoice to a sole-source supplier can matter more than a 60-day-late invoice to a replaceable vendor.
| Vendor | Invoice # | Due Date | Amount | Terms | |
|---|---|---|---|---|---|
| Apex Logistics | APX-1041 | 15/05/2026 | $67,500 | Net 30 | |
| Print Co | PC-0882 | 10/06/2026 | $3,200 | 2/10 Net 30 | |
| Cloud Infra Ltd | CIL-2201 | 01/07/2026 | $12,400 | Net 60 | |
| Office Depot | OD-0044 | 20/07/2026 | $840 | Net 30 | |
| Apex Logistics | APX-1055 | 28/07/2026 | $31,200 | Net 30 |
| Vendor | Invoice # | Amount | Days Over | Bucket | Priority | |
|---|---|---|---|---|---|---|
| Apex Logistics | APX-1041 | $67,500 | 92 days | 90+ | 🔴 High | |
| Apex Logistics | APX-1055 | $31,200 | 18 days | 1–30 | 🟡 Medium | |
| Print Co | PC-0882 | $3,200 | 66 days | 61–90 | 🟡 Medium | |
| Cloud Infra Ltd | CIL-2201 | $12,400 | 45 days | 31–60 | 🟡 Medium | |
| Office Depot | OD-0044 | $840 | 26 days | 1–30 | 🟢 Low |
Total AP: $115,140 90+ days: $67,500 ← pay immediately 61–90 days: $3,200 31–60 days: $12,400 1–30 days: $32,040 DPO = $115,140 ÷ ($820,000 ÷ 180) = 25.3 days ✓
| Vendor | Amount | Terms | Disc Window | Disc Value | Status |
|---|---|---|---|---|---|
| Print Co | $3,200 | 2/10 Net 30 | Expired 21/06 | −$64 | Gone ✗ |
| Apex Supply | $28,000 | 1/10 Net 60 | Expired 11/08 | −$280 | Gone ✗ |
| Packaging Ltd | $14,500 | 2/10 Net 30 | Expires 15/08 | $290 | 0 days left ⚠ |
| Nord Freight | $9,200 | 1.5/10 Net 30 | Expires 20/08 | $138 | 5 days ✓ |
| Vendor | Amount | Disc Rate | Disc £ | Days Left | Cash Used | Rank |
|---|---|---|---|---|---|---|
| Apex Supply | $28,000 | 1% | $280 | Expired | — | Missed ✗ |
| Packaging Ltd | $14,500 | 2% | $290 | 0 days | $14,210 | 1st (2% rate) |
| Nord Freight | $9,200 | 1.5% | $138 | 5 days | $9,062 | 2nd |
| Print Co | $3,200 | 2% | $64 | Expired | — | Missed ✗ |
Available cash: $40,000 Pay Packaging Ltd: $14,210 saves $290 (2.0% eff. rate) Pay Nord Freight: $9,062 saves $138 (1.5% eff. rate) Apex Supply: window expired 11/08 — $280 missed ✗ Total valid savings: $428 Cash required: $23,272 (within $40k limit) Priority: Packaging first (best rate), then Nord. ✓
| Vendor | Amount | Days Over | Critical? | Priority |
|---|---|---|---|---|
| Office Depot | $840 | 62 days | N | 🔴 High |
| Apex Logistics | $31,200 | 45 days | Y ← sole source | 🟡 Medium ✗ |
| Cloud Infra Ltd | $12,400 | 45 days | Y ← SLA risk | 🟡 Medium ✗ |
| Print Co | $3,200 | 22 days | N | 🟢 Low |
| Nord Freight | $9,200 | 5 days | Y ← sole carrier | 🟢 Low ✗ |
| Vendor | Amount | Days Overdue | Critical? | New Priority | Reason |
|---|---|---|---|---|---|
| Apex Logistics | $31,200 | 45 days | Y | 🔴 High | Critical + 31–60 days |
| Cloud Infra Ltd | $12,400 | 45 days | Y | 🔴 High | Critical + SLA risk |
| Nord Freight | $9,200 | 5 days | Y | 🔴 High | Critical vendor override |
| Office Depot | $840 | 62 days | N | 🟡 Medium | 61–90 days, not critical |
| Print Co | $3,200 | 22 days | N | 🟢 Low | 1–30 days, ≤$20k |
IF Critical=Y AND NOT Current → High (regardless of age) IF Critical=N → apply standard age/amount rules 3 High (all critical vendors) vs 1 High before. Office Depot $840 correctly drops to Medium. ✓
Paid invoices inflate your AP total and create phantom overdue items. Filter before pasting. If partial payments exist, include only the remaining balance.
2/10 Net 30 means 2% discount if paid within 10 days of invoice, otherwise full amount due in 30. Not every vendor offers discounts — leave the field blank for those.
ChatGPT calculates days overdue as Report Date − Due Date. Negative = not yet due = Current. State it clearly: Report date: 11/07/2025.
DPO = Total AP ÷ (COGS ÷ Days in period). Without COGS, ChatGPT can't calculate DPO. Include the figure and the period length: COGS for Q2 2025: $640,000 over 91 days.
Add a "Critical Vendor" column (Y/N) so ChatGPT can boost priority for sole-source suppliers, key logistics partners, or regulated service providers — regardless of the age of the invoice.