Lesson 266: How to find profitable investments in Excel

In this article, I will show you how to find capital investments that optimize total return in Excel.

Build a model to find investments that maximize returns

The model we will deal with looks like this in Excel.

1. To create this binary integer programming (BIP) model, answer the following three questions:

a. What decisions will be made? For this problem, we need Excel to figure out what capital investments to make (Yes = 1, No = 0).

b. What are the constraints on these decisions? First, the amount of capital used for investments must not exceed the existing limited capital (50). For example, Investment One uses 12 units of capital. Second, can only invest One or invest Two. Third, can only invest Three or invest Four. Fourth, investment Six and investment Seven can only be made if investment Five is made.

c. What is the overall measure of performance for these decisions? The overall measure of performance is the total return on capital investments made, so the goal is to maximize this amount.

2. To make the model easier to understand, create named ranges as follows.

3. Insert the following five SUMPRODUCT functions.

Explanation: cell K7 (the amount of capital used) is equal to the sum of the product of the ranges C7:I7 and YesNo, cell K8 is the sum of the product of the ranges C8:I8 and YesNo… Total Profit is equal to the result results of profits and YesNo.

Try the board and find the mistake

With this formula, the analysis of any test solution becomes easy.

1. For example, if we make investments One and Two, the second constraint will be violated.

2. For example, if we make investment Six and Seven, but do not make investment Five, then the fourth constraint will be violated.

3. However, you should invest One, Five and Six. All constraints are satisfied.

Next, I will describe how to use Excel Solver to quickly find the optimal solution.

Solve the model

1. On the Data tab, in the Analyze group, click Solver.

Enter data as shown below.

2. Import TotalProfit in Objective.

3. Select Max.

4. Enter YesNo in the By Changing Variable Cells box.

5. Click Add to enter the following constraint.

6. Click Add to enter the following constraint.

7. Select ‘Make Unconstrained Variables Non-Negative’ and select ‘Simplex LP’.

8. Finally, click Solve.

Result:

Optimal solution:

Conclusion: investing Two, Four, Five and Seven is optimal. This solution gives a maximum return of 146. All constraints are satisfied.

In addition, you can also see many other excel articles here.