Many-to-many stock reallocation based on balancing a tertiary cell.

Hi there.

So, here is my problem.

I have many stores (20), and they all have stock.
Lets call them Store1 to Store20.

I also have many items in each stockroom.

So my table looks something like this:

|Store1 |Store2| Store3
Item 1 | 1 | 0 | 5
Item 2 | 3 | 11 | 1
Item 3 | 4 | 0 | 0

Beside the table, I have the stock coverage table. (Stock coverage is basically the values in the table, divided by how fast that item sells from that location. So it gives you an idea when you will run out, in months).

I need something that will re-order the items, to minimize the difference of the coverage across rows, so all coverage is as close to uniform as possible, by modifying the values in the table.

Then I need the changes to be output in the following format: ITEM FROM TO QTY.
So for example, "Item3 Store1 Store2 1" would transfer 1 item3 from store1 to store2.
Please note, the item can be sent from one location, to many, or from many locations, to one.

Obviously this is a multi-part question, so if anyone can offer help on any of the components mentioned here, I would appreciate it. I already have, to an extent an "ideal distribution" table I've built, so the main problem now is oddly enough the formatting into "Item/to/from/qty" aspect.

Any ideas?

This should be very straightforward if you post an example of your data.

Hi all.

Yes, I want to even out the variables across all stores, however, it's the coverage I want balanced, not the variable itself.

It doesn't matter to me that each store have 5 trainers. If one store sells 5 trainers in a day, and one sells 1, I need the first store to have 5 times as many.

Is it that you want to spread the stock in such a way that all 20 Stores run out of each item at the same time?

Essentially. The reasoning being you don't want to overstock one, sitting on unsold stock that other stores are trying to sell but are out of.

If so, then your stock coverage figure will not be of relevance, but rather sales from each Store.

I doubt that, the stock coverage is per store, and has sales and stock as its variables. Stock coverage here being stock/sales gives you the estimated amount of time before your stock runs out, and that's what I want to balance.

You can recreate the coverage without using the additional cells though, that should be pretty straightforward. The data I get from the system is from a date to a date, what stock we ended on, and what sales we did across that period, per item.

I've included the file how I receive it (with identity information scrubbed, and with all null data removed. The file starts at 170MB, but it's all product information, not store or stock information).

I usually pivot it before working on it with "Item number" in the rows, Values then location in the columns, then Stock and sales in the Measures.

Right now I'm using conditional formatting running off of a stock coverage table to identify problem areas and rectify them manually.

1. What's the significance of warehouse versus store? Is it assumed that warehouses will empty their stock toward the stores that require them?

2. How is that sales is always an integer? Averaged, it is surely the case that stores sell 1.3 or 2.7 units per day during days when the product is in stock.

3. Similarly, the sales qty for many stores is zero -- those would just get completely taken out, no?