The "No-Code" Consolidation Engine

How I saved a CFO 7 days a month using the tool he already owned.

Scale: 18 Branches (34 Legal Entities)
Stack: Excel & Power Query (100% Native)

The Challenge

The client manages 18 schools with a complex legal and technical structure:

16 Branches in NetSuite: Split into 32 separate legal entities — holding entities for rent vs. operating entities for daily revenue.
2 Branches in QuickBooks: Recently acquired schools with a completely different Chart of Accounts.
The Blind Spot: Management could not see “True Profit” per school because major costs (rent) and revenue sat in different entities. Monthly consolidation took one full week of fragile, manual Excel work.

The “Before & After” Transformation

Visualizing the shift from manual chaos to automated simplicity.

Before: manual consolidation workflow across fragmented Excel files and legal entities

After: unified Power Query model with one-click refresh

The Strategy: “Smart Simplicity”

I could have built a complex cloud data warehouse, but I strategically chose Excel & Power Query.

Why? The C-Suite wanted full ownership. They did not want to rely on expensive external software or learn new systems. They wanted a tool they already trusted and could control themselves.

The Solution

I engineered a robust backend hidden within a standard Excel file:

Unified Data Model: Connected to all 34 data sources simultaneously and flattened them into a single, standardized format using Power Query.
The “Virtual Merge”: Built logic to automatically join the Holding Entity (Rent) with the Operating Entity (Revenue) to create a single, truthful “Reporting Location.”
Cost Allocation Engine: Implemented logic that proportionately spreads Head Office expenses down to each branch for a fully-loaded P&L view.

The Key Hurdle

The hardest part was integrating the 2 newly acquired QuickBooks branches, as their Chart of Accounts was completely different from NetSuite.

The Fix: I built a user-friendly “Translation Table” in Excel. This allows the Controller to map new account codes in seconds without touching any code, keeping the system flexible for future acquisitions.

The Result

Speed: Month-end consolidation went from 1 week to Real-Time (just click “Refresh”).
Clarity: Management can finally see True EBITDA per branch and group P&Ls by State, Regional Manager, or Brand instantly.
Impact: The CEO, CFO, and Controllers now use this tool as their primary base for all high-level strategic analysis, with zero ongoing software costs.