Room and Catering Revenue Pace (BI) (REP_ROOM_CAT_REV_PACE_BI.REP with REP_ROOM_CAT_REV_PACE_BI.FMX)

The Room and Catering Revenue Pace (BI) report displays room and event revenue by Stay Period for the requested Production Period. It also displays 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.

Prerequisites

• This report can only be configured in a property with an active OPERA Sales and Catering or SFA license.
• This report requires that the user be granted the Blocks>BLOCK PRINT permission in the selected property to run the report.
• This report is a BI Publisher report. We do not recommend customizing it.
  • 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

Selection Criteria

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 2003 with a start date in October 2003 turns to DEF in June 2003. 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 2003 and one for the status change to DEF in June 2003. If revenue is added to the DEF business block in July 2003, 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 on which the report will filter events by their start date and the business blocks by allotment date.

To. (Mandatory) Enter an end date range on which the report will filter events by their start date and business blocks by allotment date.

Property. Available when the OPS_MHT2 license is active. Choose the property for which the business blocks and events in this report should be filtered. If no value is selected, all properties listed in the LOV will be considered.

Origin. Select one or more Origins. This LOV lists all of the possible values from the internal field allotment_origion values from the Business Block header. When a value is selected, only events from business blocks with this origin will be considered. If no value is selected, all business blocks will be considered. Possible values are:

ORS (business blocks created in the ORS application)

SFA (business blocks created in the SFA application)

PMS (business blocks created in the PMS application)

SC (business blocks created in the S&C application)

EXT (business blocks created in an External system)

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 one or more status code from a list of all return-to-inventory statuses. The Cancel column will be calculated based on the business blocks and events that turned to this status during the requested production period. All figures will be 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 will be 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 will be 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 will be 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 (Configuration>Cashiering>Rev Type Cross Reference):
  • 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 will be 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 (Configuration>Cashiering>Rev Type Cross Reference):
  • 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 type(s) to be used when calculating Cat Rev in this report. If no value is selected, all Revenue Types will be considered.

Currency. Select the currency to be used for calculating the figures in this report. All figures will be reported in the selected currency. If no value is selected, the report will be 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 will be considered.

Overall Owner. Only business blocks with this Overall Owner will be considered.

Rooms Owner. Only business blocks with this Rooms Owner will be considered.

Catering Owner. Only business blocks with this Catering Owner will be considered.

Exclude from Forecast Event (1 of the 3 options must be selected)

Both. All events will be considered regardless of the Exclude from Forecast check box.

Yes. Only events marked as Exclude from Forecast will be considered.

No. Only events not marked as Exclude from Forecast will be 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.

Report Output

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 will always be 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 2012, Block turns DEF with 100 Rooms

In July 2012, Block drops to 95 Rooms (still DEF)

In August 2012, Block turns to ACT with 97 Rooms

If the report is run for Production Period June – August 2012, 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 will be 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).

An example of the above columns:

Production Dates in the parameter screen: Jan 2013 – Feb 2013

Stay Dates in the parameter screen: Jan 2013 – Dec 2013

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 2013 – Dec 2013.
• ACTLY - Current figures for business blocks currently in an ACT or DEF status with a stay date between Jan 2012 – Dec 2012.
• STLY - Calculated using the following filters:
  • Production Dates: Jan 2012 – Feb 2012
  • Stay Dates: Jan 2012 – Dec 2012
  • 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 will be 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 will be 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.