In a previous column I discussed how Six Sigma projects should be selected using the theory of constraints (TOC).
After attempting to do so, most discover yet another constraint: money. In most organizations there are more opportunities for improvement than one can afford to pursue. If it isn't money, some
other resource will be in short supply, such as talent. And as if that weren't bad enough, the task is further complicated by uncertainty of the payoff from the projects and their probability of
success. An exciting computer software product known as Crystal Ball Pro by Decisioneering makes it possible to select winning projects by factoring in all of the relevant
factors. It does so by simulating various scenarios thousands of times, then choosing those that perform best. For example, the research and development group of a major public
utility has identified eight possible Six Sigma projects. A net present value analysis has computed:  The expected revenue for each project, if it's successful
Its estimated probability of success Its required initial investment Using these figures, the finance manager has computed the expected return
and the expected profit for each project. Unfortunately, the available budget is only $2 million, and selecting all projects would require a total initial investment
of $2.8 million. Thus, the objective is to determine which projects will maximize the total expected profit while staying within the budget limitation. Complicating
this decision is the fact that both the expected revenue and success rates are highly uncertain. Figure 1 shows a spreadsheet model for this problem. Figure 1: Project Selection Spreadsheet
The decision variables in column H are binary; that is, they can only assume
the values zero (do not fund the project) and one (fund the project.) The assumption variables are in the "Expected Revenue" and "Success Rate"
columns. Crystal Ball Pro will use simulation to evaluate a range of values for these two columns. The total profit, shown in cell G19, is a forecast variable
whose values depend on the assumption and decision variables. The idea is to find the combination of projects (determined by the decision variables) that
maximize total profit, taking into account the variation in expected revenue and the probability of success.
The project selection spreadsheet isn't quite good enough given that the number of possible sets of projects is too large to identify by trial-and-error.
Crystal Ball Pro can help here too. It includes a
program, called OptQuest, which will perform a search to find the optimal package of projects (see Figure 2). Figure 2: Progress Toward a Solution
The best solution OptQuest found (in a search that I limited to 10 minutes) is
to fund all projects except 3 and 5 (see Figure 3). The expected net profit is $1.54 million. Note that the distribution of total profit includes a number of
scenarios that would result in a net loss. This occurs because OptQuest was asked to find the solution that maximized expected (average) total profit, but it
can limit searches to profitable software solutions too. Figure 3: Results
About the author Thomas Pyzdek is a consultant in
Six Sigma and an ASQ fellow. E-mail him at Tom Pyzdek . To learn more about Six Sigma, visit his Web site at www.pyzdek.com . |
