Annex IV & regulatory report assembly

You are helping a regulatory reporting analyst tie out the draft Annex IV return for [AIF] for [reporting period]. A human reviewer checks everything you produce. You do not file and you do not change the draft return.

I attach:
- Draft return: [the draft filing or its field export]
- Source books: [NAV pack, positions, exposures, investor and liquidity data for the period]
- Prior return: [last period's filing]

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 reporting field; 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 field, say how the reported figure was built from the books: which report, which filter, what aggregation, what FX rate, and which mapping table assigns positions to asset types, regions or counterparties. Flag fields that use a different as-at date or a different NAV basis.
Label each statement "stated in the file" or "my inference".

STEP 3. MATCH AND RECONCILE
3a. Keys. List the field codes in the return and the identifiers in the books (ISIN, LEI, internal codes). Say how complete each is.
3b. Names. For counterparties, markets and instruments with no shared code, describe how the books name them and how the return names them.
3c. Standardise. Write numbered rules and apply them in new columns.
3d. Map. Build a table from each book line to its return field, with match method and reason. A suggested mapping 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: AIF, period, base currency, FX rates with source, movement threshold for explanation; 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: book lines mapped to return fields, one row per book line and each field it feeds (one position can feed instrument, geography, currency, concentration and exposure fields), each book line keyed by its identifier (for a position, the position identifier used on the AuM tab), with match method ("shared ID", "standardised name" or "suggested, needs review") and reason. A book line that feeds no field reads "not reported", with its reason. For each suggested match, the person who confirmed it and the date.
- TieOut: one row per field. Book value in its source currency, the FX rate from the Inputs tab, the converted value in base currency (summed through the Map tab), the figure as the book report shows it, reported value, difference and status ("Agrees" or "Check") are formulas. Each row carries its source document and the page, cell or row reference for each figure.
- AuM: one row per position on the positions _Source tab: position identifier, asset type, currency, value under the AIF's valuation rules, the FX rate from the Inputs tab and the converted value in base currency; for each derivative, a derivative flag, its equivalent position in the underlying asset (from the exposures _Source tab) and the absolute value of that position. The AuM contribution of each row and the AuM total are formulas. Each row carries its source document and the page, cell or row reference for each figure.
- Movement: one row per field code on the TieOut tab: field code, reported value this period (from the TieOut tab), reported value last period (from the prior return _Source tab, matched by field code), change and an above-threshold flag against the threshold on the Inputs tab, all as formulas, and a short explanation for every flagged row.
- Exceptions: one row per failed control, untraced or disagreeing figure, unconfirmed match and open query, with its type ("failed control", "untraced figure", "disagreeing figure", "unconfirmed match", "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.
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: every field in the draft return has a TieOut row, and every book line is mapped to a field or listed as "not reported" with a reason. Target: 0 unexplained rows.
b. Positions tie: every position identifier on the AuM tab appears once in the AuM total and has at least one row on the Map tab, and the position values on the AuM tab sum to the positions _Source total. 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 TieOut tab, for every field, cite the book report and page the figure comes from, and show the report figure beside the workbook figure. 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 positions report that disagrees with the NAV pack, 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. AuM: the workbook recomputes AuM under Article 2 of Regulation 231/2013 (all assets, including those acquired through leverage, valued under the AIF's valuation rules, with each derivative converted to its equivalent position in the underlying asset at the absolute value of that position) and ties it to the AuM on the return. AuM is not the NAV. Leverage exposure under the gross method (Article 7) and the commitment method is a separate figure and is not compared with AuM. Target: a difference of 0.00.
h. Movements: every field code on the TieOut tab has a Movement row, and every Movement row flagged as moving by more than the threshold on the Inputs tab has an explanation.
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 fields that agree, fields with a difference, and movements above the threshold, the result of every check, and a list of anything you were unsure about.