Portfolio-company reporting roll-up

You are helping a portfolio monitoring analyst roll up this quarter's portfolio company reporting for [fund]. A human reviewer checks everything you produce. You do not adjust a company's figures.

I attach:
- Packs: [each company's reporting pack]
- KPI definitions: [the fund's KPI list and definitions]
- Prior quarter: [last roll-up]
- Portfolio list: [the fund's current portfolio companies with their identifiers]

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 and a KPI; 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 pack, record currency, period, and whether figures are reported, adjusted or pro forma.
Label each statement "stated in the file" or "my inference".

STEP 3. MATCH AND RECONCILE
3a. Keys. List company identifiers across packs and the fund's portfolio list.
3b. Names. Describe how companies and KPI labels are named in each pack.
3c. Standardise. Map each label to the fund's KPI definition with numbered rules.
3d. Map. Link each pack line to a company and KPI, with match method and reason.

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: quarter, the fund's reporting currency, FX rates with source and date, each stated as reporting-currency units per one unit of the other currency (1.00 for the reporting currency itself): a closing rate at the quarter end and an average rate for each measurement period the KPIs use (quarter, year to date or last twelve months), with any currency whose rate moved sharply in a period flagged for a person, movement threshold, the KPIs required from each company as the KPI definitions set them, and for each KPI its measurement period (quarter, year to date, last twelve months or point in time), its rate basis (average rate for flow measures such as revenue and EBITDA, closing rate for point-in-time measures such as cash and debt, "no FX" for ratios, percentages and counts such as margin, leverage or headcount) and its aggregation rule: sum, weighted average (with the weight) or ratio recalculated from summed components; 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: one row per pack line, mapped to a company and KPI, with the match method ("shared ID", "standardised name" or "suggested, needs review"), the reason, and for each suggested match who confirmed it and when.
- Rollup: one row per company and KPI, pulled through the Map tab, with its basis (reported, adjusted or pro forma) as the company pack states it, the reported currency and value, the KPI's measurement period, the FX rate looked up from the Inputs tab on the KPI's rate basis and measurement period (none for a "no FX" KPI), the value converted into the reporting currency by formula as value times rate (unchanged for a "no FX" KPI), and a status by formula reading "reported" or "not reported" where a required KPI has no pack value. Below the company rows, one portfolio-level row per KPI, computed from the converted values by its aggregation rule. Each row carries its source document and the page, cell or row reference for each figure.
- Movement: one row per company and KPI, with this quarter's converted value from the Rollup tab, last quarter's value from the prior quarter _Source tab, the change, and a flag by formula where the change is above the movement threshold.
- Exceptions: one row per failed control, untraced or disagreeing figure, unconfirmed match and open query. Each row carries its type ("no pack received", "not reported", "failed control", "untraced or disagrees", "unconfirmed match", "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: every company in the portfolio has a pack, and every required KPI has a value or is flagged "not reported". Target: 0 unexplained rows.
b. Aggregation: each portfolio-level KPI follows its aggregation rule on the Inputs tab after FX: additive KPIs such as revenue sum, while margins, growth rates and leverage are weighted averages or ratios recalculated from summed components. 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 Rollup tab, every value cites the pack 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 pack whose summary page differs from its own detailed tables, 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, and every Rollup row uses the rate its KPI's rate basis on the Inputs tab calls for: the average rate for the KPI's own measurement period for a flow measure, the closing rate for a point-in-time measure, and no conversion for a "no FX" KPI; every converted value equals value times rate. Target: 100%.
g. Movement: every company KPI whose change on the Movement tab is above the movement threshold on the Inputs tab has the pack's own explanation cited, or is listed on Exceptions as unexplained.
h. Basis: every adjusted or pro forma figure is labelled as such.
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 companies complete, KPIs missing, and movements above the threshold, the result of every check, and a list of anything you were unsure about.