Lookup - Different Module

Scenario 2

Use Lookup to calculate TE Expense from Quantity and Price when both drivers have different levels of detail.

• Generally, metrics are derived based on individual drivers.

• Tools and Equipment is a metric calculated from individual drivers such as Unit Price and Quantity.

• Tools and Equipment Expense = Unit Price * Quantity.

• Tools and Equipment Expense is a formula node with the above formula, using input nodes: Unit Price and Quantity.

• The formula can use multiple nodes with a combination of mathematical operands like *, /, +, -.

Scenario details

• Formula to Calculate: Tools and Equipment Expense = Unit Price * Quantity

• Unit Price and Quantity are captured/maintained at the same level of detail: by dataset.

• The module defined for Input template to maintain the Input Unit Price node values is PRICE.
• The module defined for Input template to maintain the Input Quantity node values is Finance Planning.

• Since the price remains constant while the quantity varies, we have kept the price constant in the module used for the price. This is why different modules are used for Price and Quantity.

• After calculation, the result will be posted to the same level of detail: by dataset.

Input Node

• Node1: Tools and Equipment Unit Price.
• Module: Module Price created with the following definition.
  • Rows: account
  • Columns: dataset filter
  • Filter: version
• Relation to be used in Formula = Merge

• Node 2: Tools and Equipment Quantity
• Module: Module Finance Planning can be used as the template.
  • Rows: account
  • Columns: time
  • Filter: version
• Relation to be used in Formula: Lookup
• Lookup Metric: Unit Price

Expected Output Node

• Formula Node - Tools and Equipment Expense
• Module: Module Finance Planning can be used as the template and level of details.
  • Rows: account, columns
  • Columns: time
  • Filter: version and dataset
• Formula to be used: 'Tools and Equipment Price' * 'Tools and Equipment Quantity'

Create Formula

There are two parts to creating the Formula node in the Value Driver tree.

Create Value driver.

1. To create a new value driver tree, Click on Drive menu.

2. Under Calculations, click on + & select Rules to open popup for capturing value: driver details:

   • Description: Tools And Equipment Expense

   • Action Type: Select Value Driver

   • Model: Finance Planning

     

3. Click on + Add Rule on the level 2 header to open the node creation pane:

   General Tab

   • Name: Tools and Equipment
   • Description: Cost of Tools and Tools (CAPEX)
   • Method: Formula
   • Module: Finance Planning
   • Formula: 'Tools and Equipment Quantity' * 'Tools and Equipment unit Price'

4. Select the field member tagging to post the result of the calculation.

   Click on the filter Icon on the right of the each of fields and make the relevant selections:

   • Cost Center: Same as Source

   • Version: Same as Source

   • Time: Context

   • GL Account: Same as Source

   • Dataset: AU2030 - Finance

     Note: By default, the output put is posted as "Same as source", even if left unselected for fields.

     

Input Tab

1. Click on Input Tab and Open node Tools and Equipment Quantity:

   Module: Finance Planning

   Relation: Merge

   Fields member selection: Click on the filter button on the right of each field:

   • Cost Center: Context

   • Version: Context

   • Time: Context

   • GL Account: 65100200 - Tools And Equipment

   • Dataset: QTY - Quantity

2. Next on Input Tab itself, open Input node: Tools and Equipment unit Price:

   Module: Price

   Relation: Lookup

   Lookup Metric: Tools and Equipment Quantity

   Fields member selection: Click on the filter button on the right of each field:

   • Cost Center: Context

   • Version: Context

   • GL Account: 65100200 - Tools And Equipment

   • Dataset: PRICE - Price