Sales Manager Progress (BI) (REP_MGR_PROGRESS_BI.REP with REP_MGR_PROGRESS_BI.FMX)

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 is organized by Manager and Month using Arrival Goals.

Prerequisites

• This report can only be configured in a property with an active OPERA Sales and Catering or SFA license.
• The 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 = 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

Selection Criteria

Goal Period

These mandatory LOVs list the Report Periods selected in the Report Period fields. Users 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 Y 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 that have been configured as Arrival Periods in Production Goals. If no Arrival Periods have been configured for the Production Goals, then no data will print in the report when Stay Periods as Configured is selected.

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

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

Event

FB Revenue. Select active Revenue Types to include in the F&B 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 will be considered.

Owner. Select Owners. If no value is selected, then all Owners listed in the LOV will be 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.

Overall Owner. Select if only business blocks with this Overall 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. When selected, the Yearly output will generate.

Monthly. When selected, the Monthly output will generate. Whether Arrival or Production goals will be 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 Y.)

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

Arrival. Arrival Goals will be used in the report and the Variance will be calculated against the On the Books figures.

Production. Production Goals will be used in the report and the Variance will be 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.14 - 01.31.14

- Arrival Period 01.14.14 - 12.31.14

Rooms and Catering Goal 2 Configured as

- Production 01.01.14 - 01.31.14

- 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 the application parameter General > COMBINE ROOMS AND CATERING GOALS is set to N, the report output does not include the Goals and Variance columns.

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 and you want Included and Excluded Packages in the report.

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

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 (Configuration > Cashiering > Rev Type Cross Reference):
  • 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.

Note: Currency values in this report can be represented by an abbreviation when the application setting General > Currency Divisor Value is configured. For example, if values recorded in this report are too long to accurately display in the allotted space, such as 354,000, the currency divisor value set to K for 1000 can change that display to 354K so that all values over 1000 will display shortened and without decimals. The divisor value can also be set to M, which will shorten all amounts over 1 million by 5 digits and print them without decimals.

Report Output

Yearly Option Tab

• 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 and ha 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 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 will always be 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 will always be reported as a negative.
• FB Revenue – (Food & Beverage Revenue) reports the sum of F&B Revenue from (New Definites + Cancel + Reval + Slip). Revenue Types are determined by the FB Rev filter in the report parameter screen. Cancel numbers will always be 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 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 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 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.
  • 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.

Monthly Production Tab

This option will print 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 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) 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 will be 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 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 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 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.
• 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.

Monthly - Arrival Tab

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

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

In August 2012, 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 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.
• 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.