Quarterly investor report drafting

You are helping an investor relations or fund finance analyst draft the quarterly investor letter for [fund] for the quarter ended [date] and fact-check every figure in it. A human reviewer checks everything you produce. You do not send anything to investors, you do not write a figure the attached sources do not contain, and the authorised signatory approves the letter, not you.

I attach:
- Capital account statements: [the administrator's statements for the quarter, per investor and fund total]
- Fund accounts: [quarterly financial statements with the schedule of investments]
- Cash flows: [dated LP contributions and distributions since inception, and, if net IRR is shown without the facility, the subscription facility drawdown ledger: each drawing and repayment with its date and amount and the capital call that repaid it]
- Realisations: [distribution records or realisation notices for each exit or partial exit in the quarter]
- Portfolio data: [each company's reporting pack and the valuation committee memo for the quarter]
- Drafting notes: [the deal team's notes, last quarter's letter and the approved letter template]

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 an investor, a fund, a company and a period; 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
Say how each source built its figures: how the capital account statements roll from opening to closing balance (contributions, distributions, management fees, fee offsets, partnership expenses, income, realised and unrealised gains, carried interest allocated); how the fund accounts state the fair value of each investment and the valuation date; which LP cash flows and terminal value the net IRR uses; where any gross figure comes from (it needs deal-level cash flows, which are not attached); whether each performance figure is gross or net and with or without the subscription facility; and which version of the ILPA Reporting Template the fund follows. Note where last quarter's letter states a figure that this quarter's sources restate.
Label each statement "stated in the file" or "my inference".

STEP 3. MATCH AND RECONCILE
3a. Keys. List the identifiers in the capital account statements, the fund accounts and the portfolio data: fund code, investor or account number, company code. Say which appear in all of them and how complete each is.
3b. Names. Where no shared code exists, describe how each source names the fund (parallel, feeder or currency class), the investor and each company (legal entity, holding company, trading name), and how the drafting notes name them.
3c. Standardise. Write numbered rules for fund, investor and company names and apply them in new columns, keeping the original text.
3d. Map. Build a mapping table: name as written in each source, standardised name and code, 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 quarter end, the base currency, and FX rates with source and date. The performance method as the fund's reporting policy sets it: net IRR basis and day-count, and the subscription facility basis for net IRR ("with facility", "without facility" or "both"). Where net IRR is shown without the facility, the construction as the ILPA Reporting Template sets it: LP cash flows as if capital had been called on the dates the facility was drawn, instead of the dates of the capital calls that repaid it, with those dates and amounts from the facility drawdown ledger. The rounding convention for figures in the letter. The ILPA Reporting Template version the fund follows. For each capital account line, its effect on unfunded commitment under the LPA: "decrease", "increase" or "no impact"; 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. Each row carries its match method ("shared code", "standardised name" or "suggested, needs review"), the reason, and the person who confirmed each suggested match and the date.
- Claims: one row per figure or factual statement in the draft letter: claim ID, the sentence as drafted, the figure as written, the basis label it carries (gross or net, with or without the facility), the source it ties to (a cell on the CapAcct, Performance or Portfolio tab, or a document and page), the period the figure covers, the source's as-at date, the as-at date the letter states for it (if any), the value for the same claim in last quarter's letter (if any) with its page, the source value by formula, the source value rounded by the rounding convention, difference, status ("Agrees", "Check" or "Unsupported"), and a compliance flag (forward-looking, performance comparison or valuation explanation). Each row carries its source document and the page, cell or row reference for each figure.
- CapAcct: one row per investor, a row for the general partner and any carried interest capital as the capital account statements show them, and a fund total, compared with the partners' capital in the fund accounts with its page: commitment, contributions to date, recallable distributions, unfunded commitment, opening capital account, each movement line from step 2, recalculated closing balance, closing balance on the statement, difference and status, all as formulas. Unfunded commitment is a signed formula from commitment and each line's effect on the Inputs tab.
- Performance: one row per measure the draft states: paid-in capital, distributions, NAV, DPI, RVPI, TVPI and net IRR, each recalculated from the LP cash flows and NAV on the _Source tabs, with net IRR by XIRR on the dated cash flows and the closing NAV as the terminal value, on the subscription facility basis from the Inputs tab. For net IRR without the facility, an adjusted LP cash-flow schedule by formula: each part of a capital call that repaid a facility drawing moves to that drawing's date and amount from the facility drawdown ledger _Source tab, every other LP cash flow stays as dated, and XIRR runs on that schedule; any gross figure is traced to its page in the fund accounts or portfolio data and marked "traced, not recomputed"; then the figure in the draft, difference and status.
- Portfolio: one row per company the draft names: cost and fair value this quarter and last from the schedule of investments, realised proceeds from the realisations records or the fund accounts, multiple of cost as a formula, the valuation reason the draft gives and the memo page that supports it, each operating figure the commentary cites from the company's own pack, then the figure in the draft, difference and status.
- Exceptions: one row per failed control, untraced or disagreeing figure, unsupported statement, bracketed gap in the draft, unconfirmed match and open query, with a type column ("failed control", "untraced or disagrees", "Unsupported", "gap", "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.
Separately from the workbook, give me the draft letter as plain text in the approved template's sections. Tag every figure and every factual statement with its claim ID in square brackets, for example [C-014]. Write only from the attached sources and the drafting notes. Where they do not support a sentence the notes ask for, leave a bracketed gap and list it on Exceptions.

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 figure and factual statement in the draft letter has a Claims row with a claim ID, and every claim ID in the letter appears on the Claims tab. Target: 0 unexplained rows.
b. Totals tie: on the CapAcct tab, the investor closing balances plus the general partner and carried interest capital, as the capital account statements show them, sum to the partners' capital in the fund accounts, and each investor's recalculated closing balance equals its capital account statement. 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: every row on the Claims tab cites its source cell or document 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 capital account statement that disagrees with the fund accounts, 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. FX and dates: every FX rate and every as-at date difference has its source on the Inputs tab. Target: 100%.
g. Roll-forward: for every investor on the CapAcct tab, the opening balance plus the movement lines equals the closing balance, and the unfunded commitment follows each line's effect on the Inputs tab and never goes below zero.
h. Performance: every net measure on the Performance tab is recalculated from LP cash flows and NAV by the performance method on the Inputs tab, every gross figure is traced to its source page, and none of the formulas refers to a figure in the draft. Net IRR without the facility runs on the adjusted LP cash-flow schedule, and every capital call that repaid a facility drawing ties to that drawing on the facility drawdown ledger. Gross and net figures, and figures with and without the subscription facility, are never mixed in one sentence without both labels.
i. Portfolio: every company figure on the Portfolio tab ties to the schedule of investments or the company pack it cites, and every valuation reason the draft gives appears in the valuation committee memo.
j. Period: on the Claims tab, every source as-at date is the quarter end or the letter states that date, and no figure is carried forward from last quarter's letter without a current source.
k. Wording: every statement marked "Unsupported" is on Exceptions, and every forward-looking statement, performance comparison and valuation explanation is flagged on the Claims tab for compliance review.
l. Open exceptions: count the Exceptions rows that are not closed, leaving out only named timing differences with an expected clearing date. Target: 0.
m. 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 quarter, 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 edit the IR lead made to the draft that changed a figure, 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 quarter: back-test recall (run on a past quarter's letter with known errors; share of known errors found; target 100%, and do not adopt the workbook below that); figures agreed first time with no manual fix; figures flagged that turned out fine; corrections made by the reviewer before sign-off; corrections found after the letter is sent (target 0); manual edits outside the Inputs and _Source tabs at refresh (target 0); and cycle time in hours from receiving the final 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 figures and statements in the draft, the number that agree, the number on Exceptions and the number marked "Unsupported", the result of every check, and a list of anything you were unsure about.