Question

Slick oil company has 3 warehouses from which it can ship to 4 retailers. The demand at Retailer 1 is 350 cans, at Retailer 2 it is 100, at Retailer 3 it is 50 and at Retailer 4 it is 150 cans. Warehouse 1 has an inventory of 250 units, Warehouse 2 has an inventory of 150 units, and Warehouse 3 has an inventory of 300 units. Given the costs below, they need to determine how product should be shipped between the warehouses and the retailers.

From Warehouse	to Retailer 1	retailer 2	retailer 3	retailer 4
1	6	4	8	6
2	8	2	7	8
3	3	5	3	11

Transportation Costs ($)

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

b) Solve this problem with the Excel Solver and type a short report on findings:

• What is the total shipping cost?

• How many units should be shipped between each warehouse and each retailer to reach that minimized shipping cost?

Answer 1

a)

Decision variables : Let xij = number of units transported from warehouse i tp retailer j where i = {1,2,3} and j = {1,2,3,4}

Objective function : Min 6x11+4x12+8x13+6x14+8x21+2x22+7x23+8x24+3x31+5x32+3x33+11x34

Constraints:

x11+x12+x13+x14 <= 250 (Warehouse 1 )
x21+x22+x23+x24 <= 150 (Warehouse 2 )
x31+x32+x33+x34 <= 300 (Warehouse 3 )

x11+x21+x31 = 350 (Retailer 1)
x12+x22+x32 = 100 (Retailer 2)
x13+x23+x33 = 50 (Retailer 3)
x14+x24+x34 = 150 (Retailer 4)

xij >= 0

b)

solving in solver we get,

Total shipping cost = 2600

Optimal transportation schedule

	Retailer 1	Retailer 2	Retailer 3	Retailer 4
Warehouse 1	100	0	0	150
Warehouse 2	0	100	0	0
Warehouse 3	250	0	50	0

Solver screenshot

Solver formula

Solver window