Hotel financial model: rooms, rates and operating costs

A hotel financial model starts with available room nights, occupancy and realized rates, then subtracts the costs needed to serve those guests. Build the budget and actual results on the same monthly basis so a profit change can be traced to rooms, prices or spending. Keep property investment valuation outside this operating comparison.

Start with the room register and the ledger

Use one property and one service month first. Export daily available rooms, paid room nights, room revenue after discounts and refunds, and ancillary revenue. Add department payroll, employer payroll costs, supplies, laundry, utilities and other property expenses from the accounting records.

Document treatment of complimentary stays, rooms out of service and cancellations. A sold room night and a reservation are different units: a reservation spanning several nights contributes several room nights. Assign revenue to the stay dates rather than the booking date, and remove lodging taxes collected for authorities from operating revenue.

Keep the exports behind the summary. The IRS recordkeeping guidance says business records should clearly show income and expenses. For management review, the same principle means that each reported balance should lead back to its source rows.

The controller owns the ledger mapping; the revenue manager owns occupancy and rate assumptions; the general manager confirms the staffing capacity. Record who approved each scenario and when. The broader review process belongs in FP&A analysis; the schedule below handles the hotel operating arithmetic.

Build a monthly schedule with explicit drivers

Consider an illustrative US hotel in June 2026. All money is USD. Column B holds plan, C actual and D a supplied occupancy scenario. Costs are positive amounts that are subtracted. Whole-dollar totals are exact; occupancy is displayed as a percentage and revenue per available room to two decimals.

Enter assumptions only in the input rows. In Excel, enter these formulas in B5, B7, B9, B10, B12, B14, B17, B18 and B19, respectively. Copy each calculated cell across to C and D:

=B2*B3*B4
=B5*B6
=B5*B8
=SUM(B7,B9)
=B5*B11
=B5*B13
=B10-SUM(B12,B14:B16)
=B2*B3
=B7/B18

Microsoft documents SUM for adding ranges and individual values. Covirage is not affiliated with Microsoft.

The defined operating result excludes depreciation, financing, income taxes and capital expenditure. Keep those exclusions visible rather than labeling the subtotal net profit. For distinctions between profit levels, use the separate guide to gross, contribution and net margin.

Reconcile the actual profit change

Actual profit is $38,550 below plan: $92,950 minus $131,500. Separate the occupancy effect from the rate effect using a stated calculation order. First change occupied nights at planned rates and variable costs; next change the rate on actual nights; finally change payroll.

The proof is $131,500 − $24,750 − $7,800 − $6,000 = $92,950. The schedule therefore explains the entire change, with no unexplained residual. A different calculation order can allocate the interaction differently, so keep the same order every month.

Also reconcile room revenue to the room register and total revenue to the ledger. Available nights must equal occupied plus unsold available nights. Payroll should match the approved payroll export, including accrued employer costs. Review duplicate room-date rows before aggregating them; a matching total alone does not prove the underlying records are complete.

Change one assumption, then check capacity

The scenario changes only planned occupancy, from 70% to 75%. It adds 150 occupied room nights. Each contributes $180 + $20 − $25 − $10 = $165 before payroll and fixed expenses, so profit rises by 150 × $165 = $24,750 to $156,250.

That result depends on payroll staying at $120,000. Ask whether housekeeping, front-desk coverage and food service can support the extra nights. If overtime becomes necessary, enter the supplied hours and rates as a separate scenario rather than quietly treating all labor as fixed. The capacity planning reconciliation explains the general capacity check without changing the hotel schedule.

For another hospitality operation, the bar financial model focuses on sales mix and inventory consumption rather than room nights. Use the startup financial model for an opening-stage budget and funding schedule.

Where it goes wrong

• Changing available-room definitions. Removing closed rooms from actual capacity while retaining them in budget changes occupancy without explaining demand.
• Using advertised rates. Discounts, refunds and complimentary stays can make realized revenue differ from the posted rate.
• Counting labor twice. Housekeeping payroll in both per-night cost and loaded payroll understates profit.
• Ignoring seasonal costs. A quiet month's utilities and staffing cannot automatically represent a busy month.
• Reading operating profit as cash. Deposits, receivables, supplier balances and capital spending need their own cash reconciliation.

Compare your hotel files in Covirage

Bring the room register, operating-expense export and workbook containing your approved assumptions. Covirage maps the columns, and its tools compute and check the supplied comparisons; the external AI model explains the results with every figure traced to its rows. Create a free Covirage account and bring a sample of your own data.

See Covirage for hospitality teams for the supporting industry context.