+ Reply to Thread
Results 1 to 4 of 4

Help! Formula(s) to distribute stock among stores

  1. #1
    Registered User
    Join Date
    09-28-2020
    Location
    Australia
    MS-Off Ver
    2016
    Posts
    2

    Help! Formula(s) to distribute stock among stores

    Hi,
    I've spent hours now trying to work out a way to do this, have tried countless options but can't get it to work correctly.
    What I want to achieve:
    I have X amount of stock to distribute amongst 4 stores. I want to distribute this stock in such a way as to bring each stores days on hand to as even as possible. i.e. I don't want one store to have 6 months worth of stock and another only has 1 weeks worth.
    I've created a basic spreadsheet to try and illustrate this.
    SOH = Stock on Hand at store already
    Expected = How much the store sells per 30 days
    DOH = Days on hand, showing how many days their current SOH would last them

    In the example I have 100 of an imaginary product X I wish to distribute.

    (Note that I don't want to redistribute existing stock at stores, only the new 100 I have coming in), so a store who already has plenty may not get any or very little of the new stock)

    I would very much appreciate any help with this
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    23,753

    Re: Help! Formula(s) to distribute stock among stores

    each stores days on hand to as even as possible
    The key to this is the minimum stock level that a given store should maintain, not as you suggest, even stock in all stores: for example, if a store should hold 30 days stock (minimum/safe stock level) , then if it currently has 20, allocate 10 days worth of stock.

    Another (perhaps the key) factor is lead time to obtain stock. There is plenty of information of the web on stock control, including Excel-based templates.

  3. #3
    Registered User
    Join Date
    09-28-2020
    Location
    Australia
    MS-Off Ver
    2016
    Posts
    2
    Quote Originally Posted by JohnTopley View Post
    The key to this is the minimum stock level that a given store should maintain, not as you suggest, even stock in all stores: for example, if a store should hold 30 days stock (minimum/safe stock level) , then if it currently has 20, allocate 10 days worth of stock.

    Another (perhaps the key) factor is lead time to obtain stock. There is plenty of information of the web on stock control, including Excel-based templates.
    Thanks for replying. A further piece of information which ties into both your points, is that this stock that I'm trying to distribute is stock I already have from a store closing down. So lead time on stock is not relevant in this case.

    As for min stock levels, yes 30 days is indeed what they would trade at but in this case I need to distribute the stock I already have and thus it may put some or all stores above 30 days, this is fine, I just want the stock to be distributed evenly according to their usage (or perhaps better to say distributed in such a way as to not overstock a particular store)
    One idea I had was to rank each store based on DOH with rank 1 being the lowest DOH, then allocate them 1 unit then check again which store had rank 1 then allocate that store 1 unit and so on and so on until all of the units had been allocated. I just couldn't get that to work formula/macro wise
    Last edited by timma000; 05-18-2021 at 03:02 AM.

  4. #4
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2010 on Linux - O365
    Posts
    12,732

    Re: Help! Formula(s) to distribute stock among stores

    Administrative Note:

    Please don't quote entire posts unnecessarily. They clutter threads and make them hard to read.
    Use the "Quick reply" instead
    Thanks

+ 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: 1
    Last Post: 04-26-2020, 09:29 AM
  2. Replies: 1
    Last Post: 04-11-2019, 12:17 PM
  3. Reallocating Apparel Stock between Stores
    By DamianTaylor in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-24-2016, 11:00 AM
  4. Replies: 1
    Last Post: 05-23-2016, 10:23 PM
  5. [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
  6. [SOLVED] Stock Movement for too many stores ?
    By makinmomb in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-28-2015, 08:37 AM
  7. [SOLVED] Distribute stock to requirements until it becomes zero.
    By sbabu16 in forum Excel General
    Replies: 2
    Last Post: 12-17-2013, 08:52 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