RUNALLOCATION by totals

[former_member617674]

Hi All,

Using BPC 10.0 NW, I'd like to create allocation script.

Dimensions:

CATEGORY

TIME

PRODUCT

FACTORY

FLOW

Business scenario: we have production volumes for each PRODUCT, but without FACTORY. Values are imported into FLOW=IMPORTED with DUMMY factory. Each PRODUCT member has an attribute DRIVER, in which there is information about ID of driver which should be used to allocate values. Many products are connected to one driver.

We would like to create script which will allocate imported data based on driver attribute, but not for each product seperate, but using common calculated USING/TOTAL so all products would have same allocation.

We were using *RUNALLOCATION, and script allocated values but for each product seperately (script below).

Is there any method in *RUNALLOCATION to achieve it? (except copy totals into seperate dummy product etc.)

*XDIM_MEMBERSET CATEGORY = %CATEGORY_SET%

*XDIM_MEMBERSET TIME = %TIME_SET%

*XDIM_MEMBERSET FACTORY = DUMMY

*XDIM_MEMBERSET PRODUCT = BAS(TOTAL_PRODUCT)

*XDIM_MEMBERSET FLOW = IMPORTED

*XDIM_MEMBERSET ACCOUNT = VOLUME

*RUNALLOCATION

*FACTOR=USING/TOTAL

*DIM FACTORY WHAT=DUMMY; WHERE=BAS(TOTAL_FACTORY); USING=BAS(TOTAL_FACTORY); TOTAL=<<<

*DIM FLOW WHAT=IMPORTED; WHERE=ALLOCATED; USING=DRIVER_SKUCOUNT; TOTAL=<<<

*ENDALLOCATION

[former_member617674]

And below current version of script, calculating data as above...

*XDIM_MEMBERSET CATEGORY = %CATEGORY_SET%

*XDIM_MEMBERSET TIME = %TIME_SET%

*XDIM_MEMBERSET FACTORY = DUMMY

*XDIM_MEMBERSET PRODUCT = BAS(TOTAL_PRODUCT)

*XDIM_MEMBERSET FLOW = IMPORTED

*XDIM_MEMBERSET ACCOUNT = VOLUME

*RUNALLOCATION

*FACTOR=USING/TOTAL

*DIM FACTORY WHAT=DUMMY; WHERE=BAS(TOTAL_FACTORY); USING=<<<; TOTAL=<<<

*DIM FLOW WHAT=IMPORTED; WHERE=ALLOCATED; USING=SKUCOUNT; TOTAL=<<<

*DIM ACCOUNT WHAT=VOLUME; WHERE=VOLUME; USING=DRIVER; TOTAL=<<<

*ENDALLOCATION

[former_member186338]

Not clear!

In what member of PRODUCT dimension you want to store result?

Show data sample before and after allocation!

Vadim

[former_member617674]

Hi Vadim,

Same PRODUCT as in uploaded data (FLOW=IMPORTED), FLOW member will be different and both are in seperate hierarchy, so we want double data.

Source Data:

CATEGORY     TIME          FACTORY     PRODUCT     FLOW              ACCOUNT     VALUE

BUDGET          001.2015     DUMMY        PROD_01       IMPORTED     VOLUME        100

BUDGET          001.2015     DUMMY        PROD_02       IMPORTED     VOLUME        100

BUDGET           001.2015    US                PROD_01       SKUCOUNT    DRIVER           3

BUDGET           001.2015    EU                PROD_01       SKUCOUNT    DRIVER         70

BUDGET           001.2015    TA                PROD_01        SKUCOUNT    DRIVER         27

BUDGET           001.2015    US                PROD_02       SKUCOUNT    DRIVER           8

BUDGET           001.2015    EU                PROD_02       SKUCOUNT    DRIVER           1

BUDGET           001.2015    TA                PROD_02        SKUCOUNT    DRIVER          1

Calculation should get ACCOUNT=VOLUME, multiply them by ACCOUNT=SKUCOUNT (but not by each product - by sum of products) and put results into FLOW=ALLOCATED, with same ACCOUNT.

Results we get using script above (each product has own allocation by factory)

CATEGORY      TIME      FACTORY      PRODUCT      FLOW           ACCOUNT      VALUE

BUDGET           001.2015 US                PROD_01         ALLOCATED VOLUME           3.0

BUDGET           001.2015 EU                PROD_01         ALLOCATED VOLUME           70.0

BUDGET           001.2015 TA                PROD_01         ALLOCATED VOLUME           27.0

BUDGET           001.2015 US                PROD_02         ALLOCATED VOLUME           80.0

BUDGET           001.2015 EU                PROD_02         ALLOCATED VOLUME           10.0

BUDGET           001.2015 TA                PROD_02         ALLOCATED VOLUME           10.0

Expected results (all products has same allocation by FACTORY - US 41.5%, EU 40.0%, TA 18.5%)

CATEGORY      TIME      FACTORY      PRODUCT      FLOW           ACCOUNT      VALUE

BUDGET           001.2015 US                PROD_01        ALLOCATED VOLUME           41.5

BUDGET           001.2015 EU                PROD_01        ALLOCATED VOLUME           40.0

BUDGET           001.2015 TA                PROD_01        ALLOCATED VOLUME           18.5

BUDGET           001.2015 US                PROD_02        ALLOCATED VOLUME           41.5

BUDGET           001.2015 EU                PROD_02        ALLOCATED VOLUME           40.0

BUDGET           001.2015 TA                PROD_02        ALLOCATED VOLUME           18.5

[former_member186338]

Hi Michal,

Please explain:

BUDGET          001.2015     DUMMY        PROD_01       IMPORTED     VOLUME        100

BUDGET          001.2015     DUMMY        PROD_02       IMPORTED     VOLUME        100

In your example you have equal values for PROD_01 and PROD_02 to allocate. Can be different?

And what is the business idea behind this allocation?

Vadim

[former_member617674]

sure, these values are only for example and to show you what is the problem.

business idea: sales volume are planned per PRODUCT, without knowledge about FACTORY. We need to allocate it to each factory to get volume to produce, based on some driver.

It was decided that driver will be production volume from last year - unfortunately last year production volume mustn't have volumes for sales volume (e.g. some new product are planned). So analysts had decided to allocate sales volume by factory totals no matter which product were produced in which factory...

[former_member186338]

"So analysts had decided to allocate sales volume by factory totals no matter which product were produced in which factory" - strange idea

Then I can recommend only "copy totals into seperate dummy product etc." Allocation works with base members...

But what is bad in copy of all base products into PROD_DUMMY?

Vadim

[former_member617674]

Maybe strange, but it's a consequence of using last year volumes... Dou you have any best practice or hint what you do in similar situation? Tell analysts to fill gaps in driver?

it would be better not to copy same values, as we would have same business value in two places...

But if it's not possible then we have to do this.

[former_member186338]

"it would be better not to copy same values, as we would have same business value in two places"

If you always perform copy and allocation in the same script you can think about copy as allocation step and the result of copy - as some temporary value not used later!

Vadim

[former_member186338]

"Tell analysts to fill gaps in driver?" - I think - yes!

Existing products can be allocated using previous year figures, new products - by some assumptions in special flow or auditid...

Also it depends on how production is organized (a lot of business details required)

Vadim