optimization problem

dear community.
I was wondering if you might be able to help.
I have a product that has two alternative BOMs, which excel formula or method I can use to maximize output for each batch?
please see attached sample spreadsheet, thanks a lot!

Sorry but also posted on Mrexcel forum as I am really stuck.

Is recipe 1 & 2 of product A constant or variable?
If variable, is it always A<C in rec1 and B=C in rec2?

Assuming there are only 2 components in every recipe
Recipe is sorted base qty as per example file.
With F1 is batch criteria

Please Login or Register  to view this content.

with red part is for recipe1, the rest is for recipe2
Result is, for example, batch 10-001, the max prod volumn is 21333
Here is how the formula works:
Recipe1: Get the Companent C SOH (54000) / 2 =17000 to compare with Companent A SOH (10000) then take the smaller ==> 10000
Recipe2: To compare B (54000) and C (32000) then get the smaller, then divide by 1.5 ==>21333
MAX (rec1, rec2)=21333

Originally Posted by kema

Problem is to calculate what is the maximum amount of product A that can be produced using either recipe 1 OR recipe 2

Is it exactly what I did, isn't it?
If not, what is your expected result?

I used "Solver" to determine a solution of 31333 units for batch 10-001 with details per the picture below. It uses all of the SOH for A and B and all but 2000 units of C.

If you need to do this frequently or for numerous recipes it is not as practical as Bebo021999's formula based solution. Bebo021999 says 21333 vs my 31333, but I think our answers are essentially consistent - he has taken the max of recipe A & B, whereas I have taken the sum. OP can clarify.

kema.png
kama1.png

Details
-------

Objective: maximize H11 by changing E3 and E12, subject to the constraints of stock on hand - C2:C4.

Formulas
In E9: =E8*C9, in E10: =C10*E8
In E13: =E12*C13, in E14: =E12*C14
In H11: E8 + E12
In D2: =E9, in D3: =E13, in D4: =E10+E14