# Reallocating Apparel Stock between Stores

1. ## Reallocating Apparel Stock between Stores

Hi Everyone,

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.

Rank.JPG

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.

Kind regards,

Damian

2. ## Re: Reallocating Apparel Stock between Stores

A picture is worth a thousand words, but a sample spreadsheet would really help the process. I get the feeling that this business requirement could be much better handled by a database (with transactions for sales, returns, restocks and moves), than a spreadsheet. However, I am sure you are "stuck" with Excel for this project, so we will see what we can do .

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1