Hotel Cash Management: Operating Account, Reserves & Owner Distribution Model: Hotel Cash Flow Forecast and Reserve Template

Full description

For asset managers, hotel owners, financial controllers and general managers who need one model of the hotel's cash. The workbook forecasts 24 months of collections and payments from seasonal occupancy, rate and cost drivers, holds a working capital minimum in days of operating cost, and rolls three reserves: FF&E, debt service and tax. A five-test sequence then decides whether the owner is paid, and the model adds an owner cash call when the account falls below the minimum. Three scenarios, a cash break-even occupancy grid and a working capital grid show how much room the hotel has. A companion sheet lists each month's payments with source account, approval levels and a weekly position, and a 9-page guide gives the policy, a worked example and two forms.

What is inside

• Hotel_Cash_Management_Model.xlsx: 12 tabs, 2,949 formulas, 3 native charts, 18 drop-down and input rules
• Revenue Build and Cost Build: 24 months with seasonal occupancy, rate index, three revenue streams, four cost groups and payment lags
• Reserves: FF&E reserve with a rate by operating year, debt service reserve with a months-of-payments target, tax reserve with value-added tax and income tax liabilities
• Cash Forecast: the operating account waterfall with minimum balance, owner cash call, status and all account balances; Distribution Test: 5 tests, surplus, distribution and the reason for each deferral
• Stress & Break-even: 3 scenarios for years 1 and 2 in closed form, two 5 x 5 grids (cash break-even occupancy, minimum balance)
• Dashboard with 8 tiles, a check tile, 2 charts, an annual summary and distribution results; 12 integrity checks including two independent cash proofs; Method tab with 17 documented calculations
• Hotel_Cash_Management_Monthly_Cash_Call_Sheet.xlsx: 5 tabs, 457 formulas, 23 sample payments with 5 approval levels, weekly position, aged payables by 11 categories, 8 checks
• Hotel_Cash_Management_Guide.docx and PDF: 9 pages with account roles, working capital method, reserve rules, the distribution sequence, a worked example read from the workbook, a monthly routine, 2 forms, a checklist and a glossary
• README.txt and LICENSE-and-Terms.txt

How it works

1. Enter the drivers, working capital days, reserve rates, tax rules, loan terms and the 12-month profile on Assumptions, then add the capex plan.
2. Read Cash Forecast month by month: status, cash before owner support and the owner cash call.
3. Read Distribution Test for the reason each distribution was paid or deferred, and Stress & Break-even for scenarios and headroom.
4. Each month, list the payments on the cash call sheet, get the approvers, and send the owner the call for the peak weekly shortfall.

Details

• Formats: 2 Excel workbooks (.xlsx), 1 guide in Word (.docx) and PDF, English
• Software: Excel 2016 or later, or LibreOffice Calc 7 or later
• No macros, no external links, no data tables, no array formulas
• 4 files plus README and license; instant digital download

Good to know

• Sample data is illustrative. Replace it with your own figures.
• Tax rules, loan terms, covenant levels, reserve rates and payment months are examples. Verify local requirements and your loan, management and tax documents.
• The model is monthly and uses level loan payments. The files are planning tools, not legal, tax or financial advice.
• Single-user license. Do not redistribute.

FAQ

How is the minimum balance set?

The larger of a floor and working capital days / 30 x the next month's cash operating payments. Days, floor and the owner-support switch are inputs on Assumptions.

What decides whether the owner is paid?

In a distribution month, five tests run in order: debt service reserve at target, tax reserve covers liabilities, FF&E reserve covers planned spend, cover ratio at or above the covenant, and cash above the minimum. The payout is the surplus above the minimum times the share you set.

Can I run a downside case?

Yes. Pick Base, Upside or Downside on Assumptions and the whole model recalculates. Stress & Break-even shows all three side by side for years 1 and 2.

Does it cover a different loan or reserve rule?

Rates, months of cover, tax rates, payment months and the covenant are inputs. The loan is a level-payment loan with a fixed rate.

Do I need macros or add-ins?

No. Every result is a standard worksheet formula.