+ Reply to Thread
Results 1 to 2 of 2

Excel Function to Re-Allocate Stock across Stores based on size and style performance

  1. #1
    Registered User
    Join Date
    03-15-2016
    Location
    Melbourne
    MS-Off Ver
    XP
    Posts
    6

    Excel Function to Re-Allocate Stock across Stores based on size and style performance

    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. 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.

  2. #2
    Registered User
    Join Date
    03-15-2016
    Location
    Melbourne
    MS-Off Ver
    XP
    Posts
    6

    Re: Excel Function to Re-Allocate Stock across Stores based on size and style performance

    Excel Spreadsheet with Formulas Attached.
    Attached Files Attached Files

+ 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. Reallocating Apparel Stock between Stores
    By DamianTaylor in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-24-2016, 11:00 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. Replies: 1
    Last Post: 02-17-2015, 12:12 AM
  5. Array function to allocate total across accounts based on prioritization
    By lpcapital in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 10-29-2013, 04:56 PM
  6. Performance of a stock over 20 day rolling periods
    By yamar in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-22-2009, 04:26 AM

Bookmarks

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