Prerequisites for Room and Catering Revenue Pace (BI)
The Room and Catering Revenue Pace (BI) report displays room and event revenue by Stay Period for the requested Production Period. It also displays the On the Books forecast revenue for the same Stay Period with a comparison to OBI Budgeted Revenue, Same Time Last Year (STLY), and Actual Last Year (ACTLY) figures.
Selection Criteria
Report Output
This report is a BI Publisher report. Customizing the report is not recommended. The following are the report elements:
Report Name = Room and Catering Revenue Pace (BI)
File Name = rep_room_cat_rev_pace_bi.rtf
Form to run = rep_room_cat_rev_pace_bi.fmx
Data Template = rep_room_cat_rev_pace_bi.odt
Production Period Section
Each change to Room and Event Revenue or Status is recorded in production change logs. When dates are entered in the Production From and To Date fields, those dates are used as filters when querying the logs. To illustrate, a business block that is created on status TEN in April 2018 with a start date in October 2018 turns to DEF in June 2018. In this scenario there would be two entries in the status change log for this business block: one for the creation of the business block on status TEN in April 2018 and one for the status change to DEF in June 2018. If revenue is added to the DEF business block in July 2018, another change log entry will be written to record that revenue. Because they filter on a date when something has already happened in OPERA, the dates entered here should be for today’s date or earlier. Separate change log entries are written for room and event status and revenue changes.
For STLY calculations, the report displays the Production Date From as null and the Production Date To as the Production Date entered here, minus one year.
From. (Mandatory). Enter the calendar date to indicate the beginning of the Production Period for which the report is to be filtered. Room Revenue is filtered on the business block change date and Catering Revenue is filtered on the event change date.
To. (Mandatory). Enter the calendar date to indicate the end of the Production Period for which the report is to be filtered.
Stay Dates These dates define the stay periods that display in the report. The calculations for STLY are affected by the Same Time Last Year/Fixed Stay Dates check box. When the Fixed Stay Date check box is selected, the Stay Dates in STLY are the same as those entered here. When the check box is not selected, the Stay Dates in STLY are the Stay Dates entered, minus one year.
From. (Mandatory). Enter a start date range when the report will filter events by their start date and the business blocks by allotment date.
To. (Mandatory). Enter an end date range when the report will filter events by their start date and business blocks by allotment date.
Property. Select the property for which the report should be printed. When you are logged in at the hub level, the property LOV is multi select, which allows you to run the report for multiple properties.
Origin. Select Origins. This LOV lists all of the possible values from the internal field allotment_origin values from the Business Block header. When a value is selected, only events from business blocks with this origin are considered. If no value is selected, all business blocks are considered. Possible values are based on the user who created the block and the Act As and At values set on the user in Role Manager.
If the Act As value = Conference Sales Person, and the At value = Property level, then the block is flagged as Sales and Catering.
If the Act As value = Reservation Sales Person, and the At value = Central, then the block is flagged as ORS.
If the Act As value = External System, then At value = not required, and the block is flagged as EXT.
If the Act As value = Reservation Sales Person, and the At value = Property, then the block is flagged as PMS.
If the Act As value = Conference Sales Person, and the At value = Central level, then the block is flagged as SFA.
Block:
New Definite. (Mandatory). Select one status code from a list of all status codes that can turn to an Actual status. The New Definite column will be calculated based on the business blocks and events that turned to this status during the requested production period.
Incl. Actual. (Enabled when a Status is selected in the New Definite field). Select this check box to include Actual business blocks and events with the figures in the Definite On the Books section totals.
Cancel. (Mandatory). Select status codes from a list of all return-to-inventory statuses. The Cancel column are calculated based on the business blocks and events that turned to this status during the requested production period. All figures are reported as a negative.
OTB Status 2. (Mandatory). Select one or more status codes from a list of all status codes. The 1st column in the last On the Books section are calculated based on the business blocks and events currently in this status for the stay period.
OTB Status 3. (Mandatory). Select one of more status codes from a list of all status codes. The 2nd column in the last On the Books section are calculated based on the business blocks and events currently in this status for the stay period.
Revenue – One of the options must be selected: Net or Gross. The Incl Packages check box can be selected if either Net or Gross is selected.
Net. When selected, Room and Catering Revenue are calculated as:
Net Room Rate is calculated as ROOM ONLY.
Included generates are deducted
Excluded generates are not added
Included packages and their generates are excluded
If Incl. Packages is selected, then Included & Excluded PMS Packages are included but their Included & Excluded Generates are not included.
Net Catering is calculated based on the Transaction Codes mapped in Revenue Type Cross Reference configuration (Administration> Financial> Transaction Management> Revenue Type Mapping):
Included generates are deducted.
Excluded generates are not added.
The Incl. Packages check box has no influence on the Catering Revenue calculation.
Gross. When selected, Room and Catering Revenue are calculated as:
Gross Room Rate is calculated as:
Included generates are not deducted.
Excluded generates are added.
Excluded packages are not added.
Included Packages and their generates are excluded.
If Incl. Packages is selected, then Included and Excluded PMS Packages are included and so are their Included & Excluded Generates.
Gross Catering Price is calculated based on the Transaction Codes mapped in Revenue Type Cross Reference configuration (Administration> Financial> Transaction Management> Revenue Type Mapping):
Included generates are not deducted.
Excluded generates are added.
The Incl. Packages check box has no influence on the Catering Revenue calculation.
Note:
Net and Gross calculations are only done for On the Books and ACTLY calculations. All other revenue is reported as seen in OPERA.Cat Rev. This LOV lists all active revenue types. Select the revenue types to be used when calculating Cat Rev in this report. If no value is selected, all Revenue Types are considered.
Currency. Select the currency to be used for calculating the figures in this report. All figures are reported in the selected currency. If no value is selected, the report are calculated using the Base Currency of the logged in Property.
Owner. All 3 filters are multi-select. Select one or more Owners. If no value is selected, then all Owners listed in the LOV are considered.
Overall Owner. Only business blocks with this Overall Owner are considered.
Rooms Owner. Only business blocks with this Rooms Owner are considered.
Catering Owner. Only business blocks with this Catering Owner are considered.
Exclude from Forecast Event (1 of the 3 options must be selected).
Both. All events are considered regardless of the Exclude from Forecast check box.
Yes. Only events marked as Exclude from Forecast are considered.
No. Only events not marked as Exclude from Forecast are considered.
Block Type (1 of the 3 options must be selected).
All. Select if all business blocks should be considered. When selected, the Combine check box appears. See description below.
Catering. Select if only business blocks marked as Catering Only will be considered.
Rooms. Select if only business blocks not marked as Catering Only will be considered.
Combine. This check box is only visible when the Block Type All is selected. When Combine is selected, room revenue and catering revenue figures are combined. When unselected, room revenue and catering revenue figures are separated by block type:
The Rm Rev - Rms (Room Revenue from business blocks where the Catering Only check box is not selected) and Rm Rev - Cat (Room Revenue from business blocks where the Catering Only check box is selected) figures are combined. The report label reads Room Rev and includes: Production, On the Books, Pace and On the Books). The Revenue, Budget and Variance figures are also combined.
The Cat Rev - Rms (Event Revenue from business blocks where All or Rooms is selected) and Cat Rev - Cat (Event Revenue from business blocks where Catering is selected) figures are combined. The report label reads Cat Rev and includes: Production, On the Books, Pace and On the Books). The Revenue, Budget and Variance figures are also combined.
Same Time Last Year. Select how you want the calculations made for STLY.
When the Fixed Stay Date check box is selected, the Stay Dates in STLY are the same as those entered in Stay Dates To/From fields. When the check box is not selected, the Stay Dates in STLY are the dates entered in the Stay Dates To/From fields, minus one year.
This is a Rooms and Catering revenue pace report that is sorted by Property and Year and grouped by Calendar Period.
Row Headers
Rm Rev-Rms – Room Revenue from business blocks where All or Rooms is selected in the report parameter Block Type.
Rm Rev-Cat – Room Revenue from business blocks where Catering is selected in the report parameter Block Type.
Cat Rev-Rms – Event Revenue from business blocks where All or Rooms is selected in the report parameter Block Type.
Cat Rev-Cat – Event Revenue from business blocks where Catering is selected in the report parameter Block Type.
Production Period Section
Figures in these columns are filtered on the Production Period and Stay Dates.
New DEF – Calculated from business blocks and events that turned Definite during the Production Period. Definite is determined by the New Definite filter in the report parameter screen.
Cancel – Calculated from business blocks and events that turned to Cancel during the Production Period. These numbers are always reported as a negative. Cancel is determined by the Cancel filter in the report parameter screen.
Reval – (Revaluations) Calculated from business blocks and events that were already Definite when the Production Period began and had a change during the Production Period while still in Definite. Definite is determined by the New Definite filter in the report parameter screen.
Slippage – This is calculated from business blocks and events that were already Actual when the Production Period began and had a change during the Production Period.
An example of the above columns:
In June 2019, Block turns DEF with 100 Rooms.
In July 2019, Block drops to 95 Rooms (still DEF).
In August 2019, Block turns to ACT with 97 Rooms.
If the report is run for Production Period June – August 2018, the results are:
NEW DEF +100 rooms
Reval -5 rooms
Slippage +2 rooms
Catering Slippage is calculated for each event as Billed Revenue – Production (excluding ACT Production from the requested production period).
Net Change - Net Change = ( New Definite + Cancel + Reval + Slippage).
Note:
The Production Period figures are calculated from the following tables:Room Revenue - Status change dates are taken from booking_status_changes. Room Revenue is calculated from allotment_changes. If the business block is in an Actual status, the Revenue is calculated from reservation_stat_daily.
Catering Production – Status change dates are taken from event$status_changes. Revenue is calculated from event$changes. If the event is in an Actual status, the Revenue is calculated from the billed revenue in gem$event_revenue.
1st On the Books Section
1st Column – The column header is updated with the Status Code selected in the New Definite filter in the report parameter screen and the Actual Status Code, if Incl. Actual is selected in the report parameter screen. The data is the revenue for business blocks and events currently in the Definite or Actual Status for that Stay Period.
Budget – Budget for the Stay Period as entered in the OBI Budget Forecast module.
STLY – (Same Time Last Year) Revenue for the Production and Stay Dates minus 1 Year. For Business Blocks with a last status of Actual, the room revenue figures are calculated from reservation_stat_daily. For Events with a last status of Actual, the catering revenue figures are calculated from billed revenue.
ACTLY – (Actual Last Year) Picked-up Room Revenue and Billed Event Revenue for Actual business blocks and events for the Stay Dates minus 1 Year (Production Period is not considered).
Production Dates in the parameter screen: Jan 2019 – Feb 2019
Stay Dates in the parameter screen: Jan 2019 – Dec 2019
New DEF in the parameter screen: DEF Incl Actual is checked
On the Books columns calculations as follows:
ACT/DEF - Current figures for business blocks currently in an ACT or DEF status with a stay date between Jan 2019 – Dec 2019.
ACTLY - Current figures for business blocks currently in an ACT or DEF status with a stay date between Jan 2018 – Dec 2018.
STLY - Calculated using the following filters:
Production Dates: Jan 2018 – Feb 2018
Stay Dates: Jan 2013 – Dec 2018
Status: business blocks with a LAST status of ACT or DEF during that period.
Pace Section:
Var to Budget Revenue = ACT/DEF – Budget
Var to Budget % = (ACT/DEF ÷ Budget) × 100
Var to STLY = ACT/DEF – STLY
Var to STLY % = (ACT/DEF ÷ STLY) × 100
Var to ACTLY = ACT/DEF – ACTLY
Var to ACTLY % = (ACT/DEF ÷ ACTLY) × 100
2nd On the Books Section:
This section calculates Revenue for business blocks and events currently in the OTB Statuses selected in the report parameter screen.
1st Column – The column header is updated with the Status Code selected in the OTB Status 2 filter in the report parameter screen. The data is the revenue for business blocks and events currently in that Status.
2nd Column – The column header is updated with the Status Code selected in the OTB Status 3 filter in the report parameter screen. The data is the revenue for business blocks and events currently in that Status.