Representative, anonymized project
Multi-Hotel P&L Consolidation
A practical Excel + Power Query approach to standardize submissions, validate inputs, and produce a consolidated management P&L with property and group views—without replacing your systems.
Book a consultation Back to case studies
Built for hotel group CFOs, finance directors, and controllers.
Executive summary
We supported a multi-brand hotel group in moving from manual, file-by-file consolidation to a repeatable monthly workflow. The result was a single, controlled Excel-based consolidation model that ingests property submissions, applies standardized account mapping, runs validation checks, and outputs a consistent management pack for both property-level and group-level review.
At a glance
- Scope: Monthly P&L consolidation
- Entities: 12 hotels, 3 brands
- Approach: Excel + Power Query
- Focus: Mapping, validations, visibility
Client profile
- Portfolio: 12 hotels across 3 brands
- Reporting cadence: Monthly management P&L
- Inputs: Multiple source files and formats (property submissions, brand templates, and ad-hoc schedules)
- Review needs: Group view + property drill-down for exceptions
Why this mattered to finance leadership
When consolidation is slow, late, or inconsistent, finance teams lose time on preparation and have less time for analysis and action. This case study highlights a governance-first approach that improves timeliness, comparability, and control—while staying inside familiar tools.
The challenge
Operational pain
- Inconsistent account mapping across properties and brands
- Manual copy/paste to assemble a group view
- Late submissions that delayed management reporting
Decision-making impact
- Limited property-level visibility once data was rolled up
- Harder to isolate and resolve exceptions quickly
- Management packs lacked consistency month-to-month
Note: This is a representative, anonymized example. Details have been generalized to protect confidentiality.
The solution
A controlled, template-driven consolidation workbook designed for repeatability:
- Standardized templates for property submissions (clear inputs and required fields)
- Power Query ingestion to load, combine, and refresh submissions
- Chart-of-accounts mapping table to normalize account structures across properties/brands
- Validation checks to catch missing lines, sign issues, and mapping gaps before consolidation
- Consolidated P&L with both property views and group views for review and reporting
No system replacement
This approach focuses on improving the reporting workflow using tools already familiar to finance teams—reducing change management risk and accelerating adoption.
A detailed five-step process
Step 1
Define the group P&L structure
Agree the management lines, definitions, and required granularity (brand and property), including how exceptions are handled.
Step 2
Standardize the property submission template
Create one submission format with clear input rules, controlled fields, and submission readiness checks.
Step 3
Build the mapping layer
Maintain a chart-of-accounts mapping table (property account → group account), including flags for unmapped or “review required” items.
Step 4
Ingest and validate with Power Query
Automate loading of monthly submissions, enforce required columns, run validation checks, and produce an exception list for quick follow-up.
Step 5
Publish property and group views
Generate consistent outputs: group consolidation, property comparison, and a management-pack-ready layout with stable line definitions across periods.
Controls and governance
- Submission standards: required fields, file naming convention, and a cut-off schedule
- Validation checks: missing lines, unexpected sign flips, duplicate accounts, mapping gaps
- Exception workflow: a single list of issues, ownership, and resolution status
- Change control: versioned mapping table and documented line definition updates
- Auditability: trace from group line to property source with refresh timestamps
- Month-end readiness: clear “green/amber/red” indicators before consolidation is finalized
Governance was designed to support operational discipline without introducing heavy tooling.
Practical technology stack (no system replacement)
The build deliberately stays within common finance tooling to speed adoption and reduce dependency on IT projects.
- Excel for templates, output views, and management pack formatting
- Power Query for ingestion, transformations, and refreshable consolidation logic
- Mapping table maintained by finance (with change control)
- Validation layer to drive faster exception review
Typical inputs supported
- Property P&L export files (consistent or varying columns)
- Brand reporting schedules
- Manual accrual schedules (where needed)
Outcomes (illustrative estimates)
Faster consolidation
Monthly consolidation reduced from about 3 working days to about 5 hours (estimated).
Less manual preparation
70–80% less manual copy/paste and reformatting (estimated), freeing time for review and analysis.
Better exception review
Faster identification of mapping gaps and unusual variances, with a consistent management pack month-to-month.
Important: Figures above are illustrative estimates based on typical consolidation effort under comparable conditions. Actual results vary by data quality, submission discipline, reporting requirements, and team adoption.
What was delivered
- Property submission template(s) with clear input rules
- Refreshable Power Query ingestion and consolidation model
- Chart-of-accounts mapping table with review flags
- Validation checks and an exception list for follow-up
- Property-level views for drill-down and comparisons
- Group-level consolidated P&L output
- Management pack-ready layout for consistent distribution
- Handover notes covering refresh steps and governance practices
Lessons learned
Template discipline beats complexity
A simple, enforced submission format reduces downstream transformation work dramatically.
Mapping is a living asset
Treat the mapping table as governed master data with ownership and versioning.
Exceptions should be reviewable in minutes
A single exception list helps teams focus on what changed, not on rebuilding the pack.
Want a faster, more controlled monthly consolidation—without a system replacement?
We can review your current submission files and reporting lines, then propose a practical consolidation workflow with clear controls.