NAV break & reconciliation-exception triage

You are helping a fund accountant reconcile the portfolio holdings of [fund name or code] as at [valuation date]. A human reviewer checks everything you produce. You do not sign off the reconciliation and you do not mark any difference as resolved.

I attach:
- Source A: [administrator's investment schedule or general ledger extract]
- Source B: [manager's portfolio monitoring file or valuation report]
- Original documents: [capital call and distribution notices, investment agreements, valuation memos and the valuation report for the period]
- Prior period: [last period's signed-off reconciliation, with each holding's closing value and its as-at date]

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 holding; 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 value column, say how the figure most likely got from the original document (capital call notice, distribution notice, investment agreement, valuation memo, FX rate) into the file: copied, keyed by hand, converted at an FX rate, summed from lots, or revalued. Note which source applies FX, at what rate and date, and where fees, accrued interest or unfunded commitments sit in one source but not the other. These are the points where differences are usually created.
Label each statement "stated in the file" or "my inference".

STEP 3. MATCH AND RECONCILE
3a. Keys. List every identifier in each file: ISIN, LEI, administrator investment code, manager internal code, company registration number. Say which identifiers appear in both sources and how complete each is (rows with it filled, out of total rows). Use a shared identifier as the key wherever one exists.
3b. Names. For holdings with no shared identifier, describe how each source names portfolio companies: legal or trading name; holding company or operating company; legal-form suffixes (Ltd, Limited, LLC, S.a r.l., SAS, GmbH, B.V.); abbreviations; deal or project names; punctuation, accents and case.
3c. Standardise. Write numbered standardising rules (for example: upper case; remove punctuation and accents; remove legal-form suffixes; expand the listed abbreviations). Apply them in a new column in each source. Never overwrite the original name.
3d. Map. Build a mapping table with one row per data row of each source: the source (A or B), the source row reference, the name and code as given, the standardised name, the holding key (the shared code, or the standardised name where no code is shared), match method ("shared code", "standardised name" or "suggested, needs review") and the reason for any suggested match. Several rows of one source (lots, tranches or general ledger lines) carry the same holding key. A suggested match is never confirmed. List every holding key found in only one source.

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: fund, as-at date, base currency, the reconciliation tolerance between sources (for example 1.00 in base currency), FX rates with their source and date, the period start date (last period's as-at date), and the list of movement categories for the roll-forward (calls and purchases, distributions and sales, income, fees, FX, valuation change); 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 mapping table from step 3d, as an Excel table, one row per data row of each _Source tab with its holding key, its value as a formula from that _Source row (with its currency, and converted to the base currency at the FX rate on the Inputs tab where it differs), and for each suggested match who confirmed it and when.
- RollForward: one row per movement from the sources, with its holding key from the Map tab, date, amount, source document and page, cell or row reference, each in exactly one category from the Inputs tab, with per-holding totals by category as formulas. For each holding key, a summary row: last period's closing value from the prior period _Source tab, looked up by holding key, the total of each category, the valuation change taken from the valuation report with its page, this period's Source A value from the Rec tab, and the unexplained difference, all as formulas.
- Rec: one row per holding key on the Map tab. Source A value and Source B value are each the sum of that source's Map rows for the key, by SUMIFS through the Map tab; difference, difference as a percentage and status are formulas. Status reads "Agrees" inside the reconciliation tolerance, "One source only" where the key has rows in only one source, otherwise "Difference". For each source value: the document figure as read, the workbook figure, and a TRUE/FALSE tie between them as a formula. Each row carries its source document and the page, cell or row reference for each figure.
- Exceptions: a formula-driven list of every "Difference", "One source only", "Not found" and "suggested, needs review" row, with the likely cause from step 2 and the document that would confirm it. It also lists every failed control and open query. Each row carries its type (for example "Rec difference", "One source only", "Not found", "Suggested match", "Roll-forward difference", "Failed control", "Untraced or disagrees", "Query" or "Timing difference"), a short name for the item, its owner, status ("open" or "closed"), expected clearing date and the approver's sign-off (name and date).
- Checks: the controls in step 5.
- KPIs: the measures in step 6.
- _Log: the audit trail in step 6.
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 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: the count of data rows on each _Source tab (leaving out the header, subtotal and total rows identified in step 1) equals the count of that source's rows on the Map tab, and every Map row carries a holding key. Nothing is dropped or counted twice. Target: 0 unexplained rows.
b. Totals tie: the total of each value column on the Rec tab equals the total of the data rows in the same column on its _Source tab, every "Difference" row on the Rec tab appears on the Exceptions tab with type "Rec difference" and every "One source only" row with type "One source only": the count of each type equals the count of Rec rows with that status. 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: on the Rec tab, for every holding, record the document, page and figure that supports each source value. Show the document figure, the workbook figure and a TRUE/FALSE tie. 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 capital call notice whose amount differs from the administrator's booking, 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. Roll-forward: on the RollForward tab, classify every movement in the sources into exactly one category from the list on the Inputs tab (calls and purchases, distributions and sales, income, fees, FX, valuation change), so that no movement is counted twice, for example income already inside a distribution or FX already inside a valuation. For every holding key, last period's closing value plus the categorised movements equals this period's value, with the valuation change taken from the valuation report, never as a balancing figure. Target: 0 unexplained.
h. One-source holdings: holding keys found only in Source A and only in Source B both have a Rec row reading "One source only" and an Exceptions row. Target: none dropped.
i. Open exceptions: count the Exceptions rows that are not closed, leaving out only named timing differences with an expected clearing date. Target: 0.
j. 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 three counts: holdings that agree, holdings with a difference, and holdings found in one source only, the result of every check, and a list of anything you were unsure about.