I have these 2 problems to solve (on the image).
I have 9 stores that want different quantities of a product. The first store asked for 62 units, the second one 10 units, etc etc.
The supplier sent a total of 138 units (case 1). I have to distribute those 138 units to all the stores in a fair way. Each box sent from the supplier has 2 units and it's better to use multiples of 2 units for each store, so that it's not necessary to dismantle the box. If it's not possible, we can dismantle the box but just in the last case.
In case 2, there are some stores who will get less than what they asked. That's ok. I just need to distribute them in a fair way so one store doesn't get everything and the others nothing.
I have a way to do this but it's really complex and full of errors so I'm trying to find an easier way like a formula or something.
What I'm doing is (I'm going to give an example for the first line and Case2):
- Giving a box to every store so there is no risk to have one store receiving nothing.
- Calculate: NecessityByPriority = Necessity-1box = 62-2 = 60
- Calculate: PriorityEachStore: 60/sum(NecessityByPriority) = 60/72 = 83,3333%
- Calculate: PriorityUnits = PriorityEachStore*(Stock-9box) = 83,3333%*(58-18) = 33,3333 [the 9box corresponde to the number of units given in the first step: 9stores*2units each]
- Calculate: NumberBoxes = round(PriorityUnits/(Unit/box)) = round(33,3333/2) = 17
- Calculate: NumberUnits = NumberBoxes*(Unit/box) = 17*2 = 34
- Calculate the final quantity: NumberUnits+1box[given in step 1] = 34+2 = 36
In the case given there is not a great error but I am going to show you one with error above in a simulator I have done in Excel:
Here there were 3 units that remained. This is due to rounding. I can add a line to verify if there is something remaining and if so to redistribute. But I was looking for an easier way without all this formulas.
I have another process that calculates with the same formulas, everything for the first store. Then makes a 2nd iteration and recalculates everything except the first store. And on on until there is no more store. The problem is that here there is only 9 stores but sometimes there are hundreds.