ESG and SFDR data collection from portfolio companies

You are helping an ESG or sustainability analyst collect this year's portfolio-company ESG data for [fund] and map it to the principal adverse impact (PAI) indicators for the reference period [1 January to 31 December of year]. A human reviewer checks everything you produce. You do not decide that an estimate is acceptable, and you do not publish or submit anything.

I attach:
- Returns: [each company's completed ESG questionnaire and its supporting documents]
- Indicator list: [the Annex I Table 1 indicators of Delegated Regulation (EU) 2022/1288, the additional Table 2 and Table 3 indicators the fund has chosen, and the EDCI metrics if the fund reports to the EDCI]
- Valuations: [each holding's company code, asset type, currency, value and enterprise value at the four quarter-ends]
- Estimate method: [the approved method for missing data, from the PAI policy]
- Prior year: [last year's PAI workbook or statement]

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 company, a fiscal year and a data point; 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 company and data point, record: the unit used; the fiscal year it covers and whether that matches the reference period; the method the company states (for example location-based or market-based Scope 2, or the boundary of its emissions inventory); and whether the company labels the figure as measured, estimated or not available. A zero the company reports, such as no fossil fuel revenue, is a reported value, not a gap. Note where a return gives a total without its parts, and where a group report covers more than one portfolio company. Compare each value with the prior year and note the method changes the company states.
Label each statement "stated in the file" or "my inference".

STEP 3. MATCH AND RECONCILE
3a. Keys. List the identifiers in the returns, the valuations and the prior-year workbook: company code, legal entity identifier, registration number. Say which appear in all three and how complete each is.
3b. Names. Where no shared code exists, describe how each file names the company (legal entity, trading name, holding company, group) and how the questionnaire labels each data point.
3c. Standardise. Write numbered rules for company names and for questionnaire labels, and apply them in new columns, keeping the original text.
3d. Map. Build a mapping table: company as named in the return, company code in the valuations, questionnaire label, PAI indicator or EDCI metric, 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 reference period, the four quarter-end dates, the base currency, and FX rates with source and date. For each indicator, its Annex I metric, its unit, its formula type (value-weighted by enterprise value, share of investments, or weighted average) and the unit conversion factors with their source. The enterprise value basis for unlisted companies, as the PAI policy sets it. The approved estimate method for each indicator, with its version and approval date. The movement threshold for year-on-year flags, and the data categories "reported", "estimated", "not applicable" and "not available". The asset types "investee company", "sovereign or supranational" and "real estate". An applicability table listing, for each indicator, the asset types it applies to. In Annex I Table 1, indicators 1 to 14 apply to investee companies, 15 and 16 to sovereigns and supranationals, and 17 and 18 to real estate; each chosen Table 2 or Table 3 indicator applies to the asset type its table lists it under; 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 (control e counts "shared code" as the shared-ID match), with the person who confirmed each suggested match and the date.
- Responses: one row per holding and data point, as read: the PAI indicator or EDCI metric it maps to through the Map tab, asset type from the valuations, value, unit, fiscal year, page reference, the company's own label, the category (reported, estimated, not applicable or not available), and for an estimate the method, its version and its inputs. Each row carries its source document and the page, cell or row reference for each figure.
- Normalised: one row per holding and indicator: the asset type, whether the indicator applies to it under the applicability table on the Inputs tab, the value converted to the indicator's unit by formula from the Responses and Inputs tabs, the conversion factor used, the fiscal year, a flag where it differs from the reference period with how it was treated, the category and the estimate method with its version carried through, and the prior-year value for the holding from the prior-year _Source tab.
- PAI: one row per indicator. The formula type applied (a lookup to the Inputs tab). For each quarter-end, the current value of investments in the base currency at the FX rates on the Inputs tab, the company values from the Normalised tab and the indicator computed by its Annex I formula; then the impact as the average of the four quarter-ends; the applicable investment value (holdings of the asset types the indicator applies to) and its coverage share that is reported, estimated and not available; and the prior-year impact and movement. All are formulas.
- Exceptions: one row per failed control, untraced or disagreeing figure, unconfirmed match, unreturned questionnaire, estimate awaiting acceptance, movement above the threshold and open query, with a type column ("failed control", "untraced or disagrees", "unconfirmed match", "no return received", "estimate awaiting acceptance", "movement above threshold", "query" or "timing difference"), the holding and indicator it concerns, its data category from the Inputs tab, and for a movement row the impact this year and last and each driving company's current and prior-year figures from the Normalised tab; then 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 each estimate accepted, the name of the ESG lead who accepted it and the date, and 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 holding at any of the four quarter-ends has an asset type, and has a Responses row for every indicator that applies to its asset type under the applicability table on the Inputs tab, or appears on Exceptions as "no return received" with the category "not available". Indicators that do not apply are marked "not applicable" and are not counted as missing. Target: 0 unexplained rows.
b. Totals tie: for each quarter-end, the investment values on the PAI tab total the valuations _Source tab. Target: a difference of 0.00. For each indicator, the reported, estimated and not-available coverage shares on the PAI tab sum to 100% of the applicable investment value, with "not applicable" holdings left out. 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 value on the Responses tab cites the return or report and the page it came from. 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 group report whose total differs from the company's own return, 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. Reported vs estimated: every value on the Normalised tab carries one of the data categories on the Inputs tab, "not applicable" appears only where the applicability table says the indicator does not apply, a reported zero stays "reported", every estimate cites the approved method and its version on the Inputs tab, and no estimate is labelled as reported. Every estimate stays on Exceptions until the ESG lead accepts it on the _Log.
h. Formulas: each indicator on the PAI tab uses the formula type on the Inputs tab, and the impact equals the average of the four quarter-end values. Target: a difference of 0.00.
i. Units and period: every value is converted with a factor on the Inputs tab, and every value whose fiscal year differs from the reference period is flagged with how it was treated.
j. Movement: every indicator whose impact moved by more than the threshold on the Inputs tab against the prior year is listed on Exceptions with the company figures that drive it.
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 estimate and who accepted it, 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 companies with a complete return, the number of values reported, estimated, not applicable and not available for each indicator, and the indicators that moved above the threshold, the result of every check, and a list of anything you were unsure about.