Sales Manager Progress (BI) (Rep_MGR_Progress_BI.Rep with Rep_MGR_Progress_BI.FMX)

Prerequisites for Sales Manager Progress (BI)

The Sales Manager Progress report is an easy to read month-end report showing sales managers' performance vs. their goals compared to On the Books figures. This report has three outputs: one organized by Manager and Year; one organized by Manager and Month using Production Goals; and the third organized by Manager and Month using Arrival Goals.

Prerequisites

Selection Criteria

Report Output

Monthly Production Tab

Monthly - Arrival Tab

  • This report can only be configured in a property with an active OPERA Sales and Event Management license.

  • To run this report requires you to have the REPORTS>Blocks and Groups task in the selected property.

  • To run this report requires you to have the BLOCKS>Block Print task in the selected property.

  • This report is a BI Publisher report. Customizing the report is not recommended. The following are the report elements:
    • Report Name = Sales Manager Month End Progress

    • File Name = rep_mgr_progress_bi.rtf

    • Form to run = rep_mgr_progress_bi.fmx

    • Data Template = rep_mgr_progress_bi.odt

Goal Period

These mandatory LOVs list the Report Periods selected in the Report Period fields. You cannot select a Goal Period outside of what has been selected in the Report Period.

  • From. (Mandatory). Select the calendar date to indicate the beginning of the Sales Manager's Goal Period for which the report is to be filtered.

  • To. (Mandatory). Select the end of the Sales Manager's Goal Period for which the report is to be filtered.

Report Period

These mandatory LOVs list the same periods offered when configuring sales goals. The periods selected here will be the ones on which the monthly versions of the report are grouped.

  • From. (Mandatory). Select the calendar date to indicate the beginning of the period for which the report is to be filtered.

  • To. (Mandatory). Select the end of the period for which the report is to be filtered.

Stay Periods When Configured. (Visible when the application parameter General > Combine Rooms and Catering Goals is set to On and enabled when the Goal option Production is selected.) When this check box is selected:

  • The Report Period fields are disabled.

  • The report displays the Stay periods configured as Arrival Periods in Production Goals. If Arrival Periods are not configured for the Production Goals, then no data will print in the report when Stay Periods as Configured is selected.

Property. Select the property for which the report should be printed. When you are logged in at the hub level, the property LOV lets you 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 the list of all status codes that can turn to an Actual status. The New Definite column is 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 business blocks and events in an Actual status in the Definite On the Books totals.

Cancel. (Mandatory). Select status codes from a list of all return-to-inventory statuses. The Cancel column is 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 1. (On the Books) Select Status Codes from the list of all status codes. The 1st On the Books column is calculated based on the business blocks and events currently in this status for the report period.

OTB Status 2. (On the Books) Select Status Codes from the list of all status codes. The 2nd On the Books column is calculated based on the business blocks and events currently in this status for the report period.

Event

F and B Revenue Type. Select active Revenue Types to include in the Food and Beverage Revenue calculations in the report.

Other Revenue. Select active Revenue Types to include in the Other Revenue calculations in the report.

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

Filter. Select Owners. If no value is selected, then all Owners listed are considered.

Rooms Owner. Select if only business blocks with this Rooms Owner will be considered.

Catering Owner. Select if only business blocks with this Catering Owner will be considered.

Block Owner. Select if only business blocks with this Block Owner will be considered.

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

Both. Select if all events will be considered regardless of the Exclude from Forecast check box.

Yes. Select if only events marked as Exclude from Forecast will be considered.

No. Select if 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 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.

Output

To determine whether the report is based on Yearly or Monthly output, select one or both check boxes.

  • Yearly. Select to generate the Yearly output.

  • Monthly. Select to generate the Monthly output. Whether Arrival or Production goals are generated depends on the selection in the Goal filter. When Monthly output is selected (Arrival or Production), the Sub Total per Year figures and Owner appear in the report.

Goal (visible only when the application parameter General > Combine Rooms and Catering goals is set to On).

Select one option button to determine which Monthly report option is generated. These options are accessible when the Output Monthly check box is selected (the Yearly option does not report on Goals).

  • Arrival. Arrival Goals are used in the report, and the Variance is calculated against the On the Books figures.

  • Production. Production Goals are used in the report, and the Variance is calculated against the Net Change (production) figures. The Goal Period listed in the report is the calendar timeframe period entered in the Goal From/To fields.

For Example:

Rooms and Catering Goal 1 Configured as:

– Production 01.01.18 - 01.31.18

– Arrival Period 01.14.18 - 12.31.18

Rooms and Catering Goal 2 Configured as:

– Production 01.01.18 - 01.31.18

– Arrival Period 01.01.15 - 12.31.17

If the report is run for Production Goals with the following date filters:

– Report Period 01.01.14 - 12.31.17

– Goal Period 01.01.14 - 01.31.14

The report output will have 3 Goal Period rows:

– Jan-14 to Dec 14, and Jan 15 to Dec 17, and Total

None. When selected, the report prints the same as the Production Goal minus the Goal and Variance columns.

Note:

When selected, the report prints the same as the Production Goal minus the Goal and Variance columns.

Revenue

Select one of the options, Net or Gross. The Incl Packages check box is selectable if either Net or Gross is selected and you want Included and Excluded Packages in the report.

  • Net. When selected, Room and Catering Revenue is calculated as:

    • Included generates are deducted

    • Excluded generates are not added

    • Included packages and their generates are excluded

    • If Incl Packages is selected, then Included & Excluded 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

  • 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 & Excluded 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

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.

  • This option will print when the Output Yearly is selected in the report parameter screen.

  • The 1st grouping is by Owner (which Owner is determined in the report parameter screen.

  • The 2nd grouping is by Stay Year (based on the Reporting Period range selected in the report parameter screen).

  • The report will print the requested Years for each of the selected Owners with a Total at the end for all of the selected Owners.

  • This option does not report Sales Manager Goals.

Production Period Section

This section shows data calculated based on the Goal Period date range selected in the report parameter screen. It is grouped by the business block allotment stay years, based on the selected Report Period range.

  • Year – Stay Year as determined by the range selected in the Report Period filter in the report parameter screen.

  • New DEF – (New Definites). Room nights calculated from business blocks that turned to Definite during the Production Period. Definite is determined by the New Definite filter in the report parameter screen.

  • Cancel – Room nights calculated from business blocks that turned to Cancel during the Production Period. Cancel numbers will always be reported as a negative. Cancel is determined by the Cancel filter in the report parameter screen.

  • Reval – (Revaluations) Room nights calculated from business blocks 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.

  • Slip – (Slippage). This is calculated business blocks that were already Actual when the Production Period began 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 Goal 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).

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.
  • Net Change - Net Change = (New Definite + Cancel + Reval + Slippage). Cancel numbers are always reported as a negative.

  • ADR - Average Daily Rate (Room Revenue / Net Change). The figures in this field conform to the property’s currency format.

  • Room Revenue – Calculated as the sum of the Room Revenue from (New Definite + Cancel + Reval + Slippage). Cancel numbers are always reported as a negative.

  • FB Revenue – (Food & Beverage Revenue) reports the sum of F and B Revenue from (New Definites + Cancel + Reval + Slip). Revenue Types are determined by the FB Rev filter in the report parameter screen. Cancel numbers are always reported as a negative.

  • Other Revenue – Reports the sum of Other Catering Revenue from (New Definites + Cancel + Reval + Slip). Revenue Types are determined by the Other Rev filter in the report parameter screen. Cancel numbers are always reported as a negative.

On the Books Section

On the Books data is calculated based on the current values at the time the report is run and grouped by the business block allotment stay years based on the Report Period range selected in the report parameter screen.

  • DEF/ACT - 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 currently in the Definite or Actual Status for that Stay Period.

    • Rooms – The current room nights for business blocks in this status.

    • ADR – Average Daily Rate. (Room Revenue ÷ Rooms)

    • Room Revenue – The current room revenue for business blocks in this status.

  • Status 2 – The current figures for business blocks currently in the Status selected in the OTB Status 2 LOV in the report parameter screen.

    • Rooms – The current room nights for business blocks in this status.

    • ADR – Average Daily Rate. (Room Revenue ÷ Rooms)

    • Room Revenue – The current room revenue for business blocks in this status.

  • Status 3 – The current figures for business blocks currently in the Status selected in the OTB Status 3 LOV in the report parameter screen.

    • Rooms – The current room nights for business blocks in this status.

    • ADR – Average Daily Rate. (Room Revenue ÷ Rooms)

    • Room Revenue – The current room revenue for business blocks in this status.

This option prints when the Output Monthly and Production Goal options are selected in the report parameter screen.

  • The 1st grouping is by Owner (Owner is determined by the selection in the report parameter screen).

  • The Sales Manager Name for the Owner codes selected in the report parameter screen will be listed.

  • The 2nd grouping is by Stay Month (based on the Report Period range selected in the report parameter screen).

  • The report will print the requested Period for each of the selected Owners with a Total at the end for all of the selected Owners.

  • This option will include Sales Manager Production Goals with a Variance to Net Change.

Production Period Section

The data in this section is calculated based on the Production that occurred during the periods selected in the Goal Period LOV. If more periods are selected in the Report Period than in the Goal Period, the additional periods will have On the Books data, but no figures will be reported for the Production Period columns.

Row Headers

  • Rooms – (Room Nights). The number of rooms booked per year, per Owner.

  • ADR – (Average Daily Rate). = (Rooms Rev / Rooms) This field conforms to the property’s currency format.

  • Rooms Rev – (Rooms Revenue). This field conforms to the property’s currency format.

  • FB Rev – (Food & Beverage Revenue). The Revenue Types used in this calculation are determined by the selection made in the FB Revenue LOV in the report parameter screen. This field conforms to the property’s currency format.

  • Other Rev – (Other Revenue). The Revenue Types used in this calculation are determined by selection made in the Other Revenue LOV in the report parameter screen. This field conforms to the property’s currency format.

  • Cat Covers – (Catering Covers). The Event Types used in this calculation of the number of event attendees are determined by the selection made in the Covers LOV in the report parameter screen. If no value is selected, then all values are considered.

  • Avg Check = (FB Revenue / Cat Covers). This field conforms to the property’s currency format.

Column Headers

  • New Definite – Calculated from business blocks 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 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 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 that were already Actual when the Production Period began and had a change during the Production Period.

An example for the above columns:

  • In June 2018, Block turns DEF with 100 Rooms

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

  • In August 2018, Block turns to ACT with 97 Rooms

If the report is run for Goal Period June – August 2018, the results are:

  • NEW DEF +100 rooms

  • Reval -5 rooms

  • Slippage +2 rooms

Net Change – Net Change = (New Definite + Cancel + Reval + Slippage). Cancel numbers are always reported as a negative.

Goal – The configured Goals for the Owner for that Production Month.

Variance – Variance = (Net Change – Goal). This calculation is used when Production Goal is selected in the report parameter screen.

On the Books Section

On the Books data is calculated based on the current values at the time the report is run and is grouped by the business block allotment stay months based on the Report Period range selected in the report parameter screen. If more periods are selected in the Report Period than in the Goal Period, the additional periods will have On the Books data, but no figures will be reported for the Production Period columns.

  • DEF,ACT – (On the Books Definites). 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 currently in the Definite or Actual Status for that Stay Period.

  • Status 2 – (On the Books Status 2). The current figures for bookings currently in the Statuses selected in the OTB Status 2 LOV in the report parameter screen.

  • Status 3 – (On the Books Status 3). The current figures for bookings currently in the Statuses selected in the OTB Status 3 LOV in the report parameter screen.

This option will print when the Output Monthly and Production Arrival Goal options are selected in the report parameter screen.

  • The 1st grouping is by Owner (which Owner is determined in the report parameter screen).

  • List the Sales Manager Name for the Owner codes selected in the report parameter screen.

  • The 2nd grouping is by Stay Month (based on the Report Period range selected in the report parameter screen).

  • The report prints the requested Period for each of the selected Owners with a Total at the end for all of the selected Owners.

  • This option includes Sales Manager Production Goals with a Variance to On the Books DEF,ACT.

Production Period Section

Production Period data is calculated based on the Production which occurred during the periods selected in the Goal Period LOV. If more periods are selected in the Report Period than in the Goal Period, the additional periods will have On the Books data, but no figures will be reported for the Production Period columns.

Row Headers

  • Rooms – Room Nights.

  • ADR – Average Daily Rate (Rooms Rev / Rooms). This field conforms to the property’s currency format.

  • Rooms Rev – (Rooms Revenue).This field conforms to the property’s currency format.

  • FB Rev – (Food & Beverage Revenue). The Revenue Types used in this calculation are determined by the selection made in the FB Revenue LOV in the report parameter screen.

  • Other Rev – (Other Revenue). The Revenue Types used in this calculation are determined by the selection made in the Other Revenue LOV in the report parameter screen.

  • Cat Covers – (Catering Covers). The Event Types used in this calculation of the number of event attendees are determined by the selection made in the Covers LOV in the report parameter screen. If no value is selected, then no Cover calculation is made.

  • Avg Check – (FB Revenue / Cat Covers).

Column Headers

  • New Definite – Calculated from business blocks 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 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 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 that were already Actual when the Production Period began and had a change during the Production Period.

An example for the above columns:

  • In June 2019, Block turns DEF with 100 Rooms

  • In July 2019, Block drops to 97 Rooms (still DEF)

  • In August 2019, Block turns to ACT with 95 Rooms

If report is run for Goal Period June – August 2012, the results are:

  • NEW DEF +100 rooms

  • Reval -5 rooms

  • Slippage +2 rooms

  • Net Change - Net Change = (New Definite + Cancel + Reval + Slippage). Cancel numbers will always be reported as a negative.

On the Books Section

On the Books data is calculated based on the CURRENT values at the time the report is run and is grouped by the business block allotment stay months based on the Report Period range selected in the report parameter screen. If more periods are selected in the Report Period than in the Goal Period, the additional periods will have On the Books data, but no figures will be reported for the Production Period columns.

  • DEF, ACT – (On the Books Definites) The column header update 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 currently in the Definite or Actual Status for that Stay Period.

  • Goal – The configured Goals for the Owner for that Production Month.

  • Variance – Variance = (On the Books DEF,ACT– Goal). This calculation is used when Arrival Goal is selected in the report parameter screen.

  • Status 2 – (On the Books Status 2). The current figures for bookings currently in the Statuses selected in the OTB Status 2 LOV in the report parameter screen.

  • Status 3 – (On the Books Status 3) the current figures for bookings currently in the Statuses selected in the OTB Status 3 LOV in the report parameter screen.