+ Reply to Thread
Results 1 to 2 of 2

Reallocating Apparel Stock between Stores

  1. #1
    Registered User
    Join Date
    MS-Off Ver

    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.


    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,


  2. #2
    Forum Expert dflak's Avatar
    Join Date
    North Carolina
    MS-Off Ver

    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 .
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 31
    Last Post: 10-28-2015, 01:54 AM
  2. [SOLVED] What stock is not where for too many stores ?
    By makinmomb in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 08-10-2015, 04:37 AM
  3. [SOLVED] Stock Movement for too many stores ?
    By makinmomb in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-28-2015, 08:37 AM
  4. [SOLVED] Reallocating Values Based on Percentages and Weights
    By icraig8 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 11-20-2014, 05:26 PM
  5. Replies: 4
    Last Post: 01-29-2013, 02:26 AM
  6. find top 50% of stores
    By Xia in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-10-2012, 06:58 AM
  7. Replies: 0
    Last Post: 10-28-2012, 07:22 AM


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