Help! Formula(s) to distribute stock among stores

[timma000]

Hi,
I've spent hours now trying to work out a way to do this, have tried countless options but can't get it to work correctly.
What I want to achieve:
I have X amount of stock to distribute amongst 4 stores. I want to distribute this stock in such a way as to bring each stores days on hand to as even as possible. i.e. I don't want one store to have 6 months worth of stock and another only has 1 weeks worth.
I've created a basic spreadsheet to try and illustrate this.
SOH = Stock on Hand at store already
Expected = How much the store sells per 30 days
DOH = Days on hand, showing how many days their current SOH would last them

In the example I have 100 of an imaginary product X I wish to distribute.

(Note that I don't want to redistribute existing stock at stores, only the new 100 I have coming in), so a store who already has plenty may not get any or very little of the new stock)

I would very much appreciate any help with this

[JohnTopley]

each stores days on hand to as even as possible

The key to this is the minimum stock level that a given store should maintain, not as you suggest, even stock in all stores: for example, if a store should hold 30 days stock (minimum/safe stock level) , then if it currently has 20, allocate 10 days worth of stock.

Another (perhaps the key) factor is lead time to obtain stock. There is plenty of information of the web on stock control, including Excel-based templates.

[timma000]

The key to this is the minimum stock level that a given store should maintain, not as you suggest, even stock in all stores: for example, if a store should hold 30 days stock (minimum/safe stock level) , then if it currently has 20, allocate 10 days worth of stock.

Another (perhaps the key) factor is lead time to obtain stock. There is plenty of information of the web on stock control, including Excel-based templates.

Thanks for replying. A further piece of information which ties into both your points, is that this stock that I'm trying to distribute is stock I already have from a store closing down. So lead time on stock is not relevant in this case.

As for min stock levels, yes 30 days is indeed what they would trade at but in this case I need to distribute the stock I already have and thus it may put some or all stores above 30 days, this is fine, I just want the stock to be distributed evenly according to their usage (or perhaps better to say distributed in such a way as to not overstock a particular store)
One idea I had was to rank each store based on DOH with rank 1 being the lowest DOH, then allocate them 1 unit then check again which store had rank 1 then allocate that store 1 unit and so on and so on until all of the units had been allocated. I just couldn't get that to work formula/macro wise

[Pepe Le Mokko]

Administrative Note:

Please don't quote entire posts unnecessarily. They clutter threads and make them hard to read.
Use the "Quick reply" instead
Thanks