1. ## Optimization Problem

Hi everyone,
First of all I was'nt sure where would be the most suitable place to submit this thread so I tried to play it safe by posting it here in Math Topics.

I'm facing a little optimization problem. I have 3 products, let's call them A B and C. Each of them require a certain amount of ressources.
A require 4 red blocks, 2 Yellow Blocks and 1 Blue block.
B : 1 Red, 4 Yellow and 2 Blue
C : 2 Red, 1 Yellow, 4 Blue
I would like to produce as many products as possible out of the ressource I own.

Could someone help me through this so I can get an algorythm that I can use in an Excel spreadsheet where I'll only have to insert the ressource I own and find out how many of each product I should create to get as many end product as possible.

Thank you very much

=SUM(A1:C1)
=4*A1 =B1 =2*C1
=2*A1 =4*B1 =C1
=A1 =2*B1 =4*C1
=SUM(A2:A4) =SUM(B2:B4) =SUM(C2:C4)
amt red amt yellow amt blue
=A6-A5 =B6-B5 =C6-C5

On the ribbon select solver target cell is d1 (max)
constraint a7>=0 b7>=0 c7>=0

Of course this is already the way to create the excel file. Unfortunately this does'nt really help me understand the math behind the formula. Also I'm having a hard time recreating this in Excel. I don't understand some part of it and why there is 1 solver case since I'm looking for 3 answers (how many A, B and C products to create.)

Thanks again.

2. ## Re: Optimization Problem

That's a "linear programming" problem- you want to optimize a linear "object function" subject to linear constraints.

Here, you wish to maximize "the number of products" which is A+ B+ C.

The number of "red blocks" used would be 4A+ 2B+ C, the number of "yellow blocks" used would be 2A+ 4B+ C, and the number of "blue blocks" would be A+ 2B+ 4C.
Now, you say you want to insert the "resource"- those would be the number of each such "blocks" and so would give three linear equations in those variables. If you were to graph those in a "ABC- coordinate system", they would be planes. The "feasible region", the region of all solutions that satisfy those constraints, must lie below all of those planes. Also the "object function", A+ B+ C= various constants would be various parallel planes.

The critical point of "linear programming" is that the optimum value of the linear "object function" must occur at a vertex of the "feasible region". So you need to set up your excel program to solve the the three equations simultaneously (because there are three equations in three variables, there will be a single solution- there are other vertices where the planes will meet the x= 0, y= 0, and z= 0 planes but those will clearly not give a maximum).

3. ## Re: Optimization Problem

Aww right!
Thank you very much, I forgot all about the feasible region but it's back now.
Have a nice day.