Hotel Channel Cost of Acquisition Analyzer: net ADR and cost of acquisition by booking channel
Full description
For revenue managers, directors of sales and marketing, general managers and financial controllers who need to know which booking channels earn money after costs. You enter each channel's terms (commission, payment fee, fixed fee per booking, loyalty cost, rate discount to the public rate, cost per cancelled booking) and your monthly production by channel from the PMS: bookings, cancellations, no-shows, room nights, room revenue, fees and marketing spend. An engine prices every production row in visible steps. The summary gives, per channel, the cancellation rate, length of stay, gross and net ADR, cash cost of acquisition per stayed booking and per room night, cost as a % of revenue, net RevPAR contribution and contribution after room costs per night and per stay. A shift model moves a share of eligible intermediary room nights to a direct channel, with the guest paying the public rate less the direct discount, an incremental cost per shifted booking and fixed marketing, and reports the annual profit effect, break-even CPA and break-even shift % for three scenarios and two sensitivity grids.
Save with a bundle: this workbook is also in the Hotel Revenue & Data Analytics Pro Bundle, 25 workbooks for $59 instead of $319. 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_Channel_Cost_of_Acquisition_Analyzer.xlsx: 12 tabs, 6,081 formulas, 5 native charts, 14 integrity checks, a Method tab with 20 calculation notes.
- Channel_Terms: 10 sample channels (5 intermediary, 5 direct) plus 2 spare rows, each with 7 cost terms and a shift flag.
- Production: 120 sample rows (12 months x 10 channels) and 120 spare rows; Production_Calc prices each row in 21 columns.
- Channel_Summary: three tables per channel (volume and ADR, cost of acquisition, contribution after room costs) and a gross vs net ADR chart.
- Monthly_Trend: direct and intermediary room nights, cost %, net ADR and net RevPAR by month, with a combined chart.
- Shift_Model and Shift_Engine: 3 scenarios, a per-channel engine, break-even CPA and shift %, and two 6-row sensitivity grids (shift % x CPA, shift % x rate change).
- Dashboard: 9 KPI tiles, a check tile, hotel totals, a channel scorecard and three charts (cost by element, net ADR by channel, cost per room night), on one page.
- README.txt and LICENSE-and-Terms.txt.
How it works
- Enter rooms, the analysis period and your room cost per night and per stay on Settings.
- Enter each channel's terms on Channel_Terms and mark which intermediary channels you can win direct.
- Paste monthly production by channel on Production.
- Read Channel_Summary and the Dashboard: net ADR, cost per booking and per room night, net RevPAR contribution.
- Test a direct shift on Shift_Model, then confirm the Checks tab shows ALL OK.
Details
- Format: XLSX workbook.
- Language: English.
- Software: Excel 2016 or later, or LibreOffice Calc. No macros, no data tables, no add-ins.
- Works in any currency; the currency label is an input.
- Delivery: instant download.
Good to know
- Sample data is illustrative and describes no real property or contract.
- Not financial or accounting advice. Commission, card fees and taxes on fees vary by contract and country: verify local requirements.
- Channel marketing is treated as fixed in the shift model; incremental marketing for the shift is entered separately.
- Single-user license.
FAQ
What is the difference between gross ADR and net ADR here?
Gross ADR is room revenue divided by room nights. Net ADR adds fee revenue and subtracts the cash cost of acquisition (commission, payment and booking fees, loyalty, marketing and cancellation handling), then divides by room nights.
Why is the rate discount shown separately?
A discount is revenue you never receive, so it is not a cash cost. The workbook shows it as a memo and adds it to cash cost to give an economic cost of acquisition per room night.
Does length of stay matter?
Yes. Fixed fees per booking and the cost per stay are spread over fewer nights when stays are short, so contribution per room night drops even when net ADR looks similar.
How does the shift model price a booking that moves to direct?
It keeps the length of stay, moves the guest to the public rate less the receiving channel's discount (plus any extra incentive you enter), applies the receiving channel's costs and subtracts your cost per shifted booking and fixed marketing.
Can I add channels?
There are 12 channel rows: 10 used and 2 spare. Type the new channel on Channel_Terms and it appears in the drop-down on Production and in every summary.
