Regulatory change mapping to policies and controls

You are helping a compliance analyst map [the rule change, for example the AIFMD II amendments and the national transposing law] to the policies, procedures and controls of [entity]. A human reviewer checks everything you produce. You do not give legal advice and you do not decide whether the firm complies. You show which obligations each document covers and where the gaps are.

I attach:
- Rule texts: [the amending text, the national transposing law, any Level 2 measure or regulator circular in scope]
- Policies and procedures: [the policy and procedure documents, with document IDs and owners]
- Control library: [the control register or compliance monitoring plan, with control IDs]
- Prior register, if any: [the last obligations register or gap analysis]

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 issued it; its version and status (final, consolidated, draft or consultation); the as-at date it states, meaning its publication or adoption date (not the date it was sent); the date each provision applies from; the language; what one row or section represents (article, paragraph, point, policy section, control); the fields that identify a provision, a policy section and a control; and any annexes, footnotes, cross-references or transitional provisions that change how it should be read. Where the files differ in version or as-at date, list each difference and say how you will handle it: map the adopted or transposed version marked on the Inputs tab, even where it does not yet apply, and keep drafts and consultation texts out of the mapping. Stop and ask me only if a difference means two texts cannot validly be compared.

STEP 2. TRACE THE PREPARATION
For each rule text, say how it was built: which articles of the base text each amending provision inserts, replaces or deletes; which provisions leave detail to a Level 2 measure or guideline; which national provisions add to or choose an option in the directive; and the application date and any transitional period of each provision. For each policy, say how it is structured, who owns it, when it was last reviewed, and whether it cites the articles it implements. Compare how the prior register, if any, records obligations with how this change states them.
Label each statement "stated in the file" or "my inference".

STEP 3. MATCH AND RECONCILE
3a. Keys. List the identifiers in each file: article, paragraph and point numbers; policy document IDs and section numbers; control IDs; references in the prior register. Say which policies and controls cite articles and how complete those citations are.
3b. Names. Where a policy cites no article, describe how it names the topic (for example "liquidity management tools", "delegation", "loan origination", "investor disclosures") and how the rule text names it.
3c. Standardise. Write numbered rules for article citations (for example "Art. 16(2b)" and "Article 16 paragraph 2b" become one form, and an amended article is cited as it reads after amendment) and for topic names. Apply them in new columns, keeping the original text.
3d. Map. Build a mapping table: obligation reference, policy ID and section, control ID, match method ("cited article", "standardised topic" 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 when a further measure is published.
- Inputs: each rule text in scope with its version, status (adopted, transposed, draft or consultation) and as-at date, its in-force date, and the applicable-from date of each provision, including any transitional period, with its source; the entities and fund types in scope; the obligation categories "new", "amended", "deleted" and "unchanged"; the coverage categories "covered", "partly covered", "not covered" and "not applicable", each with its definition and rank as the compliance policy sets it; the risk rating scale the compliance policy sets; the list of owner functions; plus the as-at date of each source file, with any differences between them and how each is handled. 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, one row per paragraph or section with its original numbering. Never edit these tabs.
- Map: the mapping table from step 3d, and for each suggested match who confirmed it and when.
- Obligations: one row per obligation. Reference, quoted text, page or link, category, the in-force date and the applicable-from date (lookups to the Inputs tab), entities in scope, and a "Level 2 pending" flag where the detail awaits a measure not yet adopted, a proposed overall rating (a formula: the strongest rating across its Coverage rows, using the rank on the Inputs tab) and the compliance officer's confirmed rating. Paragraphs that carry no obligation (definitions, recitals, amending instructions only) are listed with the reason. Each row carries its source document and the page, cell or row reference for each figure.
- Coverage: one row per obligation and mapped document. Policy ID and section, the quoted policy text that meets the obligation, control ID, coverage category, and reason. A status formula reads "Rated" or "Check". Each row carries its source document and the page, cell or row reference for each figure.
- Gaps: a formula-driven list, one row per obligation, of every obligation whose confirmed rating is "partly covered" or "not covered". Obligation reference, gap description, proposed action (amend policy, new procedure, new control, training, system change), risk rating, owner, target date, applicable-from date, status ("open" or "closed") and the date it was last reported to the compliance officer. Days from target date to applicable-from date, and an "overdue" flag where the applicable-from date has passed and the gap is still open, are formulas.
- Exceptions: one row per failed control, untraced or misquoted text, unconfirmed match, gap without an owner or a target date, overdue gap under control g, and open query. Each row carries its type ("failed control", "untraced or misquoted", "unconfirmed match", "gap without owner or target date", "overdue gap", "query" or "timing difference"), its name (for a gap, the obligation reference and gap description from the Gaps tab), its owner, its target date (for a gap, from the Gaps tab), status ("open" or "closed"), expected clearing date and the approver's sign-off (name and date). A gap on the Gaps tab is the output, not an exception.
- 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. An overdue gap under control g is also carried by name, with its owner and target date: it is a known breach being remediated, not a reconciling item. 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 paragraph on the rule-text _Source tabs either produces at least one row on the Obligations tab or is listed there as carrying no obligation, with the reason; and every obligation has at least one row on the Coverage tab. Target: 0 unexplained rows.
b. Totals tie: the paragraph count on each rule-text _Source tab equals the obligation-bearing paragraphs plus the paragraphs with no obligation; and the number of obligations whose confirmed rating on the Obligations tab is "partly covered" or "not covered" equals the number of rows on the Gaps tab, both counted as distinct obligations (Coverage holds one row per obligation and document, so count distinct obligation references, never Coverage rows). Target: a difference of 0.
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: every obligation on the Obligations tab quotes its article, paragraph and point word for word and cites the page or link; every "covered" or "partly covered" row on the Coverage tab quotes the policy text it relies on and cites the section. Every figure must be traced (it cites its document and page) and must agree (it equals the figure in that document), word for word for quoted text. Any item that is untraced or disagrees goes to the Exceptions tab at once. If the document itself looks wrong, for example a national provision that cites an article the directive does not contain, raise it as a query; never change the workbook to make it agree. Target: 100%.
e. Matches confirmed: count the rows matched by cited article, by standardised topic and by suggestion. Target: 0 suggested matches left unconfirmed at sign-off.
f. Versions and dates: every rule text has its version, status, as-at date, in-force date and applicable-from dates on the Inputs tab. Adopted and transposed texts are mapped even before they apply; drafts and consultation texts are never rated. Target: 100%.
g. Application dates: every obligation carries the applicable-from date of its provision from the Inputs tab, including any transitional period. Every target date on the Gaps tab falls on or before that date. Where the applicable-from date has already passed (for example a transposition deadline already reached), a gap with a later target date does not fail this control: it is flagged "overdue" on the Gaps tab, listed by name on the Exceptions tab with its owner and target date, and reported to the compliance officer until it closes. Any other target date after the applicable-from date fails this control.
h. Level 2 pending: every obligation flagged "Level 2 pending" is rated "partly covered" or "not covered", never "covered", and stays on the Gaps tab until the measure is adopted and mapped.
i. Owners: every row on the Gaps tab has an owner from the list on the Inputs tab, a target date and a proposed action. Target: 0 blank cells.
j. Rating basis: every coverage rating uses only the categories on the Inputs tab, and a "covered" rating cites both a policy section and a control that tests it; a policy with no testing control is rated "partly covered".
k. Open exceptions: count the Exceptions rows that are not closed, leaving out only named timing differences with an expected clearing date and overdue gaps carried under control g with an owner and target 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: when a further measure is published, a person adds or replaces the rule-text _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, issuer, version, as-at date, date received, paragraph count), every rule and match you applied with the reason, every assumption, and who prepared and approved. Never overwrite source text. A correction goes in a separate column with a note.
- Measured: add a KPIs tab that the team fills in for each rule change: back-test recall (run on a past rule change where the gap analysis is known; share of known gaps found; target 100%, and do not adopt the workbook below that); obligations rated 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 publication of the text to the signed gap register, 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 obligations identified, the number covered, partly covered, not covered and not applicable, the number of gaps with an owner and a target date, the result of every check, and a list of anything you were unsure about.