Task recipe · Beginner · 15 min
Receivables ageing report in Excel
এক্সেলে দেনাদারের এজিং রিপোর্ট
Get AI to write the Excel formulas for days overdue, ageing buckets and bucket totals, then test them on invoices where you already know the right answer.
The problem
Management wants to see how much customers owe and for how long: not yet due, 1 to 30 days, 31 to 60, 61 to 90 and more than 90 days overdue.
What you need
- A list of open invoices: customer code, invoice date, due date, amount outstanding
- The report date
- Your Excel version
Steps
- Lay out the invoice list with one row per invoice and the report date in a separate cell.
- Paste the prompt, changing the column letters to match your sheet.
- Enter the formulas and fill them down.
- Test the boundary days and reconcile the totals.
The prompt
Copy it, then replace the example figures and [brackets] with your own.
Excel 365. Sheet "Open invoices": A = customer code, B = invoice date, C = due date, D = amount outstanding (Tk). Data is in rows 2 to 500. The report date is in cell H1. Write formulas for: 1. E2: days overdue at the report date (0 if not yet due). 2. F2: ageing bucket: "Not due", "1-30", "31-60", "61-90" or "90+". 3. A summary in J2:K6 with the total outstanding for each bucket. Explain each formula in one or two lines and tell me how to test it.
What the AI should give you
1. Days overdue (E2, fill down) =MAX(0, $H$1 - C2) Report date minus due date; MAX(0, ...) turns "not yet due" into 0. 2. Ageing bucket (F2, fill down) =IFS(E2=0,"Not due", E2<=30,"1-30", E2<=60,"31-60", E2<=90,"61-90", TRUE,"90+") IFS checks each condition in order and returns the first match. 3. Bucket totals (type the five bucket names in J2:J6, then K2 and fill down) =SUMIFS($D$2:$D$500, $F$2:$F$500, J2) Test: give one invoice a due date exactly 30 days before H1 and another 31 days before. They should land in "1-30" and "31-60". The total of K2:K6 must equal =SUM(D2:D500).
How to check it
- Boundary days: 30 days overdue must show “1-30” and 31 days must show “31-60”.
- The bucket totals must add up to total receivables, and to the receivables control account in the ledger.
- Invoices due on the report date show 0 days (Not due). Decide whether that matches your policy.
- Bucket names in J2:J6 must match the formula exactly. “1–30” with a long dash will make SUMIFS return 0.
Common mistakes
- Measuring age from the invoice date when management asked for days past due date, or the reverse. Agree which before building.
- Typing the report date as text instead of a real date, which breaks every calculation.
Learn more
More recipes
Bank reconciliation with AI
এআই দিয়ে ব্যাংক রিকনসিলিয়েশন
Match a cash book against a bank statement, list unmatched items with likely reasons, and draft a bank reconciliation statement you can check line by line.
ChatGPTClaude
IFRS 16 lease liability schedule
আইএফআরএস ১৬ লিজ দায়ের সূচি
Calculate the initial lease liability and right-of-use asset, build the amortisation table and journal entries, then prove the numbers with Excel’s PV function.
ClaudeChatGPTCopilot in Excel
Salary income tax computation (Bangladesh)
বেতনভোগীর আয়কর হিসাব
Lay out a salaried person’s Bangladesh income tax computation step by step, using the rates and limits you paste from the current Finance Act, never the AI’s memory.
ChatGPTClaudeGoogle Gemini
Sample figures are for learning only. Check every AI answer before you rely on it.