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 or as a pivot worksheet and is used to view the events that are added.
Note: The periods in the worksheet can be displayed in rows or columns but must be linked to an inner dimension.

Right-click a measure to access the Add MI 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 MI 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 defaulted 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 Only:

By default, the Current Net Value and Current MI Value for the selected period is updated.

If the Number of Periods option is selected:

  • 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).
  • To include multiple periods, the current net value is the cumulative value of these periods. This may include periods that 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, or period for the associated MI Measure.
  • To include multiple periods, the MI value is the cumulative value of these periods. This may include periods not added to the worksheet. Consequently, the values directly retrieved from the database.
  • The Share Across Periods option is enabled.

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

  • New Net value: If this option is selected, a numerical value is generated by the application. 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: This option is available, only if 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 persisting (store) the data. 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)
  • Add % of Current MI: This option is only available if the Profile is not selected and the current MI value of the periods covered (by number of periods) is not zero. This value is verified when persisting (store) the data. 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
  • Add Value: This value is added as MI data.The user can specify a 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 cumulative value of the number of periods in the profile are updated. The MI will be spread in accordance with the profile.
  • To include multiple periods, 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.
Note: When the number of periods or the profile used to spread the forecast the across periods goes beyond the forecast horizon:
  • The Event save the maximum number of periods that can be included in the forecast.

When the Profile option is selected, the Method field lists these options:

  • Add Value: This value is added as MI data. 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, if a Profile is not selected. These are the options to spread the data:
  • Like: A list of the measures, defined as Available Spread Measures for the Market Intelligence measure are displayed. This is the default option, if a default spread measure is defined for the Market Intelligence measure. The value of the default spread measure (if defined) is used for the selected MI data.
  • Evenly: This option is used to spread the MI data equally to the specified number of periods. This is the default option, if a default spread measure is not defined for the selected Market Intelligence measure.
Note: If a profile is selected, these options are disabled and not displayed.

Base Elements Spread

This section details the process used to spread the data to the base item, location, and periods. These options overwrite the measure specified in rv_measure_spreadlike parameter, which gives the user flexibility on how the MI data must be spread.
  • Spread Like: If this option is selected, the Spread Measure Selector is enabled and populated with the default spread measure defined for the Market Intelligence measure. The list contains all the Available Spread Measures defined for the Market Intelligence measures. The Market Intelligence data is spread to the base elements, based on the selected measure.
  • Even Spread: This option is used to spread the MI data evenly to the item, location, or period, that is, each item, period, and location receive the same volume of the MI data.
    Note: This is the default option.

Add Note

This section is used to add notes to the data and is displayed only if the user selects the Include Note switch. By default, this switch is set to Off. If the Include Note switch is not selected, the note related fields are not displayed.

If the selected measure is configured to make the addition of the note mandatory (defined in the configuration for the MI record) 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 New MI added by {user}" where {user} is the current user text is added to the Summary field, when the new MI added by [%user] parameter is defined. The value can be modified.

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

Data validation

The user must click OK to check for any errors on the form before saving the changes. 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, this message is displayed

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

The number of periods are saved as the actual number of periods (and not the number of periods initially selected either using the actual value or the profile).

Number of Periods to spread must be greater than 1.

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.

If the spread measures (Share Across Period 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.

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

The error must be corrected before continuing with the process execution.

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 profile based on the Profile or the Share Across Period options respectively (if the Number of Periods option is selected). Rounding does not occur when data is spread across the periods.
  • The values are spread to the base elements. This is based on the options selected in the Base Element Spreading measures. The values are either spread evenly across the base items (if the Even Spread option is selected) or spread based on the selected measure (Spread Like).
  • 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 measures value are persisted and the updated values are displayed in the worksheet.

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