Views:

Scenario Overview

Using the North52 business rules engine for Dynamics 365 it is easy to implement Configure Price Quote (CPQ) requirements. In the example which follows discount pricing is applied according to defined rules.

Discount Pricing Rules

Rule #

Description

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%

Each product has a margin field (%) and the discounts are only applied to the margin on that product:

e.g. Local products will be 100% margin but the 3rd party products might have a margin of 70%. 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 Conditions, Actions 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

North52 Decision Suite Solution

The North52 Decision Suite 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.

Testing

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

Did you know?

North52 Quick Buttons can be used on Dynamics Portals Entity Forms

That's right, you can trigger complex business rules via a button on an Entity Form. You could:

Retrieve information from another entity or even another system and return it to the form
Create or Update records on the button click
Process advanced decision logic from information that has been completed on the form but not submitted
Have dynamic buttons showing different options dependent on your business rules
And much more!

Learn more about North52 Quick Buttons for Dynamics Portals

Keywords: Decision Table, Scheduler, DecisionTable, Formula Manager

xRM Formula #174 - CPQ - Custom Pricing - Training Courses