# How to Use Excel Solver to Determine Which Projects Should Be Undertaken?

This tutorial will demonstrate how to use Excel SolverÂ to determine which projects should be undertaken. Companies need to know which projects should undertake in order to produce the greatest NPV (net present value) when there are very few resources. Applying Excel Solver can help solve this kind of problem. We often apply it directly to predict the outcome or help in decision-making. So, it is important to learn how to use Excel solver to determine which projects should be undertaken.

## What Is an Excel Solver?

Excel Solver is used in many different algorithms for finding the solution to different complex linear and non-linear problems. The methods of Excel Solver are.

GRG Nonlinear: The full form of this method is the Generalized Reduced Gradient Nonlinear method. We will use this method when simple nonlinear problems where the data will contain a minimum of one constraint.

LP Simplex: We will use Excel Solver for Linear Programming problems when linear relationships occur. With the linear connection with the data, linear programming tends to achieve the desired goal such as minimum loss, maximum profit, etc.

Evolutionary: Among all the types, this is the most difficult where you can not determine the characteristics of the function. The system of this kind of function can increase or decrease at any time. This is because the functions are either discontinuous or nonsmooth.

## How to Enable Excel Solver

Normally, Excel Solver isnâ€™t available in the Data tab. We have to add it manually to our menu tab. We can add and enable Excel by following the below process:

• First, go to the FileÂ option.

• Then, go to Moreâ€¦ > Options.

• After that, in the Developer tab select the Excel Add-insÂ option.

• Afterward, tick the Solver Add-in option and press OK.

• Finally, go to Data > Solver options to open the Solver Parameters dialog box.

## How to Use Excel Solver to Determine Which Projects Should Be Undertaken: 3 Cases

Weâ€™ll use a sample dataset overview as an example in Excel to understand easily. If you follow the steps correctly, you should learn how to use Excel Solver to determine which projects should be undertaken on your own. The steps are:

### 1. Maximizing NPV to Determine Which Projects Should Be Undertaken

In this case, our goal is to use an Excel solver to determine which projects should be undertaken by maximizing NPV in Excel. In this case, the project that has maximum NPV will always undertake the project. The steps of this process are.

Steps:

• First, arrange a dataset like the below image. In this case, we have Project No in cell B4, NPV in cell C4, Year 1 in cell D4, and Year 2 Â in cell E4.

• Next, add more cells like Objective, By Changing Cells, and Needed We will use these three parameters to use the solver option correctly.

• After that, go to Data>Solver options to open the Solver Parameters dialog box. In the Solver Parameters dialog box, fulfill the Set Objective, To, By Changing Variable Cells, Subject to Constraints, and Select a Solving Method After that, press the Solve option to get the desired result.

• Afterward, you will get the Solver Results dialog box. Here, select the Keep Solver Solution and press OK.

• Finally, you will get the desired result like the following image.

### 2. Another Project Must be Undertaken If One Project is Ended

Suppose that if project 4 is undertaken, project 5 must be undertaken. 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. We will describe the full process in the below steps.

Steps:

• First, arrange a dataset like the below image.

• Second, go to Data > Solver options to open the Solver Parameters dialog box. In the Solver Parameters dialog box, fulfill the Set Objective, To, By Changing Variable Cells, Subject to Constraints, and Select a Solving Method After that, press the Solve option to get the desired result.

• Last, you will get the desired result like the following image.

### 3. Choosing the Number of Projects That Must be Undertaken

Now, suppose that you can do only three projects among those 9 projects. The model, in this case, will be the same as that in case 2 except for a new constraint. Therefore, the worksheet will look the same as that in case 2. We will describe the full process in the below steps.

Steps:

• To begin with, arrange a dataset like the below image.

• In addition, go to Data > Solver options to open the Solver Parameters dialog box. In the Solver Parameters dialog box, fulfill the Set Objective, To, By Changing Variable Cells, Subject to Constraints, and Select a Solving Method After that, press the Solve option to get the desired result.

• Finally, you will get the desired result like the following image.

## Things to Remember

• Excel Solver isnâ€™t normally added in the Data tab. So, at the very beginning, add it to your Data tab by following the above description.
• The most important thing is to focus while using the Solver Parameters dialog box. Any mistake in the dialog box will cause a different result in this case.
• All the main formulas must be checked properly before using the Solver If you have any problems with formulas or data then you have to correct them before using the Solver option.

## Conclusion

Henceforth, follow the above-described methods. Hopefully, these methods will help you learn to use Excel solver to determine which projects should be undertaken. We will be glad to know if you can execute the task in any other way. Please feel free to add comments, suggestions, or questions in the section below if you have any confusion or face any problems. We will try our best to solve the problem or work with your suggestions.

## Related Articles

<< Go Back to Excel Solver Examples | Solver in ExcelÂ |Â Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
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... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF