Configure internal pricing (Excel)
- 01 Nov 2024
- 7 Minutes to read
- Print
- DarkLight
Configure internal pricing (Excel)
- Updated on 01 Nov 2024
- 7 Minutes to read
- Print
- DarkLight
Article summary
Did you find this summary helpful?
Thank you for your feedback
Define a pricing scheme in Excel
- From the application left navigation area, select Pricing.
- Select Pricing Schemes.
- Select
to download the pricing scheme template. - Define the Excel file sheets. Read more about how to configure the file.
- Select
, and select a file to upload.
to download the pricing scheme template.
, and select a file to upload.Note
Use a unique name to define a new pricing scheme.
Update an existing pricing scheme in Excel
- From the application left navigation area, select Pricing.
- Select Pricing Schemes.
- Select a pricing scheme, then select to download the file.
- Introduce changes to the file. Read more.
- Select , and select a file to upload.
Configure sheets in the Pricing Scheme Excel template
Define the pricing model in the Excel sheet for an existing pricing scheme or a new pricing scheme. The Excel sheet supports the pricing step definition with different price item type categories using the following four main sheets:
- PRICING_SCHEME
- APPLICABILITY
- LOOKUP_SEQUENCE
- CALCULATION
Also, use the pricing scheme Excel sheets to define the master data that is required by each pricing step depending on the price item type category:
- GROOVY_SCRIPT
- PRICE_ITEM_TYPE
- PRODUCT_TYPE
- LOOKUP_TABLE
Tip
Zilliant recommends defining the global data for the pricing scheme in advance. This will reduce additional efforts during the pricing/costing modeling. Specifically, define the lookup table to use in a pricing step in advance. This will enable the display of the table name and the field name in PRICING_SCHEME, LOOKUP_SEQUENCE and CALCULATION sheets.
For more information on how to define and configure a lookup table, read Lookup tables.
GROOVY_SCRIPT sheet
This sheet lists all Groovy scripts of the PricingGroovyScript type. You can add a new Groovy script and use it in the PRICING_SCHEME sheet for the pricing step that has the price item type Script.
Note
- Do not update an existing Groovy script.
- Use the GROOVY_SCRIPT sheet only to calculate the cost/price.
To define the new Groovy-based pricing step
- In the GROOVY_SCRIPT sheet, enter a name for the new step.
- In the PRICE_ITEM_TYPE sheet, define the Price Item Type Category.
- In the PRICING_SCHEME sheet, add pricing step with the Price Item Type defined in the previous step.
- Upload the pricing scheme file into the system:
- From the application left navigation area, select Pricing.
- Select Pricing Schemes.
- Select then select the file to upload.
- Upload the Groovy script into the system:
- From the application left navigation area, select Administration.
- From Master Data Management, select Groovy Script.
- Select the Groovy script that was used in the pricing step configuration.
- In the Script File tab, select to upload the Groovy script file containing the actual implementation of how to calculate the price/cost.
- In the Lookup Table field, define the lookup table if the Groovy script implementation is using the Lookup Table price item type.
PRODUCT_TYPE sheet
CPQ allows assigning the product type to each pricing step. The PRODUCT_TYPE sheet lists all existing product types available in the system. Users can add a new product type or modify the name of the existing one.
Add new pricing step with applicable product
- Go to the APPLICABILITY sheet if the Product Type already exists in the PRODUCT_TYPE sheet. Otherwise, define the new Product Type in the PRODUCT_TYPE sheet.
- Select the Product Type for the pricing step to apply.
Note
Do not to copy the entire row because of a hidden ID column. To copy the properties of an existing row, select all visible cells in a row, and then copy and paste them into an empty row.
PRICE_ITEM_TYPE sheet
The PRICE_ITEM_TYPE sheet lists all price item types available in the system. You can change the price item type category of any existing price item type. However, you need to make sure that the new properties are valid.
For example, if the price item type of a Lookup category was changed to a Calculation category, then you need to define the calculation formula in the CALCULATION sheet.
Important
Before changing the category of the price item type, make sure that another pricing scheme is not using the same price item type. Changing a price item type category will impact the pricing scheme that is using the same price item type.
Add a new Price Item Type
- In the PRICE_ITEM_TYPE sheet, enter the name for the new price item type.
Note
If you want to copy an existing row, do not copy an entire row. Instead, select columns B to K to copy an existing price item type property and then enter a new name for it.
- Enter the properties for the new price item type.
- Add a new pricing step with the newly created price item type:
- Select the PRICING_SCHEME sheet.
- Add a new pricing step with Step Position and Step Name defined.
- Choose the price item type you have defined in step 1.
- Define the price item type category properties:
- If the price item type category is Lookup-based, define the Lookup Table Sequence properties in the LOOKUP_SEQUENCE sheet.
- If the price item type category is Calculation-based, define the calculation formula and its components in the CALCULATION sheet.
Note
Do not to copy the entire row because of the hidden ID column. To copy the properties of any existing row, select all visible cells in a row, and then copy and paste them into an empty row.
LOOKUP_TABLE sheet
The LOOKUP_TABLE sheet lists all lookup tables in the system. Use this sheet to define a new lookup table.
Important
You cannot modify existing lookup tables in the Excel sheet.
Define a new lookup table
- Scroll until the end of the list, then enter a name for the new lookup table.
- Define the properties for each lookup field.
- If Record Type is Fetch, define the Field Search Expression.
- Upload the file into the system.
It is also necessary to add new lookup table column in _ LOOKUP sheet in order to show the lookup table name and field name in LOOKUP_SEQUENCE sheet.
PRICING_SCHEME sheet
The PRICING_SCHEME sheet is the main sheet to define pricing steps and their properties.
To define the pricing step, enter the following properties:
- STEP POSITION
- STEP NAME
- STEP PRICE ITEM TYPE
For the price item type with category Lookup and Calculation you will be required to complete the pricing step definition in the LOOKUP_SEQUENCE sheet and CALCULATION sheet.
Note
If you want to copy an existing row, do not copy an entire row. Instead, do the following:
- Select columns B to M, then copy and paste them into an empty row.
- Change the STEP PRICE ITEM TYPE.
- Copy and paste the new step into the desired place in the sheet.
- Reorder the STEP Position.
APPLICABILITY sheet
This sheet is optional. Use this sheet only when you need to apply pricing steps to certain product types.
LOOKUP_SEQUENCE sheet
Use the LOOKUP_SEQUENCE sheet when the pricing scheme contains a pricing step with the price item type category Lookup.
The following properties are mandatory:
- PRICING SCHEME NAME
- STEP POSITION
- LOOKUP SEQUENCE ID
CALCULATION sheet
Use the CALCULATION sheet when the pricing scheme contains a pricing step with the price item type category of type Calculated.
To define the pricing step of category type Calculated, you need to enter Header information and Details information.
Header information
Header information contains these mandatory columns:
| Header name | Description |
|---|---|
| PRICING SCHEME NAME | A dropdown list with the Pricing Scheme Name listed in PRICING_SCHEME sheet |
| STEP POSITION | A dropdown list with the Pricing Step listed in PRICING_SCHEME sheet |
| CALCULATION NAME | A name to describe the formula function. Do not use long formula values and not use duplicates that can be found in other pricing steps |
| CURRENCY EXPRESSION | The currency expression will be shown as dropdown list with these two expressions: Quote().hasCurrency[0] and SalesItem().hasCurrency[0]. If there is a Dynamic Data Attribute with Data Type Currency, it will also appear in the dropdown list. |
| UoM EXPRESSION | SalesItem().isProduct.hasUnitofMeasure[0] |
| ARITHMETIC EXPRESSION | Similar to the formula expression only follows the JEXL expression syntax. Can contain variable/component name which can be defined in Details |
Details information
Details information specifies how to obtain the numeric value for each component variable used in ARITHMETIC EXPRESSION. Based on the COMPONENT TYPE, the columns in the sheet will be highlighted to guide the user to enter values appropriately.
| Header Name | Description |
|---|---|
| COMPONENT NAME | The same name as the one used in the ARITHMETIC EXPRESSION |
| COMPONENT TYPE | Refer to Header (FETCHED, LOOKUP_DELTA, STEPPED, LOOKUP) |
| FETCHED | It is needed to select/enter the valid Fetch Expression which has same syntax as IMCScript. Enter or select only those expressions that can return the numeric value. |
| LOOKUP_DELTA | Define these four properties: LOOKUP TABLE NAME; LOOKUP FIELD NAME; PRICE ITEM TYPE; AGGREGATION TYPE |
| STEPPED | Select or enter the pricing step expression by following the Zilliant CPQ naming convention. For example, PricingStep.4, where Pricing Step. is a standard prefix and 4 represents the pricing step position |
| LOOKUP | Enter the name of the lookup table from which the value will be retrieved for the LOOKUP FIELD NAME that you specified in the Searchable Lookup Field. You can also specify the DEFAULT LOOKUP RECORD KEY value if this value will return a single numeric value. |
Was this article helpful?
