---
name: acente-alacak-yaslandirma
description: "Use when an agency receivables aging analysis is requested from invoice, due date, collection/offset ledger and report date. Produce a formula-based Excel file and a Word summary."
---

# Agency receivables aging

Which agency's balance, in which due-date band, needs collection follow-up today?

## Input and scope

Invoice, due date, collection/offset ledger and report date.

Use the attached Excel/CSV files as the source. Read each one without modifying it. Identify the sheets, report scope, periods, units and required fields. Map synonymous column headers against the sample. If a missing field affects only a specific metric, still produce the other metrics. If a critical definition can be read in more than one way, ask one focused question. Trace subtotals and duplicates back to the source row. Remove fields containing personal data from the outputs. Use the sample only when the user wants to try it with the sample data.

## Analysis

1. As of the report date, deduplicate each invoice's collections, offsets, refunds and credit notes. Do not include collections dated after the report date in an earlier report.

2. Do not substitute the invoice date for the due date. Calculate the open balance per currency. Show negative balances separately from the receivables total. If the due date is missing, mark the age as unknown.

3. Calculate the overdue bands from the day difference. Do not count invoices due today or not yet due as overdue. In the Word report, explain the follow-up order by amount first, then by days overdue.

## Delivery and checks

On the first sheet of Analiz.xlsx, show the rows that answer the question and the calculated indicators. Keep the sources on a separate sheet with anonymised fields. Write Excel formulas for derived measures. On the Checks sheet, add the date range, column mapping, scope, missing/unmatched records and the reconciliation result. Create the Word file named Yonetim-Ozeti.docx. On one page, write the period, the main result, at most three evidence-backed findings and the follow-up the responsible team will check. Reopen both files; independently check the numbers, the denominators of ratios and the source totals. Do not present unverifiable causes as findings. Deliver both files as downloads.

Visible fields in the Excel analysis: Invoice, Agency, Open balance (TL), Days past due, Aging band.

## Try it with the sample

Use the file assets/ornek-girdi.xlsx. The expected Excel and Word examples are in the ornekler/ folder. Sample-specific check: The overdue open balance is 50,000 TL. 30,000 TL of it is invoice F101, which has been open for 41 days.
