LOOKUP AND ALLOCATION

Hi,

I am on BPC 10 NW. My scenario is as follows:

The requirement is to calculate cost of RM_CONCENTRATE(COGS MODEL) as 25% of SAL_REV(REVENUE MODEL).

I have two models, REVENUE and COGS.

REVENUE MODEL

ACCOUNTS TIME INPUTCURRENCY COSTCENTER PRODUCT SALESGROUP DISTRIBCHANNEL VERSION COMPANY METRIC AUDITTRAIL SIGNEDDATA NO_ACC 2014.01 AED NO_CC 000030000200000001 ZD2 10 ACTUAL DR01 SAL_REV BWUPLOAD 238

COGS MODEL:

PRODUCT_RM PRODUCT_SKU ACCOUNTS TIME INPUTCURR COSTCENTER PRODUCT DISTRCHANNEL METRICS UOM VERSION COMPANY SIGNEDDATA RM_CONCENTRATE 000000000000000175 NO_ACC 2014.01 NO_INPUTCURR NO_CC 000030000200000001 10 COGS_VOL UN BUDV0_2014 DR01 72 RM_CONCENTRATE 000000000000000163 NO_ACC 2014.01 NO_INPUTCURR NO_CC 000030000300000001 10 COGS_VOL UN BUDV0_2014 DR01 133

As can be seen, COGS has no Sales Group while Revenue model has Sales group as additional dimension.

So, I want to run allocation cross-application using *LOOKUP and distribute SAL_REV proportionately based on PRODUCT_SKU's COGS_VOL quantities and write to COGS_AMT.

AFTER: COGS MODEL

PRODUCT_RM PRODUCT_SKU ACCOUNTS TIME INPUTCURR COSTCENTER PRODUCT DISTRCHANNEL METRICS UOM VERSION COMPANY SIGNEDDATA RM_CONCENTRATE 175 NO_ACC 2014.01 NO_INPUTCURR NO_CC 000030000200000001 10 COGS_VOL UN BUDV0_2014 DR01 72 RM_CONCENTRATE 163 NO_ACC 2014.01 NO_INPUTCURR NO_CC 000030000300000001 10 COGS_VOL UN BUDV0_2014 DR01 133 RM_CONCENTRATE 175 NO_ACC 2014.01 AED NO_CC 000030000200000001 10 COGS_AMT UN BUDV0_2014 DR01 25% * 238*(72/(72+133))=238*0.35=83.5 RM_CONCENTRATE 163 NO_ACC 2014.01 AED NO_CC 000030000200000001 10 COGS_AMT UN BUDV0_2014 DR01 25% * 238*(133/(72+133))=238*0.64=154.5

So, I believe I need to Lookup from COGS Model into Revenue Model and then do allocation. Any ideas how this could be achieved?

Regards,
Draksharam