Fund expense allocation review

You are helping a fund controller or compliance analyst review the expenses booked for [period] across [manager, funds, parallel and feeder vehicles, co-investment vehicles]. A human reviewer checks everything you produce. You do not decide whether a cost is permitted under an agreement, you do not post entries and you do not approve payments. You show where the booked split and the expected split differ, and why.

I attach:
- Ledger: [the expense ledger or payables register for the period, for every entity, including the management company]
- Invoices: [the invoices and any engagement letters behind the ledger lines]
- Terms: [the expense and organisational expense clauses of each vehicle's LPA or prospectus, including how any excess over a cap is treated, and any side-letter expense terms]
- Policy: [the allocation policy in force for the period, with its approval date]
- Participation: [the commitments and invested capital of each vehicle, the amounts for any other allocation basis the policy names, and, for each deal, the share each vehicle took or was offered, including co-investment vehicles]

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 invoice, a vendor, a deal and an entity; 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
Quote, for each vehicle, the clauses that say which expenses it bears, which the manager bears, any cap (for example on organisational expenses) and how the LPA treats an excess over that cap. Quote the allocation policy's basis for each kind of shared cost: commitments, invested capital, deal participation or another basis it names. Say how each ledger line was split between entities, according to the ledger, and whether the split matches a basis in the policy.
Label each statement "stated in the file" or "my inference".

STEP 3. MATCH AND RECONCILE
3a. Keys. List the identifiers in the ledger and on the invoices: ledger line ID (journal number and line number), invoice number, invoice line number, vendor code, deal or project code, entity code. Say which appear in both and how complete each is.
3b. Names. Where no shared code exists, describe how the invoices name the deal, the vehicle and the vendor, and how the ledger names them.
3c. Standardise. Write numbered rules for vendor, deal and entity names and apply them in new columns, keeping the original text.
3d. Map. Build a mapping table: each invoice line to its ledger line IDs, each deal name to its deal code and each entity name to its entity code, 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 policy version in force, and the steps to refresh the workbook next period.
- Inputs: period, base currency, FX rates with source and date, the allocation policy version and approval date, the list of expense categories with each category's treatment for each vehicle as its LPA or prospectus sets it ("fund expense", "manager expense", "shared, allocate" or "needs legal reading"), the allocation basis for each shared category as the policy sets it, each vehicle's commitments, invested capital (in total and per deal, as at the date the policy sets), deal participation percentages and the amounts for any other basis the policy names, with their source, the list of the manager and its affiliates as the manager's records name them, for each deal the vehicles that took part in it, were offered it or are excluded from it under the policy, each cap as the LPA sets it, with the treatment of any excess as the LPA sets it, the opening cumulative charges per vehicle and capped category at the start of the period with their source, and the rounding tolerance per entity; 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 invoice, deal and entity mapping table from step 3d, with a column for the person who confirmed each suggested match and the date.
- Terms: each expense and cap clause for each vehicle, with the treatment of any excess, and each allocation rule in the policy, quoted and cited with page. Each row carries its source document and the page, cell or row reference for each figure.
- Invoices: one row per invoice line, as read, with an invoice line ID (invoice number and line number), the ledger line IDs it is booked to (through the Map tab), vendor, date, description, amount, deal, page reference, the proposed category, the clause on the Terms tab that supports it, the reason, an affiliate flag (a lookup of the vendor to the affiliate list on the Inputs tab), and whether the category is "stated in the file" or "my inference". A ledger expense line with no invoice gets its own row, with its ledger line ID, an invoice line ID of "no invoice" followed by the ledger line ID, and the ledger amount, vendor, date and description, and goes to Exceptions as "unmapped line". Each row carries its source document and the page, cell or row reference for each figure.
- Allocation: one row per invoice line and entity. The expected share (the line amount times the entity's percentage under the allocation basis for that category), the booked share from the ledger, the difference and the status are formulas. Status reads "Agrees" within the rounding tolerance, otherwise "Check".
- Caps: one row per capped category and vehicle. The cap and the opening cumulative charges from the Inputs tab, the charges this period from the Allocation tab, the cumulative amount charged to date, the headroom and the status as formulas.
- Exceptions: a formula-driven list of every "Check", every inferred or "needs legal reading" category, every unmapped line, every possible duplicate, every invoice received but not in the ledger and every excess over a cap, with a type column ("Check", "inferred category", "needs legal reading", "unmapped line", "possible duplicate", "not booked", "excess over cap", "untraced or disagrees", "query", "failed control" or "timing difference"), a column for the person's decision ("confirmed duplicate" or "cleared, separate lines"), the name of the person who confirmed a category and the date, the correction made with its note, and for an excess over a cap the treatment the LPA sets for it, quoted from the Terms tab. 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 ledger line ID for an expense line in the period, for every entity including the management company, appears on the Invoices tab, against an invoice line or on its own "no invoice" row, and every invoice received is either booked or listed on Exceptions as "not booked". Target: 0 unexplained rows.
b. Totals tie: on the Allocation tab, for every invoice line, the booked shares across all entities, including the management company, add up to the invoice amount on the Invoices tab, and the booked shares by entity total the ledger by entity. 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: for every invoice line, the amount, vendor, date and description on the Invoices tab match the invoice, with the page cited, and every term on the Terms tab quotes its clause. 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 an invoice that disagrees with its engagement letter, 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. Category: every invoice line carries exactly one category from the list of categories on the Inputs tab, with the clause that supports it. Every category marked "my inference" or "needs legal reading" is on Exceptions until a named person confirms it.
h. Allocation: on the Allocation tab, each entity's booked share equals its expected share under the allocation basis on the Inputs tab, within the rounding tolerance on the Inputs tab.
i. Co-investment and parallel vehicles: for every deal cost, including broken-deal costs, each vehicle that took part in or was offered the deal carries the share the policy sets, and a vehicle the policy excludes carries none.
j. Manager expenses: no line whose category is a manager expense for that vehicle under its LPA or prospectus is charged to the vehicle. Any charge from the manager or an affiliate to a fund is flagged and tied to the clause that permits it.
k. Caps: on the Caps tab, no vehicle's cumulative charges in a capped category exceed the cap, and any excess appears on Exceptions with the treatment the LPA sets for it (borne by the manager, offset or deferred), quoted from the Terms tab.
l. Duplicates: no invoice line ID appears twice. Lines that share vendor, amount, date and description, on one invoice or across invoices, are flagged on Exceptions as possible duplicates for a person to confirm or clear. The control passes when every flagged pair is marked "confirmed duplicate" (and corrected) or "cleared, separate lines". Target: 0 flagged pairs without a decision.
m. Open exceptions: count the Exceptions rows that are not closed, leaving out only named timing differences with an expected clearing date. Target: 0.
n. 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 vendor, deal and entity 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, category 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 misallocations or corrections; share of known differences found; target 100%, and do not adopt the workbook below that); lines agreed first time with no manual fix; lines flagged that turned out fine; categories the reviewer overturned 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 invoice lines reviewed, the number whose booked split agrees with the policy, the number on Exceptions and the total amount at issue by entity, the result of every check, and a list of anything you were unsure about.