Excel automatically calculate store/webstore amount

Hello



I am working on a case file which has to (in theory) automatically decide the amount of items it allocates to each store.
In the case, there are 50+ stores and every year 1 to 3 new stores open.

When there is a product that's at the end of it's life cycle, the stock in the warehouse needs to be distributed between stores and the webstore.

On average, the webstore sells between 2 and 5 times the amount of items a store does.

Every store has a limited surface.


Depending on the surface of the stores and the demand of the webstore (*2 till *5) i need to find a formula which should allocate stock to each store but the catch is, every store needs to get stock.

The shelf space needs to be 100% or 50% occupied, no inbetweens. (bonus points if able to be switched manually)

You can't always send the best selling stores all the stock and the least selling store no stock.

Is there anyone who knows how to do this? (mock-up data example in attachment)

kind regards

Screenshot 2022-03-14 194535.png