1. Administering Financial Consolidation and Close
  2. Consolidating Data
  3. Creating Consolidation Rule-sets

Creating Consolidation Rule-sets

You can create new rule-sets or copy existing items, either seeded system rule-sets or user-created rule-sets.

Watch the following video for information on creating consolidation rule-sets:

Video iconCreating Rule-Sets for Configurable Consolidations

To create a consolidation rule-set:

  1. On the Home page, click Application, and then click Consolidation.

  2. From the Consolidation Process page, select the Consolidated tab, and then select Configurable Consolidation.

  3. Click the Create Rule Setcreate ruleset icon icon.

  4. From the new Rule Set screen, enter the Name of the rule-set.

  5. Optional: Tap description to enter the description of the rule-set.

  6. The Condition determines the conditions under which the rule-set will execute and will initially display "<None>Always Run". Click the link next to Condition to change the condition, then select an option:

    • Select one or more of the conditions in the drop-down list.

    • Select the Advanced option to add condition blocks, or to group rule-set conditions.

      To add condition blocks, see Adding Condition Blocks.

      To group conditions, see Grouping Conditions

    • For Factor, set a rule-set factor to which to apply to the source data value when calculating the data to be written to the destination.

  7. If the Partner Elimination feature has been enabled for the application, and if one or more rules within the rule-set is to write a Partner Elimination entry, click on the Create Partner Elimination button. The Entity, Intercompany and Data Source dimensions will be added to the Scope of the rule-set.

    • Entity

      "Descendants of Total Geography" will be populated by default but can be modified

    • Intercompany

      • "#Sibling/Descendant of a Sibling#" will be populated and cannot be changed

      • Only data-points with an Intercompany Partner entry of a sibling or descendant of a sibling of the current entity being processed can generate Partner Elimination entries

    • Data Source

      Level 0 descendants of Total Data Source will be populated by default and can be changed

  8. For Rule-set Scope, specify members to restrict the data set to which the rule-set will be applied.

  9. To save the rule-set, click Save and Close, or to continue creating rule-sets, click Save and Continue.

Note:

Best practice: Where possible, exclude dimension members not required in the source data set using the Rule-set Scope settings instead of using the Condition. This design technique will provide for better processing performance. The Condition logic might be required for complex or multi-dimensional conditions but should be minimized where possible.

Adding Condition Blocks

When you create a rule-set and click the Advanced Condition dialog box, you can click the Add (+) icon to add a condition block, or click the Delete (x) icon to remove a condition block.

Each condition block will initialize with these four fields:

  • Dimension

    Select a metadata dimension for which to set a condition, or select Data Value to apply a data value condition.

  • Attribute

    If you selected a metadata dimension for the <Dimension> field, select the attribute of that dimension required in the condition. The Member Name attribute is available for all dimensions. The Entity and Intercompany dimensions also provide Ownership settings such as Method, Consolidation %, Ownership %, Minority Interest %, "Change" percentages, "Lower of entity and partner" percentages and "Prior period" percentages. The "Change" selections provide the change in the attribute from the prior period to the current period.

    If you selected Data Value for the <Dimension> field, you can select a partial POV to define the data-point for which to apply the condition. If no entries are selected, then the Source POV is used as the data-point definition. A member of each dimension can be selected in order to define a data-point other than the Source POV.

  • Operator

    Select the condition operator. The available operators will depend on the <Dimension> and <Attribute> selections. Valid operators include:

    • Equals

    • Does Not Equal

    • Is Greater Than

    • Is Not Greater Than

    • Is Less Than

    • Is Not Less Than

  • Value/Method

    Enter or select the value required against which the condition operator is applied.

    If you selected Member Name in the <Attribute> field, the Member Selector for that dimension will be available from which to select an entry or a "consolidation string". Consolidation strings are available for the Entity and Intercompany dimensions. You can select these strings from the Member Selector by clicking the Members link at the bottom of the Select Members dialog box and then selecting Consolidation Strings. See "Consolidation Strings".

  • If you selected an Entity or Intercompany Consolidation Method, then a list of methods will be available.
  • If you selected Consolidation %, Ownership %, Minority Interest % or Data Value, then you can enter a numeric value.

After you create a condition block, to add another condition, click the Add (+) icon. The conjunction between the conditions is "And" by default, but you can change it to "Or", "And Not", or "Or Not".

Grouping Conditions

After you add multiple condition blocks, you can group selected conditions. Grouping conditions determines required combinations of conditions.

To group conditions:

  1. Click on the row of the first condition to include in the grouping, then hold down Ctrl and select the required adjacent conditions.

  2. After you select all adjacent conditions for the grouping, click Ellipsis button for selections and select Group from the drop-down list.

For example, if Condition A and Condition B are grouped, and Condition C and Condition D are grouped:

Then the condition is applied as:

(Condition A and Condition B) or (Condition C and Condition D).

In this case, the rule-set will execute if conditions A and B are met, or if conditions C and D are met.

Note that only "Equals" and "Does Not Equal" are available for the Years and Period dimensions. You cannot apply "before" or "after" logic. Use a list or multiple block conditions to apply this type of logic.

Example 1:

Years | Member Name | Equals | FY16, FY17, FY18

Example 2:

Period | Member Name | Equals | Mar, Jun, Sep, Dec

Example 3:

Years | Member Name | Does Not Equal | FY16, FY17

Or

Years | Member Name | Equals | FY17

And

Period | Member Name | Equals | Jul, Aug, Sep, Oct, Nov, Dec

Rule-set Factor

The rule-set factor by default displays None. If the entry is retained, then the factor will be set in each of the related rules. If this entry is changed, then the factor selected will apply to all rules in the rule-set.

If Product of Entity / Partner is selected, then the factor applied will be the product (multiplication) of the Entity percentage and Partner percentage. If there is no Partner percentage (for example, the data point is No Intercompany), then the Entity percentage will be applied (the Partner percentage is assumed to be 100%).

If Last Period of Prior Year is selected, the factor applied will be read from the last period (period 12 or 13 depending on the application configuration) of the prior year. Note there might be an Opening Balance Scenario Carry Forward rule applied that specifies a different scenario as the scenario from which the prior year data is carried forward. If so, then the percentage will be read from the designated source scenario.

Select one of the options in the first drop-down box:

  • Current

  • Change In

  • Lower of Entity or Partner

  • Product of Entity / Partner

  • Specific %

  • Prior

  • Last Period of Prior Year

Then select one of the options in the second drop-down box:

  • Entity Consolidation %

  • Entity Ownership %

  • Entity Minority Interest %

  • Partner Consolidation %

  • Partner Ownership %

  • Partner Minority Interest %

Rule-set Source Data-set Definition

The source data-set to which the rule-set will be applied is by default drawn from the Entity Total data of the current Scenario, Year, Period and Entity, and View. For a multi-currency application the data will be Parent Currency. From within this data-set, the scope of the data to be processed by the rule-set can be further filtered by setting the scope (see "Setting the scope of the data-set").

The data-set can be taken from a different scenario, year, period, view, or consolidation member, and for a multi-currency application from either Parent Currency or Entity Currency. At present, the data must be taken from the current entity.

Changing the source POV for the Data-set

For each of the dimensions for which the source data-set can be drawn:

Scenario:

  • Current Scenario (default selection)

  • A specific scenario

  • #Previous Scenario#

    If #Previous Scenario# is selected then the scenario from which the first period Opening Balance carry forward is drawn will be used during rules execution.

Year:

  • Current Year (default selection)

  • A specific year

  • Current…

    If Current… is selected, also select an offset. The offset number can be from 0 (no offset) up to one less than the number of years in the application (so -9 for a 10-year application). The offset number will count backwards from the current year being processed and draw the data set from that year. For example, in Apr FY18, an offset of -2 will draw data from Apr FY16.

The combination of Year offset and Period offset allows an offset of up to the total number of periods in the application less one.

If the Year and / or Period offset selections, when combined with the current POV for which the consolidation is being run, cause the source POV to be outside of the limits of the application (that is, a period or year before the first period and year defined in the application), then the rule-set will not execute for that current POV. This will be noted in the Job Console but will not cause the consolidation to fail.

Entity:

Currently, only the Current Entity is available.

Consolidation:

Entity Total (default)

  • Entity Input

  • Entity Elimination Adjustment

  • Entity Consolidation

  • Proportion

  • Elimination

  • Contribution

Note that the Consolidation dimension members Proportion, Elimination or Contribution cannot be selected unless a different Scenario is selected, or if a Year or Period non-zero offset is selected. This restriction is intended to ensure that the data-set to be used as the source of the rules is not dependent on the results of the rules.

You can use the results of Opening Balance Carry Forward, Proportionalization, Standard Eliminations and Opening Balance Ownership Change system rules of the current entity. To do so, select the "Contribution" Consolidation dimension member instead of the default "Entity Total". This will then apply the consolidation rules to the post-proportionalized net data set, including the results of the system rules.

Currency:

  • Parent Currency (default)

  • Entity Currency

Note that this selection is not available for a Single-Currency application.

Also note that for multi-currency applications, Entity Currency is not available if Proportion, Elimination or Contribution has been selected for the Consolidation dimension member.

View:

  • FCCS_Periodic (default)

  • FCCS_YTD

  • FCCS_HYTD

  • FCCS_QTD

The source data will reflect the View member selected, but the rule will still write the calculated results to the Periodic view.

Setting the Scope of the Data-set

Click the Add (+) icon below the Scope bar to add a dimension to the Source POV filter definition. Select one of the available dimensions. Select the Member Selector icon, Consol member selector icon, from the right side of the new dimension row or type in the name of the required dimension. From the Member Selector, select one or more members or lists.

Specifying members in the Source POV will restrict the data-set to which the rule-set will be applied. The Entity and Intercompany dimensions also provide a Consolidation String selection for #Legal Company# and the Intercompany dimension provides for #Any ICP that is a descendant of the current parent#. See Consolidation Strings.

The rule-set Source POV will be inherited by any rule that is created within the rule-set. You cannot edit the inherited Source POV in the individual rules.

Watch this video to learn more about creating rule-sets:

Video iconUsing Year-To-Date Values in Configurable Consolidation Rules in Financial Consolidation and Close