I encountered a problem related to merchandise allocation. We are given the present available quantity of an item and its demand for every store. Quantity to be allocated is also given. The requirement is to allocate units in such a way that the "final available quantity-Demand%" is the same for maximum number of stores.
I initially found with great ease a formula for the final percentage. (If i hadn;t this would have been placed in pre-algebra section. )
So here's the formula..
Final Quantity-Demand% = (Quantity to be allocated+sum of existing quantities from all stores)/(sum of demands for all stores)
But when i tried different sets of data, i found to my utter dismay that in the process of making all the quantity-Demand% same, the formula is removing existing units from some of the stores which shouldn't be done.
I even tried removing those stores that had their units pulled and re tried using the same formula but still it resulted in the same result.
All I want is this.. Is there a way to detect which stores will get hit if we apply this formula? Something like their initial Qty-Demand% to be lesser than so and so.. If we can do that, in the very first application of the formula, we can find the answer.
Please refer the template screenshot below, that could make the problem still simpler if you haven't got a clear idea yet. Please give it a thought. Any idea would be appreciated. I have been banging my head for a lot of time reg this.
See the attached sheet-->