Multi-Entity Consolidation
for a $46.4M Portfolio
Five Alaska hospitality companies. Three source systems. A 33-tab manual workbook one bad paste away from unrecoverable. Rebuilt from source into an auditable, restatable monthly-close system a non-technical president can trust.
The problem
Month-end close as a liability.
The President was running close on a 33-tab manual Excel workbook. Every month: hand-copy five trial balances, reconcile intercompany by hand, hope nothing broke. Formula chains nobody fully understood. No audit trail. Restatement meant hours of manual work.
Three completely different source systems — Softrip for tour operations, Xero, and QuickBooks Online — each with different TB formats, different period bases, and no common chart of accounts across the portfolio.
The approach
TB-first, append-only. Eliminations as auditable data, not formulas.
Rather than fix the existing workbook, I rebuilt from source. A Python normalizer ingests all five TB formats, resolves to single-month basis, classifies every account against a 43-category common COA, and writes a flat Fact_Table. The Excel workbook reads it with pure SUMIFS — no lookups that break, no volatile functions, no external links.
The workbook is append-only. Each month adds rows and updates one date cell. Every statement and chart re-dates automatically. Prior periods are never touched.
Eliminations as data. Intercompany entries are stored as Fact_Eliminations in journal-entry form, period-stamped. Adding a new period's eliminations is an append — not a formula edit.
Technical depth
A few things that made this harder than it looks.
parent:child:leaf). The normalizer extracts the leaf code and suppresses parent aggregate rows when children are present — otherwise every line item double-counts.fullCalcOnLoad and a strict row-ordering invariant.The deliverable
One Excel file. Opens on Mac. Forty tabs.
Outcome
The prior process — fragile, undocumented, one bad paste away from unrecoverable — has been replaced with a system that is auditable, restatable across 40 months of history, and maintainable by a non-technical operator.
Monthly close is now a defined, repeatable process: drop five TB files, run the normalizer, verify the integrity guard, bump one cell. The entire workbook re-dates.