Question

Company ABC is considering opening warehouses in four cities in Canada: Ottawa, Toronto, Calgary and Winnipeg. Each warehouse can ship 100 units per day. The daily fixed cost of keeping each warehouse open is $400 for Ottawa, $500 for Toronto, $300 for Calgary and $350 for Winnipeg. Region 1 of the country requires 80 units per day, region 2 requires 70 units per day and region 3 requires 40 per day. The costs (including production and shipping costs) of sending one unit from plant to a region are shown below in the table. The company wants to meet daily demands at a minimum cost subject to the preceding information and following restrictions:

To ($): From Region 1 Region 2 Region 3
Ottawa 20 40 50
Toronto 48 15 26
Calgary 26 35 18
Winnipeg 24 50 35

a) If Ottawa warehouse is opened, then Calgary warehouse must be opened.

b) At most two warehouses can be opened

c) Either Winnipeg or Calgary warehouse must be opened.

The company wants to minimize the daily cost of meeting demand.

1. Formulate algebraically a Binary/Integer Programming (BIP) model for this problem.

2. Use Excel Solver to solve the resulting BIP. (Include both formulation and answer report)

3. Describe clearly the optimal solution to this problem using a managerial statement to make a recommendation to the leadership of company ABC.

Answer 1

1)

There will be two components of decision variables. The transportation cost and warehouse selection. Lets’ denote the warehouses as 1,2,3 and 4. Similarly the regions as 1,2, and 3. The transportation variables will be denoted in Xij where i= warehouse and j=regions. The fixed cost denoted by Fi and selection variables (binary variables) as Si where Si ={0,1}

Our objective function will be

Minimize Z where Z = summation i (Si*(Xij+Fi))

Subject to the constraints

Summation j (Xij) <= 100

Summation i (Xij) >= Dj where Dj is the demand for the region j

Xij >= 0

S1 – S3 <= 0

S1 + S2 + S3 + S4 = 2

S3 + S4 >= 1

At this point the algebraic formulation is completed.  

2)

The excel below shows the solver model

The formulas are shown below

The solver parameters are shown below

The result is shown below

3)

The optimal solution to meet all the conditions and to reduce the overall cost of transportation and fixed cost is to choose warehouses in Calgary and Winnipeg. Calgary will provide 60 units to region 2 and 40 units to region 3. Winnipeg will provide 80 units to region 1 and 10 units to regions 2. This will incur a total cost of 5890 which is the lowest possible cost.