Delegate and service-provider oversight

You are helping a delegate oversight analyst at [AIFM or management company] review this year's evidence on [list of delegates and service providers, for example the administrator, transfer agent, portfolio manager and depositary] for the delegation oversight file. A human reviewer checks everything you produce. You do not rate a delegate and you do not decide whether a delegation continues. You show what the evidence says and where it falls short.

I attach:
- Control reports: [each ISAE 3402 or SOC 1 Type II report, any bridge letter, and last year's report from each delegate for the repeat test]
- KPI packs: [each delegate's monthly KPI and SLA reports for the period]
- DDQ returns: [this year's and last year's due-diligence questionnaire returns per delegate]
- Agreements: [each delegation or service agreement and its SLA schedule]
- Delegation register: [delegates, functions, funds served, risk rating, last due diligence date, and the reason recorded for any delegate that provides no control report]
- Control register: [the manager's own controls, with control IDs, owners and the process each control covers]
- Prior findings: [last year's oversight findings per delegate and their status]

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, meaning the report period end, the pack month or the questionnaire date (not the date it was sent); the version of each agreement and SLA schedule; what one row or section represents (control objective, control activity, test result, KPI, question); the fields that identify a delegate, a fund, a control and a KPI; the value fields and how each is labelled; and any subtotals, footnotes, carve-outs or restricted-use clauses that change how it should be read. Where the files differ in as-at date or period, list each difference and say how you will handle it: compare like periods only, and test each report period against the year-end on the Inputs tab. Stop and ask me only if a difference means two figures cannot validly be compared.

STEP 2. TRACE THE PREPARATION
For each control report, say how it was built: the standard (ISAE 3402 or SOC 1), Type I or Type II, the period, the service auditor, the opinion and any qualification, the control objectives and who wrote them, the deviations found in testing with management's response, the complementary user entity controls (CUECs), and each subservice organisation with whether it is included or carved out. For each KPI pack, say how each KPI is calculated and which SLA clause sets its target. For each DDQ, say which answers carry evidence and which are self-reported only.
Label each statement "stated in the file" or "my inference".

STEP 3. MATCH AND RECONCILE
3a. Keys. List the identifiers in each file: delegate legal name and LEI, fund codes, control objective and control activity numbers, KPI names, DDQ question numbers, the manager's control IDs. Say which appear in more than one file and how complete each is.
3b. Names. Where no shared code exists, describe how each file names the delegate (group name, legal entity, branch) and how each report describes a CUEC compared with the manager's control register.
3c. Standardise. Write numbered rules for delegate names, KPI names and control descriptions, and apply them in new columns, keeping the original text.
3d. Map. Build a mapping table: report item (deviation, CUEC or KPI), manager control ID and owner, 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: the review period (first and last month); the manager's and each fund's financial year-end; the maximum gap a bridge letter may cover, as the oversight procedure sets it; the version of each agreement and SLA schedule, with each SLA target and its clause; the threshold for escalating repeated SLA breaches (for example consecutive months), as the oversight procedure sets it; the rating categories for delegates with their definitions, and a rating rule table that sets, for each category, the thresholds the oversight procedure uses (opinion type, with any qualified opinion, adverse opinion or disclaimer of opinion setting the rating no higher than the category the procedure assigns to it; open and repeat deviations, unowned CUECs, SLA breaches, DDQ flags); the DDQ questions the manager flags as material, by question number; the categories for deviations (one-off, repeat, control objective not achieved); plus the as-at date of each source file, with any differences between them and how each is handled; and 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, with the person who confirmed each suggested match and the date.
- Reports: one row per control report. Report ID, delegate, standard, type, period start and end, service auditor, opinion (unqualified, qualified, adverse or disclaimer of opinion, as the report states it), report date, bridge letter period, the number of test results with deviations and the number of CUECs the report states, and the control objectives the service auditor reports as not achieved. A Type I report has no test period and is recorded as such. Each row carries its source document and the report section and page for each item.
- Deviations: one row per deviation in each report, with the report ID and delegate, the control objective, the control activity, the auditor's test result, management's response, the remediation date, the mapped manager process (through the Map tab and the control register), the category from the list on the Inputs tab, a repeat flag (a formula against last year's report), and a status ("open" or "remediated", as the report or the delegate's follow-up states it). Each row carries its source document and the report section and page for each item.
- CUEC: one row per CUEC in each report, with the report ID and delegate, the mapped manager control ID, the owner, the evidence that it operated in the period and a status formula reading "Owned" or "Check". Each row carries its source document and the report section and page for each item.
- Subservice: one row per subservice organisation, with the report ID and delegate, included or carved out, and whether the manager holds its report or the delegate's monitoring evidence, with its reference.
- SLA: one row per KPI per month, with the delegate, the KPI name, the agreement version applied and the breach the pack itself reports. Target (a lookup to the Inputs tab), actual, breach flag, consecutive breaches and escalation status ("Escalate" where consecutive breaches reach the threshold on the Inputs tab, otherwise "No") are formulas. Each row carries its source document and the page, cell or row reference for each figure.
- Coverage: one row per delegate and fund served, from the delegation register. The fund's year-end (a lookup to the Inputs tab), the covering report with its type and period start and end and the bridge letter with its period (lookups to the Reports tab), the bridge letter length in days against the maximum on the Inputs tab, a covered flag, and days of the year-end not covered, as formulas.
- DDQ: one row per question per delegate. This year's answer quoted from the return, last year's answer, a changed flag (a formula), a material flag (a lookup to the Inputs tab), evidence attached (yes or no), evidence basis ("evidenced" or "self-reported only", as step 2 records it), a follow-up flag (a formula: changed, or material with no evidence attached) and a follow-up note. Each row carries its source document and the page, cell or row reference for each figure.
- Delegates: one row per delegate. Control report held, the reason for no control report (from the delegation register), months of KPI pack received against the review period, DDQ return held, opinion, control objectives not achieved, open deviations, repeat deviations, unowned CUECs, SLA breaches, DDQ flags and open prior findings are formulas from the tabs above and the delegation register and prior findings _Source tabs; a proposed rating applies the rating rule table on the Inputs tab by formula, with a column for the person's confirmed rating and reason.
- Exceptions: one row per failed control, untraced or disagreeing figure, unconfirmed match, document not received, Type I report, bridge letter over the maximum, qualified or adverse opinion, disclaimer of opinion, control objective not achieved, CUEC with no matching control, carved-out subservice organisation with no evidence and open query, with a type column ("failed control", "untraced or disagrees", "unconfirmed match", "not received", "Type I report", "bridge letter over maximum", "qualified or adverse opinion", "disclaimer of opinion", "control objective not achieved", "gap in the manager's own controls", "subservice evidence missing", "query" or "timing difference"), the delegate it concerns, 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, with the names of the preparer and the approver.
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 delegate in the delegation register has a control report or a reason it has none, a KPI pack for every month and a DDQ return, or appears on Exceptions as "not received"; every deviation and every CUEC in each report appears once on the Deviations and CUEC tabs. Target: 0 unexplained rows.
b. Totals tie: for each report, the count on the Deviations tab equals the number of test results with deviations in the report, and the count on the CUEC tab equals the report's list; for each delegate, the breach count on the SLA tab equals the breaches the delegate's own pack reports. Target: a difference of 0 on each count.
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 opinion and period on the Reports tab, every deviation and management response on the Deviations tab and every CUEC on the CUEC tab cites its report section and page; every KPI on the SLA tab ties to its pack and page, and every DDQ answer on the DDQ tab quotes the return and its page. 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 KPI pack whose total disagrees with its own detail, raise it as a query; never change the workbook to make it agree. Target: 100%.
e. Matches confirmed: count the rows matched by shared code, by standardised name and by suggestion. Target: 0 suggested matches left unconfirmed at sign-off.
f. Period coverage: on the Coverage tab, for every delegate and fund, the report period plus any bridge letter covers that fund's year-end on the Inputs tab with no gap. A Type I report has no test period and gives no assurance on operating effectiveness, so it goes to Exceptions under this control; a bridge letter longer than the maximum on the Inputs tab goes to Exceptions, and a bridge letter is recorded as the provider's statement, never as assurance.
g. Opinion: any qualified opinion, adverse opinion or disclaimer of opinion, and any control objective the service auditor reports as not achieved, goes to Exceptions and to the delegate's row on the Delegates tab.
h. Deviations mapped: every deviation is linked to the manager process it affects, carries management's response and a remediation date, and is classed using the categories on the Inputs tab; a repeat deviation is flagged.
i. CUECs owned: every CUEC maps to a manager control with a named owner and evidence that it operated in the period. One with no matching control goes to Exceptions as a gap in the manager's own controls.
j. Subservice: every carved-out subservice organisation is listed with the evidence the manager holds on it, or goes to Exceptions.
k. SLA breaches: every KPI is tested against the target in the agreement version on the Inputs tab, and every run of breaches at or above the threshold on the Inputs tab shows an escalation status.
l. DDQ evidence: every answer that changed from last year, and every answer to a question flagged as material on the Inputs tab with no evidence attached, is flagged for follow-up; a self-reported answer is never recorded as tested.
m. Rating basis: every proposed rating on the Delegates tab applies the rating rule table on the Inputs tab by formula, and every confirmed rating that differs from it carries a reason.
n. Open exceptions: count the Exceptions rows that are not closed, leaving out only named timing differences with an expected clearing date. Target: 0.
o. 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, page or 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 year's oversight file with known findings; share of known findings 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 reports to the signed oversight file, 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 delegates reviewed, the number of deviations and repeat deviations, the number of CUECs owned and unowned, the number of SLA breaches escalated, and the number of items on Exceptions, the result of every check, and a list of anything you were unsure about.