Time Offset

Scenario 4

Use Lookup & Time Offset to Calculate Forecasted Sales from Previous Year Sales & Growth Rate

• Generally, metrics are derived based on Individual drivers

• Forecasted Sales is a metric calculated from Individual drivers such as Previous Year Sales & Average Growth Rate.

• Forecasted Sales = Previous Year Sales * (1+Average Growth Rate)

• Forecasted Sales is Formula node with the above formula with Input nodes- Previous Year Sales & Average Growth Rate

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

Scenario details

• Formula to calculate: Forecasted Sales = Previous Year Sales * (1+Average Growth Rate)

• Previous Year Sales & Average Growth Rate are captured/maintained at the same level of details by account, by product & by customer

• Module defined for Input template to maintain both the Input node values - Gross Sales

• After calculation, the result will be posted to the same level of details by account, by product & by customer

Input Node

• Node1 - Previous Year Sales

• Module - Module Gross Sales created with below definition

  • (rows = account, product, customer, columns = time, filter = version)

• Relation to be used in Formula = Merge

• Node2 - Average Growth Rate

• Module - Same module Gross Sales can used as the template & level of details are the same.

  • (rows = account, product, customer, columns = time, filter = version)

• Relation to be used in Formula = Lookup

• Lookup Metric - Previous Year Sales

Expected Output Node

• Formula Node - Forecasted Sales

• Module - Same module Gross Sales can used as the template & level of details are same.

  • (rows = account, product, customer, columns = time, filter = version)

• Formula to be used = Previous Year Sales * (1+Average Growth Rate)

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 - Forecasted Sales

  • Action Type - Select Value Driver Tree

  • Model - Revenue Planning

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

General Tab

• Name: Forecasted_Sales

• Description: Forecasted Sales

• Method: Formula

• Module: Gross Sales

• Formula: 'Previous Year Sales' * (1+'Average Growth Rate')

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

Input Tab

Step 4: Click on Input Tab and Open node - Previous Year Sales

• Module: Gross Sales

• Relation: Merge

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

  • Company: Context Member

  • Version: Context Member

  • Time: Context Member, Relationship: Offset, Year: -1

  • Revenue Account: R10100 - Gross Sales

Step 5: Next on Input Tab itself, open Input node - Average Growth Rate

• Module: Gross Sales

• Relation: Lookup

• Lookup Metric - Previous Year Sales

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

  • Company: Context Member

  • Version: Context Member

  • Time: #

  • Revenue Account: R10101 - Average Growth Rate