Hotel Financial Model for a Multi-Property Hotel Group: Driver-Based Budget with USALI P&L

In short: A hotel financial model plans a hotel’s revenue and costs from occupancy and room rates to GOP and EBITDA. This one is a free, driver-based budget model for a multi-property hotel group, built in Quantrix. It plans five fictitious hotels in two brands, month by month over three years: occupancy and room rates by room type and booking channel, revenue for rooms, food and beverage, spa and events, and a profit and loss account in the USALI order, from each department up to the hotel, the brand and the group. London reports in pounds, the other hotels in euros, and the group is consolidated in euros. Every number comes from a named formula you can read and change, and the model checks itself. It comes free with the 30-day Quantrix trial.

Key facts

Hotels	5: Lumina London, Amsterdam and Barcelona (Lumina Collection); Maison Vienna and Rome (Maison Prestige)
Room types	Standard, Superior, Deluxe, Suite
Booking channels	Leisure, Corporate, OTA (online travel agencies, with commission)
Departments	Rooms, food and beverage, spa, events
Horizon	3 years, monthly
Currencies	GBP (London) and EUR; group in EUR
Scenarios	Base · Upside · Downside (occupancy, rates and margins together)
P&L	USALI order: departmental contribution → GOP → management fee → EBITDA → EBIT, at hotel, brand and group level
KPIs	Occupancy, ADR, RevPAR, TRevPAR, GOP and EBITDA margin, F&B revenue per cover, channel mix
Base results, year 1, group	Revenue EUR 70.6 m · GOP margin 45.1% · EBITDA EUR 29.8 m (42.1%) · occupancy 76.7%
Built-in checks	Occupancy never above 100%, channel split adds up, profit order, P&L bridge; PASS in all three scenarios
Availability	Free with the 30-day Quantrix trial

What does a hotel group financial model need to do?

A hotel budget lives at a fine level of detail: rooms by type, nights by month, rates by booking channel. Management looks at it at a coarse level: occupancy, ADR, RevPAR, GOP and EBITDA per hotel, per brand and for the group. A good hotel financial model connects both levels with one set of rules, so that a change in a channel’s rate in one hotel shows up correctly in the group P&L. This is driver-based budgeting: you plan the drivers (rooms, occupancy, ADR, spend per guest, cost rates) and the model calculates the money.

In Excel this usually means one workbook per hotel and a consolidation workbook on top, with links between them. Adding a hotel, a room type or a channel means copying sheets and checking links. In this model, hotel, room type, channel, department, month, year and scenario are dimensions of one model. A sixth hotel is one new item plus its data, with no new formulas.

How do you consolidate budgets across several hotels?

Most hotel groups budget each property in its own spreadsheet and then consolidate them by hand: copying totals, converting currencies, checking that every hotel used the same structure. It is slow, and a single broken link can change the group result without anyone noticing.

In this model there is nothing to consolidate by hand. Every hotel uses the same formulas, the brand P&L is the sum of its hotels, the group P&L is the sum of its brands, and London’s pounds are translated into euros on the way. Change one hotel and the brand and group results update at once.

Which hotel KPIs does the model calculate?

KPI	Definition
Occupancy	Room nights sold ÷ room nights available
ADR (average daily rate)	Rooms revenue, net of OTA commission ÷ room nights sold
RevPAR	Rooms revenue, net of OTA commission ÷ room nights available (= occupancy × ADR)
TRevPAR	Total revenue of all departments ÷ room nights available
GOP margin	Gross operating profit ÷ total revenue
EBITDA margin	EBITDA ÷ total revenue
F&B revenue per cover	Food and beverage revenue ÷ covers
Channel mix	Share of occupied room nights by leisure, corporate and OTA

All KPIs are calculated per hotel, year and scenario. For Lumina London in year 1 (Base): occupancy 75.5%, ADR EUR 260, RevPAR EUR 196, TRevPAR EUR 275, GOP margin 44.6%, EBITDA margin 41.6%. Other KPIs, such as GOPPAR, can be added as one more formula.

How does the model calculate occupancy and room nights?

The calculation follows how a hotel actually sells rooms:

1. Room nights available = rooms × days in the month. Hotels do not withdraw rooms in the low season, so capacity does not change with seasonality.
2. Base occupancy = the sum of the channel shares (leisure, corporate, OTA) for each room type.
3. Demand occupancy = base occupancy × the hotel’s seasonality index for the month.
4. Capped occupancy = demand occupancy, but never above 100%. A hotel cannot sell more rooms than it has.
5. Room nights sold = room nights available × capped occupancy, then split by channel.

In Lumina Barcelona, demand for Standard rooms in July is 113% of capacity, so the model sells 100%, 2 015 room nights, and no more. In January the same rooms run at 61%. This is also how the model handles seasonality in a hotel budget: as a monthly demand index per hotel, not as a change in capacity.

How is hotel revenue calculated?

• Rooms: room nights sold × ADR, by room type and channel. OTA commission is deducted as a direct cost of the rooms department.
• Food and beverage: revenue per occupied room night; covers per occupied room give F&B revenue per cover.
• Spa: revenue per available room night.
• Events: a monthly amount per hotel.

What does the USALI P&L look like?

The P&L follows the order of the Uniform System of Accounts for the Lodging Industry (USALI): each department’s revenue less its direct cost gives the departmental contribution. Less the undistributed overheads gives the gross operating profit (GOP). Less the management fee, a percentage of departmental revenue, gives EBITDA, and less depreciation gives EBIT.

The demonstration model keeps the undistributed overheads to three lines: staffing, utilities and maintenance. Further USALI lines, such as administration and general, sales and marketing, IT, rent or insurance, can be added as new items, without changing the structure.

The same lines exist for each hotel, each brand and the group. In year 1 (Base), Lumina Collection earns EBITDA of EUR 17.9 m and Maison Prestige EUR 11.8 m. The group total is EUR 29.8 m.

What happens in a weaker year?

The Downside scenario lowers occupancy and rates. Overheads are fixed amounts, so profit falls faster than revenue: group EBITDA in year 1 drops from EUR 29.8 m in Base to EUR 22.1 m, a fall of about a quarter. This is operating leverage, and it is the reason a hotel budget needs scenarios, not one number.

How does the model check itself?

Four checks run for every hotel, month, year and scenario, and the dashboard shows PASS or REVIEW:

• Occupancy is never above 100% and never below zero
• The split of room nights by channel adds up to the room nights sold
• EBITDA is never above GOP
• EBITDA rebuilt from revenue, direct cost, overheads and fee equals the EBITDA in the P&L

The small values in the validation (2 to 4) are rounding, well inside the tolerance of 10. The checks were tested by breaking the model on purpose, and each break triggered only its own check.

Is the model a black box?

No. Every number comes from a named formula you can open, read and change. The model is yours: you can add a hotel, a room type, a channel or a department, change a driver, or add what your group needs.

Why build a hotel budget in Quantrix rather than Excel?

In Quantrix, hotel, room type, channel, department, month, year, scenario and currency are dimensions of one model, so one formula covers every combination. In Excel, five hotels with four room types, three channels, twelve months, three years and three scenarios means hundreds of copied blocks of the same calculation, and a change in the rule means checking each of them. Here the occupancy rule, the revenue drivers and the P&L order are written once, and the group result is always the sum of the same calculation.

How was this model built?

The model was generated from a plain-language brief with our AI Quantrix model builder, following our modelling rule set, and built in Quantrix by our import scripts. The same method works for your own hotels, brands and chart of accounts, and also from an existing Excel budget, which the builder can use as a reference: it takes over the logic, makes hidden assumptions explicit and adds checks that the spreadsheet did not have.

What does the model not include?

It demonstrates hotel group budgeting; it is not a complete hotel accounting system:

• No balance sheet or cash flow statement
• No recurring capital expenditure (one refurbishment per hotel in year 1)
• No revenue management logic such as dynamic pricing or overbooking
• No labour planning tied to occupancy (staffing is an annual amount per hotel)
• No actuals, rolling forecast or budget-versus-actual variance
• No import from a property management or accounting system (data is entered or loaded from files)
• No loans, interest or tax
• One exchange rate per year (GBP to EUR)

Each of these can be added to the model, because its structure and formulas are open.

Get the model with your free trial

The model is included with the free 30-day Quantrix trial. Request the trial below and we will send you the model with the download link. If you would like it adapted to your own hotels, say so in the message field.

Frequently asked questions