Home

Examples Using OptQuest

1. 5 The Decision Variables panel shows one decision variable Rent Per Unit The lower bound on the variable is 400 the upper bound is 600 and the base case is 500 the current value in the worksheet The variable type is listed as Discrete Because Freeze is not checked this decision variable will be included in the OptQuest simulation 6 Click Next to continue The Constraints panel opens as shown in Figure 16 Figure 16 Constraints panel Futura with OptQuest example OptQuest Welcome Objectives Decision Variables Optionally specify constraints on the decision variables Efficient Frontier This example has no constraints on the decision variables so do not add any here 7 Click Next in the Constraints panel The Options panel opens 52 OptQuest Tutorials Figure 17 Options panel Futura with OptQuest example OptQuest Welcome z ETE Choose your options and run the optimization Objectives Decision Variables E Optimization control Type of optimization Constraints parem Run for 1000 simulations With simulation stochastic ORunfor 5 minutes Without simulation deterministic Sittin While running Decision variable cells Show chart windows as defined Leave set to original values Show only target forecast windows Automatically set to best solution V Update only for new best solutions 8 Set OptQuest to run for 1000 sim
2. ORACLE CRYSTAL BALL DECISION OPTIMIZER FUSION EDITION RELEASE 11 1 1 3 00 OPTQUEST USER S GUIDE ORACLE ENTERPRISE PERFORMANCE MANAGEMENT SYSTEM Crystal Ball Decision Optimizer OptQuest User s Guide 11 1 1 3 00 Copyright 1988 2009 Oracle and or its affiliates All rights reserved Authors EPM Information Development Team The Programs which include both the software and documentation contain proprietary information they are provided under a license agreement containing restrictions on use and disclosure and are also protected by copyright patent and other intellectual and industrial property laws Reverse engineering disassembly or decompilation of the Programs except to the extent required to obtain interoperability with other independently created software or as specified by law is prohibited The information contained in this document is subject to change without notice If you find any problems in the documentation please report them to us in writing This document is not warranted to be error free Except as may be expressly permitted in your license agreement for these Programs no part of these Programs may be reproduced or transmitted in any form or by any means electronic or mechanical for any purpose If the Programs are delivered to the United States Government or anyone licensing or using the Programs on behalf of the United States Government the following notice is applicable U S GOVERNMENT R
3. In This Chapter Overview D MEN css dts Gon casa edocs at Seen biceps steal dca ean mana 67 PPPS mE AA ostensible cn A ents elena T N ER al 69 Hotel Desigmand Pacing Probes sisi ie ie nee al a e re Bugzeleonstrained Project SERCHION ci usiui tn a a a ee es 75 Gioundwator ICANN sociari ipaa piae A aA aa aaa a oa 79 DIPSA Deve ment aaan a A E E 83 Portilo REVNE naenin a E E E 86 A E e E e EEEE BET A A E E I P EET A nes wh SE ETEN T AA E TE 92 VOR Cy Syston OMUMIZALO isean asad En ANER SAAE AEE AAEE AEE DAEN AADO 96 Drill Bit Replacement Polg iss vere wera rceu diva wsea resi a EAE 102 FASE INS Supay Chailean Aaa a enlepede dia a a Aaa 105 This chapter presents a variety of examples using OptQuest These examples illustrate how to use spreadsheets to model optimization problems the key features of OptQuest and the variety of applications for which you can use OptQuest Each section includes a problem statement a description and explanation of the spreadsheet model the OptQuest solution and optionally additional practice exercises using the model All Excel model files and associated OptQuest files are in the Examples folder under the main Crystal Ball installation folder You can also display an index to the examples by choosing one of the following command sequences and selecting from the Examples Guide e Help Crystal Ball Examples Guide Excel 2003 or earlier e Help Resources Examples Guide Excel 2007 e Start All Programs Cry
4. Type Exclude aii lt Th Oo In the first box from the left enter the cell that contains the left hand side of the constraint formula in this case cell G13 You can type G13 or you can use the cell selector to point to that cell If the cell has a range name you can use that instead of the cell address The default operator is lt In this case the formula calls for Click the underlined operator and select the one you want Figure 25 Figure 25 Changing the constraint operator Constraints 1 ModellG13 To enter the right hand value for the equation either type a number or reference a cell or range name that contains a value or formula In Figure 26 following a number was entered Figure 26 A constraint entered in Simple Entry mode Constraints 1 ModellG13 100000 OptQuest Tutorials 5 At this point you can Add another constraint Add a comment Add a variable bound for Efficient Frontier analysis Click Next to continue to the Options panel or Click Run to run the optimization For more information about adding comments and variable bounds see Constraints Editor and Related Buttons on page 31 Asan alternative you can enter the constraint formula directly using Advanced Entry mode For an example see Specifying Constraints in Advanced Entry Mode on page 29 6 When constraints settings are complete click Next to continue The Options panel opens similar to F
5. _ 11 400 00 is 10 800 00 Best solutions Infeasible solutions Last bestsolution 10 200 00 9 600 00 Gross Pro 74 149 224 299 374 449 524 599 674 Simulations Best Solution Simulation 241 Objectives Maximize the Mean of Gross Profit 11 780 33 Requirements The 5 Percentile of Casing Remaining must be greater than or equal t Veal used must not exceed inventory Pork used must not exceed inventory Beef used must not exceed inventon eoj Italian Sausage Pepperoni Polish Sausage Summer Sausage In the Best Solution view the OptQuest Results window shows a performance chart plotting best solutions found during analysis It also shows the single best solution found for the objective any requirements any constraints and all included decision variables Performance Chart The performance chart 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 as a plot of the best objective values as a function of the number of simulations solutions If any requirements have been specified the line might initially be red indicating that the corresponding solutions are not feasible according to the requirements A green line indicates feasible solutions Running Optimizations 37 38 Once OptQuest finds a feasible solution it is common for this line to show a
6. 7 0 1 Rent per Unit 85 Rent per Unit Monthly Expenses Profit or Loss Price demand parameters Slope Intercept M 4 gt ri Description Model In this 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 However 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 OptQuest select Run Run Preferences and set the following run preferences Maximum number of trials to run set to 1000 the default Sampling method set to Latin Hypercube Sample Size For Latin Hypercube set to 500 Random Number Generation set to Use Same Sequence Of Random Numbers with an Initial Seed Value of 999 Running OptQuest gt Use the following steps to start OptQuest and optimize the Futura Apartments model 1 To start OptQuest choose Run OptQuest or click gt OptQuest Tutorials The OptQuest wizard starts If this is the first time you have run OptQuest the OptQuest Welcome panel opens Otherwise the Objectives panel opens Note Notice the text at the bottom of the Welcome panel that says all OptQuest
7. 89 Maximum 40 447 09 450 82 00 138 00 110 00 90 Std Dev 91 47 2 0 46 5 65 2 30 91 92 Notes 93 Extracted data for top 10 of solutions The output is virtually identical to the information shown in the Solution Analysis view of the OptQuest Results window including the filtering options and the column sort order To see a different set of solutions display the Solution Analysis view and change the options before you choose Analyze Extract Data For more information about extracting data see the online Oracle Crystal Ball User s Guide Saving optimization models and settings When you run an optimization current settings on the Options panel and Advanced Options dialog are automatically saved in a preference file and will be applied to future optimizations Other settings such as objectives requirements and constraint definitions are saved in the primary workbook selected in the dropdown list on the Objectives panel They are saved to the workbook when the optimization runs however they are not saved permanently until you save the primary workbook itself If you choose to copy optimization values to the model these values appear as the new cell values and are also saved when the model is saved Each workbook can have one set of optimization settings If you click Close in the OptQuest wizard before you run an optimization OptQuest asks whether to save the settings If you respond Yes current sett
8. Probability Total Annual Costs zesese2a8 8 Aouenbe4 Inventory System Optimization 101 Drill Bit Replacement Policy This example was suggested from an example in Kenneth K Humphreys Jelen s Cost and Optimization Engineering 3rd ed New York McGraw Hill 1991 257 262 The following sections describe this problem and its OptQuest solution e Drill Bit Replacement Problem Statement on page 102 e Drill Bit Replacement Spreadsheet Model on page 103 e Drill Bit Replacement OptQuest Solution on page 104 Drill Bit Replacement Problem Statement When drilling wells in 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 dH T7 10 For example after 5 hours of consecutive use starting with a new drill bit the drill is able to penetrate the terrain at a rate of 15 5 10 21 21 meters per hour While after 50 hours the penetration rate is only b 6 71 meters per hour 50 10 Eventually the bit must be replaced as the costs exceed the value of the well being drilled The problem is to determine the optimum replacement policy that is the drilling cycle T hours between replacements T hours after replacing the bit the total 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
9. 130 tolerance design 131 remaining time viewing 36 reporting on OptQuest data 42 reports creating 42 using OptQuest data 42 requirements affecting performance 117 defined 17 58 113 defining 26 examples 17 feasibility 19 recommended number 116 variable 46 results analyzing in Crystal Ball 42 interpreting in OptQuest 40 Index 139 A B C D E FGH printing 42 run preferences suggested 25 S saving OptQuest settings 45 screen capture notes 10 sensitivity analysis using tornado chart 119 Simple Entry mode 29 simulations accuracy of 115 current number 36 running longer 42 speed of 118 Six Sigma 21 solution analysis of results 40 Solution Analysis window 38 solutions feasible defined 53 viewing 36 speed of simulations 118 spreadsheet design references 130 spreadsheet models creating 24 start command 36 starting OptQuest 26 statistics forecast defined 16 113 forecast optimizing 16 restricting forecasts 17 selecting forecast 26 Status And Solutions window 36 step sizes for decision variables 18 steps for using OptQuest 23 stop command 36 syntax constraint 32 T time remaining viewing 36 tolerance analysis example 92 tolerance design references 131 tornado chart 119 transferring settings from opt files 46 tutorials Futura Apartments 49 Portfolio Allocation 54 types decision variable 17 140 Index K L M N O P Q R S T U VW U using OptQ
10. 50 FALSE 0 TRUE Pos 500 400 300 0 TRUE 150 FALSE 50 TRUE He Inv Week Hold Inventory Simulation With Lost Sales Optimize order quantity and reorder point to minimize costs Total Annual Costs 1 040 1 050 5 000 SunOS Order Cost Short Cost S 80 10 5 000 5 050 Total Due Cost Cost CREE i i EA en ea aien ea ea ea i O P len en en enian ea on ea 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 is in 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 98 Examples Using OptQuest a beginning inventory level demand orders received ending inventory max 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 is to minimize the mean total annual cost cell O6 is defined as a forecast cell Column I determines whether the manager should place an order by checking if the beginning inventory position minus the weekly demand is at or below the reorder point The ending inventory position is ending beginning inventory inventory
11. If you look at the Futura with OptQuest workbook you can see that cell C4 the decision variable is now set to the Rent per Unit value that OptQuest calculated 431 The spreadsheet holds deterministic calculations based on the optimal value of the decision variable Because cells C3 and C7 contain formulas that include C4 the values of those cells have also changed as shown in Figure 19 Now you need to rent 42 units at 431 each to obtain maximum proft of about 3 059 Figure 19 Futura with OptQuest optimized for maximum profit E Futura with OptQuest xls Futura Apartments with OptQuest Number of units Number of Units Rented 42 01 Rent per Unit 85 Rent per Unit Monthly Expenses Profit or Loss Price demand parameters Slope Intercept Ja a gt WIN Description Model Note When you runan optimization wizard settings are automatically saved to your workbook For details see Saving optimization models and settings on page 45 Tutorial 2 Portfolio Allocation Model 54 This is a more detailed tutorial that will guide you through setting up and running an optimization model using Crystal Ball Decision Optimizer with OptQuest Ifyou are not familiar with basic optimization terminology such as objectives and constraints review Chapter 2 Overview OptQuest Tutorials Problem Description An investor has 100 000 to invest in four assets Below is a list of the assets expect
12. It also gives details about each of the panels and dialogs in OptQuest including all the fields and options To set up and optimize a model with OptQuest follow these steps Create a Crystal Ball model of the problem Define the decision variables within Crystal Ball In OptQuest select the forecast objective and define any requirements Select decision variables to optimize Specify any constraints on the decision variables Select optimization settings Introduction 23 7 8 Run the optimization Interpret the results For Users of OptQuest Versions Earlier Than 11 1 1 x Ifyou used a version of OptQuest earlier than 11 1 1 x be aware of some significant changes As you have discovered the user interface is redesigned to be easier to use For added flexibility there are now five types of decision variables Another difference is that opt files are no longer used to store optimization settings For more information on saving optimization settings and options see Saving optimization models and settings on page 45 An opt file viewer is provided to help you transfer settings from opt files to current model workbooks For instructions see Transferring Settings from opt Files on page 46 Developing a Crystal Ball Optimization Model 24 Before using OptQuest you must first develop a useful Crystal Ball model This involves building a well tested spreadsheet in Excel and then defining assumptions and fore
13. remaining decision variables Starting OptQuest and Defining the Forecast Objective Before you can run an OptQuest simulation you must define a forecast objective To do this Start OptQuest by selecting Run OptQuest You have probably already started OptQuest at least one time so the Objectives panel opens Figure 21 Tutorial 2 Portfolio Allocation Model 57 Figure 21 Objectives panel Portfolio Allocation example objective added OptQuest Welcome Decision Variables Constraints Objectives Options Select an objective and optionally specify requirements Requirements optional requirements on forecasts Add Objective Add Requirement Efficient Frontier OptQuest requires that you select one forecast statistic to be the objective to minimize maximize or set to a target value In addition to defining an objective you can define optimization requirements described in Editing the Optimization Settings on page 64 As described earlier the objective for this example problem is to maximize the total expected return Since OptQuest working with Crystal Ball calculates forecasts as distributions ranges of values the mean of the Total Expected Return forecast provides a good representative statistic to use for the objective To define an objective click Add Objective A default objective is displayed In this example the default objective has al
14. 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 Requirements are sometimes called probabilistic constraints chance constraints side 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 If you want to perform an Efficient Frontier analysis you can define requirements with variable bounds For more information see Efficient Frontier Analysis on page 19 Requirement Examples In the Portfolio Allocation example of Chapter 4 OptQuest Tutorials 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 restriction is a requirement The following are some examples of requirements on forecast statistics that you could specify 95th percentile gt 1000 1 lt skewness lt 1 Range 1000 to 2000 gt 50 certainty Decision Variables Decision variables are variables in your model that you can control such
15. 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 Project Selection Spreadsheet Model Figure 39 shows a spreadsheet 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 Figure 39 Project selection problem spreadsheet model Sn EET JA m B C D E F G H I Budget Constrained Project Selection 3 Expected Success Expected Initial 4 Revenue Rate Return Investme 5 t 5675 000 5250 000 5425 000 6 2 1 050 000 650 000 3 360 000 250 000 8 4 720 000 500 000 9 5 1 000 000 700 000 10 6 90 000 30 000 11 7 630 000 350 000 12 8 225 000 70 000 13 14 Budget 2 000 000 15 Invested 2 800 000 16 Surplus 800 000 17 Maximize total expected profit Total profit 1 950 000 20 subject to
16. 91 j 36 684 75 E 3 01 o 8 Probability o 8 8 36 200 00 36 300 00 36 400 00 36 500 00 36 600 00 Certainty 100 00 q Infinity OOo Statistics shown in color are tested for 0 00 pre 110 Examples Using OptQuest Optimization Tips and Notes In This Appendix Mein e Ne EE E A tna Sa de ting ag otk ep ba nes T E ATT 111 PMR Dre pte capt gps cect esses eke estado Raa arate aetna ane 111 Factors That Aitect Optimization FPeromanto acisna a a a a a er ee a ee es 114 Sensitivity Anass Using a Tomado Chatt sire accueil earn eee 119 Maintaining Multiple Optimization Settings for a MOREL ci cccimetenactnas coansias euachinnd sinna iaaa 120 OMAN INEST NOE Sisan a Miaamceeyaarne sar bas cakceenanaaes 121 Introduction This appendix describes the different factors that affect how OptQuest searches for optimal solutions including model types Understanding how these factors affect the optimization helps you control the speed and accuracy of the search This appendix 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 These tips and suggestions are followed by some notes to help you avoid unexpected results when using OptQuest They can also help you troubleshoot any difficulties that might occur Model Types Selecting the right model for your scenario is essential
17. Cost 7 Groundwater Process 6 1 Air stripping 9 000 9 2 Carbon filter 9 500 10 3 Photo oxidation 10 000 11 dollar amounts are in thousands fixed and variables costs are based on 80 efficiency rate 12 Z p R L Decision variabies PE 14 Select Cleanup Efficiency Groundwater Concentration Maintain Acceptable Risk Level v ail This model shows the population risk cell C25 which is the overall contamination risk to the people in the community as a function of the factors shown in Table 8 following Table 8 Groundwater Cleanup population risk factors Risk factors Cells Description Distribution Cancer Potency C18 C20 Cancer potency of each Lognormal contaminant Concentration Before D18 D20 Concentration of each Normal contaminant before cleanup Volume Of Water Per Day C23 Interindividual variability of volume of water consumed each day Normal with lower bound of 0 Body Weight C22 Interindividual variability of body weights in the community Normal with lower bound of 0 Remediation costs of the various cleanup methods cells E8 E10 are a function of factors shown in Table 9 following 80 Examples Using OptQuest Table 9 Groundwater Cleanup remediation cost factors Remediation cost factors Cells Description Distribution Fixed Costs C8 C10 Flat costs for each method Triangular to pay for initial setup Variable Costs D8 D10 Costs for
18. Divide complex calculations into several cells to minimize the chance for error and enhance understanding Place comments next to formula cells for explanation if needed Consult a reference such as those listed in Appendix C for further discussion of good spreadsheet 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 Oracle Crystal Ball User s Guide Setting Crystal Ball Run Preferences To set Crystal Ball run preferences select Run Run Preferences For optimization purposes you should usually use the following Crystal Ball settings Trials tab Maximum number of trials to run set to 1000 Central tendency statistics such as mean median and mode usually stabilize sufficiently at 500 to 1000 trials per simulation Tail end percentiles and maximum and minimum range values generally require at least 2000 trials Sampling tab Sampling method set to Latin Hypercube Latin Hypercube sampling increases the quality of the solutions especially the accuracy of the mean statistic Sampling tab Random Number Generation set to Use 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
19. E eee pee bon rete ae wee 133 WEN 630k eG eee eee Senet Pde eRe PRs 137 Contents vii viii Contents Welcome In This Chapter HUMP EE NIN cscs teeth comes val sca Mtns gan ms AA oh a deca a ne 9 Hew iiis Manual id CA i crete soma r aA E Wesnoth E sa REE O 9 Sereen Capite NOES circitewels alee eee eed ee eel ad bed pe A pee eee 10 GENE Helpen nep a Aaa Ha Raion RE a ee a r 10 POUL RESTUNE S aapna aeaa oun A a aAA a DA Aa Shades aia PAD tea 11 Introduction Welcome to OptQuest an optimization option available in Oracle Crystal Ball Decision Optimizer Fusion Edition OptQuest enhances Crystal Ball by automatically searching for and finding optimal solutions to simulation models Simulation models by themselves can only give you a range of possible outcomes for any situation They do not tell you how to control the situation to achieve the best outcome Using advanced optimization techniques OptQuest finds the right combination of variables to produce accurate results Suppose 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 Suppose you ask What will production rates be for this new oil field Now you can also determine the number of wells to drill to maximize net present value Suppose you wonder Which stock portfolio should I pick With OptQuest you can choose the one that yields the greatest pro
20. Management Science Spreadsheet Engineering and Modeling Craft Hoboken NJ John Wiley 2003 Ragsdale C T Spreadsheet Modeling and Decision Analysis A Practical Introduction to Management Science 5th Ed Mason OH South Western College Publishing 2007 Thommes M C Proper Spreadsheet Design Boston Boyd and Fraser Publishing Co 1992 Optimization Topics Metaheuristics Glover F J P Kelly and M Laguna New Advances and Applications of Combining Simulation and Optimization Proceedings of the 1996 Winter Simulation Conference Edited by J 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 Handbook 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 Programming New York John Wiley and Sons 1994 Multiobjective Optimization Chankong V and Y Y Haimes Multiobjective Decision Making Theory and Methodology New York North Holland 1983 Hwang C and A S M Masud Multiple Objective Decision Making Methods and Applications Berlin Springer Verlag 1979 Keeney R and Raiffa H Decisions with Multiple Objectives New York John Wiley 1976 Financial Applicatio
21. OptQuest data 44 F feasibility constraint defined 19 requirement 19 feasible solutions 53 feature changes 24 files optimization name 36 financial applications references 130 flow chart OptQuest 15 forecast statistics defined 16 maximizing or minimizing 16 forecasts cells as objectives 16 defining 25 restricting statistics 17 selecting objective 26 statistics defined 113 Futura Apartments tutorial 49 138 Index MNO P Q R S TUVW G getting started 49 groundwater cleanup example 79 guidelines for using OptQuest 55 H help 10 hotel design and pricing example 72 how OptQuest works 14 how this manual is organized 9 l icons OptQuest 126 importing settings from opt files 46 initial values affecting performance 116 inventory system example 96 inventory systems references 131 K keyboard commands OptQuest 126 L Lean principles 21 limits OptQuest elements 116 linear models 114 M mathematical operations in constraints 32 maximizing forecast statistic 16 menus OptQuest 125 metaheuristics references 130 minimizing forecast statistic 16 mixed models 113 models 15 creating 24 deterministic illustrated 112 linear and nonlinear 114 optimization defined 15 setting up 23 A B C D E F GH N new features 24 nonlinear models 114 0 objectives complex 118 defined 58 selecting forecast 26 using forecasts as 16 oil field development example 8
22. SDS TEA ae STRAIN ANDOU RE ea Nir she Pace I SEER NES ir r 5 OptQuest is designed to find global solutions for all types of objectives especially complex objectives like this one However for more complex objectives you generally need to run more simulations to find high quality global solutions Simulation Speed By increasing the speed of each simulation you can increase the number of simulations that OptQuest runs in a given time period Some suggestions to increase speed are e Use Extreme Speed when practical 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 e Reduce the number of assumptions and forecasts e Quit other applications The Oracle Crystal Ball User s Guide discusses these suggestions in more detail Precision Control 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 simulation to achieve the necessary level of accuracy 118 Optimization Tips and Notes 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 Ifyou believe that the stability of the forecast statistics varies greatly depending o
23. Solution Analysis view OptQuest Results Edit View Analyze Preferences Help 701 Total Solutions 11 744 88 11 741 15 11 706 35 102 55 11972 8571428 281 58 12520 860 959011897 Show the best Include O 15 solutions 7 Feasible solutions 598 5 of solutions C Infeasible solutions 103 All feasible solutions 598 New best solutions 46 In the Show The Best group indicate whether to show a specific number or percentage of the best solutions or all solutions Your entry defines the analysis range For example if you want to examine the top 10 of all the solutions check __ of Solutions and enter 10 in the box You can choose whether to include feasible infeasible or all solutions If you have requested an Efficient Frontier analysis you can choose just the solutions for a particular efficient frontier test point When you make your choices statistics are calculated in the four rows at the bottom the minimum mean maximum and standard deviation values for all columns according to your display selections You can click the or at the left side of a column heading to condense sections and show more columns onscreen You can also click in the right side of a heading to sort that heading A small triangle is displayed You can click it to sort the column in ascending or descending order Efficient Frontier Chart If you have entered a variable
24. 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 or simulation limit Constraints 18 Overview Constraints are optional settings in an optimization model They 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 this 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 OptQuest considers only combinations of values for gasoline and service at or below 2 500 Not all optimization models need constraints Model and Solution Feasibility A feasible solution is one that satisfies all defined constraints and requirements A solution is infeasible when no combination of decision variable values can satisfy the entire set of requirements and constraints Note that a solution i e a single set of values for the decision variables can be infeasible by failing to satisfy the problem requirements or constraints but this doesn t imply that the problem or model itself is infeasible However constraints and requirements can be defined in such a way that the entire model is
25. Then you can repeat simulations using the same set of random numbers to accurately compare the simulation results If you do not set an initial seed value OptQuest will automatically pick a random seed and use that starting seed for each simulation that is run When your Crystal Ball forecast has extreme outliers run the optimization with several different seed values to test the solution s stability Speed tab Run in Extreme Speed if possible After you define the assumptions decision variables and forecasts in Crystal Ball you can begin the optimization process in OptQuest Developing a Crystal Ball Optimization Model 25 Starting OptQuest gt To start OptQuest 1 Choose Run OptQuest The OptQuest wizard starts 2 Set up the optimization by completing each wizard panel The first step of this process is selecting a forecast objective to optimize Note This version of OptQuest does not use opt files If you would like to retrieve settings from existing opt files for use in this version of OptQuest see Transferring Settings from opt Files on page 46 Selecting the Forecast Objective 26 When the OptQuest wizard starts the Objectives panel opens similar to Figure 12 The first time you start the wizard the Welcome screen opens Click Next to display the Objectives panel In the Objectives panel you choose a forecast statistic to maximize minimize or set to a target value Optionally you can
26. a simple conditional expression like Sheet A1 lt 100 For more information see the following section Specifying Constraints in Simple Entry Mode on page 29 If you move to Advanced Entry mode you can enter constraint formulas directly See Specifying Constraints in Advanced Entry Mode on page 29 Specifying Constraints in Simple Entry Mode When you click Next in the Decision Variables panel or click Constraints in the navigation list the Constraints panel opens similar to Figure 23 By default the Constraints panel opens in Simple Entry mode If you click Add Constraint you can reference cells with formulas for the left and right sides of the constraint formula and you can choose an operator Alternatively you can enter a value for the right side or left side For information about allowable constraint formulas see Constraint Rules and Syntax on page 32 For an example of using Simple Entry mode see Specifying Constraints on page 59 Specifying Constraints in Advanced Entry Mode To use the Constraints panel in Advanced Entry mode Switch to Advanced Entry mode by checking Advanced Entry in the upper right corner of the Constraints editor In the Constraints editor enter a mathematical formula You can use the buttons at the bottom of the Constraints panel to help you edit the formula For information on the Constraints editor syntax see Constraint Rules and Syntax on page 32 You can
27. also enter parts of a constraint formula into spreadsheet cells and then reference those cells separated by an operator in a formula See Constraints and Cell References in Advanced Entry Mode on page 33 Enter any additional constraints on their own lines When you are done click Next to display the Options panel Note You can copy and paste constraints to duplicate them for further editing Specifying Constraints 29 Advanced Entry Example To enter Advanced Entry mode check Advanced Entry in the upper right corner of the Constraints panel as shown in Figure 2 A Constraints edit box opens Figure 2 Entering Advanced Entry mode in the Constraints panel OptQuest Wel gi Optionally specify constraints on the decision variables Objectives Decision Variables Ce TEE Options __insert Variable _ Insert Reference Add Comme Efficient Frontier Net gt an At first the Constraints edit box is blank A series of buttons near the bottom of the dialog can help you create a formula in it You can enter a linear or nonlinear formula and you can enter any number of formulas as long as each constraint formula is on its own line For details see Constraints Editor and Related Buttons on page 31 In this case supposed you want to create a formula that adds all the decision variable values and specifies that they should be equal to 100 000 as discussed in Tutorial 2 Portfolio Alloc
28. an optimization model that contains only discrete variables distribution See probability distribution efficient frontier The curve that plots an objective value against changes to a requirement or constraint A typical use is for comparing portfolio returns against different risk levels 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 E0Q 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 is a 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 forecast objective One forecast from a model that OptQuest uses as the primary goal of the optimization OptQuest maximizes or minimizes a statistic of the forecast s distribution Glossary 133 forecast statistic Summary values of a forecast dist
29. are necessary for obtaining reasonable estimates for tail end percentiles Empirical testing with the simulation model using the Crystal Ball Bootstrap tool see the Oracle Crystal Ball User s Guide can help you find the appropriate number of trials for a given situation Number of Decision Variables The number of decision variables greatly affects OptQuest s performance OptQuest has no physical limit on the number of decision variables you can use in any given problem As the number of decision variables increases you need more simulations to find high quality solutions General guidelines for the minimum number of simulations required for a given number of decision variables in a problem are Decision variables Minimum number of simulations Fewer 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 running more 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 Recommended Number of OptQuest Elements For best results keep the number of OptQuest elements of each type below these limits e Decision variables lt 4 096 e Constraints lt 512
30. choose Analyze Forecast Charts and select Total Expected Return Figure 29 following shows the forecast chart and statistics in Split view Note that the standard deviation of the forecast is quite high 16 258 compared to the mean return of 10 387 The ratio of these two values the coefficient of variability is shown as 1 57 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 Tutorial 2 Portfolio Allocation Model 63 64 Figure 29 Portfolio allocation forecast chart Split view Forecast Total expected return Edit View Forecast Preferences Help 1 000 Trials Split View 995 Displayed Total expected retum Statistic Forecast value Precision Trials 1 000 Mean 10 387 BSA Median 10 579 10 97 Mode Standard Dev 16 258 454 Variance 264 312 461 Skewness 0 0390 Kurtosis 3 15 Coeff of Vari 1 57 Minimum 58 869 Maximum 64 173 Mean Std Err 514 0 04 ________ D a a o 2 a Aguenbeas4 Statistics shown in color are tested for 0 00 Editing the Optimization Settings 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 e
31. e Date Formatting on page 121 Array Formulas Array formulas with brackets are supported by Excel but are not allowed in OptQuest For example suppose you enter a constraint as follows referencing a named range MyRange gt 0 An error about an unrecognized range or variable name is displayed Date Formatting It is possible to reference a decision variable cell formatted as a date such as 2 19 1900 and enter a constraint as follows in the OptQuest wizard s Constraints panel E2 gt 2 19 1900 If you do this OptQuest interprets it as 2 divided by 19 divided by 1900 and does not display an error message Other OptQuest Notes 121 This behavior is consistent with that of the Excel formula bar For best results use the Excel DATE function Minor Limit Violations With Continuous Forecasts Slight violations of bounds can occur in requirements or constraints when evaluating small continuous forecast values If present these violations should be ignored since the differences are very small compared to the relative magnitude of the forecast values Solutions Still Ranked Even With No Feasible Solution If OptQuest fails to find a feasible solution the Solution Analysis table still ranks solutions in order from best to worst objective value even though none are feasible Referenced Assumption and Forecast Cells If a constraint formula references an assumption or forecast cell that cell is evaluated before each s
32. e Requirements lt 512 Base Case Values The base case values are the initial cell values listed in the Base Case column of the Decision Variables panel in the OptQuest wizard The base case values are important because the closer they are to the optimal value the faster OptQuest might find the optimal solution If the values are constraint infeasible they will be ignored For potentially large models with many decision variables you might find it helpful to first run a deterministic optimization to search for good base case values Then use the results as your 116 Optimization Tips and Notes base case 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 significantly improve OptQuest 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 d
33. e There are eight decision variables e There is one constraint representing the budget limitations Note the use of Excel s SUMPRODUCT function in the constraint to create a linear combination of the decision variables and investment amounts Run the optimization Figure 40 shows the results of an OptQuest optimization The best solution identified selects all the projects except for 3 4 and 8 Budget constrained Project Selection 77 Figure 40 Project selection model optimization results OptQuest Results DER Edit View Analyze Preferences a g FS 8 Total profit Final Value 6 N 8 65 87 109 131 153 175 197 Simulations Simulation 10 Value 2 496 595 Figure 41 the forecast chart for Total Profit shows that the distribution of profits is highly irregular and depends on the joint success rate of the chosen projects There is a risk of realizing aloss You might wish to evaluate the risks associated with some of the other solutions identified during the search Figure 41 Project selection solution forecast chart Forecast Total profit Edit View Forecast Preferences Help 500 Trials Frequency View Total profit 8 amp 8 AOuenbel4 Probability BE ee o o iS ee 8 t a i S o 78 Examples Using OptQuest Groundwater Cleanup This example concerns choosing a method for
34. each method Uniform based on how long the cleanup takes Efficiency D14 Percent of contaminants None that the cleanup process removes Each remediation method has a different cost for different efficiency levels Groundwater Cleanup OptQuest Solution Note Except where indicated this example uses the recommended Crystal Ball run preferences See Setting Crystal Ball Run Preferences on page 25 gt To run the optimization 1 Be sure Groundwater Cleanup xls is open in Crystal Ball 2 In Crystal Ball set the number of trials per simulation to 2000 since tail end percentile requirements need more accuracy 3 Start OptQuest As you click OK to step through the problem note e The objective is to minimize the remediation cost while requiring that the population risk be under 1E 4 with 95 certainty e There are two decision variables Remediation Method cell D13 and Cleanup Efficiency cell D14 Notice that the Category type was chosen for Remediation Method since it acts as an index variable for selecting one of the methods e This problem has no constraints 4 Run the optimization The results are shown in Figure 43 following The solution in Figure 43 minimizes costs at 10 909 while keeping the risk level at 9 99E 5 rounded Groundwater Cleanup 81 Figure 43 Groundwater cleanup optimization results OptQuest Results a 2 o Best solutions Infeasible solutions La
35. following sections describe this problem and two ways to solve it using OptQuest 86 Examples Using OptQuest e Portfolio Revisited Problem Statement on page 87 e Portfolio Revisited Method 1 Efficient Frontier Optimization on page 87 e Portfolio Revisited Method 2 Multi objective Optimization on page 89 Portfolio Revisited Problem Statement The investor from Tutorial 2 Portfolio Allocation Model on page 54 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 Table 11 Sample investment requirements Investment Annual return Lower bound Upper bound Money market fund 3 0 50 000 Income fund 5 10 000 25 000 Growth and income fund 7 0 80 000 Aggressive growth fund 11 10 000 100 000 When the investor maximized the portfolio return without regard to risk OptQuest allocated almost all the money to the investment with the highest return This 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 Efficient Frontier Analysis on page 19 examines the reasons for this Portfolio Revisited Method 1 Efficient Frontier Optimization OptQuest has a feature that creates an efficient frontier for you au
36. for obtaining optimal results These types of models are described here e Optimization Models Without Uncertainty on page 111 e Optimization Models With Uncertainty on page 112 e Discrete Continuous or Mixed Models on page 113 e Linear or Nonlinear Models on page 114 Optimization Models Without Uncertainty Conceptually an optimization model might look like Figure 67 Introduction 111 Figure 67 Schematic of an optimization model without uncertainty Constant gt N Constant Pa Ps amp Zr i Objective Decision Variable Decision Variable Decision Variable Model 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 Optimization Models With Uncertainty 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 probability distribution by simulating the model using Crystal Ball This type of model is called stochastic Figure 68 Sc
37. infeasible For example suppose that in the Portfolio Allocation problem in Chapter 1 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 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 Ifa model is constraint feasible OptQuest will always find a feasible solution and search for the optimal solution that is the best solution that satisfies all constraints 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 OptQuest 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 imp
38. optimal values generally requires that you search in an iterative or ad hoc fashion A more rigorous method systematically enumerates all possible alternatives 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 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 an optimization problem in OptQuest and then let it search for the values of decision variables that maximize or minimize a predefined objective In almost all cases OptQuest will efficiently find Introduction 13 an optimal or near optimal solution among large sets of possible alternatives even when exploring only a small fraction of them The easiest way to understand what OptQuest does is to apply it to a simple example Tutorial 1 Futura Apartments Model on page 49 demonstrates basic OptQuest operation How OptQuest Works 14 Overview Traditional search methods 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 solutions to complex problems involving
39. or a constraint are classified as nonlinear Figure 71 Comparison of linear and nonlinear relationships zZ Z Linear relationship Nonlinear relationship Number of personnel vs employee costs Population growth over time Crystal Ball can handle both linear and nonlinear objectives and constraints For information on defining linear or nonlinear constraints see Specifying Constraints on page 28 Factors That Affect Optimization Performance 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 more quickly Below is the performance graph for the two hypothetical methods 114 Optimization Tips and Notes Figure 72 Performance comparison a Method A a Method B k a b E 2 x Ww 4 5 6 7 Time Minutes Figure 72 shows that although both methods find solutions with a similar expected profit after 10 minutes of searching method A jumps to the range of high quality solutions faster than B For the criteria listed above method A performs better than method B While using OptQuest you will obtain performance profiles similar to method A OptQ
40. requirement for the optimization an Efficient Frontier tab opens with the Performance Chart tab in Best Solution view Figure 7 Running Optimizations 39 Figure 7 Efficient Frontier chart Best Solution view OptQuest Results Edit View Analyze Preferences Help 2000 Simulations Best Solution View Efficient Frontier Performance Chat Efficient Frontier lean 8 8 g 8 Efficient frontier Bestsolutions Infeasible solutions s 3 Total expected retum Me a 8 500 9 000 9 500 10 000 Total expected return Standard Deviation Best Solution View test point 10 000 Simulation 55 Vas Maximize the Mean of Total expected retum 8 657 hel The Standard Deviation of Total expected retum must be less than or e 9 975 Testing efficient frontier Constraints ttt Side Right Side Money Market fund Income fund Growth and Income fund Aggres 100 000 100 000 Aggressive Growth fund Growth and Income fund Money Market fund The Efficient Frontier window displays a plot of the objective value against the requirement or constraint that is being tested The best solution for each test point is displayed as a green diamond in the chart The table below the chart shows the best solution values for a specific test point You can choose which best solution to view by selecting the View Test Point drop down menu or by clicking the diamond symbol in
41. the chart For more information about Efficient Frontier analysis see Setting Up Efficient Frontier Analysis in OptQuest on page 46 Interpreting the Results After solving an optimization problem with OptQuest you can 1 View a solution analysis to determine the robustness of the results 2 Runa longer Crystal Ball simulation using the optimal values of 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 Viewing a Solution Analysis After the optimization is finished interpret your optimization results 1 Select View Solution Analysis in the OptQuest Results window 40 Setting Up and Optimizing a Model The Solution Analysis view opens with a partial listing of the solutions that OptQuest tried during the optimization The solutions are shown row wise in the upper grid with a smaller grid giving the statistics for each column Note that the OptQuest Results window has several menus you can use to copy results to your spreadsheet copy charts print results view other charts and more For alist of menu commands and their shortcut keys see Appendix B Accessibility Choose which solutions to view Below both grids are groups of controls you can use to filter the solutions to view All of the controls combine to filter the set of solutions Some controls show the number of solutions that will b
42. values of certain decision variables and optimize the rest Selecting Decision Variables to Optimize 27 By default all decision variables in all open workbooks are shown even those that are frozen in your model Frozen decision variables have a check in the Freeze column If you want you can uncheck them and include them in the optimization Be aware though that if you freeze or unfreeze a decision variable you are also changing it in your model OptQuest uses the limits base case start value and decision variable type you entered when you defined the decision variables If you check Show Cell Locations the following additional columns appear in the Decision Variables panel Cell Address Worksheet and Workbook To confirm and change selections Review the listed variables Check Freeze for any that you do not want to include in the OptQuest optimization Optionally change the lower and upper bounds base case or decision variable type for any listed decision variable Highlight the existing value and type over it This changes the decision variable definition in your worksheet Note the following about these settings e The tighter the bounds you specify the fewer values OptQuest must search to find the optimal solution However this efficiency comes at the expense of missing the optimal solution if it lies outside the specified bounds e Bydefault OptQuest uses the base case cell values in your Crystal Ball model as th
43. var2 lt 5 Exponents Use between a term and the exponential power var1 3 Notice that the above examples are for Advanced Entry mode In Simple Entry mode the expression on the left side of the operator would be entered into a spreadsheet cell The actual 32 Setting Up and Optimizing a Model formula in the Constraints panel would include a cell reference the operator and either a value or another cell reference For an example see Figure 26 Note Although these examples always show a formula on the left side of the operator you can actually have a formula or a cell reference to a formula in the spreadsheet on either the left or the right side You can also use Excel functions and range names in constraint formulas Ifyou are using Advanced Entry mode calculations occur according to the following precedence Multiplication and division occur first and then addition and subtraction For example 5 E6 10 F7 26 G4 means Multiply 5 times the value in cell E6 add that product to the product of 10 times the value in cell F7 and then subtract the product of 26 times the value in cell G4 from the result You can use parentheses to override precedence If you are using Simple Entry mode you are creating formulas in Excel and Excel s precedence rules apply Constraints and Cell References in Advanced Entry Mode Specifying Constraints in Simple Entry Mode on page 29 describes how you can create formu
44. 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 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 Thus in Figure 56 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 a MATCH function is used in column D to identify whether an order is scheduled to arrive Inventory System OptQuest Solution Note Except where indicated this example uses the recommended Crystal Ball run preferences See Setting Crystal Ball Run Preferences on page 25 With Inventory System xls open in Crystal Ball Start the OptQuest wizard As you click Next to step through the problem note e The objective is to minimize the total annual costs e There are no constraints or requirements e This problem has two decision variables e The initial search limits are set between 200 and 400 for both variables using a Discrete decision variable type e Th
45. when it is difficult to determine reasonable lower or upper bounds for requirement statistics This method is also recommended for situations where OptQuest has trouble finding feasible solutions that satisfy many requirements The latter method is generally easier to implement and understand Tolerance Analysis 92 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 following sections describe this problem and its OptQuest solution e Tolerance Analysis Problem Statement on page 93 e Tolerance Analysis Spreadsheet Model on page 93 e Tolerance Analysis OptQuest Solution on page 95 Examples Using OptQuest Note This example involves concepts used only by Six Sigma and similar quality programs If you are not familiar with Crystal Ball s process capability features consider reviewing the process capability appendix in the Oracle Crystal Ball User s Guide Tolerance Analysis Problem Statement 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 assembly gap mu
46. 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 is not 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 Note For large values of the rate parameter A 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 distribution is discrete thus eliminating the need to round off normally distributed random variates 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 Thetime between 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 EOQ model suggests an order quantity gq 2 5200x 50_ 5 10 4 For the EOQ policy the reorder point should equal the l
47. 1 OptQuest flow Determine a new set of values for decision variables Generate random Calculate numbers for entire assumption spreadsheet cells Display results ina forecast chart Add new best result to OptQuest Results window About Optimization Models In today s competitive global economy people are faced with many difficult decisions Such decisions might involve thousands or millions of potential alternatives A model can provide valuable assistance in analyzing decisions and finding good solutions Models 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 OptQuest optimization model has four major elements an objective optional requirements Crystal Ball decision variables and optional constraints e Optimization Objectives Elements that represents the target goal of the optimization such as maximizing profit or minimizing cost based on a forecast and related decision variables e Requirements Optional restrictions placed on forecast statistics All requirements must be satisfied before a solution can be considered feasible e Decision Variables Variables over which you have control for example the amount of product to make the number of dollars to allocate among different investments o
48. 23 Constraints panel with no data Simple Entry mode OptQuest g AS Optionally specify constraints on the decision variables Objectives Decision Variables Options Efficient Frontier Tutorial 2 Portfolio Allocation Model 59 60 Optionally use the Constraints panel to specify any restrictions that you can define with the decision variables The constraint in this model limits the initial investment to 100 000 By default the Constraints panel opens in Simple Entry mode In this mode most of the constraint formula is entered into cells in your spreadsheet You then complete the constraint formula on the Constraints panel using a simple conditional expression like Sheet A1 lt 100 For example consider the constraint formula given previously as an example Money Market fund Income fund Growth and Income fund Aggressive Growth fund 100000 Each of the fund values is defined in Crystal Ball as a decision variable In this example these decision variables are are defined in cells C13 through C16 as shown in Figure 22 The left hand side of the constraint formula shown above is already entered into cell G13 of the Model worksheet of the Portfolio Allocation example SUM C13 C16 To enter this into the Constraints panel Click Add Constraint A row with two edit boxes is displayed as shown in Figure 24 following Figure 24 Constraints editor in Simple Entry mode Constraints
49. 3 online help 10 operations mathematical in constraints 32 optimization performance 114 optimization process overview 23 optimization topics references 130 optimizations deterministic model illustrated 112 model defined 15 running 35 starting and stopping 36 options selecting 34 OptQuest flow 15 how it works 14 keyboard commands and icons 126 options 34 steps to use 23 what it does 13 OptQuest element limits 116 OptQuest menus 125 OptQuest results printing 42 OptQuest guidelines for using 55 OptQuest starting 26 organization manual 9 P pause command 36 performance factors bounds and constraints 117 complex objectives 118 initial values 116 number of decision variables 116 requirements 117 K L M N O P Q R S T U V W simulation speed 118 performance affecting factors 114 performance optimization 114 petrochemical engineering references 131 Portfolio Allocation tutorial 54 portfolio revisited example 86 portfolios efficient 20 preferences suggested run 25 printing OptQuest results 42 printing results 42 process capability 21 process optimization 23 product mix example 69 project selection example 75 Q quality programs 21 R ranges decision variable 17 references financial applications 130 inventory systems 131 metaheuristics 130 on the web 129 optimization topics 130 petrochemical engineering 131 spreadsheet design
50. 4 Description Sheet Model lt Net present value cell C30 of this oil field is based on e Total discounted reserves cell C27 e Oil margin cell C13 which is equivalent to oil price minus operating costs 84 Examples Using OptQuest e Well costs cell C28 e Facilities cost cell C29 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 C7 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 NPV Oil Field Development OptQuest Solution Note Except where indicated this example uses the recommended Crystal Ball run preferences See Setting Crystal Ball Run Preferences on page 25 Be sure Oil Field Development xls is open in Crystal Ball Then Start the OptQuest wizard As you click OK to step through the problem note e The objective is to maximize the 10th percentile P90 of the NPV e Thereare three decision va
51. 7 Figure 63 Gasoline supply chain spreadsheet model Gasoline Supply Chain xls BEE Inventory Costs 238 00 Transportation Costs 39 887 50 Total Costs Worst case Stockout 0 114 00 24 562 50 M 4 Description Model Gasoline Supply Chain OptQuest Solution Note Except where indicated this example uses the recommended Crystal Ball run preferences See Setting Crystal Ball Run Preferences on page 25 gt With Gasoline Supply Chain xls open in Crystal Ball 1 Start the OptQuest wizard As you click Next to step through the problem note The objective is to minimize the mean of total costs The problem has one requirement the 95th percentile of the worst case stockout forecast must be less than 0 gallons This problem has eight discrete decision variables with bounds of 0 to 2000 These represent transportation costs among the various elements of the supply chain The problem has two constraints shown in Figure 64 These constraints specify that both links of the supply chain running through SD1 and SD2 must have sufficient inventory of gasoline 108 Examples Using OptQuest Figure 64 Gasoline supply chain constraints 2 Supply Depot 2 must have sufficient inventory of gasoline Linear o Model l13 Refinery to SD2 SD2to RO1 SD2to RO2 SD2to RO3 gt 0 2 Run the optimization Figure 65 shows sample OptQuest results Figure 65 Gasoline supply chain model o
52. 8 Best solutions Infeasible solutions Lastbestsolution 8 Total expected retum Mean 8 g 3 T T T T T T T T T i 90 180 270 360 450 540 630 720 810 900 990 Simulations Aggressive Growth fund Growth and Income fund Income fund As shown in Figure 31 after several hundred simulations OptQuest found a solution that meets the requirement well The standard deviation of Total Expected Return is just below 8 000 The objective value though is now significantly lower than the previous solution without the limit on risk Figure 27 If you return to the Portfolio Allocation model and display the resulting forecast chart in Split View Figure 32 you can see that the new values appear The standard deviation of Total Expected Return is just under 8 000 and the coefficient of variability is slightly greater than 1 Tutorial 2 Portfolio Allocation Model 65 66 Figure 32 Best optimization solution with lower risk requirement Forecast Total expected return Edit View Forecast Preferences Help 1 000 Trials Split View 995 Displayed Total expected retum istic Forecastvalues Precision o 9 o 8 Standard Dev Variance Skewness Kurtosis Coeff of Vari Minimum Mean 7 680 Maximum Mean Std Err Probability a o e 8 amp Aouanbel4 o Statistics shown in color are tested for 0 00 pre Interpreting Results This sol
53. 9 Graphical OptQuest results in a custom report OptQuest Results Primary workbook Groundwater Cleanup xls Summary Atter 229 solutions were evaluated in seconds the Mean of Total Remediation Cost was improved from 9 000 to 10 894 a change of 21 04 Performance Chart 11 600 5 i 11 200 f A 10 600 H 5 10 400 i Best solutions i infeasible solutons i soa Last best solution 9600 i s920 J 8 800 22 45 6 9 114 137 160 183 206 229 Simulations Objectives Best Solution Minimize the Mean of Total Remediation Cost 10 894 Cell D3 Requirements The 95 Percentile of Population Risk must be less than 1 00E 04 9 96E 05 Cell C25 Decision variables Best Solution Cleanup Efficiency 91 Cell D14 Remediation Method 3 00 Cell D13 End of OptQuest Results 0 4 Extracting OptQuest Data To extract several types of OptQuest data to worksheet cells for further analysis 1 Run an optimization and choose Analyze Extract Data The Extract Data Preferences dialog opens By default the OptQuest Data tab is selected 2 Choose whether to extract OptQuest solutions OptQuest statistics or both and then indicate whether to extract them for all decision variables or only the ones you choose 3 Optionally click the Simulation Data tab to extract simulation data for the best solution only similar to that described in the Oracle Crystal Ball User s Guide 4 Optionally click the Options tab to indica
54. 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 All these factors lead to the following equation for population risk cancer e contaminant e water consumed population _ potencies concentrations per day risk body weight conversion factor Groundwater Cleanup Spreadsheet Model Open the file Groundwater Cleanup xls Figure 42 Groundwater Cleanup 79 Figure 42 Groundwater cleanup spreadsheet model B Cc D E F G gt Groundwater Cleanup at a Toxic Waste Site 16 Cancer 17 Contaminant 18 Tetrachloroethylene 19 Trichloroethylene 20 Vinyl Chloride 21 22 Body Weight kilograms 23 Volume of Water per Day liters day 24 i25 Population Risk 118E0 26 Bi M 4 gt rN Description Model 2 3 Total Remediation Cost S9000 _ Minimize Cleanup Costs _ 4 5 Remediation Fixed Variable Remediation 6 Method Costs Costs
55. IGHTS Programs software databases and related documentation and technical data delivered to U S Government customers are commercial computer software or commercial technical data pursuant to the applicable Federal Acquisition Regulation and agency specific supplemental regulations As such use duplication disclosure modification and adaptation of the Programs including documentation and technical data shall be subject to the licensing restrictions set forth in the applicable Oracle license agreement and to the extent applicable the additional rights set forth in FAR 52 227 19 Commercial Computer Software Restricted Rights June 1987 Oracle USA Inc 500 Oracle Parkway Redwood City CA 94065 The Programs are not intended for use in any nuclear aviation mass transit medical or other inherently dangerous applications It shall be the licensee s responsibility to take all appropriate fail safe backup redundancy and other measures to ensure the safe use of such applications if the Programs are used for such purposes and we disclaim liability for any damages caused by such use of the Programs Oracle JD Edwards PeopleSoft and Siebel are registered trademarks of Oracle Corporation and or its affiliates Other names may be trademarks of their respective owners The Programs may provide links to Web sites and access to content products and services from third parties Oracle is not responsible for the availability of or any con
56. a drilling depth coefficient 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 However 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 a normal distribution with a mean of 300 and a standard deviation of 20 102 Examples Using OptQuest e The drill bit replacement time R varies and is determined by a triangular distribution 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 days in 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 100 s60 c aa 425T 8000 S 400R average profit _ 10 month T R The objective is to find the value of T that maxi
57. ail outlets RO A weekly snapshot of this supply chain is as follows e The refinery produces a variable amount of gasoline every week which it transports to SDs for cross docking e SDs supply gasoline to ROs which realize stochastic demand from end customers e All three supply chain levels Refinery SD and RO face inventory holding costs e In addition the RO s face the risk of stockouts for not fulfilling customer demands The problem is to determine the amount of gasoline to transport between each level of the supply chain to minimize the total operating cost which is computed as the sum of transportation costs and inventory holding costs For business reasons it is helpful to minimize stockouts at the ROs to a certain extent The following is a schematic diagram of the supply chain SD1 SD2 sot SD2 Supply Assumptions about the supply chain are as follows e The weekly supply of gasoline from the refinery SP follows a normal distribution with a mean of 2000 gallons and standard deviation s d of 450 gallons 106 Examples Using OptQuest The weekly demands at ROs are distributed lognormally with means and standard deviations of 400 gallons and 50 gallons 500 gallons and 75 gallons 650 gallons and 100 gallons respectively at ROI RO2 RO3 The inventory holding cost is a dollar for every five gallons The transportation costs in dollars per gallon are as follows note that these costs include tra
58. ained Project Selection E E E TTT E E E EE 75 Project Selection Problem Statement ee or need oe sret i er ee Project Selection Spreadsheet Model oii ivi seers ceegewienenceseun T Project Selection OptQuest Solution c4i15464eeeteedsg oenhsed pe iikot iia ia Groundwater Cleanup TE T piwi TE Te SES TEE TEER er ee s279 Groundwater Cleanup Problem Statement sis csreisrecsiissiniersirens erry Groundwater Cleanup Spreadsheet Model 5 5 05 0 6s50cssekeuaaseuwens T Groundwater Cleanup OptQuest Solution 0 CONRES TT 81 Oil Fed ieee NENG 5 6 05 0 hans one t a e ea aa A Oil Fidd Development Problem Statement cs ok wees yaw ieee evdeneneseas igang ee Oil Field Development Spreadsheet Model TASE IEEE T ET 84 Oil Field Development OptQuest Solution phat ered ete oe ver ees eee Poolo oo a ee see nr re rer rere O Portfolio Revisited Problem Statement 6c acdc aneeyaedscennaak eo TESES eee Portfolio Revisited Method 1 Efficient Frontier Optimization 87 Portfolio Revisited Method 2 Multi objective Optimization 89 Tolerance Analysis 05 lt EE EATE ELE EEEE TEA S EEE T EEE Tolerance Analysis Problem Statement 4c1s244924iee4s 00402040 eaiseeess 93 Tolerance Analysis Spreadsheet Model be sees wows er Te sri Seed aa 3D Tolerance Analysis OptQuest Solution i 24 444608640400 s dae ease eau ens ieee Inventory System Optimization 1 lt scacevavs vans iuan
59. alysis The Solution Analysis view is useful for determining the sensitivity of decision variables with respect to the model s objective When viewing the best solutions for an optimization compare the relative size of the ranges for each of your decision variables Generally speaking a decision variable with a smaller relative range indicates that it has a greater impact on the objective This is because small changes in the decision variable can force the solutions to be less than optimal Conversely a decision variable with a wider relative range indicates that is has a lesser impact on the objective since different values do not seem to alter the set of best solutions These are general guidelines only The results for your situation can be affected by the type and length of the optimization the initial bounds defined for the decision variables and other factors Interpreting the Results 41 42 PBwoNe VY w ne VY 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 If you did not choose to automatically copy OptQuest results to the model workbook set in the Options panel you can choose Edit Copy Best Solution to Spreadsheet in the OptQuest Results window OptQuest copies the decision variables values from the selected solution into the Excel model In Crystal Ball reset the optimization sel
60. amount available Decision variables Money Market fund Income fund Growth and Income fund Aggressive Growth fund Total expected return Risk aversion constant Mean Standard Deviation Mean minis Side 24 M 4 gt rih Description Model Upper 50 000 25 000 80 000 10 000 100 000 i 100 000 Limit 100 000 Amount invested _ Total weighted risk i 42 500 invested 100 000 Constraints Obiective 0 50 Extracted Mean value from Total expected return sro 6 988 Extracted StdDev value from Total expected return Multiobjective z gt l This new function cell C22 contains the multi objective relationship described by mean return k standard deviation with the risk aversion constant cell C19 broken out into a separate cell The mean return and standard deviation variables in this equation are automatically extracted at the end of the simulation from the Total Expected Return forecast cell C17 See the Oracle Crystal Ball User s Guide for more information on the Auto Extract feature Multi objective Optimization OptQuest Solution To follow this example 1 Open Portfolio Revisited xls in Crystal Ball 90 Examples Using OptQuest This example uses the Crystal Ball run preferences recommended in Setting Crystal Ball Run Preferences on page 25 Start the OptQuest wizard As you click OK to step through the problem note e The objective refers to the new mul
61. an of gross profit e The only requirement ensures that at most a 5 chance exists of exceeding the casing limitation e This problem has five decision variables one for each product and three constraints one each for availability of veal pork and beef 3 Run the optimization Figure 34 shows the OptQuest solution The optimal mean profit is 11 780 33 obtained by producing 2710 pounds of bratwurst 1460 pounds of Italian sausage 1040 pounds of pepperoni 1200 pounds of Polish sausage and 660 pounds of summer sausage 70 Examples Using OptQuest Figure 34 Product mix model optimization results OptQuest Results DEAR Edit View Analyze Preferences Help 741 Simulations Best Solution View Performance Chart Best solutions Infeasible solutions Last best solution T 74 149 224 299 374 449 524 599 67 Simulations A Best Solution Simulation 241 Objectives Value Maximize the Mean of Gross Profit 11 780 33 Requirements The 5 Percentile of Casing Remaining must be greater than or equal t Constraints Veal used must not exceed inventory Pork used must not exceed inventor italian Sausage Pepperoni Polish Sausage Figure 35 shows the Casing Remaining forecast chart for these decision variables verifying that the chance of running out of casing is indeed at most 5 Figure 35 Product mix remaining c
62. arket fund Income fund Growth and Income fund Aggres 100 000 100 000 Decision Variables Value Aggressive Growth fund Growth and Income fund Income fund Money Market fund 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 Portfolio Revisited Method 2 Multi objective Optimization Another technique for finding efficient portfolios is called multi objective or multi criteria optimization You can use this technique to optimize multiple often conflicting objectives such as maximizing returns and minimizing risks simultaneously Other examples of multi objective optimization include e Aircraft design requiring simultaneous optimization of weight payload capacity airframe stiffness and fuel efficiency 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 Most forms of multi objective 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 where k gt 0 is a risk avers
63. 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 OptQuest models You define decision variables in Crystal Ball using Define Define Decision or by clicking the Define Decision button in the toolbar or Excel 2007 ribbon When you define a decision variable in Crystal Ball you define its e Bounds Defines the upper and lower limits for the variable OptQuest searches for solutions for the decision variable only within these limits e Type Defines whether the variable type is discrete continuous binary category or custom o Continuous A variable that can be fractional that is it is not required to be an integer and can take on any value between its lower and upper bounds no step size is required and any given range contains an infinite number of possible values o Discrete A variable that can only assume values equal to its lower bound plus a multiple of its step size a step size is any number greater than zero but less than the variable s range Decision Variables 17 o Binary A decision variable that can be is 0 or 1 to represent a yes no decision where 0 no and 1 yes o Category A decision variable for representing attributes and indexes can assume any discrete integer between the lower and upper bounds inclusive where the order or direction of the values does not matter nominal The bounds mu
64. as maximizing returns and minimizing risks into one objective nonlinear A mathematical relationship where one or more terms in the formulas are nonlinear Terms such as x2 xy 1 x or 3 1x make nonlinear relationships See linear NPV Net Present Value The NPV equals the present value minus the initial investment objective A forecast 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 the objective 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 below a value default definition 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 random number A mathematically selected value which is generated by a formula or selected froma table to conform to a probability distribution random number generator A method implemented in a computer program that is capab
65. asing forecast chart Forecast Casing Remaining Edit View Forecast Preferences Help 2 000 Trials Split View Casing Remaining Probability Product Mix 71 Hotel Design and Pricing Problem A downtown hotel is considering a major remodeling effort and needs to determine the best combination of rates and room sizes to maximize revenues The following sections describe this problem and its OptQuest solution e Hotel Design Problem Statement on page 72 e Hotel Design Spreadsheet Model on page 73 e Hotel Design OptQuest Solution on page 73 Hotel Design Problem Statement Currently the hotel has 450 rooms with the following history Table 6 Hotel example data summary Room Type Rate Daily Avg No Sold 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 the 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 E H N C C rooms sold H where variables are Variable Description H Historical average number of room
66. ates 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 Product Mix 69 Product Mix Spreadsheet Model The Product Mix xls file shown in Figure 33 is a spreadsheet model for this problem The input data and model outputs are straightforward Figure 33 Product mix problem spreadsheet model Ga Summer Sausage Bratwurst Italian Sausage Pepperoni Polish Sausage Veal 12 520 00 Inventory On Hand Used Remaining Gross Profit Pork Beef Casing Pork 14 100 00 Beef Casing 6 480 00 10 800 00 Product Mix OptQuest Solution Profit per Unit Quantity to Produce 5 000 00 11 500 00 5500 00 7 000 00 a Note Except where indicated this example uses the recommended Crystal Ball run preferences See Setting Crystal Ball Run Preferences on page 25 To run the optimization 1 With Product Mix xls open in Crystal Ball set the number of trials in Crystal Ball to 2000 since tail end percentile requirements need more accuracy 2 Start OptQuest from the Crystal Ball Run menu and click Next to view each wizard panel e The objective is to maximize the me
67. ation Model on page 54 Constraints Editor Example gt To create this formula 1 Click Insert Variable The Insert Variable dialog opens 30 Setting Up and Optimizing a Model Figure 3 Insert Variable dialog Portfolio Allocation model Insert Variable Choose one or more decision variables to insert into the constraint View fl E Select m m 1G Portfolio Allocation xis 3 0 Model a Via Aggressive Growth fund Growth and Income fund Income fund Money Market fund Hel 2 Since you want to include all four decision variables in the constraint formula put a check in front of each name To check all four at once check the box in front of Decision Variables Then click OK The variables appear in the edit box as a sum Constraints Type Exclude 1 Aggressive Growth fund Growth and Income fund Income fund Money Market fund F After Money Market fund type an equals sign 4 Enter the total investment as 100 000 without the dollar sign or comma so that the final constraint looks like Money market fund Income fund Growth and income fund Aggressive growth fund 100000 Note Don t use or a comma in constraints See Constraint Rules and Syntax on page 32 for other rules about constraint formulas 5 Click Next to continue The Options panel opens similar to Constraint Rules and Syntax on page 32 Constraints Editor an
68. bei er oie ae Specifying Constraints in Advanced Entry Mode 0 ee eee eee ene 29 Constraints Editor and Related Buttons ss issisecciorcredi tesis adito Kres 31 Constraint Rules and Sas gas caedediroee den st Gessad areas eax dy oawads 4 32 Constraints and Cell References in Advanced Entry Mode sear srat ni E Constat Types bower Senos wee deee 5 pesewpiewe eeeeneete genous seyes 34 Seting PUGS ish 4esteerdaetews LiiGxGReeh heh euetsadl TT TE 34 Advanced Options sea cbwi EET ee 25 eee ere cee een oe Rumning Pe oh 2 hw 8 Ae a e a HAR k Ee oe dees lt 28 OptQuest Control Panel Buttons and Commands 0 ee eee eee 36 OptQuest Results Window seca picts PE EER EE ls tok tetas te 36 Iniierpr lins he Bs oes sgt poe eg does 44 Ge oes ewes eden eee ea aie ge ONION AI ook eee ae aghe tee ekgae soagsegussyeeneeesewes 40 Running a Longer Simulation of the Results 24 5244 24 02 4 s00so400ei000s 42 Printing OptQuest Results vor eres ebay bu ghee ered ghee bur ver re Sewn Chn a Cosa Ball gee on eine hi ee de ss kdes bos iedreen ct Creating OptQuest Reports cLpie een gabe Hose dads MRR RARE REETA 42 Extracting OptQuest Data nea er ee or aes er dee er 44 Saving optimization models and Settings ac gg esg sed dea geeeeess geese geesersas 49 CORE COPE koa cheselwtokens eaeieeuse dus bersene sabes wees acesenes 45 Setting Up Efficient Frontier Analysis in OptQuest 2 c0cisedieecesdcdees
69. bility metrics for forecasts when the process capability features are activated and at least one specification limit LSL or USL is entered for the forecasts OptQuest then includes these metrics in the list of statistics that can be optimized For more information see OptQuest and Process Capability on page 21 This example assumes that the process capability metrics have been activated in Crystal Ball Then the capability metrics are available in the Forecast Statistic list of the Objectives panel Tolerance Analysis Spreadsheet Model Open the Tolerance Analysis xls file Figure 53 Tolerance Analysis 93 Figure 53 Tolerance analysis spreadsheet model F27 Ka f F19 F25 Cost based Stack Tolerance Analysis Dimensions Piston assembly Cylinder assembly Choose tolerances levels gt Assembly gap that meet the assembly gap design criteria Minimum gap LSL 0 0030 in Maximum gap USL 0 0200 in Piston Nominal Initial Quality Component Component Statistical Assembly Dimension _ Tolerance Specification Cost Sigma Dimension Piston Piston bearing Rod Rod bearing Crankshaft Piston assembly cost Cylinder Nominal Initial Quality Component Statistical Assembly Dimension _ Tolerance Specification Cost i y Dimension Cylinder wall 8 50 in 1 i 9 45 y i normal Cylinder head depth 0 71 in 1 i 6 55 1 i normal Cylinder assembly cost 16 00 Total assembly cost s Notes 1 Initial to
70. budget constraint 21 22 v M 4 gt rN Description Model lt gt 76 Examples Using OptQuest 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 simulated 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 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 Thus incorporating risk analysis within the context of the optimization is a very useful approach Project Selection OptQuest Solution With Project Selection xls open in Crystal Ball start OptQuest from the Crystal Ball Run menu Then Start the OptQuest wizard As you click Next to step through the problem note that e The objective is to maximize the final value of total profit e There are no requirements
71. c optimization example 68 Examples Using OptQuest Note Most of the examples included here use one of the Advanced Options settings for automatically stopping the optimization when either a solution confidence level or certain number of non improving solutions is reached If you follow along with these examples your results should be similar but may not always be identical Product Mix The following sections describe this problem and its OptQuest solution e Product Mix Problem Statement on page 69 e Product Mix Spreadsheet Model on page 70 e Product Mix OptQuest Solution on page 70 Product Mix Problem Statement Ray s Red Hots 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 Table 5 Ray s Red Hots data summary 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 Sausage 1 00 3 00 1 50 1 00 1 40 Pepperoni 0 00 4 00 0 00 2 00 2 10 Polish Sausage 0 00 1 00 3 00 1 50 1 70 Limited amounts of ingredients are 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 Complicating this situation is e The unit profits are only estim
72. cast 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 decision variables e 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 Figure 20 for an example Note that all assumptions are in rows 5 through 8 Rows 13 through 16 are reserved for decision variables created by users during the OptQuest tutorials Forecast cells reference these input variable cells in 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 comma formats appropriately Setting Up and Optimizing a Model
73. ce interval e The second setting Automatically Stop After___ Non improving Solutions stops the active simulation if the specified number of solutions are calculated without generating a new best solution The default setting is unchecked off with a value of 500 Running Optimizations To run an optimization click Run at the bottom of any OptQuest wizard panel Once the optimization starts you can use buttons in the Control Panel to stop pause continue or restart at any time You cannot 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 Running Optimizations 35 36 OptQuest Control Panel Buttons and Commands You can use the buttons and commands on the OptQuest Control Panel for starting and stopping an optimization Figure 4 Figure 4 OptQuest Control Panel Control Panel OptQuest Run Analyze Help Running optimization Extreme speed CCC Total simulations 645 LELLLI Trials 1 000 gt E4 i The Control Panel menus are the same as the Crystal Ball Run and Analyze menus The Help menu describes the Control Panel The following buttons are available Action Button Description Run Preferences ee Opens a dialog for controlling optimizations Start or Continue gt Starts a new optimization or continues a paused optimizat
74. cleaning up groundwater contamination The following sections describe this problem and its OptQuest solution e Groundwater Cleanup Problem Statement on page 79 e Groundwater Cleanup Spreadsheet Model on page 79 e Groundwater Cleanup OptQuest Solution on page 81 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 below a 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 standards with a 95 certainty
75. cted value greater than or equal to a selected value or between two selected values including the values Click the underlined limit to choose another If you choose Between an additional target value is displayed e Finally review and adjust the target value or values To change a value click it and then type a new number over it f You can repeat steps3a through 3e to add additional requirements New requirements are duplicates of the last one entered g To delete a requirement click it and then click Delete If you want to set variable bounds for Efficient Frontier analysis select a variable and click Efficient Frontier For details see Efficient Frontier Analysis on page 19 4 When objective and requirement settings are complete click Next The Decision Variables panel opens Note You can create multiple requirements without using all of them at once If you check the Exclude box that requirement is not used in the current OptQuest simulation Selecting Decision Variables to Optimize When you click Next in the Objectives panel the Decision Variables panel opens similar to Figure 22 It lists every decision variable frozen or not defined in all open Excel workbooks The next step of the optimization process is selecting decision variables to optimize The value of each decision variable changes with each simulation until OptQuest finds values that yield the best objective For some analyses you might fix the
76. d Related Buttons The upper part of the Constraints panel is the Constraints editor The lower part of the Constraints panel contains buttons that perform the following tasks in Advanced Entry mode Button Description Insert Variable Lists all available decision variables you can insert If you choose more than one they are automatically added to the Constraints editor with plus signs between them Insert Displays the Cell Reference dialog where you can either point to a cell or enter a formula to include Reference in the constraint formula you are creating For more information see Constraints and Cell References in Advanced Entry Mode on page 33 Add Comment Displays the Add Comment dialog where you can enter a comment that describes the constraint The comment is displayed in the Constraints panel above the constraint It also is displayed in the OptQuest Results window to identify the constraint Specifying Constraints 31 Button Description Efficient Adds a requirement with a variable upper or lower bound for use in Efficient Frontier analysis For Frontier more information see Efficient Frontier Analysis on page 19 If you have already added a variable requirement on the Objectives panel a message is displayed and asks if you want to use the new one instead Delete Deletes the currently selected constraint To add a variable or a reference to a constraint place your cursor where y
77. ddition to the recommended options before running OptQuest in Crystal Ball select Run Run Preferences and set e The maximum number of trials run to 2000 e The sampling method to Latin Hypercube e The sample size to 2000 for Latin Hypercube 94 Examples Using OptQuest 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 5000 or 10 000 trials Tolerance Analysis OptQuest Solution The goal of the following solution is to maximize quality while minimizing cost Note Except where indicated this example uses the recommended Crystal Ball run preferences See Setting Crystal Ball Run Preferences on page 25 To run the optimization Be sure Tolerance Analysis xls is open in Crystal Ball and the maximum trials and sample sizes have been set to 2000 as described above Start OptQuest As you click Next to step through the problem note e The objective is to minimize the mean of total assembly cost Note 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 e Two requirements ensure that the assembly gap is between 0 003 and 0 02 inches e This problem has seven decision variables one f
78. define one or more requirements either on the objective forecast or on other forecasts Figure 21 shows a default objective including the first forecast found in the model If you have questions about settings click the Help button Note Each optimization must include one and only one objective However you can define several objectives and exclude those not used in the current optimization To exclude an objective check the box in the Exclude column for that objective To define a forecast objective and optionally define requirements Click Add Objective A default objective is displayed in the Objectives area 2 Review the default objective definition It has the format Operation gt Statistic gt Forecast a First if the model has more than one forecast does the default objective include the same forecast you want to include in the objective If not click the underlined forecast and replace it with your selection If more than ten forecasts are available More Forecasts is displayed at the bottom of the list You can select it to display a forecast selection dialog b Next do you want to maximize a statistic for that forecast Ifyou would prefer to minimize the statistic or set it to a target value click the underlined operation and choose an alternative c Finally is the underlined statistic the one you prefer to use If not click it and choose a different one If you have activated Crystal Ball s process capability f
79. e included in parenthesis e Inthe leftmost group choose to view only the top number or percentage of best solutions highest or lowest objective values all of the solutions or only the new best solutions corresponding to up or down jumps in the performance chart e Inthecenter group choose whether to include feasible infeasible or both types of solutions e Ifyou have requested an efficient frontier analysis choose a test point from the dropdown menu in the rightmost group Note that all of the solutions are considered for a particular test point even if they were evaluated at a previous or later test point in the optimization Once you have chosen a set of solutions to analyze you can click on the right side of a column heading to sort the solution by that heading The small triangle indicates the direction of the sort order You can also click the or symbol at the left side of a group of columns to condense or expand the amount of information displayed Bounds Analysis The Solution Analysis view is useful for determining how restrictive the bounds are for requirements or constraints especially when there are multiple bounds involved When viewing the best solutions for an optimization if most of the values for a requirement or constraint are at or near a specific bound this indicates that the requirement or constraint is having a significant effect on the values that are obtainable for the objective Sensitivity An
80. e pE EA 116 Base Case Values Pere eee PE re nae aus Sane ous eh ais Beto gras 116 Bounds sae Comsat ah weed ERA EH RR ed 117 Requirements oncci weeawee Pas tity das dole eit de Puc Secs dak font EEA 117 Complexity of the Objective EEE Kas Bi oc ee Oh ah i ah Ac ere 118 PAM SERS 64 deg we pene thks bea E E gan eeenawad i Wee oe Ya owen ee case ace Sees ade cease owes E 118 Sensitivity Analysis Using Tornado Chat gnc ucvidgseueiseeeeeeess kpi ade ce 119 Maintaining Multiple Optimization Settings fora Model TE e pees gene oe 120 Ciner Dptlmest NOMS i8ith ons Pied hoa Ree oe SE Ee ede dee edes es 121 Automatic Resets or Optimizations lt u s cic ek ends Hoe K Wadd DOR RRRER TORRES 121 Constraint Formula Limitations ar eis ee ee TET eee pe oa ax 121 Minor Limit Violations With Continuous Forecasts 0000000ee ees 122 Solutions Still Ranked Even With No Feasible Solution 000 eeu 122 Decision Variables and Ranges With the Same Name er ver er see ew M22 Linear Constraints Can Be Evaluated As Nonlinear 000000 222 122 Appendix B Accessibility dsrs TEE sdt t si eee wees ees eee Cb ag mess er need eae ee 123 Productions descended eee eens ates EELEE TT etnias RNEER OSERE SKEN 123 Accessibility Notes 5 4 40 2 564504 ib ohne sex E EE A A ET I EEEE 123 Accessibility of Code Examples in Documentation ea Seba rid sat 124 Accessibility of Links to External Web Sites i
81. e suggested starting solution If the suggested values lie outside of the specified bounds or do not meet the problem constraints OptQuest ignores them Note You can sort decision variables in the Decision Variables panel by name type freeze status cell address worksheet or workbook To sort click the column heading An arrow is displayed at the right end of the column to show the direction of the sort The sort column and direction of the decision variables is stored as a global preference and is also used to set the order of the decision variables in the reports and extracted data 3 When your decision variable selections are complete click Next The Constraints panel opens Specifying Constraints 28 In OptQuest constraints limit the possible solutions to a model in terms of relationships among the decision variables You can use the Constraints panel to specify linear and nonlinear constraints For example in Tutorial 2 Portfolio Allocation Model on page 54 the total investment was limited to 100 000 In the Constraints panel this limit is expressed by the formula Money Market fund Income fund Growth and Income fund Aggressive Growth fund 100000 Setting Up and Optimizing a Model By default the Constraints panel opens in Simple Entry mode In this mode most of the constraint formula is entered into cells in your spreadsheet You then complete the constraint formula on the Constraints panel using
82. e the objective You must also select whether to maximize or minimize the objective or set it to a target value The statistic you choose depends on 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 For minimizing overall risk the standard deviation and 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 If you are working with Six Sigma or another process quality program you might want to use process capability metrics in defining the objective For more information on these statistics see the Glossary online help and the online Oracle Crystal Ball Statistical Guide 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 select the mean as the statistic you would want to maximize the profit mean However if you select the standard deviation as the
83. ead 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 However 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 a larger 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 However the appropriate choice is not clear Simulation can test various reorder point order quantity policies Inventory System Optimization 97 Inventory System 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 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 Next determine the weekly demand and check if sufficient inventory is on hand to meet this demand Ifnot then the number of lost sales is the demand min
84. ear 62 OptQuest Tutorials Figure 28 OptQuest Results window Solution Analysis view Portfolio Allocation model OptQuest Results Edit View Analyze Preferences Help 1000 Total Solutions Solution Analysis View 2 J 3 663 10 387 4 668 10 387 5 637 10 387 10 387 10 387 10 387 10 387 10 387 Include M Feasible solutions 1000 C Infeasible solutions 0 O All feasible solutions 1000 New best solutions 46 By default the solutions list displays the best 5 of solutions ranked by the objective value If you scroll to the right you can see the sets of decision variable values that OptQuest tried during its search for the best solution You can also see the values of requirements and constraint formulas that were calculated based on these decision variables The statistics table below the solutions list shows the minimum mean maximum and standard deviation values for the objective the constraint and each decision variable the columns in the table In this case the investment strategy maximized the return of the portfolio but at a price high risk due to high volatility and little diversification Is this really the best strategy To find out the investor must interpret the results Interpreting the Results To interpret the OptQuest results start by viewing the forecast chart for the best simulation If it isn t already onscreen
85. eatures and have defined an LSL or USL the process capability statistics are available in the list of statistics Setting Up and Optimizing a Model Note 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 investors who want to maximize the upside potential of their portfolios 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 they 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 the Glossary online help and the online Oracle Crystal Ball Statistical Guide for a description of all available statistics 3 Optionally define requirements a To add a requirement click Add Requirement A default requirement is displayed b First look at the default statistic Is it the one you want to use To review the list of available choices click the underlined statistic and select a different one if you want Depending on your choice the requirement statement could change c Next review the forecast If you want click the underlined forecast and choose another d Then review the requirement operator The selected statistic can be less than or equal to a sele
86. ect Run Run Preferences and increase the maximum number of trials per simulation Run the simulation Use Crystal Ball analysis tools to analyze your results For more information on using these tools see the Oracle Crystal Ball User s Guide Printing OptQuest Results To print results from any OptQuest results view Run an OptQuest optimization and open the OptQuest Results window Choose a view from the View menu in the OptQuest Results window menu bar Choose Edit in the OptQuest Results window menu bar Choose an appropriate command related to printing at the bottom of the Edit menu Choices are Page Setup Print Preview and Print Viewing Charts in Crystal Ball When an optimization completes you can choose Analyze Forecast Charts to view forecast charts and other charts based on the best solution results However if you copied a solution from the Solution Analysis view that is different from the best solution you need to run a simulation in Crystal Ball before choosing a chart command from the Analyze menu See the Oracle Crystal Ball User s Guide for further instructions Creating OptQuest Reports Following an optimization you can create several different types of OptQuest reports To create an OptQuest report Run an optimization in OptQuest Choose Analyze Create Report In the Create Report Preferences dialog choose Setting Up and Optimizing a Model e Full to create a complete OptQuest report including s
87. ed annual returns and the minimum and maximum amounts the investor is comfortable allocating to each investment Table 3 Portfolio Allocation expected returns and investment bounds Investment Annual return Lower bound Upper bound Money market fund 3 0 50 000 Income fund 5 10 000 25 000 Growth and income fund 7 0 80 000 Aggressive growth fund 11 10 000 100 000 The source of uncertainty in this problem is the annual return of each asset The more conservative assets the Income and Money Market funds have relatively stable annual returns while the Aggressive Growth fund has higher volatility The decision problem then is to 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 Using OptQuest Using OptQuest involves the following steps 1 Create a Crystal Ball model of the problem Define the decision variables within Crystal Ball Start OptQuest In OptQuest define a forecast objective and any requirements Select decision variables to optimize Specify any constraints on the decision variables Select optimization settings Run the optimization SO 300 I GS NO eS Interpret the results Creating the Crystal Ball Model In this case the model has already been created for you To review it In Excel open the Portfolio Allocatio
88. 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 does not get trapped in local solutions and it does not get thrown off course by noisy uncertain model data You can find more information on OptQuest s search methodology in the references listed in Appendix C References and Bibliography Once you describe an optimization problem by selecting decision variables and the objective and possibly imposing constraints and requirements OptQuest invokes Crystal Ball to evaluate the simulation model for different sets of decision variable values OptQuest 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 As shown in the following flow chart the search process continues until OptQuest reaches some termination criteria either a limit on the amount of time devoted to the search or a maximum number of simulations Figure
89. ere very little hard data exists Based on seismic data analysts can estimate the probability distribution of the reserve size With little actual data available the discovery team wants to quantify and optimize the Net Present Value NPV of this asset You can simplify this analysis by representing the production profile by three phases shown in Table 10 Table 10 Oil production 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 as the 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 duration Decline The period when production rates P decline by the same proportion in each time step leading to an exponential function P t P O exp c t Oil Field Development 83 Phase Description where t is the time since the plateau phase ended and c is some constant With only estimates for the total Stock Tank Oil Initially In Place STOP 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 10th percentile P90 of t
90. etermine 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 OptQuest 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 Requirements While the search process benefits from the use of constraints and tight bounds performance generally suffers when you include requirements 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 OptQuest must identify the characteristics of solutions likely to be requirement feasible This 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 Factors That Affect Optimization Performance 117 Complexity of the Objective A complex objective has a highly nonlinear surface with many local minimum and maximum points Figure 73 Graphs of complex objectives SHR Are n ANAA NAR A EONA Se es Np ee ange PAST EN H T RS TTA NN age AY
91. even consistent manner 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 terms in the formulas can only contain a single variable multiplied by a constant For example 3x 1 2y is a linear relationship since both the first and second term involve only a constant multiplied by a variable maximum The largest value in a dataset mean The familiar arithmetic average of a set of numerical observations the sum of the observations divided by the number of observations 134 Glossary 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 scatter search and their hybrids minimum The smallest value in a dataset 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 multiobjective optimization A technique that combines multiple often conflicting objectives such
92. eviation of Total expected retum from 8000 to 10000 in steps of 250 Decision Variables Name Money Market fund Lower 0 Base 25000 Upper 50000 Type Continuous Cell Portfolio Revisited EF xis Model IC13 Name Income fund Lower 10000 Base 25000 Upper 25000 Type Continuous Cell Portfolio Revisited EF xis Model IC14 Name Growth and Income fund Lower 0 Base 25000 Upper 80000 Type Continuous Cell Portfolio Revisited EF xis Model IC15 Name Aggressive Growth fund Lower 10000 Base 25000 Upper 100000 Type Continuous Cell Portfolio Revisited EF xds Model IC16 Options With simulation stochastic Enable low confidence testing The objective and any requirements or constraints appear at the top Decision variables and options appear at the bottom Ifthe opt file contains a variable requirement for Efficient Frontier analysis it is displayed above the decision variables and is labeled Efficient Fronter as shown in Figure 11 above The Options settings indicate whether the optimization is stochastic or deterministic and whether low confidence testing is enabled to automatically stop the optimization when specified conditions are met Once you have imported the opt file into the viewer you can start transferring the information to each panel of the OptQuest wizard On the Objectives panel add an objective and set it to match the text in the viewer If there are any requirements or variable req
93. f Links to External Web Sites in Documentation This documentation may contain links to Web sites of other companies or organizations that Oracle does not own or control Oracle neither evaluates nor makes any representations regarding the accessibility of these Web sites Enabling Accessibility for Crystal Ball You do not need to enable accessibility specifically for Oracle Crystal Ball Decision Optimizer Fusion Edition including OptQuest it is always in accessible mode OptQuest charts and other output can be extracted to Excel spreadsheets and pasted into PowerPoint slides which are accessible through Microsoft Office For information about Excel or PowerPoint accessibility refer to Microsoft Office product documentation Using the Tab and Arrow Keys in the Crystal Ball Decision Optimizer User Interface The main menubar and menu commands are accessed with shortcut keys After a menu is open the Tab key or Down Arrow key highlights commands in a circular sequence tabbing from the last item moves focus to the first item Using Shift Tab or the Up Arrow highlights commands in the opposite direction Default tab order in wizard panels and dialogs flows from left to right top to bottom Tab can be used to access the first item of a subwindow in a dialog but then arrow keys are needed to move to additional items TTY Access to Oracle Support Services Oracle provides dedicated Text Telephone TTY access to Oracle Support Services with
94. fect even when precision control is turned on Therefore when using precision control you must increase the maximum number of trials to let precision control achieve the appropriate accuracy 2 Turn on Precision Control a Select cell C17 b Select Define Define Forecast c Click the More button in the Define Forecast dialog then click the Precision tab d Check the Specify The Desired Precision For Forecast Statistics option e Check the Mean checkbox f Use an absolute precision of 1000 units 3 Start OptQuest and 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 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 do not affect the objective very much If you are unsure how much each of your decision variables affects the objective Sensitivity Analysis Using a Tornado Chart 119 you can use the Tornado Chart tool in Crystal Ball see the Oracle Crystal Ball User s Guide for more information on the Tornado Chart The Tornado Chart tool shows 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 Figure 74 show
95. fit with limited risk Like Crystal Ball OptQuest 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 OptQuest terms procedures and results How This Manual is Organized Besides this Welcome chapter the OptQuest User Manual includes the following additional chapters and appendices e Chapter 2 Overview This chapter contains a description of optimization models and their components Introduction 9 e Chapter 3 Setting Up and Optimizing a Model This chapter provides step by step instructions for setting up and running an optimization in OptQuest e Chapter 4 OptQuest Tutorials This chapter contains two tutorials designed to give you a quick overview of OptQuest s features and to show you how to use the program Read this chapter if you need a basic understanding of OptQuest e Chapter 5 Examples Using OptQuest This chapter contains a variety of examples to show the types of problems that OptQuest can solve e Appendix A Optimization Tips and Notes This appendix describes different factors that enhance the performance of OptQuest s features e Appendix B Accessibility The appendix provides a summary of OptQuest s menus and a list of the commands you can execute directly from
96. fund c8 normal mean 11 standard deviation 18 If you need help viewing or defining assumptions or forecasts see the Oracle Crystal Ball User s Guide 3 Select Run Run Preferences Ep and set the following run preferences e Maximum number of trials to run set to 1000 e Sampling method set to Latin Hypercube e Sample Size For Latin Hypercube set to 500 56 OptQuest Tutorials e Random Number Generation set to Use Same Sequence Of Random Numbers with an Initial Seed Value of 999 Defining Decision Variables The next step is to identify and define decision variables in the model OptQuest models must have at least one decision variable Define the first decision variable a Select cell C13 b Select Define Define Decision A c Set the Variable Type to Continuous d Set the lower and upper bounds according to the problem data columns D and E in the worksheet as shown in Table 3 and Figure 20 Notice that you can enter cell references for cells D5 E5 and the fund name cell B5 After you complete an entry the cell reference changes to its value 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 If you used cell references for the name lower bound and upper bound of the decision variable defined in step 1 you can use Crystal Ball s Copy Data and Paste Data commands to define the
97. h decision variable 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 toa model You must define constraints in terms of decision variables continuous A variable that can be fractional that is it can take on any value between the lower and upper bounds 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 can control deterministic A model or system with no random variables that yields single valued results discrete variable A variable that can only assume values equal to its lower bound 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
98. h Efficient Frontier and Solution Analysis view Analyze Creates reports and extracts data Preferences Shows all solutions Help Displays help for the Best Solution Solution Analysis and Efficient Frontier windows Table 15 lists Alt key combinations available in the OptQuest Results window to execute the listed menu commands without using the mouse Commands are listed by menu in the order they appear on that menu Not all commands are available in every view OptQuest Results Window Menus 125 Table 15 OptQuest Results window keyboard shortcuts Menu Command Keystrokes Edit Copy Best Solution to Spreadsheet Alt e c Edit Copy Chart Alt e o Edit Page Setup Alt e g Edit Print Preview Alt e r Edit Print Alt e p View Best Solution Alt v b View Solution Analysis Alt v s Analyze Create Report Alt a r Analyze Extract Data Alt a e Preferences Show All Solutions Alt p s Help Best Solution Help Alt h b Help Solution Analysis Help Alt h s Help Efficient Frontier Help Alt h e OptQuest Control Panel Keyboard Shortcuts The OptQuest Control Panel controls OptQuest runs Start Stop Continue Reset or you can click the Run Preferences button to control the maximum length of time or number of simulations for an optimization The Control Panel has three menus Run Analyze and Help to further control the performance of OptQuest and Crystal Ball Table 16 following
99. h one group of settings stored in each Then you can open a profile workbook with appropriate settings and use it as the primary workbook in the OptQuest wizard As long as your main model workbook is also open OptQuest will use the settings in the blank workbook and your model will still run as you intended 120 Optimization Tips and Notes Other OptQuest Notes The following notes in this section can help you avoid problems while using OptQuest and can also assist in troubleshooting any difficulties that might happen e Automatic Resets of Optimizations on page 121 e Constraint Formula Limitations on page 121 e Minor Limit Violations With Continuous Forecasts on page 122 e Solutions Still Ranked Even With No Feasible Solution on page 122 e Referenced Assumption and Forecast Cells on page 122 e Decision Variables and Ranges With the Same Name on page 122 e Linear Constraints Can Be Evaluated As Nonlinear on page 122 Automatic Resets of Optimizations If the first simulation does not run for some reason or generates an error the entire optimization is reset Otherwise if the user stops a running optimization or an error occurs after an optimization starts successfully the results to that point are kept and the optimization is not reset Constraint Formula Limitations The following sections describe several limitations in defining constraint formulas e Array Formulas on page 121
100. he NPV distribution In other words the oil company wants to optimize an NPV value which they are 90 confident of achieving or exceeding 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 However 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 Oil Field Development Spreadsheet Model Open the Oil Field Development xls workbook found in the Crystal Ball Example folder Figure 46 Figure 46 Oil field development problem spreadsheet model E Oil Field Development xls DAR Oil Field Development STOIIP mmbbls Recovery Time to plateau 2 00 years Well rate mbd Wells to drill Mature Decline Minimum rate 10 00 mbd Years Years Discount factor Well cost Smm Facility size mbd mmbbls million barrels Oil margin 2 00 bbl mbd thousand barrels per day Plateau ends at 65 0 of reserves Smm million dollars Plateau rate is m of reserves annually bbl dollars Calculated values Reserves 630 00 mmbbls Max plateau rate 172 60 mbd Plateau rate 172 60 mbd Build up production 63 00 mmbbls Plateau production 346 50 mmbbls Plateau ends at 7 50 years Decline factor 0 2692 Production life 18 08 years lt Objective maximize 10th percentile 31 aa n
101. hematic of an optimization model with uncertainty Assumptions AAAA JOSS i Assumptions AANNNTS AA O gt Objective Decision Variable S Forecast Decision Variable Decision Variable A stochastic optimization model has several additional elements e Assumptions Capture the uncertainty of model data using probability distributions e Forecasts Are frequency distributions of possible results for the model 112 Optimization Tips and Notes e Forecast statistics Are summary values of a forecast distribution such as the mean standard deviation or variance You control the optimization by maximizing or minimizing forecast statistics or setting them to a target e Requirements Are additional restrictions on forecast statistics You can set upper and lower limits for any statistic of a forecast distribution Stochastic models are much more difficult to optimize because they require simulation to compute the objective While Crystal Ball is designed to solve stochastic models using Crystal Ball it is also capable of solving deterministic models Figure 69 shows that deterministic results are a single value while stochastic results are distributed over a curve Figure 69 Comparison of deterministic and stochastic results 40 55 7 0 85x 10 0 40 5 5 7 0 8 5 10 0 Deterministic Stochastic Excel worksheet result Crystal Ball simulation result Discrete Continuous
102. ibe this problem and its OptQuest solution e Project Selection Problem Statement on page 75 e Project Selection Spreadsheet Model on page 76 e Project Selection OptQuest Solution on page 77 Project Selection Problem Statement The R amp 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 following table Table 7 Project analysis example data summary Expected Expected Initial Project Revenue Success Rate Return Investment Expected 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 Budget constrained Project Selection 75 Expected Expected Initial Project Revenue Success Rate Return Investment Expected Profit 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
103. ide OptQuest with the information it needs to improve the solution Specifying Constraints 33 Instead you should break up the left hand and right hand parts of the equation and make sure the conditional operator gt lt is entered in the constraints panel In this example cell G6 could contain SUM B2 E2 and the constraint could be written G6 gt 10 Constraint Types Constraints can be linear nonlinear or constant in special situations e Linear constraints are more efficient in generating feasible solutions to try They are evaluated by OptQuest before a solution is generated e Nonlinear constraints are evaluated by Excel before a simulation is run They may be slower to evaluate if they contain many Excel functions or refer to many formulas in the spreadsheet They are less efficient at generating feasible solutions e Constant constraints are generally an error unless a user defined macro or the Crystal Ball Auto Extract feature is used to set values in a referenced spreadsheet cell For more about user defined macros and constant constraints see information about the OptQuest Developer Kit in the Oracle Crystal Ball Developer s Guide When you create a constraint its type is displayed after the formula Setting Options 34 When you click Next in the Constraints panel or click Options in the navigation list the Options panel opens similar to Figure 17 You can use the Options panel to set OptQuest opti
104. igure 17 Setting Options and Running the Optimization In the Options panel you set options for controlling the optimization process For details click the Help button 1 For this tutorial set the maximum number of simulations to 1000 2 Click Run The OptQuest Results window opens Figure 27 It is displayed in Best Solution view which provides an overview of the best solution found during the optimization Tutorial 2 Portfolio Allocation Model 61 Figure 27 OptQuest Results window Best Solution view Portfolio Allocation model OptQuest Results Edit View Analyze Preferences Help 1000 Simulations Best Solution View i e Performance Chart g 3 Best solutions Infeasible solutions Lastbestsolution Total expected retum Mean 447 559 Simulations Best Solution Simulation 140 Objectives Value Maximize the Mean of Total expected retum 10 387 Requirements Value Constraints Left Side Right Side Aggressive Growth fund Growth and Income fund Income fund M 100 000 100 000 Decision Variables Aggressive Growth fund Growth and Income fund Income fund Money Market fund The mean of the Total Fxpected Return forecast is displayed in the Objectives table In the Decision Variables table you can see the amount to allocate to each fund to achieve the objective If you choose View Solution Analysis in the menubar the Solution Analysis tables app
105. imulation results for the best solution e OptQuest to create a report with OptQuest results only e Custom to display the Custom Report dialog where you can choose which information including OptQuest results to display in the report Figure 8 shows elements you can choose to include in the OptQuest Results section of a custom report Figure 8 OptQuest Results settings in the Custom Report dialog Custom Report Report sections OptQuest Results Details v Report Summary e paa Forecasts ore Assumptions V Chart 100 l Decision Variables a Overlay Charts Trend Charts V Constraints Sensitivity Charts Scatter Charts Decision variables K M Best solution gagga Target Forecasts 4 Click OK in the Create Report Preferences dialog to generate the report Figure 9 The first set of information is textual and numerical related run preferences run statistics other statistics such as number of infeasible solutions and Crystal Ball data the number of requirements constraints assumptions decision variables forecasts and frozen items The second set of information is graphical similar to that shown in Figure 9 and contains information displayed in the OptQuest Results window For more information about Crystal Ball reports see the online Oracle Crystal Ball User s Guide Interpreting the Results 43 Figure
106. imulation runs It is therefore not possible to enter a constraint on random values in the assumption cells or on the statistics in forecast cells In general you should avoid referenceing these in constraint formulas In requirements assumption and forecast cells are evaluated at the end of the simulation Decision Variables and Ranges With the Same Name It is possible to define a decision variable and a cell address or range with the same name If you do that only the decision variable is accessible on the Constraints panel of the OptQuest wizard For best results always give decision variables and ranges different names and avoid naming decision variables combinations of letters and numbers that resemble cell addresses Linear Constraints Can Be Evaluated As Nonlinear If you have a cell reference in an OptQuest constraint that is more than seven levels of formulas removed from a decision variable any constraint based on that cell will be evaluated as nonlinear even though it might be linear 122 Optimization Tips and Notes Accessibility In This Appendix YORE PSN A vases cep dsc nb ou napa sgt en ba ln sata Ran Saale Rca ONRRAR A 123 betes SoTL NL ay ct E A ee CE T A Tr AERO ATA E E ea en PTS ate Aer te RMN ean Ve RR PRR eT eh 123 OpiQuest Wizard Keyboard Command EUAS iieii ii nan aeta ad aed eae a awd tas 124 Opiuest Resulis Window Monus untedi aay tied tees Mee ay ere ete ds Meee a ees 125 OpiQuest Control Pane
107. in the United States of America 24 hours a day seven days a week For TTY support call 800 446 2398 OptQuest Wizard Keyboard Command Equivalents Each panel of the OptQuest wizard has buttons that navigate through the wizard panels run an optimization close OptQuest get online help and perform other operations only available on a particular panel When you click Alt in each panel OptQuest Results view or the OptQuest 124 Accessibility Control Panel shortcut keys are highlighted in each menu or button label Table 13 lists keyboard equivalents shortcut keys for OptQuest wizard buttons Table 13 OptQuest wizard keyboard shortcuts Command Panel Keystrokes Add Comment Constraints Alt c Add Objective Objectives Alt o Add Requirement Objectives Alt r Back All but Welcome Alt b Close All Alt c Delete Objectives Constraints Alt d Efficient Frontier Objectives Constraints Alt e Help All Alt h Insert Reference Constraints Alt r Insert Variable Constraints Alt v Next All but Options Alt n Run All but Welcome Alt r OptQuest Results Window Menus The OptQuest Results window has the following menus listed with the operations they perform Table 15 shows shortcut keys for commands on each menu Table 14 OptQuest Results window menus Menu Actions Edit Copies solutions to spreadsheet copies charts sets up pages for printing prints View Switches between Best Solution wit
108. ings are saved to the workbook Otherwise current settings are discarded and the last saved settings remain Closing OptQuest To exit OptQuest without running an optimization click Close in the OptQuest wizard If you have not saved changes to the optimization settings yet OptQuest prompts you to save them to the primary workbook Saving optimization models and settings 45 Setting Up Efficient Frontier Analysis in OptQuest Efficient Frontier analysis calculates the curve that plots an objective value against changes to a requirement or constraint A typical use is for comparing portfolio returns against different risk levels so that investors can maximize return and minimize risk For a theoretical discussion see Efficient Frontier Analysis on page 19 For an illustration of an Efficient Frontier chart see Figure 7 To request an Efficient Frontier analysis in OptQuest you need to define a requirement or a constraint with a variable bound in either the Objectives or Constraints panel of the OptQuest wizard To define a variable bound for Efficient Frontier analysis In the Objectives panel select an existing requirement to modify or add a new one and select it Alternately select a constraint in the Constraints panel Click Efficient Frontier An Efficient Frontier row opens below the requirement or constraint Adjust the underlined elements to define a range of values for one or both bounds of the requirement or const
109. ion Pause or Stop Pauses or stops the current optimization Reset q d Resets the current optimization and closes all results The progress bars help you keep track of individual simulations and the optimization as a whole If simulations are running faster than one per second you will see a marquee style progress bar If an optimization is set to run for a maximum amount of time the upper progress bar shows elapsed time instead of number of stimulations A notification message is displayed if the optimization stops early because a set confidence level is reached or there has been no solution improvement for a set number of simulations me If you click the More button a panel opens below the control buttons with additional information about the optimization OptQuest Results Window When an optimization is complete you can view the OptQuest Results window for information about the current optimization The following sections describe different Results window views e Best Solution View on page 37 Setting Up and Optimizing a Model e Solution Analysis View on page 38 e Efficient Frontier Chart on page 39 Best Solution View Figure 5 shows Best Solution view results for an OptQuest example model Product Mix xls Figure 5 OptQuest Results window Best Solution view OptQuest Results Edit View Analyze Preferences Help 741 Simulations Best Solution View Performance Chart 12 000 00
110. ion constant and the objective is to maximize the function The relationship between return and risk for the investor is captured entirely by this one function no additional requirements are necessary Portfolio Revisited 89 Geometrically the optimal solution for a multi objective 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 For k 0 5 the optimal solution occurs at the point where the return minus one half the standard deviation has the highest value The following sections describe the model for this problem and its OptQuest solution e Multi objective Optimization Spreadsheet Model on page 90 e Multi objective Optimization OptQuest Solution on page 90 Multi objective Optimization Spreadsheet Model Open the Portfolio Revisited xls workbook found in the Crystal Ball Examples folder The total expected return forecast assumptions and decision variables are the same as in the original model Scroll down to see the new items added as shown in Figure 51 Figure 51 Portfolio Revisited Spreadsheet Model E Portfolio Revisited xls FE Portfolio Allocation Revisited Annual Lower Risk factor Investments Money Market fund Income fund Growth and Income fund Aggressive Growth fund Total
111. is optimization runs more slowly than some You might want to run fewer than 1 000 simulations or use the Advanced Options settings to automatically stop the optimization when certain criteria are met see Advanced Options on page 35 Inventory System Optimization 99 2 Run the optimization Figure 57 following shows optimization results OptQuest identified the best solution as having an order quantity of 330 anda reorder point of 320 The Performance Chart shows that OptQuest quickly found a good solution value Figure 57 Inventory system model optimization results OptQuest Results Edit View Analyze Preferences Help 636 Simulations Best Solution View Performance Chart Best solutions Infeasible solutions Last bestsolution Total Annual Costs Mean 63 127 191 255 319 383 447 511 575 Simulations Best Solution Simulation 136 Objectives Minimize the Mean of Total Annual Costs Order Quantity Reorder Point 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 per simulation for better precision This is a good practice since choosing too small a step size initially consumes a lot of time or if time is restricted OptQuest might not find a good solution Thus as the number of decision variables and range of search increases use larger step si
112. ivaes 46 Efficient Frontier Variable Bound Example abe sees ees Srey aibi sea 46 Transferring Settings oh od 2 he ee hd hee ea 46 Learning More About OptQuest SMEARS EE EARP ARORA SS EEE EET 48 Chapter 4 OptQuest TOURS bio ho ep OE he eee Ee NER RESAS Ega 49 In OUUCHOG cnc eedee breed A E a aren tavatoreasayewsaena Gawain 49 Tutorial 1 Futura Apartments Model ean er eat bas Srat ee sai 2 49 DRA cay ieios oerip ap Erra anA pee seuesenriveec eee Seoys BO Tutorial 2 Portfolio Allocation Model 4 5 24 s0060e0iescseeeesdednecnvans 54 Problem Description oe eee E eer eT eee neue bers sorot 55 Umg OPOE ae er ea eee es rene ee eer re er rere re Chapter 5 Examples Using OptQuest Sdiri eb ad ee ptkbddki bd sgaueeed eats ee sone mes 67 EE acc TELE bea dead ade EEEE TEET SEERP IR sea ceadadesaged 67 Product Mike pose eavaesawa res EE E E T awed E E I T 69 Product Mix Problem Statement seas sees ee ennee acavedas ere arei oes OF iv Contents Product Mix Spreadsheet Model seas Se eeaeRS SERDES sept er s 70 Product Miz OMQuest SOI 6a 0 os e5 549444 se4 ese edneee se cen eeweeees FU Hotel Design and Pricing Problem eee Lee eT E E Te Tee re eee Te Hotel Design Problem Statement ates hes EPEE m EP or seus 72 Hotel Design Spreadsheet Model 46 446444604800 ibs 5 d due bon bedi ws eiai da Hotel Design Oot ett Some sece pace hand amp it Krena Pee Ree Kaede T Budget constr
113. kes phone des Neu snds 96 Inventory System Problem Statement srai or aaa sade ow ee TE 96 Inventory System Spreadsheet Model p eso ge4veueccagessedeegceseseoeces BB Inventory System OptOuest Soon serrr oirir ertr kpk inr sexe OO Deil Bit Replacement Policy 2o644ic0s00essdaieeassedapenssseces itiaeevn WE Drill Bit Replacement Problem Statement TEP TET ete er siit TEP 102 Drill Bit Replacement Spreadsheet Model EE e PNE TEN wa 103 Drill Bit Replacement OptQuest Solution sssusa aasaran reiki 104 Gasoline Supply Chain prek lt r 254 aoe eaeee nea eae ke brad er 105 Gasoline Supply Chain Statement of Problem 2 5 444 4s40 0ecese000e04 106 Gasoline Supply Chain Spreadsheet Model ee vere ree Te rere eee 107 Gasoline Supply Chain OptQuest Solution 2 242 0c eacsewacsiaseovisaears 108 Contents v Appendix A Optimization Tips and Notes LOOT ETC TET ETT Te REC ES Cee eT ee eee fii e 6 oie sg E E ee eee E E EE E E y Model Typis c prsirerip setete tpai a E T T E 111 Optimization Models Without Uncertainty eT Sari eres ates er vr sa ALL Optimization Models With Uncertainty s 224 246 5884 i0sc ddnseesreiveas 1 Discrete Continuous or Mixed Models 0 ccc ccc ccc e eee eeees 113 Factors That Affect Optimization Performance ee sau ated sa ere eeu 114 Simulation Accuracy Sie Ree HSCS ING ARS ETRE ee SKS 115 Numberof Decision Ve sg whe hn dos oe hbas
114. l Keyboard SHOMCUIS cctsiusvudednaddiativad conia dadderas guider rinira cinikan Sinana tundin 126 Introduction This appendix describes the accessibility features of OptQuest including keyboard equivalents for OptQuest commands For details on accessibility features of Crystal Ball Decision Optimizer see the Oracle Crystal Ball User s Guide The following sections summarize accessibility features and keyboard shortcuts e Accessibility Notes on page 123 e OptQuest Wizard Keyboard Command Equivalents on page 124 e OptQuest Results Window Menus on page 125 e OptQuest Control Panel Keyboard Shortcuts on page 126 Accessibility Notes The following sections discuss Crystal Ball and OptQuest accessibility features e Accessibility of Code Examples in Documentation e Accessibility of Links to External Web Sites in Documentation e Enabling Accessibility for Crystal Ball on page 124 e Using the Tab and Arrow Keys in the Crystal Ball Decision Optimizer User Interface on page 124 e TTY Access to Oracle Support Services on page 124 Introduction 123 Accessibility of Code Examples in Documentation Screen readers may not always correctly read the code examples in this document The conventions for writing code require that closing braces should appear on an otherwise empty line however some screen readers may not always read a line of text that consists solely of a bracket or brace Accessibility o
115. las in spreadsheet cells and then reference them when creating constraints You can also use cell references in Advanced Entry mode to simplify constraint formulas To do this in Advanced Entry mode Enter a formula for the left side of the constraint into a spreadsheet cell The example in Specifying Constraints in Simple Entry Mode on page 29 has SUM C13 C16 entered into cell G13 Consider what to use for the right side of the formula It can be a single value or a formula that resolves to a constant Decide on the relationship between the left and right side lt gt Run OptQuest and display the Constraints panel With the cursor in a constraint formula edit box click Insert Reference Point to the cell with the left side of the formula and click OK Following the cell reference type the relationship operator Click Insert Reference again and point to the cell for the right side of the formula Click OK again Alternately you can type a numeric value instead of using a cell reference You can add additional constraints or other OptQuest settings and run the optimization when settings are complete For best results avoid putting an entire formula including operator in a cell and then referencing that cell in a constraint formula that tests whether the formula is true or false For example suppose cell G6 contains SUM B2 E2 gt 10 Youshould avoid defining a constraint asG6 TRUE This method does not prov
116. le of producing a series of independent random numbers range The difference between the largest and smallest values in a data set rank correlation A method whereby Crystal Ball replaces assumption values with their ranking from lowest value to highest value 1 to N prior to computing the correlation coefficient With this method you can 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 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 for assumption values every time you run a simulation sensitivity The amount of uncertainty in a forecast cell that is a 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
117. lerance levels are specified at three sigma quality for all components Minimize total cost 2 Quality specification is the target sigma quality needed for each component 3 Component costs vary as a function of the quality specifications 4 Sigma one standard deviation Descripti Model ET A drawing of the assembly is in the upper right corner In this example e The nominal dimensions are in cells C14 C18 and C23 C24 e Initial tolerances of each 3 sigma component are in cells D14 D18 and D23 D24 e 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 e The statistical dimension cells H14 H18 and H23 H24 of each 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 The 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 F24 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 a
118. lists the commands and shortcut keys for each of the Control Panel menus Table 16 OptQuest Control Panel keyboard shortcuts Menu Command Keystrokes Run Continue Optimization Alt r c Run Stop Optimization Alt r s Run Reset Optimization Alt r r Run OptQuest Alt r o Run Predictor Alt r p Run Tools Alt r t 126 Accessibility Menu Command Keystrokes Run Save Results Crystal Ball Alt r v Run Restore Results Crystal Ball Alt r e Run Run Preferences Alt r u Analyze Assumption Charts Alt a a Analyze Forecast Charts Alt a f Analyze Overlay Charts Alt a o Analyze Trend Charts Alt a t Analyze Sensitivity Charts Alt a s Analyze Scatter Charts Alt a e Analyze OptQuest Charts Alt a q Analyze Cascade Alt a c Analyze Close All Alt a Analyze Create Report Alt a r Analyze Extract Data Alt a d Help Control Panel Help Alt h c OptQuest Control Panel Keyboard Shortcuts 127 128 Accessibility References and Bibliography In This Appendix Li Tas 1 TE saat eee ey ecw ee ee Er ct Ie GR yt E eae ee my PT a ETC TET ERT STS nae 129 EEA E e ee ERO RP eR TO En EI A RE eT ATER mam Ny A A eS RCN ae PRT nae se 129 Spreadsheet DESIN sisviaeds tuaviel Gadde Ra ad ei awe ed a a 130 RRR IUPOIT HOGS en cas cee aes Canard pond ee eee eer ued Wee tee Goedel aa eas 130 PATS Chel Ai CAUCE aiun a aa gna daee teed atieas DA a A Aa a FAA EAA 130 Qua
119. ly and sisemaa APPC saaa a a 131 Petrochemical Engingenng AD DIAMONG anise E aAA EEA 131 MEE E CIN Pe EE e EEE E E T AE E E A E E E N A E ET ATT 131 Introduction This appendix provides a list of references on advanced topics suggested in this Guide It is intended for advanced users who want more detail on topics such as metaheuristic methods and how optimizations work This appendix also includes bliography entries by subject References These references provide further detail on metaheuristic methods comparisons of optimization methods and optimization of complex systems See the following references on our Web site Also see Optimization Topics on page 130 e Glover F J P Kelly and M Laguna The OptQuest Approach to Crystal Ball Simulation Optimization Graduate School of Business University of Colorado 1998 e M Laguna Metaheuristic Optimization with Evolver Genocop and OptQuest Graduate School of Business University of Colorado 1997 e M Laguna Optimization of Complex Systems with OptQuest Graduate School of Business University of Colorado 1997 A variety of white papers concerning optimization are available on the Web site of OptTek Systems Inc the company that developed the OptQuest calculation engine For a list of papers with abstracts see http www opttek com News WhitePapers html Introduction 129 Spreadsheet Design Powell S G and K R Baker The Art of Modeling with Spreadsheets
120. mization All your new settings are stored in the workbook and will be saved permanently with the workbook the next time you save it If you have more than one opt file for one workbook you can store settings in additional workbooks and use them for a single model For instructions see Maintaining Multiple Optimization Settings for a Model on page 120 Learning More About OptQuest 48 To learn more about OptQuest complete the tutorials in Chapter 4 OptQuest Tutorials Then review the examples in Chapter 5 Examples Using OptQuest For further information check the Crystal Ball website for training opportunities http www oracle com crystalball Setting Up and Optimizing a Model OptQuest Tutorials In This Chapter WER ORM ta 1E E ahh cn cache T EAA en E EE elect crams tees asda EREE A O E AT 49 Tutorial 1 Futura Apartments Modal cscs cananeesagawindn pininana aAA E EANNA I KAATER ON ones TAE Ai 49 Tutorial 2 Poroa Allocation Model crisci iureeniea a in be ee 54 Introduction The first tutorial the Futura Apartments model is an extension of the model used in the first Crystal Ball tutorial in the Oracle Crystal Ball User s Guide and finds the optimal rent for an apartment building This model is virtually ready to run so you can quickly see how OptQuest works The second tutorial the Portfolio Allocation model shows how to set up and define an optimization yourself This model finds
121. mizes the average profit per month Drill Bit Replacement Spreadsheet Model Open the Drill Bit Replacement example shown in Figure 60 below This workbook has Crystal Ball assumptions defined for Table 12 Drill Bit Replacement model assumptions 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 Drill Bit Replacement Policy 103 Figure 60 Drill bit replacement problem spreadsheet model Z Drill Bit Replacement xls gt Drill Bit Replacement Policy Model Inputs Model Outputs Drilling costs hour 425 00 Drilling depth m 520 Replacementtime bit hours Revenue cycle 31 176 91 Costto replace bit 11 000 00 Drilling expenses cycle 23 750 00 Drilling depth coefficient m Profit cycle 7 426 91 Revenue meter drilled 60 00 Replacement cycles month 8 00 Drilling days month Time between replacements Profit month 59 415 32 Optimize time between P replacements to maximize profit The model outputs are computed using the formulas developed in the previous section The drilling expenses in cell F7 include both the drilling costs and the replacement costs The forecast cell is F12 profit per month Drill Bit Replacement OptQuest Solution Note Fxcept where indicated this example uses the recomme
122. n Documentation 124 Enabling Accessibility for Crystal Ball cc cccesnsd cies csseeieudesactieneiaca 124 Using the Tab and Arrow Keys in the Crystal Ball Decision Optimizer User Wee TENE LTE aces ORE T TEC CTT eT eet Te Tor ee Te Tee 124 TITY Access to Oracle Support Services cic aieds deka daceteas ESETED sax 124 OptQuest Wizard Keyboard Command Equivalents suss eis keke oe roS 124 Capt mest Results Window Menus csc eanees deb cieuesee pekia a eea 123 Opi uest Control Panel Keyboard Shnttcute c0ssceneursvaoivaaeedeicneseag 126 vi Contents Appendix C References and Bibliography 0 0 ccc eee eens 129 ene 10S EEE E ee ee ree eae eee eee eee are ee eee EEEE S ere 129 Reet od eee Goat ooteedesee dG E E Ged heee Foe E 129 Spreadsheet Desens votes eed eeeesdks ee nt SRE R SUERTE S Bee eR TIES SEs 130 LIP aR Pps 46g a og ed ye Ae oe I 130 Kerikeri sts sepsin tdt Cee ee ede i ESENE TERETE AEAEE setae edie 130 Stochastic Probabilistic Optimization Theory 2 0 eee eee eee eee 130 Moiltiobiective Optimization 2 4064 da0casediedeeerokowe need REEE ETRAS 130 Fa App hoe ok ates ongheer doh os Wee Sous pebeer eee ees ene aes 130 Quality and Six Signa Applications s12 2 0icseds Poatradseoaebaederaisadews 131 Petrochemical Engineering Applications 64sss 62056254824 s5esseeesee sae eues 131 Inventory Setem APE ys ped ad oe eRe he 5 PR heeii eeheehe 131 MASSA VEREA A hot bea aa ek ee aE eae
123. n exponential decay form for minimization where most improvements occur early in the search Best Solution Values Each time OptQuest identifies a better solution closer to feasibility or with a better objective during the optimization it plots new points in the performance chart and updates the tables below the chart If you have requested an Efficient Frontier analysis you can also display the Efficient Frontier view For more about this view see Efficient Frontier Analysis on page 19 Menus The OptQuest Results window has several menus you can use to copy results to your spreadsheet copy charts print results view other charts and more For a list of menu commands and their shortcut keys see OptQuest Results Window Menus on page 125 Solution Analysis View In Solution Analysis view the OptQuest Results window lists the best solutions found during the optimization By default the top 5 of solutions are sorted by the objective value Controls at the bottom of the window indicate how many solutions to view Statistics are calculated for the solutions shown Note While OptQuest is running Solution Analysis view shows the new best solutions except for Efficient Frontier analyses The top ten solutions still show when an Efficient Frontier analysis is running To display Solution Analysis view choose View Solution Analysis in the OptQuest Results window menubar Setting Up and Optimizing a Model Figure 6
124. n 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 OptQuest selects conservative investments the variability of the expected return is low and the statistics are relatively stable When OptQuest 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 Note that finding the appropriate precision control settings might require some trial and error It can be challenging to decide whether to use absolute or relative precision what is the best precision value in either case and which statistics should receive precision control For more information on setting the precision control feature see the Oracle Crystal Ball User s Guide gt To see the effects of using precision control with the Portfolio Allocation model 1 In Crystal Ball select Run Run Preferences and change the maximum number of trials from 1000 to 5000 This maximum limit is always in ef
125. n workbook from the Crystal Ball Examples folder The worksheet for this problem is shown in Figure 20 following Tutorial 2 Portfolio Allocation Model 55 Figure 20 Portfolio Allocation worksheet E Portfolio Allocation xls Portfolio Allocation Model Investments Money Market fund Income fund Growth and Income fund Aggressive Growth fund Total amount available Decision variables Annual Lower return 50 000 25 000 80 000 100 000 100 000 Amount Total amount invested invested Money Market fund Income fund Growth and Income fund Aggressive Growth fund Total expected return 20 i nN Description Model 25 000 100 000 525 000 25 000 25 000 7 6 500 Maximize Objective lt In this example problem data values 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 in the bottom half of the worksheet This model already has the assumptions and forecast cells defined in Crystal Ball The decision variables are defined as part of this tutorial 2 Make sure the assumptions are defined as follows Assumption Cell Distribution Parameters Money market fund C5 uniform minimum 2 maximum 4 Income fund C6 normal mean 5 standard deviation 5 Growth and income fund C7 normal mean 7 standard deviation 12 Aggressive growth
126. nded Crystal Ball run preferences See Setting Crystal Ball Run Preferences on page 25 With Drill Bit Replacement xls open in Crystal Ball 1 Start the OptQuest wizard As you click Next to step through the problem note e The objective is to maximize the mean profit per month e The problem has no requirements or constraints e This problem has one decision variable whose search limits are 1 and 50 2 Run the optimization Figure 61 following shows the OptQuest results The best solution is to replace the drill bit approximately every 19 9 hours 104 Examples Using OptQuest Figure 61 Drill bit replacement model optimization results OptQuest Results Ce Edit View Analyze Preferences Help 780 Simulations Best Solution View Performance Chart 58 000 00 57 000 00 Best solutions 56 000 00 t t t T T Infeasible solutons Last best solution 78 157 236 315 394 473 552 631 710 Simulations Best Solution Simulation 280 Objectives Maximize the Mean of Profit month 58 281 33 Requirements Cycle time hours Figure 62 following shows the OptQuest forecast chart and statistics 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 Figure 62 Drill bit
127. nerates random 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 Glossary 135 variance The square of the standard deviation where standard deviation is approximately the average of the sum of the squares of the deviations of a number of observations n from their mean value except the sum is divided by n 1 instead of n which would yield a true average 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 and run an optimization model This wizard presents panels for you to complete in the proper order 136 Glossary A B C D E F GH I K MNO P Q R S TUVW Index Symbols opt files 46 A analysis solution 38 using tornado chart 119 apartment tutorial 49 assumptions defining 25 B bibliography by subject 129 financial applications 130 inventory systems 131 optimization topics 130 petrochemical engineering 131 spreadsheet design 130 tolerance design 131 bounds affecting performance 117 defined for decision variables 17 budget cons
128. ng 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 These 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 low end percentile as the measure of risk or uncertainty These selection criteria would be mean variance efficient mean range minimum efficient or mean percentile efficient The mode is usually only available for discrete valued forecast distributions where distinct values might occur more than once during the simulation OptQuest and Process Capability You can use OptQuest to support process capability programs such as Six Sigma Design for Six Sigma DFSS Lean principles and similar quality initiatives To do this activate the Crystal Ball process capability features by checking Calculate Capability Metrics on the Statistics tab of the Run Preferences dialog Once you do that define a lower specification limit LSL upper specification limit USL or both for a forecast in the Define Forecast dialog You can also define an optional value target Once you have defined at least one of the specification limits you can optimize capability metrics for that forecast The process capability metrics appear with other forecast statistics in the OptQue
129. ns Brealey R and S Myers Principles of Corporate Finance 4th ed New York McGraw Hill Inc 1991 Chen N R Roll and S Ross Economic Forces in the Stock Market Journal of Business 59 July 1986 383 403 Markowitz H M Portfolio Selection 2nd ed Cambridge MA Blackwell Publishers Ltd 1991 130 References and Bibliography Quality and Six Sigma Applications Creveling C Tolerance Design A Handbook for Developing Optimal Specifications Reading MA Addison Wesley 1997 Pyzdek T The Six Sigma Handbook Revised and Expanded The Complete Guide for Greenbelts Blackbelts and Managers at All Levels 2nd Ed New York McGraw Hill 2003 Sleeper A Design for Six Sigma Statistics New York McGraw Hill Professional 2005 Sleeper A Six Sigma Distribution Modeling New York McGraw Hill Professional 2006 Petrochemical Engineering Applications Humphreys K K Jelen s Cost and Optimization Engineering 3rd ed New York McGraw Hill 1991 257 262 Inventory System Applications Evans J R and D L Olsen Introduction to Simulation and Risk Analysis New York Prentice Hall 1998 Quality and Six Sigma Applications 131 132 References and Bibliography Glossary APT Arbitrage Pricing Theory assumption An estimated value or input to a spreadsheet model Assumptions capture the uncertainty of model data using probability distributions bound A maximum or minimum limit you set for eac
130. nsportation distances SP to SD1 15 SP to SD2 12 5 SD1 to RO1 6 5 SD1 to RO2 7 5 SD1 to RO3 9 0 SD2 to RO1 9 0 SD2to RO2 8 0 SD2 to RO3 7 0 Existing inventories in gallons are Refinery 200 gallons SD1 50 gallons SD2 100 gallons RO1 120 gallons RO2 180 gallons RO3 80 gallons Other assumptions include There is no capacity limit on transportation links and supply chain points There is an implicit constraint that the SDs do not have any stockouts This mathematically implies that Existing Inventory Supply Received Demand Fulfilled gt 0 Gasoline Supply Chain Spreadsheet Model Open the spreadsheet model for this example Gasoline Supply Chain xls as shown in Figure 63 This model includes Four Crystal Ball assumptions in cells C5 C33 F33 and 133 These represent stochastic output from the refinery and stochastic demand at the retail outlets Two Crystal Ball forecasts in cells K5 and K7 to represent total costs and the worst case stockout situation Eight decision variables that represent transportation costs from the refinery to the two supply depots and from each depot to each retail outlet These appear in cells E10 H10 C21 C22 C23 121 122 and 123 For this example OptQuest can determine how much to supply at each of the SDs and ROs to minimize the total expected cost while maintaining stockouts at ROs at an acceptable level Gasoline Supply Chain 10
131. obtainable 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 possible for given mean mean return standard deviation of return 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 for given standard deviation mean return standard deviation of return Portfolios that lie directly on the curve are called efficient see Markowitz 1991 listed in Financial Applications on page 130 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 Tutorial 2 Portfolio Allocation Model on page 54 uses one technique to search for optimal solutions on the efficient frontier This method uses the mean and standard deviation of returns as the criteria for balanci
132. ons including optimization length time or number of simulations Crystal Ball simulation preferences optimization type with or without simulation window display automatic decision variable value settings and more Note Ifyou saved settings in a version of OptQuest earlier than 11 1 1 you will need to set new options in this version of OptQuest To change the settings Choose the settings you want typing any new numeric values Settings are as follows Table 2 OptQuest Options panel settings Option Description Optimization Settings that control how long the optimization runs Control Choose Run for __ simulations or Run for __ minutes and enter the desired value The defaults are 1000 simulations and 5 minutes You can also click the Run Preferences button to change settings in the Crystal Ball Run Preferences dialog Setting Up and Optimizing a Model Option Type Of Optimization Description Choose With simulation stochastic to run a simulation on the assumption variables or choose Without simulation deterministic to use the base case cell value for the assumption cells While Running Settings that control chart window display You can choose Show chart windows as defined for maximum information or Show only target forecast window for fastest performance Update only for new best solutions is checked by default to enhance performance and will only show results related to the be
133. or Mixed Models Optimization models can be classified as e Discrete Contain only discrete decision variables e Continuous Contain only continuous decision variables e Mixed Contain both discrete and continuous decision variables or any of the other decision variable types binary category or custom For more information on discrete and continuous decision variables see Decision Variables on page 17 Figure 70 shows that discrete variable distributions are a series of individual values while continuous variable distributions are an infinite range of values without distinctive bounds except the end points Model Types 113 Figure 70 Comparison of discrete and continuous decision variables 1 3 7 9 5 4 50 4 75 5 00 5 25 5 50 Discrete variable Continuous variable Staff requirements Prime interest rate Linear or Nonlinear Models An optimization model can be linear or nonlinear depending on the form of the mathematical relationships used to model the objective and constraints Figure 71 following illustrates linear and nonlinear relationships In a linear relationship all terms in the formulas only contain a single variable multiplied by a constant For example 3x 1 2y is a 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 1x make nonlinear relationships Any models that contain such terms in either the objective
134. or the quality specification for each assembly component with a continuous range between 1 and 5 sigmas e The problem has no constraints Return to the Objectives panel and enter the objective and requirements shown in Figure 54 Figure 54 Objectives settings to optimize quality and cost Objectives Exclude Maximize the Zst total of Assembly aap Requirements Exclude The Minimum of Assembly gap mustbe greater than orequalto 0 0030in inches The Maximum of Assembly gap mustbe less thanoregualto 0 0200in inches F The Eiai Value of I mustbe less itanoreaualio 60 00 dolors a Run the optimization The cost and quality solution values appear in Figure 55 Tolerance Analysis 95 Figure 55 OptQuest solution for maximum quality with a cost requirement OptQuest Results DER Edit View Analyze Preferences Help Best Solution View Performance Chart Best solutions Infeasible solutions Lastbestsolution 335 447 559 671 783 895 Simulations Maximize the Zsttotal of Assembly gap The Minimum of Assembly gap must be greater than or equal to 0 0030i 0 0037 in The Final Value of Total assembly cost must be less than or equal to 6 59 97 The Maximum of Assembly gap must be less than or equal to 0 0200 in i 0 0164 in Crankshaft Piston Piston bearing Rod Invent
135. ory System Optimization This example is adapted from James R Evans and David L Olson Introduction to Simulation and Risk Analysis New York Prentice Hall 1998 The following sections describe this problem and its OptQuest solution e Inventory System Problem Statement on page 96 e Inventory System Spreadsheet Model on page 98 e Inventory System OptQuest Solution on page 99 Inventory System Problem Statement 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 96 Examples Using OptQuest 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 below a level R called the reorder point the manager orders Q units called the order quantity Note that the reorder decision is based on the inventory position including orders and not the inventory level If managers 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
136. ou want the variable and then either type the variable name or click the Insert Variable button and select one or more variables in the list You can define any number of constraints Constraint Rules and Syntax In general constraint formulas are like standard Excel formulas Each constraint formula e Is constructed of mathematical combinations of constants selected decision variables and other elements e Must each be on its own line e Can be linear or nonlinear You can multiply a decision variable by a constant linear and you can multiply it by another decision variable nonlinear e Cannot have commas dollar signs or other non mathematical symbols In Advanced Entry mode decision variables can be entered directly by name but in Simple Entry mode they can only be referenced within spreadsheet formulas by cell location or range name The mathematical operations allowed in constraint formulas are Table 1 Mathematical operations in the Constraints panel OptQuest Operation Syntax Example Addition Use between terms varl var2 30 Subtraction Use between terms vari var2 12 Multiplication Use between terms 4 2 var1 gt 9 Division Use between terms 4 2 var1 gt 18 Equalities and inequalities Use lt or gt between left and right sides of the constraint Notice that lt and gt are treated as lt and gt for constraints involving continuous decision variables vari
137. ources see the Crystal Ball Web site at http www oracle com crystalball Additional Resources 11 12 Welcome Overview In This Chapter IMRT NM nossa ehh dae cass Meehan mse oe RL A Ao asada T 13 er OMNES DOS a AA eZ a E A E A 13 How TIS WORE eari aeaa aeaa aaa a a eae 14 POGUE Opimiraion Modeli cerna nna a a dhe dtd a A 15 purizam ECONO S asipi aA AAA AT ENA Naa a a Ga 16 Decision Varia DIGS ciccacqunigensawepeniapauviar EE E si Consta nna E E E nae paemnoMnaeNNEes 18 Modol and Solution Feasibil sss eronneen aenean AAE AA hater AAA AEA EA NEEE ikine 19 o E E A E e AE EA E E A A TE A A AE AE AE E L E 19 Optinest and Process CapaD Y creed iocsuica nero eA A A AAEE iE 21 Introduction This chapter describes the three major elements ofan optimization model the objective decision variables and optional constraints It also describes other elements required for models with uncertainty such as forecast statistics and requirements and ends with discussions of feasibility Efficient Frontier analysis and using optimization with Crystal Ball s process capability features What OptQuest Does Most simulation models have variables that you can control such as how much to charge for rent or how much to invest In Crystal Ball 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
138. pea ad gaa he ee Re ee ha a RE 16 Pn Or ae ee ky teak ae eee ee eee hers ERA 16 ReEQUINCHICN odpocet tatarera d aara bee eehadd Sheree teesedaas eae 17 Decision Variables 4icaske sy Sede aes biirr ehe edere e irete seseae suede 17 SG wiih he es 18 Modeland Dalition Peasipilty incctads iw eet aediivetusktawnshen Davetads das 19 Eificient Frontier Analysis s sso sareraronnorn atana ENTENI RATAN Ea ERS 19 Einer PONDE spree ered eee peg a4 ce ete aE aae 20 Cptlviest and Proca Capa oo Groc ck eee eu heer egw eee sheen eee ENGELS 21 Chapter 3 Setting Up and Optimizing a Model 20 0 ccc ete eens 23 eee n as E A E EE E E TEE EE ET 23 Do T E E E a E E a E EE 23 For Users of OptQuest Versions Earlier Than 11 1 1 x 2 0 2 0 0 eee eee ee eee 24 Developing a Crystal Ball Optimization Model s 2 00s4eks 00a sseee eve tatino 24 Developing the Worksheet ccsscuirdeee eed ease ease ae eaeearaeeaeean ds 24 Defining Assumptions Decision Variables and Forecasts 0000005 25 oeng Crystal Ball Kim Preerences gt cae ag kos ede soy eed ese ie ERE 25 platting VNC Wes ss paepae aaa PARE thee oes Reese Leeds Sas aoe 26 Contents iii Selecting the Forecast Objective PPRT DSS oe rsi need sesa seni sopi sae 28 Selecting Decision Vanables to OpZ 4 ai 245 909 4 444 4549406409044 4 eee Os ae Specie COMI eraat roren daea aeaa Nees deere sess 28 Specifying Constraints in Simple Entry Mode cosee4ecesdnees EET a
139. ptimization results OptQuest Results Edit View Analyze Preferences Help Best Solution View Performance Chart Best solutions Infeasible solutions _ __ Lastbestsolution Best Solution Minimize the Mean of Total Costs Requirements The 95 Percentile of Worst case Stockout must be less than or equal Constraints Supply Depot 1 must have sufficient inventory of gasoline Supply Depot 2 must have sufficient inventory of gasoline Refinery to SD1 Refinery to SD2 SD1to RO1 SD1to RO2 D1to RO3 SD2to RO1 SD2to RO2 SD2to RO3 Figure 65 shows that ifthe quantities of gasoline shown for each decision variable are transported between the indicated destinations the mean total cost will be 36 380 91 and the 95th percentile of the worst case stockout will be 2 88 less than 0 Figure 66 following shows the Total Costs forecast chart and statistics for the simulation of this solution The Total Costs standard deviation is quite small relative to the total costs suggesting that this cost forecast is an accurate representation of the true weekly costs Gasoline Supply Chain 109 Figure 66 Total Costs forecast chart and statistics Forecast Total Costs Edit View Forecast Preferences Help 1 000 Trials 36 380 91 MA 36 38081 0 02 2 95 12 457 9 048 50 0 0626 3 17 0 0026 35 958 41 Mean 36 380
140. r which projects to select from among a limited set e Constraints Optional restrictions placed on decision variable values For example a constraint might ensure that the total amount of money allocated among various About Optimization Models 15 investments cannot exceed a specified amount or at most one project from a certain group can be selected For direct experience in setting up a model and running an optimization see Tutorial 2 Portfolio Allocation Model on page 54 Optimization Objectives 16 Overview Each optimization model has one objective that mathematically represents the model s goal as a function of the assumption and decision variable cells as well as other formulas in the model OptQuest 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 cannot 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 So to use OptQuest you must select a statistic of one forecast to b
141. raint When you define a range for a requirement or constraint bound instead of a single point you also define a number of points to check within the range by setting the step amount OptQuest runs one full optimization for each test point in the range starting with the most limiting requirement test point Then you can see the effects of tightening or loosening a requirement Efficient Frontier Variable Bound Example In Tutorial 2 Portfolio Allocation Model on page 54 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 restriction is a requirement However 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 requirement with a variable upper bound since this limits the maximum standard deviation The investor can define this upper bound with a lower limit of 8 000 and an upper limit of 10 000 For an example of this technique see Portfolio Revisited xls Transferring Settings from opt Files 46 OptQuest versions shipped with previous releases of Crystal Ball prior to 11 1 1 x stored optimization settings in opt files As described in Saving optimization models and settings on page 45 this version of OptQuest saves settings in workbooks An opt file viewer is available to help
142. ready been added Maximize the Mean of Total Expected Return This is the desired objective and needs no editing 2 Click Next to continue The Decision Variables panel opens Selecting Decision Variables to Optimize When you click Next the Decision Variables panel opens similar to Figure 22 58 OptQuest Tutorials Figure 22 Decision Variables panel with cell locations Portfolio Allocation example OptQuest Welcome Review decision variables and change properties as necessary Objectives enon oe Constraints Decision Variabl Lower Bound BaseCase UpperBound Type Step Cell address Worksheet 100 000 Continuous C16 Model Growth and Inco 80 000 Continuous Income fund 25 000 Continuous Money Market fund 50 000 Continuous Options Every decision variable defined in the Crystal Ball model is displayed in the Decision Variables panel The last column indicates whether the variable has been frozen or removed from the optimization In Figure 22 Show Cell Locations is checked so cell addresses appear before the last column The other columns show the bounds base case current model value type and step for each variable The settings are correct for this example so click Next to continue The Constraints panel opens as shown in Figure 23 Specifying Constraints When you click Next in the Decision Variables panel the Constraints panel opens Figure
143. replacement forecast chart and statistics Forecast Profit month DER Edit View Forecast Preferences Help 1 000 Trials Split View Profit month isti Forecast values Precision 58 281 33 186 58 199 29 2 32 o amp 17 519 38 306 928 511 32 0 0832 2 91 0 3006 6 068 58 116 074 56 554 01 Probability o o 8 F Aousnbel4 o 8 Mean 58 281 33 lt gt Statistics shown in color are tested for 0 00 precision Gasoline Supply Chain This example shows how to determine the optimum amount of gasoline to transport between different levels of a gasoline supply chain The objective is to minimize the total cost which Gasoline Supply Chain 105 includes transportation costs and inventory holding costs at various points in the supply chain Itis also important to minimize stockouts at various retail outlets The complexity ofthe problem arises from the fact that there is stochastic variable production at the refinery level and stochastic demand at the retail outlet level The following sections describe this problem and its OptQuest solution e Gasoline Supply Chain Statement of Problem on page 106 e Gasoline Supply Chain Spreadsheet Model on page 107 e Gasoline Supply Chain OptQuest Solution on page 108 Gasoline Supply Chain Statement of Problem The supply chain illustrated here is simplified It consists of one refinery SP two supply depots SD and three ret
144. riables Wells to drill cell C8 Facility size cell C12 and Plateau rate cell C15 e This problem has no constraints Run the optimization The results are shown in Figure 47 Oil Field Development 85 Figure 47 Oil field development optimization results OptQuest Results Edit View Analyze Preferences Help 617 Simulations Best Solution View Performance Chart 21000 _ _ n 180 00 i 150 00 Best solution Infeasible solution Last best solution NPV 10 Percentile 113 170 227 284 341 398 455 512 569 Simulations Best Solution Simulation 117 Objectives Value Maximize the 10 Percentile of NPV 195 50 Facility size Plateau rate Wells to drill The Crystal Ball simulation of this solution in Figure 48 maximizes the 10th percentile P90 of the NPV Figure 48 Oil field development solution percentile view Forecast NPV Edit View Forecast Preferences Help 1 000 Trials Percentiles View Percentile Forecast values Precision pion 260 06 10 195 50 SSS 20 227 53 2 33 30 249 36 2 33 40 269 17 2 10 50 287 24 1 52 60 303 03 1 57 70 319 60 2 19 20 341 80 1 61 90 373 54 2 06 100 520 00 Statistics shown in color are tested for 0 00 precision at 95 00 confidence Portfolio Revisited This example concerns analysis of an investment portfolio with respect to risk as well as return The
145. ribution such as the mean standard deviation or variance You control the optimization by maximizing or minimizing forecast statistics or setting them to a target value 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 level The amount of inventory on hand not counting ordered quantities not received inventory position The amount of inventory on hand plus any amount on order but not received less any back orders kurtosis The 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 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
146. roving this solution driven by the objective in the model Efficient Frontier Analysis Efficient Frontier analysis calculates the curve that plots an objective value against changes to a requirement or constraint A typical use is for comparing portfolio returns against different risk levels so that investors can maximize return and minimize risk If you want to use this type of analysis you need to define a range of values for a requirement or constraint bound For instructions and more information see Setting Up Efficient Frontier Analysis in OptQuest on page 46 Model and Solution Feasibility 19 20 Overview One use for Efficient Frontier analysis is to allocate funds among a portfolio of investments in the most efficient way The Description page of Portfolio Revisited xls describes this technique Efficient Portfolios on page 20 following offers the concepts behind it Efficient Portfolios If you were to examine all the possible combinations of investment strategies for the assets described for Portfolio Revisited xls 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 gt reward standard deviation of return Points on or under the curve represent possible combinations of investments Points above the curve are un
147. rystal Ball model with a requirement added in the Objectives panel Specifically the total room demand is limited by a requirement using the forecast statistic Percentile 80 with an upper bound of 450 Hotel Design and Pricing Problem 73 e This problem has three decision variables and no constraints 2 Run the optimization The results appear in Figure 37 Figure 37 Hotel pricing model optimization results OptQuest Results Edit View Analyze Preferences Help SESE o pes SLU Performance Chart 40 420 00 Best solution Infeasible solution Lastbestsolution Total Revenue Mean 177 266 355 444 533 622 711 800 Simulations Best Solution Simulation 386 Objectives Value Maximize the Mean of Total Revenue 40 418 33 Requirements The 80 Percentile of Total room demand must be less than or equal to 450 The Crystal Ball simulation of this solution in Figure 38 verifies that the chance of demand exceeding capacity is just slightly less than 20 100 80 72 74 Examples Using OptQuest Figure 38 Hotel pricing solution percentiles view Forecast Total room demand Edit View Forecast Preferences Help 500 Trials Frequency View Total room demand Probability Aouenbel Budget constrained Project Selection This example concerns project selection for maximum profitability The following sections descr
148. s a Crystal Ball tornado chart When you viewa tornado chart the most important variables are at the top This arrangement makes it easier to 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 effect is significantly smaller than those at the top you can probably eliminate them as variables and just let them assume a constant value Figure 74 Crystal Ball tornado chart Material 1 Reliability 1 00 1 05 1 10 1 15 1 20 1 25 Material 1 Strength Coil Diameter in Number of Coils Shearing Modulus of Elasticity Wire Diameter in Spring Deflection in Before running the Tornado Chart tool run an initial optimization so that the base case values of the decision variables are close to the optimal solution for your model You can use the Tornado Chart tool to measure the impact of your decision variables For information see Viewing a Solution Analysis on page 40 Maintaining Multiple Optimization Settings for a Model In this version of OptQuest optimization settings are stored in workbooks instead of separate opt files Only one group of settings can be stored in each workbook This is convenient for using and transferring models However there are times when you might want to have more than one group of optimization settings for a model In that case you can create different blank workbooks wit
149. s panel with a variable requirement OptQuest Decision Vabi Primary workbook Porioio Revisted EFxs Y Select an objective and optionally specify requirements Constraints Options The Standard Deviation of Total expected return mustbe less than or eaualto 8 000 dollars Efficient Frontier varythe bound from 8 000 to 10 000 instepsof 250 dollars Add Objective Add Requirement Efficient Frontier The requirement has a variable upper bound for the standard deviation statistic The variable requirement bounds are 8 000 for the lower bound and 10 000 for the upper bound 3 Run the optimization for 2000 simulations The results are shown in Figure 50 88 Examples Using OptQuest Figure 50 Portfolio Revisited Efficient Frontier optimization results OptQuest Results Edit View Analyze Preferences Help 2000 Simulations Best Solution View Efficient Frontier Performance Chart Efficient Frontier Efficient frontier Bestsolutions E3 Infeasible solutons Total expected retum Mean 8 000 8 500 9 000 9 500 10 000 Total expected return Standard Deviation a Best Solution View test point 10 000 v Simulation 55 Objectives Maximize the Mean of Total expected retum 8 657 Requirements The Standard Deviation of Total expected retum must be less than or e 9 975 Testing efficient frontier Constraints Left Side Right Side Money M
150. s sold E Elasticity N New price 72 Examples Using OptQuet Variable Description 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 prices are 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 Hotel Design Spreadsheet Model To follow this example open the Hotel Design example shown in Figure 36 Figure 36 Hotel pricing problem spreadsheet model Hotel Design xls Standard 85 00 250 21 250 00 250 21 250 00 Gold 98 00 100 9 800 00 100 9 800 00 Platinum 139 00 50 6 950 00 50 6 950 00 EEE a boat subject to capacity faa Capacity 4 viN Description Model lt The decision variables correspond to cells G7 through G9 Hotel Design OptQuest Solution Note Except where indicated this example uses the recommended Crystal Ball run preferences See Setting Crystal Ball Run Preferences on page 25 gt With Hotel Design xls open in Crystal Ball 1 Start the OptQuest wizard As you click Next to step through the problem note e The objective is to maximize the mean of total revenue e To ensure that the probability of demand exceeding capacity does not exceed 20 the projected number of rooms sold cell H12 is a forecast in the C
151. sensitivity with respect to the assumption or decision variable cells simulation A set of Crystal Ball trials OptQuest finds optimal solutions 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 on one side of the peak of the curve as compared to the other side A normal distribution curve having no skewness is symmetrical spreadsheet model Any spreadsheet that represents an actual or hypothetical system or set of relationships standard deviation The square root of the variance for a distribution A measurement of the variability of a distribution that is the dispersion of values around the mean 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 to5 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 STOMP 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 ge
152. settings will be stored in the workbook when you run an optimization If the Welcome panel opens click Next The Objectives panel opens Figure 14 Figure 14 Objectives panel Futura with OptQuest example OptQuest Sica Select an objective and optionally specify requirements amp Decision Variables Primary workbook Futura with OptQuest xis v Constraints Objectives Options Maximize the Mean of Profitor Loss Requirements optional requirements on forecasts Add Objective Add Requirement Efficient Frontier Ned gt The objective for this example is to maximize the mean of the Profit or Loss forecast To define an objective click Add Objective For this example the objective has already been added A default objective is displayed in the Objectives list Maximize the Mean of Profit or Loss This is the desired objective so no further editing is necessary Click Next to continue The Decision Variables panel opens as shown in Figure 15 Tutorial 1 Futura Apartments Model 51 Figure 15 Decision Variables panel Futura with OptQuest example OptQuest Welcome Objectives Constraints Options Review decision variables and change properties as necessary amp Show cell locations Lower Bound BaseCase Upper Bound Type Step Freeze 400 00 500 00 600 00 Discrete 1 00
153. st Objectives panel When you copy the values back to the model the optimized values relevant forecast charts and the capability metrics table appear with the workbook See the Oracle Crystal Ball User s Guide for more information OptQuest and Process Capability 21 22 Overview Setting Up and Optimizing a Model In This Chapter TA NT Mt asus tei a a ecco x rns abn ne oa a na hoe TA 23 D NENW A E T AE AT E cas aaa E A TAT E E A E A E A T 23 Developing a Cystal Ball Optimization Modal 2 5 icic cies eee aai ai Meee a eee 24 Stanne OPONE S ha a a a A a T a eles gatos 26 Seoane Me FSCO ODE CIN E aens pa a aa S FAE hes 26 SECU Decision Yanabies to OpUMIZE ciccnaaanaud agunseenpawmeasiavarnnassaaennael anaes rag nieeioeaNner E 27 PE CIVINE CONSTANS aaae N eauneeuoanecense 28 GE ei OU E E E A E A E E S E A tao A E EA E E A E 34 POAT TINGS GOUMANS arana ra ti pon A EE etd odes RE TAAUA 39 nierptenng the RESUS cca tiscinesesincnstoagae ae Mahi ber ere aa nerd 40 Saving COLMA TOT modes and SAMME onian sonaa aia Dianmind ORE R ed 45 Coane OOUE rna a a A a A a A 45 Seting Up Emcient Frontier Analysis im CRTC ex 5 2cis snr since vive tcoiaew hndiy y hese eyineu a ew ane cp ew E EENS 46 ANSE Settings MONTAGE FIGS cts caivtais saisir sland cbadhiona bons view eea EAEE hive beara 46 leaming Mor Abom OE UG Si aiii EE EA E E E ae 48 Introduction Overview gt 1 2 3 4 5 6 This chapter describes how to use OptQuest step by step
154. st 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 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 a 5 sigma component the smallest This is called the quality specification e 1 sigma n 5 sigma 2 component 2 component 5 z g S Pz Q o o 5 l T 7 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 Crystal Ball supports quality programs such as Six Sigma by calculating a set of process capa
155. st be integers o Custom A decision variable that can assume any value from a list of specific values two values or more You can enter a list of values or a cell reference to a list of values in the spreadsheet If a cell reference is used it must include more than one cell so there will be two or more values Blanks and non numerical values in the range are ignored If you enter values in a list they should be separated by a valid list separator a comma semicolon or other value specified in the Windows regional and language settings For details refer to the Oracle Crystal Ball User s Guide e 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 to5 anda step size of 1 can only take on the values 1 2 3 4 or 5 a discrete decision variable with a range of 0 to 2 with a step 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 The cell value becomes the base case value or starting value for the optimization Note If changing the type of a decision variable causes the base case to fall outside the range of values that are valid for that type a new base case value is selected The base case changes to the nearest acceptable value for the new type In an optimization model you select which decision variables to optimize from a list of all the defined decision variables
156. st best solution n o Total Remediation Cost Mean sg 38 Simulations Best Solution Simulation 519 Obes ee Minimize the Mean of Total Remediation Cost 10 909 Remediation Method The distributions for the total remediation cost and the population risk are shown in Figure 44 and Figure 45 Figure 44 Groundwater cleanup total remediation cost forecast chart Forecast Total Remediation Cost Edit View Forecast Preferences Help 2 000 Trials Frequency View 2 000 Displayed Total Remediation Cost o 8 i Probability o 8 o o a i 10 400 10 800 11 200 Certainty 100 00 82 Examples Using OptQuest Figure 45 Groundwater cleanup population risk forecast chart Forecast Population Risk Edit View Forecast Preferences Help 2 000 Trials Frequency View 1 979 Displayed Population Risk o 8 Aouenbel Probability ssssesass m25 9 99E 05 Oil Field Development This example concerns an oil company analysis of Net Present Value for a new asset The following sections describe this problem and its OptQuest solution e Oil Field Development Problem Statement on page 83 e Oil Field Development Spreadsheet Model on page 84 e Oil Field Development OptQuest Solution on page 85 Oil Field Development Problem Statement Oil companies need to assess new fields or prospects wh
157. st solution Uncheck this setting to see the forecast results for each solution Decision Variable Cells Choose Leave set to original values to keep the original base case values in decision variable cells the default Atthe end of an optimization you can copy any solution OptQuest tried including the best solution to these cells if you want Choose Automatically set to best solution to update decision variable cells in the workbook to the best solution found at the end of the optimization Advanced Options Click this button to display the Advanced Options dialog where you can choose to stop a simulation early if the desired confidence level or number of non improving solutions is met For details see Advanced Options on page 35 2 When options settings and all other required OptQuest settings are complete click Run Advanced Options The OptQuest advanced options control whether the optimization stops automatically under certain conditions e The first setting Enable Low Confidence Testing stops the active optimization if the confidence interval around the forecast objective indicates that the current solution is inferior to the current best solution This only works if the statistic used for the forecast objective is the mean standard deviation or a percentile This setting uses the Confidence Level setting on the Trials tab of the Crystal Ball Run Preferences dialog to determine the confiden
158. stal Ball Examples Windows Start menu Table 4 following summarizes the examples in this chapter and the features illustrated Overview 67 Table 4 OptQuest examples Application Product mix Decision Variables Type discrete Constraints 3 Requirements 1 Illustrated Methods Classic optimization example Hotel design and pricing discrete Uses a percentile requirement shows the risk of using a deterministic solution instead of a probabilistic one Budget constrained project selection binary 0 1 Uses binary decision variables for Yes No decisions Groundwater cleanup mixed Uses a category decision variable to select different sets of assumptions Oil field development mixed Uses a percentile objective and a lookup table based on a decision variable Portfolio revisited including Portfolio Revisited EF discrete step 100 Combines several objective functions into one multiobjective using extracted statistics and uses the Arbitrage Pricing Theory for incorporating risk Example of Efficient Frontier Tolerance analysis continuous Uses process capability metrics Inventory system discrete Searches a wide solution space with large steps and then refines the search Drill bit replacement continuous Defines time as a decision variable Gasoline supply chain discrete Classi
159. te whether to extract data to a new workbook or worksheet and can specify the name to use for that data sheet 5 When all settings are complete click OK to extract the data Figure 10 shows what happens when you check OptQuest Solutions and OptQuest Statistics Some OptQuest solutions data rows have been omitted to show the OptQuest statistics data 44 Setting Up and Optimizing a Model Figure 10 Extracted data from Hotel Design xls A B C D E G EE Maximize Mean 80 Percentile lt 450 3 Solution Total Revenue Total room demand Standard price __ Platinum price Gold price _4 302 40 406 61 450 80 00 133 00 110 00 5 65 40 387 99 450 80 00 134 00 109 00 6 61 40 364 52 449 80 00 134 00 110 00 7 64 40 344 47 449 80 00 135 00 109 00 8 57 40 321 00 448 80 00 135 00 110 00 9 156 40 299 50 449 80 00 136 00 109 00 10 40 40 293 91 450 80 00 137 00 107 00 11 87 40 276 04 448 80 00 136 00 110 00 12 62 40 274 53 449 80 00 137 00 108 00 13 134 40 253 10 448 80 00 137 00 109 00 14 798 40 250 17 447 81 00 129 00 104 00 s e2 83 84 Objective Requirements Decision Variables 85 Maximize Mean 80 Percentile lt 450 86 Statistics Total Revenue Total room demand _ Standard price _ Platinum price Gold price 87 Minimum 40 085 37 440 80 00 120 00 102 00 83 Mean 40 308 74 447 80 78 127 58 107 48
160. tent provided on third party Web sites You bear all risks associated with the use of such content If you choose to purchase any products or services from a third party the relationship is directly between you and the third party Oracle is not responsible for a the quality of third party products or services or b fulfilling any of the terms of the agreement with the third party including delivery of products or services and warranty obligations related to purchased products or services Oracle is not responsible for any loss or damage of any sort that you may incur from dealing with any third party Contents Chapter 1 Welcome 246 c02sc000 6 eaes diab edad Eds bee EGES RdRR EEG aOR a 9 TAO gy dice SARS ee ESR GC GER Re EAS A GER ERO ERE E E A 9 How Tor Mamusi is Organized porran cee sea SECIS REE TETOR ESEETO RETEN 9 Sereen Gaptiite NOES 2 1446 c00400s4 00040464 40646804080 EERTE RTE eed oes 10 Sette HO cies 4a dk eo eee ee a eee 10 Addon RMI as born och oGhw bene yees tee oo LETARA whe hs 11 Chapo 2 OOO ni sis got hos it hs ed ek eg a le E E E DA 13 e r e ice cteesnde E E A Pheer ed hoe aes aoePausedeapsdadage gas 13 Wha Opus Does cineca te eeee ey ODES be ORES OEE AETE ESSE eee eee bas 13 How OP Onet WORS ae rg aie Sei we eA EWE ER TRA IRR te 14 About Opunizaiient Models eisor tarki ee dee Ca ete ek uae hed eeetadeews 15 Optimization DORCIVES rorirori seat debian Sade SHAT EES DO TARE RRR RTA DS 16 Foreca DIAS 44
161. the keyboard e Appendix C References and Bibliography The appendix lists references describing OptQuest s methodology theory of operation and comparisons to other optimization software packages This appendix is designed for the advanced user e Glossary This section is a compilation of terms specific to OptQuest as well as statistical terms used in this manual Screen Capture Notes All the screen captures in this document were taken in Excel 2003 for Windows XP Professional using a Crystal Ball Run Preferences random seed setting of 999 unless otherwise noted Due to round off differences between various system configurations you might obtain slightly different calculated results than those shown in the examples Getting Help As you work in OptQuest you can display online help in a variety of ways e Click the Help button in a dialog e Press F1 ina dialog 10 Welcome Note In Excel 2007 click Help at the right end of the Crystal Ball ribbon Note that if you press F1 in Excel 2007 Excel help is displayed unless you are viewing the Distribution Gallery or another Crystal Ball dialog Tip When help opens the Search tab is selected Click the Contents tab to view a table of contents for help Additional Resources Oracle offers technical support training and additional resources to increase the effectiveness with which you can use Crystal Ball products For more information about all of these res
162. the optimal set ofinvestments that balances the risk and the return of an investment portfolio Tutorial 1 Futura Apartments Model 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 Note You can use Predictor supplied with Crystal Ball to find the linear relationship of a dependent variable to one or more independent variables Introduction 49 50 gt To begin the tutorial 1 Start Crystal Ball 2 Open the Futura With OptQuest workbook from the Crystal Ball Examples folder This spreadsheet is an enhanced version of the original Futura Apartments example in Crystal Ball This version contains decision variables The Futura Apartments worksheet opens as shown in Figure 13 Figure 13 Futura Apartments worksheet 5 Futura with OptQuest xls Futura Apartments with OptQuest Number of units Number of Units Rented 35 4
163. ti objective 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 The following figure illustrates the objective and shows that there are no requirements Requirements Exclude optional requirements on forecasts Cc e The decision variables and constraints are the same as previous Portfolio Allocation examples Run the optimization for 2 000 simulations The results appear in Figure 52 After reviewing the results close Portfolio Revisited xls without saving it Portfolio Revisited 91 Figure 52 Portfolio Revisited Multi objective Optimization Results OptQuest Results DEAR Edit View Analyze Preferences Help 2000 Simulations Best Solution View Performance Chart 3 060 00 Best solutions Infeasible solutions Last best solution 8 8 g 8 8 8 Mean minus stdev Final Value Rai 250 501 752 1003 1254 1505 1756 Simulations Simulation 361 Objectives Value Maximize the Final Value of Mean minus stdev 3 052 33 Value Constraints Left Side Right Side Money Market fund Income fund Growth and Income fund Aggres 100 000 100 000 Aggressive Growth fund Growth and Income fund Income fund Money Market fund When should you use multi objective optimization and when should you use single objectives with requirements The former method is especially useful
164. tomatically To use the Efficient Frontier function in OptQuest you need only define a requirement with a variable upper or lower bound OptQuest then calculates points within the variable requirement range The following sections describe the model for this solution method and its OptQuest solution e Efficient Frontier Spreadsheet Model on page 87 e Efficient Frontier OptQuest Solution on page 88 Efficient Frontier 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 original model with the decision variables already defined Portfolio Revisited 87 Efficient Frontier OptQuest Solution Note Except where indicated this example uses the recommended Crystal Ball run preferences See Setting Crystal Ball Run Preferences on page 25 Perform these steps 1 With Portfolio Revisited EF xls open in Crystal Ball set the number of trials per simulation to 2000 in the Run Preferences dialog 2 Start OptQuest from the Crystal Ball Run menu As you click Next to step through the problem note that the objective decision variables and constraints are the same as for the original example Tutorial 2 Portfolio Allocation Model on page 54 Figure 49 shows the Objectives panel with the variable requirement needed for efficient frontier testing Figure 49 Objective
165. trained project selection example 75 c capability metrics 21 charts viewing 42 charts viewing 42 cleanup groundwater example 79 closing OptQuest 45 commands start pause stop 36 commands keyboard OptQuest 126 complexity of objective 118 constraint editor syntax 32 constraint feasibility defined 19 constraints affecting performance 117 defined 18 60 defining 28 editor 31 recommended number 116 simple entry 29 syntax 32 continuous decision variables 17 models 113 creating reports 42 Crystal Ball models creating 24 Crystal Ball charts 42 Crystal Ball web page 129 D data extracting 44 extracting to spreadsheets 44 including in reports 42 decision variables bounds defined 17 defined 13 17 in constraints 31 number affecting performance 116 recommended number 116 selecting to optimize 27 step size 18 types 17 Design for Six Sigma 21 deterministic model illustrated 112 Index 137 A B C D E FGH discrete decision variables 17 models 113 variable step size 18 drill bit replacement example 102 E efficient portfolios 20 engineering petrochemical references 131 examples drill bit replacement 102 groundwater cleanup 79 hotel design and pricing 72 inventory system 96 oil field development 83 overview 67 portfolio revisited 86 product mix 69 project selection 75 requirements 17 tolerance analysis 92 exiting OptQuest 45 extracting data 44 extracting
166. uest 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 only a limited number of simulations within the available time limit However several factors affect OptQuest s performance and the importance of these factors varies from one situation to another The following is a list of the relevant factors that directly affect the search for an optimal solution e Simulation Accuracy on page 115 e Number of Decision Variables on page 116 e Base Case Values on page 116 e Bounds and Constraints on page 117 e Requirements on page 117 e Complexity of the Objective on page 118 e Simulation Speed on page 118 e Precision Control on page 118 Simulation Accuracy For sufficient accuracy 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 fewer trials than the standard deviation or a percentile General guidelines for determining the number of simulation trials necessary to obtain good estimates are Factors That Affect Optimization Performance 115 e 200 to 500 trials is usually sufficient for obtaining accurate estimates for the mean e Atleast 1000 trials
167. uest 55 V variable requirements 46 variables decision defined 17 decision range 17 decision step size 18 decision types 17 variables decision defined 13 in constraints 31 number affecting performance 116 selecting to optimize 27 viewing charts 42 W web pages references 129 what OptQuest does 13 what s new 24 windows Solution Analysis 38 Status And Solutions 36
168. uirements add those and edit them to match the text Figure 12 shows how to enter the objective and the requirement labeled Efficient Frontier in Figure 11 Transferring Settings from opt Files 47 Figure 12 Objectives panel Portfolio Revisited EF xls OptQuest Sidon TON Select an objective and optionally specify requirements amp Decision Variables Primary workbook Portfolio Revisited EF xis v Constraints Options The Standard Deviation of Total expected return mustbe Jess than oreaqualto 8 000 dollars Efficient Frontier vary the bound from 8 000 to 10 000 instepsof 250 dollars Add Objective JI Add Requirement Efficient Frontier l Import 7 Enter any constraints on the Constraints panel You can select one or more constraint formulas in the viewer click the Copy button and then paste the constraint s into an empty constraint row using Ctrl v If you paste more than one constraint each is automatically placed in a separate row If new decision variables are required they must be added in Crystal Ball If necessary you can copy decision variables from the viewer into Notepad print them and then use the printout for a reference when creating the new ones When all the decision variables have been defined start OptQuest again Click the Decision Variables panel to confirm that all have been entered correctly Now you can run the opti
169. ulations the default 9 Click Run in the Options panel OptQuest systematically searches among the set of feasible solutions for ones that improve the mean value of the Profit Or Loss forecast Ina short time OptQuest finds the best solution and displays the OptQuest Results window Figure 18 Figure 18 OptQuest results for Futura Apartments model OptQuest Results Edit View Analyze Preferences Help 201 Simulations Best Solution View Performance Chart 3 000 00 2 900 00 Profit or Loss Mean 2 800 00 Best solutions Infeasible solutions 2 700 00 Last best solution 83 104 125 146 167 188 Simulations 2 959 95 Value teft side Right Side Value Tutorial 1 Futura Apartments Model 53 The performance chart shows solutions calculated by OptQuest Numeric results appear in the table below the chart For this optimization the best solution was found at simulation 25 The optimum rent of 431 per unit produced a maximum mean expected profit of 2 959 95 As you requested in the Options panel a forecast chart for the best solution is displayed If you choose View Statistics in the forecast chart menubar you can see that the mean of the displayed forecast distribution is equal to the maximum mean expected profit shown in the OptQuest Results window 2 959 95 Choose Edit Copy Best Solution to Spreadsheet in the OptQuest Results window menu bar
170. us 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 in Figure 56 implements this logic The 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 This example assumes the specified lead time is constant Figure 56 Inventory system problem spreadsheet model z Inventory System xls gt Order Quantity gt Reorder Point Initial Inventory Lead time ee 5 Cost 50 Holding Cost 0 20 Lost Sales Cost 100 units units 250 units 2 weeks Beg Inv Pos Ending Units End Lost Order Rec d Dmd Inv Sales Placed Beg Order Inv Rec d 250 150
171. ution 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 Thus the investor must face the trade off between higher returns with higher risk and lower returns with lower risk How does this solution compare with the high risk solution You can compare Figure 29 with Figure 32 to answer that question The mean return is lower in Figure 32 but the standard deviation variance and coefficient of variability the risk indicators are also lower Portfolio Allocation Optimization Summary The best OptQuest 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 Appendix A Optimization Tips and Notes and References on page 129 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 66 OptQuest Tutorias Examples Using OptQuest
172. xpected return You might want to find the best solution for which the standard deviation is much lower say below 8 000 You can edit the OptQuest settings to add this risk limitation and still maximize the total expected return To edit OptQuest With Portfolio Allocation xls open choose Run OptQuest If you just ran an optimization click Reset in the OptQuest Control Panel When the Reset prompt is displayed check Launch OptQuest Wizard and click Yes If it is not already open click Objectives in the left pane of the OptQuest wizard The panel opens with Maximize the Mean of Total Expected Return listed as the objective Click Add Requirement This creates a new row in the Requirements area In the new row click Mean In the list select Standard Deviation Click greater than or equal to and change it to less than or equal to Then click 100 and change it to 8000 This adds a requirement that the standard deviation of the expected returns must be less than or equal to 8 000 for a solution to be considered feasible OptQuest Tutorials Figure 30 Objectives panel with the new requirement Objectives Exclude Maximize the Mean of Total expected return Fi an or egual to 7 Click Run The new results are shown in Figure 31 Figure 31 Portfolio allocation optimization results with risk OptQuest Results Edit View Analyze Preferences Help 1000 Simulations Best Solution View Performance Chart
173. you transfer settings from opt files into this version Setting Up and Optimizing a Model To use the opt file viewer Open an optimization model created in a version of Crystal Ball earlier than 11 1 1 The model should have at least one forecast and one decision variable defined They can be dummy data cells and you can delete them later if you need to Choose Run OptQuest or click When the Objectives panel opens click Import The Import Optimization Settings dialog opens Click Browse to locate the opt file When you reach its folder double click the file Its settings appear in the Import Optimization Settings dialog Figure 11 following Figure 11 Imported settings for Portfolio Revisited EF xls Import Optimization Settings Opt File DER Previous versions of OptQuest prior to v11 1 stored optimization settings in Opt files If you had saved the settings for your primary workbook in one of these files use the Browse button below to locate the file You must manually re import the settings from this file into the OptQuest wizard If there are constraint formulas in the file you can copy and paste the entire text into the wizards Constraints panel C sourcesafe Portfolio Revisited EF opt Objective Maximize the Mean of Total expected retum Constraints Money Market fund Income fund Growth and Income fund Aggressive Growth fund lt 100000 Efficient Frontier Vary the upper bound of the Standard D
174. zes and fewer trials initially Later refine the search around good candidates Figure 58 shows the results of an optimization with Order Quantity and Reorder Point bounded to the range 300 to 360 with a step size of 1 and 1000 trials per simulation OptQuest identified the best solution as Order Quantity 332 and Reorder Point 325 There was very little change from the initial solution 100 Examples Using OptQuest Figure 58 Inventory system second optimization results OptQuest Results DER Edit View Analyze Preferences Help Total Annual Costs Mean Best Solution View Performance Chart Best solutions Infeasible solutions Last best solution 53 107 161 215 269 323 377 431 48 Simulations Best Solution Objectives Minimize the Mean of Total Annual Costs Figure 59 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 Figure 59 Inventory system final best solution forecast chart Forecast Total Annual Costs Edit View Forecast Preferences Help 1 000 Trials Frequency View Sa a 8

Examples Using OptQuest

Contents

Download Pdf Manuals

Related Search

Related Contents