Fund financial statements tie-out

You are helping a fund financial reporting analyst tie out the draft annual financial statements of [fund] for the year ended [date]. A human reviewer checks everything you produce. You do not change the draft, you do not post adjustments and you do not conclude on accounting treatment.

I attach:
- Draft accounts: [the draft financial statements, with version number and date]
- Trial balance: [the final year-end trial balance, with the administrator's mapping of accounts to statement lines if available]
- Holdings report: [the administrator's holdings or investment report at the year-end date]
- Capital accounts: [the partners' capital or investor allocation statements for the year]
- Prior year: [last year's signed financial statements]
- Terms: [the LPA or prospectus pages that set the management fee: rate per period and share class, step-downs, offsets and waivers]
- Fee base: [commitments, invested capital or NAV by fee period and share class, as the fee clause defines the base, and the amounts any offset or waiver applies to]
- Ledger detail: [the general ledger detail for the year for the accounts behind the cash flow statement and the note movements]
- Movement schedules: [the cash flow workings, the investment movement schedule (purchases, sales, realised and unrealised gains and losses), the payables and receivables analysis, and any other note roll-forward the administrator prepares]

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 account, a holding, an investor and a statement line; 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 primary statement and each note, say how its figures were built: which trial balance accounts feed each line, which lines come from the holdings report (investments at fair value, cost, unrealised gains and losses, the fair value hierarchy), which come from the capital account statements (contributions, distributions, allocation of the result), which come from the ledger detail or a movement schedule (cash flow lines, investment movements, payables and receivables, other note roll-forwards), and which are carried from the prior year (comparatives, opening balances). Name any figure whose source is not among the files I attached. Note the rounding unit, any reclassification of comparatives and any line that nets two balances. Compare how the trial balance signs debits and credits with how the draft presents them.
Label each statement "stated in the file" or "my inference".

STEP 3. MATCH AND RECONCILE
3a. Keys. List the identifiers in each source: account numbers, holding or security identifiers (ISIN, internal asset code), investor numbers, note and line references. Say which appear in more than one source and how complete each is.
3b. Names. Where no shared code exists, describe how the draft names a line or a holding and how the trial balance or the holdings report names it.
3c. Standardise. Write numbered rules for account, line and holding names and apply them in new columns, keeping the original text.
3d. Map. Build a mapping table keyed by account and presentation group: each trial balance account maps to exactly one primary-statement line, and may also map to lines in one or more note or presentation groups (for example the fair value hierarchy, or a concentration disclosure). The same balance may appear in several groups. Within each group, the mappings for a primary-statement line must sum to that line on their own; groups are never added together. Map each holding on the schedule of investments to one holding on the holdings report, with match method ("shared code", "standardised name" or "suggested, needs review") and 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, the draft version, and the steps to refresh the workbook for the next draft or next year.
- Inputs: financial year-end, presentation currency, the draft version number and date, the rounding unit of the draft and the rounding tolerance for casts and ties, FX rates with source and date, the management fee schedule as the LPA or prospectus sets it (rate per period and share class, step-down dates and rates, offsets and waivers), the fee-base series by period and share class (commitments, invested capital or NAV) as the fee clause defines it, and the amounts any offset or waiver applies to by period and share class, with their source, and the list of statement and note categories used on the Figures tab; 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, including the draft's tables as extracted. Never edit these tabs.
- Map: the account mapping from step 3d, one row per account and presentation, with a presentation-group column ("primary statement" or the name of the note or disclosure group), the line, the primary-statement line it reconciles to and the amount mapped; and the holding mapping table. Each row carries its match method ("shared ID", "standardised name" or "suggested, needs review"), the reason, and the person who confirmed each suggested match and the date.
- Figures: one row per figure in the draft, current year and comparative, including every figure stated in the narrative of the notes and the report on activities, with statement or note, line, category, a period column ("current year" or "comparative"), amount as read and page reference; for a comparative, the prior-year signed figure with its page; and for a narrative figure, the table or statement line on the Figures tab that it describes. Each row carries its source document and the page, cell or row reference for each figure.
- Ties: one row per figure on the Figures tab. The source figure (the sum of mapped trial balance accounts, the ledger detail or movement-schedule total, the holdings report total, the capital account total or the prior-year figure), the difference and the status are formulas. A figure with no source among the attached files shows "source not supplied", never an estimate. Status reads "Agrees" within the rounding tolerance, otherwise "Check". For each movement schedule, one further row compares its opening balance with the prior-year closing balance and its closing balance with the statement of financial position, with difference and status as formulas. Each row carries its source document and the page, cell or row reference for each figure.
- Casts: each subtotal and total in every primary statement and note recomputed from its lines, and each cross-reference between a note and a primary statement, with difference and status as formulas.
- SOI: one row per holding on the draft schedule of investments. Quantity, cost, fair value and percentage of net assets as read, the same fields from the holdings report through the Map tab, differences and status as formulas.
- Capital: the roll-forward of partners' capital or net assets attributable to investors. The opening balance, one row for each movement line the capital account statements show (for example contributions, distributions, redemptions, transfers, equalisation, the result for the year and translation differences) and the closing balance, as formulas from the capital account statements, the prior-year accounts and the trial balance, compared with the draft; plus the total of the investor-level capital accounts against the fund's closing balance, with difference and status as formulas.
- Fees: one row per fee period and share class. Fee base, rate, gross fee, offsets, waivers and net fee as formulas from the Inputs tab, with the total compared with the fee note and the trial balance.
- Exceptions: a formula-driven list of every "Check", every "source not supplied" figure, unmapped account, unmatched holding and "suggested, needs review" row, with a type column ("Check", "source not supplied", "unmapped", "unmatched holding", "suggested, needs review", "reclassification", "restatement", "untraced or disagrees", "failed control", "query" or "timing difference"), the reference of the note that discloses each reclassification or restatement, and the person's explanation of each difference. It also lists every failed control and open query, each with 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 figure in the draft, current year and comparative, appears once on the Figures tab, and every trial balance account with a balance maps to exactly one primary-statement line in the "primary statement" group on the Map tab or appears on Exceptions as "unmapped", and every figure on the Ties tab has a source or appears on Exceptions as "source not supplied". Target: 0 unexplained rows.
b. Totals tie: the trial balance nets to zero, the total of the primary-statement mappings on the Map tab equals the trial balance total, for each note or presentation group, taken separately, the mappings to each primary-statement line sum to that line, and for every statement line the sum of its mapped accounts agrees with the draft figure on the Ties tab within the rounding tolerance on the Inputs tab. Target: a trial balance difference of 0.00, and every statement line within the rounding tolerance.
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 figure on the Figures tab cites its draft page, and every source figure on the Ties tab cites its document and page or row. 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 trial balance that disagrees with the administrator's holdings report, 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. Casts: on the Casts tab, every subtotal and total in each primary statement and note equals the sum of its lines within the rounding tolerance on the Inputs tab.
h. Cross-references: every note total that also appears in a primary statement agrees with it, including investments at fair value, the fair value hierarchy total, net assets and the management fee. Every narrative figure on the Figures tab agrees with the table or statement it describes.
i. Prior year: every comparative on the Figures tab equals the prior-year signed accounts, or is listed on Exceptions as a reclassification or restatement with the note that discloses it.
j. Movements: every cash flow line and every note movement on the Figures tab ties to the ledger detail or a movement schedule, each movement schedule's opening balance equals the prior-year closing balance, and its closing balance equals the figure in the statement of financial position, within the rounding tolerance on the Inputs tab.
k. Schedule of investments: on the SOI tab, every holding's quantity, cost and fair value equals the holdings report, the total fair value equals investments in the statement of financial position, and each percentage of net assets recalculates.
l. Capital roll-forward: on the Capital tab, the opening balance equals the prior-year closing balance, opening plus each movement line the capital account statements show (contributions, distributions, redemptions, transfers, equalisation, the result for the year, translation) equals the closing balance, the closing balance equals net assets in the statement of financial position, and the investor-level capital accounts total to the fund.
m. Fee note: the management fee in the notes equals the trial balance, and equals the net fee on the Fees tab, recalculated for each period and share class from the fee schedule and the fee-base series, within the rounding tolerance on the Inputs tab. Any difference goes to Exceptions for a person to explain.
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: for the next draft or next year, a person replaces the _Source tabs and updates the Inputs tab, and every other tab recalculates. Keep the Map tab so confirmed account and holding matches carry forward.
- Auditable: on the _Log tab, list each source file (name, producer, as-at date, draft version, 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 year's draft with known audit adjustments and review points; share of known differences found; target 100%, and do not adopt the workbook below that); figures 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, including by the auditor (target 0); manual edits outside the Inputs and _Source tabs at refresh (target 0); and cycle time in hours from receiving the draft 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 tied, the number that agree, and the number on Exceptions, the result of every check, and a list of anything you were unsure about.