Case Study · Financial Systems

Multi-Entity Consolidation
across $46.4M in Assets

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.

IndustryAlaska Hospitality & Tourism

Entities5 · Parent + 4 OpCos

Assets$46.4M

History loaded40 months

Source systemsSoftrip · Xero · QBO

Total Assets

$46.4M

BS balance

$0 residual

History loaded

40 months

Formula errors

01 /

The problem

Month-end close had become a liability.

The President was running close on a 33-tab manual Excel workbook. Every month meant hand-copying five trial balances and reconciling intercompany by hand, hoping nothing broke along the way. Nobody fully understood the formula chains. There was no audit trail, and restatement meant hours of manual work.

The portfolio ran on three completely different source systems: Softrip for tour operations, Xero, and QuickBooks Online. Each had its own TB format and period basis, and there was no common chart of accounts across the group.

Before: data flow

Softrip TB╮

Xero TB┼→33-tab manual workbook

QBO (×3) TB╯

Hand-copy · Manual reconcile · No history · Fragile

02 /

The approach

TB-first and append-only, with eliminations stored as auditable data rather than formulas.

After: data flow

Softrip╮

Xero┼→Python normalizer→Fact_Table

QBO (×3)╯

Fact_Eliminations↗

Excel (pure SUMIFS)→40-tab workbook

Append-only · Auditable · Restatable · Mac-clean

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, so there are no volatile functions or external links to break.

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.

03 /

Technical depth

A few things that made this harder than it looks.

Softrip YTD contamination

One tour operator’s export carries unclosed prior-year P&L in retained earnings, so the YTD column is contaminated. Single-month IS is derived from the Change column only; BS uses the Ending column. Handled in the normalizer and documented.

QuickBooks hierarchical account codes

QBO exports colon-separated paths (parent:child:leaf). The normalizer extracts the leaf code and suppresses parent aggregate rows when children are present. Otherwise every line item double-counts.

Mixed period bases across entities

The Xero entity uses a different period basis than the other four. IS comes from the Month columns; BS from the YTD columns. Handled transparently in the normalizer — the downstream workbook never sees the difference.

openpyxl can’t safely touch this workbook

The file uses Dashboard conditional formatting (x14 extensions), a web add-in, and dynamic array tables that openpyxl silently strips on save. All surgical edits go via direct XML manipulation: unzip → edit the target sheet XML → rezip, preserving fullCalcOnLoad and a strict row-ordering invariant.

YTD anchor delta math

QBO and Xero export YTD-cumulative figures. A hidden anchor tab stores the prior YTD after each close. To get single-month IS, the normalizer subtracts prior YTD from current, requiring only the current TB file and never prior-month files.

04 /

The deliverable

One Excel file. Opens on Mac. Forty tabs.

Consolidation

Consolidated IS · BS · Cash Flow, period-aware and driven by one date cell

Entity statements

All five entities — IS, cash flow, and BS history

Operating report

Hospitality LOB dashboard — KPI panel, EBITDA bridge, monthly trend strip

Liquidity & credit

Liquidity and credit metrics

Capital detail

Capital-in-progress detail

Data layer

Fact_Table · Fact_Eliminations · Fact_Adjustments · COA_Mapping, visible and auditable

05 /

Outcome

The close now runs on a documented procedure instead of institutional memory.

The prior process was fragile and undocumented. It 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.

06 /

Stack

PythonExcelQuickBooks OnlineXeroSoftripopenpyxlLibreOfficeXML surgical editingSUMIFS architectureMulti-entity consolidationGAAP

← Start a conversation