A steel production plant manufactures bands and coils which sell at a profit of $25 and $30 per ton, respectively. The production rate of the plant for bands is 200 tons/hr, and that for coils is 140 tons/hr. Based on the market analysis, it has been identified that the weekly demand is at most 6000 tons and 4000 tons for bands and coils, respectively . The plant operates for a maximum of 40 hours per week. 1. In Microsoft Excel, formulate a linear programming model instance for this problem, then solve. 2. Assume that the products need to be painted after manufacturing, and the painting department can paint at a rate of 600 tons per hour for bands and 400 tons per hour for coils. The painting department works only for 20 hours. Update the linear programming model instance to accom- modate this requirement, and re-solve the problem. 3. The plant is planning to introduce another product “rods” to its production mix with the following parameters: •Profit/ton = $40 •Production volume = 150 ton/hr. •Processing volume in the painting department = 350 ton/hr. •Maximum amount = 4000 ton Write a new linear programming model instance in a new Excel file. Is it profitable to include this new product for production?
A steel production plant manufactures bands and coils which sell at a profit of $25 and $30 per ton,
respectively. The production rate of the plant for bands is 200 tons/hr, and that for coils is 140 tons/hr.
Based on the market analysis, it has been identified that the weekly demand is at most 6000 tons and
4000 tons for bands and coils, respectively . The plant operates for a maximum of 40 hours per week.
1. In Microsoft Excel, formulate a linear programming model instance for this problem, then solve.
2. Assume that the products need to be painted after manufacturing, and the painting department
can paint at a rate of 600 tons per hour for bands and 400 tons per hour for coils. The painting
department works only for 20 hours. Update the linear programming model instance to accom-
modate this requirement, and re-solve the problem.
3. The plant is planning to introduce another product “rods” to its production mix with the following
parameters:
•Profit/ton = $40
•Production volume = 150 ton/hr.
•Processing volume in the painting department = 350 ton/hr.
•Maximum amount = 4000 ton
Write a new linear programming model instance in a new Excel file. Is it profitable to include this new product for
production?
In the question, for each case, we would first formulate the LP, then, we would solve this LP with the help of Ms Excel Solver, here, we are running the plant to maximize the total profit value, so, we would determine the total tonnage for bands and coils that would further maximize the total profit value.
1)
First, let me formulate the LP model,
Decision variables:
Total tonnage for bands=X1
Total tonnage for coils=X2
Objective function:
we would maximize the total profit value for the bands and coils, hence, Max Z=25*X1+30*X2
Constraints:
Given that the production rate for bands=200 tons/hour, here, for each ton, Hours would be 0.005 and the production rate for coils=140 tons/hour, here, for each ton, Hours would be 0.007143
Weekly Hours: 0.005*X1+0.007143*X2=<40
Weekly demand for bands: X1=<6000
Weekly demand for coils: X2=<4000
X1 and X2>=0 (non-Negativity)
The solution for this LP formulation is given in the next section:
Trending now
This is a popular solution!
Step by step
Solved in 4 steps with 15 images
