Divide cell value so weekly stock is balance between warehouses

[IsmaelPacheco]

Hello all!

First time I post in a forum for a excel issue I would like to solve. Hope you can help me, thank you for your time in advance.

The problem I have is based on stock of product that I need to send to different locations -Warehouses-. Let's say I have 5 warehouses, each of them with a certain level of stock already.
Warehouse 1: 30 units
Warehouse 2: 40 units
Warehouse 3: 15 units
Warehouse 4: 30 units
Warehouse 5: 5 units.

Now, I have available 100 units to split across the warehouses. I don't want to divide them equally, because that would mean adding 20 to each location and having warehouse 2 with 60 units and warehouse 5 with 25 only.

So that would be something already I would like to know if someone knows how to do it.

But to make it more tricky, each of units have a different values in terms of the days of stock that they represent, because maybe warehouse one sales 10 units per day, while warehouse 2 sales 20 per day. Is there a way I can split the 100 units so I match the days of stock or close to that on each warehouse?? See below screenshot:

I have sort of manually fill the column with "Extra stock" myself to get the same days of stock (sort of similar). Can I do this with excel??

And third and last part, final twist, what if stock I had cannot be split into individual units and can only be divided in lots of 5 units for example, is there a way to do that so I can only ship 5, 10, 15, 20, 25... units?

Thanks a lot!!

 

[Kaper]

The easy and automatic way to do it (without too much mathematics) would be by using a Solver tool.
If you do not have it activeted (it installed by default, but not active) see Load the Solver Add-in in Excel - Microsoft Support in Microsoft help.

Use a standard deviation minimalization as a goal. Limit your variables to be integer only values. And use Evolutionary method.
See first attached screenshot (it was made before running Solver, the output of Solver was exactly as proposed by you).

If you want to have lots of 5 (or any other number) you may either:
* use number of packs as variable (column E) and in column F formula use not Exxx but for instance Exxx*5,
* or: you may add next constraint to Solver, as shown on second screenshot. I used MOD function which calculates rest from the integer division. You probably shall use comma, not semicolon in a list of parameters of this function - it depends on regional settings). So the constraint is that all MODs are equal 0.

Note that evolutionary model in Solver needs some time for "evolution", so you have to wait some time for the result. You can observe in status bar how the solution improves (standard deviation decreases) with solver running. In solver options you can set a maximum time without improvement as a "flag" to stop Solver. From my experience with similar problems, 30 seconds is quite good setting here, byt it depends on your computer hardware, and on a problem itself.