- #1
sganesh88
- 286
- 0
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.
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.
Is there a logic that can make this simple? Please refer the template excel sheet below, that could make the problem still simpler if you haven't got a clear idea yet.
See the attached sheet--> View attachment problem.xls
I initially found with great ease a formula for the final percentage.
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.
Is there a logic that can make this simple? Please refer the template excel sheet below, that could make the problem still simpler if you haven't got a clear idea yet.
See the attached sheet--> View attachment problem.xls
