Advanced Pricing Calculation in a Price Rule

Achieving pricing goals specific to the needs of an organization is a crucial element to the success of selling products. Achieving pricing goals sometimes requires the ability to define organization-specific formulas that use a series of different operands. Enterprise Pricer enables you to create mathematical expressions in the price rule that are used to calculate the net price for a given product. Thus, you can define complex promotional discounts in which you specify the adjustment, override price, and giveaway quantity using a custom mathematical expression. For example: a 5 percent discount plus a 5 USD discount for all orders of product number 10050 during the ordering period February 1, 2005, to December 31, 2005.

Enterprise Pricer enables you to:

• Define custom mathematical expressions, using the expanded set of pricing operands.

• Use the expanded list of variables known to Enterprise Pricer (net price, list price, and so forth).

• Define pricing variables for use with one or more price rules.

• Cap the mathematical expression calculations, for example, (List Price *.95) or 1000 USD, whichever is smaller.

Using Indexes in Pricing Calculations

In PeopleSoft Contracts, IndexStartValue, IndexEndValue, and IndexStartAmount are pricing variables that are used in the mathematical expression attached to a price formula. They are used for price index calculations. IndexStartValue and IndexEndValue are price index values that are effective on the IndexStartDate and IndexEndDate values that are automatically retrieved from the RT_RATE_TBL table by Enterprise Pricer during the pricing process.

Note:

IndexStartValue, IndexEndValue, and IndexStartAmount are used together. If one is used, you cannot use any other pricing variables.

The RT_RATE_TBL table is used to maintain the price indexing data:

Field Name	Description	Used by Enterprise Pricer
RT_RATE_INDEX	Market Rate Index	Identify the index. Its category must be Economic Indicator, as defined in table RT_INDEX_TBL.
TERM	Term	NA
FROM_CUR	From Currency Code	Both currency codes FROM and TO must be the same. They match the currency of the transaction.
TO_CUR	To Currency Code	Both currency codes FROM and TO must be the same. They match the currency of the transaction.
RT_TYPE	Rate Type	NA
EFFDT	Effective Date	Used to select the effective index value.
RATE_MULT	Rate Multiplier	Index value.
RATE_DIV	Rate Divisor	NA
SYNCID	Synchronization ID	NA
LASTUPDDTTM	Last Update Date/Time	NA

As an example, suppose these market rates exist:

Market Index	Effective Date	Rate (Index Value)
CPI	January 1, 2000	1200
CPI	July 1, 2000	1280
CPI	January 1, 2001	1300
CPI	July 1, 2001	1320
GOV	January 13, 2000	100.20
GOV	June 20, 2000	100.40
GOV	December 11, 2000	100.80
GOV	March 1, 2001	101.10

Then, a rate renewal occurs:

Current Offering Amount	Start Date	End Date	Renewal Basis	Price Index	Additional Percent	Price Cap Basis	Cap Percent
10,000	February 1, 2000	January 31, 2001	Index + additnl percent	CPI	2	Percent	5
10,000	February 1, 2000	January 31, 2001	Additnl percent	GOV	1.5	Index + Addtnl percent	1

You can set up price rule formulas to address the rate change:

Formula ID	Adjustment Flag	Numeric Value	Text Value (Math Expression)	Market Rate Index	Select Small/Larger
1	Percentage and Math Expression	5	IndexStartAmount * (1 + (IndexEndValue – IndexStartValue)/IndexStartValue + 2 / 100)	CPI	Smaller
2	Percentage and Math Expression	1.5	IndexStartAmount * (1 + (IndexEndValue – IndexStartValue)/IndexStartValue + 1 / 100)	GOV	Smaller
3	Percentage and Math Expression	2	IndexStartAmount * (IndexEndValue / IndexStartValue)	GOV	Larger

The starting values are:

• IndexStartAmount = 10000.

• StartDate = February 1, 2000.

• EndDate = January 31, 2001.

The first formula is processed in this manner:

For the CPI market rate index, use the Market Rate Table to find the index values for the start and end dates: 1200 and 1300. The mathematical expression is evaluated as:

IndexStartAmount * (1 + (IndexEndValue – IndexStartValue)/IndexStartValue + 2 / 100) = 10000 * (1 + (1300 – 1200) / 1200 + 2 / 100) = 10000 * (1 + 0.0833 + 0.02) = 11033

The percentage calculation is 10000 * (1 + 5 / 100) = 10500

Because the formula indicates to take the smaller of the two values 11033 and 10500, 10500 is the result of the formula.

The second formula is processed in this manner:

For the GOV market rate index, use the Market Rate Table to find the index values for the start and end dates: 100.20 and 100.80. The mathematical expression is evaluated as:

IndexStartAmount * (1 + (IndexEndValue – IndexStartValue)/IndexStartValue + 1 / 100) = 10000 * (1 + (100.80 – 100.20) / 100.20 + 1 / 100) = 10000 * (1 + 0.0060 + 0.01) = 10160

The percentage calculation is 10000 * (1 + 1.5 / 100) = 10150

Because the formula indicates to take the smaller of the two values 10160 and 10150, 10150 is the result of the formula.

Processing of the third formula uses index values to perform different types of calculations:

For the GOV market rate index, use the Market Rate Table to find the index values for the start and end dates: 100.20 and 100.80. The mathematical expression is evaluated as:

IndexStartAmount * (IndexEndValue / IndexStartValue) = 10000 * (100.80 / 100.20) = 10000 * 1.0060 = 10060

The percentage calculation is 10000 * (1 + 2 / 100) = 10200

Because the formula indicates to take the larger of the two values 10060 and 10200, 10200 is the result of the formula.