Dynamic Pricing Sheet

How to prepare a pricing process for automating the pricing calculation in the sales order.

The following reference materials for the example can be reviewed:

The Excel Worksheet

The associated price schedule that performs the same function as the worksheet

The sales order that needs a price generated

The sales order report with the price generated

The price tables that were set up for this example

Steps for setting up a new Price Schedule

1                    Set up a worksheet like the example shown in MS Excel that describes the pricing process for the product. Setting this up in Excel for one example provides the intermediate and ultimate test results required to check that the pricing process is working as expected.

2                    Identify or create the price tables that will be required to provide the data for the formulae. Note that if a new table needs to be created, it is better to create multiple tables that provide simple, easily verified results than complex tables that are harder to set up and maintain.

3                    Create the price schedule that follows the same pricing process as the worksheet. This is typically set up one line at time then tested before adding a subsequent line, to ensure that the intermediate results are valid before performing the next calculation. The price schedule is tested by setting up an order for the product using the quantities and specifications established in the worksheet, then running the pricing process by pressing the “Get price” button. The results should be the same as the intermediate results in the work sheet.

4                    Once the price schedule is working for the work sheet example test further by setting up the extremes and variations (quantity, dimensions, weight and any other pricing parameters that affect the eventual price) of the product specification in the worksheet and then amending the sales order to simulate the calculations (as in step 3). Multiple test samples are required to ensure that the calculations work appropriately under all circumstances. We recommend setting up and storing a spreadsheet of the test matrix of the tests to be performed so that regression testing can accurately replicate the conditions when any amendment is made.

Warning: DO NOT RELEASE A PRICE SCHEDULE TO A PRODUCTION ENVIRONMENT WITHOUT CAREFUL AND ADEQUATE TESTING.

Excel Worksheet

Price Schedule

This price schedule performs the same calculations as the Excel spreadsheet. Each formula delivers a result which is either used in the next step of the calculation or appears on the sales order. The intermediate results steps can be identified because the last step of the formula has an entry in the “Save in” column. These intermediate results are then used in the “Use” column of a subsequent step. Note that the “Save in” and “Use” columns can be specified as either a number or a short name. We have chosen in the example to identify the results of formula “100” as “100”. The following table illustrates the calculation in each formula:

Sales Order Header

Sales Order Line Item

The sales order item shows the results of the Pricing Schedule formula lines that do not have a “Save in” specified. These are accumulated to Total.

Sales Order Report

This illustrates how the final calculation appears on the sales order report.
Price Rates

Discount

This illustrates a simple table that allocates a discount rate based on the Weight. No discount if the weight is below 5,000 LB, 2% if the weight is from 5,000 to 10,000 LB and 3% if the weight is greater than 10,000 LB. Note that because the various components that comprise a total price are added to arrive at a selling price, the discount percentage is expressed as a negative multiplier.

Markup

The Mark up is 15% and could be varied according to Grade, thickness width and length. Currently as set up here, all grades get the same mark up.

Packaging Cost

Paint Cost

Paint cost is set at 3 cents per unit.

Important note: In the example, the cost is per square foot painted, however note that the Unit is set as CWT, because the eventual calculation requires cost per CWT. Introducing a different unit here such as Square Foot would complicate the calculation and lead to additional conversion processing that can make calculation more difficult to check. There is no translation between square foot and CWT, so any translation attempt would fail. Alternatively leave the unit blank. Only put a value in “unit” if it can directly be converted to the units required by the order.

Processing Cost

Processing cost can be varied by thickness in this example. 50 cents per CWT if the thickness is less than .2, 60 cents per CWT if it is greater than .2.

SO Density

This is a simple look up table that based on grade will return the density of the steel. Only one density is supplied for all grades for this example.

SO Length

This table looks up the value “Length” from the sales order and returns it multiplied by 1. This illustrates how the value returned by a table can be a mixture of active values from the sales order item, logical criteria and business rules and multipliers that can modify or categorise the actual values. Note that you cannot specify a multiplier that is not a column, however the column need not have any parameters set in min and max.

SO Pieces

A straight look up of the number of pieces from the sales order item

SO Thkns

A straight look up of the thickness requirement from the sales order item.

SO Width

Start Cost

The start cost can vary according to the product and the thickness. For less than .2 thickness the start cost is $20.00 per CWT. For anything greater than .2, the start cost is 18.00 per CWT.