Results 1 to 2 of 2

Thread: Inventory Optimization using Lot Size and other Conditions

  1. #1
    Neophyte ggmkp's Avatar
    Join Date
    Jun 2019
    Posts
    2
    Articles
    0
    Excel Version
    2016

    Question Inventory Optimization using Lot Size and other Conditions



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I'm trying to add qty to Inventory (B5:G7) so that Supply meets Demand by first looking at the Storage with the lowest fruit count and adding the qty based on the lot size.

    Click image for larger version. 

Name:	Problem.png 
Views:	13 
Size:	42.8 KB 
ID:	9167

    Demand is manual input
    Supply is Sum(B5:B7), Sum(C5:C7)+B3 and on (cumulative)
    Total is Supply - Demand
    Storage table is used only to find the fruit with the lowest count. Then adding the qty based on the lot size
    Apple is 4, 8, 12, 16... Orange is 5, 10, 15, 20... and so on

    The result should look like this:

    Click image for larger version. 

Name:	Result.PNG 
Views:	15 
Size:	23.6 KB 
ID:	9168

    Again the goal is to add qty so that Total is equal or above 0, other way to think about this is to match or exceed demand
    I tried to first rank the fruits in Storage table with something like
    Code:
    IFERROR(INDEX(A15:A17,MATCH(MIN(C15:C17),C15:C17,0)),"")

    But not sure how I can add based on the lot size...

    I'm looking for a solution using formula, VBA, Solver or any other combination of excel features
    Or let me know if this is not possible in excel...

    Thank you

    Cross posts:
    https://www.mrexcel.com/forum/excel-...-lot-size.html

    https://chandoo.org/forum/threads/ad...ot-size.41929/


    Attached Files Attached Files
    Last edited by p45cal; 2019-06-19 at 11:25 AM. Reason: adjusted links

  2. #2
    Neophyte ggmkp's Avatar
    Join Date
    Jun 2019
    Posts
    2
    Articles
    0
    Excel Version
    2016
    Is there a way to edit my post? if not please delete this thread

Posting Permissions

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