Month End Details Report (BI) (rep_month_end_details_bi with rep_month_end_details_bi)

Prerequisites for Month End Details Report

Report Name: Month End Details (REP_MONTH_END_DETAILS_BI with REP_MONTH_END_DETAILS_BI.fmx):  The Month End Details report details business block and sales and event production changes during the dates requested. The report can be grouped by Stay Month or Owner and has many filtering options including business blocks with the Catering Only check box = Y or the Catering Only check box = N.

Selection Criteria

Production Date Range

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 2020 with a start date in October 2020 turns to DEF in June 2020. 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 2020 and one for the status change to DEF in June 2020. If revenue is added to the DEF business block in July 2020, another change log entry will be written to record that revenue. Because they filter on a date when something has already happened in OPERA Cloud, 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.

• From Date. 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 Date. (Mandatory) Enter the calendar date to indicate the end of the Production Period for which the report is to be filtered.

1. Stay Date Range: These are the calendar periods that will be displayed in the report.

   • From Date. (Mandatory) Enter a start date range when the report will filter events by their start date and the business blocks by allotment date.

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

2. Property: Select the property for which the events should be filtered.

3. Block Origin: Select Origins. This lists all of the possible values from the internal field allotment_origion. When an Origin is selected, only events from business blocks with this Origin will be considered. If no Origin is selected, all business blocks will be considered. Possible options 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)

4. Block & Events:

   1. Definite: (Mandatory) Select one status code from the list of all status codes that can turn to an Actual status.

   2. Incl Actual: (Enabled when a Status is selected in the Definite field) Select this check box to include business blocks and events in an Actual status with the status selected in the Definite LOV.

   3. Status: Select Status Codes from the list except the ACTUAL status and the status selected in the Definite LOV.

   4. Business Block: Select specific business blocks from the Business Block Search screen for which to print the report. If none are selected, the report will print all business blocks that meet the other criteria on the report parameter screen.

   5. Market Segments: Select market segments by which the business blocks should be filtered. If none are selected, business blocks will not be filtered by market segments.

   6. Covers: Select Event Types to indicate which will be included in the Covers calculation in the reports. If no value is selected, all event types will be considered.

   7. Block Type (1 of the 3 options must be selected):

      • All: Select if all business blocks will be considered.

      • 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.

5. Print Options:

   1. Revenue: One of the options must be selected, Net or Gross. The Incl Packages and/or Print Grand Total check box can be selected if either Net or Gross is selected. The Incl Packages option is described with Net and Gross examples below.

      The Print Grand Total check box, when selected, will print a final summary page which includes the Totals for each subtotal (Changes and New) and a Grand Total. If not selected, no summary page will print.

   2. 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 and Excluded PMS Packages are included, but their Included and 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.

   3. Gross: When selected, Room and Catering Revenue will be calculated, 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 and 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 the Current figures. Change revenue is reported as seen in OPERA.

   4. Print Grand Total: If selected, a final summary page will print which includes the Totals for each subtotal (Changes & New) and a Grand Total.

   5. FB Revenue: Select active Revenue Types to include in the F&B Revenue calculations in the report. If none are selected, no F&B Revenue calculation will be done.

   6. Other Revenue: Select active Revenue Types to include in the Other Revenue calculations in the report. If none are selected, no Other Revenue will be calculated.

   7. Currency: Select the one currency for which all revenue figures will be reported. If no value is selected, the report will be calculated using the Base Currency of the logged in Property.

   8. Owner: (Select Owners. If no value is selected, then all Owners listed in the LOV will be considered.)

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

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

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

   9. Exclude from Forecast: (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.

   10. Group By : (1 of the 4 options must be selected)

       • Stay Month. If selected, the report will be grouped by business block stay month. For room nights and room revenue, the business blocks will be grouped based on which night the room is reserved. For event revenue, the business blocks will be grouped based on the event start date.

       • Overall Owner: If selected, the report will be grouped by the Business Block Overall Owner.

       • Rooms Owner: If selected, the report will be grouped by the Business Block Rooms Owner.

       • Catering Owner: If selected, the report will be grouped by the Business Block Catering Owner.

Report Data

1. Business Block Owner

   The 1st grouping may be by Owner (Rooms Owner, Catering Owner, or Overall Owner) as selected as a Group By in the report parameter screen.

   • The Owner's First and Last Name is printed in bold.

   • The Owner's name is listed by First Name in Alpha Order.

   If, for example, the report is Grouped by Rooms Owner and filtered by Catering Owner, the grouping will continue to list each of the Rooms Owners that qualify based on the other filters, as well as having the selected Catering Owner on the business blocks.

2. Business Block Changes

   If there are business block or event production changes for the Owner for the requested production period, the ¿Changes¿ heading displays and the changes are listed in that grouping under the appropriate Status.

3. Status Code:

   • Each Status that has a change is listed with the Status Code and the word ¿Changes¿.

   • The Status Codes are listed in order by their configured sequence.

   • Within each Status Code, the business block is listed that contributed with a room or event change.

   • A business block will be included if it has a change to room revenue, room nights, or catering revenue during the requested production period. A business block may be listed under more than one status if the sleeping room and events are in a different status and thus contribute to a different status code.

   In the Status Code section the following columns display.

   • Block Name

   • Block ID

   • Arrival (the business block start date in the logged in Property¿s Short Date format)

   Current and Change rules apply to each of the sections in this report. Current Revenue and room nights at the time the report is printed and only including business block and events currently in that status.

   • A business block will be included if the room nights, room revenue or catering revenue had a status change during the production period in this status ¿ last status only except if noted below. The current revenues will be calculated from On the Books figures unless that business block or event is in an Actual Status. In that case, the current revenue will be calculated as:

     • Current Room Nights and Room Revenue figures will be taken from reservation_stat_daily.

     • Current Event figures are taken from Billed Revenue in gem event_revenue.

   • If a business block or event turned to a Return-to-Inventory Status, the report displays 0.00 for the Current Revenue, Room Nights, and Covers.

     • A (see example below): If a business block or event turned to Actual (and not DEF) during the production period and did not have a room night or revenue change during the period, that business block will not be listed in this report.

     • B (see example below): If a business block or event turned to DEF and ACT during the production period, it will be listed in the New DEF/ACT grouping. The Current revenue will be calculated from statistics for rooms and room revenue and from billed revenue for events.

     • C (see example below): If a business block or event turned to Actual (but not DEF) and had a change during the production period, that business block will be listed in the ACT Changes grouping.

     • D (see example below): If a business block turned to DEF and Cancel during the production period, that business block will be listed in the New DEF and New CAN groupings.

       Change Revenue and room nights that changed during the requested production period for this status.

     • E (see example below): Last status changes to room nights and revenue during the production period.

       • If a business block turned to a Return-to-Inventory Status during the production period, the Change Revenue is reported as a negative.

       • If an Actual business block or event had a change to room nights or revenue during the production period, that change is reported here and will be calculated as:

         • Room Revenue. Changes will be calculated from the production logs.

         • Catering Revenue (Billed Revenue and Production) excluding ACT Production from the requested production period. Any event with a change in gem$event_revenue or event$changes will be included.

   • Examples: Report generated for Production Dates July 1 and July 31, 2013

     • A : Business block turned to DEF on June 15th, 2013.

       Business block turned to ACT on July 2nd, 2013.

       No other changes in July 2013.

       That business block would not be included in the report.

       There will never be a New ACT grouping in the report.

     • B : Business block turned to DEF on July 2nd 2013 with 100.00 Catering Revenue / 3450.00 Room Revenue / 30 Room Nights. All rooms picked-up at 3450.00 room revenue / 30 room nights.

       Business block turned to ACT on July 5th, 2013.

       No other changes in July 2013.

       That business block will be included in the New DEF/ACT grouping with the following figures:

       • Current catering revenue = 100.00, Current room revenue =3450.00, Current room nights = 30.

       • Change catering revenue = 100.00, Change room revenue = 3450.00, Change room nights = 30.

     • C : Business block turned to DEF on June 15th, 2013.

       Business block turned to ACT on July 2nd, 2013 with 36,000.00 Catering Revenue / 1000.00 Room Rev.

       Business block added 300.00 catering revenue on July 2nd, 2013 / No Room changes Business block will be included in the ACT Changes grouping in this report with the following figures:

       • Current catering revenue = 36,300.00. Current room revenue - 275,000.00. Current room nights = 1000.

       • Change catering revenue = 300.00. Change room revenue = 0.00. Change room nights = 0.

     • D: Business block turned to DEF on July 15th, 2013 with 100.00 Catering Revenue / 3450.00 Room Revenue / 30 Room Nights.

       • Business Block added 300.00 Catering Revenue / 6750.00 Room Revenue / 3 Room Nights on July 17th, 2013

       • Business block turned to CAN on July 20th.

       Business Block will be included in the New DEF grouping in this report with the following figures:

       • Current catering revenue = 0.00, Current room revenue = 0.00, Current room nights = 0.

       • Change catering revenue = 400.00, Change room revenue = 4,125.00, Change room nights = 33

       Business Block will be also included in the New CAN grouping in this report with the:

       • Current catering revenue = 0.00, Current room revenue = 0.00, Current room nights = 0.

       • Change catering revenue = -400.00, Change room revenue = -4,125.00, Change room nights = 33.

     • E: Business block turned to TEN on July 2nd, 2013 with 20 room nights / 2000.00 room revenue.

       Business block added 10 room nights / 1500.00 on July 3rd, 2013

       Business block turned to DEF on July 7th, 2013

       Business block added 3 room nights / 450.00 on July 20th, 2013

       Business block will be included in the New DEF grouping with Current & Change the same:

       • Current figures calculated from OTB as 33 room nights / 3950.00 room revenue

       • Change figures calculated from OTB as 33 room nights / 3950.00 room revenue

     FB Revenue and Revenue Types to consider determined by the values selected in the FB Revenue filter in the report parameter screen. If no values have been selected in the parameter screen, no calculation will be done for these fields.

     • Current: The FB Revenue as of the time the report is printed and only including events currently in that status.

       • If a business block is in a Return-to-Inventory Status, report displays 0.00.

     • Change: The FB Revenue that changed during the requested production period for this status.

       • If a business block turned to a Return-to-Inventory Status during the production period, the Change Revenue is the full value of the F&B event revenue and is reported as a negative.

     Other Revenue - Revenue Types to consider determined by the values selected in the Other Revenue filter in the report parameter screen.

     • Current: The Other Revenue as of the time the report is printed and only including events currently in that status.

       • If a business block is in a Return-to-Inventory Status, report displays 0.00.

     • Change: The Other Revenue that changed during the requested production period for this status.

       • If a business block turned to a Return-to-Inventory Status during the production period, the Change Revenue is the full value of the Other event revenue and is reported as a negative.

     Room Nights

     • Current: The Room Nights as of the time the report is printed. Current Room Nights are included in the status grouping based on the business block’s status at the time the report is run.

       • If a business block is in a Return-to-Inventory Status, the report displays report 0.00.

     • Change: The Room Nights that changed during the requested production period. Change Room Nights are included in the status grouping based on the business block’s status when the change was made.

       • If a business block turned to a Return-to-Inventory Status during the production period, the Change Room Nights are the total number of rooms and is reported as a negative.

     ADR

     • Current: Calculated as (Current Rooms Revenue ÷ Current Room Nights). If a business block is in a Return-to-Inventory Status, the report displays 0.00.

     • Change: Calculated as (Current ADR (Current Rooms Revenue and Change Rooms Revenue) ÷ (Current Room Nights and Change Room Nights).

     Rooms Revenue

     • Current: The Rooms Revenue as of the time the report is printed. Current Room Revenue will be included in the status grouping based on the business block’s status at the time the report is run.

       • If a business block is in a Return-to-Inventory Status, the report displays 0.00.

     • Change: The Room Revenue that changed during the requested production period. Change Room Revenue is included in the status grouping based on the business block’s status when the change was made.

       • If a business block turned to a Return-to-Inventory Status during the production period, the Change Revenue is the full value of the room revenue and will be reported as a negative.

     Current

     • Covers: This value is populated with the sum of the Attendees for all of the Events for this business block and status and filtered by the Event Types selected in the Covers filter in the report parameter screen. If no value is selected, all Event Types will be considered.

       • If the event is Actual, the Actual Attendees are reported.

       • If the event is not Actual and Guaranteed Attendees is populated, the Guaranteed Attendees are reported.

       • If the event is not Actual and Guaranteed Attendees is NOT populated, the Expected Attendees are reported.

       • If the event is in a Return-to-Inventory status, the report displays 0.00.

     • Avg Check and Calculated (Current FB Revenue ÷ Current Covers).

       • For events in a Return-to-Inventory status, the report displays 0.00.

   At the end of each Status Changes grouping is a Total Status Changes line totaling each of the columns above.

   New Status Changes Grouping:

   • If the Owner has a business block or event that changed status during the requested period, the heading and New Status Changes appears.

   • All of the columns detailed above are included. For the New Status records, the Current and Change columns will be the same (except for the New DEF/ACT grouping).

   • At the end of each New Status grouping a Total New Status line appears that totals each of the columns above (ADR & Avg Check calculations to remain as detailed above).

   • At the end of each New Status section (per Owner) a Total New Status ¿ [Owner First + Last Name] line totaling all of the Bookings for this Owner.

   • At the end of each Owner grouping is a Total [Owner First + Last Name] that totals the Total Changes and Total New Statuses lines.

   • If the Print Grand Total check box is selected in the report parameter screen, an additional page with Totals & Grand Total displays.

   Grouped By Stay Month

   This option prints when Stay Month is selected as a Group By in the report parameter screen.

   The 1st grouping is by Business Block Stay Month. The report is then grouped and sorted as detailed above and the Totals are done on the stay month grouping instead of the Owner.

   Note:

   Current Catering figures are calculated using the standard calculation for catering forecasting.

   If the Use Forecast Revenue OPERA Control is active:

   • If the Use Forecast Revenue check box is selected in the main Events screen, only Forecast Revenue for that Event will be reported. If the Use Forecast Revenue Only check box is not selected in the main Events screen, the Expected Revenue for that Event will be reported.

   • If the Event is in an Actual status, the Billed Revenue for that Event will be reported.

   If the Use Forecast Revenue OPERA Control is not active, the report will evaluate each Revenue Type for each Event.

   • If there is Guaranteed Rev, it will be reported.

   • If there is no Guaranteed Rev but there is Expected Rev, Expected Rev will be reported. If there is no Expected Rev but there is Forecast Rev, Forecast Rev will be reported.

   • If the Event is in an Actual status, it will be reported as Billed Revenue.

   • Once Expected Revenue is added to the event, Forecast Revenue will be ignored for that Event and Revenue Type. So, even if the Resource is deleted, Forecast Revenue will no longer be reported.