Home

Version 1.3 Optquest manual

Contents

1. Figure 4 34 shows sample O ptQ uest results T he best solution is to replace the drill bit approximately every 19 9 hours Figure 4 35 below shows the Crystal Ball report for the simulation of this solution The profit per month has a relatively large standard deviation compared to the mean coefficient of variability 0 30 thus it is likely that the true profit month is significantly higher or lower than the mean objective value OptQuest User Manual 149 Chapter Examples U sing OptQuest SE REPORT3 Crystal Ball Report Simulation started on 6 12 00 at 21 17 43 Simulation stopped on 6 12 00 at 21 17 44 Forecast Profit imonth Summary Display Range is from 10 000 00 to 110 000 00 dollars Entire Range is from 8 661 89 to 108 090 46 dollars After 500 Trials the Std Error of the Mean is 769 60 Statistics Trials Mean Median Mode Standard Deviation Variance Skewness Kurtosis Coeff of Variability Range Minimum Range Maximum Range width Mean Std Error Value 500 58 258 76 57 961 38 17 208 72 HH 0 06 2 85 0 30 8 661 89 108 090 46 99 423 58 763 60 Cell F12 Foroa ct Rottmonh Fequenoy Chart eee quaet MPD REPORT Il Figure 4 35 Drill bit replacement simulation report Practice exercise To meet drilling schedules the project manager proposes replacing the bit only after drilling at least 450 meters Define a forecast for the drilling depth cell F5 speci
2. 2 33333 3 22222 13 6666 2 Figure 4 26 Tolerance analysis optimization results OptQuest User Manual OptQuest optimizes the quality specifications for each component to minimize the total assembly cost while maintaining an assembly gap between 0 003 and 0 02 inches Forecast Assembly gap E E lol x Edit Preferences View Run Help 1 000 Trials Frequency Chart 0 Outliers 32 E dP UWL E 24 3 3 016 HAW 18 2 2 m amp oos4 pit MUN o e 8 000 ARRERERRERTRRERTRRERTERERER 0 0 0025 0 0069 0 0113 0 0156 0 0200 inche gt infinity Certainty fi 00 00 4 tinfinity Figure 4 27 Tolerance analysis forecast chart The optimization shows that the solution results in a total assembly cost of 41 83 with the quality specifications set at Piston 2 2 sigma Piston bearing 2 5 sigma Rod 4 0 sigma Rod bearing 2 3 sigma Crankshaft 2 4 sigma Cylinder wall 3 0 sigma Cylinder head depth 3 2 sigma Practice exercise Since the engine performance increases as the assembly gap reaches the design criterion the engineer wants to change the objective to minimize the gap Since reducing the gap increases the costs of the components the engineer wants to place a maximum cap of the total assembly cost at 70 How can you reconfigure OptQuest to accomplish this OptQuest User Manual 137 Chapter Examples U sing OptQuest Inventory system optim
3. A feature that leads you through the steps to create 1 Define the first decision variable a Select cell C13 8 h c Set the Variable T ype to Continuous Select Cell gt Define Decision d Set the lower and upper bounds according to the problem data columns D and E in the worksheet as shown below Cell C13 Define Decision Variable Name Mone Market fund r Variable Bounds Yariable Type G n Lower jo 80 Continuous C Discrete Upper 50 000 Step Cancel Help Figure 1 5 Define Decision Variable window 2 Define the decision variables for cells C14 C15 and C16 according to the values in columns D and E of the worksheet by following the process described in step 1 Selecting decision variables to optimize 1 Start O ptQ uest by either e Selecting CBTools gt OptQuest aT e Clicking on the OptQuest button on the Crystal Ball toolbar Crystal Ball Note The toolbar icon does not appear until the first time you select CBT ools gt OptQuest 2 In OptQuest select File gt New A wizard starts leading you through steps to create a new optimization file The Decision Variable Selection window appears an optimization model This wizard presents windows for you to complete in the proper order OptQuest User Manual 23 Chapter Glossary Term constraint A limitation that restricts the Getting Started OptQuest Note f you make a mistake at any poin
4. Graduate School of Business University of Colorado 1998 http www decisioneering com optquest methodology html M Laguna M etaheuristic Optimization with Evolver Genocop and OptQuest Graduate School of Business University of Colorado 1997 http www decisioneering com optquest comparisons html M Laguna Optimization of Complex Systems with O ptQ uest Graduate School of Business University of Colorado 1997 http www decisioneering com optquest complexsystems html OptQuest User Manual 165 Appendix Advanced Optimization R eferences 166 OptQuest User Manual Appendix B Keyboard Commands Ny Mi In this appendix This appendix lists OptQuest commands and icons OptQuest toolbar Command key combinations and icons Use the following Alt key combinations to execute the listed menu commands without using the mouse OptQuest menu commands and icons Commands Keystrokes Icons About OptQ uest Alt h a Clear selected text Alt e aOR Del Close the current optimization file Alt f c Copy selected text Alt e cOR gt Ctrl c Create a new optimization file Alt f n OR Ctrl n 5 Cut selected text Alt e tOR Ctrl x A Exit OptQuest Alt f x Help contents Alt h c Open an existing optimization file Alt f oOR Ctrl o Open the bar graph window Alt v b lil Open the Constraints window Alt t c u Open the Decision Var
5. The median is the middle value in a set of values For example 6 isthe median of 1 3 6 7 and 9 while the mean is 5 2 If there is an odd number of values the median is found by placing the values in order from smallest to largest and then selecting the middle value If there isan even number of values the median is the mean of the two middle values The mode is the value that occurs most frequently in a set of values In general the greatest degree of clustering occurs at the mode The modal wage for example is the one received by the greatest number of workers The modal color for a new product is the one preferred by the greatest number of consumers In a perfectly symmetrical distribution like the normal distribution shown below on the left the mean median and mode converge at one point In an asymmetrical or skewed distribution like the lognormal distribution on the right the mean median and mode tend to spread out as shown below M Mean Mode ao Median Mean Crystal Ball Note When using continuous distributions it is likely that your forecast will not have two values that are exactly the same When this OptQuest User Manual 55 C hapter Understanding the Terminology occurs Crystal Ball sets the mode to to indicate that the mode is undefined Standard deviation The standard deviation is the square root of the variance for a distribution of values Like the variance it
6. variable requirements requirements variable 158 variables decision defined 45 decision range 45 decision step size 46 decision types 46 determined defined 90 Index 203 variables decision defined 13 in constraints 72 number affecting performance 156 selecting to optimize 68 selection window 69 variance 56 View menu 176 W web pages Decisioneering 160 references 165 what OptQuest does 13 who this program is for 7 Window menu 178 windows bar graph 87 Constraints 72 Current Decision Variables 87 Decision Variable Selection 69 Forecast Selection 75 Optimization Log 88 Options 76 Performance Graph 85 See also dialogs Solution Analysis 91 Status And Solutions 83 wizard defined 23 204 OptQuest User Manual Credits OptQuest User Manual Written by J ames R Evans University of Cincinnati and Manuel Laguna University of Colorado With assistance and editing by Carol Werckman Terry Hardy Eric Wainwright and Beth H eywood Layout design by Mike Marsh Illustrations and screen captures by Tracy Ford using J asc Software Inc s Paint Shop Pro Screen captures for the Windows version were performed using Desert color scheme This document was created electronically using Adobe Framemaker Release 5 5 for Microsoft Windows Typeset using N ewBskvll BT and Univers fonts Plate ready film was output on the Scitex Dolev 200 I magesetter at 2540 dpi Blueline proof printed on the O
7. Current Decision Yariables _ oO x Objective 748753 Simulation Independent Scales en 7487 53 29 Uniform Scales Money Income fund Growth and Aggressive Watching this window can give you a sense for the preferred values for each variable as well as the amount of variation from one solution to the next The fields and options in this window are Objective Displays the resulting objective for the displayed variable values Simulation Displays the simulation number during the optimization or Complete after the optimization is done OptQuest User Manual 87 C hapter Setting Up and Optimizing a M odel Scale options Sets whether the different bar graphsall use the same scale the maximum range that includes all individual ranges or different scales independent Optimization log TheO ptimization Log window displaysthe optimization details such as whether Confidence Testing was on and how many simulations ran as well asthe actual values of each decision variable objective and requirement for each simulation not only the best ones identified in the Status And Solutions window To access this window either Select View gt Optimization Log Ei e Click on the Optimization Log icon Use the vertical scroll bar to scroll through the entire list of solutions The list is saved in the file name specified in Options gt Preferences gt Optimization File You can copy the log file content
8. Run Tools Window Help It also refers to other parts of the manual where the commandsare described in more detail In this appendix OptQuest menu commands File menu Edit menu New Creates a new optimization file for the current Crystal Ball spreadsheet model This command automatically starts the wizard which leads you through all the windows needed to run an optimization See Selecting decision variables to optimize on page 68 Open Opensan existing optimization file Close Closes the current optimization file If the current file was changed since you last saved it OptQuest prompts you to save the file Save Saves the current optimization file O ptQuest saves only the optimization settings in the file not the solutions themselves Save As Saves the current optimization file to another file Print Prints the current view of the OptQ uest window Exit Exits OptQuest See Running a longer simulation of the results on page 93 Cut Removes the selected text from the window and pastes it to the clipboard Copy Puts a copy of the selected text on the clipboard OptQuest User Manual 175 Appendix C Commands Paste Pastes the contents of the clipboard to the cursor location or in place of the selected text Clear Deletes the selected text Duplicate M akes a duplicate of the current forecast in the Forecast Selection window This command is only available when the Forecast Sel
9. Starts an optimization See Start Pause Stop commands on page 82 Pause Pauses the current optimization You can continue a paused optimization See Start Pause Stop commands on page 82 Stop Stops the current optimization You cannot continue a stopped optimization You can only start an optimization over from the beginning See Start Pause Stop commands on page 82 Solution Analysis Opens the Solution Analysis window See Running a solution analysis on page 90 Tools menu Wizard Starts the wizard that leads you through the series of windows you must complete to run an optimization See Decision Variable Selection window on page 69 Decision Variables Opens the Decision Variable Selection window where you select which decision variables to optimize You must select at least one decision variable to optimize See Decision Variable Selection window on page 69 OptQuest User Manual 177 Appendix C Commands Constraints Opens the Constraints window where you can enter constraints on the decision variables N ot all models need constraints See Constraints window on page 72 Forecasts Opens the Forecast Selection window where you select the statistics of the forecast to maximize or minimize You also define requirements in this window See Forecast Selection window on page 75 Options Opensthe Options window where you can change optimization options
10. This title appears in the Status And Solutions window The default is Crystal Ball Simulation Optimization Log File Advanced tab 1s the path to the log file You can change the log file name or location by clicking on Change Log and selecting a new file and folder from the Open dialog The default on Windows 95 98 is C Windows Temp O ptQ uest log The default on Windows NT 2000 is C Temp O ptQ uest log The Advanced tab hasthe following options Optimization Type Lets you select the type of optimization to run either Stochastic using assumptions or Deterministic freezing assumptions If you select Deterministic for a model with assumptions OptQuest uses only the current values in the assumption cells OptQuest User Manual 79 C hapter Setting Up and Optimizing a M odel The Confidence Testing option stops some simulations in progress if the confidence interval around the forecast objective indicates that the current solution isinferior to the current best solution This only works if the statistic used for the forecast objective is the mean standard deviation or a percentile Crystal Ball Note The Confidence Testing option uses the Confidence Level setting in the Crystal Ball Run Preferences to determine the confidence interval By default the Optimization Type is Stochastic and Confidence Testing is On OptQuest Note Running a deterministic calculation before a full optimization m
11. VALUE Ja gt sh Portfolio Sheet Z Sheets FO I Figure 4 20 Portfolio revisited spreadsheet model This new function cell C21 contains the multiobjective relationship described in Equation 4 1 with the risk aversion constant cell C20 broken out into a separate cell The mean return and standard deviation variables in this equation are encoded as special Crystal Ball functions that compute the statistics of other forecast cells These special functions and their parameters are documented in the Developer Kit for Crystal Ball For now just note that the first and second functions compute the mean and standard deviation respectively of the total expected return forecast cell C17 OptQuest User Manual OptQuest solution OptQuest Note Except where indicated this example uses the recommended Crystal Ball run preferences See Setting Crystal Ball run preferences on page 67 Start O ptQuest from the Crystal Ball CBTools menu or toolbar In OptQ uest 1 Open the Portfolio R evisited 2 opt file 2 Start the OptQ uest wizard As you step through the problem note e The decision variables and constraints are the same e The objective refers to the new multiobjective function The statistic to optimize is Final Value to calculate only the statistical values for the total expected return forecast at the end of the simulation 3 Run the optimization The results are shown in Figure 4 21 St
12. initial values 156 noisy objectives 155 number of decision variables 156 number of simulation trials 155 requirements 158 simulation speed 160 performance graph 85 performance affecting factors 154 performance optimization 153 petrochemical engineering references 182 Portfolio Allocation tutorial 20 portfolio revisited example 120 APT method 128 multiobjective method 124 portfolios efficient 121 practice exercises changing number of trials 37 changing objectives 36 correlating assumptions 35 preferences OptQuest 78 preferences suggested run 67 process optimization 65 product mix example 98 project selection example 107 R ranges 60 decision variable 45 references financial applications 182 inventory systems 182 metaheuristics 181 on the web 165 optimization topics 181 petrochemical engineering 182 spreadsheet design 181 tolerance design 182 referrals consulting 9 remaining time viewing 83 requirements affecting performance 158 bounds on statistics 76 defined 25 45 49 defining 73 examples 50 feasibility 49 variable 50 rescale button performance graph 86 results analyzing in Crystal Ball 93 interpreting in OptQuest 90 Run menu OptQuest 177 run preferences suggested 67 runs See simulations Index 201 S screen capture notes 10 sensitivity analysis using tornado chart 160 simulations accuracy of 154 current number 83 limiting time and number 77 running longer 93 saving 78 speed of 160 t
13. 0 0 0 0 0 0 1 1 0 00000 1 1264E 06 0 0 1 1730E 06 0 1 1 2740E 06 1 0 1 1 1 1 1 1 1 3373E 06 1 4453E 06 1 5065E 06 1 1 0 0 0 1 1 1 0 1 0 1 1 1 1 1 1 1 0 0 1 1 1 1 1 0 0 0 Figure 4 10 Project selection model optimization results OptQuest User Manual 109 Chapter 110 Examples U sing OptQuest Figure 4 10 shows the results of an O ptQ uest optimization The best solution identified selects all the projects except for 3 and 5 As Figure 4 11 shows the distribution of profits is highly irregular and depends on the joint success rate of the chosen projects There is a risk of realizing a loss You might wish to evaluate the risks associated with some of the other solutions identified during the search Forecast Total profit lol x Edit Preferences View Run Help 500 Trials Frequency Chart 0 Outliers 046 23 035 7 p dh 17 2 z o RELE Et hri 1 la 2 o c amp 012 4 H ahr hhh HAW 1 EL HE 5 75 8 1 000 000 2 500 000 4 000 000 2 000 000 gt Finfinity Certainty f100 00 4 tInfinity 500 000 Figure 4 11 Project selection solution forecast chart Practice exercise Because a risk of realizing a loss exists an alternative objective might find a solution that gives the highest probability of achieving a positive profit This can be done
14. 1994 Multiobjective optimization Chankong V and Y Y H aimes M ultiobjective Decision M aking Theory and M ethodology New York North Holland 1983 H wang C and A S M Masud M ultiple Objective Decision M aking M ethods and Applications Berlin Springer Verlag 1979 Keeney R and Raiffa H Decisions with M ultiple O bjectives N ew York John Wiley 1976 OptQuest User Manual 181 Bibliography Financial applications Brealey R and S Myers Principles of Corporate Finance 4th ed New York McGrawH ill Inc 1991 Chen N R Roll and S Ross Economic Forces in the Stock Market Journal of Business 59 J uly 1986 383 403 Markowitz H M Portfolio Selection 2nd ed Cambridge MA Blackwell Publishers Ltd 1991 Tolerance design applications Creveling C Tolerance Design A H andbook for Developing Optimal Specifications Reading MA Addison Wesley 1997 Petrochemical engineering applications Humphreys K K Jelen s Cost and Optimization Engineering 3rd ed N ew York McGrawH ill 1991 257 262 Inventory system applications Evans J R and D L Olsen Introduction to Simulation and Risk Analysis N ew York Prentice H all 1998 182 OptQuest User Manual Glossary In this glossary A compilation of terms specific to OptQuest as well as statistical terms used in this manual APT Arbitrage Pricing Theory assumption An estimated value or input to a sp
15. 4 5 6 7 8 9 10 Time Minutes Figure 5 1 Performance comparison Figure 5 1 shows that although both methods find solutions with a similar expected profit after 10 minutes of searching method A jumpsto the range of high quality solutions faster than B For the criteria listed above method A performs better than method B OptQuest User Manual 153 Chapter Optimization Tips and Suggestions While using OptQuest you will obtain performance profiles similar to method A OptQuest s search methodology see the references in Appendix B is very aggressive and attempts to find high quality solutions immediately causing large improvements with respect to the initial solution early in the search This is critical when OptQuest can perform onlya limited number of simulations within the available time limit H owever several factors affect O ptQ uest s performance and the importance of these factors varies from one situation to another This chapter reviews these factors and offers tips and suggestions on how to achieve maximum performance Factors that affect search performance Glossary Term heuristic An approximate and self educating technique for improving solutions Any heuristic method for solving problems cannot guarantee to find the optimal solution It might only find a solution that is very close to the optimal solution Thisis why maximizing performance is so important The following is a list of the
16. 400 5 2936 07 447 g 2954 47 423 1 2960 33 2960 71 Figure 1 2 O ptQ uest results for Future Apartments model For this optimization the best rental price is 431 and will result in an expected profit of 2 961 OptQuest User Manual 17 Chapter Getting Started Closing the tutorial To close the tutorial and return to Excel 1 SelectFile gt Exit OptQuest prompts you to save the optimization file before closing 2 Clickon No OptQuest asks whether to copy the selected values of the decision variables into your spreadsheet This lets you perform further analyses using the best solution 3 Click on Yes OptQ uest restores the Crystal Ball simulation of the best solution to your spreadsheet You can now analyze the forecast windows create reports and use any other Crystal Ball options How OptQuest works Glossary Term metaheuristics A family of optimization approaches that includes genetic algorithms simulated annealing tabu search scatter search and their hybrids Traditional search methods such as the one used in the Excel Solver work well when finding local solutions around a given starting point with model data that are precisely known These methods fail however when searching for global solutions to real world problems that contain significant amounts of uncertainty Recent developments in optimization have produced efficient search methods capable of finding optimal s
17. 50 percent U nder these assumptions a Crystal Ball simulation of the room demand for the optimal set of prices in Figure 4 6 shows that the risk of demand exceeding capacity isapproximately 50 Clearly such risk is unacceptable A more appropriate requirement would be to limit the probability of demand exceeding the hotel capacity to a smaller value for example 20 104 OptQuest User Manual Forecast Total room demand Of x Edit Preferences View Run Help S00Trials Frequency Chart OOutliers 026 13 020 fms eg o eb ed db d b ape 2 2 4 9 75 7 oa EL ee RE EEE Ze De e ee 65 2 F T 2 OOF 4 50 ME HHHHHHN L 11 325 amp 420 00 435 00 450 00 465 00 480 00 450 00 Certainty IE 4 Infinity Figure 4 6 Forecast chart for stochastic solution of hotel pricing model OptQuest solution OptQuest Note Except where indicated this example uses the recommended Crystal Ball run preferences See Setting Crystal Ball run preferences on page 67 Start O ptQ uest from the Crystal Ball CBTools menu or toolbar In OptQuest 1 Open the Hotel Design optfile 2 Start the OptQ uest wizard As you step through the problem note e This problem has three decision variables and no constraints e To ensure that the probability of demand exceeding capacity does not exceed 20 the projected number of rooms sold cell H 12 is a forecast in the Crystal Ball model with a requirement ad
18. And a constraint was Income fund lt 5000 This also results in an infeasible problem You can make infeasible problems feasible by fixing the inconsistencies of the relationships modeled by the constraints OptQuest detects optimization models that are constraint infeasible and reports them to you If amodel is constraint feasible O ptQ uest will always find a feasible solution and search for the optimal solution i e the best solution that satisfies all constraints OptQuest User Manual 47 C hapter Understanding the Terminology Objective Each optimization model has one objective a forecast cell that mathematically represents the model s objective in terms of the assumption and decision cells O ptQ uest s job is to find the optimal value of the objective by selecting and improving different values for the decision variables When model data are uncertain and can only be described using probability distributions the objective itself will have some probability distribution for any set of decision variables You can find this probability distribution by defining the objective as a forecast and using Crystal Ball to simulate the model Forecast statistics You can t use an entire forecast distribution as the objective but must characterize the distribution using a single summary measure for comparing and choosing one distribution over another Thus to use O ptQ uest you must select a statistic of one forecast
19. Certainty fi 00 00 4 Hinfinity O 00E 0 3 13E 5 gt infinity Certainty 100 00 4 Infinity Figure 4 15 Groundwater cleanup risk forecast chart Practice exercise The photo oxidation method was the cheapest process for the required efficiency level but because of the high cost the community decides to relax the risk requirement to 3 out of 10 000 3E 4 Based on this new requirement what method is now the cheapest and how much would the community save OptQuest User Manual 115 Chapter Examples U sing OptQuest Oil field development Problem statement Oil companies need to assess new fields or prospects where very little hard data exists Based on seismic data explorationists can estimate the probability distribution of the reserve size With little actual data available the discovery team wants to quantify and optimize the N et Present Value N PV of this asset You can simplify this analysis by representing the production profile by three phases Phase Description Build up The period when you drill wells to gain enough production to fill the facilities Plateau After reaching the desired production rate plateau the period when you continue production at that rate as long asthe reservoir pressure is constant and until you produce a certain fraction of the reserves In the early stages of development you can only estimate this fraction and production above a certain rate influences plateau durat
20. In almost all cases O ptQ uest will efficientlyfind an optimal or near optimal solution among large sets of possible alternatives even when exploring only a small fraction of them OptQuest User Manual 13 Chapter Getting Started Futura Apartments model The easiest way to understand what O ptQuest doesisto apply it to a simple example Suppose that you have recently purchased the Futura Apartments complex One of your critical decisions is the amount of rent to charge You have researched the situation and created a spreadsheet model to help you make a knowledgeable decision From the analysis of the price structures and occupancy rates of similar apartment complexes you have estimated that demand for rental units is a linear function of the rent charged and is expressed as Number of units rented 1 rent per unit 85 for rents between 400 and 600 In addition you have estimated that operating costs will average about 15 000 per month for the entire complex To begin the tutorial 1 Start Crystal Ball 2 Open the Futura With O ptQ uest workbook from the Crystal Ball Examples folder OptQuest Note This spreadsheet is an enhanced version of the original Futura Apartments examplein Crystal Ball that contains no decision variables 1 You can find the linear relationship of a dependent variable to one or more independent variables using the regression tool in Microsoft Excel s Analysis Toolpak or CB
21. Predictor available with the Professional Edition of Crystal Ball 14 OptQuest User Manual The Futura Apartments worksheet appears as shown below Gh Futura with OptQuest _ oO x Futura Apartments Number atunis er 27 Rent ger lini 85 Number of Units Rented 35 Rent per Unit 500 00 Monthly Expenses Me Zoos vorste Profit or Loss 2 500 00 1 Price demand parameters Slope cera arena X FI FUTURA dal BR Figure 1 1 Futura Apartments worksheet In your spreadsheet the rent is set to 500 where Number of units rented 1 500 85 35 and the total profit will be 2 500 If all the data were certain the optimal value for the rent could be found using a simple data table H owever in a more realistic situation monthly operating costs and the price demand function parameters 1 and 85 are not certain probability distributions for these assumptions are already defined for this example Therefore determining the best rental price is not a straightforward exercise 3 Before running O ptQ uest set the following run preferences Maximum number of trials set to 500 e Sampling method set to Latin hypercube Random Number Generation set to Use Same Sequence Of Random Numbers with an Initial Seed Value of 999 OptQuest User Manual 15 Chapter Getting Started Running OptQuest Use the following steps to run OptQuest for the Fu
22. See Options window on page 76 Window menu Help menu The OptQuest Window menu only has standard functions There are no functions in this menu unique to O ptQ uest Contents and Index Displays O ptQuest s on line help files About O ptQ uest Displays version and copyright information for OptQ uest 178 OptQuest User Manual Bibliography In this bibliography Bibliography entries by subject Spreadsheet design Optimization topics Financial applications Tolerance design applications Petrochemical engineering applications Inventory system applications Spreadsheet design Thommes M C Proper Spreadsheet Design Boston Boyd and Fraser Publishing Co 1992 Optimization topics Metaheuristics Glover F J P Kelly and M Laguna N ew Advances and Applications of Combining Simulation and Optimization Proceedings of the 1996 Winter Simulation Conference Edited by M Charnes D J Morrice D T Brunner and J J Swain 1996 144 152 Glover F and M Laguna Tabu Search Boston Kluwer Academic Publishers 1997 Laguna M Scatter Search to appear in H andbook of Applied Optimization P M Pardalos and M G C Resende Eds Oxford Academic Press 1999 Stochastic probabilistic optimization theory Infanger G Planning Under Uncertainty Boston Boyd amp Fraser Publishing 1994 Kall P and S W Wallace Stochastic P rogramming N ew York J ohn Wiley and Sons
23. This lets you repeat simulations using the same set of random numbers to accurately compare the simulation results Crystal Ball Note When your Crystal Ball forecast has extreme outliers run the optimization with several different seed values U pdate Forecast Windows and Check Precision Control Every 50 Trials OptQ uest checks ongoing simulations periodically to stop and eliminate simulations that have a small chance of improving upon the best solution OptQuest User Manual 67 C hapter Setting Up and Optimizing a M odel Selecting decision variables to optimize After you define the assumptions decision variables and forecasts in Crystal Ball you can begin the optimization process in OptQuest The first step of this process is selecting decision variables to optimize The values of these decision variables will change with each simulation until O ptQ uest finds values that yield the best objective For some analyses you might fix the values of certain decision variables and optimize the rest 68 OptQuest User Manual Start O ptQ uest by either e Selecting CBTools gt OptQuest e Clicking on OptQuest s icon on the Crystal Ball toolbar In OptQuest select File gt New A wizard starts leading you through the windows to complete for the optimization The Decision Variable Selection window appears first listing every decision variable defined in the Crystal Ball model For details on the fields in this window see D
24. and set the sample size to 1 000 2 Start O ptQ uest 3 Open the Tolerance Analysis opt file OptQuest User Manual 135 Chapter 136 4 Star Examples U sing OptQuest t the O ptQ uest wizard As you step through the problem note This problem has seven decision variables one for the quality specification for each assembly component with a continuous range between 1 and 5 sigmas The problem has no constraints The objective is to minimize the total assembly costs N ote that the total cost function does not depend on any assumption cells and thus has a deterministic value You can use the final value statistic in these cases to retrieve the deterministic value Two requirements ensure that the assembly gap is between 0 003 and 0 02 inches 5 Run the optimization Status and Solutions Minimize Objective Total assembly cost Final_Yalue c program files crystal ball examples optquest files tolerance Optimization File Tolerance Analysis Optimization is Complete 2 6910E 03 Infeasible 1 7801E 02 3 00000 3 00000 i 46 0518 2 7471E 03 Infeasible 1 7648E 02 3 22164 3 74515 3 00000 3 13 49 6522 3 3572E 03 1 7480E 02 2 33333 2 33333 3 66667 3 4 135 49 1681 3 1811E 03 1 7847E 02 2 08738 2 23179 3 70036 3 6 45 3648 3 3518E 03 1 7512E 02 2 33333 2 77778 3 1801E 03 1r605E 2 12 333331 3 22990
25. be the same as the inventory level At the beginning of the week if any outstanding orders have arrived the manager adds the order quantity to the current inventory level OptQuest User Manual 139 Chapter Examples U sing OptQuest Next determine the weekly demand and check if sufficient inventory ison hand to meet this demand If not then the number of lost sales is the demand minus the current inventory Subtract the current inventory level from the inventory position set current inventory to zero and compute the lost sales cost If sufficient inventory is available satisfy all demand from stock and reduce both the inventory level and inventory position by the amount of demand The next step is to check if the inventory position is at or below the reorder point If so place an order for Q units and compute the order cost The inventory position is increased by Q but the inventory level remains the same Schedule a receipt of Q units to arrive after the lead time Finally compute the holding cost based on the inventory level at the end of the week after demand is satisfied and the total cost Open the file Inventory System xls This spreadsheet model shown below implements this logic T he basic problem data are shown in the upper left corner The decision variables are the order quantity cell E3 and the reorder point cell E4 The initial inventory is set equal to the chosen order quantity The lead time specified is a
26. by optimizing the certainty statistic that is finding the best solution that generates the most values between two specified endpoints in this case the largest number of observations between zero and Infinity Modify the OptQuest file to solve the problem with this objective OptQuest User Manual Groundwater cleanup Problem statement A small community gets its water from wells that tap into an old large aquifer Recently an environmental impact study found toxic contamination in the groundwater due to improperly disposed chemicals from a nearby manufacturing plant Since this is the community s only source of potable water and the health risk due to exposure to these chemicals is potentially large the study recommends that the community reduce the overall risk to belowa 1 in 10 000 cancer risk with 95 certainty 95th percentile less than 1E 4 A task force narrowed down the number of appropriate treatment methods to three It then requested bids from environmental remediation companies to reduce the level of contamination down to recommended standards using one of these methods Your remediation company wants to bid on the project The costs for the different cleanup methods vary according to the resources and time required for each cleanup efficiency With historical and site specific data available you want to find the best process and efficiency level that minimizes cost and still meets the study s recommended standa
27. by this one function no additional requirements are necessary Geometrically the optimal solution for a multiobjective function occurs in the saddle point between the optimal endpoints of the individual objectives In the case of the two objective function above the optimal solution occurs somewhere on the efficient frontier between the maximum return portfolio and the minimum risk portfolio mean return standard deviation of return For k 0 5 the optimal solution occurs at the point where the return minus one half the standard deviation has the highest value OptQuest User Manual 125 C hapter Examples U sing OptQuest 126 Spreadsheet model Open the Portfolio Revisited workbook found in the Crystal Ball Example folder The total expected return forecast assumptions and decision variables are the same asin the original model Scroll down to see the new items added as shown in Figure 4 20 Portfolio Revisited xIs Portfolio Allocation Revisited Annual Lower Upper Risk factar favesimenis return bund Money Market fund Income fund Growth and Income fund Aggressive Growth fund 10 000 Total amount valable 100 000 Amaunt Total weighted Decision variables invested aoe HER Money Market fund 25 000 42 500 Income fund 25 000 Growth and Income fund 25 000 Total amaun Aggressive Growth fund 25 000 mmvesiad Total expected retin 6 5001 Bu e 100 000 Risk aversion constant 0 50 Mean minus sida
28. different types It also presents examples of the different model types The last part of this chapter describes the different statistics available to describe the objective In this chapter What is an optimization model Glossary Term model A representation of a problem or system in a worksheet application such as Excel or Lotus 1 2 3 Glossary Term optimization model A model that seeks to maximize or minimize some quantity such as profit or risk In today s competitive global economy people are faced with many difficult decisions These decisions include allocating financial resources building or expanding facilities managing inventories and determining product mix strategies Such decisions might involve thousands or millions of potential alternatives Considering and evaluating each of them would be impractical or even impossible A model can provide valuable assistance in analyzing decisions and finding good solutions M odels capture the most important features of a problem and present them in a form that is easy to interpret Models often provide insights that intuition alone cannot An optimization model has three major elements decision variables constraints and an objective decision variables Are quantities over which you have control for example the amount of product to make the number of dollars to allocate among different investments or which projects to select from among a limited
29. lot of time or if timeis restricted OptQuest might not find a good solution Thus as the number of decision variables and range of search increases use larger step sizes and fewer trials initially Later refine the search around good candidates Status and Solutions Optimization is Complete ERER EES 2859 89 2850 20 2844 24 Za e Best 73 2843 06 2034 53 m L gt Figure 4 31 Inventory system second optimization results 144 OptQuest User Manual Figure 4 31 shows the results of an optimization with Q and R bounded to the range 300 to 360 with astep size of 1 and 1 000 trials per simulation changed in Crystal Ball OptQuest identified the best solution asQ 330 and R 321 There was very little change from the initial solution Figure 4 32 shows the Crystal Ball forecast chart for the annual total costs You can see that the distribution of total annual cost is highly concentrated around the mean but is also skewed far to the right indicating that very high values of cost are possible although not very likely For such highly skewed distributions run more trials than usual since statistics like the mean and tail end percentiles can be susceptible to extreme outliers Forecast Total Annual Costs lol x Edit Preferences View Run Help 1 000 Trials Frequency Chart 30 Outliers 087 87 065 65 2 2 oO o 435 2 2 o c 02 217 000 0 2 250 2 688 3 12
30. meters Revenue 31 176 91 cycle Drilling expenses 23 750 00 cycle Profit 7 426 91 cycle Revenue meter drilled i Replacement cycles 8 00 month Drilling days Time between replacements 30 00 hours Profit 553 415 32 month To Gatinze tine between raplace J IEIS O IZE PNA ial 4 gt oh Sheet dal Figure 4 33 Drill bit replacement problem spreadsheet model 148 OptQuest User Manual OptQuest solution OptQuest Note Except where indicated this example uses the recommended Crystal Ball run preferences See Setting Crystal Ball run preferences on page 67 Start O ptQ uest from the Crystal Ball CBTools menu or toolbar In OptQuest 1 Open theDrill Bit Replacement opt file 2 Start the O ptQ uest wizard As you step through the problem note e This problem has one decision variable whose search limits are 1 and 50 e The problem has no constraints or goals e The objective is to maximizing the mean profit month 3 Run the optimization 7 Status and Solutions loj x ee aa File c program files crystal Drill Bit Replacement Policy Optimization is Complete Maximize Objective em Cycle time hours 103 58252 4 20 1691 108 58257 7 19 9981 115 58258 5 19 9349 126 58258 7 19 6505 177 58258 8 19 8807 58258 8 19 8859 Best 383 58258 8 19 8838 E Figure 4 34 Drill bit replacement model optimization results
31. near the minimum rate although some are much higher Curve B illustrates negative skewness skewed to the left where most of the wages are near the maximum although some are much lower If you describe the curves statistically curve A is positively skewed and might have a skewness coefficient of 0 5 while curve B is negatively skewed and might have a 0 5 skewness coefficient A skewness value greater than 1 or less than 1 indicates a highly skewed distribution A value between 0 5 and 1 or 1 and 0 5 indicates a moderately skewed distribution A value between 0 5 and 0 5 indicates a fairly symmetrical distribution 58 OptQuest User Manual Kurtosis Kurtosis refers to the peakedness of a distribution For example a perfectly symmetrical distribution of values can look either very peaked or very flat as illustrated below Suppose the curves in the example above represent the distribution of wages within a large company Curve A is fairly peaked since most of the employees receive about the same wage with few receiving very high or low wages Curve B is flat topped indicating that the wages cover a wider spread Describing the curves statistically Curve A is fairly peaked with a kurtosis of about 4 Curve B which is fairly flat might have a kurtosis of 2 A normal distribution is usually used as the standard of reference and has a kurtosis of 3 Distributions with a kurtosis value less than 3 ar
32. over the range of a variable requirement For more information on this window see Efficient Frontier window on page 89 K Start Pause Stop commands These commands for starting pausing and stopping an optimization are under the Run menu gt Start Starts a new optimization Thisis unavailable when an optimization is already running or paused Hitt Pause Pauses the current optimization This is available whenever an optimization is running When you pause an optimization anew button appears below the toolbar to resume the optimization E Stop Stops the current optimization This is available whenever an optimization is running When you stop an optimization you cannot resume that optimization The Run icon becomes available but it starts a new optimization 82 OptQuest User Manual H owever O ptQ uest does remember the best solution of the stopped optimization and uses it as its starting point if you run the optimization again Status And Solutions window This window displays current information about the current optimization To access this window either e Run the wizard e Select View gt Status And Solutions e Click on the Status And Solutions icon This window has three areas Status e Optimization File e Solutions Status The optimization status is in the top left corner above the best solution results and on a bar across the top of the solution columns T his area of
33. people in the community as a function of Risk factors Cells Description Distribution Cancer Potency C18 C20 Cancer potency of each Lognormal contaminant Concentration D18 D20 Concentration of each Normal Before contaminant before cleanup Volume Of C23 Interindividual variability of Normal with Water Per Day volume of water consumed lower bound each day of 0 Body Weight C22 Interindividual variability of Normal with body weights in the lower bound community of 0 Remediation costs of the various cleanup methods cells E8 E10 are a function of Remediation Cot Racker Cells Description Distribution Fixed Costs C8 C10 Flat costs for each method to pay Triangular for initial setup Variable Costs D8 D10 Costs for each method based on Uniform how long the cleanup takes Efficiency D14 Percent of contaminants that the None cleanup process removes Each remediation method has a different cost for different efficiency levels OptQuest User Manual 113 C hapter Examples U sing OptQuest OptQuest solution OptQuest Note Except where indicated this example uses the recommended Crystal Ball run preferences See Setting Crystal Ball run preferences on page 67 To run the optimization 1 In Crystal Ball set the number of trials per simulation to 1 000 since tail end percentile requirements need more accuracy 2 Start O ptQ uest Op
34. solution O ptQ uest generates Each time OptQuest identifies a better solution closer to requirement feasibility or with a better objective during the optimization it adds a new line to the Status And Solutions window showing the new objective value and the values of the decision variables Status and Solutions n A ojx UnNamed opt Optimization File Crystal Ball Simulation Portfolio Allocation sls Optimization is Complete Maximize Objective Requirement Total expected return 1 Total expected return 2 Money Income Growth and Aggres Mean N Std_Dev lt 8000 Market fund fund Income fund Growth 10412 2 16194 9 Infeasible 0 00000 10000 00 0 00000 g000C 1601 32 1860 00 0 00000 10000 00 0 00000 10000 8 7005 67 7294 01 18333 3 10000 00 48333 3 23333 13 7318 25 7735 80 14398 0 10000 00 48402 7 27198 117 7424 15 7901 34 16934 3 10000 00 33175 7 3715 122 7459 31 7871 26 13361 6 10532 2 46157 1 29948 7574 29 7992 45 16025 7 10000 00 38761 1 35213 10000 00 38928 1 Figure 3 1 Status And Solutions window 84 OptQuest User Manual The results columns include Simulation Lists the number of the best previous best and current simulations T he best up to that point has Best before the simulation number The current one has Current before the simulation number Objective Lists the value of the objective forecast sta
35. the mean and standard deviation of returns as the criteria for balancing risk and reward You can also use other criteria for selecting portfolios Instead of using the mean return you could select the median or mode as the measure of central tendency T hese selection criteria would be called median standard deviation efficient or mode standard deviation efficient Instead of using the standard deviation of return you could select the variance range minimum or lowend percentile as the measure of risk or uncertainty T hese selection criteria would be mean variance efficient mean range minimum efficient or mean percentile efficient 1 Reference Harry M Markowitz P ortfolio Selection 2nd ed Cambridge MA Blackwell Publishers Ltd 1991 122 OptQuest User Manual Method 1 Statistical Note The mode is usually only available for discrete valued forecast distributions where distinct values might occur more than once during the simulation Efficient Frontier optimization OptQuest has a feature that creates an efficient frontier for you automatically To use the Efficient Frontier function in OptQuest you need only define a variable requirement OptQuest will calculate points within the variable requirement range Spreadsheet model Open the Portfolio Revisited EF xls workbook found in the Crystal Ball Examples folder The total expected return forecast assumptions and decision variables are the same as in the origi
36. the Step Size dialog appears for you to define the step size Enter a value and click on OK The step size appears in parentheses after the word Discrete Displays the Excel workbook file that the decision variable is from This field is for display only Displays the Excel worksheet that the decision variable is from This field is for display only Displays the Excel cell that the decision variable is from This field is for display only Moves all the selected decision variables to the top of the list and all the unselected decision variables to the bottom of the list Specifying constraints In OptQuest constraints limit the possible solutions to a model in terms of relationships among the decision variables For example in the Portfolio Allocation model from Chapter 1 the total investment was limited to 100 000 In this window this is limited by the equation Money Market fund Income fund Growth and Income fund Aggressive Growth fund lt 100000 You can only use the Constraints window to specify linear constraints To use the Constraints window to specify a linear constraint 1 In the Constraint editor enter a linear mathematical constraint For information on the Constraint editor syntax see Constraints window below 2 For additional constraints enter them in the Constraint editor on their own line 3 Click on OK The Forecast Selection window appears next To specify nonlinea
37. the window appears only while the optimization is running The optimization status lists Time Remaining Simulation Displays the time left to complete the optimization Even if you limit the optimization by the number of simulations OptQuest calculates how long that number of simulations will take based on the length of the first simulation and displays the remaining time Displays the number of the current simulation Current actions Displays various optimization actions as they occur such as Evaluating Trial Solutions Optimization is Complete Initializing Running a Test or Optimizing OptQuest User Manual 83 C hapter Setting Up and Optimizing a M odel Optimization File The Optimization File information is in the top right corner of the window above the best solution results T his area lists Optimization file name Displays the optimization file path If you have not yet saved the file the default name is UnNamed opt Description of the optimization model Isa title for the model To change this descriptive title see Preferences tab on page 78 The default is Crystal Ball Simulation Solutions OptQuest displays the results of the best simulationsin the solutions area of this window The first best simulation is always either e The suggested values used in your worksheet if those values satisfy the constraints imposed e The first constraint feasible
38. work in Crystal Ball or Excel or make changes in OptQuest when running an optimization but you can work in other programs Do not close Excel Crystal Ball or OptQuest while running an optimization To run the optimization 1 Either e When prompted to start the optimization click on Yes The Status And Solutions window appears For more information on this window see Status And Solutions window on page 83 e Select Run gt Start See Start Pause Stop commands below 2 Pause stop or rerun the optimization For more information on how pausing stopping and rerunning optimizations works see Start Pause Stop commands below 3 View the status during the optimization While the optimization is running you can select to view these three windows from the View menu OptQuest User Manual 81 C hapter Setting Up and Optimizing a M odel Performance Graph N Shows a plot of the objective value asa function of the number of simulations evaluated The wizard opens this window automatically For more information on this window see page 85 Bar Graph Shows how the value of each decision variable changes during the optimization search procedure For more information on this window see page 87 Optimization Log Provides details of the sequence of solutions generated during the search For more information on this window see page 88 B Efficient Frontier Plots a set of objective values found
39. 00 42 500 If this amount were greater than the limit of 100 000 this solution would not be feasible and could not be chosen 3 Chen N R Roll and S Ross Economic Forces in the Stock Market Journal of Business 59 July 1986 383 403 OptQuest User Manual 129 Chapter 130 Amaunt Total weighted Decision variables invested HER Examples U sing OptQuest Spreadsheet model Portfolio Revisited xls Portfolio Allocation Revisited Annual Lawer Upper Fitsk faciar favesiments retuin aund hound Money Market fund 50 000 Incorne fund 10 000 25 000 Growth and Income fund 0 80 000 Aggressive Growth fund 10 000 100 000 A Total amaun avala amp le 100 000 Lim 100 000 lal gt Portfolio Ense fd Money Marketfund 25 000 42 500 Income fund 25 000 Growth and Income fund 25 000 Talal amoun Aggressive Growth fund 25 000 nested Total exgected reti 6 500 Sbjectine 100 000 Risk aversion constant 0 50 Mean minus sida VALUE Figure 4 22 Portfolio revisited problem spreadsheet model Open the Portfolio Revisited xls worksheet found in the Crystal Ball Example folder The total expected return forecast assumptions decision variables and the original constraint limiting the total investment to 100 000 are the same asin the original model The new item is a constraint limiting the total weighted risk cell F13 calculated by money income growth and aggressive weig
40. 183333 10000 00 48333 3 23332 13 7318 25 7735 80 14398 0 10000 00 48402 7 27198 7901 34 33175 7 37155 7871 26 461571 29948 7992 45 38761 1 10000 00 38928 1 Figure 1 14 Portfolio allocation optimization results with risk Before analyzing these new results in Crystal Ball save the settings file and exit O ptQ uest 10 After O ptQ uest completes the optimization save the current optimization settings by selecting File gt Save The Save As dialog appears 32 OptQuest User Manual Portfolio Allocation xls 11 Save the file and name it Portfolio Allocation opt 12 Click on Save This saves only the optimization settings you must save the Crystal Ball model separately in Excel Optimization files automatically have the extension OPT and you can reopen them by selecting File gt Open the next time you run OptQ uet 13 To exit O ptQ uest select File gt Exit If you hadn t saved the optimization file yet O ptQ uest would prompt you to save it OptQuest asks if you want to copy the best solution into your spreadsheet model 14 Click on Yes OptQ uest copies the best solution into your Crystal Ball model and then closes You can also copy one of the other solutions into your Crystal Ball model by selecting the corresponding row in the Status And Solutions window before exiting The associated simulation for the selected solution is automatically restored when you exit a
41. 21 Method 1 Efficient Frontier optimization oo 123 Method 2 Multiobjective optimization oo eee 124 Method 3 Arbitrage Pricing Theory 128 Tolerance ANALYSIS eee miau inea iE a 132 Problem statement cccccccccssecssssssrssssssssessseesssressnreseats 132 Spreadsheet model ceccscescecseesssescsrsssseesseeseessereeenness 134 OptQ est solution saririsa a ia 135 Practice exercise aeneesensanenennenennnennnnnnennnennnnenenennnnnnnn nennen 137 Inventory system optimization emenneernenennnennnennennnnnnn 138 Problem statement cccccscccesessssesersssseessresseesseeessnreseats 138 Spreadsheet model ueuseeeeennsnennnnnnnnnnnennenennennnenn nennen 139 Opt uest solution anne een ee 142 Practic amp amp xerdiseT einen 145 Practice exercise 2 ccccsccccsscssssecsesesersssnrssseressesssesseseesens 146 Drill bit replacement policy sermenneenneenennneennnnnnnnnnnnnn 146 Problem statement cccccsccccsesssssessrsssseesseesseesssresenreseats 146 Spreadsheet model uuznseneeennsnennnnnnnnnnnennenenennnenen nenne nenn 148 O ptQ uest solution sissi ns iiaa 149 Practice exerci SE near 150 Chapter 5 Optimization Tips and Suggestions OVEPVIC Weis cesiecceuiticciansaniici anima dadenondioniadi AEREA 153 Factors that affect search performance sser 154 Simulation ACCULACY oes eee cette tee eee e ee tee tee nenn nennen 154 Number of decision variables sesser 156 Initial values u essen 156 B
42. 300 10 00 21 300 50 YES 250 200 0 YES 450 24 40 00 450 2 13 20 00 Zo F gt bih Inventory 4 Sheet 4 Sheet3 4 Sheet4 4 SheetS 4 Sheet6 4 Sheet 4 4 Ol Figure 4 28 Inventory system problem spreadsheet model In the actual simulation the beginning inventory position and inventory level for each week equals the ending levels for the previous week except for the first week which is specified in the problem data The demand isin column F as Crystal Ball assumptions Since all shortages are lost sales the inventory level cannot be negative Thus the ending inventory each week is BR beginning inventory level demand orders received ending inventory max 0 Lost sales are computed by checking if demand exceeds available stock and computing the difference The spreadsheet simulates 52 weeks or one year of operation of the inventory system Since the objective isto minimize the mean total annual cost cell O6 is defined as a forecast cell OptQuest User Manual 141 Chapter Examples U sing OptQuest Column determines whether the manager should place an order by checking if the beginning inventory position minusthe weekly demand is at or below the reorder point The ending inventory position is ending beginning inventory inventory weekly demand lost sales weekly orders position position This formula might not appear to be obvious Clearly if there are no lost sales the ending
43. 5 3 563 4 000 Dollars gt Fintinity Certainty 100 00 4 Infinity Figure 4 32 Inventory system final solution forecast chart Practice exercise 1 If you had defined each cost component as a forecast the shortage costs could run very large A large number of shortages each year can have a detrimental effect on customer goodwill Suppose that you limit the number of shortages to at most 25 each year This is equivalent to restricting the maximum range of the shortage cost to 2 500 since each stockout costs 100 and the minimum shortage cost is zero Incorporate this into OptQuest by defining a requirement for the total annual shortage cost forecast statistic Range_M ax with an upper limit of 2 500 Achieving fewer shortages will probably require either higher order quantities or higher reorder points Thus increase the range of the decision variables to provide enough room to search OptQuest User Manual 145 C hapter Examples U sing OptQuest Practice exercise 2 Try other values for lead time such as 1 3 or 4 weeks Compare these to the two week solution Drill bit replacement policy Problem statement When drilling wellsin certain types of terrain the performance of a drill bit erodes with time because of wear After T hours the drilling rate can be expressed as dM 15 __ meters per hour Equation 4 2 dH T 10 For example after 5 hours of consecutive use starting with a new dril
44. 9 29 0 r9000 10000 00 35000 0 55000 0 5 9310 97 0 00000 10000 00 15000 0 75000 0 10001 9 0 00000 10000 00 10237 2 10412 2 0 00000 10000 00 0 00000 90000 0 Figure 1 10 OptQuest solution results The last line in the Status And Solutions window shows the best solution found by OptQuest All the money is allocated to the fund that has the highest return the Aggressive Growth fund with the exception of the minimal amount in the Income fund that the investor required The investor s strategy maximized the return of the portfolio but at a price high risk due to high volatility and little diversification Is this really what the investor wanted To find out the investor must interpret the results Interpreting the results To interpret the O ptQ uest results 1 After O ptQ uest completes the optimization copy the optimization results to your model by selecting Edit gt Copy To Excel 2 In Crystal Ball view the forecast chart for the best simulation OptQuest User Manual 29 Chapter Getting Started Forecast Total expected return Of x Edit Preferences View Run Help 500 Trials Frequency Chart 1 Outlier 032 i 16 10240 Hebb th7 12 2 x oO BOI 70 RAN T o oo a 2 fa amp 008 4 HAAR Ed n 4 3 000 0 40 000 15 000 10 000 35 000 60 000 dollars gt infi
45. OptQuest for Crystal Ball 2000 DECISIONEERING 1515 Arapahoe St Suite 1311 Denver Colorado USA 80202 303 534 1515 Phone Fax or 1 800 289 2550 303 534 4818 Web Site www decisioneering com Developed by Systems Inc This manual and the software described in it are furnished under license and may only be used or copied in accordance with the terms of the license agreement Information in this document is provided for informational purposes only is subject to change without notice and does not represent a commitment asto merchantability or fitness for a particular purpose by Decisioneering Inc No part of this manual may be reproduced or transmitted in any form or by any means electronic or mechanical including photocopying and recording for any purpose without the express written permission of Decisioneering Inc Written designed and published in the United States of America To purchase additional copies of this document contact the Technical Services or Sales Department at the address below Decisioneering Inc 1515 Arapahoe St Suite 1311 Denver Colorado USA 80202 Phone 303 534 1515 Toll free sales 1 800 289 2550 Fax 303 534 4818 1988 2001 Decisioneering Inc OptQuest is a registered trademark of O ptTek Systems Inc Crystal Ball isa registered trademark of Decisioneering Inc Other product names mentioned herein may be trademarks and or registered tradema
46. Portfolio Sheet A Sheets 7 dal Figure 1 4 Portfolio Allocation worksheet OptQuest User Manual 21 Chapter Getting Started 22 In this example problem data are specified in rows 5 through 9 Model inputs the values of the decision variables the model output the forecast objective and the constraint the total amount invested are on the bottom half of the worksheet This model already has the assumptions and forecast cells defined in Crystal Ball 2 Make sure the assumptions are defined as Assumption Cell Distribution Parameters Money C5 uniform minimum 2 market fund maximum 4 Income fund C6 normal mean 5 standard deviation 5 Growth and C7 normal mean 7 income fund standard deviation 12 Aggressive C8 normal mean 11 growth fund standard deviation 18 Crystal Ball Note f you need help viewing or defining assumptions or forecasts see your Crystal Ball U ser M anual 3 Set the following run preferences Maximum number of trials set to 500 e Sampling method set to Latin hypercube e Random Number Generation set to U se Same Sequence Of Random Numbers with an Initial Seed Value of 999 Defining decision variables The next step is to identify the decision variables in the model This step isnot required when you create Crystal Ball simulation models H owever it is mandatory when using OptQuest OptQuest User Manual Glossary Term wizard
47. ach component is defined as an assumption with a normal distribution having a mean equal to the nominal dimension and a standard deviation equal to the component sigma Note that the mean and standard deviation are cell references to these cells The dimensions of the assemblies are a cumulation of their respective components statistical dimensions T he difference in length between the cylinder assembly cell C5 and the piston assembly cell C4 is the assembly gap cell C6 Component cost cells F14 F18 and F23 F 24 is a nonlinear function of quality specification The higher the specification the higher the cost Also note that each component has a different cost function associated with it In addition to the recommended options before running OptQuest in Crystal Ball select Run gt Run Preferences and set e The maximum trials to 1 000 e The sample size to 1 000 Since the model is heavily dependent on the tails of the forecast distribution these settings will provide higher accuracy and will be adequate for this example In actual practice to gain better accuracy the engineer might want to run longer simulations of 5 000 or 10 000 trials OptQuest solution OptQuest Note Except where indicated above this example uses the recommended Crystal Ball run preferences See Setting Crystal Ball run preferences on page 67 To run the optimization 1 In Crystal Ball set the number of trials per simulation to 1 000
48. arches a wide solution space with large steps and then refines the search Drill bit 1 continuous O O Defines time as a decision variable replacement Product mix Problem statement Ray s Red H ots Inc manufactures five types of sausages The number of pounds of four ingredients veal pork beef and casing used per unit of product and the profit generated per unit are given in the table below Products Veal Pork Beef Casing Profit Per Unit Summer 0 00 2 50 1 00 1 00 1 25 Sausage Bratwurst 4 00 1 00 0 00 1 50 1 80 Italian 1 00 3 00 1 50 1 00 1 40 Sausage Pepperoni 0 00 4 00 0 00 2 00 2 10 Polish Sausage 0 00 1 00 3 00 1 50 1 70 A limited amount of ingredients is available for the next production cycle Specifically only 12 520 pounds of veal 14 100 pounds of pork 6 480 pounds of beef and 10 800 pounds of casing are available 98 OptQuest User Manual Complicating this situation is e The unit profits are only estimates because all customer contracts have not been finalized e The amount of casing used per unit might be more than anticipated because of production losses due to tearing or partial rejections during inspection The problem is to determine how many pounds of each product to produce in order to maximize gross profit without running out of meat ingredients or casing during the manufacturing run Spreadsheet model The Prod
49. assumption or decision variable cells simulation A set of Crystal Ball trials O ptQuest finds the best values by running multiple simulations for different sets of decision variable values skewed An asymmetrical distribution skewness The measure of the degree of deviation of a curve from the norm of an asymmetric distribution The greater the degree of skewness the more points of the curve lie to either side of the peak of the curve A normal distribution curve having no skewness is symmetrical spreadsheet model Any spreadsheet that represents an actual or hypothetical system or set of relationships spreadsheet model standard deviation The square root of the variance for a distribution A measurement of the variability of a distribution i e the dispersion of values around the mean Glossary 191 step size Defines the difference between successive values of a discrete decision variable in the defined range For example a discrete decision variable with a range of 1 to 5 and a step size of 1 can take on only the values 1 2 3 4 or 5 a discrete decision variable with a range of 0 to 17 with a step size of 5 can take on only the values 0 5 10 and 15 stochastic A model or system with one or more random variables STOIIP Stock Tank Oil Initially In Place STOIIP is the estimated reserves of an oil field in millions of barrels mmbbls trial A three step process in which Crystal Ball generates ra
50. atus and Solutions Ale x c program files crystal ballyexampleskoptquest r Optimization File Portfolio Revisited Method 2 Optimization is Complete Maximize Objective Mean minus stdev Money Income Growth and ggressive Final_Value Market fund fund Income fund Growth fund 25000 0 25000 0 25000 0 25000 0 63 3028 69 25884 1 24837 5 23612 9 25665 5 66 3035 50 26768 1 24675 0 22225 9 26331 1 81 3046 19 50000 0 25000 0 15000 0 10000 00 82 3047 07 37500 0 25000 0 20000 0 e17500 0 111 3059 92 32138 6 25000 0 13634 4 29199 1 191 3062 21 33048 9 25000 0 15674 1 26044 3 3074 41 42976 5 24577 3 15582 6 16863 6 Figure 4 21 Portfolio revisited multiobjective optimization results When should you use multiobjective optimization and when should you use single objectives with requirements T he former method is especially useful when it is difficult to determine reasonable lower or upper bounds for requirement statistics OptQuest User Manual 127 Chapter Method 3 Examples U sing OptQuest This method is also recommended for situations where OptQuest has trouble finding feasible solutions that satisfy many requirements T he latter method is generally easier to implement and understand Practice exercise RAROC which stands for Risk Adjusted Return on Capital isa multiobjective function gaining popularity in use as a measure of portfolio performance Th
51. believe that the stability of the forecast statistics varies greatly depending on the decision variable values Precision control periodically calculates the accuracy of the forecast mean standard deviation and any indicated percentile during the simulation When the simulation reaches a desired accuracy it stops regardless of the number of trials already run This feature is especially useful for optimization models such as Portfolio Allocation where the forecast statistics are highly sensitive to the decision variables When O ptQ uest selects conservative investments the variability of the expected return is low and the statistics are relatively stable When O ptQ uest selects aggressive investments the variability is high and the statistics are relatively less stable Using precision control increases your forecast statistic accuracy while avoiding running too many trials when a simulation reaches this accuracy quickly The difficulty with using this feature is that finding the appropriate precision control settings might require some trial and error Every model is unique and therefore it is very challenging to decide whether to use absolute or relative precision what is the best precision value in either case and which statistics to apply the precision to For more information on setting the precision control feature see the Crystal Ball U ser M anual To see the effects of using precision control with the Portfolio Allocation
52. bly gap must be between 0 003 and 0 02 inches This might seem like a simple problem but since milling processes are not exact and quality control has a direct effect on prices components have an error associated with each called tolerance When stacked these errors compile or add together to create a cumulative tolerance 132 OptQuest User Manual probability When a batch of components is milled and measured the components actual dimensions form a distribution around the desired or nominal dimension Standard deviation or sigma is a measure of the variation present in a batch of components The components then have a statistical dimension based on this distribution The quality of the component and the associated tolerance is described in terms of sigmas with 1 sigma component having the largest tolerance and a5 sigma component the smallest This is called the quality specification 1 sigma 5 sigma component component fe Q Qa statistical dimension statistical dimension One simplified solution takes the total tolerance allowed and divides it by the number of components But due to individual component complexity and process differences in manufacturing each component of the assembly has a different cost function associated with the quality specification This then becomes a juggling act to balance cumulative tolerance and associated cost OptQuest User Manual 133 Chapter 134 Examples U si
53. cantly improve O ptQ uest s performance by selecting meaningful bounds for the decision variables Suppose for example that the bounds for three variables X Y and Z are 0 lt X lt 100 0 lt Y lt 100 0 lt Z lt 100 And in addition to the bounds there is the following constraint 10 X 12 Y 20 Z lt 200 Although the optimization model is correct the variable bounds are not meaningful A better set of bounds for these variables would be 0 lt X lt 20 0 lt Y lt 16 667 0 lt Z lt 10 These bounds take into consideration the values of the coefficients and the constraint limit to determine the maximum value for each variable The new tighter bounds result in a more efficient search for the optimal values of the decision variables Since constraints limit the decision variables you are optimizing OptQ uest can eliminate sets of decision variable values that are constraint infeasible before it spends the time running the simulation Therefore limiting the optimization with constraints is very time effective OptQuest User Manual 157 C hapter Optimization Tips and Suggestions OptQuest Note You can only definelinear constraints in theC onstraints window For information on defining nonlinear constraints see Specifying constraints on page 71 Requirements While the search process benefits from the use of constraints and tight bounds performance generally suffers when you include requirem
54. cccccsscccsecssseesnresseessressetessaeesseesenees 101 H otel design and pricing problem rmneernennnnnnnenn 102 Problem statement cccccscccsessrsscssecseresseressessssesseesees 102 Spreadsheet model uuunenseeensenenennennnnnennnnennnnennnnenennenennn 103 OptQuest solution eenneennsennnrsnnnnnnnnnennnennnnnnnnnnenn ernennen 105 Budget constrained project selection 107 Problem statement ccccccscccsecsrsesssecssresseeesssesssressresees 107 Spreadsheet model uunensessnsenennnenennnnennnnennnnennnnenennennnnn 108 OptQuest solution uenneennnennnrsnnnnnnnnnennnennnn nenn nenn ernennen 109 Practice 6XerCi Senenin a utes 110 Groundwater Cleanup usrsrnnennneennennennnnennnen nenn nnnnnnnnnn nn 111 Problem statement cccccsccscsecsssssersssseessresseesseressnressats 111 Spreadsheet model uzuseneesnenennnnnnnnnenennensnennnenn nennen 112 O ptQ Uest solution sseni n nun nnnnn nennen 114 Practice Exercise u a 115 Oil field development ersnsneenneernnnennnnnnnnennn nenn nnnnnnnnnn non 116 Problem statement cccccccccesesseessserssssrsssresseeesseeesnreseats 116 Spreadsheet model uuenseseneenenennnnnnnnnenennenenennnnenn nennen 117 OptQ uest solution arriera eE 118 Portfolio revisited enseeenseneessnensnnnnnnennnnennnnnnnnnennnnnnnnennnennnnn 120 Problem statement cccccccccesesssssssessssrsssresseesssreesnreseats 120 Efficient portfolios u nun ea 1
55. comma formats appropriately Divide complex calculations into several cells to minimize the chance for error and enhance understanding e Place comments next to formula cells for explanation if needed 66 OptQuest User Manual Consult a reference such as M C Thommes P roper Spreadsheet Design Boston Boyd and Fraser Publishing Co 1992 for further discussion of good worksheet design Defining assumptions decision variables and forecasts Once you build and test the spreadsheet you can define your assumptions decision variables and forecasts For more information on defining assumptions decision variables and forecasts see the Crystal Ball U ser M anual Setting Crystal Ball run preferences For optimization purposes you should usually use the following Crystal Ball run preferences Maximum number of trials set to 500 Central tendency statistics such as mean median and mode usually stabilize sufficiently at 500 trials per simulation Tail end percentiles and maximum and minimum range values generally require at least 1 000 trials Sampling method set to Latin hypercube Latin hypercube sampling increases the quality of the solutions especially the accuracy of the mean statistic Random Number Generation set to U se Same Sequence Of Random Numbers with an Initial Seed Value of 999 The initial seed value determines the first number in the sequence of random numbers generated for the assumption cells
56. decision variable name to where the cursor is double click on a decision variable name in the Variables column U se an asterisk to multiply a constant and a variable eg 3 X 1 Click on Sum All Variables 2 Putaless than sign lt before the equals sign 3 Enter the total investment as 100 000 so that the final constraint looks like Money market fund Income fund Growth and income fund Aggressive growth fund lt 100000 OptQuest Note Don t use or a comma in the constraint See Constraints window on page 72 for other rules on constraints Constraints Input any linear constraints on decision variables Money Market fund Income fund Growth and Income fund Aggressive Growth fund lt 100000 Glossary Term objective A formula in terms of decision variables that gives a mathematical representation of the model s goal Glossary Term requirement A restriction on a forecast statistic that requires the statistic to fall between specified lower and upper limits for a solution to be considered feasible Sum All Variab Income fund Figure 1 7 Constraints window 4 Clickon OK The Forecast Selection window appears Selecting the forecast objective OptQuest requires that you select one forecast statistic to be the objective to minimize or maximize In addition to defining an objective you can define optimization requirements described in Editing the optimizati
57. decision variables OptQuest User Manual 65 C hapter Setting Up and Optimizing a M odel A working space for all complex calculations formulas and data tables e A separate output section that provides the model results Examine the Portfolio Allocation spreadsheet model below introduced in Chapter 1 for an example Gh Portfolio Allocation xls Portfolio Allocation Model Annual Lower Upper favesiments return bound bound Money Market fund 50 000 Income fund 25 000 Growth and Income fund 80 000 Aggressive Growth fund 10 000 100 000 Total amount avala amp le 100 000 fae Decision variables invested estas Money Market fund 25 000 Income fund 25 000 Pee Growth and Income fund 25 000 Fatal amaun Aggressive Growth fund 25 000 nesie Total expected retin 6 500 Gbjiactine 100 000 X Fh Portfolio She A Seea y dal oly Note that all input variables assumptions and decision variables are in rows 5 through 9 and rows 13 through 16 and forecast cells reference the cellsin their calculations not values directly Therefore you could easily change any values and the forecast calculations would be automatically updated Other tips that improve the usefulness of your spreadsheet are e Reference input data only with cell references or range names so that any changes are automatically reflected throughout the worksheet e Use formats such as currency or
58. ded in the Forecast Selection window Specifically the total room demand is limited by a requirement using the forecast statistic Percentile 80 with an upper bound of 450 OptQuest User Manual 105 C hapter Examples U sing OptQuest 3 Run the optimization Status and Solutions loj x Optimization File c program files crystal ballexamplesSoptquest files hotel Hotel Design Problem Optimization is Complete Maximize Objective Requirement Total R Total d d 4 r 2 g Se ee ee Standard price Gold price Platinum price 85 98 80 1 38000 0 400 000 139 6 39703 4 447 986 100 149 19 39995 1 449 438 80 102 144 53 40261 3 448 051 81 103 129 71 40402 1 446 936 81 110 120 179 40402 2 447 244 81 109 121 184 40423 7 448 327 81 108 121 187 40447 0 449 126 81 108 120 Best 214 40447 2 449 625 80 110 132 Figure 4 7 H otel pricing model optimization results The results are shown in Figure 4 7 The Crystal Ball simulation of this solution in Figure 4 8 verifies that the chance of demand exceeding capacity is just slightly less than 20 T Forecast Total room demand Ioj x Edit Preferences View Run Help Cell H12 Percentiles Percentile 0 10 20 30 40 50 a Required to one ae be lt 450 100 Figure 4 8 Hotel pricing solution forecast chart 106 OptQuest User Manual Budget constrained project selection Problem statement The R am
59. delines for the minimum number of simulations required for a given number of decision variables in a problem are Decision variables Minimum number of simulations Less than 10 100 Between 10 and 20 500 Between 20 and 50 2000 Between 50 and 100 5000 For very large numbers of decision variables you might try increasing the number of simulations by lowering the number of trials per simulation at least initially After you find an approximate solution you can rerun the optimization by using the approximate solution as suggested values further restricting the bounds on the decision variables and increasing the number of trials to find more accurate results Initial values The initial values are the values listed in the Suggested Values column of the Decision Variables window T he initial values are important because the closer they are to the optimal value the faster O ptQuest might find the optimal solution If the initial values are constraint infeasible they will be ignored 156 OptQuest User Manual For potentially large models with many decision variables you might find it helpful to first run a deterministic optimization to search for good initial values see page 79 Then use the results as your initial values and run a stochastic optimization This technique however might not work well if you have objectives or requirements defined with other than central tendency statistics Bounds and constraints You can signifi
60. design and pricing 102 inventory system 138 oil field development 116 overview 97 portfolio revisited 120 product mix 98 project selection 107 requirements 50 tolerance analysis 132 F feasibility constraint defined 47 requirement 49 feasible solutions 17 File menu 175 files optimization 84 optimization name 83 financial applications references 182 flow chart OptQuest 19 fonts changing 78 forecast statistics defined 48 maximizing or minimizing 49 forecasts cells as objectives 48 defining 67 restricting statistics 49 selecting objective 73 statistics defined 45 frontier efficient window 89 Futura Apartments tutorial 14 Index 197 G getting started 11 graphs bar 87 performance 85 groundwater cleanup example 111 H K Help menu 178 heuristic methods 154 hotel design and pricing example 102 how OptQuest works 18 how this manual is organized 8 icons list 169 OptQuest 169 initial values affecting performance 156 inventory system example 138 inventory systems references 182 keyboard commands 169 OptQuest 169 kurtosis 59 L linear models 51 log optimization 88 M manual conventions 10 mathematical operations in constraints 72 maximizing forecast statistic 49 mean 54 mean standard error 60 median 55 menu commands OptQuest 175 menus Edit 175 File 175 Help 178 Run OptQuest 177 Tools O ptQ uest 177 View 176 Window 178 198 OptQuest User Manual metaheuristics defined 18 refer
61. dit the optimization file to add this risk limitation and still maximize the total expected return To edit the optimization file 1 Return to OptQuest by clicking on the O ptQ uest button on the Windows taskbar 2 Open the Forecast Selection window The window appears with the Total Expected Return forecast listed in the first row Click in the existing forecast row 4 Select Edit gt Duplicate This creates a new row with the forecast named Total Expected Return 2 5 Inthe new row select Requirement from the Select list From the Forecast Statistic drop down list select Std_Dev Set the upper bound to 8000 OptQuest User Manual 31 Chapter Getting Started This adds a requirement that the standard deviation of the expected returns must be less than 8 000 for a solution to be considered feasible Forecast Selection o Maximize Objective Total expected return 1 Mean Requirement Z Total expected return 2 Std_Dev dollars a Figure 1 13 Forecast selection window with new requirement 8 Click on OK gt 9 Run the optimization by selecting Run gt Start Status and Solutions lioj x UnNamed opt Optimization File Crystal Ball Simulation Portfolio Allocation xls SUI is Complete 1 161949 Infeasible 0 00000 2 1601 32 1860 00 0 00000 10000 00 0 00000 10000 8 7005 67 7294 01
62. e also range width The range minimum is the smallest number in a set and the range maximum is the largest number The range is the difference between the range minimum and the range maximum For example if the range minimum is 10 and the range maximum is 70 then the range is 60 Mean standard error The mean standard error statistic lets you estimate the accuracy of your simulation results and thus determine how many trials are necessary to ensure an acceptable level of error T his statistic tells you the probability of the estimated mean deviating from the true mean by more than a specified amount T he probability that the true mean of the forecast is within plus or minus the mean standard error of the estimated mean is approximately 68 Statistical Note The mean standard error statistic only provides information on the accuracy of the mean and can be used as a general guide to the accuracy of the simulation The standard error for other statistics such as mode and median will probably differ from the mean standard error 60 OptQuest User Manual Certainty Final value Certainty describes the percentage of simulation results that fall within arange For instance in the portfolio allocation example from Chapter 1 if your objective was to achieve the highest probability of a minimum return of 8 000 you might choose a range of 8 000 to Infinity and then maximize the certainty of this range When you select the c
63. e is the rent per unit to charge The objective is to maximize net profit which can be expressed as Net profit Revenue Expenses Number of units rented Rent unit Monthly expenses 0 1 Rent unit 85 Rent unit Monthly expenses There are no constraints in this example The Portfolio Allocation model in the second tutorial in Chapter 1 is more complex The decision variables are the amounts to allocate to a money market fund an income fund a growth and income fund and an aggressive growth fund The objective is to maximize the total expected annual return 0 03 M oney market fund 0 05 Income fund 0 07 Growth and income fund 0 11 Aggressive growth fund There is one constraint the total amount of money available to invest Money market fund Income fund Growth and income fund Aggressive growth fund lt 100000 If all returns for this example are constant rather than uncertain then the model is a continuous linear deterministic optimization model All variables are continuous that is they might assume any fractional value The objective is linear Finally the returns on each prospective investment are known with certainty i e the returns are true values not distributions thus the model is deterministic In contrast the Futura Apartments model is discrete nonlinear and stochastic It is discrete because the decision variable is defined in whole dollar increments It is nonlinear b
64. e RAROC equation is generally stated as mean return mean return P5 where P5 is the 5th percentile of the distribution of expected returns The divisor mean return P5 is sometimes called the Value At Risk VAR since it measures the difference between the expected performance of the portfolio and the potential loss Taken together the RAROC equation calculates the ratio of the mean return to the value at risk When maximizing this function the best solutions will give the highest possible returns while at the same time producing the lowest possible value at risk In the Portfolio Revisited model add a multiobjective function that computes RAROC Run OptQuest to maximize this value Hint see the CB GetForePercent function in the Developer K it for Crystal Ball Arbitrage Pricing Theory A different approach to incorporating risk in a decision model is called Arbitrage Pricing Theory APT APT does not ask whether portfolios are efficient Instead it assumes that a stock or mutual fund s return is based partly on macroeconomic influences and partly on events unique to the underlying company or assets Further this theory only considers macroeconomic influences since diversification asin a portfolio practically eliminates unique risk 128 2 Brealey R and S Myers Principles of Corporate Finance 4th ed New York NY McGraw Hill Inc 1991 OptQuest User Manual Glossary Term risk factor A n
65. e described as platykurtic meaning flat and distributions with a kurtosis value greater than 3 are leptokurtic meaning peaked OptQuest Note In some places OptQ uest uses a standard reference of 0 Coefficient of variability The coefficient of variability measures the variability of a forecast compared to the mean value Since this statistic is independent of the forecast units you can use it to compare the variability of two or more forecasts even when the forecast scales differ For example if you are comparing the forecast for a penny stock with the forecast for a stock on the N ew York Stock Exchange you would expect the average variation standard OptQuest User Manual 59 C hapter Understanding the Terminology deviation of the penny stock price to appear smaller than the variation of the NYSE stock H owever if you compare the coefficient of variability statistic for the two forecasts you will likely observe that the penny stock shows significantly more variation on a relative scale The coefficient of variability typically ranges from a value greater than 0 to 1 It may exceed 1 in a small number of cases in which the standard deviation of the forecast is unusually high The coefficient of variability is calculated by dividing the standard deviation by the mean as follows coefficient of variability gt To present thisin percentage form simply multiply the result of the above calculation by 100 Rang
66. e number of rooms sold by 3 Similarly a 1 increase in the price will decrease the number of rooms sold by 3 For any proposed set of prices the projected number of rooms of a given type sold can be found using the formula rooms sold H EAW O C where Variable Is the H Historical average number of rooms sold E Elasticity N N ew price 102 OptQuest User Manual C Current price The hotel owners want to keep the price of a standard room between 70 and 90 a gold room between 90 and 110 and a platinum room between 120 and 149 All pricesare in whole dollar increments discrete Although the rooms may be renovated and reconfigured there are no plans to expand beyond the current 450 room capacity Spreadsheet model Hotel Design xls of x Average Projected Daily New Rooms Projected Room type Rate Sold Revenue Elastici Price Sold Revenue 55 00 250 21 250 00 65 00 250 21 250 00 98 00 100 9 600 00 98 00 100 9 800 00 139 00 50 6950 00 139 00 50 6 950 00 Atawinize PESSINA Total 38 000 00 SURRE fa ages Canad bi Hotel Sheet A Sheet3 f Sheett f SheetS A Sheet6 4 Sheet A Sheets Z S Figure 4 4 Hotel pricing problem spreadsheet mode Open the Hotel Design example shown in Figure 4 4 The decision variables correspond to cells G7 through G9 If all the data are regarded as fixed a deterministic optimization model might be in terms of the ce
67. ecause the objective net profit includes a term that isthe square of the decision variable rent unit Finally it is stochastic because the price demand function parameters and the monthly expenses are not known with certainty OptQuest User Manual 53 C hapter Understanding the Terminology OptQuest is designed to handle any and all types of optimization models with only one limitation constraints must be linear unless you model the nonlinear constraints as requirements See Specifying constraints on page 71 Statistics This section explains the forecast statistics you can choose in OptQuest to define the optimization s objective These terms are listed in the Statistics window when you run a simulation in Crystal Ball and in the reports that you can create Statistic See Mean page 54 Median page 55 Mode page 55 Standard deviation page 56 Variance page 56 Percentile page 57 Skewness page 58 Kurtosis page 59 Coefficient of variability page 59 Range also range width page 60 Mean standard error page 60 Certainty page 61 Final value page 61 The formulas for many of the statistics are listed in the Crystal Ball User M anual Mean The mean of a set of values is found by adding the values and dividing their sum by the number of values The term average usually refers to the mean For example 5 2 is the mean or average of 1 3 6 7 and 9 54 OptQuest User Manual Median Mode
68. ecision Variable Selection window below Select which decision variables to optimize By default all are selected Optionally change the lower and upper bounds for each decision variable By default O ptQuest uses the limits you entered when you defined the decision variables The tighter the bounds you specify the fewer values O ptQ uest must search to find the optimal solution H owever this efficiency comes at the expense of missing the optimal solution if it lies outside the specified bounds Optionally change the start values for each decision variable in the Suggested Value column By default O ptQuest uses the cell values in your Crystal Ball model If the suggested values lie outside of the specified bounds or do not meet the problem constraints O ptQ uest ignores them Check that the T ype column indicates the correct type of values You can change the variable type here or in the Define Decision Variable dialog of Crystal Ball 7 Clickon OK The Constraints window appears next Decision Variable Selection window The Decision Variable Selection window lets you select which defined decision variables to optimize To access this window either Run the wizard Select Tools gt Decision Variables a e Click on the Decision Variables icon Decision Variable Selection Select variables and set bounds Variable Name Suggested Value Upper Bound Money Market fund 50000 Continuous Income fu
69. ection window is active See Selecting the forecast objective on page 73 Copy To Excel Copiesthe simulation for the selected solution in the Status And Solutions window to the spreadsheet model This command is only available after an optimization when the Status And Solutions window is active See Preferences tab on page 78 or Running a longer simulation of the results on page 93 Select All Selects all the text in the optimization log window This option is only available when the Optimization Log window is active See Optimization log on page 88 View menu Status And Solutions Opens the Status And Solutions window See Status And Solutions window on page 83 Performance Graph Opens the Performance Graph window where O ptQuest displays the best solutions on a graph See Performance graph on page 85 Bar Graph Opens the Current Decision Variables window where O ptQ uest displays the values of the decision variables for the current simulation as a bar graph See Bar graph on page 87 176 OptQuest User Manual Log Opens the Optimization Log window where O ptQ uest lists all the optimization information for each simulation See Optimization log on page 88 Efficient Frontier Opensthe Efficient Frontier window where O ptQ uest plots a set of objective values found over the range of a variable requirement See Efficient Frontier window on page 89 Run menu Start
70. ements e The objective is to minimize the total annual costs simulations since this is a larger model 4 Run the optimization Double the amount of time you have been using for Status and Solutions ioj x r Optimization File c program files crystal invent System Optimization is Complete Minimize Objective gas ns er Order Quantity Reorder Point g 4003 50 400 300 10 3755 64 400 400 11 3396 32 400 365 16 3293 58 365 365 17 2911 71 335 335 2889 80 2849 12 d Figure 4 29 Inventory system model optimization results Sample results are shown in Figure 4 29 O ptQ uest identified the best solution as having an order quantity of 330 and a reorder point of 320 Figure 4 30 shows the performance graph which gives the rate of improvement of the objective function as each new simulation was evaluated during the search You can see that O ptQ uest quickly converged to a good solution value OptQuest User Manual 143 C hapter Examples U sing OptQuest Performance Graph Figure 4 30 Inventory system optimization performance graph Because this optimization used a step size of 5 you can fine tune the solution by searching more closely around the best solution using a smaller step size while also increasing the number of trials for better precision Thisis a good practice since choosing too small a step size initially consumes a
71. en the Groundwater Cleanup opt file 4 Start the O ptQ uest wizard As you step through the problem note e There are two decision variables remediation method cell D13 and cleanup efficiency cell D14 e This problem has no constraints e The objective is to minimize the remediation cost while requiring that the population risk be under 1E 4 with 95 certainty 5 Run the optimization 7 Status and Solutions lol x r Optimization File c program files crystal ball examples optquest Groundwater Cleanup Optimization is Complete Minimize Objective Requirement Total Remediation Cost Population Risk Cleanup Remediation Mean Percentile 95 lt 1e 4 Efficien Method 1 9000 03 2 3029E 04 Infeasible 0 800000 13500 0 2 3029E 05 0 980000 1 12920 0 2 3029E 05 0 980000 2 11446 3 2 3029E 05 0 980000 3 11068 1 6833E 05 0 933274 3 3 3 10951 8 9 3471E 05 0 318824 10901 4 1 0070E 04 0 912549 Figure 4 13 Groundwater cleanup optimization results 114 OptQuest User Manual The results are shown in Figure 4 13 The solution in Figure 4 13 minimizes costs at 10 901 while keeping the risk level at 1 01E 4 The distributions for the total remediation cost and the population risk are shown below Edit Preferences View Run Help O17 Probability a o D Ss S 9 750 10 313 10 875 11 438 12 000 fin thousands gt Finfinity
72. ences 181 methods heuristic 154 metaheuristic 18 minimizing forecast statistic 49 mixed models 51 mode 55 models 43 creating 65 deterministic 52 deterministic illustrated 44 linear and nonlinear 51 optimization defined 43 setting type 79 setting up 63 stochastic 52 stochastic optimization illustrated 45 models optimization types 51 multiobjectives 124 N noisy objectives 155 nonlinear constraints 71 nonlinear models 51 number of simulations setting 77 0 objectives changing practice exercise 36 complex 159 defined 25 noisy 155 selecting forecast 73 using forecasts as 48 oil field development example 116 operations mathematical in constraints 72 optimal solution definition 13 optimization log 88 optimization models types 51 optimization performance 153 optimization process overview 65 optimization tips 151 optimization topics references 181 Index 199 optimizations deterministic model illustrated 44 files 84 model defined 43 running 81 starting and stopping 82 status of 81 stochastic model illustrated 45 options advanced 79 preferences 78 selecting 76 time 77 OptQuest flow 19 how it works 18 keyboard commands and icons 169 menu commands 175 options 76 steps to use 65 toolbar 171 what it does 13 OptQuest Run menu 177 organization manual 8 P pause command 82 peakedness statistical 59 percentage from best 92 performance factors bounds and constraints 157 complex objectives 159
73. ents in the optimization model for two reasons e Requirements are very time consuming to evaluate since OptQuest must run an entire simulation before determining whether the results are requirement infeasible e To avoid running requirement infeasible simulations OptQ uest must identify the characteristics of solutions likely to be requirement feasible T his makes the search more complex and requires more time When you use requirements you should increase the search time by at least 50 based on the time used for an equivalent problem without requirements If you have lots of requirements that O ptQ uest can t easily satisfy consider combining your requirements into one multiobjective function See Method 2 Multiobjective optimization on page 124 for an example of using multiobjective functions Variable requirements Optimizations with variable requirements take significantly longer to run than optimizations without basically because they are running an optimization for each point in the variable requirement range To speed up optimizations with variable requirements you can either e Manually use the Efficient Frontier window s N ext Point button to force OptQuest to work on the next point in the range e Increase the Tolerance value in the Advanced Options window 158 OptQuest User Manual To increase the tolerance you must understand how O ptQ uest decides that it is time to move on to another point Firs
74. ertainty of a forecast as a requirement you must first define the range that you want the forecast values to fall in such as between 8 000 and positive infinity Then you must define in the Lower Bounds column the minimum percentage of results that must fall in the defined range for the solution to be feasible such as 60 Certainty 051 507 038 380 2 7 o 5 025 233 2 P a m3 126 amp 000 0 2 000 5 500 9 000 12 500 16 000 Dollars gt 8 000 Certainty 68 93 4 Infinity By default the range of certainty is from negative infinity to positive infinity The certainty for this range is always 100 The final value is the last value that is calculated for a forecast during a simulation The final value is useful when a forecast contains a function that accumulates values across the trials of a simulation or isa function that calculates the statistic of another forecast See Portfolio revisited on page 120 and Tolerance analysis on page 132 for examples using final value OptQuest User Manual 61 C hapter Understanding the Terminology 62 OptQuest User Manual Chapter 3 Setting Up and Optimizinga M odel Ny Mi In this chapter Developing a model Defining decision variables Selecting decision variables to optimize Specifying constraints Selecting the forecast objective Selecting optimization options Running the optimization Interpreting the results This chapter describ
75. es how to use OptQuest step by step It also gives details about each of the windows and dialogs in OptQuest including all the fields and options Overview Setting up and optimizing a model using O ptQ uest requires the following steps Create a Crystal Ball model of the problem Define the decision variables within Crystal Ball In OptQuest select decision variables to optimize Specify any constraints on the decision variables Select the forecast objective and define any requirements Select optimization options na PWN bP Run the optimization 8 Interpret the results You perform steps 1 and 2 in Crystal Ball 3 through 7 in OptQuest and 8 in both Developing the Crystal Ball model Before using OptQuest you must first develop a useful Crystal Ball model This entails building a well tested spreadsheet in Excel and then defining assumptions and forecast cells using Crystal Ball You should refine the Crystal Ball model and run several simulations to ensure that the model is working correctly and that the results are what you expect Developing the worksheet You should build your spreadsheet model using principles of good design since this makes understanding and modifying it easier The spreadsheet should include e A descriptive title e An input data area separate from the output and any working space Place all input variables in their own cells where you can later define them as assumptions or
76. examples In the Portfolio Allocation example of Chapter 1 the investor wantsto impose a condition that limits the standard deviation of the total return Because the standard deviation is a forecast statistic and not a decision variable this restriction isa requirement The following are some examples of requirements on forecast statistics that you could specify 95th percentile gt 1 000 1 lt skewness lt 1 Range 1 000 to 2 000 gt 50 certainty Variable requirements Variable requirements let you define a range for a requirement bound instead of a single point and a number of points to check within the range O ptQuest runs one full optimization for each point in the range starting with the most limiting requirement point This lets you see the effects of tightening or loosening a requirement When you define a variable requirement you first select a forecast either the objective forecast or another forecast Like the objective or the requirement you then select a statistic for that forecast but instead of maximizing or minimizing it you select to restrict the upper bound or the lower bound You then define the upper or lower bound with a range Variable requirement example In the Portfolio Allocation example of Chapter 1 the investor wants to impose a condition that limits the standard deviation of the total return Because the standard deviation is a forecast statistic and not a decision variable this res
77. fashion This involves running a simulation for an initial set of values analyzing the results changing one or more values re running the simulation and repeating the process until you find a satisfactory solution This process can be very tedious and time consuming even for small models and it is often not clear how to adjust the values from one simulation to the next A more rigorous method systematically enumerates all possible alternatives Although this approach guarantees optimal solutions it has very limited application Suppose that a simulation model depends on only two decision variables If each variable has 10 possible values trying each combination requires 100 simulations 102 alternatives If each simulation is very short e g 2 seconds then the entire process could be done in approximately 3 minutes of computer time H owever instead of two decision variables consider six then consider that trying all combinations requires 1 000 000 simulations 10 alternatives or approximately 23 days of computer time tis easily possible for complete enumeration to take weeks months or even years to carry out OptQuest overcomes the limitations of both the ad hoc and the enumerative methods by intelligently searching for optimal solutions to your simulation models You describe your optimization problem in OptQuest and then let it search for the values of decision variables that maximize or minimize a predefined objective
78. field development solution forecast chart OptQuest User Manual 119 Chapter Portfolio revisited Problem statement Examples U sing OptQuest The investor from Chapter 1 has 100 000 to invest in four assets Below is a relisting of the investor s expected annual returns and the minimum and maximum amounts the investor is comfortable allocating to each investment Investment Annual retum Lower bound Upper bound Money 3 0 50 000 market fund Income fund 5 10 000 25 000 Growth and 7 0 80 000 income fund Aggressive 11 10 000 100 000 growth fund When the investor maximized the portfolio return without regard to risk OptQuest allocated almost all the money to the investment with the highest return T his strategy didn t result in a portfolio that maintained risk at a manageable level Only limiting the standard deviation of the total expected return generated a more diversified portfolio The next section examines the reasons for this 120 OptQuest User Manual Efficient portfolios If you were to examine all the possible combinations of investment strategies for the given assets you would notice that each portfolio had a specific mean return and standard deviation of return associated with it Plotting the means on one axis and the standard deviations on another axis you can create a graph like this mean return reward gt risk standard deviati
79. fy a requirement in OptQ uest that the 10th percentile of the drilling depth must be greater than 450 and determine the optimum cycle time and mean profit month that meets this goal 150 OptQuest User Manual Chapter 5 Optimization Tips and Suggestions Ny Mi In this chapter e What affects the search e Screening out low priority decision variables Thischapter describesthe different factors that affect how O ptQ uest searches for optimal solutions Understanding how these factors affect the optimization helps you control the speed and accuracy of the search This chapter also includes discussion of the Crystal Ball Tornado Chart tool and how you can use it to analyze the sensitivity of the variables in your model and screen out minor decision variables Overview Glossary Term performance The ability to find high quality solutions as fast as possible There are many factors that influence the performance of OptQuest For example consider two optimization methods A and B applied to an investment problem with the objective of maximizing expected returns When you evaluate the performance of each method you must look at which method e Finds an investment portfolio with a larger expected return e Jumps to the range of high quality solutions faster Below is the performance graph for the two hypothetical methods Expected Profit Method A amp Method B 2 3
80. gure 4 19 Portfolio revisited Efficient Frontier optimization results When should you use the Efficient Frontier function This method is useful when it is difficult to determine reasonable lower or upper bounds for requirement statistics Method 2 Multiobjective optimization Another technique for finding efficient portfolios is called multiobjective or multicriteria optimization This technique lets you optimize multiple often conflicting objectives such as maximizing returns and minimizing risks simultaneously Other examples of multiobjective optimization include e Aircraft design requiring simultaneous optimization of weight payload capacity airframe stiffness and fuel efficiency 124 OptQuest User Manual e Public health policies requiring simultaneous minimization of risks to the population direct taxpayer costs and indirect business regulation costs e Electric power generation requiring simultaneous optimization of operating costs reliability and pollution control M ost forms of multiobjective optimization are solved by minimizing or maximizing a weighted combination of the multiple objectives In the portfolio example a weighted combination of the return and risk objectives might be mean return k standard deviation Equation 4 1 where k gt 0 is a risk aversion constant and the objective is to maximize the function The relationship between return and risk for the investor is captured entirely
81. he Aggressive Growth fund has higher volatility The decision problem then isto determine how much to invest in each asset to maximize the total expected annual return while maintaining the risk at an acceptable level and keeping within the minimum and maximum limits for each investment 20 OptQuest User Manual Using OptQuest Using OptQuest involves the following steps 1 2 3 4 5 6 7 8 c 1 Create a Crystal Ball model of the problem Define the decision variables within Crystal Ball In OptQuest select decision variables to optimize Specify constraints on the decision variables Select the forecast objective and define any requirements Select optimization options Run the optimization Interpret the results reating the Crystal Ball model In Excel open the Portfolio Allocation workbook from the Crystal Ball Examples folder The worksheet for this problem is shown below Portfolio Allocation xls ioj x Portfolio Allocation Model Money Market fund 50 000 Income fund 25 000 Growth and Income fund 80 000 Aggressive Growth fund 10 000 100 000 Total amaum available 100 000 Annual Lower Upper favesiments return bound bound age Decision variables invested palace a Money Market fund 25 000 Income fund 25 000 Growth and Income fund 25 000 Fatal amaun Aggressive Growth fund 25 000 este Tote exgected retum sesle Me 100 000 bi
82. heet model for this problem which you can view by opening the Project Selection xls file The decision variables in column H are binary that is they can assume only the values zero and one representing the decisions of either not selecting or selecting each project The total investment in cell F15 is the required investment in column F multiplied by the respective decision variable in column H Hi Project Selection xis _ oOo x Budget Constrained Project Selection Maximize total expected pratt subject ta Budget consiraint Expected Success Expected Initial Expected Revenue Rate Return Investment Profit Decisions 675 000 425 000 1 050 000 400 000 360 000 110 000 720 000 220 000 1 000 000 300 000 90 000 60 000 630 000 280 000 225 000 155 000 Budget 2 000 000 Invested 2 800 000 Surplus 800 000 Total profit 1 950 000 DIN Project Sheet2 4 Sheet Sheett Z Sheet 4 Sheet Sheet A Sheet Figure 4 9 Project selection problem spreadsheet model The expected revenue and success rates are assumption cells in the Crystal Ball model The expected revenues have various distributions while the success rates are modeled using a binomial distribution with one trial During the simulation the outcomes in column D will be either 0 or 100 not successful or successful with the probabilities initially specified Thus for each sim
83. his as mutual fund 1 mutual fund 2 50000 OptQuest only considers combinations of values for the two mutual funds whose sum is 50 000 Or if your budget restricts your spending on gasoline and fleet service to 2 500 you can define this as gasoline service lt 2500 In this case O ptQ uest considers only combinations of values for gasoline and service at or below 2 500 OptQuest Note N ot all optimization models need constraints 46 OptQuest User Manual Feasibility A feasible solution is one that satisfies all constraints Infeasibility occurs when no combination of values of the decision variables can satisfy a set of constraints N ote that a solution i e asingle set of values for the decision variables can be infeasible by failing to satisfy the problem constraints and this doesn t imply that the problem or model itself is infeasible For example suppose that in the Portfolio Allocation problem the investor insists on finding an optimal investment portfolio with the following constraints Income fund Aggressive growth fund lt 10000 Income fund Aggressive growth fund gt 12000 Clearly there is no combination of investments that will make the sum of the income fund and aggressive growth fund no more than 10 000 and at the same time greater than or equal to 12 000 Or for this same example suppose the bounds for a decision variable were 15 000 lt Income fund lt 25 000
84. hows the Casing Remaining forecast chart for these decision variables verifying that the chance of running out of casing is indeed at most 5 zix Edit Preferences View Run Help 1 000 Trials Frequency Chart 1 Outlier 029 z 015 A o 007 000 200 00 25 00 250 00 475 00 700 00 gt 0 00 Certainty 95 80 4 Infinity Figure 4 3 Product mix remaining casing forecast chart Practice exercise Suppose that the amount of veal available is uncertain due to an unreliable supplier Assuming that the on hand veal inventory is defined by a uniform distribution between 11 000 and 12 520 pounds formulate an appropriate requirement in place of the inventory limitation edit the O ptQuest file and rerun the model H ow does the solution change OptQuest User Manual 101 Chapter Examples U sing OptQuest Hotel design and pricing problem Problem statement A downtown hotel is considering a major remodeling effort and needs to determine the best combination of rates and room sizes to maximize revenues Currently the hotel has 450 rooms with the following history Room Type Rate ee E a Revenue Standard 85 250 21 250 Gold 98 100 9 800 Platinum 139 50 6 950 Each market segment has its own price demand elasticity Estimates are Room Type Elasticity Standard 3 Gold 1 Platinum 2 This means for example that a 1 decrease in the price of a standard room will increase th
85. hted 0 3 market 0 5 _ fund 0 4 income fund 2 1 growth fund investment investment investment investment The total weighted risk is limited to be less than or equal to 100 000 OptQuest solution OptQuest Note Except where indicated this example uses the recommended Crystal Ball run preferences See Setting Crystal Ball run preferences on page 67 OptQuest User Manual Start O ptQuest from the Crystal Ball CBTools menu or toolbar In OptQ uest 1 Open the Portfolio R evisited 3 opt file 2 Start the OptQ uest wizard As you step through the problem note e The decision variables are the same as in Chapter 1 e Thereisanewconstraint limiting the total weighted risk added to the previous constraint limiting the total investment to 100 000 e The objective is the same as in Chapter 1 3 Run the optimization Status and Solutions gt 01 x c program files crystal ball examples optquest files portfolio Optimization File Portfolio Revisted Method 3 Optimization is Complete Maximize Objective Total expected return Money Growth and Aggressive Mean Market fund ieee Income fund Growth fund 6504 61 25000 0 25000 0 25000 0 25000 0 8094 80 0 00000 17500 0 30900 0 45900 0 8447 91 0 00000 24000 0 26000 0 48000 0 8448 51 0 00000 24518 9 27206 4 402747 8449 07 0 00000 25000 0 26470 6 48529 4 Figure 4 23 Portfolio revisited optimization
86. iable Alt t d Selection window OptQuest User Manual 169 Keyboard Commands Commands Keystrokes Icons Open the Efficient Frontier Alt v e window Z Open the Forecast Selection Alt t f window E Open the Optimization Log Alt v window H Open the Options window Alt t o e Open the Performance Graph Alt v p window N Open the Solution Analysis Alt r v 2 window O Open the Status And Solutions Alt v s window Paste text from the clipboard Alt e p OR Ctrl v Ea Pause an optimization Alt r p Mm Print the OptQuest window Alt f p OR Ctrl p Save the current optimization file Alt f sOR Ctrl s Save the current optimization file Alt f a to another file Search for help on atopic Alt h s OptQuest User Manual Commands Keystrokes Icons Start an optimization Alt r s gt Start the O ptQ uest wizard Alt t w ting age Stop an optimization Alt r t OptQuest toolbar The OptQuest toolbar has the following tools se PO CS oe Pe sg oS Se X cs S So TLR COAG ipg RI eg RS Ne 3 g O D eX o a amp x O 4 N 0 C NA N x FE S re EENE E TEE fs P 2 O mnM AKRO gt mm Cee mage OptQuest User Manual X 171 Appendix Keyboard Commands 172 OptQuest User Manual Appendix C Commands This appendix describes all the OptQuest menu commands File Edit View
87. iables at the top you can see the relative importance of all the decision variables The variables listed at the bottom are the least important in that they affect the objective the least If their 160 OptQuest User Manual effect is significantly smaller than those at the top you can probably eliminate them as variables and just let them assume a constant value Total assembly cost 35 51 40 51 45 51 50 51 55 51 Piston Cylinder wall Rod cylinder head depth Crankshaft Piston bearing Rod bearing You can use the Tornado Chart tool in addition to the Solutions Analysis chart in OptQuest to measure the impact of your decision variables For information see Running a solution analysis on page 90 OptQuest User Manual 161 C hapter Optimization Tips and Suggestions 162 OptQuest User Manual Appendix A Advanced Optimization R eferences Ny Mi This appendix provides a list of references of advanced topics suggested in this manual It is intended for advanced users who want more detail on topics such as metaheuristic methods and how optimizations work In this appendix References This appendix provides references for further detail on e Metaheuristic methods e Comparisons of optimization methods e Optimization of complex systems See these references on our website Glover F J P Kelly and M Laguna The OptQuest Approach to Crystal Ball Simulation Optimization
88. ight help you determine good initial values See Chapter 5 Optimization Tips and Suggestions for situations where this is useful Tolerance Sets how close a set of decision variables can be to any previous set to consider them equivalent If a set of decision variables is equivalent to a previously run set O ptQ uest discards the set before it runs a simulation for it Also when running an optimization with a variable requirement O ptQ uest uses the tolerance value to determine when the optimization for each variable requirement value is complete OptQuest Note For more information on how O ptQuest uses the tolerance when calculating variable requirements see Variable requirements on page 158 80 OptQuest User Manual The tolerance is a decision variable range multiplier For example if a decision variable range is 50 to 100 and the tolerance is0 01 then any decision variable within 0 5 of the selected decision variable value is equivalent All decision variable values in a set must be equivalent to discard the entire set of values By default this value is 0 00001 Running the optimization gt When running an optimization you can stop pause continue or restart at any time You can display any OptQuest window or any of the optimization s performance graph bar graph or optimization log dialogs during an optimization You can select windows cascade windows or close windows You cannot
89. inventory position is simply the beginning position minus the demand plus any order that may have been placed If lost sales occur computing the ending inventory position this way reduces it by the unfulfilled demand which is incorrect Thus you must add back the number of lost sales to account for this In the ordering process the manager places orders at the end of the week and receives orders at the beginning of the week T hus in Figure 4 28 the order placed at the end of the first week with a lead time of 2 weeks will arrive at the beginning of the fourth week Column K determines the week an order is due to arrive and aMATCH function is used in column D to identify whether an order is scheduled to arrive OptQuest solution OptQuest Note Except where indicated this example uses the recommended Crystal Ball run preferences See Setting Crystal Ball run preferences on page 67 Searching for the optimal combination of reorder point and order quantity can be quite tedious Fortunately O ptQ uest performs this search efficiently Start O ptQ uest from the Crystal Ball CBTools menu or toolbar In OptQuest 1 Open the Inventory System opt file 142 OptQuest User Manual 2 Start the OptQ uest wizard As you step through the problem note e This problem has two decision variables e The initial search limits are set between 200 and 400 for both variables using a step size of 5 e There are no constraints or requir
90. ion Decline The period when production rates P decline by the same proportion in each time step leading to an exponential function P t P 0 exp c t where t is the time since the plateau phase began and c is some constant With only estimates for the total Stock Tank Oil Initially In Place STOIIP reserve size and percent recovery amounts the objective is to select a production rate a facility size and well numbers to maximize some financial measure In this example the measure used is the P10 of the N PV distribution In other words the oil company wants to optimize an NPV value which they are 90 confident of achieving or exceeding 116 OptQuest User Manual As described the problem is neither trivial nor overly complex A high plateau rate doesn t lose any reserves but it does increase costs with extra wells and larger facilities H owever facility costs per unit decrease with a larger throughput so choosing the largest allowed rate and selecting a facility and number of wells to match might be appropriate Spreadsheet model al il Field Development xls mmbbls years 2 00 bbl 630 00 mmbbls 172 60 mbd 172 60 mbd 63 00 mmbbls 346 50 mmbbls 7 50 years 0 2692 18 08 years Oil Production Profile Max Plateau Buildup Plateau Decline Phase Phase Phase 0 Growth Mature Decline Years Years Years 65 0 of reserves 10 0 of reserves annually Abb
91. ion example was to maximize returns subject to the requirement that the standard deviation remain under 8 000 An equally valid objective is to minimize the standard deviation subject to the requirement that the return be above a certain amount Change the optimization to make the objective minimizing the standard deviation and the requirement that the mean be above some amount such as 8 000 H ow different are the optimization results The Portfolio Revisited examplein Chapter 4 shows further how these two objectives are related and discusses other types of objectives that incorporate different risk factors 36 OptQuest User Manual Changing the number of trials Increasing the number of trials used in the Crystal Ball simulations affects the performance of OptQ uest in two ways First in the same amount of time fewer simulations can be evaluated decreasing the chances of converging to an optimal or near optimal solution H owever an increased number of trials provides more discrimination among solutions since the accuracy of the forecast statistics will be better To see the effects of increasing the number of trials 1 Reopen the Portfolio Allocation xls workbook and enter the original decision variables and cell values 2 Inthe Crystal Ball Run Preferences dialog change the maximum number of trials from 500 to 2500 3 Start O ptQ uestand reload the optimization settings file you saved earlier 4 Run another opti
92. ionally define requirements OptQuest User Manual e To define a requirement for the same forecast as the objective first duplicate the forecast by clicking in the forecast objective row and selecting Edit gt Duplicate This creates a new row with the same forecast name plus a number appended to the name In the row you want to be a requirement select Requirement from the Select column Select a statistic from the Forecast Statistic drop down menu Enter either e An upper limit for the selected statistic in the U pper Bound column A lower limit for the selected statistic in the Lower Bound column e Both upper and lower limits for the selected statistic Repeat steps 4a 4d for additional requirements Optionally define one variable requirement To define a variable requirement for the same forecast as the objective first duplicate the forecast by clicking in the forecast objective row and selecting Edit gt Duplicate This creates a new row with the same forecast name plus a number appended to the name In the row you want to be a variable requirement select either Variable Requirement U pper Bound or Variable Requirement L ower Bound from the Select column Input the number of samples in the Variable Requirement dialog and click on OK Select a statistic from the Forecast Statistic drop down menu Enter both upper and lower limits for the selected bound Click on OK The Options window a
93. is a measure of dispersion about the mean For example you can calculate the standard deviation of the values 1 3 6 7 and 9 by finding the square root of the variance that is calculated in the variance example below The standard deviation denoted as s is calculated from the variance as follows s 10 2 3 19 See Variance calculation below Variance Variance is a measure of the dispersion or spread of a set of values about the mean When values are close to the mean the variance is small When values are widely scattered about the mean the variance is larger To calculate the variance of a set of values 1 Find the mean or average 2 For each value calculate the difference between the value and the mean 3 Square these differences and sum the squares 4 Divide by n 1 where n is the number of differences For example suppose your values are 1 3 6 7 and 9 The mean is 5 2 The variance denoted by s is calculated as follows A 1 59 3 5 2 6 5 2 7 5 2 9 5 2 o 5 1 40 8 10 2 56 OptQuest User Manual Percentile OptQuest Note The calculation uses n 1 instead of n to correct for the fact that sample variances are slightly smaller than the variance of the entire population A percentile is a number on a scale of zero to one hundred that indicates the percent of a distribution that is equal to or belowa value Standardized tests usually report resultsin percentiles S
94. ization Problem statement Glossary Term inventory Any resource set aside for future use such as raw materials semifinished products and finished products Inventory also includes human financial and other resources Glossary Term inventory position The amount of inventory on hand plus any amount on order but not received less any back orders Glossary Term reorder point The inventory position when you reorder Glossary Term order quantity The standard amount of product you reorder when inventory reaches the reorder point Glossary Term safety stock The additional quantity kept in inventory above planned usage rates The two basic inventory decisions that managers face are e How much additional inventory to order or produce e When to order or produce it Although it is possible to consider these two decisions separately they are so closely related that a simultaneous solution is usually necessary Typically the objective is to minimize total inventory costs Total inventory costs typically include holding ordering shortage and purchasing costs In a continuous review system managers continuously monitor the inventory position Whenever the inventory position falls at or belowa level R called the reorder point the manager orders Q units called the order quantity N ote that the reorder decision is based on the inventory position and not the inventory level If manage
95. l All Excel model files and associated O ptQuest files are in the Crystal Ball Examples folder The table below summarizes the examples in this chapter and the features illustrated 7 f Application i f 5 Illustrated Methods Hl gt g Product mix 5 continuous 3 1 Classic optimization example H otel design and 3 discrete 0 1 Usesa percentile requirement shows pricing the risk of using a deterministic solu tion instead of a probabilistic one Budget 8 binary 0 1 1 O Uses binary decision variables for Yes constrained project No decisions and uses the certainty selection statistic for optimizing the values observed between two endpoints Groundwater 2 mixed 0 0 Usesa decision variable to select cleanup different sets of assumptions Oil field 3 mixed 0 0 Usesa percentile objective and a development lookup table based on a decision variable Portfolio revisited 4 continuous 2 2 Combines several objective functions into one multiobjective using special Crystal Ball functions and uses the Arbitrage Pricing Theory for incorporating risk Example of Efficient Frontier OptQuest User Manual 97 Chapter Examples U sing OptQuest Application Decisions Variables Type Constraints Requirements Illustrated Methods Tolerance analysis 7 continuous 0 2 Usesfinal value and range width statistics Inventory system 2 discrete 0 0 Se
96. l bit the drill is able to penetrate the terrain at a rate of en 21 21 meters per hour 3 10 While after 50 hours the penetration rate is only Ds 6 71 meters per hour 50 10 Eventually the bit must bereplaced asthe costsexceed the value of the well being drilled The problem isto determine the optimum replacement policy that is the drilling cycle T hours between replacements T hoursafter replacingthebit thetotal drilled depth in meters M is given by the integral of Equation 4 2 from 0 to T or M 300 T 10 meters where 300 is a drilling depth coefficient 6 Suggested from an example in Kenneth K Humphreys J elen s Cost and Optimization Engineer ing 3rd ed New York McGraw H ill 1991 257 262 146 OptQuest User Manual The revenue value per meter drilled is calculated to be 60 Drilling expenses are fixed at 425 per hour and it generally requires R 7 5 hours to install a new drill bit at a cost of 8 000 400R If all drilling parameters were certain calculating the optimal replacement policy would be straightforward H owever several of the drilling parameters are uncertain and knowledge about their values must be assumed e Because of variations in the drilling process and terrain the depth coefficient C is characterized by anormal distribution with a mean of 300 and a standard deviation of 20 The drill bit replacement time R varies and is determined by a triangular di
97. lls in the worksheet Maximize Total Revenue cell 112 Subject to 70 lt Standard Price cell G7 lt 90 90 lt Gold Price cell G8 lt 110 120 lt Platinum Price cell G9 lt 149 Total Room Demand cell H 12 lt 450 OptQuest User Manual 103 Chapter Examples U sing OptQuest You can solve this discrete nonlinear optimization model in OptQuest using deterministic mode see Options window on page 76 Figure 4 5 shows the solution with recommended prices of 79 110 and 127 for the three types of rooms This solution uses all but one of the 450 rooms the best possible for a discrete solution Status and Solutions c program files crystal ball examples optquest files hotel otel Design Problem Gold Platinum price price 98 139 Optimization File Optimization is Complete Total Revenue lese Total room demand Standard Deterministic Value lt 450 price 85 80 Maximize Objective 1 38000 0 400 000 6 39703 4 434 883 100 149 40822 8 445 542 80 110 120 40 40846 2 446 562 80 109 120 66 40950 6 448 192 79 109 130 69 40986 9 448 911 79 109 129 40998 4 79 110 41031 8 448 610 449 329 Figure 4 5 H otel pricing deterministic solution In a realistic situation the elasticities are probably uncertain Assume that they can vary from the specified values uniformly by plus or minus
98. ls the Std Error of the Mean is 358 Statistics Malye Trials 500 Mean 7 577 Median 7 604 Mode oa Standard Deviation 7 997 Variance 63 956 831 Skewness 0 18 Kurtosis 3 10 Coeff of Yariability 1 06 Range Minimum 16 364 Range Maximum 36 038 Range Width 52 402 Mean Std Error 357 65 Forvoact Tots sapeoted retin Frequenoy Chart Figure 1 17 Longer simulation results from Figure 1 15 Practice exercises Correlating assumptions Very often stocks and therefore mutual funds are positively correlated with each other to some degree This magnifies the variance of the stock portfolios and their risk and you must take this into account when evaluating portfolios OptQuest User Manual 35 Chapter Getting Started Test how correlation affects the results of the optimization 1 In Crystal Ball define correlations of Money income fund Growth and Aggressive market fund income fund growth fund Money market fund 1 0 0 2 0 1 0 1 Income fund 1 0 0 3 0 2 Growth and income 1 0 0 5 fund Aggressive growth 1 0 fund To simplify setting up the matrix of correlations use the Correlation M atrix tool in Crystal Ball For information on using this tool see the Crystal Ball U ser M anual 2 Rerun the optimization 3 Compare the results with the optimization results with no defined correlation Changing the optimization objective The objective in the Portfolio Allocat
99. ly give you a range of possible outcomes for any situation They don t tell you how to control the situation to achieve the best outcome Through a new optimization technique O ptQuest finds the right combination of variables that produces the best results possible If you use simulation models to answer questions such as What are likely sales for next month now you can find the price points that maximize monthly sales If you asked What will production rates be for this new oil field now you can additionally determine the number of wells to drill to maximize net present value And if you wonder Which stock portfolio should pick with OptQuest you can choose the one that yields the greatest profit with limited risk Like Crystal Ball O ptQ uest is easy to learn and easy to use With its wizard based design you can start optimizing your own models in under an hour All you need to know is how to use a Crystal Ball spreadsheet model From there this manual guides you step by step explaining O ptQuest terms procedures and results Who should use this program OptQ uest is for the decision maker from the businessperson analyzing the risk of new markets to the scientist evaluating experiments and hypotheses With OptQuest you can make decisions that maximize the use of your resources time and money OptQ uest has been developed with a wide range of spreadsheet uses and usersin mind You don t need highly ad
100. mine a new set of values for decision variables Crystal Ball Simulation Generate random numbers for gt assumption cells Display resultsin a _ forecast chart Calculate entire spreadsheet Add new best result to Status And Solutions window Stop and prompt to continue Figure 1 3 OptQuest flow OptQuest User Manual 19 Chapter Getting Started Portfolio Allocation model The remainder of this chapter contains a more detailed tutorial that will guide you through setting up and running an optimization model using Crystal Ball and O ptQ uest If you are not familiar with basic optimization terminology such as objectives and constraints review Chapter 2 Understanding the Terminology on page 41 Problem description An investor has 100 000 to invest in four assets Below isa list of the assets expected annual returns and the minimum and maximum amounts the investor is comfortable allocating to each investment Investment Annual retum Lower bound Upper bound Money 3 0 50 000 market fund Income fund 5 10 000 25 000 Growth and 7 0 80 000 income fund Aggressive 11 10 000 100 000 growth fund The source of uncertainty in this problem isthe annual return of each asset The more conservative assets the Income and Money Market funds have relatively stable annual returns while t
101. mization Figure 1 18 shows the results of the optimization for the portfolio example using the same amount of time but 2500 trials per simulation instead of the original value of 500 Note that fewer solutions were identified Therefore you must make a trade off between the accuracy of the results and the breadth of the search Experiment with differing numbers of trials and time limits to see the differences in your results 7 Status and Solutions p loj x UnNamed opt Optimization File Crystal Ball Simulation Portfolio Allocation xls Optimization is Complete Maximize Objective Requirement Total expected return 1 Total expected return 2 Money Income Growth and Aggres Mean Std_Dev lt Ri 00 Market fund fund Income fund Growth 1 1 8 6499 51 5637 71 250000 25000 0 25000 6999 58 7275 53 183333 10000 00 483333 23333 12 7003 08 7362 30 17396 0 10000 00 501214 22482 15 7076 61 7446 28 166079 10000 00 49858 3 23533 25 7403 25 7643 60 194629 124127 323549 35768 107 Best 110 7416 91 7748 35 18625 2 11296 0 330142 Figure 1 18 Results using 2500 trials per simulation OptQuest User Manual 37 Chapter Getting Started Using precision control You can use Crystal Ball s precision control feature for several purposes e When you are unsure of how to set the number of trials used for Crystal Ball simulations e If you
102. mnivac Theimer Cover design and production by Crossroads Communication Nashua NH Printing and binding by CGPress Broomfield Colorado
103. model 1 Inthe Crystal Ball Run Preferences dialog change the maximum number of trials from 500 to 2500 This maximum limit is always in effect even when precision control isturned on Therefore when using precision control you must increase the maximum number of trials to let precision control achieve the appropriate accuracy 38 OptQuest User Manual 2 Turn on Precision Control a Selectcell C17 b Select Cell gt Define Forecast c Check the Specify option d Usean absolute precision of 1000 units e Check the Mean checkbox 3 Start O ptQ uestand reload the optimization settings file you saved earlier 4 Run another optimization Experiment with various other precision control settings to see the difference in the results OptQuest User Manual 39 Chapter Getting Started 40 OptQuest User Manual Chapter 2 U nderstandingthe Terminology Ny Mi What is an optimization model Decision variables Constraints Objective Forecast statistics Requirements Variable requirements Types of optimization models Statistics The first part of this chapter describes the three major elements of an optimization model decision variables constraints and the objective It also describes other elements such as requirements and forecast statistics required for models with uncertainty The second part of this chapter describes the different types of optimization models and how OptQuest deals with the
104. n it adds a new line to the Status And Solutions window showing the new objective value and the values of the decision variables The time remaining and the simulation number under evaluation appear in the upper left corner of the window This information disappears when the time limit is reached While the optimization is running you can select three commands from the View menu Performance Graph Shows a plot of the objective value as a function of the number of simulations evaluated When using the wizard this window opens automatically Bar Graph Shows how the value of each decision variable changes during the optimization search procedure Optimization Log Provides details of the sequence of solutions generated during the search Efficient Frontier Plots a set of objective values found over the range of a variable requirement Figure 1 10 shows the Status And Solutions window after the optimization your results should be similar but will depend on the speed of your processor and other factors Status and Solutions E loj x UnNamed opt Optimization File Crystal Ball Simulation Portfolio Allocation xls Optimization is Complete Maximize Objective Total expected return Money Income Growth and Aggressive Mean Market fund fund Income fund Growth fund 1 6504 61 25000 0 25000 0 25000 0 25000 0 3 8775 42 0 00000 13333 3 35833 3 50833 3 4 900
105. n if the process has not found a better solution for a significant number of simulations You must still define a time or simulation count to stop the optimization but this option overrides those settingsif necessary In most cases it is better for you to stop OptQuest manually but if you are unsure you can use Automatic Stop option The default is Off The Preferences tab has the following options OptQuest User Manual Welcome Sound Font Sets whether to play the associated sound when OptQuest opens Selecting On plays the sound The default is On Changes the font and point size O ptQ uest uses in many O ptQ uest windows The default is MS Sans Serif 10 point Save Crystal Ball Runs Sets whether to save the simulation results for all the best simulations in the Status And Solutions window only the best one or none The simulations are saved for recall until you exit O ptQ uest Saving Crystal Ball simulations might require a lot of hard drive space and slow down the optimization The default is Only Best OptQuest Note This option saves the simulation only temporarily until you exit O ptQuest it does not save the simulation with the OPT file To save the simulation permanently to a file copy the run simulation to Excel using Edit gt Copy To Excel and then saveit in Crystal Ball using the Save Run command Description of Optimization Model Isa brief descriptive title for the optimization model
106. n placing an order and receiving the order is 2 weeks Therefore the expected demand during lead time is 200 units Orders are placed at the end of the week and received at the beginning of the week The traditional economic order quantity EO Q model suggests an order quantity Q 2x 5200x50_ 554 10 4 For the EOQ policy the reorder point should equal the lead time demand that is place an order when the inventory position falls to 200 units If the lead time demand is exactly 200 units the order will arrive when the inventory level reaches zero H owever if demand fluctuates about a mean of 200 units shortages will occur approximately half the time Because of the high shortage costs the manager would use either a larger reorder point alarger order quantity or both In either case the manager will carry more inventory on average which will result in a lower total shortage cost but a higher total holding cost A higher order quantity lets the manager order less frequently thus incurring lower total ordering costs H owever the appropriate choice is not clear Simulation can test various reorder point order quantity policies Spreadsheet model Before examining the spreadsheet simulation model step through the logic of how this inventory system operates Assume that no orders are outstanding initially and that the initial inventory level is equal to the order quantity Q Therefore the beginning inventory position will
107. nal model with the decision variables already defined OptQuest solution OptQuest Note Except where indicated this example uses the recommended Crystal Ball run preferences See Setting Crystal Ball run preferences on page 67 1 In Crystal Ball set the number of trials per simulation to 500 2 Start OptQuest from the Crystal Ball CBTools menu or toolbar In OptQuest open the Portfolio Revisited EF opt file 4 Start the OptQ uest wizard As you step through the problem note that the decision variables constraints and objective are the same 5 Change the requirement to a Variable Requirement U pper Bound for the standard deviation statistic 6 Click on OK to accept 10 as the number of samples in the range 7 Set the variable requirement bounds by using 8000 for the lower bound and 10000 for the upper bound OptQuest User Manual 123 C hapter Examples U sing OptQuest 8 IntheOptions gt Advanced dialog change the Tolerance to 0 0001 9 Run the optimization for 60 minutes The results are shown in Figure 4 19 Efficient Frontier lol x 8107 40 920458 0 00000 10000 00 8341 42 9428 71 6111 11 10000 00 8341 42 9428 71 6111 11 10000 00 5631 21 9950 78 0 00000 14310 0 Efficient Frontier 9000 8500 Objective 3900 7500 8000 8500 9000 9500 10000 Total expected return 2 Std Dev Nest Point Variable Requirement Current Value 10000 00 Fi
108. nd 25000 Continuous Growth and Income fund 80000 Continuous Aggressive Growth fund 100000 Continuous Ka K R K The columns and buttons in this window are Select Indicates whether O ptQuest will optimize the variable A check indicates the decision to optimize the variable The default is to optimize all decision variables Variable Name Displays the variable name defined in Crystal Ball This field is for display only Lower Bound Is the lower limit for the variable If you change this field OptQuest automatically updates the lower limit in Crystal Ball with the new value The default is the variable s lower bound defined in Crystal Ball OptQuest User Manual 69 Chapter Setting Up and Optimizing a M odel Suggested Value Upper Bound Type Workbook Worksheet Cell Reorder 70 OptQuest User Manual Is the initial value O ptQ uest starts optimizing with For unselected decision variables O ptQ uest always uses the suggested value to evaluate the objective The default is the cell value in the worksheet Is the upper limit for the variable If you change this field OptQuest automatically updates the upper limit in Crystal Ball with the new value The default is the variable s upper bound defined in Crystal Ball Is whether the variable is continuous or discrete You can select either Continuous or Discrete from the drop down menu If you select Discrete
109. ndom numbers for assumption cells recalculates the spreadsheet models and displays the results in a forecast chart A Crystal Ball simulation is made up of multiple trials variable A quantity that might assume any one of a set of values and is usually referenced by a formula variance The square of the standard deviation i e the average of the squares of the deviations of anumber of observations from their mean value Variance can also be defined as a measure of the dispersion or spread of a set of values about a mean When values are close to the mean the variance is small When values are widely scattered about the mean the variance is larger wizard A feature that leads you through the steps to create an optimization model This wizard presents windows for you to complete in the proper order 192 Index In this index A comprehensive index designed to give you quick access to the information in this manual A advanced options 79 analysis solution 91 using tornado chart 160 apartment tutorial 14 APT 128 arbitrage pricing theory 128 assumptions correlating practice exercise 35 defining 67 B bar graphs 87 best percentage from 92 bibliography by subject 180 financial applications 182 inventory systems 182 optimization topics 181 petrochemical engineering 182 spreadsheet design 181 tolerance design 182 bounds affecting performance 157 defined for decision variables 45 requirement sta
110. near A mathematical relationship where one or more terms in the formulas are nonlinear Terms such as x2 xy 1 x or 3 1 make nonlinear relationships See linear NPV Net Present Value The NPV equals the present value minus the initial investment objective A formula in terms of decision variables that gives a mathematical representation of the model s goal optimal solution The set of decision variable values that achieves the best outcome optimization A process that finds the optimal solution to a model optimization model A model that seeks to maximize or minimize some quantity such as profit or risk order quantity The standard amount of product you reorder when inventory reaches the reorder point percentile A number on a scale of zero to one hundred that indicates the percent of a probability distribution that is equal to or belowa value performance For an optimization program the ability to find high quality solutions as fast as possible probability The likelihood of an event probability distribution A set of all possible events and their associated probabilities Glossary 189 random number A mathematically selected value which is generated by a formula or selected from a table to conform to a probability distribution random number generator A method implemented in a computer program that is capable of producing a series of independent random numbers range The difference be
111. nen 69 Specifying Constraints unnnnneersensnennnnnnnnnnnnnnnnnen nenn nennen 71 Constraints windoW enersensennnnnnnnnnnnnnnnnnennnen nenn nn 72 Selecting the forecast objective nnnsenneenneernnnennnnennnen nen 73 Forecast Selection window zunneenseernennennnnennnernnnnnn nennen 75 Selecting options ueeernsernnernennnennnnnnnennnnnnnnnnnnnnn nase nn nnnnn 76 Options window METETE 76 Running the optimization rssrsennsenneernennennnnennnennn nennen nn 8 Start Pause Stop COMMANAS ruennmeeneenneenneenennneen nennen 82 Status And Solutions window menenneenneenennneennnen nenn 83 Performance graph ernereenneennnennennnnennnnennnen nennen nn 85 Bar graph u enges 87 Optimization log 2rnernnnnennnennnennnnnnnnnnennnennnn nennen 88 Efficient Frontier windoW ensenssennseennersnnnnnnnnennnnnnennn nn 89 Interpreting the results ersrsnnnennnennensnnnnnnnen anna nnn nn 90 Running a solution analysis seeen 90 Running a longer simulation of the results s e 93 Chapter 4 Examples Using OptQuest OVERVIEW sii ciated enea nea iaaa EAEE anna ented aan enatenda th 97 Product MIX mirise nanna nananana naai 98 Problem statement enunsersnenssennennnnnennnennnennnnennennnnnn nn 98 Spreadsheet model ccccccssecesessssesessseressersecseesseesseeesees 99 OptQuest solution zenneernserseenennnnennnnnnnernnn nennen nn nn 100 Practice exerCise ccccsc
112. ng OptQuest Spreadsheet model Gi Tolerance Analysis xls oO x Dimensions Piston assembly 9 2000 in Cylinder assembly 9 2100 in Assembly gap Piston 0 i ma Piston bearing 0 0015 in i namal Rod Rod bearing J i pena Crankshaft i ze Cylinder wall 0 006 in 2 945 DO nam Cylinder head depth O71 in __0 0025 in N 6 55 i poems Notes Initial tolerance levels are specified at three sigma quality for all components Quality specification is the target sigma quality needed for each component 1 Component costs varies as a function of the quality specifications Sigma one standard deviation el Tolerance 7 Tl Eos Cost based Stack Tolerance Analysis Minimum gap 0 0030 in Maximum gap 0 0200 in Nominal Initial Assembly Dimension Tolerance Spec 0 0035 in i PAUMA Toler Cylinder assembly cost Total assembly cost Figure 4 25 Tolerance analysis spreadsheet model Open the Tolerance Analysis xIs file A drawing of the assembly isin the upper right corner In this example OptQuest User Manual The nominal dimensions are in cells C14 C18 and C23 C24 Initial tolerances of each 3 sigma component are in cells D14 D18 and D23 D24 The relationship between the initial tolerance and the quality specifications cells E14 E18 and E23 E24 yields a component sigma cells G14 G18 and G23 G24 The statistical dimension cells H 14 H 18 and H 23 H 24 of e
113. nity Certainty 100 00 4 Infinity Figure 1 11 Portfolio allocation forecast chart 3 In the Forecast window select View gt Statistics The forecast statistics appear Forecast Total expected return Of x Edit Preferences View Run Help Cell C17 Statistics Statistic Trials 500 Mean 10 412 Median 10 215 Mode al Standard Deviation 16 195 Variance 262 276 220 Skewness 0 02 Kurtosis 2 94 Coeff of Variability 1 56 Range Minimum 37 777 Range Maximum 63 224 Range Width 101 001 Mean Std Error 724 26 Figure 1 12 Portfolio allocation results statistics Note that the standard deviation of the forecast is quite high 16 195 compared to the mean return of 10 412 The ratio of these two values the coefficient of variability is shown as 1 56 or above 150 Most of the money allocated was in the Aggressive growth fund and the uncertainty of returns for that fund was quite high indicating the relative riskiness of the investment 30 OptQuest User Manual Editing the optimization file In portfolio management controlling the variability of the solution to minimize risk can be just as important as achieving large expected returns Suppose that this same investor wants to reduce the uncertainty of returns for the portfolio while still attempting to maximize the expected return You might want to find the best solution for which the standard deviation is much lower say below 8 000 E
114. nnnennnnnnnn nennen ennnnnnn 48 Requirements nseni aaeain 49 Variable requirements ssseeseesreerierrrrerrerirrinrierinrerreee 50 Types of optimization models sssssssesersrrsererisreerrerirreereenenn 51 Discrete continuous OF MIXED ssssesseerrserrrssrerrsrrrrrrerrsree 51 Linear or nonlinear eseeeeeeneneeesennnnnnnnnennennennnnee nennen nennen 51 Deterministic or stochastic esseeeeeneneeenennnenenneesnennennnnne nennen 52 Examples of model types seeen 53 SEAL Sti S een ea ee laden 54 MEAN nina an hene 54 Median au nn ae 55 NO A EE OA 55 Standard deviation eeeeenenneeseeneennnnnsnennennnnensnnnnennnn ne nnnnnenenn 56 Variance une A 56 P rcentil anti nennen o 57 SKEWNESS era a a E AE 58 K FTOSISE nn ann en nn dere dan dietintisatienaagiinugennnines 59 Coefficient of variability ersenneenneernennennneennnenne nennen 59 Range also range width renmenneennsennnernnnnnnnennnen nenn 60 Mean standard eror u a 60 Certalnty ass TAN A A 61 Final Valle a nenn 61 Chapter 3 Setting Up and Optimizing a Model OVERVIEW aaa aan deinen adie 65 Developing the Crystal Ball model 65 Developing the worksheet ennernensennneenneernnennnn nennen 65 Defining assumptions decision variables and forecasts 67 Setting Crystal Ball run preferences nennen 67 Selecting decision variables to optimize nenen 68 Decision Variable Selection window neneeeenneenn
115. o if you were at the 95 percentile that means you scored better than 95 of the people who took the test T his doesn t mean you answered 95 of the questions correctly You might have answered only 20 correctly but that was better than 95 of the people who took the test As another example suppose you want to be 90 sure that you put enough money away for your retirement You might create a model with all the uncertain variables such as annual return on your investments inflation and expenses at retirement The resulting distribution shows the most likely investment needed but if you select the mean you have a 50 chance of not having enough money So you choose the value at the 90 percentile which leaves only a 10 chance of not having enough money 10 30 50 70 90 20 40 60 80 Figure 2 2 Percentiles for anormal distribution Crystal Ball Note You can reverse the meaning of the percentiles by changing the setting in the Run Preferences gt Options dialog For more information see the Crystal Ball U ser M anual OptQuest User Manual 57 C hapter Understanding the Terminology Skew ness A distribution of values a frequency distribution is said to be skewed if it is not symmetrical For example suppose the curvesin the example below represent the distribution of wages within a large company Curve A illustrates positive skewness skewed to the right where most of the wages are
116. odel O ptQ uest closes 3 Inthe Run Preferences dialog increase the maximum number of trials per simulation 4 Continue the simulation 5 Use Crystal Ball analysis tools to analyze your results For more information on using these tools see the Crystal Ball U ser M anual OptQuest User Manual 93 C hapter Setting Up and Optimizing a M odel 94 OptQuest User Manual Chapter 4 Examples U sing OptQuest Ny Mi In this chapter Product Mix example H otel Design and Pricing example Budget constrained Project Selection example Groundwater Cleanup example Oil Field Development example Portfolio Revisited example Tolerance Analysis example Inventory System example Drill Bit Replacement example This chapter has many different examples that illustrate different uses for O ptQuest from many different fields The models use different methods of solving their problems illustrating the different types of constraints requirements and forecast statistics you can use to solve problems Overview This chapter presents a variety of examples using OptQ uest 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 mode
117. olutions to complex problems involving elements of uncertainty OptQuest incorporates metaheuristics to guide its search algorithm toward better solutions This approach uses a form of adaptive memory to remember which solutions worked well before and recombines them into new better solutions Since this technique doesn t use the hill climbing approach of ordinary solvers it doesn t get trapped in local solutions and it doesn t get thrown off course by noisy uncertain model data You can find more information on O ptQuest s search methodology in the references listed in Appendix A 18 OptQuest User Manual Once you describe an optimization problem by selecting decision variables and the objective and possibly imposing constraints and requirements O ptQuest invokes Crystal Ball to evaluate the simulation model for different sets of decision variable values O ptQ uest evaluates the statistical outputs from the simulation model analyzes and integrates them with outputs from previous simulation runs and determines a new set of values to evaluate This is an iterative process that successively generates new sets of values Not all of these values improve the objective but over time this process provides a highly efficient trajectory to the best solutions The search process continues until O ptQ uest reaches some termination criteria either a limit on the amount of time devoted to the search or amaximum number of simulations Deter
118. on and comparisons to other optimization software packages This appendix is designed for the advanced user e B Keyboard Commands A list of the commands you can execute directly from the keyboard e C Commands A brief description of OptQuest s menus Bibliography A list of related publications and textbooks e Glossary A compilation of terms specific to OptQuest as well as statistical terms used in this manual e Index An alphabetical list of subjects and corresponding page numbers Additional resources Decisioneering Inc offers these additional resources to increase the effectiveness with which you can use our products Technical support If you have a technical support question or would like to comment on OptQuest there are a number of ways to reach Technical Support See the accompanying Crystal Ball README file for more information Consulting referral service Decisioneering Inc provides referrals to individuals and companies alike The primary focus of this service is to provide a clearinghouse for consultants in specific industries who can provide specialized services to the Crystal Ball and OptQuest user community If you wish to learn more about this referral service go to our Web site at www decisioneering com or call 800 289 2550 M onday through Friday between 9 00 A M and 5 00 P M Mountain Standard Time OptQuest User Manual 9 Introduction Conventions used in this man
119. on file on page 31 OptQuest User Manual 25 Chapter Getting Started Forecast Selection Select an objective and any requirements reqs must have a bound oj x gt Maximize Objective Total expected return Mean dollars EI Figure 1 8 Forecast Selection window To select a forecast statistic to be the objective 1 From the Select drop down menu select Maximize Objective The default statistic is the mean 2 Clickon OK The Options window appears The goal for this example is to maximize the mean of the only forecast cell as shown in Figure 1 8 For many problems the mean expected value of the forecast is the most appropriate statistic to optimize but it need not always be For example if an investor wants to maximize the upside potential of his portfolio he might want to use the 90th or 95th percentile as the objective The results would be solutions that have the highest likelihood of achieving the largest possible returns Similarly to minimize the downside potential of the portfolio he might use the 5th or 10th percentile as the objective to minimize the possibility of large losses You can use other statistics to realize different objectives See Statistics on page 54 for a description of all available statistics 26 OptQuest User Manual Running the optimization In the Options window you set options for controlling the optimization process The Options window has
120. on of return Points on or under the curve represent possible combinations of investments Points above the curve are unobtainable combinations given the particular set of assets available For any given mean return there is one portfolio that has the smallest standard deviation possible This portfolio lies on the curve at the point that intersects the mean of return smallest standard deviation S possible for given mean mean return standard deviation of return OptQuest User Manual 121 C hapter Examples U sing OptQuest Similarly for any given standard deviation of return there is one portfolio that has the highest mean return obtainable This portfolio lies on the curve at the point that intersects the standard deviation of return highest mean possible Fa for given standard deviation mean return standard deviation of return Portfolios that lie directly on the curve are called efficient since it is impossible to obtain higher mean returns without generating higher standard deviations or lower standard deviations without generating lower mean returns The curve of efficient portfolios is often called the efficient frontier Portfolios that lie below the curve are called inefficient meaning better portfolios exist with either higher returns lower standard deviations or both The example in Chapter 1 uses one technique to search for optimal solutions on the efficient frontier This method uses
121. ons you can always terminate the search by selecting Run gt Stop pressing lt Esc gt or clicking on the Stop icon Additionally OptQuest prompts you to extend the search when the time limit ends The Time tab lets you Run acertain number of simulations When you select this option select a number from the menu or enter the number of simulations you want to run The default is 100 simulations OptQuest Note OptQuest runs and times a single simulation and then computes the time required to run the specified number of simulations Because there are slight differencesin run times per simulation the actual number might be greater than or less than the number you specify OptQuest User Manual 77 Chapter 78 Preferences tab Setting Up and Optimizing a M odel Run for anumber of minutes When you select this option select a number from the menu or enter the number of minutes you want the optimization to run Thisis the default Time option T he default number of minutes is 10 Run until a date time Automatic Stop When you select this option enter the hour minute second AM or PM month date and year for the optimization to stop To increment the time place the cursor in the hour minute second or AM PM field and use the up or down arrow buttons You can select the month date and year from drop down menus The default is the time and date when you select this option This stops the optimizatio
122. orecast s distribution forecast statistic Summary values of a forecast distribution such as the mean standard deviation or variance You control the optimization by maximizing minimizing or restricting forecast statistics frequency distribution A chart that graphically summarizes a list of values by sub dividing them into groups and displaying their frequency counts heuristic An approximate and self educating technique for improving solutions inventory Any resource set aside for future use such as raw materials semifinished products and finished products Inventory also includes human financial and other resources inventory position The amount of inventory on hand plus any amount on order but not received less any back orders kurtosis T he measure of the degree of peakedness of a curve The higher the kurtosis the closer the points of the curve lie to the mode of the curve A normal distribution curve has a kurtosis of 3 Latin hypercube sampling A sampling method that divides an assumption s probability distribution into intervals of equal probability The number of intervals corresponds to the Minimum Sample Size option available in the Crystal Ball Run Preferences dialog A random number is then generated for each interval Compared with conventional Monte Carlo sampling Latin hypercube sampling is more precise because the entire range of the distribution is sampled in a more even consis
123. ounds and constraints mensseennennnnennnn nennen namen 157 Requirements uuenssennnernennennnnnnnnennnnnnn nun nnnnn nenne nnnennnnnn nn 158 Variable requirements snnsennsernnernnnnnennnennnnnn nennen 158 Complexity of the objective enssenneernennnnnnnnnnnen nn 159 Simulation speed unnersennnennnennnnennnnennnennnnnnn nennen 160 Sensitivity analysis using a tornado chart 160 Appendix A Advanced Optimization References References uunenesssensenenennnnnnnennnnennnnenenennenennnnennnnennnsnennnnennennnnnn 165 Appendix B Keyboard Commands Command key combinations and iCONS scscssecsseeessseserees 169 OptQuest menu commands and ICONS eseceseeeeeteeeeeeees 169 OptQuest toolbar unenmernnsennnnnnnennnnnnnnnnnnnnnnn ernennen nn 171 Appendix C Commands OptQuest menu commandsS unnnenneernnenennneennennnnnn nennen 175 Fil uO Me see nn emeaosenAunen 175 EdIE MENUS en een 175 VIECW MON rennen ee 176 RUM MENU serisinin hehln 177 Tools MENU Eraser een rher en 177 Window menu adeniinin aa an aia nn 178 HClO mien U siaaa a an aa aa aaae 178 Bibliography nennen 179 GOES en 183 FING ON See NEE T REN LIFE ANGE EUER EEE EA E E MN 193 vi Welcome to OptQuest Welcome to OptQuest Version 1 3 for Crystal Ball 2000 OptQuest enhances Crystal Ball by automatically searching for and finding optimal solutions to simulation models Simulation models by themselves can on
124. p D group of a major public utility has identified eight possible projects A net present value analysis has computed e The expected revenue for each if it is successful e The estimated probability of success e The initial investment required for each project Using these figures the finance manager has computed the expected return and the expected profit for each project as shown in the table below Project Expected Success Expected Initial Expected Revenue Rate Return Investment Profit 1 750 000 90 675 000 250 000 425 000 2 1 500 000 70 1 050 000 650 000 400 000 3 600 000 60 360 000 250 000 110 000 4 1 800 000 40 720 000 500 000 220 000 5 1 250 000 80 1 000 000 700 000 300 000 6 150 000 60 90 000 30 000 60 000 7 900 000 70 630 000 350 000 280 000 8 250 000 90 225 000 70 000 155 000 Total invested 2 800 000 Total profit Budget 2 000 000 1 950 000 Unfortunately the available budget is only 2 0 million and selecting all projects would require a total initial investment of 2 8 million Thus the problem is to determine which projects to select to 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 OptQuest User Manual 107 Chapter Examples U sing OptQuest Spreadsheet model Figure 4 9 shows a spreads
125. plus a multiple of its step size a step size is any number greater than zero but less than the variable s range Discrete also describes an optimization model that contains only discrete variables distribution See probability distribution efficient frontier The curve representing the best combinations of portfolio assets when plotting return opposite risk efficient portfolio Combinations of assets for which it is impossible to obtain higher returns without generating higher risk or lower risk without generating lower returns An efficient portfolio lies directly on the efficient frontier EOQ Economic Order Quantity feasible solution A solution that satisfies any constraints imposed on the decision variables as well as any requirements imposed on forecast statistics final value The last value that is calculated for a forecast during a simulation The final value is useful for when a forecast contains a function that accumulates values across the trials of a simulation or isa function that calculates the statistic of another forecast forecast A statistical summary of the mathematical combination of the assumptions in a spreadsheet model output graphically or numerically Forecasts are frequency distributions of possible results for the model 186 forecast objective One forecast from a model that O ptQ uest uses as the primary goal of the optimization OptQuest maximizes or minimizes a statistic of the f
126. ppears Forecast Selection window This window lists all the forecasts for the model each in its own row To access this window either e Run the wizard e Select Tools gt Forecasts H Click on the Forecasts icon lolx Forecast Selection Select an objective and any requirements reqs must have a bound gt Maximize Objective Total expected return dollars 4 The window has the following columns Select Indicates whether the forecast is an objective to maximize or minimize a requirement a variable requirement or none of these You must set one forecast to either Maximum Objective or Minimum Objective The default is No for all forecasts Name Displays the forecast name defined in Crystal Ball This field is for display only Forecast Statistic Indicates the statistic of the forecast distribution to maximize minimize or otherwise restrict For more information see Statistics on page 54 The default is Mean OptQuest User Manual 75 Chapter Selecting options 76 Lower Bound Upper Bound Units Workbook Worksheet Cell Setting Up and Optimizing a M odel Is a lower limit for a requirement statistic This field is used only for requirements and variable requirements For a requirement you must define either an upper bound a lower bound or both For a variable requirement you must set both the lower and upper bound Isthe upper limit for a req
127. probability distribution by simulating the model using Crystal Ball An optimization model with uncertainty has several additional elements assumptions Capture the uncertainty of model data using probability distributions forecasts Are frequency distributions of possible results for the model 44 OptQuest User Manual Assumptions ANNAA Assumptions Decision Variable Decision Variable Decision Variable h u LP PALAIS ep AY forecast statistics requirements K 8 ics gt gt Are summary values of a forecast distribution such as the mean standard deviation or variance You control the optimization by maximizing minimizing or restricting forecast statistics Are additional restrictions on forecast statistics You can set upper and lower limits for any statistic of a forecast distribution You can also define a range of requirement values by defining a variable requirement eee NND Objective we Forecast R Sa Model ae Optimization Model With Uncertainty 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 Decision variables aren t required for Crystal Ball models but are required for O ptQuest models You define decision variables in Crystal Ball using Cell gt Define Decision When you define a decision variable in Cry
128. r constraints for O ptQuest 1 In your model define a cell that combines the decision variables in a nonlinear equation 2 Define that cell as a Crystal Ball forecast cell 3 In OptQuest define the final value of that forecast as a requirement OptQuest User Manual 71 C hapter Setting Up and Optimizing a M odel Constraints window The Constraints window lets you specify limits in terms of decision variables To access this window either e Run the wizard e Select Tools gt Constraints E Click on the Constraints icon Constraints Input any linear constraints on decision variables Money Market fund Income fund Growth and Income fund Aggressive Growth fund lt 100000 The left side of the Constraints window is the Constraint editor The right side of the Constraints window contains buttons that insert decision variables or create an equation that sums all the decision variables To add a variable to a constraint place your cursor where you want the variable and then either type the variable name or click on the variable in the Variables list You can define any number of constraints Constraints e Use mathematical combinations of constants and selected decision variables e Must each be on its own line Can only be linear In other words you can multiply a decision variable by a constant but not by another decision variable including itself Cannot have commas dollar signs or othe
129. r more information on the percentage to enter in this field see Solution Analysis window below 3 Click on Analyze OptQ uest analyzes the forecasts and decision variables for the best solutions found and displays statistics for the ones within the specified percentage 4 Click on Cancel If the analysis indicates make changes to the optimization and rerun it Solution Analysis window The Solution Analysis window is a solution report It finds solutions that are within a specified percentage of the best solution and then calculates statistics for the decision variable values of those solutions To access this window either Select Run gt Solution Analysis e Click on the Solution Analysis icon 7 Solution Analysis ol x Number of Observations Percentage from Best 10 v Analyze Cancel Help 74 10 Range Analysis Average Maximum Standard Deviation gt Total expected return 1 7513 49 6840 49 7467 63 7513 49 90 1951 Money Market fund 0 00000 0 00000 633 851 21708 0 2555 24 Income fund 25000 0 11623 1 24380 5 25000 0 1710 47 E Solutions Indicates that a solution may not meet the specified confidence level Objective Total expected return 1 Money Market fund Growth and Income fund 7513 49 0 00000 25000 0 41810 4 7513 07 0 00000 25000 41810 9 7512 44 0 00000 41811 6 EEEE I anann Trans CETIS Figure 3 4 Solution Analysis windo
130. r non mathematical symbols Cannot have parentheses M ust have a constant on the right side of the equation 72 OptQuest User Manual The mathematical operations allowed in this window are Operation Syntax Example Addition Use between terms varl var2 30 Subtraction Use between terms varl var2 12 Multiplication Use between a 4 2 varl gt 9 constant and a decision variable with the constant first Equalitiesand Use lt or gt 2 varl lt 5 inequalities between left and right sides of the constraint Selecting the forecast objective After you exit the Constraints window the Forecast Selection window appears listing all the forecasts defined in the model In this window you define your forecast objective and optionally any requirements either on the objective forecast or on other forecasts For information on the specific columns in the Forecast Selection window see Forecast Selection window below To select a forecast objective and define requirements 1 In the forecast row for your objective click in the Forecast Statistic column 2 From the drop down menu select a statistic to optimize 3 From the Select column select either M aximize Objective To maximize the selected forecast statistic Minimize Objective To minimize the selected forecast statistic OptQuest User Manual 73 Chapter 74 Setting Up and Optimizing a M odel 4 Opt
131. rds with a 95 certainty Complicating the decision making process e You have estimates of the contamination levels of the various chemicals Each contaminant s concentration in the water is measured in micrograms per liter e The cancer potency factor CPF for each chemical is uncertain The CPF is the magnitude of the impact the chemical exhibits on humans the higher the cancer potency factor the more harmful the chemical is e The population risk assessment must account for the variability of body weights and volume of water consumed by the individuals in the community per day OptQuest User Manual 111 C hapter Examples U sing OptQuest AIl these factors lead to the following equation for population risk cancer e contaminant e water consumed population _ potencies concentrations per day risk body weight e conversion factor Spreadsheet model 1 Air stripping 2 Carbon filter 3 Photo oxidation dollar amounts are in thousands fixed and variables costs are based on 80 efficiency rate Select Remediation Method Select Cleanup Efficiency Trichloroethylene Vinyl Chloride Body Weight Volume of Water per Day Population Risk Manian Acceptable Risk Level bi TOXIC CLEANUP Figure 4 12 Groundwater cleanup spreadsheet model 112 OptQuest User Manual Open the file Groundwater Cleanup xls This model shows the population risk cell C25 which is the overall contamination risk to the
132. readsheet model Assumptions capture the uncertainty of model data using probability distributions bound A maximum or minimum limit you set for each decision variable CPF Cancer Potency Factor certainty The percentage of simulation results that fall within a range coefficient of variability A measure of relative variation that compares the standard deviation to the mean Results can be represented in percentages for comparison purposes constraint A limitation that restricts the possible solutions to a model You must define constraints in terms of decision variables continuous A variable that can be fractional so no step size is required and any given range contains an infinite number of possible values Continuous also describes an optimization model that contains only continuous variables correlation A dependency that exists between assumption cells correlation coefficient A number between 1 and 1 that specifies mathematically the degree of positive or negative correlation between assumption cells A correlation of 1 indicates a perfect positive correlation minus 1 indicates a perfect negative correlation and 0 indicates there is no correlation decision variable A variable in your model that you have control over Glossary 185 deterministic A model or system with no random variables that yields single valued results discrete A variable that can only assume values equal to its lower bound
133. relevant factors that directly affect the search performance e Simulation accuracy e Number of decision variables Initial values Bounds and constraints e Requirements Variable requirements Complexity of the objective Simulation speed Simulation accuracy There are two factors that affect simulation accuracy Number of simulation trials e Noisiness of the objective 154 OptQuest User Manual Number of simulation trials For sufficient accuracy you must set the number of simulation trials to the minimum number necessary to obtain a reliable estimate of the statistic being optimized For example you can reliably estimate the mean with less trials than the standard deviation or a percentile General guidelines for determining the number of simulation trials necessary to obtain good estimates are e 200 to 500 trials is usually sufficient for obtaining accurate estimates for the mean e Atleast 1 000 trials are necessary for obtaining reasonable estimates for tail end percentiles Empirical testing with the simulation model using the Crystal Ball Bootstrap tool see the Crystal Ball U ser M anual can help you find the appropriate number of trials for a given situation Furthermore for some models the accuracy of the statistics is highly dependent on the values of the decision variables In these cases you can use Crystal Ball s precision control feature to run a sufficient number of trials for each
134. results The results are shown in Figure 4 23 The Crystal Ball simulation of this solution in Figure 4 24 maximizes the total expected return at 8 449 with the new constraint Compare this to the original total expected return of 7 577 from Chapter 1 using the different method of limiting risk with the standard deviation OptQuest User Manual 131 Chapter Examples U sing OptQuest Forecast Total expected return d 7 _ 5 x Edit Preferences View Run Help 500 Trials Frequency Chart 1 Outlier 036 7 18 o DS a ee ee Be JFHF BE Probability oO wo Aguanbai4y A in t o 22 500 30 000 12 500 40 000 gt Fintinity Certainty f100 00 4 Infinity 5 000 dollar Figure 4 24 Portfolio revisited solution forecast chart Tolerance analysis Problem statement An engineer at an automobile design center needs to specify components for piston and cylinder assemblies that work well together To do this he needs the dimensions of the components to be within certain tolerance limits while still choosing the most cost efficient methods This is called an optimal stack tolerance analysis The piston assembly consists of five components and the cylinder assembly consists of two each with certain nominal dimensions These components are then stacked to create the assembly The difference in length between the two called the assem
135. reviations Used mmbbls million barrels mbd thousand barrels per day mm million dollars bbl dollars per barrel Facilities Costs Output mbd Cost mm 50 70 100 130 150 180 200 220 250 250 300 270 Figure 4 16 Oil field development problem spreadsheet model OptQuest User Manual 117 Chapter 118 Examples U sing OptQuest Open the Oil Field Development worksheet found in the Crystal Ball Example folder Net present value cell B30 of this oil field is based on e Total discounted reserves cell B27 e Oil margin cell B13 which is equivalent to oil price minus operating costs e Well costs cell B28 e Facilities cost cell B29 which is determined for various production levels by a look up table Facility capacity places a maximum limit on production rate while the production rate of the wells is defined as a normal distribution cell B7 The Production Profile table at the bottom of the model shows that the production phase determines annual production rates Cumulative oil production is calculated per year and is then discounted at 10 lognormal distribution in cell B10 resulting in a total discounted reserves value The model gives an oil or profit margin of 2 00 per barrel bbl and converts total discounted reserves to present value dollars Total well and facilities costs are then subtracted for total project N PV OptQuest solution OptQuest Note Except where indicated this e
136. rials per affecting performance 155 skewness 58 solution analysis of results 90 Solution Analysis window 91 solutions feasible defined 17 optimal defined 13 viewing 83 sounds turning off 78 speed of simulations 160 spreadsheet design references 181 spreadsheet models creating 65 standard deviation 56 standard error mean 60 start command 82 statistics coefficient of variability 59 forecast defined 45 48 forecast optimizing 49 kurtosis 59 mean 54 mean standard error 60 median 55 mode 55 range 60 restricting forecasts 49 selecting forecast 73 skewness 58 standard deviation 56 variance 56 Status And Solutions window 83 status during optimization 81 Step Size dialog 70 step sizes for decision variables 46 steps for using OptQuest 65 202 stochastic models 52 setting model type 79 stochastic optimization model illustrated 45 stop command 82 suggested values 70 support technical 9 symbols in constraints 25 syntax constraint 72 T technical support 9 time options 77 time remaining viewing 83 tips for optimizing 151 tolerance analysis example 132 tolerance design references 182 toolbar OptQuest 171 Tools menu OptQuest 177 tornado chart 160 trials changing practice exercise 37 tutorials Futura Apartments 14 Portfolio Allocation 20 types decision variable 46 optimization models 51 U user manual conventions 10 V values suggested 70 variability coefficient of 59 variable requirements 50
137. rks of the respective holders Introduction Welcome to OptQuest 00 eects eesti eter te tetieetiee setae 7 Who should use this program erneersenseenneernnennennnnnnnen nenn 7 How this manual is organized erneemeenmeenneennnennen nennen 8 Additional resources en een 9 Technical support u nee 9 Consulting referral service eeren rrene 9 Conventions used in this manual seeen 10 Chapter 1 Getting Started What O ptQuest does uunnennsernensennennnnennnennnn nennen nennen nn 13 Futura Apartments model 40srsnnsennnnnnernennnnnnenn nenn 14 Running O ptQuest uuneenseernsernnernennennnnnnnnnn nennen nennen 16 Closing the tutorial nennseeseersennneennnernnnnnnnnennnen nennen 18 H OW OptQuest works enueessnssennsernnernnnnnnnnnnnnennn nennen nenn nn 18 Portfolio Allocation model rssrnnrennnnnnernnnnnnnenn nennen 20 Problem description ernerennennnernnernnnnnnnennnenn nennen 20 USING OptQuest nnennnennneesnnnsnnnneennnennnnnennnnennnrnnnnnnnnnnnnn na 21 Practice exercises uneennennneennnennnnnnnnnnnnnnnnnnnnen nennen nennen 35 Chapter 2 Understanding the Terminology What is an optimization model seenen 43 Decision variables uneeeeeneneeesnennennnnennennennnnee nennen nennen 45 Constraints seeeeeeeeneneeennnnnnneennennnnnnnnensnnnnnnnnnennnnnennennnennnnnnneennnn 46 Objektive een 48 Forecast statistics eeeneneeenenneneennesnnnnnnne
138. rs used the inventory level they would place orders continuously as the inventory level fell below R until they received the order When you receive the order after the lead time the inventory level jumps from zero to Q and the cycle repeats In inventory systems demand is usually uncertain and the lead time can also vary To avoid shortages managers often maintain a safety stock In such situations it isnot clear what order quantities and reorder points will minimize expected total inventory cost Simulation models can address this question In this example demand is uncertain and is Poisson distributed with a mean of 100 units per week Thus the expected annual demand is 5 200 units 4 Adapted from James R Evansand David L Olson Introduction to Simulation and Risk Analysis New York Prentice H all 1998 5 For large values of the rate parameter 4 the Poisson distribution is approximately normal Thus this assumption is tantamount to saying that the demand is normally distributed with a mean of 100 and standard deviation of 100 10 The Poisson is discrete thus eliminat ing the need to round off normally distributed random variates 138 OptQuest User Manual Additional relationships that hold for the inventory system are e Each order costs 50 and the holding cost is 0 20 per unit per week 10 40 for one year e Every unfilled demand is lost and costs the firm 100 in lost profit e The time betwee
139. s and Solutions i E 1 x c program files crystal ball examples optquest files product mix opt Optimization File Product Mix Optimization is Complete Maximize Objective Requirement Gross Profit Casing Remaining Summer Bratworet Italian Pepperoni Mean 0 lt Percentile Zum e Sausage 5 8166 83 3282 04 un 1000 000 1000 000 1000 000 1000 000 _ 10829 6 359 157 m 2677 78 177 778 2677 78 177 778 _ 26 111071 50 7793 AET 2896 98 0 320039 2760 39 160 511 _ 61 111218 37 7198 0 00000 2916 23 0 00000 2757 74 152 824 _ 76 11156 3 11 6998 0 00000 3013 49 0 160019 2751 44 80 2553 _ 106 11158 7 102 143 0 00000 2899 23 254 927 2542 97 248 619 202 11163 7 114 722 0 00000 2889 27 321 064 2495 27 266 472 205 11184 5 110 651 0 00000 2891 09 354 477 2464 45 287 687 CI 213 112193 8 56 6528 9 68258 2869 69 290 008 2501 10 331 677 m 351 11240 7 6 93978 0 00000 2890 82 205 225 2569 85 314 114 m 490 2490 34 367 265 gt Best 376 11302 9 9 05936 176 2863 21 364 025 2423 81 4 Figure 4 2 Product mix model optimization results OptQuest User Manual Figure 4 2 shows the OptQuest solution The optimal mean profit is 11 303 obtained by producing 11 pounds of summer sausage 2 863 pounds of bratwurst 364 pounds of Italian sausage 2 424 pounds of pepperoni and 422 pounds of Polish sausage Figure 4 3 s
140. s shown below wv inrxi Forecast Total expected return lelx favesiments Money Market fund Income fund Growth and Income fund Aggressive Growth fund Total amaum available Decision variables Portfolio Allocation Modsopotais Edit Preferences View Run Help Frequency Chart 4 Outliers 032 16 oO nm Probability 3 F a o S oO 100 000 S Amaum 15 000 invested 3 750 7 500 dollar 18 750 30 000 Money Market fund Income fund Growth and Income fund Aggressive Growth fund Total exgected TERHI 15 909 10 000 38 928 35 163 S gt infinity Certainty TMT 4 Infinity nesie 100 000 bik Portfolio Sheet2 A Sheets 7 a Figure 1 15 Best optimization solution IE el OptQuest User Manual 33 Chapter Getting Started Interpreting results Thissolution has significantly reduced the variability of the total expected return even though it now has a lower mean return The portfolio achieved this by finding the best diversification of conservative and aggressive investments T hus the investor must face the trade off between higher returns with higher risk and lower returns with lower risk H ow does this solution compare with the high risk solution Figure 1 16 shows the Crystal Ball results for the first solution overlaid on top of the new solution E Overlay Chart i loj x Edit Preferences View R
141. s to the clipboard Optimization Log Jolx Optimization Statistics Optimization File UnNamed opt Total Number of Simulations 154 Number of Trials per Simulation 500 Confidence Testing is Activated Number of Simulations Run Maximum Number of Trials 90 Number of Simulations Stopped by Precision Control 0 Number of Simulations Stopped by Confidence Testing 64 Neural Network Engaged after simulation 40 Number of Simulations Avoided Due to Neural Network 0 Population Size 20 Simulation 154 Values of Variables Money Market fund 1109 05702749951 Income fund 24858 6651186083 Growth and Income fund 39308 4792005112 Agaressive Growth fund 34341 7042436723 88 OptQuest User Manual Efficient Frontier window The Efficient Frontier window displays the best solutions for each requirement value and a graph of all these best solutions The best solution information looks like the Status And Solutions window without the simulation number For more information on those fields see Status And Solutions window on page 83 This windowis only available if you have started an optimization that includes a variable requirement To access the Efficient Frontier window either e Select View gt Efficient Frontier Z e Click on the Efficient Frontier icon e In the Performance Graph window click on Frontier Use the vertical scroll bar to scroll through the entire list of best solutions Efficien
142. set constraints Describe relationships among decision variables that restrict the values of the decision variables For example a constraint might ensure that the total amount of money allocated among various investments cannot exceed a specified amount or at most one project from a certain group can be selected objective Gives a mathematical representation of the model s objective such as maximizing profit or minimizing cost in terms of the decision variables OptQuest User Manual 43 C hapter Understanding the Terminology Conceptually an optimization model might look like Constant N Constant arn Pi gt 3 TER gr m Objective Decision Variable bee ee wae fa Decision Variable gt 4 Decision Variable Oo OS Model Gy a Deterministic Optimization Model The solution to an optimization model provides a set of values for the decision variables that optimizes maximizes or minimizes the associated objective If the world were simple and the future were predictable all data in an optimization model would be constant making the model deterministic In many cases however a deterministic optimization model can t capture all the relevant intricacies of a practical decision environment When model data are uncertain and can only be described probabilistically the objective will have some probability distribution for any chosen set of decision variables You can find this
143. sified as nonlinear OptQuest User Manual 51 C hapter Understanding the Terminology OptQuest can handle linear or nonlinear objectives but the Constraints window can handle only linear constraints For information on defining linear or nonlinear constraints see Specifying constraints on page 71 lt lL Linear function Nonlinear function Number of Personnel vs Employee costs Population growth over time Deterministic or stochastic Optimization models might also be classified as deterministic or Glossary Term stochastic depending on the nature of the model data In a stochastic deterministic model all input data are constant or assumed to N be known with certainty In a stochastic model some of the random variables model data are uncertain and are described with probability distributions Stochastic models are much more difficult to optimize because they require simulation to compute the objective While O ptQ uest is designed to solve stochastic models using Crystal Ball it is also capable of solving deterministic models See Selecting options on page 76 4 0 55 70 8 5 10 0 40 5 5 7 0 8 5 10 0 Deterministic Stochastic Excel worksheet result Crystal Ball simulation result 52 OptQuest User Manual Examples of model types To illustrate these model types consider the Futura Apartments model used in the first tutorial in Chapter 1 The decision variable there is only one in this cas
144. simulation to achieve the necessary level of accuracy Objective noisiness N oisiness can also affect the accuracy of your OptQuest results ra UNSER ed a Noisy objective Smooth objective Figure 5 2 Noisy and smooth objectives In Figure 5 2 the objective on the left has significant amounts of noise caused by the model assumptions being very uncertain For this types of objective OptQ uest might have trouble discerning the minimum or maximum value You can detect noisy functions by watching the Status And Solutions window for best solutions that seem to bounce around from one set of values to completely different sets of values To help solve this OptQuest User Manual 155 C hapter Optimization Tips and Suggestions problem you can either increase the number of trials per simulation or try to decrease the uncertainty in your assumptions On the right the objective appears smooth due to the relative certainty in the model assumptions In these cases O ptQuest should quickly converge to the optimal solution Number of decision variables The number of decision variables greatly affects O ptQ uest s performance OptQuest has no physical limit on the number of decision variables you can use in any given problem H owever the performance might deteriorate if you use more than 100 decision variables Also as the number of decision variables increases you need more simulations to find high quality solutions General gui
145. ssumed to be constant for this example 140 OptQuest User Manual i Inventory System xls olx Total Annual Costs 1 050 5 000 7 090_ 0 os a Pe We n a 50 50 50 50 50 B A A A A a A D D D D D D D D E E A A A A A A OOOH HHH D D D nm A B c D E F G H J K E M N o Inventory Simulation With Lost Sales 2 3 gt Order Quantity 250 caus Order Cost 50 4 gt Reorder Point 250 wys Holding Cost 0 20 5 Initial Inventory 250 wais Lost Sales Cost 100 6 Lead time 2 seats 1 040 N 8 Beg ding 9 g Ord d Lo Orde ee old 10 ee 0 Rec d d Dmd Placed 0 e 0 11 1 250 250 0 150 0 YES 400 4 30 00 42 2 400 150 0 50 0 NO 300 10 00 43 3 300 50 0 0 50 YES 500 6 14 4 500 0 YES 250 150 Oo NO 400 30 00 15 5 400 150 0 50 0 NO 300 10 00 16 6 300 50 YES 250 200 0 YES 450 9 4000 17 7 450 200 0 100 0 NO 350 20 00 168 8 350 100 0 0 oO YES 500 11 419 9 500 0 YES 250 150 0 NO 400 30 00 20 10 400 150 0 50 0 NO 300 10 00 21 11 300 50 YES 250 200 0 YES 450 14 4000 22 12 450 200 0 100 0 NO 350 20 00 23 13 350 100 0 0 0 YES 500 16 24 14 500 O YES 250 150 0 NO 400 30 00 25 15 400 150 0 50 0 NO 300 10 00 26 16 300 50 YES 250 200 0 YES 450 19 40 00 27 17 450 200 0 100 0 NO 350 20 00 28 18 350 100 0 0 0 YES 500 21 29 19 500 0 YES 250 150 0 NO 400 30 00 30 20 400 150 0 50 0 NO
146. stal Ball you define its bounds Definesthe upper and lower limits for the variable OptQuest searches for solutions for the decision variable only within these limits OptQuest User Manual 45 Chapter Understanding the Terminology type Defines whether the variable is discrete or continuous A discrete variable can assume integer or non integer values and must have a defined step size that is greater than 0 integer or non integer A continuous variable requires no step size and any given range contains an infinite number of possible values step size Defines the difference between successive values of a discrete decision variable in the defined range For example a discrete decision variable with a range of 1 to 5 and astep size of 1 can only take on the values 1 2 3 4 or 5 a discrete decision variable with arange of 0 to 2 with astep size of 0 25 can only take on the values 0 0 25 0 5 0 75 1 0 1 25 1 5 1 75 and 2 0 In an optimization model you select which decision variables to optimize from a list of all the defined decision variables The values of the decision variables you select will change with each simulation until the best value for each decision variable is found within the available time limit Constraints Constraints restrict the decision variables by defining relationships among them For example if the total amount of money invested in two mutual funds must be 50 000 you can define t
147. stribution with parameters 6 5 7 5 and 9 e The number of 10 hour days available per month D also varies due to the weather and the number of daysin a month and is assumed to be triangular with parameters 24 28 and 30 With these assumptions the profit drilling cycle if the bit is replaced after T hours equals the revenue obtained from drilling minus drilling expenses and replacement costs profit drilling cycle 60M 425T 8 000 400R Assuming D ten hour days per month the average number of cycles per month is 10D T R Therefore the average profit per month is top soo c Z 425T 8 000 400R average profit _ 1 month u T R The objective is to find the value of T that maximizes the average profit per month OptQuest User Manual 147 C hapter Examples U sing OptQuest Spreadsheet model Open the Drill Bit Replacement example shown below This workbook has Crystal Ball assumptions defined for Cell Assumption C6 Replacement time R C8 Drilling depth function coefficient C C10 Number of days available per month D One decision variable is defined in cell C12 the cycle time between replacements of the drill bit T The model outputs are computed using the formulas developed in the previous section The drilling expensesin cell F7 include both the drilling costs and the replacement costs The forecast cell is F12 profit per month Drill Bit Replacement xls Drilling depth 520
148. t Specifying decision variable constraints and forecast objectives and requirements e Running an optimization This chapter has two tutorials a short one and along one that provide an overview of OptQuest s features The first tutorial the Futura Apartments model is an extension of the model used in the Crystal Ball documentation and finds the optimal rent for an apartment building This model is ready to run so you can quickly see how OptQuest works The second tutorial the Portfolio Allocation model lets you set up and define an optimization yourself T his model finds the optimal solution of investments that balances the risk and the return of the portfolio Now spend some time learning how OptQuest can help you find the optimal solutions for your Crystal Ball models In this chapter What OptQuest does Glossary Term decision variable A variable in your model that you have control over Glossary Term optimal solution The set of decision variable values that achieves the best outcome In most simulation models there are variables that you have control over such as how much to charge for rent or how much to invest These controlled variables are called decision variables Finding the optimal values for decision variables can make the difference between reaching an important goal and missing that goal Obtaining optimal values generally requires that you search in an iterative or ad hoc
149. t OptQuest starts with a point at the most restrictive end of the requirement range OptQuest runs simulations either until it decides that enough simulations have gone by without making a significant improvement to the best solution or until it reaches some maximum number of simulations based on the number of decision variables in your model OptQuest considers a significant improvement any improvement greater than the largest improvement during the optimization multiplied by the Tolerance value Therefore increasing the Tolerance which is set to 0 00001 by default to a number such as 0 01 can speed up the movement to the next requirement point Of course this also means that OptQuest might stop and move on to the next point prematurely so changing the Tolerance should be approached with caution Complexity of the objective A complex objective has a highly nonlinear surface with many local minimum and maximum points Rh Ai ake I MR As AN i a u N Ui AN N IN Es a NN AN SR vi SH NON N Sun BR N OptQuest is designed to find global solutions for all types of objectives especially complex objectives like this one H owever for more complex objectives you generally need to run more simulations to find high quality global solutions OptQuest User Manual 159 C hapter Optimization Tips and Suggestions Simulation speed By increasing the speed of each simulation you can increase the number of simulations
150. t Frontier 15 x Objective Requirement Money Growth and Aggressive Total Total expected Market Income fund Growth fund 8107 40 9204 58 0 00000 10000 00 57500 0 32500 0 8341 42 9428 71 6111 11 10000 00 39444 4 44444 4 8341 42 9428 71 6111 11 10000 00 39444 4 44444 4 gt 8631 21 9950 78 0 00000 14310 0 36091 4 48916 5 4 Efficient Frontier Objective 7500 8000 8500 9000 9500 10000 Total expected return 2 Std Dev Next Point Variable Requirement Current Value 10000 00 Figure 3 3 Efficient Frontier window The only button on this window is the Next Point button which forces O ptQuest to start optimizing for the next requirement point If you don t use the N ext Point button O ptQ uest runs the initial requirement point the most restrictive end of the range until there is no significant improvement between best values or OptQuest User Manual 89 Chapter Setting Up and Optimizing a M odel until it reaches a maximum number of simulations based on the number of decision variablesin the model O ptQ uest then runs the successive requirement points for approximately half the time of the initial requirement point Interpreting the results After solving an optimization problem with O ptQuest you can 1 Run a solution analysis to determine the robustness of the results 2 Runalonger Crystal Ball simulation using the optimal values of
151. t and want to start over again w a Click on Cancel b Select Tools gt Wizard Decision Variable Selection Select variables and set bounds Money Market fund 50000 Continuous Income fund 25000 Continuous Growth and Income fund 80000 Continuous Aggressive Growth fund 100000 Continuous Figure 1 6 OptQuest Decision Variable Selection window Every decision variable defined in the Crystal Ball model appears in the Decision Variable Selection window The first column indicates whether the variable has been selected for optimization The other columns show the bounds initial value and type for each variable Check the checkboxes by each decision variable to optimize all decision variables By default all decision variables are already selected Click on OK The Constraints window appears Specifying constraints The Constraints window lets you specify any restrictions you can possible solutions to a model You must define constraints in terms of decision variables 24 define with the decision variables The constraintin this model limits the initial investment to 100 000 The right side of the Constraints window lists the selected decision variables Constraints can use only linear combinations of these variables Enter constraining equations in the window placing each constraint on its own line OptQuest User Manual OptQuest Note To move a
152. t the mean as the statistic you would want to maximize the profit mean H owever if you select the standard deviation as the statistic you might want to minimize it to limit the uncertainty of the forecast Requirements Requirements restrict forecast statistics These differ from constraints since constraints restrict decision variables or relationships among decision variables OptQuest Note R equirements are sometimes called probabilistic constraints chance constraints or goals in other literature When you define a requirement you first select a forecast either the objective forecast or another forecast As with the objective you then select a statistic for that forecast but instead of maximizing or minimizing it you give it an upper bound a lower bound or both a range Feasibility Like constraints requirements must be satisfied for a solution to be considered feasible When an optimization model includes requirements a solution that is constraint feasible might be infeasible with respect to one or more requirements After first satisfying constraint feasibility OptQ uest assumes that the user s next highest priority is to find a solution that is requirement feasible Therefore it concentrates on finding a requirement feasible solution and then on improving this solution driven by the objective in the model OptQuest User Manual 49 C hapter Understanding the Terminology Requirement
153. tent manner Glossary 187 The increased accuracy of this method comes at the expense of added memory requirements to hold the full Latin hypercube sample for each assumption linear A mathematical relationship where all termsin the formulas can only contain a single variable multiplied by a constant For example 3x 1 2y isa linear relationship since both the first and second term involve only a constant multiplied by a variable mean The familiar arithmetic average of a set of numerical observations the sum of the observations divided by the number of observations mean standard error The standard deviation of the distribution of possible sample means This statistic gives one indication of how accurate the simulation is median The value midway in terms of order between the smallest possible value and the largest possible value metaheuristic A family of optimization approaches that includes genetic algorithms simulated annealing tabu search and their hybrids mixed A type of optimization model that has both discrete and continuous decision variables mode The value that if it exists occurs most often in a data set model A representation of a problem or system in a spreadsheet application such as Excel or Lotus 1 2 3 multiobjective optimization A technique that combines multiple often conflicting objectives such as maximizing returns and minimizing risks into one objective 188 nonli
154. that O ptQuest runs in a given time period Some suggestions to increase speed are e Use Precision Control in Crystal Ball to stop simulations as soon as they reach a satisfactory accuracy e Reduce the size of your model e Increase your system s RAM memory e Reduce the number of assumptions and forecasts e Increase the Burst Mode for small models e Quit other applications The Crystal Ball U ser M anual discusses these suggestions in more detail For networked computers Decisioneering Inc offersCB Turbo a distributed processing add on to Crystal Ball that can dramatically increase the speed of your simulations For more information visit our Web page at http decisioneering com cbturbo about html Sensitivity analysis using a tornado chart One of the easiest ways to increase the effectiveness of your optimization is to remove decision variables that require a lot of effort to evaluate and analyze but that don t affect the objective very much If you are unsure how much each of your decision variables affects the objective you can use the Tornado Chart tool in Crystal Ball see the Crystal Ball U ser M anual for more information on the Tornado Chart The Tornado Chart tool graphs how sensitive the objective is to each decision variable as they change over their allowed ranges The chart shows all the decision variables in order of their impact on the objective Viewing a tornado chart with the most important var
155. that satisfies any constraints imposed on the decision variables 9 10 Click on OK in the Options window The search time is set for 10 minutes O ptQ uest prompts you to run the optimization Click on Yes in the Run Optimization Now query OptQ uest begins to systematically search among the set of feasible solutions for ones that improve the mean value of the Profit Or Loss forecast N ote that the first solution examined by OptQuest consists of the initial values of the decision variables in your spreadsheet different initial values can result in different sequences of solutions or a different best identified solution As the optimization progresses O ptQ uest collects the results of the best solutions both in the Status And Solutions window and on a performance graph See Figure 1 2 OptQuest Note When you limit the optimization by time asin this example the number of simulations varies depending on your computer s processing speed Thus your results might not be exactly the same as those shown in Figure 1 2 however they should be close For more information on other factors that affect the results see F actors that affect search performance on page 154 Status and Solutions ioj x r Optimization File UnNamed opt N Crystal Ball Simulation Futura with Optimization is Complete Maximize Objective Profit or Loss ERR Rent per Unit 1 2500 93 500 2 2867 60
156. the decision variables to more accurately assess the risks of the recommended solution 3 Use Crystal Ball s analysis features to further evaluate the optimal solution Running a solution analysis Glossary Term determined variables Variables that take on the same or almost always the same value for most high quality solutions Statistics about the decision variable values can help you answer two questions e How robust is the best solution e Are there any variables that are irrelevant and should be deleted from the model The analysis answers the first question by identifying determined variables f the best solution s decision variables are determined variables the solution is robust The analysis answers the second question by identifying irrelevant variables These variables vary widely within their defined bounds with little or no effect on the results You can undefine these decision variables and leave them as constants This reduces the number of decision variables and improves the performance of the optimization When you eliminate one or more variables from the model you should rerun the optimization The search will then intensify around the remaining variables 90 OptQuest User Manual After the optimization is finished interpret your optimization results on 1 Select Run gt Solution Analysis The Solution Analysis window appears 2 Enter a percentage in the Percentage From Best field Fo
157. the following three tabs e Time e Preferences Advanced Options Select allowable running time for optimization Time Run for J simulations Automatic Stop Run for fic minutes Run until BEN 1 BE I H Current Time and Date 11 55 41 PM September 10 1999 Figure 1 9 OptQuest Options window Time tab The Time tab lets you specify the total time that the system searches for the best solutions for the decision variables You can enter the number of minutes to run an optimization the number of simulations or a date and time for the process to stop The default optimization time is 10 minutes 1 Ifyou have a 200 MHz processor or faster set the time limit to 10 minutes Set it higher for slower processors If you select a very long time limit you can always terminate the search by selecting Run gt Stop or pressing lt Esc gt Additionally OptQuest prompts you to extend the search when the time limit ends Preferences and Advanced tabs The Preferences and Advanced tabs contain additional options for controlling the optimization process See O ptions window on page 76 for descriptions of these options OptQuest User Manual 27 Chapter 28 2 EK BE OptQuest User Manual Getting Started Click on OK OptQuest prompts you to run the optimization Click on Yes The Status And Solutions window appears Each time OptQuest identifies a better solution during the optimizatio
158. tistic for each simulation The column heading displays whether the objective is Maximize Objective or Minimize O bjective the name of the forecast and the optimized statistic Requirements Lists the value of the requirement forecast statistic for each simulation The column heading displays that the column isa requirement the name of the forecast and the requirement statistic Variable requirements Lists the value of the variable requirement forecast statistic for each simulation and the current requirement value which changes during the optimization Decision Variables Lists the value for each decision variable for that simulation in its own column The column heading displays the variable name Performance graph This window displays the trajectory of the search that is the rate at which the best objective value has changed during the course of the search This is shown asa plot of the best objective values as a function of the number of trial solutions To access this window either e Run the wizard e Select View gt Performance Graph al e Click on the Performance Graph icon OptQuest User Manual 85 C hapter Setting Up and Optimizing a M odel Performance Graph E ioj x Requirement Feasible Objective Requirement Infeasible Simulation Figure 3 2 Performance graph window As OptQuest runs this window graphically displays the values listed in the Status And Solutions window If any req
159. tistics 76 budget constrained project selection example 107 C changing objectives practice exercise 36 cleanup groundwater example 111 coefficient of variability 59 commands OptQ uest menu 175 start pause stop 82 commands keyboard lit 169 OptQ uest 169 complexity of objective 159 constraint editor syntax 72 constraint feasibility defined 47 Index 195 constraints affecting performance 157 defined 24 46 defining 71 defining nonlinear 71 editor 72 syntax 72 window description 72 consulting referral service 9 continuous decision variables 46 models 51 conventions manual 10 correlating assumptions practice exercise 35 credits 206 Crystal Ball models creating 65 suggested run preferences 67 Current Decision Variables window 87 D decision variables bounds defined 45 defined 13 45 in constraints 72 number affecting performance 156 selecting to optimize 68 selection window 69 step size 46 types 46 Decisioneering web page 160 165 determined variables 90 deterministic model illustrated 44 models 52 option for setting model type 79 setting model type 79 deviation standard 56 dialogs Step Size 70 discrete decision variables 46 models 51 variable step size 46 drill bit replacement example 146 196 E Edit menu 175 efficient frontier window 89 efficient portfolios 121 engineering petrochemical references 182 error mean standard 60 examples drill bit replacement 146 groundwater cleanup 111 hotel
160. tive and variable values for the solutions whose objective falls within the analysis range T he columns in the Solutions table are Solution The ordered ranking of the solution as it falls in the analysis range This might be different than the number of the original simulation Objective The objective value for the solution 92 OptQuest User Manual Variables The value of each decision variable listed in its own column OptQuest Note You can select and copy cells to the clipboard from either the analysis table or the solutions table Running a longer simulation of the results To more accurately assess the recommended solution run a longer Crystal Ball simulation using the optimal values of the decision variables 1 Copy asolution to Crystal Ball by a Selecting asolution to copy in the Status And Solutions window The default is the best solution found b Selecting Edit gt Copy To Excel OptQ uest copies the decision variables values from the selected solution into the Excel model OptQuest Note By default O ptQ uest restores only the simulation for the best solution To save other solutions select the appropriate option under Options gt Preferences 2 Exit OptQuest by selecting File gt Exit If you haven t saved the optimization file yet O ptQ uest prompts you to save it If you haven t copied a solution into Crystal Ball OptQ uest prompts you to copy the best solution into your spreadsheet m
161. to be the objective You must also select whether to maximize or minimize the objective Forecast Total expected return Of x Edit Preferences View Run Help 500 Trials Frequency Chart 1 Outlier 032 T 16 Probability Aguanbai4 40 000 15 000 10 000 llars gt Hinfinity Certainty A 4 Infinity 35 000 60 000 Figure 2 1 Forecast shown with mean statistic The statistic you choose dependson your goals for the objective For maximizing or minimizing some quantity the mean or median are often used as measures of central tendency with the mean being the more common of the two For highly skewed distributions however the mean might become the less stable having a higher standard error of the two and so the median becomes a better measure of central tendency 48 OptQuest User Manual For minimizing overall risk the standard deviation or the variance of the objective are the two best statistics to use For maximizing or minimizing the extreme values of the objective a low or high percentile might be the appropriate statistic For controlling the shape or range of the objective the skewness kurtosis or certainty statistics might be used For more information on these statistics see Statistics on page 54 Minimizing or maximizing Whether you want to maximize or minimize the objective depends on which statistic you select to optimize For example if your forecast is profit and you selec
162. triction isa requirement H owever if the investor wants to see if a small increase in the requirement could create a sharp increase in the investment return the investor can set this as a Variable Requirement Upper Bound since this limits the maximum standard deviation The investor can define this upper bound with a lower limit of 8000 and an upper limit of 10 000 50 OptQuest User Manual Types of optimization models Discrete continuous or mixed Optimization models can be classified as Model Have discrete Only discrete decision variables continuous Only continuous decision variables mixed Both discrete and continuous decision variables For more information on discrete and continuous decision variables see Decision variables on page 45 1 3 5 7 9 4 50 4 75 5 00 5 25 5 50 Discrete variable Continuous variable Staff requirements Prime interest rate Linear or nonlinear An optimization model can be linear or nonlinear depending on the form of the mathematical relationships used to model the objective and constraints In a linear relationship all terms in the formulas only contain a single variable multiplied by a constant For example 3x 1 2y isa linear relationship since both the first and second term only involve a constant multiplied by a variable Terms such as x2 xy 1 x or 3 1 make nonlinear relationships Any models that contain such terms in either the objective or a constraint are clas
163. tura Apartments model 1 To start OptQuest either e Select CBTools gt OptQuest Ai e Click on the OptQuest icon on the Crystal Ball toolbar Crystal Ball Note The toolbar icon does not appear until the first time you select CBT ools gt OptQuest The initial O ptQ uest logo and window appears 2 Select File gt New The Decision Variable Selection window appears with the one decision variable Rent Per Unit The check in the Select column indicates that the variable is selected for optimization The lower bound on the variable is 400 the upper bound is 600 and the suggested value is 500 the current value in the worksheet The variable type is listed as Continuous 3 Click on OK in the Decision Variable Selection window The Constraints window appears This problem has no constraints on the decision variables so do not add any here 4 Click on OK in the Constraints window The Forecast Selection window appears In the model the Profit Or Loss cell is a forecast cell and the objective is to maximize the mean average profit Click on the down arrow button under Select 6 Select Maximize Objective for the Profit Or Loss forecast 7 Click on OK in the Forecast Selection window The Options window appears letting you set various optimization options 8 Set the run time to 5 minutes Therun time is on the Time tab 16 OptQuest User Manual Glossary Term feasible solution A solution
164. tween the largest and smallest valuesin a data set rank correlation A method whereby Crystal Ball replaces assumption values with their ranking from lowest value to highest value using the integers 1 to N prior to computing the correlation coefficient This method lets you ignore the distribution types when correlating assumptions RAROC A multiobjective function that calculates the Risk adjusted Return On Capital reorder point The inventory position when you reorder requirement A restriction on a forecast statistic that requires the statistic to fall between specified lower and upper limits for a solution to be considered feasible risk The uncertainty or variability in the outcome of some event or decision risk factor A number representing the riskiness of an investment relative to a standard such as U S Treasury bonds used especially in APT run A Crystal Ball simulation 190 safety stock The additional quantity kept in inventory above planned usage rates seed value The first number in a sequence of random numbers A given seed value produces the same sequence of random numbers every time you run a simulation sensitivity The amount of uncertainty in a forecast cell that isa result of both the uncertainty probability distribution and model sensitivity of an assumption or decision variable cell sensitivity analysis The computation of a forecast cell s sensitivity with respect to the
165. ual Conventions used in this manual This manual uses the following conventions Text separated by gt symbols means you select menu options in the sequence shown starting from the left The following example means that you select the Exit option from the File menu 1 Select File gt Exit Steps with attached icons mean you can click on the icon instead of manually selecting the menu optionsin the text For example 2 Select Cell gt Define Decision N otes provide additional information expanding on the text There are four categories of notes OptQuest Note N otes that provide additional directions or information about using OptQuest Crystal Ball Note N otes that provide additional directions or information about using Crystal Ball Statistical Note N otes that provide additional information about statistics Excel Note N otes that provide additional information about using the program with M icrosoft Excel Screen capture notes All the screen captures in this document were taken in Excel 97 for Windows 95 Due to round off differences between various system configurations you might also notice slightly different calculated results than those shown in the examples 10 OptQuest User Manual Chapter 1 Getting Started Ny Mi What O ptQ uest does Futura Apartments model tutorial Portfolio Allocation model tutorial Defining decision variables in Crystal Ball Running OptQues
166. uct Mix xls file shown in Figure 4 1 is a spreadsheet model for this problem The input data and model outputs are straightforward Browse through the Crystal Ball assumptions that define the uncertainty of the casing requirements and unit profits Profit per Quantity to Unit Produce I SE NEN I Bratwerst Hakan Sean Fagpern Frat Sanne Inventory sing Gin Hand 12520 00 14 100 00 6 480 00 800 00 i died i 5 000 00 11 500 00 5 500 00 7 000 00 I Remaining 7520 00 2 600 00 980 00 3 800 00 as Gross Profit 38 250 001 Figure 4 1 Product mix problem spreadsheet model OptQuest User Manual 99 C hapter Examples U sing OptQuest 100 OptQuest solution OptQuest Note Except where indicated this example uses the recommended Crystal Ball run preferences See Setting Crystal Ball run preferences on page 67 To run the optimization 1 Set the number of trials in Crystal Ball to 1000 since tail end percentile requirements need more accuracy 2 Start O ptQ uest from the Crystal Ball CB Tools menu or toolbar 3 Open the Product Mix opt file 4 Start the O ptQ uest wizard As you step through the problem note e This problem has five decision variables three constraints one each for availability of veal pork and beef and one requirement e The requirement ensures that at most a 5 chance exists of exceeding the casing limitation 5 Run the optimization Statu
167. uirement statistic This field is used only for requirements For a requirement you must define either an upper bound a lower bound or both For a variable requirement you must set both the lower and upper bound Displays the forecast units defined in Crystal Ball This field is for display only Displays the Excel workbook file the forecast is from This field is for display only Displays the Excel worksheet the forecast is from This field is for display only Displays the Excel cell the forecast is from This field is for display only To select optimization options 1 Change any options in the O ptions window For information on the options see Options window below Click on OK A dialog asks you to start the optimization Options window The Options window lets you set options for controlling the optimization process To access this window either OptQuest User Manual e Run the wizard Select Tools gt Options e Click on the Options icon The Options window has the following three tabs e Time e Preferences Advanced Options Select allowable running time for optimization Run for J simulations Automatic Stop Run for fo z minutes O Runu S iy g fal a Current Time andDate 11 55 41 PM September 10 1999 Time tab The Time tab lets you control how long to run the optimization If you select a very long time limit or a large number of simulati
168. uirements have been specified the line might initially be red indicating that the corresponding solutions are requirement infeasible A green line indicates requirement feasible solutions Once OptQuest finds a requirement feasible solution it is common for this line to show an exponential decay form for minimization where most improvements occur early in the search The Frontier button opens the Efficient Frontier window This button is only available when your optimization has a variable requirement For more information see Efficient Frontier window on page 89 The Rescale button lets you Change the range of the y axis of the graph e Plot the values on a linear or logarithmic scale Add an additional requirement or decision variable to the graph 86 OptQuest User Manual Bar graph These functions are useful for examining the graph where the new best values are too close together to distinguish easily To return the graph to its original scale and range click on Automatic Scaling To remove an additional plotted line select None from the Additional Y Value list This window displays the different values for the decision variables for either the current simulation or after the optimization is complete the best simulation found If your optimization model has more than 10 variables only the first 10 are displayed To access this window either e Select View gt Bar Graph e Click on the Bar Graph icon
169. ulated trial the expected returns will either equal the expected revenue generated in column C or zero Consequently the expected profits can be positive or negative 108 OptQuest User Manual Although good solutions might be identified by inspection or by trial and error basing a decision on expected values can be dangerous because it doesn t assess the risks In reality selecting R amp D projects is a one time decision each project will be either successful or not If a project is not successful the company runs the risk of incurring the loss of the initial investment T hus incorporating risk analysis within the context of the optimization is a very useful approach OptQuest solution Start O ptQ uest from the Crystal Ball CBTools menu or toolbar In OptQuest 1 Open the Project Selection opt file Start OptQ uest from the Crystal Ball CBTools menu or toolbar In OptQuest 1 Open the Hotel Design optfile 2 Start the OptQ uest wizard As you step through the problem note that there are eight decision variables one constraint representing the budget limitation and no requirements 3 Run the optimization Status and Solutions Ez lo x c program files crystal ball examples optquest files project Optimization File Budget Constrained Project Selection Optimization is Complete Maximize Objective en Project 1 Project 2 Project 3 Project 4 Project 5 Project 6 Project 7 Projec 0
170. umber representing the riskiness of an investment relative to a standard such as U S Treasury bonds Some macroeconomic influences might include e The level of industrial activity e The rate of inflation e The spread between short and long term interest rates The spread between low and high risk bond yields A weighted sum of these influences determines the risk factor of an asset APT provides estimates of the risk factors for particular assets to these types of influences Higher risk factors indicate greater risk lower factors indicate less risk Assume that the risk factors per dollar allocated to each asset are Investment Risk factor dollar invested Money market fund 0 3 Income fund 0 5 Growth and income fund 0 4 Aggressive growth fund 2 1 Using this method the investor can specify a target level for the weighted or aggregate risk factors leading to a constraint that limits the overall risk For example suppose that the investor can tolerate a weighted risk per dollar invested of at most 1 0 Anything above 1 0 is too risky for the investor Thus the weighted risk for a 100 000 total investment must be at or below 100 000 If the investor distributed 100 000 equally among the four available assets the return would be 0 03 25 000 0 05 25 000 0 07 25 000 0 11 25 000 7 000 And the total weighted risk would be 0 3 25 000 0 5 25 000 0 4 25 000 2 1 25 0
171. un Help Frequency Comparison 058 o BB High risk portfolio Probability er 8 oO n E Medium risk portfolio 000 40 000 15 000 10 000 35 000 60 000 i Add Distribution Add Data Chart Prefs Help Figure 1 16 Simulation results comparison Portfolio allocation optimization summary The best O ptQ uest solution identified might not be the true optimal solution to the problem but should be close to the true optimal solution The accuracy of the results depends on the time limit you select for searching the number of trials per simulation the number of decision variables and the complexity of the problem With more decision variables you need a larger number of simulations Further details of the search procedure can be found in Chapter 5 Optimization Tips and Suggestions on page 151 and Appendix A Advanced Optimization References After solving an optimization problem with OptQuest run a longer Crystal Ball simulation using the optimal values of the decision variables to more accurately compute the risks of the recommended solution 34 OptQuest User Manual Sh REPORT2 _ 5 x Crystal Ball Report Simulation started on 6 12 00 at 10 36 57 Simulation stopped on 6 12 00 at 10 37 01 Forecast Total ezpected return Cell C17 Summary Display Range is from 15 000 to 30 000 dollars Entire Range is from 16 364 to 36 038 dollars After 500 Tria
172. vanced statistical or computer knowledge to use OptQuest to its full potential All you need is a basic working knowledge of your personal computer and the ability to use a Crystal Ball spreadsheet model OptQuest User Manual 7 Introduction 8 Welcome to O ptQuest How this manual is organized The manual includes the following OptQuest User Manual Chapter 1 Getting Started This chapter contains two tutorials designed to give you a quick overview of O ptQuest s features and to show you howto use the program Read this chapter if you need a basic understanding of OptQuest Chapter 2 U nderstanding the T erminology This chapter contains a description of optimization models their components and types and a review of basic statistics Read this chapter carefully if your modeling background is limited or if you need a review Chapter 3 Setting Up and Optimizing a Model This chapter provides step by step instructions for setting up and running an optimization in OptQuest Chapter 4 Examples U sing O ptQ uest This chapter contains nine optimization examples from a variety of fields and disciplines Chapter 5 Optimization Tips and Suggestions This chapter describes different factors that enhance the performance of the program s features Appendices e A Advanced Optimization References A list of references describing O ptQ uest s methodology theory of operati
173. w OptQuest User Manual 91 Chapter Setting Up and Optimizing a M odel In the Percentage From Best field enter the percent difference from the best objective that you would consider acceptable for other simulations T his defines the analysis range For example if you want to examine all the solutions that have an objective within 10 of the best objective enter 10 in this field The Analyze button recalculates each of the tables according to the value in the Percentage From Best field The Number Of Observations area displays how many solutions were found with objectives within the percentage specified OptQuest only includes feasible solutions in the analysis The columns in the Analysis table are Column Displays Name The name of the forecast objective or the decision variables Best Values of the objective and the decision variables from the best solution Minimum The minimum values for the objective and the decision variables from the set of solutions that fell within the analysis range Average The average values of the objective and the decision variables from the set of solutions that fell within the analysis range Maximum The maximum values for the objective and the decision variables from the set of simulations that fell within the analysis range Standard Deviation The standard deviation of the objective and decision variable values in the analysis range The Solutions table lists all the objec
174. xample uses the recommended Crystal Ball run preferences See Setting Crystal Ball run preferences on page 67 Start O ptQ uest from the Crystal Ball CBTools menu or toolbar In OptQuest 1 Openthe Oil Field Development opt file 2 Startthe OptQ uest wizard As you step through the problem note e There are three decision variables wells to drill cell B8 facility size cell B12 and plateau rate cell B15 e This problem has no constraints e The objective is to maximize the 10th percentile of the NPV OptQuest User Manual 3 Run the optimization Status and Solutions c program files crystal ball examples optquest Optimization File Oil Field Valuation and Development lol x a is Complete 10 00000 42 5516 10 0931 250 r 115 817 j 9 67936 200 36 134 047 26 13 2500 150 46 149 783 23 15 0000 200 65 178 599 18 12 4737 150 130 178 718 18 12 5665 150 133 179 129 18 12 6594 150 135 182 916 19 12 7212 150 9 02384 Figure 4 17 Oil field development optimization results The results are shown in Figure 4 17 The Crystal Ball simulation of this solution in Figure 4 18 maximizes the 10th percentile of the NPV 3 Forecast NPV Cell B30 Help Percentiles Percentile 0 10 20 30 40 50 60 70 80 90 100 0 x Edit Preferences View Run 188 mm Figure 4 18 Oil

Download Pdf Manuals

Related Search

Related Contents

User's Manual  Operating Instructions  MS-Q Imager User`s Manual    MODE D`EMPLOI U BOT SWEEPING ROBOT  Manuel d`entretien Dynacode IP - carl  

Copyright © All rights reserved. Failed to retrieve file