Views:

Scenario Overview

The following is a discount pricing example for a Configure Price Quote (CPQ) Scenario.

A different discount applies for each band:

Band 1 - Purchase 0-10 items  0%
Band 2 - Purchase 11-50 items 22.5%
Band 3 - Purchase 51-100 items  40.5%
Band 4 - Purchase 101-200 items 58.5%
Band 5 - Purchase 201- items  67.5% 
  • Every product has a different set of discounts and potentially different bands.
  • To complicate it a bit more each product has a margin field (%) and the discounts are only applied to the margin on that product
  • Local products will be 100% margin but the 3rd party products might have a margin of 70% for example
  • So if the product sells at $100 and my margin is 70% then discounts only apply to $70 of the product

For this article it is assumed that you have at least basic familiarity with Decision Tables and/or have read the following articles:

Note: We will not detail step-by-step instructions here on how to set up ConditionsActions or Multi-Sheet Decision Tables, please read the above articles if you need detailed configuration steps.

The Multi-Sheet Decision Table will have to evaluate the band depending on the Quantity

To do this we will create 3 Decision Tables Sheets

  • Band Discounts
  • Determine Bands
  • Calculate Discount

N52 Formula Manager Solution

The Formula Manager solution works like this:

  • A formula of type Save - Perform Action is created on the Opportunity Product entity
  • When either the Price Per Unit or Quantity fields change:
    • The first Decision Table sheet calculates the band discounts
    • The second Decision Table sheet determines which band should be used
    • The third Decision Table sheet calculates the discount using the bands and band discounts and then updates the Manual Discount Amount field

Create the Formula

  • Create a new formula, setting the following values in the Formula Guide
    • Source Entity set to Opportunity Product
    • Set Formula Type to Save - Perform Action
    • Select the Decision Table editor
    • Click create 
  • Change the Name of the formula to CPQ - Custom Pricing - Training Courses
  • Set the Event to Update
  • Expand the Source & Target section
    • Control-click to multi-select the Price Per Unit and Quantity fields
  • Click Save

Global Calculations Sheet

The Calculations Sheet has a single calculation: Product Margin - used to calculate product margin by subtracting the standard cost from the price per unit.

  • Right-click and select Operations > Toggle Advanced mode to show the Global Calculations sheet
  • Select the Global Calculations sheet
  • Select cell A2 and type Product Margin
  • Select cell B2, go to the Source tab and expand the Source node
    • Click on Price Per Unit, then type a - 
    • Close the Source node and expand the Related (N:1) node
    • Expand product (productid)
    • Expand (Attributes)
    • Click on Standard Cost and replace the ? with 0 to set 0 as the default value if the Standard Cost field is null
  • Cell B2 should now look like:
    [opportunityproduct.priceperunit]-[opportunityproduct.productid.standardcost.0]
  • Click Save
  • Right-click and select Operations > Toggle Advanced mode to hide the Global Calculations sheet

Sheet 1: Band Discounts

  • Rename the existing Decision Table sheet to Band Discounts
  • Select columns A and B, then right-click and select Delete > Delete Column as we do not need Conditions or Actions for this sheet
  • Select all columns A to E, then right-click and select Insert > Insert Inline Calculation (==)
    • Note: if you using a North52 BPA build less than 562 you will need insert the Inline Calculations one by one
  • Rename row 2 for the columns:
    • A: Band 1 Discount
    • B: Band 2 Discount
    • C: Band 3 Discount
    • D: Band 4 Discount
    • E: Band 5 Discount
  • Set the discount levels in row 4
    • Band 1: set to 0
    • Band 2: type 0.225* then select Product Margin from the Global Calculations node in the Source tab 
    • Band 3: type 0.405* then select Product Margin again
    • Band 4: type 0.585* then select Product Margin again
    • Band 5: type 0.675* then select Product Margin again

Sheet 2: Determine Band

  • Right-click and select Insert > Insert Decision Table
  • Select the new Decision Table sheet and rename to Determine Band
  • Select column B, then right-click and select Delete > Delete Column as we do not need Actions for this sheet
  • Select all columns to G, then right-click and select Insert > Insert Inline Calculation (==)
    • Note: if you using a North52 BPA build less than 562 you will need insert the Inline Calculations one by one
  • Rename row 2 for the columns:
    • B: Band
    • C: Band 1 Qty
    • D: Band 2 Qty
    • E: Band 3 Qty
    • F: Band 4 Qty
    • G: Band 5 Qty
  • Select cell A2, go to the Source tab and search for quantity
    • Expand the Source node and click on Quantity
  • Select cell A4 and type ((0,10))
  • Select cell B4 and type 1
  • Select cell C4 and click on Quantity from the Source tab
  • Complete the rest of the table as outlined in the screen capture below

Sheet 3: Calculate Discount

Note: Please click on the refresh icon in the Source tab to update all the Decision Table Calculations


 

  • Right-click and select Insert > Insert Decision Table
  • Select the new Decision Table sheet and rename to Calculate Discount
  • Right-click and select Hit Policy > Exit this Decision Table on First Match to uncheck this option as we want to evaluate all rows in this sheet
  • Select cell A2, go to the Source tab and expand Decision Table Calculations and expand the Determine Band node 
    • Click on Band
  • Select cell A4 and type >=1
  • Select cell A5 and type >=2
  • Select cell A6 and type >=3
  • Select cell A7 and type >=4
  • Select cell A8 and type >=5
  • Select column B, then right-click and select Insert > Insert Increment Calculation (+=)
    • Replace Set Name with Cumulative Discount
  • Select cell B4, go to the Source tab and expand Decision Table Calculations
    • Expand the Determine Band node and c​​​​lick on Band 1 Qty, then type a (multiply), then click on Band 1 Discount in the Band Discounts node
    • Cell B4 should look like: {Band 1 Qty}*{Band 1 Discount}
  • Select cell B5 and click on Band 2 Qty, then type a (multiply), then click on Band 2 Discount
  • ​​​​​​​Select cell B6 and click on Band 3 Qty, then type a (multiply), then click on Band 3 Discount
  • ​​​​​​​Select cell B7 and click on Band 4 Qty, then type a (multiply), then click on Band 4 Discount
  • ​​​​​​​Select cell B8 and click on Band 5 Qty, then type a (multiply), then click on Band 5 Discount
  • Select cell C2, go to the Source tab and search for manual
    • Expand the Source node and click Manual Discount Amount 
  • ​​​​​​​Select cell C9, clear the search on the Source tab and expand the Decision Table Calculations node
    • Click on Cumulative Discount 
  • ​​​​​​​Click Save



This table uses an Increment Calculation (Calculate Discount Sheet, Column B) variable to keep a running sum of the discount based on the results of the individual Decision Table Sheets. You can think of this as a variable result which is then used to update the Manual Discount Amount field on the Opportunity Product entity.

Testing

Create a new Opportunity record and Opportunity Products to see the various discount levels being applied.

Did you know?

Decision Tables Simplify Complex Logic

Decsion Tables allow you manage complex business logic without code.  Key features include:

  • Simple visualization in a compact table format
  • Promotes grouping of rules in a single table
  • Business logic can be controlled by domain experts

A Decision Table represents multiple related business rules by using conditions, actions and data values in a spreadsheet-style table. The Decision Table uses columns for the conditions and actions of the rules and rows to represent the associated data values.

Learn more about Decision Tables