For Hotel Revenue Managers ·
What you'll need
Create a new Excel workbook. In rows 1–15, set up a labeled input section where you'll enter group quote details each time you use the template:
| Row | Label | Value |
|---|---|---|
| 2 | Group Name | [enter each time] |
| 3 | Arrival Date | [enter each time] |
| 4 | Departure Date | [enter each time] |
| 5 | Number of Rooms | [enter each time] |
| 6 | Number of Nights | [enter each time] |
| 7 | Group Room Rate (ADR) | [enter each time] |
| 8 | Transient Forecast ADR | [enter each time] |
| 9 | Transient Forecast Occupancy % | [enter each time] |
| 10 | Hotel Total Rooms | [your hotel's constant] |
| 11 | F&B Revenue Per Attendee | [enter each time] |
| 12 | Attendees per Room | [enter each time] |
| 13 | OTA Commission Rate | [your hotel's constant] |
| 14 | Direct Booking Premium | [your hotel's constant] |
Format as an Excel Table (Ctrl+T).
Open the Copilot panel and type:
"I'm building a hotel group displacement model. I have input values in rows 2-14 in column B. Please help me build calculation formulas for:
Copilot will generate each formula referencing your input cells. Review them carefully — a typical output:
=B5*B6*B7 (rooms × nights × rate)=B5*B6*B8*B9 (rooms × nights × trans ADR × occupancy%)=B20-B21 (group room revenue minus displaced)=B5*B12*B11 (rooms × attendees per room × F&B per attendee)=B21*B13 (displaced transient × OTA commission rate)=B22+B23+B24 (net room + F&B + commission savings)Ask Copilot: "Add a DECISION cell that says ACCEPT if the total net impact is positive and DECLINE if negative, plus a note explaining the key factor driving the decision."
Copilot generates: =IF(B25>0,"ACCEPT — Group is net positive by $"&TEXT(B25,"$#,##0"),"DECLINE — Displacing $"&TEXT(ABS(B25),"$#,##0")&" net transient revenue")
Ask Copilot: "Create a small sensitivity table showing how the total net impact changes if the group rate varies from $10 below to $30 above the quoted rate, in $5 increments."
Copilot guides you to create a data table using Excel's What-If Analysis feature, or generates the rates manually in a lookup table.