Stop manually counting days on the calendar to figure out which clients owe you money. This guide demonstrates how to use Claude AI to build an automated Accounts Receivable (AR) tracker in Excel. You will learn to generate dynamic formulas that automatically calculate days outstanding and assign past-due invoices into priority collection buckets, ensuring your finance team focuses on securing cash flow rather than updating spreadsheets.
The Final AR Output
By the end of this mini-course, your raw invoice data will be transformed into a dynamic tracker that instantly flags overdue accounts. It will look like this:
Managing AR is the lifeblood of corporate liquidity. As detailed in our AI for Finance hub, keeping a real-time view of outstanding collections allows you to proactively mitigate bad debt.
Preparing Invoice Data
Before prompting Claude, export your open receivables from your billing software. Ensure each row represents a single unpaid invoice. For the formula to work accurately, you need an exact Due Date. If your export only provides an "Invoice Date" and "Terms" (e.g., Net 30), Claude can help you calculate the Due Date first.
Once you have standardized dates, the process becomes entirely automated. You can even link this clean data into a larger visual representation, as shown in our cash flow dashboard course.
The Exact Claude Prompt for AR Tracking
Copy this prompt into Claude. It contains instructions to handle empty dates gracefully and to use the dynamic TODAY() function so your spreadsheet remains accurate every day you open it.
Context: Taking Action on the Data
Once the formula assigns every invoice a status, you can easily use Excel's filter functionality to isolate the "60+ Days Late" bucket. This allows your collections team to prioritize their morning calls based on age and amount outstanding.
Frequently Asked Questions
How does Claude handle partial invoice payments in Excel?
Claude does not automatically know about partial payments unless your data includes an "Amount Paid" column. You must instruct Claude to calculate the "Remaining Balance" (Invoice Amount - Amount Paid) and only flag the invoice as overdue if the Remaining Balance is greater than zero.
Can I use Claude to calculate Days Sales Outstanding (DSO)?
Yes. Once your basic AR tracker is functioning, you can prompt Claude to write the formula for DSO using your total accounts receivable and total credit sales over a specific period.
Will the AR tracker update automatically tomorrow?
Yes, provided Claude uses the TODAY() function in the formula. This ensures the "Days Overdue" calculation recalculates dynamically every time you open the Excel file.