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.

Mark the warehouse locations as 1,2,3, and 4 for Ottawa, Toronto, Calgary and Winnipeg. Let’s assign a decision variable Xij to each of the to and from values between the warehouse to regions. Here i={1,2,3,4} and j={1,2,3}

Then the amount transported from a source to region can be denoted by Xij. Similarly the cost between a warehouse and a region could be denoted by Cij. For example, Ottawa to Region 3 can is C13 = 50.

Let the fixed cost of each of the warehouse be Fi and the demand for each of the regions be Dj.

Now we need to introduce some dummy binary variables so that we can select the warehouse. Let’s denote them by Si for each of the warehouses.

The objective function is to minimize the total cost thus it is

Minimize Z, where

Z = Sum ij (Xij*Cij) + Sum i (Si*Fi)

The constraints are

Sum i (Xij) >= Dj (Demands must be fulfilled)

Sum j (Xij) <= 100*Si (supply cannot be exceeded for a chosen facility)

S1 – S3 <= 0 (If Ottawa is open, Calgary needs to be open)

Sum Si = 2 (at most 2 warehouses)

S3 + S4 >= 1 (Either Winnipeg or Calgary open)

Xij = Integer

Si = {0,1}

2.

The model is shown below. The yellow cells represent the decision variables and the objective function.

The formula is shown below.

The solver parameters are shown below. In this case, we cannot use simplex and will need to use GRG non-linear solver.

The result is shown below.

3.

The optimal solution for this problem is to use only Calgary and Winnipeg as the two warehouses to supply to 3 of the regions. We should ship 60 units from Calgary to region 2 and 40 units to region 3. Next we should ship 80 units from Winnipeg to region 1 and 10 units to region 2. This will fulfill the demand and also provide the lowest possible cost at $5890