Capital-call & distribution notice processing

You are helping a fund operations analyst process this period's capital call and distribution notices for [fund or list of underlying funds]. A human reviewer checks everything you produce. You do not instruct payments and you do not post to the ledger.

I attach:
- Notices: [the batch of call and distribution notices, PDF or email]
- Commitment register: [commitments, paid-in to date, recallable distributions to date and unfunded per fund and investor, and the known contact on file for each fund]
- Prior notices: [the last call or distribution notice from each fund, for its bank details]
- Payments: [the payment instructions prepared for these notices and any paid earlier in the period, with notice reference, investor, currency, amount and value date]
- Terms: [the LPA or fund summary pages that set call and fee mechanics]

Work through the six steps below. Show the result of each step before you move on.

STEP 1. MAP THE SOURCES
For each file, record: who produced it; the as-at date it states (not the date it was sent); the currency; what one row or section represents; the fields that identify a fund, an investor and a notice; the value fields and how each is labelled; and any subtotals, hidden rows, merged cells or footnotes that change how it should be read. Where the files differ in as-at date, period or currency, list each difference and say how you will handle it: convert at an FX rate from the Inputs tab with its source, or compare like periods only. Stop and ask me only if a difference means two figures cannot validly be compared.

STEP 2. TRACE THE PREPARATION
For each notice, say how each amount was built: percentage of commitment, management fee, organisational or other expenses, equalisation interest, recallable distribution, and the currency and FX rate used. Note where the notice gives a total but not the breakdown. Compare how the commitment register stores paid-in and unfunded amounts with how the notice states them.
Label each statement "stated in the file" or "my inference".

STEP 3. MATCH AND RECONCILE
3a. Keys. List the identifiers on the notices and in the register: fund code, investor or account number, notice reference. Say which appear in both and how complete each is.
3b. Names. Where no shared code exists, describe how the notices name the fund (fund number, vehicle name, parallel or feeder vehicle, currency class) and how the register names it.
3c. Standardise. Write numbered rules for fund and vehicle names and apply them in new columns, keeping the original text.
3d. Map. Build a mapping table: notice fund name, register fund name and code, match method ("shared code", "standardised name" or "suggested, needs review"), reason. A suggested match is never confirmed.

STEP 4. BUILD THE LINKED WORKBOOK
Build an .xlsx file with these tabs, in this order:
- Index: a link to each tab, one line on what it holds, the as-at date, and the steps to refresh the workbook next period.
- Inputs: period, base currency, FX rates with source and date, the rounding tolerance per investor, the allocation basis the LPA sets for calls (with any excused or excluded investors), the total participating commitments per fund as the notice or the LPA states it, the funds with a call due this period with the source of each, and for each line type its effect on unfunded commitment under the LPA: "decrease", "increase" or "no impact"; plus the as-at date of each source file, with any differences between them and how each is handled; the tolerance for agreement with documents (0.00 wherever exact agreement is required). Each value carries its source beside it, and every formula that needs one points here.
- _Source tabs: each file exactly as received, one tab per file. Never edit these tabs.
- Map: the fund mapping table from step 3d. For each suggested match, the person who confirmed it and the date.
- Notices: one row per notice line, as read: fund, investor, notice reference, line type, amount, currency, due date, the fund-level total call where the notice states it, and bank details (beneficiary, bank, account and payment reference), with the source document and the page reference for each figure. Beside them, the bank details on the last notice from the same fund (from the prior notices _Source tab) and a bank-change flag are formulas.
- Schedule: one row per notice: fund, investor, notice reference and currency. Commitment, unfunded before and paid-in before (from the register), recallable amounts to date, call percentage, capital contribution (commitment times percentage), each other line on the notice (fees, expenses, equalisation, recallable amounts) as read, expected total, amount on the notice, difference, the allocated amount (the fund-level total call on the basis set on the Inputs tab) and its difference, unfunded after the call, paid-in after the call, due date, a possible-duplicate flag and status are formulas. Unfunded after the notice is a signed formula: unfunded before, less the lines whose effect on the Inputs tab is "decrease", plus the lines marked "increase" (for example a recallable distribution), with "no impact" lines ignored. Status reads "Agrees" or "Check".
- Payments: one row per payment instruction: notice reference, investor, currency, amount, value date and account paid to, with the source document and the row reference for each figure, and a duplicate flag as a formula.
- Exceptions: a formula-driven list of every "Check", missing notice, unmapped fund, possible duplicate and changed bank detail. It also lists every failed control and open query. Each row carries its type ("Check", "no notice received", "unmapped fund", "possible duplicate", "changed bank detail", "unconfirmed match", "failed control", "open query" or "timing difference"), a description, its owner, status ("open" or "closed"), expected clearing date and the approver's sign-off.
- Checks: the controls in step 5.
- KPIs: the measures in step 6.
- _Log: the audit trail in step 6, plus, for each bank detail change, the person who confirmed it, the contact called and the date and time of the call.
Every derived number is a formula that leads back to a _Source tab or the Inputs tab. Nothing is typed in as a value. Use Excel tables and XLOOKUP or INDEX/MATCH. Wrap lookups in IFERROR and show "Not found", never zero. Dates as yyyy-mmm-dd, negatives in brackets, units in every value header, header rows frozen, no decorative formatting.

STEP 5. CONTROLS (EVERY ONE MUST PASS BEFORE SIGN-OFF)
On the Checks tab, write each control as a formula that returns TRUE or FALSE, with the count or difference beside it. A FALSE is not a score to improve: it blocks sign-off until it is resolved, by correcting the figure with a note or by getting the source corrected. An explanation alone does not clear it. The only item that may stay open at sign-off is a timing difference with an expected clearing date: it is listed by name on the Exceptions tab as a reconciling item and re-checked next period. A document tie-out failure or a changed bank detail is never a timing difference and can never be carried. Show every FALSE at the top of the Index tab under "Checks failing".
a. Complete: every notice in the batch appears on the Notices tab, and every fund in the register with a call due this period either has a notice or appears on Exceptions as "no notice received". Target: 0 unexplained rows.
b. Totals tie: the Schedule totals equal the Notices totals by currency and line type, and for every fund, the change in unfunded commitment equals the signed sum of the lines by their effect on the Inputs tab. Target: a difference of 0.00.
c. Formula-linked: every calculated cell is a formula (test with ISFORMULA), and there are no #N/A or #REF errors. Target: 100%.
d. Tied to documents: for every notice, the amount, due date and bank details on the Notices tab match the PDF, with the page cited. Bank details that differ from the last notice from the same fund go to Exceptions, whatever the amount. Every figure must be traced (it cites its document and page) and must agree (it equals the figure in that document), within the tolerance on the Inputs tab and no wider. Any figure that is untraced or disagrees goes to the Exceptions tab at once. If the document itself looks wrong, for example a notice that disagrees with the LPA, raise it as a query; never change the workbook to make it agree. Target: 100%.
e. Matches confirmed: count the rows matched by shared ID, by standardised name and by suggestion. Target: 0 suggested matches left unconfirmed at sign-off.
f. FX and dates: every FX rate and every as-at date difference has its source on the Inputs tab. Target: 100%.
g. Allocation: where a notice states the fund-level total call, each investor's amount equals that total allocated on the basis set on the Inputs tab from the LPA (usually commitment share of the participating investors, after any excused or excluded investors), within the rounding tolerance.
h. Commitment limits: unfunded never goes below zero, and paid-in never exceeds commitment plus recallable amounts.
i. Duplicates: no two payments share notice reference, investor, currency and amount. Two notices with the same fund, amount and due date are flagged for a person to check.
j. Bank details: any change from the last notice from the same fund is flagged. HUMAN STEP: before payment, a person confirms the change by phone with a known contact taken from the register, never from the notice, and records who and when on the _Log.
k. Open exceptions: count the Exceptions rows that are not closed, leaving out only named timing differences with an expected clearing date. Target: 0.
l. Four-eyes: the _Log names the preparer and the approver, who must be different people, and the approver signs off every Exceptions line.

STEP 6. REUSABLE, AUDITABLE AND MEASURED
- Reusable: next period, a person replaces the _Source tabs and updates the Inputs tab, and every other tab recalculates. Keep the Map tab so confirmed matches carry forward.
- Auditable: on the _Log tab, list each source file (name, producer, as-at date, date received, row count, control total), every rule and match you applied with the reason, every assumption, and who prepared and approved. Never overwrite source data. A correction goes in a separate column with a note.
- Measured: add a KPIs tab that the team fills in each period: back-test recall (run on a past period with known answers; share of known differences found; target 100%, and do not adopt the workbook below that); items agreed first time with no manual fix; items flagged that turned out fine; corrections made by the reviewer before sign-off; corrections found after sign-off (target 0); manual edits outside the Inputs and _Source tabs at refresh (target 0); and cycle time in hours from receiving the files to sign-off, against the manual baseline.

If your tool cannot create an .xlsx file, give me each tab as a table plus the exact formula for the first row of every calculated column.

At the end, give me the number of notices processed, the number that agree to the commitment record, and the number on Exceptions, the result of every check, and a list of anything you were unsure about.