This chapter presents a variety of examples using OptQuest. These examples illustrate how to use spreadsheets to model optimization problems, the key features of OptQuest, and the variety of applications for which you can use OptQuest.
Each section includes a problem statement, a description and explanation of the spreadsheet model, the OptQuest solution, and optionally additional practice exercises using the model. All Microsoft Excel model files and associated OptQuest files are in the Examples folder under the main Crystal Ball installation folder. You can also display an index to the examples by choosing one of the following command sequences and selecting from the Examples Guide:
Help, Crystal Ball, Examples Guide (Microsoft Excel 2003 or earlier)
Help, Resources, Examples Guide (Microsoft Excel 2007 or later)
Start, All Programs, Crystal Ball, Examples (Windows Start menu)
Table 4, following, summarizes the examples in this chapter and the features illustrated.
Table 4. OptQuest examples
Application | Decision Variables | Type | Constraints | Requirements | Illustrated Methods |
|---|
Product mix | 5 | discrete | 3 | 1 | Classic optimization example. |
Hotel design and pricing | 3 | discrete | 0 | 1 | Uses a percentile requirement; shows the risk of using a deterministic solution instead of a probabilistic one. |
Budget-constrained project selection | 8 | binary (0-1) | 1 | 0 | Uses binary decision variables for Yes/No decisions. |
Groundwater cleanup | 2 | mixed | 0 | 1 | Uses a category decision variable to select different sets of assumptions. |
Oil field development | 3 | mixed | 0 | 0 | Uses a percentile objective and a lookup table based on a decision variable. |
Portfolio revisited (including Portfolio Revisited EF) | 4 | discrete, step = $100 | 1 | 1 | Combines several objective functions into one multiobjective using extracted statistics and uses the Arbitrage Pricing Theory for incorporating risk. Example of Efficient Frontier. |
Tolerance analysis | 7 | continuous | 0 | 2 | Uses process capability metrics. |
Inventory system | 2 | discrete | 0 | 0 | Searches a wide solution space with large steps, and then refines the search. |
Drill bit replacement | 1 | continuous | 0 | 0 | Defines time as a decision variable. |
Gasoline supply chain | 8 | discrete | 2 | 1 | Classic optimization example. |
Note: | Most of the examples included here use one of the Advanced Options settings for automatically stopping the optimization when either a solution confidence level or certain number of non-improving solutions is reached. If you follow along with these examples, your results should be similar but may not always be identical. |