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.
Use Excel Solver to Determine which projects should be undertaken image 1

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.

Use Excel Solver to Determine which projects should be undertaken image 2

Figure 1.2 [click on the image to get a full view]

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.
Use Excel Solver to Determine which projects should be undertaken image 3

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.

Use Excel Solver to Determine which projects should be undertaken image 4

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.

Use Excel Solver to Determine which projects should be undertaken image 5

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.

Use Excel Solver to Determine which projects should be undertaken image 6

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”.

Use Excel Solver to Determine which projects should be undertaken image 7

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.

Use Excel Solver to Determine which projects should be undertaken image 8

Figure 3.3

Download working file

Download the working file from the link below.

Zhiping Yan

Zhiping Yan

I am from China and this photo was taken in a classical garden. There are many similar gardens in China, attracting a lot of visitors every year, especially in spring and summer. I was major in Biotechnology. But I took a job as a SAS programmer because I prefer programming. Besides SAS, I also learned Excel VBA in my spare time. It is fantastic to be able to manipulate data, files and even to interact with the internet via programming. This will save me a lot of time. I am keen to learn new things.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo