Lookup - Different Module

Scenario 2

Use Lookup to calculate TE Expense from Quantity & Price, when both drivers have different level of details

• Generally, metrics are derived based on Individual drivers

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

• Tools and Equipment Expense = Unit Price * Quantity

• Tools and Equipment Expense is Formula node with the above formula with Input nodes- Unit Price & Quantity

• Formula can use multiple nodes with the combination of mathematical operands like *, /, +, -

Scenario details

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

• Units Price & Quantity are captured/maintained at the same level of details -

  • by dataset

• Module defined for Input template to maintain the Input Unit Price node values - PRICE

• Module defined for Input template to maintain the Input Quantity node values - Finance Planning

• As the Price will remain the same all the time but Quantity varies, so for Price, we have kept the price constant and this consideration is taken in the module used for the price. That's why we have used different modules for Price and Quantity.

• After calculation, the result will be posted to the same level of details

  • by dataset

Input Node

• Node1 - Tools and Equipment Unit Price

• Module - Module Price created with below definition

  • (rows = account, columns = dataset 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, dataset 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 & level of details.

  • (rows = account, columns = time, filter = version and dataset)

• Formula to be used = 'Tools and Equipment Price' * 'Tools and Equipment Quantity'

Create Formula

There are 2 parts of creating the Formula node in Value Driver tree

Part 1 - Create Value driver

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

• 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

Step 2: 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'

Step 3: 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 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 not selected for fields.

Input Tab

Step 4: 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

Step 5: 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