Optimum Batch Size Selection

[Narender Sagar]

Dear All
I need to create a solution for following :
1. I have a Product Master Table in which, I have fields like ProductID, Product Name etc.
2. I have created another table for Batch Size (One to Many relationship) as my one ProductID can have multiple batch size. (But these are standard batch sizes on which the number of batches to produced to be calculated)
3. I have Demand Table in which I have ProductID and the Market Demand (along with Demand Month/Date etc.)
The situation is as follows: A Product "X" can have 3 standard batch size (in BatchSize Master) e.g.
(i) 100000, (ii) 500000 and (iii) 1000000 nos.
Suppose my demand is
a) from 1 to 400000 nos. I want, system should propose to make as many number of batches of 100000 each (depending on nearest figure)
b) If the demand is between 400000 to 500000, the system can propose to make one batch of 500000 units.
c) If my demand is 600000 units, system should propose one batch of 500000 units and 2nd batch of 100000 units.
d) Similarly, If the demand is 700000 units, system can propose to make one batch of 500000 units and 2 batches of 100000 units.
e) If demand is between 900001 to 1000000 units, system can propose to make 1 batches of 1000000 units.
and so on and so forth.
Like wise Product "Y" can have altogether different batch size in BatchSize Master.

I need your support to resolve this.
Please advise..
Thanks

[Narender Sagar]

Hi All
May I request someone to look into the case..
I am so hopeful from this group and I'm sure I'll get some quality response here.
Once again I'd like to bring attention of experts in this group..as this is very important project for me.
Thanks

[PhilOfWalton]

We seem to get a lot of questions from you, and with respect, I think that you ask Bytes before you have done any thinking.

I will point you on the right track - the rest is up to you.

Is the demand >= maximum batch size -
YES Add 1 to the large Bach size counter Deduct the Large Batch size and repeat until the answer is NO

Do the same thing for the Second largest Batch Size.

I leave you to work out what to do after you have process the smallest batch Size

Phil

[Narender Sagar]

Thanks Phil..
I Will keep this in mind.

[Oralloy]

Narender,

Your question looks interesting, but it is difficult to do anything without seeing what you have done.

Will you please post your code when asking questions, so that some of us who are a bit slow-witted are better able to understand? I am having a bit of difficulty understanding your question, because I cannot envision the table structure and example data...

Thanks,
Oralloy

[Narender Sagar]

Dear Oralloy

First of all, sorry for late response and thanks for showing interest in question.
I've thought about the logic I need to apply. which is follows:
(Though, I'm open for further suggestion)
So system will check Maximum BatchSize and compare it with the demand. If MaxBatchSize is less than or equal to Demand, Value if true : Reduce it from Demand and count and store the BalanceDemand in Temp.Table, Value if False : check for next available BatchSize (Check if Next Available BatchSize is less than or equal to Demand or BalanceDemand and so on ;
I think I need to use following syntax:

Do { While | Until } condition
    [ statements ]
    [ Continue Do ]
    [ statements ]
    [ Exit Do ]
    [ statements ]
Loop
-or-
Do
    [ statements ]
    [ Continue Do ]
    [ statements ]
    [ Exit Do ]
    [ statements ]
Loop { While | Until } condition
 

But I don't know what exactly should be my code

May I request to move further..

Thanks

[Oralloy]

Narender Sagar,

My preference is for the top driven loop form, because when one needs to analyse a program, one sees the loop control prior to analysing the content. Yes, the bottom driven loop has coding efficiency, I agree. Use of "Structured Coding" is about the ability to reason about code, so take it for what you will.

Your pseudo-code, rewritten slightly:

So system will check Maximum BatchSize and compare it with the demand. 
 
If MaxBatchSize is less than or equal to Demand, 
  Value if true : Reduce it from Demand and count and store the BalanceDemand in Temp.Table, 
Else
  Value if False : check for next available BatchSize (Check if Next Available BatchSize is less than or equal to Demand or BalanceDemand and so on )

Let's simplify that a little bit and remove the editorialising:

Run MaxBatchSize batches until demand is less than MaxBatchSize
 
Run Batches of decreasing size until required production is complete.
 

In the end, you might end up with something like this:

Dim BatchSize(0 To NumberOfBatchSizes) As Long
BatchSize(0) = ...
BatchSize(1) = ...
BatchSize(2) = ...
etc.
 
Dim MaxBatchSize As Long
Let MaxBatchSize = BatchSize(Ulimit(BatchSize, 1))
 
Dim DemandRemaining As Long
Let DemandRemaining = Demand
 
While DemandRemaining > MaxBatchSize Do
  Store MaxBatchSize in TempTable
  Let DemandRemaining = DemandRemaining - MaxBatchSize
Loop
 
While DemandRemaining > 0 Do
  For BatchIndex = LBound(BatchSize, 1) to UBound(BatchSize, 2)
    If BatchSize(BatchIndex) > DemandRemaining Then
      Store BatchSize(BatchIndex - 1) in TempTable
      Let DemandRemaining = DemandRemaining - BatchSize(BatchIndex - 1)
      Exit For
    End If
  Next
Loop
 

I would consider compressing both loops into one, but it is not necessary.

This is fine, if batch size is inversely proportional to production cost per unit. Are you trying to perform total optimisation, or just good-enough optimisation? For example, if our customer wants 103 units, that might be a batch of 100, and three batches of 1, while it is less expensive to produce a batch of 100, and a batch of 10, and just discard 7 units.

--Oralloy