Build an Accounts Payable Aging Dashboard in Excel with Claude AI
Progress1 of 4
1
How the dashboard works
2
What Claude needs from you
3
Prompt, FAQ & related
4
Quick quiz
Section 01
AR aging tells you who owes you. AP aging tells you who you might be about to upset.
Paying vendors late costs more than the late fee. It costs you priority allocation when supply is tight, negotiating leverage at renewal, and — eventually — the vendor relationship itself. The problem isn't intent; it's visibility. When 200 invoices sit in a shared inbox, the ones that are 5 days from due and the ones that are 60 days past due look identical.
Claude takes your open vendor invoices, calculates how many days remain until due (or how many days overdue), assigns each invoice to an aging bucket, scores payment priority, and produces a vendor-level summary with DPO. Paste new data weekly and the dashboard updates.
The Pipeline: Invoices → Aging → Payment Strategy
Vendor invoices
Days to/past due
Aging & discounts
Payment priority
Three Payment Strategies — One Dashboard
Pay Early
Discount available (e.g. 2/10)
Saves 2% on invoice value
Worth it if cash is available
Pay On Time
Within terms, no discount
Preserves relationship
Standard cash management
Past Due
Late fees, holds, credit risk
Vendor may stop shipping
Immediate action required
The Output: What Claude Produces
A
B
C
D
E
F
G
1
Vendor
Invoice
Due Date
Amount
Days Over
Bucket
Priority
2
Delta Supplies
VS-2041
10/04/2025
67,500
92
90+
High
3
Peak Logistics
VS-2198
25/05/2025
22,000
47
31–60
Medium
4
Apex Materials
VS-2287
28/06/2025
15,800
13
1–30
Low
5
TechServ Inc
VS-2310
18/07/2025
41,200
—
Current
2% disc
6
Meridian Corp
VS-2315
30/07/2025
28,000
—
Current
Low
7
Summary
8
Total AP
174,500
Current
69,200
Overdue
105,300
TechServ shows "2% disc" — a $41,200 invoice with 2/10 Net 30 terms. Paying within the discount window saves $824. Delta Supplies at 90+ days is the fire to put out first. Report date: 11/07/2025.
Key insight: AP aging isn't just about avoiding late payments — it's about optimising payments. The TechServ invoice saves $824 if paid early. The Delta invoice risks a supply disruption if paid late. The dashboard shows both opportunities in one view. DPO from this tracker feeds directly into your financial KPI dashboard.
What Claude Needs From You
Claude builds the aging from an open invoice register — one row per unpaid vendor invoice. The critical fields: vendor name, invoice number, due date, amount outstanding, and (optionally) discount terms. Without the due date, aging is impossible. Without discount terms, you miss savings opportunities.
Invoice Register Layout
A
B
C
D
E
1
Vendor
Invoice #
Due Date
Amount
Terms
2
Delta Supplies
VS-2041
10/04/2025
67,500
Net 30
3
TechServ Inc
VS-2310
18/07/2025
41,200
2/10 Net 30
4
Meridian Corp
VS-2315
30/07/2025
28,000
Net 45
01
Include only open invoices — exclude anything already paid
Paid invoices inflate your AP total and create phantom overdue items. Filter before pasting. If partial payments exist, include only the remaining balance. Paid items belong in your bank reconciliation, not the AP tracker.
02
Add discount terms where they exist
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. Claude only flags discounts when terms are present.
03
Specify the report date
Claude calculates days overdue as Report Date − Due Date. Negative = not yet due = Current. State it clearly: Report date: 11/07/2025.
04
Provide COGS for DPO calculation
DPO = Total AP ÷ (COGS ÷ Days in period). Without COGS, Claude can't calculate DPO. Include the figure and the period length: COGS for Q2 2025: $640,000 over 91 days.
Common mistake: Treating all overdue invoices equally. A $67,500 invoice 92 days late to your sole raw material supplier is a crisis. A $800 office supply invoice 35 days late is an admin task. The priority score in this dashboard weights both age and amount and vendor criticality.
Pro tip: Add a "Critical Vendor" flag to your input data (Y/N). Claude can use this to boost the priority score for vendors where a payment delay would halt operations — sole-source suppliers, key logistics partners, or regulated service providers. A 15-day-late invoice to a critical vendor may matter more than a 60-day-late invoice to a replaceable one.
The Claude Prompt for AP Aging
Prompt — paste into Claude
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 early-payment discount (e.g. 2/10 Net 30) and the discount window is still open based on the invoice date, mark "Disc Available" and calculate the dollar savings.
- If the discount window has passed, mark "Disc Expired."
- If no discount terms exist, leave blank.
PRIORITY SCORING
- High: 90+ days overdue OR amount > $30,000 and 30+ days overdue OR marked Critical Vendor
- Medium: 31–60 days overdue, 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 values = Current.
- Do NOT age from the invoice date — age from the due date.
- Do NOT invent invoices, vendors, or amounts.
- Do NOT mark Current invoices as overdue.
OUTPUT FORMAT
1. INVOICE DETAIL: Vendor | Invoice # | Due Date | Amount | Days Overdue | Bucket | Discount Status | Priority. Sorted by priority (High first), then days overdue descending.
2. VENDOR SUMMARY: Vendor | Total AP | Current | 1–30 | 31–60 | 61–90 | 90+. One row per vendor using SUMIFS.
3. DPO: Total AP ÷ (COGS ÷ Days). Show the calculation.
4. AGING SUMMARY: Total AP by bucket. Total overdue vs current.
5. DISCOUNT OPPORTUNITY: List of invoices with open discount windows and total potential savings.
Format as tab-separated tables ready to paste into Excel.
AP and AR Are Two Sides of the Same Coin
AR aging tells you how fast you collect. AP aging tells you how fast you pay. Together they define your cash conversion cycle — the gap between when you pay vendors and when customers pay you. A company that pays in 30 days but collects in 60 is funding 30 days of operations from its own cash. Track both sides: the accounts receivable tracking course covers the collection side.
The AP total and DPO feed your cash flow dashboard — overdue AP represents cash that will leave imminently, while Current AP is your near-term payment pipeline. For month-end close, AP review is typically one of the first tasks on the close checklist.
AP Mistakes That Cost Real Money
A
B
1
Mistake
What It Costs You
2
Missing early-payment discount windows
2% of invoice value — adds up to thousands annually
3
Paying small invoices first because they're easier
Large critical-vendor invoices age past due while you clear $200 items
4
No distinction between critical and non-critical vendors
Sole-source supplier stops shipping because their invoice was deprioritised
5
Including paid invoices in the aging report
Overstated AP balance leads to incorrect DPO and misleading cash projections
Frequently Asked Questions
What is an accounts payable aging report?
An AP aging report groups outstanding vendor invoices by how far past due they are. Standard buckets: Current, 1–30, 31–60, 61–90, and 90+ days. It helps decide which invoices to pay first and identifies overdue obligations that risk vendor relationships.
How is AP aging different from AR aging?
AR tracks what customers owe you. AP tracks what you owe vendors. The mechanics are similar but the priorities are opposite: AR asks who to chase; AP asks who to pay first.
Can Claude calculate DPO?
Yes. DPO = Total AP ÷ (COGS ÷ days in period). Provide COGS and the period length. Claude computes DPO and can track it over time.
How do I handle early payment discounts?
Include discount terms (e.g. 2/10 Net 30) in your data. Claude flags invoices where the discount window is still open and calculates the dollar savings.
Should I always pay the oldest invoices first?
Not necessarily. Priority depends on penalties, discounts, vendor criticality, and cash. A 15-day-late invoice to your sole supplier may matter more than a 60-day-late invoice to a replaceable one.