# Thread: Inventory Optimization using Lot Size and other Conditions

1. ## Inventory Optimization using Lot Size and other Conditions

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.

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:

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

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