Last week I showed how you can use Goal Seek to solve for a single unknown input value that will result in a specific value. Goal Seek works well and is fast at solving for a single input value. However, not all real world problems are so simple. In fact many problems involve the manipulation of several input values under defined constraints to yield a minimum, maximum or exact value. Want an example? I thought you might.
Suppose you were part of a management team responsible for selecting which of 10 possible IT projects to move forward into next year’s portfolio. All of the projects on the surface sound good because they all have a positive Net Present Value (NPV) calculated using methods similar to last week. Each project requires resources that might include labor and investment dollars. The problem is you only have $150,000 to invest in projects and your staff has been cut to 42 people. In your staff you have two managers, five DBAs and 35 developers. From the project managers, you got a list by project of the number of hours needed from each staff type. Now your challenge is to select which combinations of projects you should commit to for next year to maximize the revenue that these projects will bring to your organization.
To start, let’s build an Excel spreadsheet that displays the known data. In the figure below, you see the list of 10 projects in the first column. I have cleverly renamed these projects Project A through Project J to disguise their real names.
Column B (the second column) holds a flag to identify whether the project is included in next year’s portfolio of projects. I’ll initially set all of the flags to ‘1’ or selected. You’ll see in a moment that this is not a practical solution because it exceeds the available resources
Column C (the third column) shows the Net Present Value of each project. Then column D (the fourth column) shows that Net Present Value of only the selected projects. Of course initially, this column looks like column C because all projects start off selected. However, when a project is not selected, the value in column B for that project changes to 0 and 0 multiplied by the project’s NPV will always be 0.
Cell D17 sums the net present values for each of the selected projects. Thus you see that if you selected all ten projects, you would have a net present value of $2,440,000. Knowing that you do not have the resources for all ten projects, your net present value will be less than this. However, you do want to maximize this value.
In columns E, F, and G (columns 2-4 in the figure below) you see the number of hours each project would need from each of the labor types. Beneath the project hours are three rows that calculate the number of available hours for each labor type. If you assume that there are 2000 work hours in a year, then with two managers you have 4000 hours available. Similarly, 35 developers can provide 70,000 hours of developer time and 5 DBAs will contribute 10,000 hours of DBA support.
Now in the next figure, you see what happens when you multiply the number of hours for each staff type that the project needs by the selection flag (remember it is 0 or 1 only. No fractional projects are possible.) In row 16, you sum the number of hours the selected projects needs. When you compare the number of hours needed by staff type to the actual number of hours available in E18..G18 you see that you don’t have enough hours within any of the staff types to do all of the projects. Therefore, you know right that the simple solution of doing everything is not an option.
Furthermore, let us also suppose that some projects require some up-front investment dollars. Unfortunately, you only have $150,000 available (See cell D16). The required investment for each project is shown in column H in the following figure. Some projects have no investment and thus you see $0 in those cells. Then you can multiple the values in column H with the selected flag from column B to get the actual investment needed by the portfolio of projects shown in column J. Summing these values into cell J18 you see that if you committed to all the projects, you would need $218,000, but you don’t have that much money.
So far, I have identified the following constraints:
- Projects either go or they do not go (1 or 0).
- The total number of available hours by each staff type cannot be exceeded.
- The total investment amount needed cannot exceed $150,000.
So now it is time to decide which projects to include in your active portfolio for next year.
What’s that you said? You’ll just try different random combinations of projects to see what their total Net Present Value is? Well, you could do that. However, it would take you awhile to try every possible combination. Your next thought is that there has to be a better way to solve this problem.
There is and that better way is called the Solver. The Solver uses techniques based on linear programming to define a multidimensional volume of valid solutions. It defines this volume based on constraints such as the ones mentioned above. Then you have to set a goal for the solver such as to minimize or maximize a specific calculated value that is defined by the points within the volume.
Still don’t know where to begin. Where is this Solver? If Solver has not already been added to your version of Excel, you can download it from:
Mac Excel 2011: http://www.solver.com/mac/dwnmac2011solver.htm
Both tools work essentially the same and the above referenced pages provide instructions on how to install these tools. Since I am creating this example on a MacBook, this add-in appears in the Tools dropdown as shown below:
When opened, the solver displays a dialog similar to the one shown below:
Next you must define how the Solver should go about solving your problem. First, you need to define the object cell. In this case, you want to maximize the net present value of our selected project portfolio. I previously mentioned that I calculated this value in cell D17. After entering the objective cell, be sure to click the maximize button. Notice that you can also choose to minimize your objective (such as when looking at costs) or converge on a specific value.
Next you must tell Solver which cells it can change. In our case, these cells are the project selection flags found in column B5:B14.
Every problem has constraints. In the next section you must define the constraints you know the system must obey. For example, you know that the values in B5:B14 must be either 0 or 1. You can do this with the following three constraints:
$B$5:$B$14 >= 0
$B$5:$B$14 <= 1
B$5:$B$14 = Integer
Note that these three constraints taken together insure that each project flag is 0 or 1 and is not a faction.
Next look at the number of remaining hours for each of the staff types. Remember I said that you can not use more hours in your project portfolio than you have staff hours for each staff type. Another way of saying the same thing is that the remaining hours cannot be negative. If the remaining hours are positive, that means that the staff is not fully utilized by the selected projects. However, if any one of the three staff types fall to zero, no other projects can be started that require that staff type.
$K$18:$M$18 >= 0
Finally, you have to make sure that the initial investment (I18) does not exceed the amount of money in the budget for next year (D16) using the following constraint:
$i$18 <= $D$16
Then check the box that limits unconstrained variables to positive values unless you know and want certain constraints to be negative.
There are three methods you can use to solve the problem. Each method should come up with the same solution (Although I have seen in very complex constraints minor variations between the solutions.). Some methods let you see each iteration flash by as the algorithm narrows in on a solution. Simplex LP (Linear Programming) does not show iterations.
After selecting a method to solve your problem, click the Solve button. Within a few seconds, Excel solves this simple problem by showing the following project portfolio selection to yield a maximum net present value:
This Solver tells you to include projects D, E, G, H, and J in your next year portfolio for a maximum net present value of $1,520,000. Looking at some of the other data, you will see that these projects used $113,000 of the available $150,000 as startup capital. While some manager and developer time remains unused, the selected projects will use all of the available DBA hours leaving nothing for any other projects.
While this example was simplified to help illustrate the method, you can use the Excel Solver with more complex problems and more constraints. For example, the above analysis assumes that all developer hours are equal and that you can use whichever developer is available for each hour of the project. This simple analysis is not realistic, but it does serve to show the power of the example. To improve the accuracy of the model, you could develop a spreadsheet that includes each individual staff member as a separate constraint. This change complicates the problem shown here substantially. Also the current example does not consider the timing of the activity of each employee type. Obviously, if the two managers were needed heavily at the start of each of the selected projects, the number of projects selected could be different. It also does not consider if the project is front-loaded with staff or back-loaded with staff. Front-loaded projects use most of the hours in the first two weeks or months. Back-loaded projects tend to use most of the staff hours toward the end of the project rather than the beginning.
While all of these concerns are true, the Excel Solver still provides a good first solution to a problem that requires you to maximize or minimize a goal limited by constraints that make it difficult, if not impossible to easily manually calculate the solution.
On this Christmas eve, let the spirit of Christmas fill your hearts no matter what religion beliefs you may have.