cancel
Showing results for 
Search instead for 
Did you mean: 

what is the IBP formula to get the correct calculation for BOM where price type is Rent

0 Kudos
781

Hello Experts,

I have a Bill of Material master data and the header has the keyfigures “Forecast” and “Usage” with planning level YEAR-PRD-LOC-CUST-PHASE-MARKETSEGMENT. I need to bring the value of these 2 KF to its component using the standard BOM component planning level YEAR-PRD-LOC-SOURCEID-COMPONENT to calculate the “Contract Value” of the header.

Given this data from the header (the difference is only on the MARKETSEGMENT):

I need to know the "Contract Value" based on the sum of the components contract value

The “Usage” and “Usage Com” keyfigures has the following definition:

KF: Usage

Planning Level: YEAR-PRD-LOC-CUST-PHASE-MARKETSEGMENT

Aggregation Mode: SUM

Disggregation Mode: Copy Value (No Proportional Disaggregation)

KF: Usage Com

Planning Level: YEAR-PRD-LOC-SOURCEID-COMPONENT

Aggregation Mode: SUM

Disaggregation Mode: Copy Value (No Proportional Disaggregation)

In my Copy operator, the source is “Usage” and target is “Usage Com” with copy attribute PRD and YEAR only. (The reason why only PRD is because at the component level, all components will use a dummy location called BOM).

GreenMat has the following Components and its corresponding component price and price type:

After the running the copy operator, FORECAST and USAGE is copied down to its component, the component keyfigure looks like this

The Component Contract Value has the following calculation definition:

If the Price Type is Buy then do the calculation: Forecast Com * Price Com

If the Price Type is Rent then do the calculation: Forecast Com * Price Com * Usage Com

Now, my problem is on the component level where the price type is Rent because the calculation is not correct.

If I compute this manually, (remember that the difference from the header level is in the MARKETSEGMENT East and West) the Contract Value Com for White component should be 100 (not 224)

It looks like the aggregation mode of the header level for keyfigure “Usage” is not correct. I tried to use the weighted average but also yield an incorrect result. Please advise on how I should copy the “Usage” keyfigure from the header level to “Usage Com” keyfigure for its component? Or maybe my formula for price type Rent is incorrect?

Please note that I need to use the standard planning level of the components because I run need to run the TS-Heur operator to explode the BOM

Thank you ��

Accepted Solutions (1)

Accepted Solutions (1)

riyazahmed_ca2
Contributor
0 Kudos

Hi Jerson,

I think I got your concern. Aggregation Mode of the KF 'Usage' might be the culprit. Anyway, I need your input on the below queries to see how far we can achieve your desired results.

1) When you say BOM, Is it the normal Production Source Header?

2) Is the Base Planning Level of 'Usage' KF comprises of Customer, Phase and Market Segment as Root Attributes? Snapshot of the Keyfigure config might help.

3) I understood you have only one dummy location 'BOM' as Location. But how about the Phase, Customer and Market Segments?

Best Regards,

Riyaz

0 Kudos

1) When you say BOM, Is it the normal Production Source Header? Yes. just to add more information, the KF Forecast (YEAR-PRD-LOC-CUST-PHASE-MARKETSEGMENT) is copied to the standard KF INDEPENDENTDEMAND (YEAR-PRD-LOC) as input for supply planning. Then run the algorithm TS-Based Supply Heuristic to populate the demand (KF DEPENDENTPRODUCTIONDEMAND output for supply planning) at the components level.

2) Is the Base Planning Level of 'Usage' KF comprises of Customer, Phase and Market Segment as Root Attributes? Yes, these are the root attributes of the keyfigure 'Usage'

3) I understood you have only one dummy location 'BOM' as Location. But how about the Phase, Customer and Market Segments? The Phase, Customer and Market Segment are not carried to the BOM level. This is because for the BOM to function in IBP, the prerequisite is to use the standard attributes (PRDID and LOCID) and the planning level PRD-LOC. Hence, i created a copy operator to bring the FORECAST to INDEPENDENTDEMAND and USAGE to USAGECOM.

Thank you Riyaz. Please let me know if there's anything not clear.

riyazahmed_ca2
Contributor
0 Kudos

Hi Jerons,

That infos has helped. So you perform a copy from Forecast to Independent Demand. I guess Forecast has aggregation mode as SUM. This is not an issue. Also you perform a copy form Usage to Usagecom. This looks like an issue due to their planning levels.

See the below example. KF Usage to KF UsageCom copy will happen by aggregating the data to Prod-Loc level with a sum and then copied to all the components as shown below. This is not correct. So first decide what kind of copy(Avg, Max, Min, Sum) you want to perform and update the same in aggregation level.

Once you finalize on this case(Min - 2; Max - 4; Avg - 3; Sum - 9), lets open up the discussions for rest open points.

Best Regards,

Riyaz

0 Kudos

Hi Riyaz,

Will use the aggregation mode MAX. So in your example will have a 'Usagecom' of 4 for all the components.

But also considering the aggregation mode CUSTOM because the calculation definition might not be a straightforward calculation to get the desired aggregation to compute the component contract price with price type "rent".

thank you

riyazahmed_ca2
Contributor
0 Kudos

Hi Jersons,

Cool!! Mark your aggregation mode as Custom and keep 'Max' at Request Level. This will resolve the issues at Usage to UsageCom copy.

Anyway does it resolve your concern with Price Type or you still have issues. If issue is unresolved, let me know where the issue is happening. Also let me know whether Price Type is an attribute of ProductionSourceItem.

Best Regards,

Riyaz

0 Kudos

Hi Riyaz,

No, still is not resolve. Sorry, I wasn’t clear enough on my issue. So, I will try to give more step by step details.

Step1: User upload the keyfigure Forecast and Usage of the Finished Product.

Given the Forecast and Usage, the user wants to know what the ‘Contract Value’ is. And this Contract Value’ calculation is the sum of all the components contract value.

Step 2: I run the copy operator from ‘Forecast’ to ‘Independent Qty’. The purpose of copying the ‘Forecast’ to ‘Independent Qty’ is to be able to use the standard IBP BOM functionality. The total aggregated forecast qty is 8 (East has 3 and West has 5)

to be continued..

0 Kudos

Step3: Execute algorithm "S&OP Operator V2" to populate the demand from ‘Independent Qty’ of the finished product PRD1 to the ‘Dependent Qty’ of its components (COMP1, COMP2, and COMP3).

The ‘Usage Com’ is populated by running the copy operator from keyfigure ‘Usage’ from step1.

The ‘Price Com’ are the components price

The ‘Contract Cost Com’ has a formula that when the Price Type is Buy then Dependent Qty * Price Com. If the Price Type is Rent then Dependent Qty * Price Com * Usage Com.

The Price Type is an attribute of COMPONENT which is a reference master data type. It is reference from PRODUCT master data. This is also following the standard IBP master data for BOM (Product, Component, Production Source Header, Production Source Item).

Now, the calculation result of keyfigure ‘Contract Cost Com’ is technically working as I was able to get the result. I got COMP1 with contract cost of 64, COMP2 with 96 and COMP3 with 160. The problem is that for price type “Rent” the result is NOT correct. When computing this manually the Contract Price should only be 100 (not 160) for COMP3.


Below I added the Market Segment East and West to show on how I derive the COMP3 contract cost com of 100:

There is no problem when the price type is buy because it is not using ‘Usage Com’ as input in the calculation. The incorrect calculation only happens when the price type is Rent.

I feel like there must be a correct custom aggregation formula to get the usage data correctly from the header down to its component and I just cannot figure out what formula should it be.

Right now, I use aggregation mode MAX in the keyfigure ‘Usage’. Hence, in the example above all components get 5 (East has 5 and West has 2)

riyazahmed_ca2
Contributor
0 Kudos

Hi Jerson,

I understood your need clearly. Still issue pertains on the Planning Levels only.

Source KF 'Usage' with Planning Level Year-Prod-Loc-Cust-Phase_MarketSegment has now aggregation mode Max

Target KF 'UsageCom' with Planning Level Year-Prod-Loc-SourceId-Component has disaggregation mode 'Copy value'

So as per this logic, 'UsageCom' has no relevance to Market Segment or even Customer Dimension.

The result of copy operator will be something like this.

Also if you review the 'UsageCom' KF in terms of Market Segment, it will be shown in Planning View as below.

The logic for 'Contract Component Value' KF should be something like below.

KF Name: Contract Component Value

KF Base PL: Year-Prod-Loc-Cust-Phase-MarketSegment

KF@Request = SUM(KF@Year-Prod-Loc-Cust-Phase-MarketSegment)

KF@Year-Prod-Loc-Cust-Phase-MarketSegment = SUM(KF@Year-Prod-Loc-Cust-Phase-MarketSegment-SourceId-Comp)

KF@Year-Prod-Loc-Cust-Phase-MarketSegment-SourceId-Comp = Forecast@Year-Prod-Loc-Cust-Phase-MarketSegment * PriceCom@year-(any)-Comp * IF("PRICETYPE" = "RENT", UsageCom@year-Prod-Loc-SourceId-Comp, 1)

**You may have to create the Planning Level year-Prod-Loc-Cust-Phase-MarketSegment-Sourceid-Comp. Also, I'm not sure about the PriceCom keyfigure config. Hence you need to align with them. If you get stuck, let me know.**

Best Regards,

Riyaz

Answers (0)