Question

A company has three factories in Chicago, Kansas City, and Houston where it produces its products and from where it can weekly supply its three distribution centers located in New York, Los Angeles, and Atlanta. The supply capacities at the three factories, the demand requirements at each of the three distribution centers, and the transportation costs in $ per ton from each factory to each distribution center are shown in the table below. Formulate a linear programming (LP) model of this transportation problem with the objective of minimizing total shipping cost. I am asking for the LP model formulation only in question

1. Factories Distribution Centers Total Supply New York Los Angeles Atlanta Chicago $8 $5 $6 120 tons Kansas City $15 $10 $12 80 tons Houston $3 $9 $10 80 tons Total Demand 150 tons 70 tons 60 tons

2. Solve the LP model you formulated in question 1, using Excel Solver. Information and steps for Excel Solver are provided in one the attached document on Blackboard in this module. You are to submit your answer for question 2 of this Assignment, as in the solution summary shown in the attached document on Blackboard containing information and steps for Excel Solver, as well as submit the Excel file containing both the Answer Report and Sheet1 spreadsheet.

Answer 1

We tabulate the given data in excel as shown below:

As seen from above, total supply = total demand = 280.

Let the no. of products transported from Chicago to New York be x11, Chicago to Los Angeles be x12 and so on. We get the decision variables as per the table shown below:

Total Cost of transportation = 8*x11 + 5*x12 + 6*x13 + 15*x21 + 10*x22 + 12*x23 + 3*x31 + 9*x32 + 10*x33

We have to minimize this cost

Hence, we get the Objective function as:

Minimize Total Cost C = 8*x11 + 5*x12 + 6*x13 + 15*x21 + 10*x22 + 12*x23 + 3*x31 + 9*x32 + 10*x33

Subject to Constraints:

Supply Constraint:

x11 + x12 + x13 = 120

x21 + x22 + x23 = 80

x31 + x32 + x33 = 80

Demand constraint:

x11 + x21 + x31 = 150

x12 + x22 + x32 = 70

x13 + x23 + x33 = 60

x11, x12, x13, x21, x22, x23, x31, x32, x33 >= 0..............Non-negativity constriant as no. of products transported cannot be negative.

2. We find the optimum quantity of products transported from each factory to distribution centers to minimize total cost using Excel Solver as shown below:

The above solution in form of formulas and excel solver extract is shown below for better understanding and reference:

Chicago to New York = 70 units

Chicago to Los Angeles = 0 units

Chicago to Atlanta = 50 units

Kansas City to New York = 0 units

Kansas City to Los Angeles = 70 units

Kansas City to Atlanta = 10 units

Houston to New York = 80 units

Houston to Los Angeles = 0 units

Houston to Atlanta = 0 units

_______________________________________________________________________________________

In case of any doubt, please ask through the comment section before Upvote/downvote.