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

##### New Member
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
. 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

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

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

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

#### mjbeam

##### Well-known Member

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:

##### New Member

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!

##### New Member
I have a problem with this line: F1CoJ+F1CoY >= F1T/4 I can't do that

#### mjbeam

##### Well-known Member
It's a check. If F1CoJ+F1C0Y >= F1T/4 then the constraint is met.

#### mjbeam

##### Well-known Member
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:

Replies
1
Views
6K
Replies
2
Views
4K
Replies
1
Views
948

1,132,686
Messages
5,654,754
Members
418,151
Latest member
shukoor

### 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.

### Which adblocker are you using?

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

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