Print this article

OptQuest: Finding the Best Solutions Under Uncertain Conditions

What is OptQuest?

OptQuest is an optimization tool available with the Professional and Premium Editions of Crystal Ball. As an add-in to Crystal Ball, OptQuest enhances simulation models by automatically searching for and finding optimal solutions.

For example, if you were a supply chain manager, you could use Crystal Ball to simulate uncertain demand and the likelihood of running short on stock (given specific inventory levels). For the same problem, OptQuest could help you to identify adequate inventory levels, reduce your holding costs, and optimize your potential profit, while still accounting for the uncertainty of demand.

You can also use OptQuest for portfolio analysis, where you determine an investment strategy that optimizes profit while taking into consideration the uncertainty around the annual returns of each asset. Other applications for optimization include workforce planning of employees, configuration of machines for production scheduling, the location of facilities for distribution, and tolerances in manufacturing design.

What Are You Trying to Optimize?

Before using OptQuest, you first need to create your Crystal Ball model. Once you feel that the model is a valid representation of your problem, system, or process, you then need to use Crystal Ball to define the decision variables. Decision variables are variables in your model that you have control over, such as how much rent to charge or how much money to invest in a mutual fund.

In the spreadsheet below, you have three decisions to make: the number of Wells to Drill (Cell B8), the Facility Size (Cell B12), and the Plateau Rate (Cell B15). These variables are all in your control.

In Cell B12, the decision variable describes the range of sizes possible for an oil-processing facility you want to build. The smallest facility would process 50 mbd (thousand barrels of oil per day), and the largest would process 350 mbd per day.

After defining all the decision variables, you can start OptQuest through the Run menu.

The OptQuest Wizard

The OptQuest wizard program helps you through the setup process. You first select which decision variables to use. Next, you can set a constraint on your decision variables. A constraint restricts the decision variables to specific combinations of values. For example, if you can only invest $50,000 in a group of portfolio assets (your decision variables), a constraint will ensure that the investment sum from all of the assets will never exceed $50,000.

Next, you select the forecast you want to optimize (e.g., minimize construction costs, maximize profits). You can also set a requirement, which is a restriction on a forecast statistic such as the mean or standard deviation. For example, you could set a requirement to ensure that in any acceptable solution, you will be 90% certain of having a final portfolio value of $1 million or more (shown below). OptQuest will ignore any solution that does not comply with your requirement.

Finally, you can select from several setup options, the most important of which is how long you want OptQuest to run.

The Optimization Process

At the basic level, OptQuest selects a value for each decision variable, enters those values into your spreadsheet, runs a Monte Carlo simulation on the spreadsheet, records the results, and repeats the process.

You could manually perform this sort of analysis, but as you increase the number of decision variables, the number of possible variable combinations becomes unwieldy.

On a more advanced level, OptQuest does a much better job at finding optimal solutions than is possible with manual calculations. OptQuest surpasses the limitations of genetic algorithm optimizers because it uses multiple, complimentary search methodologies, including advanced tabu search and scatter search, to help find the best global solutions.

While running solutions, OptQuest also checks for compliance with the constraints and requirements. In addition, OptQuest applies adaptive and neural network technologies to help it learn from past optimizations so it can achieve better results in less time.

Informative Charts and Tables

As you run an optimization, you have several ways to view your progress. The Status and Solutions window (shown below) tells you how much time remains in your optimization, how many solutions OptQuest has run, and what the current best result is. Each time OptQuest generates a new best result, it adds a line to the table so you can view past best results.

The Performance Graph (shown below) is useful for gauging your progress. As the graph levels off and remains unchanged, you can decide whether or not you have converged on an optimal solution.

Using the Results

Once you have completed your optimization, OptQuest copies the best decision variable values back to your spreadsheet model. You can then view your model and the forecast chart for the optimal outcome. Optionally, you can use OptQuest's Solution Analysis feature to compare the optimal and suboptimal results for any useful trends in decision variable results.

Not every problem requires optimization. For some questions, simulation alone can provide useful insights and solutions. But when your aim is to make better decisions and achieve an optimal forecast, the powerful combination of Crystal Ball and OptQuest can help you achieve your goal with speed and accuracy.

Additional Resources

> Crystal Ball Professional Edition
> How does OptQuest differ from Excel's Solver?
> OptQuest White Papers
> OptQuest Technical Support

Was this One-Minute Spotlight helpful?

Please take a moment to e-mail (feedback@crystalball.com) us your comments and suggestions for other One-Minute Spotlights.

Related Topics

For more information and literature citations related to OptQuest and decision variables, see the OptQuest User Manual and Crystal Ball User Manual.

> List of current One-Minute Spotlights
> Crystal Ball Professional Edition features page
> Tech Support home page