Home

Oracle® Crystal Ball Decision Optimizer

1. Growth and Income fund 25 000 Aggressive Growth fund 25 000 Total expected return T 6500 Maximize Objective In this example problem data values are specified in rows 5 through 9 Model inputs the values ofthe 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 296 maximum 496 Income fund C6 normal mean 596 standard deviation 596 Growth and income fund C7 normal mean 7 standard deviation 12 Aggressive growth fund C8 normal mean 1196 standard deviation 1896 If you need help viewing or defining assumptions or forecasts see the Oracle Crystal Ball User s Guide 3 Select Run and then 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 58 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 variable
2. Without simulation deterministic Alt t While Running settings n a Show chart windows as defined Alt s Show only target forecast windows Alt f Update only for new best solutions Alt d Decision Variable Cells settings n a Leave set to original values Alt l Automatically set to best solution Alt a Advanced Options button Alt o OptQuest Results Window Menus The OptQuest Results window has the following menus listed with the operations they perform Table 7 shows shortcut keys for commands on each menu Table 6 OptQuest Results Window Menus Menu Actions Edit Copies solutions to spreadsheet copies charts sets up pages for printing prints View Switches between Best Solution with 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 7 lists Alttkey 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 are displayed on that menu Not all commands are available in every view 72 Accessibility Table 7 OptQuest Results Window Keyboard Shortcuts Menu Command Keystrokes Edit Copy Best Solution to Spreadsheet Altre c Edit Copy Chart Altre o Edit Page S
3. Decision Variables Overlay Charts Trend Charts Ivi Constraints Sensitivity Charts Scatter Charts v Decision variables Chat fioo l v Best solution S S S S S S S 8 amp v Target Forecasts 4 Click OK in the Create Report Preferences dialog to generate the report Figure 8 The first set of information is textual and numeric 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 8 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 8 Graphical OptQuest Results in a Custom Report l REPORT1 OptQuest Results Primary workbook Groundwater Cleanup xls Summary After 1000 solutions were evaluated in 4 minutes and 39 seconds the Mean of Total Remediation Cost was improved from 11 446 to 10 909 a change of 4 69 Performance Chart Best solutions infeasible solutions 4 Last best solution 90 180 270 360 450 540 630 720 810 900 990 Simulations Best Solution Minimize the Mean of Total Remediation Cost 10 909 Cell D3
4. For further information check the Crystal Ball website for training opportunities http www oracle com crystalball Learning More About OptQuest 49 50 Setting Up and Optimizing a Model OptQuest Tutorials In This Chapter WER RR die T REA RETI ETE ean he cence ana E wn tbat eA ewan 51 Tutorial 1 Futura Apartments Modal ossis cand neoxexheka hann Rd palo Ra Rada tnm ex Rn nca 51 Tutorial Portfolio Allocation Model 45 533 a Ue OE GA ERR Ha CR ee a eR ER 56 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 the optimal set of investments 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 functi
5. Simulation 25 Objectives Maximize the Mean of Profit or Loss 2 959 95 Tutorial 1 Futura Apartments Model 55 The performance chart shows solutions calculated by OptQuest Numeric results are displayed 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 select 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 10 Select Edit and then Copy Best Solution to Spreadsheet in the OptQuest Results window menu bar 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 18 Now you need to rent 42 units at 431 each to obtain maximum proft of about 3 059 Figure 18 Futura with OptQuest Optimized for Maximum Profit E Futura with OptQuest xls Futura Apartments with OptQuest Number of units Number
6. Type Step Cell address Worksheet Workbook 1 10 000 25 000 100 000 Continuous C16 Model Portfolio Allocatio Growth and Income fund 1 80 000 Continuous Portfolio Allocatio Income fund 25 000 Continuous Portfolio Allocatio Money Market fund 1 50 000 Continuous Portfolio Allocatio 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 21 Show cell locations is selected so cell addresses are displayed 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 select Show cell locations and click Next to continue The Constraints panel opens as shown in Figure 22 Specifying Constraints When you click Next in the Decision Variables panel the Constraints panel opens Tutorial 2 Portfolio Allocation Model 61 Figure 22 Constraints Panel with No Data Simple Entry Mode OptQuest viel elcome Optionally specify constraints on the decision variables amp Objectives Decision Variables Advanced entry Umen dE irr Options 1 optional constraints on decision variables Efficient Frontier Optionally use the Constraints panel to
7. variables Finding the optimal values for decision variables can make the difference between reaching an important goal and missing that goal Obtaining optimal values generally requires that you search in an iterative or ad hoc 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 Futura Apartments Model on page 51 demonstrates basic OptQuest operation How OptQuest Works 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 Rec
8. Best Solution View Performance Chart Best solutions 4 Infeasible solutions 4 Last best solution 4 Total expected retum Mean 8288895 8 90 180 270 360 450 540 630 720 810 900 990 Simulations Best Solution Objectives Maximize the Mean of Total expected retum The Standard Deviation of Total expected retum must be less than or eq Constraints Model G13 100000 mI Hasan Goh id 31 0 7 Growth and Income fund 31 077 23 301 Income fund Money Mek As shown in Figure 30 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 26 If you return to the Portfolio Allocation model and display the resulting forecast chart in Split View Figure 31 you can see that the new values are displayed The standard deviation of Total Tutorial 2 Portfolio Allocation Model 67 Expected Return is just less than 8 000 and the coefficient of variability is slightly greater than l Figure 31 Best Optimization Solution with Lower Risk Requirement Forecast Total expected return DER Edit View Forecast Preferences Help 995 Displayed Forecast values Precision j Trials i Base Case j Mean 1 Median Mode Standard
9. Dev Variance 63 433 870 Skewness Kurtosis i Coeff of Vari i Minimum j Maximum i Mean Std Err 55 50 45 40 35 30 S 5 KouenboeJ4 ils T Statistics shown in color are tested for 0 00 precision at 95 00 confidence Interpreting Results This solution 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 28 with Figure 31 to answer that question The mean return is lower in Figure 31 but the standard deviation variance and coefficient of variability the risk indicators are also lower Portfolio Allocation Optimization Summary The best OptQuest solution identified may 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 the OptQuest section of the Oracle Crystal Ba
10. 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 definedat least one ofthe specification limits you can optimize capability metrics for that forecast The process capability metrics are displayed with other forecast statistics in the OptQuest Objectives panel When you copy the values back to the model the optimized values relevant forecast charts and the capability metrics table are displayed in the workbook See the Oracle Crystal Ball User s Guide for more information OptQuest and Process Capability 21 22 Overview Model Setting Up and Optimizing a In This Chapter THO UTI cedo alcatel sd pidas Drug E E lace RR 23 D I causa T AE AT E E A Edo acu D Gn DRM Kixda Du EU MEI anal DATUR 23 Developing a Crystal Ball Optimization Model 1 25 terio eee ee End ee Ee Ee ee 24 ate TUE SUIS cos edet tocius Let eL ISO oe bci Cod uod eL OO Eod bL Roc odd Vade 26 Selecane the FHSS ONTING engri XAR Gee Ula c S pu RU aa paa SERVI RaME ERR fd US 26 selechng Decision Vanables to OpEImZB excipe i vor EN PRSE RE E e P pa E bna M SERRE E pu R ME SESS 28 PE CIVINE CONSTANS ET E 29 RE OPONE qp 34 Rinmine ELO EMO Pcs oua tc ust bo E EE AE AE EAEN 35 nierpre ng the RESUS iixa cuxaedit ck is Fri Yir e CORR HERR HODEK MEE ILE RA PRECOR PERI Hare 40 Saving Donation Models and
11. 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 provide OptQuest with the information it needs to improve the solution 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 Microsoft Excel before a simulation is run They may be slower to evaluate if they contain many Microsoft Excel functions or refer to many formulas in the spreadsheet They are less efficient at generating feasible solutions e Constant constra
12. Requirements The 95 Percentile of Population Risk must be less than or equal to 1 00E 04 9 99E 05 Decision variables Best Solution Cleanup Efficiency 91 Remediation Method Hh Report Note Ifyou run an Efficient Frontier optimization you can only create a default OptQuest report This is because one best simulation exists for each test point To create a custom report or any other type of report with Efficient Frontier analysis pick a test point and run a simulation for it Extracting OptQuest Data gt To extract several types of OptQuest data to worksheet cells for further analysis 1 Run an optimization and select Analyze and then 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 44 Setting Up and Optimizing a Model 3 Optional 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 Optional Click the Options tab to indicate 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 Figure9 shows what happens when you select OptQuest Solutions and OptQuest Statistics S
13. a Solution Analysis After the optimization is finished interpret your optimization results 1 Select View and then Solution Analysis in the OptQuest Results window 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 Notice 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 a list of menu commands and their shortcut keys see Appendix A Accessibility 2 Select which solutions to view Along with both grids are groups of controls you can use to filter the solutions to view AII of the controls combine to filter the set of solutions Some controls show the number of solutions that will be included in parenthesis e Inthe first group select 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 Inthenext group select whether to include feasible infeasible or both types of solutions e Ifyouhave requested an efficient frontier analysis selecta test point from the dropdown menu in the last group Notice that all of the solutions are considered for a particular test point even if they were eva
14. 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 In Simple Entry mode cell references and range names cannot be preceded by a minus sign to indicate that they should be subtracted from something unless they are part of a formula expression and not an isolated cell reference or range name If you are using the cell selector in Simple Entry mode only simple cell references or range names are selectable You cannot include coefficients or mathematical operators Normally constraint formulas should always refer to atleast one decision variable either directly or indirectly However there may be situations where you want to set the value in a constraint formula by some other means for example a user defined macro or some other process In these cases you should enter the constraint using the form cell reference lt constant OptQuest identifies this constraint as a constant type since it does not include decision variables and may warn you that the constraint may result in no feasible solutions if care is not taken The mathematical operations allowed in constraint formulas are Table 1 Mathematical Operations in the OptQuest Constraints Panel Operation Syntax Example Addition Use between terms vari var2 30 Subtraction Use between terms varl var2 12 Multiplication Use between terms 4 2 varl gt 9
15. 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 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 76 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 th
16. ed de mde bes 17 OSEE a de S d XC e RC E o e EU CEA CES CA ol e ca eC Ge e de ded 18 Modeland Solution Pessibllite ues aces ep ees be XOCRE CRUS KORR ROGO ORE RC RR KORR 19 Ecen Frontit ANa SS bod ped ba AGERE IKE RE X TRECE ONERE SA I Bihcient Portloll s ic eaoases RR e RR RERYG FRE d eed eaque dE GGG e SK 20 UOptQuest and Process Capability 4a 1524 ss er ge eta SEE RASH cR RES es 21 Chapter 3 Setting Up and Optimizing a Model 20 0 ccc cee eee tenes 23 E ar AE E EE EE AA EAA AE EEEE EE ETEN 23 COENEN 45414602 gradi od dp add edet ad aaa e quads pud qudd d drin 23 For Users of OptQuest Versions Earlier Than 111 18 4s ses hw 24 Developing a Crystal Ball Optimization Model 44cs 06s4445 eh RR wh 24 Developing the Worksheet icio dues b ERR Gear ERE REY GU PES KR EY ata 24 Defining Assumptions Decision Variables and Forecasts 000 0005 25 Setting Crystal Ball Rim Preferences uai cese e485 e454 peas Rew Ee Oa 2n Contents iii Starting OptQuest ias ds Sa ae md ads Sea ss d Selecting the Potent OO 44 ceso eor e ERR EE SR RI RHERRERURE ARR HOER GR 26 Selecting Decision Variables to Optimize 12aeqaeeskasepacrh p a RRP E ERR d 28 Specifying Constraints bins aves T gus TEE er bre 29 Specifying Constraints in Simple Entry Mode ssnsneuruean nner 29 Specifying Constraints in Advanced Entry Mode 000 0000 esses 4a Constraints Editor and Rel
17. enter a comment that describes the constraint The comment is displayed in the Constraints panel near the constraint It also is displayed in the OptQuest Results window to identify the constraint and is included in reports Efficient Changes the selected contraint to have a variable upper or lower bound for use in Efficient Frontier analysis For more Frontier information see Efficient Frontier Analysis on page 19 If you have already added a variable requirement on the Objectives panel a message is displayed that asks if you want to use the selected constraint instead Delete Deletes the currently selected constraint To add a variable or a reference to a constraint place your cursor where you 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 Microsoft Excel formulas Each constraint formula Specifying Constraints 31 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
18. example a discrete decision variable with a range of 1 to 5 and a step size of 1 can take on only the values 1 2 3 4 or 5 a discrete decision variable with a range of 0 to 17 with a step size of 5 can take on only the values 0 5 10 and 15 stochastic A model or system with one or more random variables STOIIP Stock Tank Oil Initially In Place STOIIP is the estimated reserves of an oil field in millions of barrels mmbbls trial A three step process in which Crystal Ball generates 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 77 variance The square of the standard deviation where standard deviation is approximately the average of the sum ofthe squares ofthe 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 prop
19. 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 greater than 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 65 Figure 28 Portfolio Allocation Forecast Chart Split View Forecast Total expected return Edit View Forecast Preferences Help 1 000 Trials Split View 995 Displayed Probability Total expected retum Statistic Forecastvalues Precision Trials Base Case Mean Median 10 579 10 99 Standard Dev 16 258 454 Variance 264 312 461 Skewness 0 0390 Kurtosis 3 15 Coeff of Vari 157 Minimum 58 869 Maximum 64 173 Mean Std Err 514 Mean 10 387 Statistics shown in color are tested for 0 00 precision at 95 00 confidence 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 expected return You may want to find the best solution for which the standard deviation is much lower say below 8 000 You can edit
20. 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 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 you transfer settings from opt files into this version gt To use the opt file viewer 1 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 2 Select and then OptQuest or click amp 3 When the Objectives panel opens click Import The Import Optimization Settings dialog opens 4 Click Browse to locate the opt file When you reach its folder double click the file Its settings are displayed in the Import Optimization Settings dialog Figure 10 following Figure 10 Imported Settings for Portfolio Revisited EF xls Import Optimization Sett
21. of Units Rented 42 4 01 Rent per Unit 85 Rent per Unit Monthly Expenses rsin Tee Decision varabie Profit or Loss 3 058 90 Price demand parameters Slope Intercept Mo 4 gt MN Description Model Note When yourunan 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 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 If you are not familiar with basic optimization terminology such as objectives and constraints review Chapter 2 Overview 56 OptQuest Tutorials Problem Description An investor has 100 000 to invest in four assets Below is a list of the assets expected 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 396 0 50 000 Income fund 596 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 Aggre
22. set the following run preferences e Maximum number of trials to run set to 1000 the default e Sampling method set to Latin Hypercube e Sample Size For Latin Hypercube set to 500 e 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 select Run and then OptQuest or click amp The OptQuest wizard starts 52 OptQuest Tutorials 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 settings will be stored in the workbook when you run an optimization Optional If the Welcome panel opens click Next The Objectives panel opens Figure 13 Figure 13 Objectives Panel Futura with OptQuest Example OptQuest Viel Select an objective and optionally specify requirements amp gt Decision Variables Primary workbook Futura with OptQuest xls v Constraints Objectives 7 Options Maximize the Mean of Profitor Loss Requirements optional requirements on forecasts Add Objective JI Add Requirement J Efficient Frontier lt Back The objective for this example is to maximize the mean of the Profit or Loss forecas
23. 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 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 no combination of investments exists 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 5000 This also results in an infeasible problem You can make infeasible problems feasible by fixing the inconsistencies of the relationships modeled by the constraints OptQuest detects optimization models that are constraint infeasible and reports them to you If a 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 fe
24. 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 Decision Optimizer as a decision variable In this example these decision variables are defined in cells C13 through C16 as shown in Figure 21 The left hand side of the constraint formula shown previously is already entered into cell G13 of the Model worksheet of the Portfolio Allocation example SUM C13 C16 gt To enter the constraint into the Constraints panel 1 Click Add Constraint A row with two edit boxes is displayed as shown in Figure 23 following Figure 23 Constraints Editor in Simple Entry Mode 1 Dos Ju p 62 OptQuest Tutorials In the first box 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 t
25. the OptQuest settings to add this risk limitation and still maximize the total expected return gt 1 66 To edit OptQuest With Portfolio Allocation xls open and settings as described previously in this tutorial select Run and then OptQuest If you just ran an optimization click Reset in the OptQuest Control Panel When the Reset prompt is displayed select Launch OptQuest Wizard and click Yes Optional If it is not already open click Objectives in the navigation 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 Exclude 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 OptQuest Tutorials 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 Figure 29 Objectives Panel with the New Requirement Objectives 9 Exclude Maximize the Mean of Total expected return oO Requirements gt Standard Deviation of Total expected return less than or equal to 8 000 7 Click Run The new results are shown in Figure 30 Figure 30 Portfolio Allocation Optimization Results with Risk OptQuest Results Edit View Analyze Preferences Help 1000 Simulations
26. 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 within 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 controls that enable you to make settings and 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 Altin each panel OptQuest Results view or the OptQuest Control Panel shortcut keys are highlighted in each menu or 70 Accessibility button label Table 4 lists keyboard equivalents shortcut keys for OptQuest wizard controls in the Objectives Decision Variables and Constraints panels Table 5 lists keyboard equivalents for settings in the Options panel Options panel buttons are included in Table 4 Note The Enter key can be used in the OptQuest wizard to put an objective requirement or c
27. 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 24 Setting Up and Optimizing a Model Use formats such as currency or comma formats appropriately 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 the publication references in the OptQuest section of the Oracle Crystal Ball Reference and Examples Guide 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 tria
28. 43 requirements defined 17 60 defining 26 examples 17 feasibility 19 variable 46 results analyzing in Crystal Ball 42 interpreting in OptQuest 40 printing 42 Run 47 run preferences suggested 25 S saving OptQuest settings 45 screen capture notes 10 Simple Entry mode 29 simulations current number 37 running longer 42 Six Sigma 21 solution analysis of results 40 Solution Analysis window 38 solutions feasible defined 55 viewing 37 spreadsheet models creating 24 start command 36 starting OptQuest 26 statistics forecast defined 16 forecast optimizing 16 restricting forecasts 17 selecting forecast 26 Status And Solutions window 37 step sizes for decision variables 18 steps for using OptQuest 23 stop command 36 syntax constraint 31 G MNO P Q R S TUVW T time remaining viewing 37 transferring settings from opt files 47 tutorials Futura Apartments 51 Portfolio Allocation 56 types decision variable 17 U using OptQuest 57 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 selecting to optimize 28 viewing charts 42 W what OptQuest does 13 what s new 24 windows Solution Analysis 38 Status And Solutions 37 Index 81 A B C D E FG H K LM N O P QRS TUVW 82 Index
29. 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 var1 var2 lt 5 Exponents Use between a term and the exponential power var143 Notice that the examples in Table 1 on page 32 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 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 25 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 32 Setting Up and Optimizing a Model You can also use Microsoft Excel functions and range names in constraint formulas Ifyouare using Advanced Entry mode calculations occur according to the following precedence multiplication and division 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 over
30. E EEE boob od EXEC Y EHE CER dp IO OEC Ro PO Eolo EEE AGRO de ole bd E ede 75 POOR 79 Contents V vi Contents Documentation Accessibility For information about Oracle s commitment to accessibility visit the Oracle Accessibility Program website at http www oracle com pls topic lookup ctx acc amp id docacc Access to Oracle Support Oracle customers have access to electronic support through My Oracle Support For information visit http www oracle com pls topic lookup ctx acc amp id info or visit http www oracle com pls topic lookup ctx acc amp id trs if you are hearing impaired 8 Documentation Accessibility Welcome In This Chapter dy grec nc Mere Mr e PME 9 How tre PARLE NEAR Ls nnion basdcrat ima b Kia abit dori ERO d an A M nay baia 9 irc EBEN D C em 10 Duran Me a 10 Papinii sp PET 11 Introduction Welcome to OptQuest an optimization feature available in Oracle Crystal Ball Decision Optimizer OptQuest enhances Crystal Ball Decision Optimizer 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
31. If you click Add Constraint you can reference cells with formulas for the left and right sides of the constraint formula and you can select 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 31 For an example of using Simple Entry mode see Specifying Constraints on page 61 Specifying Constraints in Advanced Entry Mode gt To use the Constraints panel in Advanced Entry mode 1 Switch to Advanced Entry mode by selecting Advanced Entry in the corner of the Constraints editor 2 Inthe 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 31 Specifying Constraints 29 You can 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 In Advanced Entry mode you can use Ctrl c and Ctrl v to copy and paste constraints to duplicate them for further editing You can also paste formulas from the clipboard and this is limited to Advanced Entry mode Advanced Ent
32. Oracle Crystal Ball Decision Optimizer Oracle9 Crystal Ball Suite OptQuest User s Guide Release 11 1 2 2 ORACLE ENTERPRISE PERFORMANCE MANAGEMENT SYSTEM Crystal Ball Decision Optimizer OptQuest User s Guide 11 1 2 2 Copyright 1988 2012 Oracle and or its affiliates All rights reserved Authors EPM Information Development Team Oracle and Java are registered trademarks of Oracle and or its affiliates Other names may be trademarks of their respective owners This software and related documentation are provided under a license agreement containing restrictions on use and disclosure and are protected by intellectual property laws Except as expressly permitted in your license agreement or allowed by law you may not use copy reproduce translate broadcast modify license transmit distribute exhibit perform publish or display any part in any form or by any means Reverse engineering disassembly or decompilation of this software unless required by law for interoperability is prohibited The information contained herein is subject to change without notice and is not warranted to be error free If you find any errors please report them to us in writing If this is software or related documentation that is delivered to the U S Government or anyone licensing it on behalf of the U S Government the following notice is applicable U S GOVERNMENT RIGHTS Programs software databases and related documentation and te
33. SEMNE sininen abun o bas pania aiulambadignacineds piaenana qid 45 EC A4og8 MED 46 Setting Up Efficient Frontier Analysis in OptQuest sseeeeeeeeeeeemm mm 46 Transteming Settings from ipt FIIGS s osos suas epa bv can eva Rn nba e ei ER rt RR PIS D ER Wend cca 4T Losing More Abort CRUSE sean Dus niea nan Rata fuga dut daas AA AENA 49 Introduction This chapter describes how to use OptQuest step by step It also gives details about each of the panels and dialogs in OptQuest Overview To set up and optimize a model with OptQuest follow these steps oa A W N e 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 1 Runthe optimization 8 Interpret the results For Users of OptQuest Versions Earlier Than 11 1 1 x If you 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
34. 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 47 Developing a Crystal Ball Optimization Model Before using OptQuest you must first develop a useful Crystal Ball model This involves building a well tested spreadsheet in Microsoft Excel and then defining assumptions and forecast cells using Crystal Ball You should refine the Crystal Ball model and run several simulations to ensure that the model is working correctly and that the results are what you expect Developing the Worksheet You should build your spreadsheet model using principles of good design since this makes understanding and modifying it easier The spreadsheet should include e A descriptive title e Aninput 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 Aworking 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 19 for an example Notice 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
35. Standard Deviation View test point 10 000 Simulation 55 Onis Maximize the Mean of Total expected retum 8 657 Requirements 9 975 Testing efficient frontier Constraints ise Right Side Money Market fund Income fund Growth and Income fund Aggress 100 000 100 000 Decision Variables Aggressive Growth fund Growth and Income fund 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 that accompanies the chart shows the best solution values for a specific test point You can select which best solution to view by selecting the View Test Point drop down menu or by clicking the diamond symbol in 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 do the following 1 Viewa solution analysis to determine the robustness of the results 2 Runalonger 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 40 Setting Up and Optimizing a Model Viewing
36. Worksheet and Workbook gt To confirm and change selections 1 Review the listed variables Select Freeze for any that you do not want to include in the OptQuest optimization 2 Optional Changethe 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 Notice 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 By default OptQuest uses the base case cell values in your Crystal Ball model as the 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 to show the direction of the sort The sort column and direction ofthe 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 28 Setting Up and Optimizing a Mod
37. a value default definition performance Foranoptimization 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 from a table to conform to a probability distribution random number generator A method implemented in a computer program that is capable of producing a series of independent random numbers range The difference 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 This method lets you ignore the distribution types when correlating assumptions RAROC A multiobjective function that calculates the Risk adjusted Return On Capital reorder point The inventory position when you reorder requirement A restriction on a forecast statistic that requires the statistic to fall between specified lower and upper limits for a solution to be considered feasible risk The uncertainty or variability in the outcome of some event or decision risk factor A number representing the riskiness of an investment relative to a standard such as U S Treasury bonds used especially in APT safety stock The addition
38. act 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 ofthe optimization the initial bounds defined for the decision variables and other factors Running a Longer Simulation of the Results To more accurately assess the recommended solution run a longer Crystal Ball simulation using the optimal values of the decision variables 1 If you did not select to automatically copy OptQuest results to the model workbook set in the Options panel you can select Edit and then Copy Best Solution to Spreadsheet in the OptQuest Results window OptQuest copies the decision variables values from the selected solution into the Microsoft Excel model 2 InCrystal Ball reset the optimization select Run and then Run Preferences and increase the maximum number of trials per simulation Run the simulation 4 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 1 Run an OptQuest optimization and open the OptQuest Results window 2 Select a view from the View menu in the OptQuest Results window menu bar 3 Select Edit in the OptQuest Results window menu bar 4 Select an appropriate command related to printing at the bottom of the Edit m
39. al 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 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 stepsize Defines the difference between successive values of a discrete decision variable in the defined range For
40. alysis View Constrainis Model G13 100000 10 387 99999 9999915621 100000 000037475 0 0 00249561158203 Include v Feasible solutions 1000 5 ofsolutions Infeasible solutions 0 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 the list 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 is not already onscreen select Analyze Forecast Charts and select Total Expected Return Figure 28 following shows the forecast chart and statistics in Split view Notice that the standard deviation of the
41. arch 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 14 Overview Figure 1 OptQuest Flow Determine a new set of values for decision variables Generate Display results in a forecast chart random Calculate numbers for entire assumption spreadsheet cells Add new best objective result to better than OptQuest previous Results ones window About Optimization Models In today s competitive global economy people are faced with many difficult decisions Such decisions may 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 re
42. asible may 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 improving 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 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 Plottin
43. at 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 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
44. ated Buttons 1 5 22 esses b RR RR EO 31 Constraint Rules and Syntax dads ne oe ee 1 Constraints and Cell References in Advanced Entry Mode 0 00 eee 33 Constraint Types 2 2222 na a EE PEIS EA TE EE PITE ANET 34 Setting Options TEP TETEE see or TT TE TEE er 34 Advanced OPUS erdirsin ws PvE a owe DEA SE RE ERA Een Dd ERE Y FEN dde 29 Running Optimizations HERR 35 OptQuest Control Panel Buttons and Commands llis 36 Dp Results T BOE 154i De Ede o eens ee POE Sa a HER Buc pron De DEAE Lou Ly Re wp syte PR Ra KRPRUCKATAN RR EES KE PEE RR Kd 40 Viewing a Solution Analysis 24 9 5 64 5 04 6544624 PRArR REC seeded Qd P Ed RR qe 41 Running a Longer Simulation of the Results soni ws eus bud 42 Printing Opt ues Rens iua neds Ran uo ER eRe Eas EX ERG Y eae eee Viewing Choris 1k Coal OM cb de det bo ORO ORE X Ro eee REA RAO ES I 42 Creating OptQuest Reports ad pads Extras Optio a 05 0 ho ee qe e a CR eC ea TE Saving Op misation Models and Bettinps noe is aas ure yt Era RR EE aa 45 Cosme DOBLE LuqesudesRectisuRa quPARE RARE PRATER quePpaqebhQdqaddxGdde 46 Setting Up Efficient Frontier Analysis in aun IIT d 34 du n ee sze 46 Efficient Frontier Variable Bound Example 0444006406 600sd0e00as ee aw vas 46 Transferring Settings rom opt Files ziceasa desse Erken RA RR REFER RE 47 Learning More A
45. ates 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 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 calcul
46. ates 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 75 forecast statistic 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 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 ofthe degree of peakedness ofa curve The higher the kurtosis the closer the points of the curve lie to the mode of the curve A normal
47. 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 If you would prefer to minimize the statistic or set itto a target value click the underlined operation and select an alternative 26 Setting Up and Optimizing a Model c Finally is the underlined statistic the one you prefer to use If not click it and select a different one If you have activated Crystal Ball s process capability features and have defined an LSL or USL the process capability statistics are available in the list of statistics 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 may 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 may 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 4 Optional Define requirements 6 7 a To adda requirement click A
48. aximizing 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 statistic you may want to minimize it to limit the uncertainty of the forecast 16 Overview 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 requiremen
49. bout OptQuest dd T saad ds Chapter 4 OptQuest Tutorials 6a cc teed co baat eat A d d iu i Introduction ais er Raids 245 1 Tutorial 1 Futura Apartments Model ss ci usar aes are hd sacra RE e aee d 51 Running OptQuest c2sssaes ews A TT IEEE LEST dd ease herding 52 Tutorial 2 Portfolio Allocation Model EUR dag dus du dd 9 OO Probier DISQUE o4 L9 ew FRA e OR ac EC EAE em DR ie Ro RU EN RO NM Using OptQuest oos ees n tei RN LEET desk Le AEA TNE 457 Apacnde A ROCCS SUING cuiu ccce doce ER EOCEROR EX QCE GE e ER CX CC DX RC OE 69 T troductioB is sca sce Reg Pare Per 69 Accessibility Notes er ae es TEP ses OF iv Contents Accessibility of Code Examples in Documentation 00 0000 cee eee 70 Accessibility of Links to External Web Sites in Documentation 70 Enabling Accessibility for Crystal Balle coc 6 sack uasa pua RR E eee ee RR Eu 70 Using the Tab and Arrow Keys in the Crystal Ball Decision Optimizer User oc o Pp EDGR A TRE AA WORE HER OE eRe ae wee eee ee ee eee 70 TITY Access to Orade Support Sere hh avd seas d Rb XAR RO ee we 70 OptQuest Wizard Keyboard Command Equivalents 0 e eee eee eee 70 OptQuest Results Window Ment 414240454196 4 00 4048444624444 4e0m0544 Fs 72 OptQuest Control Panel Keyboard Shortcuts isses edo RECETA EGG E aues 73 GOSS ANY
50. bution as the objective but must characterize the distribution using a single summary measure for comparing and selecting one distribution over another So to use OptQuest you must select a statistic of one forecast to be the objective You must also select whether to maximize or minimize the objective or set it to a target value The statistic you select 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 may become the less stable having a higher standard error of the two and so the median becomes a better measure of central tendency The X in Y Chance statistic can be used only for requirements not objectives 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 may be the appropriate statistic For controlling the shape or range of the objective the skewness kurtosis or certainty statistics may be used If you are working with Six Sigma or another process quality program you may 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 M
51. chnical 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 the use duplication disclosure modification and adaptation shall be subject to the restrictions and license terms set forth in the applicable Government contract and to the extent applicable by the terms ofthe Government contract the additional rights set forth in FAR 52 227 19 Commercial Computer Software License December 2007 Oracle America Inc 500 Oracle Parkway Redwood City CA 94065 This software or hardware is developed for general use in a variety of information management applications It is not developed or intended for use in any inherently dangerous applications including applications that may create a risk of personal injury If you use this software or hardware in dangerous applications then you shall be responsible to take all appropriate fail safe backup redundancy and other measures to ensure its safe use Oracle Corporation and its affiliates disclaim any liability for any damages caused by use of this software or hardware in dangerous applications This software or hardware and documentation may provide access to or information on content products and services from third parties Oracle Corporation and its affiliates are not responsible for and expressly disclaim all warranties of any kind wi
52. d for the objective any requirements any constraints and all included decision variables Running Optimizations 37 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 ofthe number of simulations solutions If any requirements have been specified the line may initially be red indicating that the corresponding solutions are not feasible according to the requirements A green line indicates feasible solutions Once OptQuest finds a feasible solution it is common for this line to show an 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 that accompany 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 72 Sol
53. dd 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 select another d Then review the requirement operator The selected statistic can be less than or equal to a selected value greater than or equal to a selected value or between two selected values including the values Click the underlined limit to select another If you select 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 Optional 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 Note You can create multiple requirements without using all of them at once If you select Exclude that requirement is not used in the current OptQuest optimization Optional If you have an opt file from an earlier version of OptQuest click Import to open the file for assistance in defining new objectives r
54. displayed in the edit box as a sum Setting Up and Optimizing a Model Constraints Type Exclude 1 Aggressive Growth fund Growth and Income fund Income fund Money Market fund go 3 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 Do not use or a comma in constraints See Constraint Rules and Syntax on page 31 for other rules about constraint formulas 5 Click Next to continue The Options panel opens similar to Constraint Rules and Syntax on page 31 Constraints Editor and 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 select 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 in the constraint Reference formula you are creating For more information see Constraints and Cell References in Advanced Entry Mode on page ad Add Comment Displays the Add Comment dialog where you can
55. e 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 47 Selecting the Forecast Objective When the OptQuest wizard starts the Objectives panel opens similar to Figure 11 The first time you start the wizard the Welcome screen opens Click Next to display the Objectives panel In the Objectives panel you select a forecast statistic to maximize minimize or set to a target value Optionally you can define one or more requirements either on the objective forecast or on other forecasts Figure 20 shows a default objective including the first forecast found in the model Note You can define more than one objective but can use only one at a time Select Exclude to eliminate an objective from the current optimization gt To define a forecast objective and optionally define requirements If you have more than one workbook open use the Primary workbook list to select the workbook with data to optimize 2 Click Add Objective A default objective is displayed in the Objectives area 3 Review the default objective definition It has the format Operation Statistic 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
56. ecify requirements Constraints Objectives Options 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 66 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 Figure 20 the default objective has already been added Maximize the Mean of Total Expected Return This is the wanted objective and needs no editing 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 21 60 OptQuest Tutorials Figure 21 Decision Variables Panel with Cell Locations Portfolio Allocation Example OptQuest Review decision variables and change properties as necessary v LowerBound Base Case Upper Bound
57. effectiveness with which you can use Crystal Ball products For more information about all of these resources see the Crystal Ball Web site at http www oracle com crystalball Additional Resources 11 12 Welcome Overview In This Chapter MPT RIN Gnosis MT TT 13 AURAL Ne c quoc e M PER 13 How TPIS WINN mer 14 AOOUE Op rnizabari MBCA occu jest ec pees ee et oed uo bene pass ete Mee Ud delle 15 Dpurisatioh ODIeDI eS cs cess dido ta Eon pae did ducat aule FR RACE MUN PEA 16 Bor EQ lr 17 Fur nea E ES 18 Model and Solution Feastbil B sesser carismei edad bob gba d bna sian pla Pack nd aar cati cundis a 19 o FONBOE ABAE SIS Lis ceased E A it uda POR SERM Rae aba aA UA EE Md EHE UE dd 19 Dpiest ang Process Capabilily ssusiiesi sies orca yer beh CO aad Kal A bane Veo d oxx d Ape dies 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
58. el Specifying Constraints 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 56 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 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 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 Note Youcancreate multiple constraints without using all ofthem at once If you select Exclude that constraint is not used in the current OptQuest optimization 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 22 By default the Constraints panel opens in Simple Entry mode
59. ent developments in optimization have produced efficient search methods capable of finding optimal solutions to complex problems involving elements of uncertainty OptQuest incorporates metaheuristics to guide its search algorithm toward better solutions This approach uses a form of adaptive memory to remember which solutions worked well before and recombines them into new better solutions Since this technique doesn t use the hill climbing approach of ordinary solvers it does not get trapped in local solutions and it does not get thrown offcourse by noisy uncertain model data You can find more information on OptQuest s search methodology in the publication references listed in the OptQuest section of the Oracle Crystal Ball Reference and Examples Guide 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 se
60. enu Page Setup Print Preview and Print Viewing Charts in Crystal Ball When an optimization completes you can select Analyze and then 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 selecting a chart command from the Analyze menu See the Oracle Crystal Ball User s Guide for further instructions 42 Setting Up and Optimizing a Model Creating OptQuest Reports Following an optimization you can create several different types of OptQuest reports gt To create an OptQuest report 1 Run an optimization in OptQuest 2 Select Analyze and then Create Report 3 Inthe Create Report Preferences dialog select one of the following e Full to create a complete OptQuest report including simulation 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 7 shows elements you can choose to include in the OptQuest Results section of a custom report Figure 7 OptQuest Results Settings in the Custom Report Dialog Custom Report R Soe OptQuest Results Details Report Summary t OptQuest Results v Summary v Forecasts Assumptions
61. equirements and constraints For details see Transferring Settings from opt Files on page 47 Optional To delete a requirement click it and then click Delete When objective and requirement settings are complete click Next The Decision Variables panel opens Selecting the Forecast Objective 27 Selecting Decision Variables to Optimize When you click Next in the Objectives panel the Decision Variables panel opens similar to Figure 21 It lists every decision variable frozen or not defined in all open Microsoft 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 may fix the values of certain decision variables and optimize the rest 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 clear 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 select Show Cell Locations the following additional columns are displayed in the Decision Variables panel Cell Address
62. er order 78 Glossary A B C DEF G K L M NO PQ R S TU V W Index Symbols opt files 47 A analysis solution 38 apartment tutorial 51 assumptions defining 25 B bounds defined for decision variables 17 C capability metrics 21 charts viewing 42 charts viewing 42 closing OptQuest 46 commands start pause stop 36 commands keyboard OptQuest 73 constraint editor syntax 31 constraint feasibility defined 19 constraints defined 18 62 defining 29 editor 31 simple entry 29 syntax 31 continuous decision variables 17 creating reports 43 Crystal Ball models creating 24 Crystal Ball charts 42 D data extracting 44 extracting to spreadsheets 44 including in reports 43 decision variables bounds defined 17 defined 13 17 in constraints 31 selecting to optimize 28 step size 18 types 17 Design for Six Sigma 21 discrete decision variables 17 variable step size 18 E efficient portfolios 20 examples requirements 17 exiting OptQuest 46 extracting data 44 extracting OptQuest data 44 F feasibility constraint defined 19 requirement 19 feasible solutions 55 feature changes 24 files optimization name 37 Index 79 A B C D E F G H 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 Futura Apartments
63. es no decision where 0 no and 1 yes Decision Variables 17 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 must 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 numeric 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 to 5 and a 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 t
64. etup Altre g Edit Print Preview Altre r Edit Print Alt e p View Best Solution Alt v b View Solution Analysis Alt v s Analyze Create Report Altta r Analyze Extract Data Alt a d Preferences Show All Solutions Alt p s Help Best Solution Help Altth b Help Solution Analysis Help Alt h s Help Efficient Frontier Help Altth 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 8 following lists the commands and shortcut keys for each of the Control Panel menus Table 8 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 0 Run Predictor Alt r p Run Tools Alt r t OptQuest Control Panel Keyboard Shortcuts 73 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 Altta a Analyze Forecast Charts Alt a f Analyze Overlay Charts Alt a o Analyze Trend Charts Alt a t Analyze Sensi
65. g 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 values lower than the curve represent possible combinations of investments Points above the curve values higher than the curve are unobtainable combinations given the particular set of assets available For any given mean return one portfolio 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 one portfolio has the highest mean return obtainable This portfolio lies on the curve at the point that intersects the standard deviation of return 20 Overview 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 the publication references in the OptQuest section ofthe Oracle Crystal Ball Reference and Examples Guide 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 P
66. he 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 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 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 less than 2 500 Not all optimization models need constraints 18 Overview 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 Notice that a
67. he 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 24 Figure 24 Changing the Constraint Operator Constraints 1 ModeliG13 ra 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 25 following a number was entered 100000 Figure 25 A Constraint Entered in Simple Entry Mode Constraints 1 ModellGi3 100 At this point you can do one of the following e Add another constraint e Add a comment e Add a variable bound for Efficient Frontier analysis e Click Next to continue to the Options panel e Click Run to run the optimization For more information about adding comments and variable bounds see Constraints Editor and Related Buttons on page 31 As an 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 When constraints settings are complete click Next to continue The Options panel opens similar to Figure 16 Setting Options and Running the Optimization In the Options panel you set options for controlling the optimization process For details click the Help button For this tutorial set the maximum number of simulations to 1000 Click Run Tutorial 2 Portfolio Allocat
68. ings Opt File BEE 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 wizard s 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 Deviation 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 EFxls Model IC15 Name Aggressive Growth fund Lower 10000 Base 25000 Upper 100000 Type Continuous Cell Portfolio Revisited EF xis Model IC16 Options With simulation stochastic Enable low confidence testing The objective and any requirements or constraints are displayed at the top Deci
69. ints 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 When you click Next in the Constraints panel or click Options in the navigation list the Options panel opens similar to Figure 16 You can use the Options panel to set OptQuest options 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 1 Select 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 Select Run for simulations or Run for minutes and enter the target value The defaults are 1000 simulations and 5 minutes You can also click Run Preferencesto change settings in the Crystal Ball Run Preferences dialog 34 Setting Up and Optimizing a M
70. ion Model 63 The OptQuest Results window opens Figure 26 Itis displayed in Best Solution view which provides an overview of the best solution found during the optimization Figure 26 OptQuest Results Window Best Solution View Portfolio Allocation Model OptQuest Results Edit View Analyze Preferences Help 1000 Simulations Best Solution View Performance Chart 7 Best solutions Infeasible solutions 4 Lastbestsolution s g s 8 3 Total expected retum Mean 8 90 180 270 360 450 540 630 720 810 900 990 Simulations Best Solution Simulation 140 Grecs Maximize the Mean of Total expected retum 10 387 Constraints Left Side Right Side ModellG13 100000 100000 100000 Decision Variables Aggressive Growth fund Growth and Income fund Income fund Money Market fund The mean of the Total Expected Return forecast 10 387 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 Aggressive Growth fund 90 000 Growth and Income fund 0 Income fund 10 000 and Money Market fund 0 If you select View Solution Analysis in the menubar the Solution Analysis tables are displayed 64 OptQuest Tutorials Figure 27 OptQuest Results Window Solution Analysis View Portfolio Allocation Model OptQuest Results Edit View Analyze Preferences Help 1000 Total Solutions Solution An
71. jective Add Requirement Efficient Frontier Delete lt Back Run Close Help 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 y 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 optimization All your new settings are stored in the workbook and will be saved permanently with the workbook the next time you save it You can store settings in additional workbooks and use them for a single model For instructions see Maintaining Multiple Optimization Settings for a Model in the OptQuest section of the Oracle Crystal Ball Reference and Examples Guide Setting Up and Optimizing a Model Learning More About OptQuest To learn more about OptQuest complete the tutorials in Chapter 4 OptQuest Tutorials Then review the examples in the OptQuest section of the Oracle Crystal Ball Reference and Examples Guide
72. lity The appendix provides a summary of OptQuest s menus and a list of the commands you can execute directly from the keyboard e Glossary This section is a compilation of terms specific to OptQuest as well as statistical terms used in this manual For OptQuest examples information about how OptQuest works and optimizing performance and a bibliography of references see the Oracle Crystal Ball Reference and Examples Guide Screen Capture Notes All the screen captures in this document were taken in Microsoft Excel 2003 for Windows XP Professional using a Crystal Ball Run Preferences random seed setting of 999 unless otherwise noted Because of round off differences between various system configurations you may obtain slightly different calculated results from those shown in the examples Getting Help As you work in OptQuest you can display online help in a variety of ways e Clickthe Help button in a dialog v e Press Fl ina dialog Note In Microsoft Excel 2007 orlater click Help atthe end ofthe Crystal Ball ribbon Notice that if you press F1 in Microsoft Excel 2007 or later Microsoft 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 10 Welcome Additional Resources Oracle offers technical support training and additional resources to increase the
73. ll Reference and Examples Guide 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 68 OptQuest Tutorials Accessibility In This Appendix dy vieles eme tm RE TUER 69 Pere UD NA eeu unicus Rut P E reU mda p aa LU OQ REC A ied A 69 OptQuest Wizard Keyboard Command Equivalents cccccecsceseeseceeeeeeeeaeeeeeeeeees 70 OptQuest Results Window Mens 13 422 tard 03233 xx Extr to edd Gade bd dat ep ed ea EXER eed 12 OptQuest Control Panel Keyboard SROItGUES 1 2 e oices cese ser anat coadsaad rer rh nha ah ane To 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 69 e OptQuest Wizard Keyboard Command Equivalents on page 70 e OptQuest Results Window Menus on page 72 e OptQuest Control Panel Keyboard Shortcuts on page 73 Accessibility Notes Subtopics Accessibility of Code Examples in Documentation Accessibility of Links to External Web Sites in Documentation Enabling Accessibility for Crystal Ball Using the Tab and Arrow Keys in the Cry
74. ls 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 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 Select Run and then OptQuest The OptQuest wizard starts 2 Setup 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 us
75. luated at a previous or later test point in the optimization Once you have chosen a set of solutions to analyze you can click in 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 beside 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 Analysis 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 ofthe 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 Interpreting the Results 41 Conversely a decision variable with a wider relative range indicates that is has a lesser imp
76. mization 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 Run Preferences dialog to determine the confidence 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 cleared off with a value of 500 Note When confidence testing is selected OptQuest can yield different results even when the same seed is selected For complete result equivalence from one optimization to the next do not select Enable Low Confidence Testing 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 Running Optimizations 35 You cannot work in Crystal Ball or Microsoft Excel or make changes in OptQuest when running an optimization but you can work in other programs Do not close Microsoft Excel Crystal Ball or OptQuest while run
77. nel opens with additional information about the optimization 36 Setting Up and Optimizing a Model 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 e Solution Analysis View on page 38 e Efficient Frontier Chart on page 39 Best Solution View Figure 4 shows Best Solution view results for an OptQuest example model Product Mix xls Figure 4 OptQuest Results Window Best Solution View OptQuest Results Edit View Analyze Preferences Help 710 Simulations Best Solution View iw Performance Chart Best solutions Infeasible solutions Lastbestsolution GrossProft Meaa 70 140 210 280 350 420 490 560 630 700 Simulations Best Solution Simulation 210 Objectives Maximize the Mean of Gross Profit 11 695 62 Requirements The 5 Percentile of Casing Remaining must be greater than or equal to Constraints ies Right Side Veal used must not exceed inventory 12510 lt 12520 Pork used must not exceed inventory 14100 lt 14100 Beef used must not exceed inventory 5465 Decision Variables In the Best Solution view the OptQuest Results window shows a performance chart plotting bestsolutions found during analysis It also shows the single best solution foun
78. ning an optimization 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 3 Figure 3 OptQuest Control Panel Control Panel OptQuest Run Analyze Help Running optimization Extreme speed RERRRRRRRRRRRRRRRRRRRRNN Total simulations 645 BENNNN Trials 1 000 gt 8 iP 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 Opens a dialog for controlling optimizations Start or Continue Starts a new optimization or continues a paused optimization Pause or Stop iml Pauses or stops the current optimization Reset q q 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 ate If you click the More button 7 a pa
79. o constraints on the decision variables so do not add any here 1 Click Next in the Constraints panel The Options panel opens 54 OptQuest Tutorials Figure 16 Options Panel Futura with OptQuest Example OptQuest Welcome T one Choose your options and run the optimization Objectives Decision Variables Optimization control Type of optimization Constraints a Run for 1000 simulations 9 With simulation stochastic QhRunfo 5 minutes Without simulation deterministic Simulation Run Preferences While running Decision variable cells Q Show chart windows as defined 9 Leave set to original values 9 Show only target forecast windows Automatically set to best solution v Update only for new best solutions 8 Set OptQuest to run for 1000 simulations 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 In a short time OptQuest finds the best solution and displays the OptQuest Results window Figure 17 Figure 17 OptQuest Results for Futura Apartments Model OptQuest Results Edit View Analyze Preferences Help 201 Simulations Best Solution View Performance Chart g 8 8 77 Best solutions Infeasible solutions Lastbestsolution Profit or Loss Mean g 3 8 g 3 83 104 125 146 167 188 Simulations
80. odel Option Description Type Of Select With simulation stochastic to run a simulation on the assumption variables or select Without Optimization simulation deterministic to use the base case cell value for the assumption cells While Running Settings that control chart window display Select Show chart windows as defined for maximum information or Show only target forecast window for fastest performance Update only for new best solutions is selected by default to enhance performance and will only show results related to the best solution Clear this setting to see the forecast results for each solution Decision Variable Cells Select Leave set to original values to keep the original base case values in decision variable cells the default At the end of an optimization you can copy any solution OptQuest tried including the best solution to these cells if you want Select 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 stop a simulation early if the target 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 opti
81. ome OptQuest solutions data rows have been omitted to show the OptQuest statistics data Figure 9 Extracted Data from Hotel Design xls Ell Book2 m DER Objective Requirements Decision Variables Maximize Mean 80 Percentile lt 450 00 Solution _ Total Revenue Total room demand Gold price Platinum price Standard price 57 110 00 133 00 80 00 109 00 134 00 80 00 110 00 134 00 80 00 109 00 135 00 80 00 110 00 135 00 80 00 108 00 136 00 80 00 110 00 136 00 80 00 109 00 137 00 80 00 105 00 129 00 81 00 110 00 137 00 80 00 110 00 138 00 Decision Variables Statistics Gold price Platinum price Standard Minimum 91 00 125 00 Mean A 106 82 135 29 Maximum 406 110 00 149 00 Std Dev E 5 46 5 39 Notes Extracted data for top 596 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 select Analyze and then 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 op
82. on 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 51 To begin the tutorial 1 Start Crystal Ball 2 Open the Futura With OptQuest xls workbook from the Crystal Ball Examples Guide Figure 12 Figure 12 Futura Apartments Workbook Ell Futura with OptQuest xls Futura Apartments with OptQuest Rent per Unit Monthly Expenses Profit or Loss 2 500 00 Number of units Number of Units Rented 35 4 0 1 Rent per Unit 85 Price demand parameters Slope Intercept Notice that the rent is set to 500 where Number of units rented 1 500 85 235 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 and then Run Preferences and
83. onstraint row into edit mode so individual elements can be accessed and edited Table 4 OptQuest Wizard Keyboard Shortcuts Objectives Decision Variables and Constraints Panels Command Panel Keystrokes Add Comment Constraints Alt c Add Constraint Objectives Simple Entry Alt o Add Objective Objectives Alt o Add Requirement Objectives Alttr a Advanced Entry Constraints Alt a Back All but Welcome Alt b Close All Alt c Constraints group Constraints Alt s Add Constraint Constraints Alt o Delete Objectives Constraints Alt d Efficient Frontier Objectives Constraints Altte Exclude Objectives Constraints Alt x Help All Alt h Import Objectives Altti Insert Reference Constraints Advanced Entry Alt r Insert Variable Constraints Advanced Entry Alt v Next All but Options Alten Objectives group Objectives Alt j Primary Workbook Objectives Alt p Requirements group Objectives Alt q Run All but Welcome Alter Show Cell Locations Decision Variables Alt s Table 5 lists Options panel settings See Table 4 for Options panel buttons OptQuest Wizard Keyboard Command Equivalents 71 Table 5 OptQuest Wizard Keyboard Shortcuts Options Panel Settings Command Keystrokes Optimization Control settings n a Runfor simulations Alt u Run for X minutes Alt n Run Preferences Alt p Type of Optimization settings n a With simulation stochastic Alt w
84. ortfolios with values lower than 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 56 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 balancing risk and reward You can also use other criteria for selecting portfolios Instead of using the mean return you could select the median or mode as the measure of central tendency 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 may 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 selecting Calculate Capability Metrics on the Statistics tab of the Run Preferences dialog
85. 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 profit with limited risk Crystal Ball Decision Optimizer with 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 the 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 Appendix A Accessibi
86. quirements 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 or which projects to select from among a limited set e Constraints Optional restrictions placed on decision variable values For example a constraint may ensure that the total amount of money allocated among various investments About Optimization Models 15 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 56 Optimization Objectives 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 distri
87. ride precedence If you are using Simple Entry mode you are creating formulas in Microsoft Excel and Microsoft Excel s precedence rules apply Note Constraint formulas with cell ranges such as A1 A3 lt B1 B3 are not supported in OptQuest This is a shorthand notation for defining three constraints Al lt B1 A2 lt B2 A3 B3 The three constraints can be entered separately to define the same target as the cell range formula Constraints and Cell References in Advanced Entry Mode Specifying Constraints in Simple Entry Mode on page 29 describes how you can create formulas 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 1 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 2 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 then click OK 6 Following the cell reference type the relationship operator 7 Click Insert
88. ry Example To enter Advanced Entry mode select Advanced Entry in the Constraints panel of the OptQuest wizard A Constraints edit box opens 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 Allocation Model on page 56 Constraints Editor Example To create this formula 1 30 Click Insert Variable The Insert Variable dialog opens Figure 2 Insert Variable Dialog Portfolio Allocation Model Insert Variable Choose one or more decision variables to insert into the constraint View Fs E Select Fal m 1G Portfolio Allocation xls amp C3 Model 2 Mea v Aggressive Growth fund v 4f Growth and Income fund Q Income fund Q Money Market fund OK Cancel Help L ok ce Since you want to include all four decision variables in the constraint formula select each name To select all four at once select the box in front of Decision Variables Then click OK The variables are
89. s in the model OptQuest models must have at least one decision variable 1 Define the first decision variable a Select cell C13 b Select Define and then 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 19 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 2 Define the decision variables for cells C14 C15 and C16 according to the values in columns D and E of the worksheet by following the process described in step 1 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 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 1 Start OptQuest by selecting Run and then OptQuest You have probably already started OptQuest at least one time so the Objectives panel opens Figure 20 Tutorial 2 Portfolio Allocation Model 59 2 Figure 20 ObjectivesPanel Portfolio Allocation Example Objective Added OptQuest Decision anani Primary workbook PoriokoAlocaonois Select an objective and optionally sp
90. sion variables and options are displayed at the bottom Transferring Settings from opt Files 47 48 If the opt file contains a variable requirement for Efficient Frontier analysis it is displayed near the decision variables and is labeled Efficient Fronter as shown in Figure 10 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 requirements add those and edit them to match the text Figure 11 shows how to enter the objective and the requirement labeled Efficient Frontier in Figure 10 Figure 11 Objectives Panel Portfolio Revisited EF xls OptQuest Welcom a Select an objective and optionally specify requirements e Doce Varin Primary workbook Portfolio Revisited EF xis Constraints Objectives 9 Options Maximize the Mean of Total expected return Requirements The Standard Deviation of Total expected return mustbe less than orequalto 8 000 dollars Efficient Frontier varythe bound from 8 000 to 10 000 insteps of 250 dollars Add Ob
91. ssive 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 SE Oe A Oy SUP Gee ie a Interpret the results Creating the Crystal Ball Model In this case the model has already been created for you To review it 1 Start Crystal Ball and open the Portfolio Allocation xls workbook from the Examples Guide The worksheet for this problem is shown in Figure 19 following Tutorial 2 Portfolio Allocation Model 57 Figure 19 Portfolio Allocation Worksheet E Portfolio Allocation xls Portfolio Allocation Model Annual Lower Investments return Money Market fund 50 000 Income fund 25 000 Growth and Income fund 80 000 Aggressive Growth fund 100 000 Total amount available 100 000 Amount T REP RS Total amount Decision variables invested uS invested Money Market fund 25 000 100 000 Income fund 25000
92. stal Ball Decision Optimizer User Interface TIY Access to Oracle Support Services The topics listed previously discuss Crystal Ball and OptQuest accessibility features Introduction 69 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 open 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 of 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 including OptQuest it is always in accessible mode OptQuest charts and other output can be extracted to Microsoft Excel spreadsheets and pasted into PowerPoint slides which are accessible through Microsoft Office For information about Microsoft 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
93. t The following are some examples of requirements on forecast statistics that you could specify 95th percentile 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 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 Microsoft Excel 2007 or later 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 o Binary A decision variable that can be is 0 or 1 to represent a y
94. t 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 wanted objective so no further editing is necessary Click Next to continue The Decision Variables panel opens as shown in Figure 14 Tutorial 1 Futura Apartments Model 53 Figure 14 Decision Variables Panel Futura with OptQuest Example OptQuest Welcome Objectives Constraints Options Review decision variables and change properties as necessary amp gt Show cell locations Decision Variables Lower Bound BaseCase Upper Bound Type Step Freeze r Unit 400 00 500 00 600 00 Discrete 1 00 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 selected this decision variable will be included in the OptQuest simulation 6 Click Next to continue The Constraints panel opens as shown in Figure 15 Figure 15 Constraints Panel Futura with OptQuest Example OptQuest Welcome Objectives Decision Variables Options Optionally specify constraints on the decision variables Efficient Frontier This example has n
95. te 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 select of Solutions and enter 10 in the box You can select whether to include feasible infeasible or all solutions If you have requested an Efficient Frontier analysis you can select 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 next to a column heading to condense sections and show more columns onscreen You can also click in 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 requirement for the optimization an Efficient Frontier tab opens with the Performance Chart tab in Best Solution view Figure 6 Running Optimizations 39 Figure 6 Efficient Frontier Chart Best Solution View OptQuest Results Edit View Analyze Preferences Help 2000 Simulations Best Solution View Efficient Frontier Performance Chart Efficient Frontier Efficient frontier Best solutions v Infeasible solutions 8 000 8 500 9 000 9 500 10 000 Total expected return
96. th respect to third party content products and services Oracle Corporation and its affiliates will not be responsible for any loss costs or damages incurred due to your access to or use of third party content products or services Contents Dacumestaton Accessibility iuisnus duse dd rzdnh RERO EORR ok Rug ups RR aa ER Ec RR P ress 7 Chapter 1 Welcome 25 4240540046 cae badaee de bad SOMO DA TES Ode dE ENG ROEDER OEE SRS 9 DotEDO HC DH acd oe De Se EW ede dea os PERE WR PRR TORE OR es 9 How the Minoal oL PIER Loic CERE Bd EE Ra dor dM b d Gees 9 Mar CAE NMED CPP pPI r 10 Loc ms Th ouuss e ARP AY Ra d tAS SX ATA RRPTOERESSa E SAGE TERT MESSRS RE SS 10 EO WIST i ERRIPEICI rP r 11 Chapter 2 varvibW iunc xu Ru eke ERO een en HA dee hee ORS eho Ree e Kad ee es See 13 loodu o MC 13 What Opi ues DOES ccc maeexeihetqepqqebeqRqQxespeqeqauseqdpmgEexqqup as 13 Hos iue WORS ee dey Pe ee aene RR do PE ae Re PRR d 14 About Optimizam ModE cc rey tes WR eee ROI ee ON UR RO POR R GRE Ate 15 Op rmeation DRIVES esa sies ed EX rA EE SEU ERRARE R MX qp Ud s 16 For castotatisli S uie em Ro RA RR HORROR RARE E AU ORO ERRORS 16 Minimizing or Maximizing aa acd goed ees ge eed eR Ree eee de a 16 Reguircnicnlls sneeicaeeensteeeseredeeubenedeaedugedeeedegeseasdans I7 Decision Yanabl s 2 edsmeeebe RE ROS EAR Ree E wehbe twee ee
97. timizations 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 Saving Optimization Models and Settings 45 If you choose to copy optimization values to the model these values are displayed as the new cell values and are also saved when the model is saved Each workbook can have one set of optimization settings Ifyou click Closein the OptQuest wizard before you run an optimization OptQuest asks whether to save the settings If you respond Yes current settings 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 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 ill
98. tivity 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 h 74 Accessibility 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 maximum or minimum limit you set for each 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 to a 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 indic
99. tutorial 51 G getting started 51 guidelines for using OptQuest 57 H help 10 how OptQuest works 14 how this manual is organized 9 icons OptQuest 73 importing settings from opt files 47 K keyboard commands OptQuest 73 L Lean principles 21 M mathematical operations in constraints 31 maximizing forecast statistic 16 menus OptQuest 72 minimizing forecast statistic 16 models 15 creating 24 optimization defined 15 setting up 23 80 Index MNO P Q R S TUVW new features 24 0 objectives defined 60 selecting forecast 26 using forecasts as 16 online help 10 operations mathematical in constraints 31 optimization process overview 23 optimizations model defined 15 running 35 starting and stopping 36 options selecting 34 OptQuest flow 15 how it works 14 keyboard commands and icons 73 options 34 steps to use 23 what it does 13 OptQuest menus 72 OptQuest results printing 42 OptQuest guidelines for using 57 OptQuest starting 26 organization manual 9 P pause command 36 Portfolio Allocation tutorial 56 portfolios efficient 20 preferences suggested run 25 printing OptQuest results 42 printing results 42 process capability 21 process optimization 23 Q quality programs 21 A B C DEF R ranges decision variable 17 remaining time viewing 37 reporting on OptQuest data 43 reports creating 43 using OptQuest data
100. ustration of an Efficient Frontier chart see Figure 6 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 gt To define a variable bound for Efficient Frontier analysis 1 Inthe Objectives panel select an existing requirement to modify or add a new one and select it Alternately select a constraint in the Constraints panel 2 Click Efficient Frontier 3 An Efficient Frontier row opens near the requirement or constraint Adjust the underlined elements to define a range of values for one or both bounds of the requirement or constraint 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 56 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 46 Setting Up and Optimizing a Model However if the investor wants to see
101. ution Analysis View In Solution Analysis view the OptQuest Results window lists the best solutions found during the optimization By default the top 596 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 select View and then Solution Analysis in the OptQuest Results window menubar 38 Setting Up and Optimizing a Model Figure 5 Solution Analysis View OptQuest Results Edit View Analyze Preferences Help 710 Total Solutions Maximize Mean 5 Percentile gt 0 00 Veal used must notexcee px Gross Proit C Remaining SUMPRODUCT Model C amp Cil 1 11 695 62 12510 11 561 52 11 520 20 11 508 02 11 492 60 11 483 07 11 478 61 11 464 04 11 460 91 11 456 28 11 443 97 11 442 12 11 442 08 11 429 61 11 419 51 11 415 73 11 282 10 11 417 88 180 76 11415 83333333 11 695 62 95 13 133 70 1456 574013616 Include v Feasible solutions 577 5 of solutions C Infeasible solutions 133 All feasible solutions 577 New best solutions 6 In the Show The Best group indica

Oracle® Crystal Ball Decision Optimizer

Contents

Download Pdf Manuals

Related Search

Related Contents

Oracle&reg; Crystal Ball Decision Optimizer

Contents

Download Pdf Manuals

Related Search

Related Contents

Oracle® Crystal Ball Decision Optimizer