Time Offset

Scenario 4

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

• Generally, metrics are derived based on individual drivers.

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

• Forecasted Sales = Previous Year Sales * (1 + Average Growth Rate)
• Forecasted Sales is a formula node with the above formula with Input nodes Previous Year Sales and Average Growth Rate.
• The formula can use multiple nodes with a combination of mathematical operands like *, /, +, -.

Scenario details

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

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

• The module defined for the input template to maintain both the input node values is Gross Sales.

• After calculation, the result will be posted to the same level of detail by account, by product, and by customer.

Input Node

• Node1: Previous Year Sales
• Module: Module Gross Sales is created with the below definition:
  • (rows = account, product, customer, columns = time, filter = version)
• Relation to be used in Formula = Merge

• Node2: Average Growth Rate
• Module: The same module Gross Sales can be used as the template and the 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: The same module Gross Sales can be used as the template and the level of details are the 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.

Create Value driver

1. To create a new value driver tree, click on the Drive menu:

   • Under Calculations, click on + and select Rules to open the popup for capturing value driver details :

   • Description: Forecasted Sales

   • Action Type: Select Value Driver Tree

   • Model: Revenue Planning

     

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')

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

   Click on the filter icon on the right of each field and make relevant selections.

   

Input Tab

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

2. 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