Question

[Business Analytics/Operational Management] (Excel) Capital budgeting: A firm has 6 projects that it would like to undertake over the next 5 years but because of budget limitations not all can be selected. The total budget that the firm has considered to invest in the projects is $12,400,000. The following table displays the expected revenue (NPV) of each project after 5 years and the required yearly capital for each investment.

Table 1: Investment Details

Capital (in $000) required per year
Investment/ Project	Expected NPV ($000)	Year 1	Year 2	Year 3	Year 4	Year 5
1 2 3 4 5 6	$2700 $3330 $7010 $5770 $2900 $4870	$ 975 $1200 $2500 $1550 $1400 $1900	$ 350 $ 200 $1200 $1350 $ 350 $1900	$ 200 $ 200 $ 850 $ 675 $87.5 $ 350	$ 100 $ 200 $ 400 $ 337.5 $ 21.875 $ 350	$ 50 $ 200 $ 400 $168.75 $ 0 $ 350

The capital available for the time period of each year is shown in the following table: (The $12,400,000 in investment capital is spread over the 5 years)

Capital (in $000) allocated per year
Year 1	Year 2	Year 3	Year 4	Year 5
$ 5800	$ 3500	$ 1300	$ 900	$ 900

In addition, the firm must follow a few federal and state laws regarding these projects:
1. Surplus capital funds in any year cannot be carried over from year to year.
2. If the firm decides to invest in the second investment/project, it must also invest in the fourth.
3. If the firm decides to invest in the first investment/project, it cannot invest in the third investment/project.
Considering the budget limitations and the laws, which of the investments/projects should be chosen to maximize potential NPV?
a. Formulate the Integer/Binary Linear Programming model in algebraic form. Define the decision variables, objective function, and constraints.
b. Formulate this same problem on a spreadsheet and SOLVE using Excel solver (Provide a printout of the corresponding “Excel Spreadsheet” and the “Answer Report”).
c. Describe clearly and completely the optimal solution to this problem using a managerial statement.

Answer 1