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.