Capital Budgeting: IT Project Portfolio Optimization Redux
Appendix A: Other Methods
A concise treatment of the following, commonly-used, cash- and profit-basis methods for project and portfolio selection in the capital budgeting process can be found in Reference 12.
- Cash flow
- Discounted cash flow
- Discount rate
- Present value
- Net present value
- Profitability index
Some of the methods described in this reference, for example payback, are trivial and oversimplify the real world. But, their use may be appropriate for small projects because such methods are not expensive to carry out. They're often applied to individual projects, not portfolios.
Appendix B: Everything You Wanted to Know About how RiskOptimizer Works Under the Hood, but Were Afraid to Ask
Regardless of whichever optimization method is used, the model should reasonably represent the significant features of the real process if the extremum sought is to be meaningful. Even when this qualification is met, typical difficulties encountered with the model are:
- The criterion to be optimized may be insensitive to changes in the independent (decision) variables, so that no clear-cut extremum can be located.
- The criterion to be optimized or one or more of the constraints may become unbounded in the range of the search for the extremum. The way to overcome this difficulty is to suitably restrict the range of the independent variables by adding constraints to the problem or to reformulate the mathematical model.
- There may be poor scaling among the variables. Scaling difficulties can occur, for instance, when one of the terms in the criterion is of a much different order of magnitude than another in view of the significant figures in each term. Then, the criterion is insensitive to changes in the values of the variables in the small term. It is not easy to rescale in a mathematical model.
- There may be interaction among the variables in a poorly designed mathematical model. Parameter interaction can be illustrated by examining an extremely simple criterion in which two parameters are multiplied by each other:
y = 2x1x2 + 10
The individual values of x1 and x2 can range over any series of values for a given value of the product x1x2. Scaling is more difficult if interaction exists.
Figure 6: Hill-climbing optimization technique.
RISKOptimizer and Evolver (the Excel add-in discussed in the previous article and the source of the genetic algorithm-based optimizer used in RISKOptimizer) do not use hill climbing as shown in Figure 6. Rather, they use a stochastic, directed search technique, dubbed a genetic algorithm, as shown in Figure 7. This lets RISKOptimizer jump around in the solution space of a problem, examining many combinations of input values without getting stuck in local optima. In addition, RISKOptimizer lets good scenarios communicate with each other to gain valuable information as to what the overall solution landscape looks like, and then uses that information to better guess which scenarios are likely to be successful.
Figure 7: RISKOptimizer generates many possible scenarios, and then refines the search based on the feedback it receives.
Traditional Excel-based optimization problems analyzed using Evolver are comprised of:
- An output or "target" cell that you want to minimize or maximize
- A set of input or "adjustable cells" whose values you control
- A set of constraints that need to be met, often specified using expressions such as COSTS<100 or A11>=0
During an Evolver optimization, the adjustable cells are changed across allowable ranges you specify. For each possible set of adjustable cell values, the model is recalculated, and a new value for the target cell is generated. When the optimization is complete, an optimal solution (or combination of adjustable cell values) is found. This solution is the combination of adjustable cell values which yields the best (in other words, minimum or maximum) value for the target cell while satisfying the constraints you've entered.
When a model has uncertain elements, however, Evolver cannot generate optimal solutions. In the past, many optimization models just ignored uncertainty, making models unrealistic but optimizable.
With RISKOptimizer, the uncertainty present in a model may be included and reliable optimal solutions that take that uncertainty into account can be generated. RISKOptimizer uses simulation (from @RISK, Palisade Corporation's risk analysis and simulation add-in for Excel) to deal with the uncertainty present in the model and uses genetic algorithms (from Evolver) to generate possible values for the adjustable cells. The result of simulation optimization is the combination of values for the adjustable cells that minimizes or maximizes a statistic for the simulation results for the target cell. You may, for example, want to find the combination of adjustable cell values that maximizes the mean of the target cell's probability distribution, or minimizes the standard deviation.
For modeling uncertainty, RISKOptimizer allows you to describe the possible values for any spreadsheet element using any of the probability distribution functions available in @RISK—two of which are shown in Figure 2. A value of 10, for example, in a spreadsheet cell could be replaced with the @RISK function =RiskNormal(10,2). This would specify that the possible values for the cell are described by a probability distribution with a mean of 10 and a standard deviation of 2. As in @RISK, probability distributions can be correlated by using @RISK functions such RiskCorrmat and DepC or the dialog shown in Figure 3.
When optimizing, RISKOptimizer runs a full simulation for each possible trial solution that is generated by its optimizer. In each iteration of a trial solution's simulation, probability distribution functions in the spreadsheet are sampled and a new value for the target cell is generated. At the end of a simulation, the result for the trial solution is the statistic for the distribution of the target cell which you want to minimize or maximize. This value then is returned to the optimizer and used by the genetic algorithms to generate new and better trial solutions. For each new trial solution, another simulation is run and another value for the target statistic is generated. See Figure 8.
As in traditional optimizers, constraints that need to be met can be entered in RISKOptimizer. Constraints can be checked either at each iteration of a simulation (an iteration constraint) or at the end of each simulation (a simulation constraint). Iteration constraints are typically traditional Evolver style constraints, such as A11>1000. Simulation constraints are constraints that reference a statistic on the distribution of simulation results for any cell in your model you specify. A typical simulation constraint could be "Mean of A11>1000" or the mean of the distribution of simulation results for cell A11 must be less than 1000. As in Evolver, constraints can be hard or soft, and a violated hard constraint causes a trial solution to be rejected.
Because large numbers of simulations are being run by RISKOptimizer, two important techniques are used to minimize runtimes and generate optimal solutions as quickly as possible. First, RISKOptimizer uses convergence monitoring to determine when a sufficient number of iterations have been run (but not too many). This insures that the resulting statistic from the target cell's probability distribution is stable, and that any statistics from output distributions referenced in constraints are stable. Secondly, RISKOptimizer uses Evolver's genetic operators to generate trial solutions that move toward an optimal solution as quickly as possible. RISKOptimizer comes with a set of simulation statistics functions that can be used to return simulation results directly to your spreadsheet. The function RiskMean(cell reference), for example, returns the mean of the simulated distribution for the entered cell directly to a worksheet cell or formula. In addition, any model built in RISKOptimizer can be directly simulated in @RISK when you want to get detailed graphics and statistics on the best model solution found by RISKOptimizer. Because RISKOptimizer's simulation is based on @RISK, no changes to a RISKOptimizer model are required to simulate it in @RISK!
Figure 8: Overview of simulation/optimization with uncertainty
Appendix C: Software Developer's Kit
The RISKOptimizer Developer's Kit (RODK) is Palisade's optimization and simulation programming toolkit. The RODK allows you to build genetic algorithm optimization models that also incorporate Monte Carlo simulation using Windows and .NET programming languages.
This software developer's kit offers access to all of the optimization and simulation technology available in RISKOptimizer.
Because "A picture is worth a thousand words," take a look at the user interface shown in Figure 9 of a running RODK-built application. The objective of this desktop app is to maximize the mean return of a portfolio, while keeping the standard deviation of the portfolio below 0.1.
Figure 9: Desktop optimization app (.NET framework, C# language) developed using RODK.
For many users, the spreadsheet is the preferred modeling environment. However, many times an application written in a standard programming language needs optimization capabilities, and will have uncertain factors for which simulation can account. The application will have its own user interface, variables, and model calculations. In addition, a developer may want to allow users to access the application over a corporate network or over the Internet.
The RODK allows custom applications such as these to run [genetic algorithm] optimizations to maximize or minimize a particular output, while simulating the different possible scenarios of each trial solution. Applications written with the RODK often will run faster and can contain larger numbers of adjustable cells and distribution functions when compared with models written in spreadsheet versions of RISKOptimizer. This is because RODK applications are compiled and do not contain additional routines executed during spreadsheet recalculation.
- Winston, W. Decision Making Under Uncertainty with RISKOptimizer, Palisade (2000)
- Levine, H. Project Portfolio Management, Wiley (2005)
- Bonham, S., IT Project Portfolio Management, Artech House (2005)
- Gido, J., Clements, J. Successful Project Management 3rd Ed., Thomson (2006)
- Ross, S. et al. Corporate Finance, McGraw-Hill Irwin (2005)
- Shapiro, A. Capital Budgeting and Investment Analysis, Prentice Hall (2005)
- Knight, G. Analyzing Business Data with Excel, O'Reilly (2006)
- Carlberg, C. Business Analysis with Microsoft Excel 2nd Ed., Que (2002)
- Moskowitz, K., Kern, H. Managing IT as an Investment, Prentice Hall (2003)
- Snedaker, S. How to Cheat at IT Project Management, Syngress (2005)
- Jayaswal, S., Shetty, Y. Practical .NET for Financial Markets, Apress (2006)
About the Author
Marcia Gulesian has served as Software Developer, Project Manager, CTO, and CIO over an eighteen-year career. She is author of well more than 100 feature articles on Information Technology, its economics, and its management. You can e-mail Marcia at email@example.com.
© 2006 Marcia Gulesian
Page 3 of 3