Question

An industrial company is planning to build plants in three cities: Boston, San Francisco and Tennessee. The company needs to produce at least 40,000 units each year to satisfy the demand. Each plant has a certain capacity and there is an annual fixed cost to set up the plants and a variable cost per each unit produced. The following table provides the costs associated with each location.

Location	Annual Fixed Cost	Variable Cost	Annual Capacity
Boston	$350,000	$20	20,000
San Francisco	$375,000	$23	22,000
Tennessee	$250,000	$21	25,000

The company is willing to determine the best location(s) to build the plant(s). The goal is to minimize the total cost (i.e., annual fixed cost and variable cost).

Formulate the problem in Excel and solve it using Excel Solver.

(iii)           Now, assume the following situation. If the company opens a plant in Boston, then the plant in San Francisco should be opened as well. If the company does not open a plant in Boston, then the plant in San Francisco should not be opened either. Similarly, if the company opens a plant in San Francisco, a plant in Boston should be opened as well. If the company does not open a plant in San Francisco, then the plant in Boston should not be opened either. Write a constraint to model this situation. Incorporate this constraint into the Excel formulation and resolve the Excel formulation. How did the optimal solution change?

(iv)           Now, assume the following situation. The company would like to open exactly two plants. Write a constraint to model this situation. Incorporate this constraint into the Excel formulation and resolve the Excel formulation. How did the optimal solution change?

Answer 1

Let B,S,T be the quantities produced in plants in Boston, San Francisco, and Tennessee respectively

Since we do not need to setup all the 3 plants, let the following 0-1 variables indicate if a plant is setup at the corresponding location

• FB=1 if a plant is setup at Boston, 0 otherwise
• FS=1 if a plant is setup at San Francisco, 0 otherwise
• FT=1 if a plant is setup at Tennessee, 0 otherwise

The decision variables are B,S,T,FB,FS,FT

The total cost of production is variable cost +fixed cost. The fixed cost is incurred only if a plant is setup at that location.

For example, to produce B units at Boston the variable cost is B*20 and the fixed cost is FB*350000, will come in only if FB=1

The total cost then is

We want to minimize this and hence this is the objective function

We now look at the constraints

Demand, the plants need to produce at least 40000units

• 

Capacit, units produced at each plant should be less than or equal to the capacity.

• Capacity of Boston is available only if FB=1
  • ​
• Capacity of San Francisco is available only if FS=1
  • ​
• Capacity of Tennessee is available only if FT=1
  • ​

The model is

Minimize

s.t.

• 

• ​
• ​
• 
• 

First we setup the following excel sheet

To get the following

Set the solver using data--->solver

get the following solution

The optimum locations to setup the plants is Boston and Tennessee, to minimize the cost

iii) We know that FB=1 if the plant is opened at Boston, FS =1, if the plant is opened at San Francisco.

To make sure that plants are opened/not opened at both these locations we add the constraint

• 

The excel set up would be

the values are

Set the solver as below

get the following solution

The optimum solution now is to setup the plants at Boston and San Francisco. The cost to produce 40,000 units has increased to $1,585,000 from the earlier $1,420,000

iv) We will ignore the condition in iii) and add a new condtion to have exactly 2 plants

The number of plants setup is FB+FS+FT. We want this sum to be equal to 2

The additional constraint is

• 

The excel now is

the values are

set the solver as below

Get the following solution

the optimum solution is to set up plants at Boston and Tennessee. The optimum solution has not changed in comparison to the first part