Prioritized Allocation

Cam_Haines

Trying to do some allocation by priority. Example. I have Product as a Parent and Customer as its child. I have 100 units of product A and 3 customers. Customer1 has a demand of 50 and is first priority so he gets all 50 he needs, customer 2 has demand of 40 and is second priority. I have 50 units left after customer 1 so customer 2 gets his 40. Customer 3 needs 30 and is 3rd priority. I have 10 units left so customer 3 gets shorted 20 units.  The challenge is subtracting from the total 100 units as it does down through prioritized hierarchy list.

I have an idea of how to do this, but it's turning out to be less than scaleable... wondering if any of you have other ideas.

Cam_Haines

Probably one of the quickest self-solved forum questions lol.  Just needed to type it out I guess.

 

With the help of @johndorma, I think we've solved this by calculating the % demand for each customer, then using RANKCUMULATE on that % to then calculate how much demand each customer is allocated.  RANKCUMULATE is limited to 10 million cells, but that should be fine for my model.

LipChean_Soh

Hi Cam_Haines,

 

Please apply DISCO, and create a 'Customer Properties' module to capture the Parent of 'Customer'.

 

Please try the following.

Thanks,

LipChean

dihya.atmani

Hello

You could also use the optimizer

The variables would be the quatity to assign to each customer (A, B and C)

You could assign to each customer a property wich is the priority: such as priority 1 will get the highest weight

so in this example, a will be assigned the weight of 3, B the weight of 2 and C the weight of 1

the objective function will then be maximize weight * Variable

The constraint is sum of variable <= to the available quatity (100 in this example). we could write A+B+C=100 but it is safer to write <= to avoid any errors

Variable <= Demand (in your example A<= 50,...)

I guess your solution works too, but this one alternative for the ones who are fond of the optimizer