Question

Cyberdata, a PC manufacturer, currently has two production facilities. The first one is located in Alpha City and has a capacity of 200,000 units a year and an annual fixed cost of 20 million. The second plant is located in Beta City and has a capacity of 60,000 units a year and annual fixed cost of 9 million. The two plants serve the entire country which is divided into four regional markets: the Northwest with a demand of 120,000, the Southwest with a demand of 30,000, the Center with a demand of 80,000, and the East with a demand of 26,000 For the next year, Cyberdata anticipates a 30% growth in demand in the Northwest, Southwest, and Center, and 50% growth in the East. Managers at Cyberdata want to increase their production capacity in order to accommodate the growth of the demand (which is expected to remain stable after 2020). Potential decisions being considered are the following 1. Increase the capacity of the Alpha City plant to 300,000 units a year. The 2. Increase the capacity of the Beta City plant to 100,000 units a year. The 3. Build a new plant in Gamma City with a capacity of 100,000 units a year and arn 4. Build a new plant in Delat City with a capacity of 50,000 units a year and arn corresponding annual fixed cost is 26 million; corresponding annual fixed cost is 11 million; annual fixed cost of 15 million; annual fixed cost of 7 million The production cost of one unit is 610 in Alpha City, 620 Beta City, 630 in Gamma City, and 590 in Delta City. The shipping costs of one unit are shown in the following table Northwest Southwest Center East Alpha Cit 15 25 30 35 Beta Cit 25 12 20 40 Gamma Cit 25 35 15 20 Delta Cit 25 45 20 10 Cyberdata wants to compute the lowest annual cost for the production and distribution network and determine which plants serve which markets

a. Formulate the corresponding integer programming problem

b. Find an optimal solution using Excel Solver

Answer 1

a)

Next year projected demand after growth:

Northwest = 120000*1.3 = 156,000 units

Southwest = 30000*1.3 = 39,000 units

Center = 80000*1.3 = 104,000 units

East = 26000*1.5 = 39,000 units

Decision variables:

A = 1, if capacity is increased of Alpha plant, otherwise A = 0

B = 1, if capacity is increased of Beta plant, otherwise B = 0

G = 1, if a new plant is built at Gamma city, otherwise G = 0

D = 1, if a new plant is built at Delta city, otherwise D = 0

AN, BN, GN, DN be the quantity to be shipped from Alpha, Beta, Gamma and Delta plant resp to Northwest

AS, BS, GS, DS be the quantity to be shipped from Alpha, Beta, Gamma and Delta plant to Southwest

AC, BC, GC, DC be the quantity to be shipped from Alpha, Beta, Gamma and Delta plant to Center

AE, BE, GE, DE be the quantity to be shipped from Alpha, Beta, Gamma and Delta plant to East

Linear program is following:

Min (20+(26-20)A+9+(11-9)B+15G+7D)*10^6+(610+15)AN+(610+25)AS+(610+30)AC+(610+35)AE+(620+25)BN+(620+12)BS+(620+20)BC+(620+40)BE+(630+25)GN+(630+35)GS+(630+15)GC+(630+20)GE+(590+25)DN+(590+45)DS+(590+20)DC+(590+10)DE

s.t.

AN+BN+GN+DN = 156000

AS+BS+GS+DS = 39000

AC+BC+GC+DC = 104000

AE+BE+GE+DE = 39000

AN+AS+AC+AE-(300000-200000)A <= 200000

BN+BS+BC+BE-(100000-60000)B <= 60000

GN+GS+GC+GE-100000G <= 0

DN+DS+DC+DE-50000D <= 0

A, B, G, D binary

All other variables >= 0

b) Solution using Excel Solver is determined as below:

EXCEL FORMULAS:

Parameter table
	Alpha	Beta	Gamma	Delta	Demand
Northwest	=610+15	=620+25	=630+25	=590+25	=120000*1.3
Southwest	=610+25	=620+12	=630+35	=590+45	=30000*1.3
Center	=610+30	=620+20	=630+15	=590+20	=80000*1.3
East	=610+35	=620+40	=630+20	=590+10	=26000*1.5
Original Capacity	200000	60000	0	0
Augmented Capacity	300000	100000	100000	50000
Annual Fixed Cost ($million)
Original Capacity	20	9	0	0
Augmented Capacity	6	2	15	7
Optimal distribution strategy
	Alpha	Beta	Gamma	Delta	Total
Northwest	156000	0	0	0	=SUM(B16:E16)
Southwest	0	39000	0	0	=SUM(B17:E17)
Center	5000	99000	0	0	=SUM(B18:E18)
East	39000	0	0	0	=SUM(B19:E19)
Total	=SUM(B16:B19)	=SUM(C16:C19)	=SUM(D16:D19)	=SUM(E16:E19)
Capacity added/Plant built	0	1	0	0
Capacity Constraint	=B7+B21*B8-B20	=C7+C21*C8-C20	=D7+D21*D8-D20	=E7+E21*E8-E20
				Total Cost ($ million) =	=SUMPRODUCT(B16:E19,B3:E6)/10^6+SUMPRODUCT(B21:E21,B12:E12)+SUM(B11:E11)