A new knowledge base article has been published on our support portal.
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
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 B 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 click 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.