Question

Sweetland Sugar &Co. produces cane sugar in its three plants in Tampa, Mobile, and Houston It has four distribution centers in Charlotte, Kansas City, Indianapolis, and Flagstaff. These distribution centers serve five major markets, New York, Chicago, St. Louis, Las Vegas, and Seattle. The capacities of each plant, demands of each market and the transportation costs of sending a ton sugar from a plant to distribution center and from a distribution center to a market are given in the tables below. The company seeks to develop a plan to minimize transportation costs.

a) Determine the objective function and constraints.

b) Find the minimum cost and the amounts of sugar sent from each plant to distribution centers and from each distribution center to markets using Excel Solver.

From Plants	To Distribution Center				Supply (Tons)
	Charlotte	Kansas City	Indianapolis	Flagstaff
Tampa	$13	$17	$18	$25	20,000
Mobile	$16	$15	$15	$22	18,000
Houston	$18	$13	$17	$18	25,000

From Distribution Centers	To Markets
	New York	Chicago	St. Louis	Las Vegas	Seattle
Charlotte	$16	$13	$12	$22	$25
Kansas City	$20	$11	$6	$15	$18
Indianapolis	$10	$8	$8	$17	$19
Flagstaff	$25	$18	$15	$8	$15
Demand (tons)	15,000	12,000	9,000	14,000	13,000

Answer 1

(a): Let the decision variables be as follows:

From/To	Charlotte	Kansas City	Indianapolis	Flagstaff
Tampa	t1	t2	t3	t4
Mobile	m1	m2	m3	m4
Houston	h1	h2	h3	h4

From/To	New York	Chicago	St. Louis	Las Vegas	Seattle
Charlotte	c1	c2	c3	c4	c5
Kansas City	k1	k2	k3	k4	k5
Indianapolis	i1	i2	i3	i4	i5
Flagstaff	f1	f2	f3	f4	f5

Objective function = 13t1+17t2+18t3+25t4+16m1+15m2+15m3+22m4+18h1+13h2+17h3+18h4+16c1+13c2+12c3+22c4+25c5+20k1+11k2+6k3+15k4+18k5+10i1+8i2+8i3+17i4+19i5+25f1+18f2+15f3+8f4+15f5. This is the totall cost and has to be minimized:

Constraints: The 1st 3 constraints are with regards to the supply capacity of each plant:

(1): t1+t2+t3+t4<=20,000

(2): m1+m2+m3+m4<=18,000

(3): h1+h2+h3+h4<=25,000

The next 5 constraints are with regards to demand at each market:

(4): c1+k1+i1+f1 = 15,000

(5): c2+k2+i2+f2 = 12,000

(6): c3+k3+i3+f3 = 9,000

(7): c4+k4+i4+f4 = 14,000

(8): c5+k5+i5+f5 = 13,000

The next set of constraints shows that amount received by distribution center from all plants should be the same as amount distributed by it to all markets.

(9): t1+m1+h1 = c1+c2+c3+c4+c5

(10): t2+m2+h2 = k1+k2+k3+k4+k5

(11): t3+m3+h3 = i1+i2+i3+i4+i5

(12): t4+m4+h4 = f1+f2+f3+f4+f5

Lastly all variables >=0 (i.e. non-negativity) and should be integers.

(b): Using excel solver the following solution is obtained:

Minimized cost = $1,660,000

Quantity from plants to distribution centres:

From/To	Charlotte	Kansas City	Indianapolis	Flagstaff	Total
Tampa	-	11,000	9,000	-	20,000
Mobile	-	-	18,000	-	18,000
Houston	-	11,000	-	14,000	25,000
Total	-	22,000	27,000	14,000

Quantity from distribution centres to markets:

From/To	New York	Chicago	St. Louis	Las Vegas	Seattle	Total
Charlotte	-	-	-	-	-	-
Kansas City	-	-	9,000	-	13,000	22,000
Indianapolis	15,000	12,000	-	-	-	27,000
Flagstaff	-	-	-	14,000	-	14,000
Total	15,000	12,000	9,000	14,000	13,000

Screenshots (two) of excel solver are given below: