# Use Excel Solver to Determine Which Projects should be Undertaken

Companies need to know which projects should be undertaken in order to produce the greatest NPV (net present value) when there are limited resources. Applying Excel Solver can help solve this kind of problem. To get familiar with Excel Solver, you can read one of my previous articles – Using Excel Solver to Determine the Optimal Product Mixtures.

## Case 1: Maximize NPV by determining which projects should be undertaken

Suppose that a company is trying to determine which of nine projects it should undertake to maximize NPV. The NPV contributed by each project and the capital required for each project during the next two years is shown in the following table. For example, project 1 yields \$14,000,000. It requires \$12,000,000 during Year 1, \$3,000,000 during Year 2. Project 2 requires \$54,000,000 during Year 1, \$7,000,000 during Year 2 and contributes \$17,000,000.

 NPV Year 1 expenditure Year 2 expenditure Project 1 \$14,000,000 \$12,000,000 \$3,000,000 Project 2 \$17,000,000 \$54,000,000 \$7,000,000 Project 3 \$17,000,000 \$6,000,000 \$6,000,000 Project 4 \$15,000,000 \$6,000,000 \$2,000,000 Project 5 \$40,000,000 \$32,000,000 \$35,000,000 Project 6 \$12,000,000 \$6,000,000 \$6,000,000 Project 7 \$14,000,000 \$48,000,000 \$4,000,000 Project 8 \$10,000,000 \$36,000,000 \$3,000,000 Project 9 \$12,000,000 \$18,000,000 \$3,000,000

During Year 1, \$50 million in capital is available for projects and \$20 million in capital is available for projects during Year 2. Assume that the company cannot undertake a fraction of a project. It must undertake either all or none of it. It cannot undertake (let’s say) 50 percent of the project. How can you maximize NPV?

Now let’s identify the target cell, the changing the cells, and the constraints first.

 Target cell (cell D2): Maximize the NVP yielded by chosen projects. Formula “=SUMPRODUCT (B5: B13, D5: D13)” is used to compute the total NVP. Changing cells (cell B5: B13): Binary changing cells should be applied in this situation since we cannot undertake a fraction of a project. A binary changing cell always equals to 0 or 1. 0 means that you don’t undertake the project while 1 means that you undertake the project. For example, 1 in cell B5 means that project 1 will be undertaken. Constraints (Range E15: F17): Capital used in Year t is less than or equal to capital available in Year t. Binary changing cells should be applied here. Figure 1.1

Fill in the Solver Parameters dialog box as below. To add the constraint about binary changing cells, just select the changing cells (B5:B13 here in this example) and then choose Bin from the list in the Add Constraint dialog box.

By clicking Solve in the Solver Parameters dialog box and OK in the Solver Results dialog box, we will get results as shown in Figure 1.3. To get maximum NPV, we need to undertake projects 1, 3, 4, 6, and project 9. The maximum NPV is \$70,000,000. Figure 1.3

## Case 2: Another project must be undertaken if one project is chosen

Suppose that if project 4 is undertaken, project 5 must be undertaken. How can you maximize NPV for the above company?

Read More: Excel Solver – Introducing you with a simple example

The model, in this case, will be the same as that in case 1 except for a new constraint. Therefore, the worksheet will look the same as that in case 1. Let’s see the new constraint. It implies that cell B9 should be 1 if cell B8 equals to 1. If cell B8 equals to 0, cell B9 can be either 1 or 0. In summary, cell B8 should be less than or equal to cell B9. As a result, the Solver Parameters dialog box should be filled as shown in Figure 2.1. You can see the difference compared with Figure 1.2. Figure 2.1

The solution returned by Excel is shown in Figure 2.2. You can see that both cell B8 and cell B9 equals to 0. It tells that we cannot undertake both project 4 and project 5 to get maximum NPV and have to give up both of these two projects. The maximum NPV is \$55,000,000 if we undertake projects 1, 3, 6, and project 9. Figure 2.2

## Case 3: Another project must be undertaken if one project is chosen

Now suppose that you can do only three projects among those 9 projects. How can you maximize the NPV?

Read More: Financial Planning with Excel Solver [2 Case Studies]

Cells B2 through F17 will be the same as those in the above cases. Range C19:F19 includes the new constraint. Formula “=SUM (B5: B13)” was entered in D19 to compute the total number of projects undertaken by the company. Figure 3.1

Fill in the Solver Parameters dialog box as shown in Figure 3.2. You can see that the new constraint is “\$D\$19 <= 3”. Figure 3.2

The maximum NPV, in this case, is \$46,000,000 per solution (shown in Figure 3.3) returned by Excel. If we can only undertake at most 3 projects, we should undertake project 1, project 3, and project 4 to get maximum NPV. Figure 3.3  