Question

Reconsider the California Manufacturing Co. case study presented in Section 7.1. The mayor of San Diego now has contacted the company’s president, Armando Ortega, to try to persuade him to build a factory and perhaps a warehouse in that city. With the tax incentives being offered the company, Armando’s staff estimates that the net present value of building a factory in San Diego would be $6.1 million and the amount of capital required to do this would be $3.5 million. The net present value of building a warehouse there would be $5.5 million and the capital required would be $3.2 million. (This option will only be considered if a factory also is being built there.)

Armando has asked Steve Chan to revise his previous management science study to incorporate these new alternatives into the overall problem. The objective still is to find the feasible combination of investments that maximizes the total net present value, given that the amount of capital available for these investments is $10.2 million.

a. Formulate a BIP model in algebraic form for this problem. (Please clearly define all the decision variables, clearly write down the objective function and each constraints)

b. Formulate and solve this model on a spreadsheet.

Answer 1

Part A

Data-

Decision Number		Decision Variable	NPV (mn)	CR (MN)
1	Build a factory in Los Angeles	X1	8	6
2	Build a factory in San Francisco	X2	5	3
3	Build a warehouse in Los Angeles	X3	6	5
4	Build a warehouse in San Francisco	X4	4	2
Availability				10

Mathematical Model-

Objective Function: To maximize the total NPV.

Zmax = 8X1+5X2+6X3+4X4

Constraints-

6X1+3X2+5X3+2X4 <= 10 (Capital Available)

X1-X3 = 0 (If the factory is set up in LA, then warehouse also will be set)

X2-X4 = 0 (If the factory is set up in SF, then warehouse also will be set)

X3+X4 = 1 (at most one location would have a warehouse)

Solving in solver-

The solver is an excel plugin which can be installed form excel options. After installation, it is available in the data segment of the excel sheet. Once installed and launched, the parameters can be added

Spreadsheet Model along with formula-

Adding Parameters to Solver-

1st: Enter Green highlighted cell (objective function) in the set objective field

2nd: Select Max

3rd: Enter the yellow cells (decision variables) in the by changing variable cells field

4th: In constraints, click on add, enter the blue cells in the dialogue box which appears.

On the left area (cell reference), enter the left side values, select relationships in the middle, and in the right enter the right side values of the inequality signs. Similarly, repeat for the next constraints by clicking on the add button. Then click ok to go back to the parameters part.

Add an additional constraint as binary. Select the yellow cells in the cell reference part in the above box and select binary in the middle option.

5th: Select Simplex Lp in solving method

6th: Click solve

Solution-

Answer- The California Manufacturing company should expand with factories and warehouses in San Francisco.

Part B

Data with additional values-

Decision Number	Yes/No Question	Decision Variable	NPV (mn)	CR (MN)
1	Build a factory in Los Angeles	X1	8	6
2	Build a factory in San Francisco	X2	5	3
3	Build a warehouse in Los Angeles	X3	6	5
4	Build a warehouse in San Francisco	X4	4	2
5	Build a factory in San Diego	X5	6.1	3.5
6	Build a warehouse in San Diego	X6	5.5	3.2
Availability				10.2

Decision Variables: Let the decision to set up a warehouse/factory in a location is xi. i = 1,2,3,4,5,6

The decision variables x1,x2,x3,x4,x5,x6 represent the decisions as mentioned in the above table.

Objective Function: To maximize the total NPV.

Zmax = 8X1+5X2+6X3+4X4+6.1X5+5.5X6

Constraints-

6X1+3X2+5X3+2X4+3.5X5+3.2X6 <= 10.2 (Capital Available)

X1-X3 = 0 (If the factory is set up in LA, then warehouse also will be set)

X2-X4 = 0 (If the factory is set up in SF, then warehouse also will be set)

X5-x6 = 0 (If the factory is set up in SD then warehouse also will be set

X3+X4+X6 = 1 (at most one location would have a warehouse)

Spreadsheet Model along with formula-

Adding Parameters to Solver-

1st: Enter Green highlighted cell (objective function) in the set objective field

2nd: Select Max

3rd: Enter the yellow cells (decision variables) in the by changing variable cells field

4th: In constraints, click on add, enter the blue cells in the dialogue box which appears.

On the left area (cell reference), enter the left side values, select relationships in the middle, and in the right enter the right side values of the inequality signs. Similarly, repeat for the next constraints by clicking on the add button. Then click ok to go back to the parameters part.

Add an additional constraint as binary. Select the yellow cells in the cell reference part in the above box and select binary in the middle option.

5th: Select Simplex Lp in solving method

6th: Click solve

Solution-

Answer- The factory and warehouse should be built in San Diego.