How to Optimize Project Selection with a Spreadsheet and Basic Algebra

By Sean Brooks

pencil with sharpener

As an executive, your time is a valuable asset, and your organization’s profitability depends on the efficient use of time. Managing time wisely requires, in part, that you do not pursue projects whose returns on investment, or value to the organization, do not justify the time or money required to complete them.

Projects with poor organizational alignment can sink companies and government agencies, or at least chart the wrong course. Implementing and executing on a systematic and consistent project selection approach can increase project value to your organization.

Two different (but complementary) project selection methods

There are many methods for evaluating and selecting projects, which include—but are not limited to—opportunity cost, cost-benefit analysis, scoring models, internal rate of return (IRR), net present value (NPV), and mathematical optimization. Many organizations employ more than one method in their business planning processes in order to conduct sensitivity analyses on the impacts of various project selection decisions on strategic priorities.

The Project Management Institute (PMI), which governs Project Management Professional (PMP) credentialing and certification, makes a distinction in the project initiation process between a comparative approach, which includes benefit measurement methods such as cost-benefit analysis and attractiveness scoring, and a mathematical approach, or what it calls the constrained optimization method.

Let’s assume that you’ve already whittled your list of potential projects from 15 to eight using a robust and defensible comparative approach such as a project attractiveness scoring system by which members of the executive team have evaluated projects based on some number of criteria (e.g., strategic fit, probability of commercial success, and potential for spin-off company).

Now you are ready for project selection based on priority criteria and some basic resource constraints such as budget and staff. You also could model additional factors such as customer requirements, project time frames, and urgency. A more sophisticated model could include capital requirements over multiple years for large projects and probability parameters (e.g., estimates of success by project tied to project-level NPV, if successful) in order to model uncertainty and assess risk.

Let’s keep it simple enough for now to demonstrate the power of mathematical optimization but without losing sight of the potential in more sophisticated modeling techniques. Follow along with this example model and resource, which walks you through using Excel Solver (perhaps a new tool for you in a familiar spreadsheet environment) to build a basic optimization model for project selection decisions.

X + Y ≤ 1, or junior high algebra to the rescue

Decision modeling under certainty is a mathematical approach for determining a way to achieve the best outcome (e.g., “maximize profit” or “minimize expense”), and, believe it or not, it draws on skills that you learned in your junior high algebra course. At that time, you may have thought that you never would need to know algebra in the “real world,” but recalling lessons on inequalities will help you now.

The model itself represents the mathematical relationship between decision variables, the objective function, and the constraints. A linear programming model, which is a basic type of optimization model, guarantees an optimal solution if there is one.

Getting started with building an optimization model

What must be decided?

Decision variables

What other quantitative variables are not in your control?

Input variables (data)

How do we compare different choices for decision variables?

Objective function

What limits our decisions?


Additionally, you can model “yes/no” decisions, where the decision is represented by a binary variable (0 or 1), which is useful in managerial decision making. Despite the fact that Solver cannot handle “if…then…” statements, you can use your algebra skills to transform qualitative descriptions of project restrictions and commonalities into logical conditions expressed as Solver constraints.

For example, suppose you have eight projects from which to choose, and you want to undertake at least two of the four projects (Projects 5, 6, 7, and 8) involving government clients.

  1. Think of this priority as P5 + P6 + P7 + P8 >= 2.


     2. Next, sum the four cells representing the yes/no decision to pursue each project              somewhere on your spreadsheet.


      3. Finally, add this inequality as a constraint in your model by setting the cell that     represents the sum of these four projects greater than or equal to 2. This action will force the model to find an optimal solution that includes this constraint (i.e., at least two of the four government projects must be selected) if one exists. Otherwise, there may be no optimal solution, in which case you could modify your constraints and try again.

Check out the Part (b) and Part (c) worksheets in the accompanying resource for clearly labeled example applications of these modeling techniques.

Five steps toward formulating a mathematical optimization model

In summary, follow these five basic steps as you formulate, build, and solve your next linear programming model for optimizing project selection decisions:

  1. Understand the problem.
  2. Identify the decision variables.
  3. State the objective function as a linear combination of the decision variables.
    1. This means that you cannot have any squares or other exponents, square roots, logarithms, min, max, or “if statements” in the objective function cell itself.
  4. State the constraints as linear combinations of the decision variables.
  5. Identify any upper or lower bounds on the decision variables, if applicable.

Next steps

Once you become comfortable with evaluating and optimizing project selection decisions, you could consider project portfolio balancing as a next step. It may be helpful to think of your project portfolio as an investment portfolio that must be optimized not only according to resource requirements and constraints but also with other priorities such as technology risk or industry maturity in mind.

Contact us with questions, comments, or to continue the conversation on how to implement this approach in order to improve your project selection decisions.

5 Tips for Security Data Analysis