Question

Solve the following LP problems using the Solver in MS Excel.

Q1.      A candy manufacturer has 130 pounds of chocolate-covered cherries and 170 pounds of chocolate-covered mints in stock. He decides to sell them in the form of two different mixtures. One mixture will contain half cherries and half mints by weight and will sell for $2.00 per pound. The other mixture will contain one-third cherries and two-thirds mints by weight and will sell for $1.25 per pound. How many pounds of each mixture should the candy manufacturer prepare in order to maximize his sales revenue?

Q2.      A company which manufactures canoes employs 120 employees, each of whom working 30 hours per week. Half of them work in the carpenter department, 20 persons in the plastics department, and the rest of them at the completion department. The company manufactures the simple canoes with net unit profit € 7 and the luxury canoes with corresponding profit € 10. A simple canoe requires 4.5 hours in the carpenter department and two hours in each of the other two departments. The working hours for each luxury canoe are 5, 1 and 4 at the carpenter department, plastics department and completion department respectively. Marketing calculations have shown that not less than 1/3 and not more than 2/3 of the total number of the canoes should be luxurious. How will the company maximize its overall net profit?

Q3.      A municipality has two incinerators for burning trash . Incinerator A costs $3 .80 per ton of trash to operate, and has a capacity of 28 tons per day . Incinerator B costs $4 .25 per ton to operate, and has a capacity of 30 tons per day . The municipality produces over 100 tons of trash per day, and all trash not burned in the incinerators must be buried in a land fill at a cost of $5 .00 per ton . The city manager wants to minimize costs by burning as much trash as possible . However, the city must conform to environmental regulations limiting production of pollutants from burning in the incinerators to 180 pounds of hydrocarbons and 640 pounds of particulates a day . Incinerator A produces 3 pounds of hydrocarbons and 20 pounds of particulates for every ton of trash burned, and incinerator B produces 5 pounds of hydrocarbons and 10 pounds of particulates for every ton of trash . Determine the optimum amount of trash to burn in each incinerator.

Q4.      A farmer wants to customize his fertilizer for his current crop. He can buy plant food mix A and plant food mix B. Each cubic yard of food A contains 20 pounds of phosphoric acid, 30 pounds of nitrogen and 5 pounds of potash. Each cubic yard of food B contains 10 pounds of phosphoric acid, 30 pounds of nitrogen and 10 pounds of potash. He requires a minimum of 460 pounds of phosphoric acid, 960 pounds of nitrogen and 220 pounds of potash. If food A costs $30 per cubic yard and food B costs $35 per cubic yard, how many cubic yards of each food should the farmer blend to meet the minimum chemical requirements at a minimal cost? What is this cost?

Q5. A dietician wishes to mix two types of foods in such a way that vitamin contents of the mixture contain atleast 8 units of vitamin A and 10 units of vitamin C. Food ‘I’ contains 2 units/kg of vitamin A and 1 unit/kg of vitamin C. Food ‘II’ contains 1 unit/kg of vitamin A and 2 units/kg of vitamin C. It costs Rs 50 per kg to purchase Food ‘I’ and Rs 70 per kg to purchase Food ‘II’. Formulate this problem as a linear programming problem to minimise the cost of such a mixture.

Answer 1

Q1.

solver model:

Solver solution:

1 01) 2 candy manufacturer cherries mints selling price #lbs | Solver Parameters 4 5 6 Lbs in stock Mix 1 Mix 2 130 170 0.5 0.5 $ 0.33 0.67$ Set Target Cell: EES3 2.00 1.25 Equal To: OMax Min Value of: 0 By Changing Cells: 8 Revenue SE$5:$E$6 Subject to the Constraints Options 10 s.t. $A$11:$A$12 <= $C$11:SC$12 130 cherries stock 170 mints stock 12 13 14 15 16 Reset All Delete

Optimal solution
Mix	pounds prepared
1	260
2	0
Optimal Revenue	$ 520.00