Powerhouse Capacitors produces capacitors at three locations – Los Angeles, Chicago, and New York. Each plant has an annual production capacity of 100,000 capacitors. Capacitors are shipped from these locations to public utilities in five regions of the country: Northeast (NE), Northwest (NW), Midwest (MW), Southeast (SE), and Southwest (SW). The annual demands for capacitors in each region are as follows: 55,000 in NE, 50,000 in NW, 60,000 in MW, 60,000 in SE, and 45,000 in SW. The costs of producing and shipping a capacitor from each plant to each region of the country are given in the file “Powerhouse_data.” (This file also includes the above demand data, in ‘000s of units).
(a) Formulate the problem of minimizing the total cost, this year, of producing and shipping capacitators to meet all demands from the current three plants.
(b) Implement in Excel, and report the optimal solution and minimum annual cost.