Instructions: use excel solver to resolve the following problems. You are to sub

Question

Instructions: use excel solver to resolve the following problems. You are to submit an excel spreadsheet with the solver models and solutions. Problem 1: Steelco manufactures three types of steel at different plants. The time required to manufacture I ton of steel (regardless of type) and the cost at each plant are shown in the table below Each week, 100 tons of each type of steel (1, 2 and 3) must be produced. Each plant is open 40 hours per week. Formulate a balanced transportation problem to minimize the cost of meeting Steelco's weekly requirements.

Explanation / Answer

ANS;

We are given demand for each type of steel in units of tons. The capability of each plant is given in minutes. We must convert capability of each plant to units of tons so that we can solve.

40 hour work week
100 tons of each steel type per week

40hrs*(60mins/hr)=2400mins for work

Plant 1:
2400min/(20mins/ton)=120tons

Plant 2:
2400min/(16mins/ton)=150tons

Plant 3:
2400min/(15mins/ton)=160tons

At this point it does not matter which plant produces which type of steel.

Total Supply: 430 tons
Total Demand: 300 tons
Initial Graphical Representation

Add Dummy Demand point

Capacity: 130 tons excess supply

Mathematical Representation

Supply constraints

Demand constraints

From Plant1 to Steel1 = 100*60 -> 6000
From Plant1 to Steel2 = 20*40 -> 800
From Plant2 to Steel2 = 80*30 -> 2400
From Plant2 to Steel3 = 70*30 -> 2100
From Plant3 to Steel3 = 30*20 -> 600
From Plant3 to Dummy = 130*0 -> 0

Total Cost = 11900

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.