Assignment:
A company is considering opening warehouses in four cities: New York, Los Angeles, Chicago, and Atlanta to serve three different demand regions. Each warehouse can ship 100 units per week. The weekly fixed cost of keeping each warehouse open is $400 for New York, $500 for Los Angeles, $300 for Chicago, and $150 for Atlanta. Region 1 of the country requires 80 units per week, region 2 requires 70 units per week, and region 3 requires 70 units per week. The cost (including production and shipping costs) of sending one unit from a plant to a demand region is shown in the following table.
| |
To
Region 1 Region 2
|
Region 3 Fixed Co$t
|
|
NY
|
$20
|
$40
|
$50
|
$40,000
|
|
LA
|
$48
|
$15
|
$26
|
$50,000
|
|
Chicago
|
$26
|
$35
|
$18
|
$30,000
|
|
Atlanta
|
$24
|
$50
|
$35
|
$25,000
|
The company wants to meet weekly demands at minimum cost, subject to the following additional restrictions:
1. If the New York warehouse is opened, then the Los Angeles warehouse must be opened.
2. At most two warehouses can be opened.
3. Either the Atlanta or the Los Angeles warehouse must be opened.
Formulate a mixed-integer linear program IN EXCEL to determine which warehouses to open and how to service demand at a minimum total cost.
Provide complete and step by step solution for the question and show calculations and use formulas.