Portfolio Analysis

A company has limited capital—say, \$200,000—and dozens of investment opportunities across departments and regions. Which subset of projects should it fund to maximize total value (NPW, IRR, or benefit-cost) while respecting budget and logical constraints?

This is the capital budgeting problem. Unlike earlier analyses (where you compare a few projects one at a time), portfolio analysis simultaneously considers all possible combinations under a fixed budget, finding the optimal mix. Large companies (Boeing, Rolls-Royce) use this daily: hundreds of projects worldwide, limited capital, complex dependencies (some projects require others to be completed first).

Capital constraint: Total initial investment across selected projects cannot exceed available budget.

Mutually exclusive: Either select Project A or Project B, but not both. Examples: choose one technology for a facility, select one vendor.

Contingent (prerequisite): Project B can only be selected if Project A is also selected. Example: a housing facility for workers can only proceed if a dam project (its precursor) is approved.

Resource constraints: Limited skilled personnel, equipment, or facility capacity. Example: only 2 project managers available; each large project requires one.

Define binary decision variables $x_j$ for each project (0 = reject, 1 = select). The objective is to maximize total present worth:

$$\text{Maximize } Z = \sum_{j=1}^{n} \text{NPW}_j \cdot x_j$$

Subject to:

Capital constraint: $$\sum_{j=1}^{n} I_j \cdot x_j \leq \text{Budget}$$ where $I_j$ is the initial cost of project $j$.

Mutually exclusive (A and B): $$x_A + x_B \leq 1$$

Contingent (B requires A): $$x_B \leq x_A \quad \text{or equivalently} \quad x_B - x_A \leq 0$$

Non-negativity and binary: $$x_j \in \{0, 1\} \quad \forall j$$

Example: Six projects, budget \$200,000: \begin{align} \text{Maximize } &Z = 50x_1 + 60x_2 + 40x_3 + 70x_4 + 55x_5 + 45x_6\\ \text{s.t. } &50x_1 + 60x_2 + 45x_3 + 80x_4 + 90x_5 + 100x_6 \leq 200\\ &x_1 + x_2 \leq 1 \quad (\text{mutually exclusive})\\ &x_5 + x_6 \leq 1 \quad (\text{mutually exclusive})\\ &x_5 \leq x_1 + x_4 \quad (\text{Project 5 requires 1 or 4})\\ &x_j \in \{0, 1\} \end{align}

Setup:

1. Calculate NPW for each project (known from prior analysis).
2. Create a decision variable row: one cell per project, initially all 0.
3. Create a total-value cell with a formula: =SUMPRODUCT(NPW_range, decision_range). This is your objective function.
4. Create constraint cells: capital used (sum of $I_j \cdot x_j$), mutual exclusivity sums, contingency checks.

Running Solver: Go to Data → Solver (or Data → What-If Analysis → Solver on some systems).

• Set objective cell (your total-value formula).
• Choose "Max" (or "Min" if minimizing cost).
• Add constraints (capital ≤ budget, mutual exclusivity ≤ 1, contingencies ≤ 0).
• Set decision variables as "Binary" (forces 0 or 1).
• Click Solve.

Solver returns the optimal selection: which projects to fund. Verify that constraints are satisfied and total NPW is maximized.

Before Solver: Eliminate obviously bad projects (NPW < 0, BC < 1). This reduces the search space and makes the optimization faster and more interpretable. A project with negative NPW should never be selected, so you can discard it upfront.

Interpreting results: Solver tells you which projects to fund. Check that the total cost doesn't exceed budget and all constraints are satisfied. Ask: Does the selection make strategic sense? Are key initiatives (those pushing the company's strategy forward) included? If not, you may want to manually adjust constraints (e.g., "Project X must be selected") and re-run to explore alternatives.

Sensitivity: How sensitive is the solution to assumptions? If budget increased by 10%, would a different set of projects be optimal? If a project's NPW estimate is off by 20%, is it still in the optimal portfolio? Run Solver multiple times with modified inputs to understand robustness.

Real-world scale: Large companies have hundreds of projects. Solver still works—it's just a big linear program. Some firms use specialized capital-budgeting software that handles thousands of projects and complex global constraints (e.g., capacity limits per region, minimum investment per business unit). But the principle is the same: allocate scarce capital to maximize total value.

← Back to ENGR 4760