Question

1. HKV company is considering investing in several projects that have varying capital requirements over the next four years. The net present value of each project, cash outflows required over the years and capital available per year are given (in millions) in the table below:

	Plant 1 Expansion	Plant 2 Expansion	New Product Development	Automation	Annual Capital Available
Year 1	8	5	4	7	20
Year 2	10	7	2	5	25
Year 3	10	10	1	5	20
Year 4	7	3	1	5	15
NPV of each Project	45	20	10	20

Each project must be done in full (with the corresponding cash flows for all the years) or not done at all. Any available money not spent in a given year is then available to spend the following year. Help the company find the optimal investment plan that would maximize the total net present value. (Recall the Capital Budgeting example in Section 3.2. There are some similarities between the example and this problem, but some differences – this is a BIP problem.)

1. Formulate the problem as a binary integer programming (BIP) problem in spreadsheet form. Solve the spreadsheet model. What is the optimal investment plan? What is the total net present value of the optimal investment plan?
2. Additional constraint is considered: the New Product Development is selected only if Plant 2 Expansion project is also adopted. Formulate and solve the spreadsheet model. What is the optimal investment plan? What is the total net present value of the optimal investment plan?
3. From question b, considering one more constraint: Plant 1 Expansion and Plant 2 Expansion cannot be both selected. Formulate and solve the spreadsheet model. What is the optimal investment plan? What is the total net present value of the optimal investment plan?
4. Formulate the problem (c) as a binary integer programming (BIP) problem in algebraic form.
5. (Practice; will not be graded) If the cash available in year 1 varies between 10 and 30 million dollars, how would that affect the optimal solution and the optimal total net present value of the investment plan? Use the Analytics Solver to conduct the Parameter Analysis and interpret the report generated.

Answer 1

Give this answer a thumbs up, It really helps!

(a)

The spreadsheet formulas are shown below

The solver parameters are shown below

The result is shown below

(b) The new solver parameter is shown below

The result is shown below

(c) The new solver parameter is shown below

The result is shown below

(d)

The formulation for part b can be written as

Maximize 45X1 + 20X2 + 10X3 + 20X4

Subject to

8X1 + 5X2 + 4X3 + 7X4 <= 20

10X1 + 7X2 + 2X3 + 5X4 <= 25

10X1 + 10X2 + 1X3 + 5X4 <= 20

7X1 + 3X2 + 1X3 + 5X4 <= 15

X2 >= X3

X1, X2, X3, X4 = {0,1}