Representative anonymous project
Consolidation of income and accounts of several hotels
A practical approach to Excel + Power Query to standardize the presentation, validate data input and create a consolidated management income and account with property and group views - without replacing systems.
Book a Consultation Back to examples
Built for controllers and finance leaders in hotel groups.
Summary
We supported a multi-brand hotel group in transitioning from manual file-by-file consolidation to a repeatable monthly workflow. The result is a single, manageable Excel-based consolidation model that accepts property data, applies standardized account mapping, runs checks and outputs a consistent management package for both property level and group level views.
Fast
- Area: Monthly consolidation of income and expenses
- Subjects: 12 hotels, 3 brands
- Approach: Excel + Power Query
- Focus: Mapping, checks, visibility
Client profile
- Portfolio: 12 hotels across 3 brands
- Reporting Intensity: Monthly P&L Management
- Inputs: Multiple output files and formats (property views, brand templates and custom schedules)
- Requirements checks: Group view + detail properties for exceptions
Why it matters to financial management
When consolidation is slow, delayed or inconsistent, team finance loses time to prepare and has less time to analyze and act. This case study highlights a management-first approach that improves Timeliness, Comparability and Control—while staying within familiar tools.
Task
Operational Pain
- Inconsistent matching of accounts between properties and brands
- Manual copying and pasting to compile group review
- Late submission resulting in delayed management report
Impact on decision making
- Limited property-level visibility data has just been aggregated
- Exceptions are harder to isolate and resolve quickly
- Management packs lack month-to-month consistency
Note. This is a representative anonymous example. Details have been summarized to protect privacy.
Solution
Controlled template-based consolidation workbook designed for repeatability:
- Standardized templates to present properties (clear input and required fields)
- Power Query Load load, merge, and refresh views
- Chart of Accounts Mapping Standardize account structures across properties and brands
- Confirmation checks to identify missing rows, signature issues, and mapping gaps before by consolidation
- Consolidated P&L with both views property and views group for viewing 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 implementation.
A detailed five-step process
Step 1
Define the group's revenue and expense structure
Agree on governance, definitions, and required granularity (brand and property), including how exceptions are handled.
Step 2
Standardize the property presentation template
Create a single presentation format with clear input rules, controlled fields and submission readiness checks.
Step 3
Create a mapping level
Maintain chart of accounts mapping (property account → group account), including flags for unposted or "required to view" items.
Step 4
Check and verify with Power Query
Automate the loading of monthly messages, apply required columns, run checks, and create an exception list for quick follow-up.
Step 5
Publish property and group views
Create consistent results: group consolidation, property comparisons, and a management pack-ready layout with stable row definitions over periods.
Controls and controls
- Submission standards: required fields, file naming convention and truncation schedule
- Confirmation checks: missing lines, unexpected sign changes, duplicate accounts, display gaps
- Exception workflow: single issue list, ownership and resolution status
- Change management: version mapping table and documented string definition updates
- Verification capability: trace from group string to property source with updated timestamps
- Ready by the end of the month: clear green/yellow/red indicators before completion consolidation
Management was designed to support operational discipline without the use of a heavy tool.
Practical process system (no system replacement)
The design intentionally remains within the general financial toolkit to speed implementation and reduce reliance on IT projects.
- Excel for templates, output views and management pack formatting
- Power Query for input, transformations and updateable consolidation logic
- Mapping table supported by finance (with change control)
- Validation level for faster exception review
Typical data supported
- Property income and expense export files (consecutive or different columns)
- Brand reporting graphs
- Manual accrual graphs (if necessary)
Results (illustrative estimates)
Faster consolidation
Monthly consolidation reduced from about 3 working days to about 5 hours (approx).
Less manual preparation
70-80% less manual effort copy/paste and reformatting (approx), freeing up time for review and analysis.
Better exception review
Identify mapping gaps and unusual deviations faster with a consistent monthly management package.
Important: The above figures are illustrative estimates based on typical consolidation efforts under comparable conditions. Actual results depend on data quality, submission discipline, reporting requirements, and team acceptance.
What Was Provided
- Property Representation Template(s) with Clear Input Rules
- Updated Power Query Intake and Consolidation Model
- Mapping Table chart of accounts with revision marks
- Confirmation checks and list of exceptions for further action
- Property-level views for drill-down and comparison
- Derivation of consolidated income and expenses at group level
- Management pack-ready layout for coordinated distribution
- Handover notes covering upgrade steps and management techniques
Lessons learned
Template Discipline Beats Complexity
A simple, compelling representation format greatly reduces post-conversion work.
Mapping is a living asset
Treat the mapping table as a managed master data with ownership and version control.
Exceptions can be reviewed as counted minutes
A single exception list helps teams focus on changes rather than rebuilding the package.
Want a faster, more controlled monthly consolidation—without replacing your system?
We can review your current submission files and reporting lines, then propose a practical consolidation workflow with clear controls.