PaPM - Linear Programming Optimization

[olica]

Hello! I would like to know if it is possible to solve linear programming problems in PaPM?

I need to calculate the optimal Transfer Price given a set of constrains. For reference, this problems are solve in Excel with the Solver Add-in.

Example: What is the optimal TP if the TP can only increase/decrease 20% relatively to the previous period price and the Operation Margin should be 10%.

[cojba]

Hello Andres,

Thank you for your question. We need more informations from your side in order to help you. Here is a list of questions that we have:

1. Can you please give us mathematical formula that you want to maximise (minimise) and explain all variables - for example a1*x1 + a2*x2+ ... an*xn (where xn is transfer price)?
2. Can you please give us list of constraints that needs to be met with explanation of all variables - for example b1*y1 + b2*y2 + ... bm*ym < C?
3. Do you contain informations about TP for previous period already in the data (together with TP for current year) or you want to manually set this value?
4. If you have some dummy data which match this issue, it would be more than helpful for us.
5. Are you using PaPM Cloud or PaPM On-Premise?

Best regards,

Novak

[olica]

Dear Novak, thanks for your swift reply!

I attached the photo of an example and the .txt file with the data.

I short question is, does PaPM have a solver integrated?

Answering your questions:

1. Your formula is correct. The example I just provided in simple. I need to achieve a target amount of sales and I can add a Markup % to the sales to increase/decrease them. So my objective function is Price * (1+ Mark up %). Therefore, the decision variable is the Mark Up %. papm-optimization-example-1-data.txt

2. The constrain is that the Mark Up can not be higher than 15%

3. In the previous example that I gave, yes we have info about the previous price. But let's examine first this easy example.

4. This is the dummy data ->

5. I am using PaPM On-Premise

[ZYT]

Hi olica,

There are first two options for your consideration: First one is HANA Optimization Function Library (OFL). Since the optimization problems are commonly linear and they can be derived to solve a matrix problem, HANA Predictive Analysis Library (PAL), e.g. regression, may also help. Both would require you to first create HANA procedures outside PaPM (e.g. in HANA studio), then integrate them in your PaPM model via "Remote Function Adapter - HANA procedures".

More interesting and easier approach would be to leverage the remote function adapter of R Script type: You may use R libraries to solve linear programming problems, for instance LPSolve (free), Gurobi (commercial), etc.

Soon some colleagues of mine will publish a blog to share their experience and insights of the third approach.

I hope this would be hlepful.

Best regards,

Zhiyi Tang

[olica]

Thank you Zhiyi !!

It is very helpful!

Would love if you can let me know when your colleagues will publish the article. I will have to start implementing this soon also.

Kind regards,

Andres

[olica]

Dear Novak, thanks for your swift reply!

I attached the photo of an example and the .txt file with the data.

I short question is, does PaPM have a solver integrated?

Answering your questions:

1. Your formula is correct. The example I just provided in simple. I need to achieve a target amount of sales and I can add a Markup % to the sales to increase/decrease them. So my objective function is Price * (1+ Mark up %). Therefore, the decision variable is the Mark Up %.

2. The constrain is that the Mark Up can not be higher than 15%

3. In the previous example that I gave, yes we have info about the previous price. But let's examine first this easy example.

4. This is the dummy data -> papm-optimization-example-1-data.txt

5. I am using PaPM On-Premise

[ZYT]

Hi olica,

I just realized your example might be a little bit trivial, because the total sales is a monotonous function in each mark up decision variable. When the actual price and quantity are fixed, the maximum of total sales lies at the upper boundary of each mark up variable, i.e. 15%.

Best regards,

Zhiyi

[olica]

Indeed. I made the example very simple. But I actually need to calculate the TP on grade level based of forecast.

Kr,

Andres