Question

1. Insomnia, a coffee bean broker, has 4 warehouses from which it can ship to 3 main buyers. The demand for coffee beans at buyer 1 is 800 pounds, at buyer 2 it is 1000, and at buyer 3 it is 500. Each warehouse holds an inventory of 2000 pounds of coffee beans. The warehouses can only ship coffee beans in full pounds. Given the transportation costs below, they need to determine how product should be shipped between the warehouses and the retailers in order to minimize total cost.

From	To Buyer	To Buyer	To Buyer
Warehouse	1	2	3
1	8	10	7
2	6	4	9
3	3	5	6
4	5	2	4

Transportation Costs ($)

a. Write a formulation for this problem, following the 4 Step approach. (Include this in your submission too)

b. Carry work over to Excel and solve via Excel Solver and report on findings:

• What is the total cost?

• How many units should be shipped between each warehouse and buyer pair?

Answer 1

From Warehouse	To Buyer 1	To Buyer 2	To Buyer 3
1	8	10	7
2	6	4	9
3	3	5	6
4	5	2	4

Question – a:

Step – 1 (Decision Variable):

We have to identify the decision variables.

Let the shipment volume warehouse i and buyer j is defined as xij.

From Warehouse 1 to Buyer 1: x11

From Warehouse 1 to Buyer 2: x12

From Warehouse 1 to Buyer 3: x13

From Warehouse 2 to Buyer 1: x21

From Warehouse 2 to Buyer 2: x22

From Warehouse 2 to Buyer 3: x23

From Warehouse 3 to Buyer 1: x31

From Warehouse 3 to Buyer 2: x32

From Warehouse 3 to Buyer 3: x33

From Warehouse 4 to Buyer 1: x41

From Warehouse 4 to Buyer 2: x42

From Warehouse 4 to Buyer 3: x43

Step – 2 (Constraints):

The total shipment from each warehouse should not exceed the capacity of the warehouse.

The capacity of each warehouse is 2000 pounds.

Hence,

x11 + x12 + x13 <= 2000

x21 + x22 + x23 <= 2000

x31 + x32 + x33 <= 2000

x41 + x42 + x43 <= 2000

The total receipt to each buyer should satisfy the demand of the buyer. This means the total shipment to each buyer should be equal to the demand of that buyer.

The demand of buyer 1 = 800 pounds

The demand of buyer 2 = 1000 pounds

The demand of buyer 3 = 500 pounds

x11 + x21 + x31 + x41 = 800

x12 + x22 + x32 + x42 = 1000

x13 + x23 + x33 + x43 = 500

Step – 3 (Objective Function):

The objective here is to minimize the cost of shipment.

The cost of shipment between each pair is:

From Warehouse	To Buyer 1	To Buyer 2	To Buyer 3
1	8	10	7
2	6	4	9
3	3	5	6
4	5	2	4

Hence the objective function is:

MAXIMIZE 8*x11 + 10*x12 + 7*x13 + 6*x21 + 4*x22 + 9*x23 + 3*x31 + 5*x32 + 6*x33 + 5*x41 + 2*x42 + 4*x43

Step – 4 (Solving):

We will solve this using excel. This is a transportation problem and hence we will solve this using Simplex Method.

Question – b (solving using excel):

We are using transportation problem

Here the minimized cost is the total multiplication between the cost for each pair and volume of each pair. The formula used is: =SUMPRODUCT(D7:F10,D17:F20)

The total supplied is sum of all the shipment for a ware house.

The total demand satisfied is all the shipment received by each buyer.

Solver Parameters:

Optimal Solution:

From Warehouse	To Buyer 1	To Buyer 2	To Buyer 3
1	0	0	0
2	0	0	0
3	800	0	0
4	0	1000	500

Units shipped from warehouse to buyer:

Warehouse 1 to Buyer 1: 0

Warehouse 1 to Buyer 2: 0

Warehouse 1 to Buyer 3: 0

Warehouse 2 to Buyer 1: 0

Warehouse 2 to Buyer 2: 0

Warehouse 2 to Buyer 3: 0

Warehouse 3 to Buyer 1: 800

Warehouse 3 to Buyer 2: 0

Warehouse 3 to Buyer 3: 0

Warehouse 4 to Buyer 1: 0

Warehouse 4 to Buyer 2: 1000

Warehouse 4 to Buyer 3: 500

Hence, Buyer 1 receives the whole demand (800 pounds) from warehouse 3. Buyer 2 receives the whole demand (1000 pounds) from warehouse 4. Buyer 3 receives the whole demand (500 pounds) from warehouse 3.

The total cost is: $6400

IF YOU LIKE THE ANSWER, PLEASE GIVE AN UP-VOTE OR THUMB UP. THIS WILL ENCOURAGE ME TO ANSWER MORE!!