Input-Based Formula Definition

Owned by Ondřej Tesař
Nov 01, 2024
3 min read

The Input-Based Formula Definition allows for the configuration of prices using the Cost Plus Formula Type.

The following components are main parts of the Input-Based Formula:

  • Formula Type: Defines the calculation engine and the method of calculation. Currently, only the AGR_Formula_CostPlus type is provided.

  • Formula Definition: Provides a specific setup for the engine, including parameters such as lookup parameters and weights.

A single Formula Type can be utilized by multiple Formula Definitions, each offering different parameters to the engine.

Creating a Formula Definition

  1. Navigate to Agreements & Promotions > Formula Definition.

  2. Click Create New Formula Definition.

  3. Select the Formula Type, currently limited to AGR_Formula_CostPlus.

  4. Specify values in the formula input fields.

  5. Once the Formula Definition setup is complete, click the Submit button to submit it for approval.

Formula Input Fields

Cost Plus Formula Fields

  • Structure Type: The type of structure where the data is located. The supported data source is PX (Product Extensions).

  • Table Name: The name of the table where cost data is stored.

  • Cost Field: The column where the Cost is stored.

  • Currency Field: The column where the Currency is stored.

  • UoM Field: The column where the Unit of Measure (UoM) is stored.

  • Valid From Field: The column where the Valid From date is stored.

  • Valid To Field: The column where the Valid To date is stored.

Cost Plus Calculation Specific Fields

  • Adder Type: Defines the type of the adder – Absolute or Percentage.

  • Base Adder: Defines the amount to be added to the price, either as an absolute value or percentage.

Note: The Base Adder uses the default currency and UoM defined in the agreement header. It can be overridden at the line item level or using exceptions for specific products.

Bounds

Bounds prevent frequent price changes by ensuring price updates only occur if the change exceeds a specified threshold. The bounds compare the previous result (engine result from the previous period for the same agreement and SKU) with the current period's calculated result (previous minus calculated). Both bounds are specified as positive numbers.

  • Upper Bound: The newly calculated price is applied only if it increases by more than this value.

  • Lower Bound: The newly calculated price is applied only if it decreases by more than this value.

Note: Bounds can be overridden with exceptions for specific products.

Rolling Period

Allows you to calculate an average price over a defined period of time. For example, if the Rolling Period is 3 months, it considers the current date and then takes the previous 3 months plus the current one, using the costs found in those 4 months to calculate the average. If a Lag Period (see below) is also defined, it is applied first. This means that the current date is first adjusted by the defined number of months/quarters, and only then are the previous months/quarters looked up.

  • Rolling Period Type: Defines the type of the rolling period – Month or Quarter.

  • Rolling Period Amount: Defines the number of months/quarters over which to calculate the average price.

Lag Period

Moves the cost lookup back by a specified number of months/quarters from the current date.

  • Lag Period Type: Defines the type of the lag period – Month or Quarter.

  • Lag Period Amount: Defines the number of months or quarters to move back.

Bounds Examples

Example 1

Example 2

Example 3

Example 1

Example 2

Example 3

Scenario:

  • Previous price = 12

  • New calculated price = 14

  • Upper Bound = 5

  • Lower Bound = 2

Result:

Difference between previous and calculated price = 2

It is a price increase, so we apply Upper Bound. The price difference is not bigger than the Upper Bound value and so the previous price stays in place.

Scenario:

  • Previous price = 12

  • New calculated price = 19

  • Upper Bound = 5

  • Lower Bound = 2

Result:

Difference between previous and calculated price = 7

It is a price increase, so we apply Upper Bound. The price difference is bigger than the Upper Bound value and so the new calculated price is used.

Scenario:

  • Previous price = 12

  • New calculated price = 9

  • Upper Bound = 5

  • Lower Bound = 2

Result:

Difference between previous and calculated price = 3

It is a price drop, so we apply Lower Bound. The price difference is bigger than the Lower Bound value and so the new calculated price is used.

Â