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.
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.1
Please apply DISCO, and create a 'Customer Properties' module to capture the Parent of 'Customer'.
Please try the following.
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 optimizer2