Month End Details (BI) (REP_MONTH_END_DETAILS_BI with REP_MONTH_END_DETAILS_BI_FMX)

The Month End Details reports business block sales and catering 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.

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 Block>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 = Month End Details
  • File Name = rep_month_end_details_bi.rtf
  • Form to run = rep_month_end_details_bi.fmx
  • Data Template = rep_month_end_details_bi.odt

    Note: When printing this Report help topic, we recommend printing with Landscape page orientation.

Selection Criteria

Production Period

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.

From. 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 are the calendar periods that will be displayed in the report.

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 events in this report should be filtered.

Origin. Select one or more Origins. This LOV 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)

Block & Events

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

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.

Status. Select one or more Status Codes from the list of all status codes except the ACTUAL status and the status selected in the Definite LOV.

Business Block. Select one or more 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.

Market. Select one or more market segments by which the business blocks should be filtered. If none are selected, business blocks will not be filtered by market segments.

Covers. Select one or more Event Type(s) to indicate which will be included in the Covers calculation in the reports. If no value is selected, all event types will be considered.

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.

Revenue

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.

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.

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

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

FB Revenue. Select one or more active Revenue Type(s) to include in the F&B Revenue calculations in the report. If none are selected, no F&B Revenue calculation will be done.

Other Revenue. Select one or more active Revenue Type(s) to include in the Other Revenue calculations in the report. If none are selected, no Other Revenue will be calculated.

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.

Owner. (Select one or more 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.

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.

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

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.

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.

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 bock 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 – 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 – 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 – 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 – Change Rooms Revenue) ÷ (Current Room Nights –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 – 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 ‘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 application parameter Catering>Use Forecast Value Only is active:
  
  - If the Use Forecast Revenue Only 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 application parameter Catering>Use Forecast Value Only 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.

Note: When currency values are too long to fully display in the allotted space in the report columns, (i.e. for values in the thousands with four digits or more and for values in the millions with seven digits or more, these values can be abbreviated so they can be accurately viewed. The application setting General>Currency Divisor Value can be configured to set the currency divisor value to K for values over 1,000 and to M for values over 1,000,000. For example, if the record is 354,000, when the currency divisor value is set to K, the value will display as 354K. If the record is 5,750,000.00, and the currency divisor value is set to M, the value will be rounded up and displayed, as in this example to 6M.