Question

Problem 2. (10 points) Froberg Aerospace LLC. has three plants producing small-sat propulsion units that are to be shipped to four distribution centers. Plants 1, 2, and 3 produce 12, 17, and 11 shipments per month, respectively Each distribution center needs to receive 10 shipments per month. The distance from each plant to the respective distribution centers is given below Distance to Distribution Center Miles Distribution Center1 800 1,100 600 Distribution Center2 1,300 1,400 1.200 Distribution Center 3 400 600 800 Distribution Center 4 700 1,000 900 Plant 3 The freight cost for each shipment is $100 plus 50 cents/mile How much should be shipped from each plant to each of the distrībution centers to minimize the total Shipping cost? a. Formulate this problem as a LP problem in algebraic form. b. Formulate this LP model as a transportation problem on a spreadsheet in Excel and then use Solver to obtain an optimal solution. Answer the aforementioned question on the quantity shipped from each plant to each distribution center c.

Answer 1

There are nine options of distributing and we have to minimize the transport cost:

Let the number of shipments distributed from plant 1 to Dist center 1, Plant 1 to Dist center 2....and so on be X₁₁, X₁₂,X₁₃,X₁₄,X₂₁,X₂₂,X₂₃,X₂₄,X₃₁,X₃₂,X₃₃,X_34. This is in form of X_ij where i=Plant number and j=Distribution center number. The cost for 1 shipment per combination is as shown below (Formulae in table 1.1, costs in table 1.2)

Table 1.1

Table 1.2

We have to minimize total cost of distribution. Cost of distribution is $\sum$ Shipments_ij x cost. Hence, The objective function is:

Minimize 500X₁₁+750X₁₂+300X₁₃+450X₁₄+650X₂₁+800X₂₂+400X₂₃+600X₂₄+400X₃₁+700X₃₂+500X₃₃+550X₃₄

Each center needs 10 shipment per month, plant 1,2,3 produces 12, 17, 11 shipments respectively

Hence the constraints are:

X₁₁+X₂₁+X₃₁=10

X₁₂+X₂₂+X₃₂=10

X₁₃+X₂₃+X₃₃=10

X₁₄+X₂₄+X₃₄=10

X₁₁+X₁₂+X₁₃+X₁₄=12

X₂₁+X₂₂+X₂₃+X₂₄=17

X₃₁+X₃₂+X₃₃+X₃₄=11

Solving using Excel solver, we get

The optimal solution, quantity shipped from each plant to each distribution center is shown.

As seen, the total minimum cost is $20200 for optimum solution.