Automatic Unit price calculation without having to create Unit price formula manually

[Former Member]

Hi,

I am a beginner in CLM and I work in a project which has quite a complex system.

In our system we have a need of a formula to create unit price in the RFx document. There are 2 required fields called ‘Period of performance start date’ and ‘Period of performance end date’ in the header tab of the RFx document.

Suppose the user selects the ‘Period of performance start date’ as 02/08/2017 and ‘Period of performance end date’ as 02/08/2019, then there are 3 years included in the procurement process. The buyer will enter yearly quantity and unit price per year across each line item. The formula defined to calculate unit price should be (QtyYr1 * PriceYr1 + QtyYr2 * PriceYr2 + QtyYr3 * PriceYr3…) / (QtyYr1 + QtyYr2 + QtyYr3).

Till now users have to manually add this formula in RFx and this is a tedious process especially when there are many years involved.

So a script is needed to be developed which will create formula automatically based on the Period of performance dates. When the supplier enters the forecasted yearly quantities and the unit prices, the ‘Extended price’ should be calculated automatically using the formula created.

Please suggest the logic for this.

Thanks in advance.