Hotel Three-Statement Budget Model: Monthly hotel budget with a linked P&L, balance sheet and cash flow
Full description
For financial controllers, general managers and asset managers who must present a budget the owner and the lender can trust. You enter monthly occupancy, ADR, F&B covers and checks, other revenue, headcount and pay by department, cost ratios, management fees, the FF&E reserve, depreciation, interest, tax, receivable, inventory and payable days, capex, loan repayments and owner distributions, plus the opening balance sheet. The workbook builds a monthly P&L in the uniform system of accounts layout down to net income, rolls the balance sheet forward from it and explains every change in cash with an indirect cash flow statement. A check row proves that assets equal liabilities plus equity in every month. Covenants shows quarterly DSCR with Pass, Watch or Breach, the distribution lock-up, the cash low point and its month, and debt / EBITDA. A scenario switch and a one-case-per-row engine re-run the operating model 28 times for a scenario table and two 5 x 5 grids, with no data tables or macros.
Save with a bundle: this workbook is also in the Hotel Finance & Investment Pro Bundle, 25 workbooks for $59 instead of $329. All 100 Pro workbooks are in the Pro Library for $119, and all 241 templates and workbooks are in the Ultimate Hospitality Library for $149.
What is inside
- Hotel_Three_Statement_Budget_Model.xlsx: 12 tabs, 3,501 formulas, 3 native charts, 15 input rules, 15 integrity checks, a Method tab with 16 calculation notes.
- Sample hotel (illustrative): 220 rooms, 12 months of drivers, 6 payroll departments, a 16-line balance sheet with an opening column and 21 cash flow lines.
- Covenants: quarterly DSCR with cushion and status, lock-up test on distributions, month-end cash test, debt / EBITDA and debt yield.
- Scenarios: Base, Upside and Downside plus NOI and DSCR grids by occupancy and ADR (25 cases each, 28 engine rows).
- Dashboard: 11 KPI tiles, a check tile, revenue and GOP and month-end cash charts, the three statements in one table, printable on one page.
- Hotel_Three_Statement_Budget_Guide.docx and Hotel_Three_Statement_Budget_Guide.pdf: 11-page guide on how the three statements link, the inputs, the method in equations, a worked example on the sample results, audit tests, covenant reading, common mistakes, a sign-off checklist and a glossary.
- README.txt and LICENSE-and-Terms.txt.
How it works
- Enter cost ratios, pay rates, fees, depreciation, interest, tax, working capital days and covenant limits on Assumptions.
- Enter the opening balance sheet from your latest month-end accounts.
- Enter monthly volumes, prices, headcount, capex, repayments and distributions on Drivers.
- Read the P&L, balance sheet and cash flow; the check row must stay at zero.
- Read covenants and the cash low point, test the Downside and confirm the Checks tab shows ALL OK.
Details
- Formats: XLSX workbook, DOCX guide, PDF guide.
- Language: English.
- Software: Excel 2016 or later, or LibreOffice Calc 7 or later. No macros, no data tables, no add-ins.
- One budget year, monthly, in any currency.
- Delivery: instant download.
Good to know
- Sample data is illustrative and does not describe any market. Replace it with your own figures.
- Not accounting, tax, legal or lending advice. Verify local requirements for tax and payroll charges and read your loan agreement for covenant definitions.
- The FF&E reserve is treated as cash set aside, not an expense: it reduces NOI, not net income.
- Single-user license.
FAQ
Does the balance sheet really balance?
Yes, by construction. Every flow has a P&L, cash or balance sheet partner, and a check row shows assets minus liabilities minus equity for each month. It reads zero on the sample and the Checks tab tests it.
Can I use my own chart of accounts?
The P&L follows the uniform system of accounts summary layout. You can rename lines; if you add one, give it a cash flow or balance sheet link so the check row stays at zero.
How is the cash low point found?
The model takes the lowest month-end unrestricted cash and returns its month, then compares it with your minimum cash covenant.
Does it handle a management company and an owner?
Yes. Base and incentive fees come from the management agreement inputs, and distributions to the owner are tested against the DSCR lock-up.
Is there more than one year?
No. It is a 12-month budget. For a multi-year forecast, roll the year-end balance sheet into the opening column of a copy.
