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.


Download Practice Workbook

You can download the practice workbook from here.


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 this method when a Linear Programming problem by linear relationships occurs. 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.

Enabling Excel Solver to Use Excel Solver to Determine Which Projects Should Be Undertaken

  • Then, go to More… > Options.

Enabling Excel Solver to Use Excel Solver to Determine Which Projects Should Be Undertaken

  • Next, go to Add-ins > Solver Add-ins > OK options.

Enabling Excel Solver to Use Excel Solver to Determine Which Projects Should Be Undertaken

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

Enabling Excel Solver to Use Excel Solver to Determine Which Projects Should Be Undertaken

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

Enabling Excel Solver to Use Excel Solver to Determine Which Projects Should Be Undertaken

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

Enabling Solver Use Excel Solver to Determine Which Projects Should Be Undertaken


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

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.

Dataset to Use Excel Solver to Determine Which Projects Should Be Undertaken

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

Using Solver Parameter Dialog Box Use Excel Solver to Determine Which Projects Should Be Undertaken

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

Read More: Excel Solver Guide (Easy and Step by Step)


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:

  • At first, arrange a dataset like the below image.

Dataset Use Excel Solver to Determine Which Projects Should Be Undertaken

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

Solver Dialog box Use Excel Solver to Determine Which Projects Should Be Undertaken

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

Read More: Where Is Solver in Excel?


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.

Read More: Example with Excel Solver to Minimize Cost


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 cost 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 to learn using 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. Follow the ExcelDemy website for more articles like this. 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

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