Need help, stuck with a Linear programing exercice using solver!

wadinho

New Member
Joined
Oct 9, 2016
Messages
3
Hi guys,

I am trying to solve this problem using solver but I am so stuck. There are so many constraints that I don't even know where to start from
frown.gif
. Can you please give some guidance ?

here is the problem:

The Monkey Bagels Biscuit Company produces *******s, Cookies and Bagels. It has outlets in Jersey and York. It is the company's policy that its production must satisfy the demand at the outlets. The monthly demand for the products (in thousand pounds) are:

Jersey York

*******s 20 30

Cookies 50 25

Bagels 30 20

The company has two production facilities, one in Cranium and one in Medulla. Each facility can make each product.

Production involves two key operations ---baking and packing. The oven capacity at the Cranium and Medulla bakeries are, respectively, 150 and 100 thousand pounds (of raw ingredients fed in) per month. Each facility uses its own packing line, which packs the *******s and Bagels that it bakes. Cookies are shipped in bulk; they need not be packed. The capacities of the packing lines in Cranium and Medulla are, respectively, 80 and 50 thousand pounds per month. This capacity is measured in the quantity processed through the packing line.

The baking line in Cranium is not automated, and an agreement has been reached with the Cranium workers that at least 25 percent of its baking (as measured by oven usage) must be devoted to Cookies, which are more labor intensive than *******s and Bagels.

The shipping cost is the same for all products. The cost (in dollars) per thousand pounds shipped from the bakeries to the outlets is given below:

Jersey York

Cranium 15 30

Medulla 20 10

The production costs (in dollars per thousand pounds of raw ingredients fed) are:

Cranium Medulla

*******s 5 4

Cookies 6 7

Bagels 5 5

The Operations Manager of the Monkey Bagels Biscuit Company must design a production/distribution plan so that the total monthly costs --- manufacturing and transportation costs --- are as low as possible.

The Operations Manager must decide how much of each product to manufacture at each bakery and how much to ship to each outlet.

(a) Formulate a linear program to solve this problem. Define your decision variables and explain the constraints.

(b) Solve the linear program.
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

mjbeam

Well-known Member
Joined
Apr 6, 2002
Messages
756
This should help you get started:

Code:
0 <= F1CrJ <=20			'Cranium *******s for Jersey
0 <= F1CoJ <= 50		'Cranium cookies for Jersey
0 <= F1BJ <= 30			'Cranium bagels for Jersey
0 <= F1CrY <= 30		'Cranium *******s for York
0 <= F1CoY <= 25		'Cranium cookies for York
0 <= F1BY <= 20			'Cranium bagels for York
0 <= F2CrJ <=20			'Medulla *******s for Jersey
0 <= F2CoJ <= 50		'Medulla cookies for Jersey
0 <= F2BJ <= 30			'Medulla bagels for Jersey
0 <= F2CrY <= 30		'Medulla *******s for York
0 <= F2CoY <= 25		'Medulla cookies for York
0 <= F2BY <= 20			'Medulla bagels for York

F1CrJ+F2CrJ = 20		'Jersey *******s needed
F1CoJ+F2CoJ = 50		'Jersey cookies needed
F1BJ+F2BJ = 30			'Jersey bagels needed
F1CrY+F2CrY = 30		'York *******s needed
F1CoY+F2CoY = 25		'York cookies needed
F1BY+F2BY = 20			'York bagels needed

F1J = F1CrJ+F1CoJ+F1BJ		'Cranium products for Jersey
F1Y = F1CrY+F1CoY+F1BY		'Cranium products for York
F1T = F1J+F1Y <= 150		'Cranium product total and constraint ***
F1CoJ+F1CoY >= F1T/4		'Cranium secondary product constraint ***
F2J = F2CrJ+F2CoJ+F2BJ		'Medulla products for Jersey
F2Y = F2CrY+F2CoY+F2BY		'Medulla products for York
F2T = F2J+F2Y <= 100		'Medulla product total and constraint ***

S1J = 15*(F1CrJ+F1CoJ+F1BJ)	'Cranium shipping cost to Jersey
S1Y = 30*(F1CrY+F1CoY+F1BY)	'Cranium shipping cost to York
F1CrJ+F1BJ+F1CrY+F1BY <= 80	'Cranium packing constraint ***
S2J = 20*(F2CrJ+F2CoJ+F2BJ)	'Medulla shipping cost to Jersey
S2Y = 10*(F2CrY+F2CoY+F2BY)	'Medulla shipping cost to York
F2CrJ+F2BJ+F2CrY+F2BY <= 50	'Medulla packing constraint ***
ST = S1J+S1Y+S2J+S2Y		'Total shipping cost

P1Cr = 5*(F1CrJ+F1CrY)		'Cranium *******s production cost
P1Co = 6*(F1CoJ+F1CoY)		'Cranium cookies production cost
P1B = 5*(F1BJ+F1BY)		'Cranium bagels production cost
P1T = P1Cr+P1Co+P1B		'Cranium total production cost
P2Cr = 4*(F2CrJ+F2CrY)		'Medulla *******s production cost
P2Co = 7*(F2CoJ+F2CoY)		'Medulla cookies production cost
P2B = 5*(F2BJ+F2BY)		'Medulla bagels production cost
P2T = P2Cr+P2Co+P2B		'Medulla total production cost
PT = P1T+P2T			'Total production cost

T = PT+ST			'Total cost
 
Last edited:

wadinho

New Member
Joined
Oct 9, 2016
Messages
3
This should help you get started:

Code:
0 <= F1CrJ <=20            'Cranium *******s for Jersey
0 <= F1CoJ <= 50        'Cranium cookies for Jersey
0 <= F1BJ <= 30            'Cranium bagels for Jersey
0 <= F1CrY <= 30        'Cranium *******s for York
0 <= F1CoY <= 25        'Cranium cookies for York
0 <= F1BY <= 20            'Cranium bagels for York
0 <= F2CrJ <=20            'Medulla *******s for Jersey
0 <= F2CoJ <= 50        'Medulla cookies for Jersey
0 <= F2BJ <= 30            'Medulla bagels for Jersey
0 <= F2CrY <= 30        'Medulla *******s for York
0 <= F2CoY <= 25        'Medulla cookies for York
0 <= F2BY <= 20            'Medulla bagels for York

F1CrJ+F2CrJ = 20        'Jersey *******s needed
F1CoJ+F2CoJ = 50        'Jersey cookies needed
F1BJ+F2BJ = 30            'Jersey bagels needed
F1CrY+F2CrY = 30        'York *******s needed
F1CoY+F2CoY = 25        'York cookies needed
F1BY+F2BY = 20            'York bagels needed

F1J = F1CrJ+F1CoJ+F1BJ        'Cranium products for Jersey
F1Y = F1CrY+F1CoY+F1BY        'Cranium products for York
F1T = F1J+F1Y <= 150        'Cranium product total and constraint ***
F1CoJ+F1CoY >= F1T/4        'Cranium secondary product constraint ***
F2J = F2CrJ+F2CoJ+F2BJ        'Medulla products for Jersey
F2Y = F2CrY+F2CoY+F2BY        'Medulla products for York
F2T = F2J+F2Y <= 100        'Medulla product total and constraint ***

S1J = 15*(F1CrJ+F1CoJ+F1BJ)    'Cranium shipping cost to Jersey
S1Y = 30*(F1CrY+F1CoY+F1BY)    'Cranium shipping cost to York
F1CrJ+F1BJ+F1CrY+F1BY <= 80    'Cranium packing constraint ***
S2J = 20*(F2CrJ+F2CoJ+F2BJ)    'Medulla shipping cost to Jersey
S2Y = 10*(F2CrY+F2CoY+F2BY)    'Medulla shipping cost to York
F2CrJ+F2BJ+F2CrY+F2BY <= 50    'Medulla packing constraint ***
ST = S1J+S1Y+S2J+S2Y        'Total shipping cost

P1Cr = 5*(F1CrJ+F1CrY)        'Cranium *******s production cost
P1Co = 6*(F1CoJ+F1CoY)        'Cranium cookies production cost
P1B = 5*(F1BJ+F1BY)        'Cranium bagels production cost
P1T = P1Cr+P1Co+P1B        'Cranium total production cost
P2Cr = 4*(F2CrJ+F2CrY)        'Medulla *******s production cost
P2Co = 7*(F2CoJ+F2CoY)        'Medulla cookies production cost
P2B = 5*(F2BJ+F2BY)        'Medulla bagels production cost
P2T = P2Cr+P2Co+P2B        'Medulla total production cost
PT = P1T+P2T            'Total production cost

T = PT+ST            'Total cost


thank you man! I am gonna try to solve it!
 

wadinho

New Member
Joined
Oct 9, 2016
Messages
3
I have a problem with this line: F1CoJ+F1CoY >= F1T/4 I can't do that
 

mjbeam

Well-known Member
Joined
Apr 6, 2002
Messages
756
It's a check. If F1CoJ+F1C0Y >= F1T/4 then the constraint is met.
 

mjbeam

Well-known Member
Joined
Apr 6, 2002
Messages
756
Here is a hint. Calculate all of the unique unit costs. There are 12 of them. For example, the unit cost for Cranium *******s shipped to Jersey is:

Cranium *******s to Jersey unit cost = Cranium *******s production cost + Cranium to Jersey Shipping cost

F1CrJ_UnitCost = 5 * 15
F1CrJ_UnitCost = 75

If you do this for all the combinations you should notice something. Find the lowest cost where all the constraints are met.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,129,568
Messages
5,637,094
Members
416,957
Latest member
Brovashift

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top