Problems with Using the Excel Solver- Kak Industries

KAK Industries sells air-handling units. The annual demand for these
units in each region of the country is as follows: East, 100,000;
South, 150,000; Midwest, 110,000; West, 90,000. KAK is considering
building its air-handling units in four different cities: New York,
Atlanta, Chicago, and Los Angeles. The cost of producing an
air-handling unit in a city and shipping it to a region of the country
is given in the table below. Any factory can produce up to 150,000
air-handling units per year. The annual fixed cost of operating a
factory in each city is presented below. At least 50,000 units of the
Midwest demand for air-handlers must come from New York, and at least
50,000 units of the Midwest demand must come from Atlanta.



Unit production and shipping costs



To



East South Midwest West

From N.Y. $206 $225 $230 $290

Atlanta $225 $206 $221 $270

Chicago $230 $221 $208 $262

L.A. $290 $270 $262 $215









Annual fixed costs (millions)



N.Y. $6.0





Atlanta $5.5





Chicago $5.8





L.A. $6.2





a. Formulate an appropriate model to determine how KAK Enterprises can
minimize the total annual cost of meeting demand for its air-handling
units. Clearly define your decision variables, your objective function,
and your constraints. Place your algebraic formulation in the space
below.

b. Using Excel Solver, solve the problem. Place your solution in the
space below. Identify the locations of the factories that KAK should
use to manufacture its air-handlers and clearly and completely describe
the production and shipping schedule you have derived and clearly
indicate the total cost of meeting the customers’ demands.

PAGE 2

PAGE 1