Automating monthly portfolio reporting with reconciliation and approvals

Define the submission contract before connecting systems

For every portfolio company, record the source owner, reporting period, expected arrival, schema, units, currency, and version. A controlled spreadsheet can be a valid starting point for a smaller business. The critical distinction is whether the same contract is followed each month and whether changes are detected.

Use company and period identifiers inside the data rather than inferring them only from a filename. Retain the original submission and its received timestamp. When a controller sends a corrected file, create a new source version and identify what it replaces. Importing both versions as additional activity is a common source of double counting.

Check the source, mapping, and output separately

A file can be complete yet mapped incorrectly, and a correct mapping can still feed the wrong report filter. Check row counts and business keys on ingestion; account coverage and signs after mapping; and approved totals after calculation. Material differences should stop release. Smaller differences still need an explicit policy and an owner.

Worked example: a sign convention creates a false variance

A fictional company submits revenue of $12 million. Its source ledger stores credit revenue as negative, but the reporting template expects positive values. An initial model imports −$12 million and compares it with a positive $11.5 million budget, producing a meaningless −$23.5 million variance.

The fix belongs in an approved sign mapping tied to the source account convention. After normalization, revenue is $12 million, the absolute variance is $0.5 million, and the percentage variance is approximately 4.35%. Reconcile the normalized total to the source evidence and keep the transformation visible. Do not solve the issue with an absolute-value function across every account; genuine reversals and refunds can legitimately change signs.

Show freshness and release readiness beside performance

A margin result from an old source should not look equivalent to a freshly reconciled number. Display the data-as-of date, latest source receipt, current source version, and unresolved exceptions. Define freshness relative to an explicit expected arrival or as-of date; a historical demonstration should not become permanently stale just because today's date changes.

Keep late-company coverage visible in portfolio totals. A rollup of five out of six companies is a partial view, even if its arithmetic is correct. The public demo keeps exceptions in the working view and labels it as unapproved. Production release policies can exclude or block unapproved data, but must make that choice visible.

Prove the handoff with a parallel close

Reproduce one prior approved cycle, then run a new cycle in parallel. Ask a second person to follow the instructions without the original builder. Test a late file, a corrected file, an unmapped account, a duplicate submission, and a failed refresh. Record the expected owner response for each.

Use Reporting and BI Rescue when the existing pack is worth retaining but the data path is fragile. A new dashboard is not automatically required: the first useful outcome may be a reproducible Excel pack, a controlled model, and a shorter list of unresolved questions.

• Agree the pilot period and approval owner.
• Record actual preparation time before and after the pilot; do not estimate savings as a result.
• Retain source and calculation versions for the approved run.
• Confirm that failures notify the owner and do not publish silently.

Related services and experience

• Explore the portfolio operations demo (Synthetic interactive example)
• Portfolio reporting automation (Implementation service)
• Private equity portfolio analytics (Industry overview)