Data-room & due-diligence first-pass review

You are helping a deal team associate do a first pass over the data room for [target]. A human reviewer checks everything you produce. You do not reach a view on the deal.

I attach:
- Data room index: [folder and file list]
- Request list: [the diligence request list]
- Documents: [the files in scope]

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 document; 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 document, record type, date, parties, and whether it is signed, a draft or superseded.
Label each statement "stated in the file" or "my inference".

STEP 3. MATCH AND RECONCILE
3a. Keys. List request list item numbers and data room references.
3b. Names. Describe how counterparties are named across contracts.
3c. Standardise. Apply numbered rules in new columns.
3d. Map. Link documents to request items, with 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: target, base currency, issue categories, materiality threshold in the base currency; plus the as-at date of each source file, with any differences between them and how each is handled; any FX rates needed, each stated as base-currency units per one unit of the other currency (1.00 for the base currency itself), with their source and date, 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: documents linked to request items, by request item number and data room reference, with the match method ("shared ID", "standardised name" or "suggested, needs review"), the reason, and the person who confirmed each suggested match and the date.
- DocIndex: one row per document, with its data room reference, folder path, title, type, parties, date, status (signed, draft or superseded, as the document shows it) and, for a superseded document, the document that replaces it, and the count of request items linked to it on the Map tab.
- Issues: one row per issue, with category (from the issue categories on the Inputs tab), document (its data room reference on the DocIndex tab), that document's status looked up from the DocIndex tab, a flag where the status is not signed, page and clause quoted, the amount at stake and its currency where the clause or document states one, the FX rate looked up from the Inputs tab, the amount converted to the base currency by formula (amount times that rate), and a materiality rating: by formula comparing the converted amount with the materiality threshold on the Inputs tab where an amount is stated ("material" or "below threshold"), otherwise "deal team to rate", with the deal team's rating and reason entered by a person. Each row carries its source document and the page, cell or row reference for each figure.
- Gaps: request items with no document, by formula: the request item number from the request list _Source tab and the count of documents linked to it on the Map tab.
- Exceptions: one row per failed control, untraced or disagreeing figure, unconfirmed match and open query, with a type column ("failed control", "untraced or disagrees", "unconfirmed match", "query" or "timing difference"), 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 entry 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 file in the room has a DocIndex row, and every request item is covered or listed on Gaps. Target: 0 unexplained rows.
b. Index agrees: every document cited on the Issues tab exists on the DocIndex tab, and the Gaps count equals the request items with no linked document. 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: on the Issues tab, every issue quotes the clause and cites the page. Every quote and figure must be traced (it cites its document and page) and must agree: a quote matches the document word for word, and a figure equals the document within the tolerance on the Inputs tab and no wider. Any item that is untraced or disagrees goes to the Exceptions tab at once. If the document itself looks wrong, for example a signed agreement whose index title names a different party, 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. Material issues: every issue carries one of the issue categories on the Inputs tab and a materiality rating on the Issues tab. An issue with a stated amount is converted to the base currency at an FX rate on the Inputs tab and rated against the materiality threshold on the Inputs tab by formula, never comparing amounts in different currencies; an issue with no amount, such as a change-of-control or exclusivity clause, reads "deal team to rate" until a person rates it. Target: 0 issues unrated at sign-off.
g. Status: every document is marked signed, draft or superseded, and issues cite signed documents unless flagged.
h. Coverage: a request item counts as covered only when a linked document on the Map tab answers it by its content, not only by its file name. A match by file name alone stays "suggested, needs review".
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 documents indexed, issues found, and request items not covered, the result of every check, and a list of anything you were unsure about.