How to imlement Sales Planning

[Former Member]

Hi,

I will implement the following sales planning scenario in BPC 7.0, NW version:

Input sales amount, input price and then automatically calculate the sales volume (sales volume = sales amount * price).

Have anybody an Idea how I can implement this scenario in BPC?

Thanks

Oktay

[Jeffrey_Holdema · Accepted solution]

Just for the record, Volume x Price = Revenue so to calculate volume you would need to divide the Sales amount by Price. I'm certain everyone has politely overlooked this point to address the BPC question.

As mentioned, script logic is the right method to do this calculation.

Only one application is probably all that would be needed, but you would have to consider the dimensionality of the 3 factors. Perhaps you have sales dollars at a customer/product level, but prices are input at a higher level, and volume may be needed at an intermediate level. Such a scenario could be made to have more than 1 application.

Best regards,

Jeffrey Holdeman

EPM RIG

[Former Member]

Hi,

thank for your answers that was very helpful. I have solved the problem as follows.

I define following member in dimension P_ACCT: SALESVOLUME, SALESAMOUNT und INPUTPRICE.

I design 3 input schedule. And calculate SALESAMOUNT with dimension member script. The calculation works fine!

Following problems I have to solve:

Inpput schedule Inputprice: data for price is aggregated.

Thanks

Oktay

[Former Member]

To do this in default logic, you can use the following code.

*WHEN ACCOUNT

*IS SALESAMOUNT

*REC(FACTOR=INPUTPRICE,ACCOUNT=SALESVOLUME)

*ENDWHEN

This will automatically calculate the sales volume when the amount and price is entered.

Tim

[esjewett]

You could do this using front-end logic (in Excel), in a dimension member formula, or in script logic.

This is a very broad question you are asking, so I would recommend trying to implement in script logic and see if that meets your requirements. That should get your started.

If you decide to go with dimension formulas or Excel formulas, be wary of the aggregation behavior of these options. You can read more about how different types of formulas aggregate in the BPC documentation.

[Former Member]

Thank for your answers Ethan and Tim.

Tim when I use your Script logic I guess that my Input Schedule must look like this:

2009.Q1

ProductA SALESAMOUNT 10

ProductA INPUTPRICE 10

ProductA SALESVOLUME 100

I am right?

The problem is following, I must deal with 50 Products and 10 Customer.

2009.Q1

ProductA SALESAMOUNT 10

ProductA INPUTPRICE 10

ProductA SALESVOLUME 100

ProductB SALESAMOUNT 10

ProductB INPUTPRICE 10

ProductB SALESVOLUME 100

....

....

-> This can be very confusing.

So I try to build three Applications: 1) SALESAMOUNT 2) PRICE 3) SALESVOLUME.

Now I'm trying to calculate sales volume. I think I have to fetch salesamount and price with script logic.

Thanks

Oktay

[pravin_datar]

Hi Oktay,

Did you mean to say you created 3 accounts (and not 3 applications) ? Why create 3 applications just for this strightforward calculation? you can use Tim's logic and loop over all the customers and products that you have.

Regards

Pravin

[Former Member]

Hi Oktay,

They can all be achieved through one application. Does the price stay constant for each product? If so then I would store it against a certain member in the datasrc dimension (if you have that) that does not roll up to the totals. Also it probably won't have a customer component, therefore it should be stored against a 'NON_CUSTOMER' member in that dimension.

You;d then enter just the prices for each produc tin one sheet. e.g.

2009.Q1

PRODUCTA,INPUTPRICE 10

PRODUCTB,INPUTPRICE 15

PRODUCTC,INPUTPRICE 20

The default logice could be the following

*WHEN ACCOUNT

*IS SALESAMOUNT

*REC(FACTOR=GET(DATASRC="PRODPRICEINP",ACCOUNT="INPUTPRICE", CUSTOMER="NON_CUSTOMER"),ACCOUNT=SALESVOLUME)

*ENDWHEN

This will allow you to just input the sales amount per product, per customer and it will calc the result.

Tim