Below I have attached the questions I need to answer. I need the answers on an excel spreadsheet. I worked on most of the answers but i need help putting them in an excel spreadsheet showing the math.

QUESTION:

A private equity firm is considering five competing projects in which to invest in the upcoming quarter. The firm needs to decide how to allocate its available capital based upon the combination of projects (denoted as A to E) selected to maximize returns (based upon net present value (NPV)). Table 1 below presents the capital requirements and the NPV for each project, along with the associated risk (given as a percentage of the initial investment). The company has $43 million in capital to allocate, with the goal of having an average associated risk of no more than 5%. There are some additional constraints to be met: (i) if project B is selected, then project E is also selected; (ii) one of the two projects A and C must be selected but not both; (iii) at least one of projects A, B, and D is selected.

Table 1: Project Data

Project	NPV (M$)	Risk (%)	Capital (M$)
A	19	4	14
B	22	5	10
C	24	6	12
D	27	7	15
E	21	5	13

Grading Rubric

Points for Integer Programming Formulation

1 point for correctly defining decision variables

2 points for the objective function equation

1 point for selecting it as the correct type i.e. max or min

3 points for constraint 1

3 points for constraint 2

3 points for constraint 3

3 points for constraint 4

3 points for constraint 5

1 point for the binary integer constraints

-----------------------------------------------------------------------------------------------------------

(My answers so far->)

From the given data, the Capital constraint is 14a+10b+12c+15d+13e <= 43

Risk Constraint is 4%a + 5%b + 6%c + 7%d + 5%e <= 5%

Maximize returns: 19a +22b+24c+27d+21e

Now according to additional constraints to be met: bte=> 1(project values can be integer only).

Since when project b is selected, project 'e' would also be selected but not vice versa.

'A' cannot equal 'c' (either project 'a' or 'c' selected, but not both)

A+b+d => 1

All a, b, c, d, and e are non-zero variables hence they can be either 0 (not selected) or 1 (selected for investing)

The total capital available is $43 million.

As we know that the risk associated can be a maximum of 5%. We can choose investments B and E.

To keep the risk below or equal to 5%, we have to choose investment A, which has a risk of 4%. As we have chosen investment A, we cannot choose investment C. Now if we choose to invest in E, the average risk would exceed the rate of 5%. Hence, to maximize our returns, we would choose to invest in projects A, B, and E.

Our total investment would be: $14 million + $10 million + $13 million = $37 million

The total NPV of our investments would be: $19 million + $22 million + $21 million = $62 million.

capital constraint 14a +10 b+12c+15d+13e<=43

risk constraint=4a%+5b%+6c%+7d%+5e%<=5%

maximize =19a+22b+24c+27d+21e

subject to adding a constraint

b+e=>1(since the project value can be integers only)when b is selected, then e is also selected but not a vice versa

a cannot equal c

a+b+d=>1

all a,b,c,d,e are non-zero variables hence they can be either 0( not selected ) or 1 (selected for the investment)