Question

Eastinghouse sells air conditioners. The annual demand for air conditioners in each region of the country is as follows: East, 100,000; South, 150,000; Midwest, 110,000; West, 90,000. Eastinghouse is considering building the air conditioners in four different cities: New York, Atlanta, Chicago, and Los Angeles. The cost of producing an air conditioner in a city and shipping it to a region of the country is given in the table below.

Price By Region ($)

CITY	EAST	SOUTH	MIDEAST	WEST
NEW YORK	206	225	230	290
ATLANTA	225	206	221	270
CHICAGO	230	221	208	262
LOS ANGELES	290	270	262	215

Any factory can produce as many as 150,000 air conditioners per year. The annual fixed cost of operating a factory in each city is given in the table below

CITY	Annual Fixed Cost ($ Million)
NEW YORK	6
ATLANTA	5.5
CHICAGO	5.8
LOS ANGELES	6.2

Formulate an IP whose solution will tell Eastinghouse how to minimize the annual cost of meeting demand for air conditioners.

(I have to make spreadsheet file with formula and answer report with word file that contains the objective function, decision variables, constraints, and solution of all questions. Pleas help me)

Answer 1

Decision variable

xij = number of units transported from factory i to location j where i ={NEW YORK=1,ATLANTA=2,CHICAGO=3,LOS ANGELES=4} and j = {EAST=1,SOUTH=2,MIDEAST=3,WEST=4}

yi = 1 if factory is built at location i else 0

Spreadsheet model

Formula used

part 1 (Consider the part 2 as side by side of part 1)

Part 2

Solver window

Answer report

Optimal schedule

	EAST	SOUTH	MIDEAST	WEST
NEW YORK	100000	0	50000	0
ATLANTA	0	150000	0	0
CHICAGO	0	0	0	0
LOS ANGELES	0	0	60000	90000

We can see factory is be built in New york, Atlanta and Los Angeles to cater to demand

Minimized cost = 115770000