I have a rather challenging problem to solve and thank you to the legend that solved my last issue (a first time posting).
In the example below I have taken a top in 4 sizes across 9 inventory locations (8 stores and a head office location). SC is our support centre location that initially holds the store and then it is transferred to the eight other stores. Stores are referenced by 2 letters e.g TK which is our Toorak Retail Store.
I am trying to ensure that each size is allocated to the store the has sold it best. If a store has sold out of an item I want to take it from the store that has sold it the least well and replenish the store that has sold it the best. This then creates the transfers of stock that I need to send to stores. The problem is whilst this works well for each size it is possible that a store is just left with a single size of the item - from a retailers persepctive this isnt great as you want at least 2 of a style (in different sizes) in a store rather than having one store with say 3 of the item. I will explain my current model below.
This first image shows how each size of the style has sold since it has been dropped in store. You can see that 32 have been sold in total.
Sales Since Drop.JPG
This second image shows the current stockholding by store of each size of the top.
Actual Stock on Hand.JPG
The third image shows the rank of how well each store has sold the particular item. Rank 1 means that it has sold it the best and rank 8 it has sold the style/size the worst. If the item hasnt been sold at all it works to rank from left to right.
This next image shows the minimum stock I would like to hold in each store. Often this cannot be fulfilled because there is not enough stock.
Store Stock Limit.JPG
The final image shows the Optimised Stock Level for a store and then how the stock should be moved between stores. A -1 means send it out and a +1 means that store will receive the item. The problem that I have is that the Optomised stock level for all for sizes for the AP Store leaves only 1xsize 2 of the top - this will leave just the one size of the top looking fracturered instore. Is there a way to change the formulas so that if a store has 3 in total that one of those is allocated to a store with only 1 so that both stores end up with 2? E.g take the Size 3 from TK and move it to AP?
Optimised Stock Level and Stock Movement.JPG
I will attach the spreadsheet in the next post.