Overview of adding MI data to a worksheet

To add MI related data to an embedded worksheet, you must access the worksheet of the type, Pivot Editor. The worksheet is displayed in a tabular format and contains the MI measure to be used.
Note: The periods in the worksheet can be displayed in rows or columns but must be set as the inner dimension in the worksheet that is displayed.

Right-click a measure to access the Add Market Intelligence option. However, this option is available only if:

  • The selected measure is defined as an MI data measure and tagged as Enabled for the cycle period, in the current data context.
  • The scenario can be edited by the current user (part of share).
  • The user is assigned the appropriate access rights to add MI data.
  • The measure is editable (as defined in the rv_measure_editable parameter).
Note: You cannot directly edit a MI Measure in the worksheet.

When you select the Add Market Intelligence option, the Add Market Intelligence window with these sections is displayed:

Context, Reference and Status

You can only edit the Reference field. The values for the other fields are derived from the worksheet.

Add Market Intelligence

This section is used to view the details of the volume of the MI data that is shared across the periods, and also to add the data to the specified periods.

Process flow:

  • The value of the Start Period is based on the period selected in the worksheet. This value cannot be modified.
  • To update the data, you can select these options:

Current Period:

By default, the Current Net Value and Current MI Value for the selected item, location and period are updated based on the current values and cannot be modified.

If the Number of Periods option is selected:

  • Specify the number of periods including the Start Period for which the MI is to be added.
    Note: When the number of periods used to add MI exceeds beyond the forecast horizon, the actual number of periods to spread is limited to the extent of the future horizon.
  • The Current Net Value is updated for the selected item, location, and period for the associated Net Forecast Measure (the display of the Net Forecast Measure data in the worksheet is optional).
  • The current net value is the cumulative value of these periods. This may include periods that are not added to the worksheet. Consequently, the values are directly retrieved from the database.
  • The Current MI Value is calculated for the selected item, location, and number of periods for the associated MI Measure.
  • The MI value is the cumulative value of these periods. This may include periods not added to the worksheet. Consequently, the values are directly retrieved from the database.
  • The Share Across Periods options are enabled.

When the Current Period or Number of Periods option is selected, the Method field lists these options:

  • New Net value: If this option is selected, the user must specify a numerical value for New Net Forecast. The difference between the Current Net and the New Net values is shared across the selected periods. This option is not available if the Profile option is selected.
  • Add % of Net: The user can specify a negative or a positive value, to either add or subtract a % of the net value. The result is shared across the periods:
    • current net = 1000, % Add of Net = +10. New MI value is 100 (resulting net value is 1100)
    • current net = 1000, % Add of Net = -10. Net MI value is -100 (resulting net value is 900)
    • This option is displayed only if the Profile option is not selected, and the Current Net value of the periods covered (by number of periods) is not zero. This value is verified when saving the data.
  • Add % of Current MI: The user can specify a negative or a positive value, to either add or subtract a % of the current MI value. The result is shared across the periods:
    • current MI = 1000, % Add of Current MI = +10. New MI value is 1100
    • current MI = 1000, % Add of Current MI = -10. Net MI value is 900
    • This option is displayed only if the Profile option is not selected, and the Current MI value of the periods covered (by number of periods) is not zero. This value is verified when saving the data.
  • Add Value: This value is added as MI data. The user can specify a negative or a positive value, which is shared across the selected periods.

If the Profile option is selected:

  • The Current Net Value, Current MI Value and the Number of Periods are updated based on the selected profile. The MI is spread in accordance with the profile.
  • The MI values are the cumulative value of the number of periods applicable for the profile. This may include periods not added to the worksheet. Consequently, the values are directly retrieved from the database.
    Note: When the number of periods based on the profile used to add MI exceeds beyond the forecast horizon, the actual number of periods to spread is limited to the extent of the future horizon

When the Profile option is selected, the Method field is populated with Add Value. Consequently, these options are displayed:

  • Uplift: This value is shared across all the positive profile values and applied as a positive MI value.
  • Downturn: This value is shared across all the negative profile values and applied as a negative MI value.

Share Across Periods

This section is used to spread the MI data across the periods. This option is applicable only if the Number of Periods option is selected. These are the options to spread the data:
  • Like: A list of the measures, derived from the list of Available Spread Measures in the Market Intelligence measure is displayed. The default spread measure defined for the Market Intelligence measure is selected by default.
  • Even: This option is used to spread the MI data equally to the specified number of periods. This is the default option.

Base Elements Spread

This section details the process used to spread the data to the base item, location, and periods. These options override the measure specified in rv_measure_spreadlike parameter providing the user greater flexibility on how the MI data must be spread.
  • Spread Measure: This contains a list of all the Available Spread Measures defined for the Market Intelligence measure. By default, the Default Spread Measure defined for the Market Intelligence measure is selected. The Market Intelligence data is spread to the base item and location elements, based on the selected measure.
  • Use for Period Spreading:
    • If this option is selected, the selected Spread Measure is used to spread to Period also, that is the Spread Measure is used to spread to item, location, and period elements.
      Note: You must select a time-phased measure as Spread Measure when selecting this option.
    • If this option is not selected, then the Spread Measure is used to spread to item and location and the Cycles Spread Measure is used to spread to period.
      Note: You must select a period constant measure as Spread Measure when selecting this option.
Note: The default value for this option is determined by the Market Intelligence definition.

Add Note

This section is used to add notes to the data and is displayed only if the user selects the Include Note option. By default, this option is set to Off. If the Include Note option is not selected, the note related fields are not displayed.
Note: If the selected measure is configured to make the addition of the note mandatory (defined in the configuration for the MI measure) or if the Force Add note on Recalculate option is selected for the cycle, the Add Note check box is selected and disabled. The user must manually generate the values of all the required fields for the note.

By default, the Summary field is populated with New MI added by {user}" where {user} is the current user. The value can be modified.

When the Force note on Recalculate option is selected for the cycle, the default values including the value for the Summary field are retrieved from the cycle settings.

Data validation

When you select OK , a validation check is performed to check for any errors on the form before the changes are saved. These checks are performed:

If the Add % of Net option is selected, a check is performed to ensure that the total current net forecast for the periods selected is not zero. If the value is zero, this error message is displayed:

The Method "Add % of Net" cannot be used when the Net Forecast is zero for the selected periods.

If the Add % of Current MI option is selected, a check is performed to ensure that the total current MI for the periods selected is not zero. If the value is zero, this error message is displayed:

The Method "Add % of Current MI" cannot be used when the Market Intelligence is zero for the selected periods.

If the stored Net Forecast values vary when compared to the retrieved Net Forecast Values (if the Net Forecast is modified by another user), this error message is displayed:

Unable to save changes. The Net Forecast has been modified since it was last loaded. Refresh worksheet and re-apply your changes.

A check is also performed when the data is submitted to ensure that the number of periods for which the data must be spread (either by using the Number of Periods or Profile option) does not exceed the Forecast Horizon. If the number of periods exceeds the forecast horizon, only the periods which can fit within the horizon are used and this message is displayed:

Number of periods to spread exceeds Forecast Horizon. Event will only be applied to {0} periods.

If the number of periods is a number less than 2, a warning message is displayed. The error must be corrected before continuing with the process execution.

Number of Periods to spread must be greater than 1.

If the spread measures (Share Across Periods Measure, Base Element Spreading - Spread Measure and Cycles's Period Spread Measure) do not have values specified for the selected range, this warning message is displayed. The error must be corrected before continuing with the process execution.

Measure {0} does not contain values and therefore cannot be used to spread Market Intelligence.

Post validation

If there are no errors, the MI data is persisted (saved) and a note is added to the database (if applicable). A window is displayed to indicate the status of the operation and these processes to spread the data are initiated:

  • The values are first shared across the selected periods or profiles based on the Share Across Period or Profile option respectively.
    • If Share Across Period is selected, the value is shared evenly (Even option) across each period or shared proportionally based on the selected measure (Like option)
    • Rounding does not occur when data is spread across the periods.
  • The values are then spread to the base elements. This is based on the options selected in the Base Element Spreading.
    • The MI is spread to base Item and locations based on the selected Spread Measure.
    • Subsequently, the MI is spread to the base period, if required, by using Spread Measure (if Use for Period Spreading option is set to On) or by using the Cycle’s Period Spread Measure.
  • The status of the MI Event is set to Pending:
    • Relevant measures are populated in the editable scenario with the latest values.
    • The scenario data is set to display only. When you view the scenario (current user is not part of share) only the MI Event and note, if applicable, are displayed.
    • If the scenario changes are discarded, the MI Event and note, if applicable, are deleted.
  • The worksheets data is updated to include the new data.

After the scenario changes are saved, the status of the MI Event is set to Approved. The measure values are persisted and the updated values are displayed in the worksheet.

This table describes the status and visibility of MI Event when scenario changes are saved or discarded.

Status of Event Discard Scenario changes Save Scenario changes
Pending MI Event and Note deleted and measure values not persisted. Measure values persisted (Event and Note already created).
Approved No Action - Created in previous edit session. No Action - Created in previous edit session.
Pending Delete Status reverted to Approved. Note is retained. Measure values updated prior to scenario editing. MI Event and Note deleted and updated measure values persisted to repository.