Monte Carlo Simulation in Crystal Ball 7.3
Contents
1. Cancel Defaults Help It is possible to run user defined macros in different stages of the simulation which can be activated under Options gt Run user defined macros The macro facilities is further described in the section Macro facilities Moreover more information can be found in the file 01_Crystal Ball 7 3 User Manual pdf in the file package Another important option is the stop criteria It is possible to make the simulation stop when a certain confidence level is reached Under the Sampling tab it is possible to make Crystal Ball use the same sequence of random numbers for the simulation This is done by ticking off use same sequence of random numbers During the setup of a model it can be convenient to be able to replicate the exact simulation again This can be done by using the same initial value for the simulation In addition this can be useful for presentation purposes The last thing that should be mentioned under Run Preferences is the Options tab Here it is important to remember to tick off Store Assumption Values for Sensitivity Analysis if it should be possible to make sensitivity charts after the simulation AARHUS S AJ UNIVERSITY E E Monte Carlo Simulation in Crystal Ball 7 3 Analytics Group 12 7 Macro facilities If one has special requirements for the simulation that Crystal Ball is not able to handle the macro facilities might be help ful As the figure below suggests the2. Crystal Ball which is an Add in for Microsoft Excel is made by Decisioneering www decisioneering com Through itera tions the program makes it possible to define assumptions for the input cells in contrast to Excels static cells which can only be one specific value Therefore the program is excellent for simulating for example budgets For a budget the varia bles the inputs sales and price can be uncertain for the coming period The simulation is made by defining distributions for the outcomes in each input cell and thereafter specifying the output cells which Crystal Ball should collect information about In the budget case the output could be the result for the coming period which Crystal Ball will then be able to cal culate statistics on and generate graphs for the result This manual describes firstly the basic functions used for simulations and thereafter an example of the structure of a spreadsheet used for simulations in Crystal Ball In connection to the manual there is a package with links and files that can be useful in connection with the manual Please notice that the manual is based on Excel 2007 but the exact same functions keys menus etc is in Excel 2003 Reports about mistakes shortcomings and requests for additional support can be addressed to Analytics Group at analyt ics asb dk AARHUS S AJ UNIVERSITY Ey T Monte Carlo Simulation in Crystal Ball 7 3 Analytics Group 2 2 Crystal Ball and simulations With C
3. Maximum 751 686 95 Range Width 719 268 43 Mean Std Error 4 428 59 Percentiles Forecast values 0 32 418 52 10 104 295 25 20 133 895 02 30 159 114 04 40 185 178 48 50 208 245 10 60 236 609 53 70 268 914 48 80 310 464 06 90 468 685 01 100 751 686 95 Forecast resultattotal Cell E8 Summary Entire range is from 142 931 41 to 903 414 33 Base case is 590 000 00 After 1 000 trials the std error of the mean is 4 349 37 AARHUS S AJ UNIVERSITY E Ea GG Monte Carlo Simulation in Crystal Ball 7 3 Analytics Group 33 resultattotal S amp S 2 0 04 F Pd 0 Statistics Trials Mean Median Mode Standard Deviation Variance Skewness Kurtosis Coeff of Variability Minimum Maximum Range Width Mean Std Error Percentiles 0 AARHUS UNIVERSITY Forecast values 1 000 437 275 87 423 991 11 137 539 15 18 917 018 707 19 0 6728 3 35 0 3145 142 931 41 903 414 33 760 482 92 4 349 37 Forecast values 142 931 41 Monte Carlo Simulation in Crystal Ball 7 3 Analytics Group 34 10 272 251 04 20 321 998 05 30 353 705 47 40 394 361 99 50 423 950 05 60 450 625 92 70 489 627 38 80 539 245 04 90 622 653 94 100 903 414 33 End of Forecasts Assumptions Worksheet CB EX XLSJEKSEMPEL Assumption produktionspris00 Cell C13 produktionspris00 Uniform distribution with parameters R Minimum 4 0 i Maximum 6 0 40
4. INF Certainty TE 4 INF The results that can be shown in the graph are frequency cumulative frequency and reverse cumulative frequency Moreover Crystal Ball can also show descriptive statistics and frequency tables An example of a frequency can be seen in the section Forecasts The figure shown above is a cumulative frequency dia gram Under the menu View it is possible to change between different types of histograms and statistics Under Prefer ences it is possible to specify parameters such as decimals number of columns in the graph and whether it should be a line or columns AARHUS S AJ UNIVERSITY E T Monte Carlo Simulation in Crystal Ball 7 3 Analytics Group 15 8 2 Trend Charts This type of chart can be found under View charts in the Crystal Ball menu The chart will display the probability intervals for the Forecast cells besides each other so they can be compared It is possible to get intervals from O 100 with inter vals of 5 In addition to this it is possible to specify whether the probability band should be upper lower or double sid ed This chart gives a measure of the variation in the output data Trend Chart 1 Edit View Trend Preferences Help 1 000 Trials Centered on Median View Trend Chart 1 500 000 00 Certainty Bands 400 000 00 Ely 25 50 300 000 00 200 000 00 700 000 00 OO yess LOEHNSSA The chart can for example be used to see if income exce
5. In the menu point Run the setup and actual simulation is made During the setup many different parameters can be defined Moreover it is possible to use four different macros which have different properties during the simulation se the description in the section Macro facilities Under Tools there is a number of advanced options to extent the simple simulation including among other thing OptQuest which makes it possible to combine simulation with optimization for example linear programming to be able to optimize in the case of uncertain parameters The toolbar contains the following functions A D Pi These are the buttons that are used for defining one or more cells as Assumption or Forecast Define Assumption and Define Forecast When defining an Assump Define Define Define Assumption Decision Forecast tion the cell should contain a constant number on beforehand In the case of a Forecast cell the cell should contain a formula If more then one cell is selected each cell will be defined one at a time one does thereby avoid selecting each cell separately The Define Decision key is used when one wants to optimize under uncertainty in OptQuest or in a Decision Table ae Copy The Copy Paste and Clear keys can be used for copying pasting and clearing 74 Paste the properties of the cells For example if a cell is defined as being normally distrib CP Clear uted and another cell shoul
6. macros can be initialized at five different stages of the simulation CBBeforeSimulation CBBeforeTrial CBAfterRecalc CBAfterTrial CBAfterSimulation A simulation has a startup face where Crystal Ball does not do anything Hereafter random numbers are generated for the Assumptions cells then the spreadsheet is recalculated and the values in the Forecast cells are updated This is done until a stop criteria is fulfilled and Crystal Ball will either set the Assumptions cells to the initial value or the mean It is between these stages that it is possible to initialize user defined macros It is done by naming the macro s according to the names in the figure above e g CBBeforeSimulation Thereby Crystal Ball can recognize the macro and start it at the specific place in the simulation It is worth mentioning that in most instances it is possible to make the necessary specifications by using normal functions in the spreadsheet But in any case the use of macros will be illustrated with a breif example AARHUS S AJ UNIVERSITY E T Monte Carlo Simulation in Crystal Ball 7 3 Analytics Group 13 Consider the setup where one wants to observe the inventory at the end of a period C2 described as the inventory at the beginning of the period B2 e g B2 100 in period 0 the net purchase A2 i e C2 B2 A2 An iteration will thus represent one period For simplicity the net purchase in each period will be a random number from a norm
7. of distribution param eters 4 Name of the distribution pa rameter indexed by ParmNumber 5 Value of the distribution pa rameter indexed by ParmNumber 6 Value of the lower truncation point 7 Value of the higher truncation point 8 Extreme value distribution flag O Minimum 1 Maximum 10 True if assumption is frozen False if it is not The function calculates coefficient of corre AssumRefer ence Col Row AARHUS UNIVERSITY su lation between two Assumption cells the first specifies by AssumRef and the other specified by column number and row num ber If one wants more detailed correlation output CBTools Correlation Matrix can be used _ S I G Hy I Cc CT E Monte Carlo Simulation in Crystal Ball 7 3 Analytics Group 20 See the 02_Crystal Ball 7 3 Developer Kit User Manual in the file package for more information about the Excel func tions http aln hha dk ita manualer CB file package zip 9 2 OptQuest OptQuest is one of the modules tools in Crystal Ball that can be used to optimize given uncertainty That means that on through OptQuest can combine classic linear programming LP with a simulation and thereby optimize under uncertainty This can typically be relevant in corporate decision making where for example the demand delivery time and vast in the production are all uncertain in the LP model OptQuest can be found under Tools in the menu be aware that OptQ
8. periods likely outcome Lik and maximum Max AARHUS S AJ UNIVERSITY T Monte Carlo Simulation in Crystal Ball 7 3 Ca YY Analytics Group TS Similar for other distributions CB GetForeStatFN Fore Ref Index CB GetAssum FN As sumRefer ence Index ParmNumber optional CB GetCorrelationFN The function draws Forecast statistics from the iterations When one works with many Forecast cells it can be confusing with many open windows and difficult to copy the numbers to the spreadsheet For the Fore cast in the cell ForeRef the function calcu lates statistics Index specified by the num bers shown to the right The function draws statistics on the Assump tions cells equal to CB GetForeStatFN AssumReference refers to the output type see index possibilities to the right ParmNumber is not nessesercy but can be used to specify the distributionparameter that index 4 or 5 refers to These functions are used in the post simulation analysis This makes it possible to use results or statistics output input from the simulation as input in the model or for analysis purposes Index values 1 number of trials 2 mean 3 median 4 mode 5 standard deviation 6 variance 7 skewness 8 kurtosis 9 coefficient of variability 10 minimum 11 maximum 12 range max min 13 standard error Index values 1 Assumption name 2 Assumption distribution type index value 3 Number
9. to enable and disable output Ba windows in OptQuest The window Status and Solu tions for example shows the optimal solution after the optimization Performance Graph shows the progress of the optimization The first icon links to the Wizard which is an im portant tool in the process of setting up the model the wizard will be further dealt with later The other icons are used for choosing the decision variables constraints forecasts and options these are also the steps that the wizard goes through AARHUS S AJ UNIVERSITY S 7 T Monte Carlo Simulation in Crystal Ball 7 3 Analytics Group 21 The first three icons control the optimization process with Start Pause and Stop The last button links to a window where one can analyze the solution When the optimization is started it is important that the decision variables assumption and forecasts are defined in the spreadsheet before OptQuest is started The decision variables are the cells that can be changed in order to find the best solution The Assumptions cells specify the uncertainty of the parameters and the Forecast cells are used for the cells that should be maximized minimized in the LP problem in addition to other constraints When OptQuest is opened from the Crystal Ball menu in Excel the first window will be blank To start the process one should either open a saved optimization file or start a new project For setting up the model in Op
10. 42 44 45 48 50 52 54 56 58 69 Assumption produktionspris01 Cell D13 produktionspris01 Uniform distribution with parameters Minimum 4 0 Probability Maximum 6 0 40 42 44 46 48 5 0 52 54 56 58 69 AARHUS S AJ UNIVERSITY E L Monte Carlo Simulation in Crystal Ball 7 3 Analytics Group 39 Cell C12 Assumption afs tning00 afs tning00 Normal distribution with parameters Mean 100 000 3 Std Dev 100 Correlated with Coefficient afs tning01 D12 0 50 Assumption afs t ning01 Cell D12 afs tning01 Normal distribution with parameters Mean 100 000 amp Std Dev 100 1 Correlated with Coefficient 0 50 afs tning00 C12 Cell C14 Assumption reklamepavirkning00 AARHUS S A UNIVERSITY EEN T Monte Carlo Simulation in Crystal Ball 7 3 Analytics Group 36 reklamep virkning00 Triangular distribution with parameters Minimum Likeliest 09 09 10 10 19 11 11 11 11 12 12 12 13 13 Maximum Assumption reklamep virkning01 Cell D14 reklamep virkning01 Triangular distribution with parameters Minimum Likeliest 09 09 10 10 19 11 11 11 11 12 12 12 13 13 Maximum End of Assumptions AARHUS S AJ UNIVERSITY T UT Monte Carlo Simulation in Crystal Ball 7 3 Analytics Group 37 11 File package and literature This manual is as mentioned limited to the most basic functions in Crystal Ball Hence functions like CB Predic
11. G mm san nn Sn Popo Sana 0116 25 3309 32 1000 000 1000 000 1000 000 1000 000 1000 000 0376 92 2999 09 U UUUUU 233 146 2299 95 1484 23 1010 02 10662 4 064 697 0 00000 1371 75 1250 67 1660 34 1534 67 r 115667 5 69 3162 2659 56 1861 76 1144 01 1219 12 Be E i E i S PTonter When the optimization is done it is asked whether one wants to expand it with more simulations and thereby try to get a better solution To copy the solution to Excel chose Edit gt Copy to Excel Thereby the optimal values for the decision variables will be copied to the cells in the spreadsheet If one wants more detailed analysis of the solutions the Solution Analysis can be used This can be found in Run gt Solution Analysis AARHUS S AJ UNIVERSITY BEES SEES ESS SSS Monte Carlo Simulation in Crystal Ball 7 3 Analytics Group 26 10 Example A company that exports high quality glass balls whishes to make a simulation of the result of a new production The lifespan of the new product will be 2 years and the cost per unit is not fixed and the effect of an advertising campaign is not known It has been decided to have a fixed sales price for the first year The price in the other year is dependent on the size of the sale in the first year The number of sold glass balls is also an uncertain parameter Information about the case Year 2000 Year 2001 Price 295 9 95 if the sale in 2000 is larger than 1 million otherwise 12 95
12. RHUS S AJ UNIVERSITY DE Monte Carlo Simulation in Crystal Ball 7 3 Analytics Group 23 Forecast Selection Select an objective and any requirements regs must have a bound Mean _ Eo oo Mean oP Memo The previous steps have illustrated how OptQuest can be tailored to the specific model When these hasve been put in Variable Selection Constraints and Forecast Selection there is only one step left before the optimization can start that is settings for the simulation Press OK this will take you to the last step Options AARHUS S AJ UNIVERSITY Ey SSS Monte Carlo Simulation in Crystal Ball 7 3 Analytics Group 24 9 2 4 Options Under Options it is possible to define the settings for the optimization process which is the last part of the wizard The Options window looks like the following screenshot Options Select allowable running time for optimization Ole SS e fg coo too The settings can be specified with regards to time preferences and advanced setting which can be found in the tabs in the top of the window Since optimization with uncertainty can be a time consuming process especially if the model is complex and if one wants an exact solution it can be useful to specify the time of the optimization This can be done in the Time tab for example by specifying the number of simulations In this example the simulation runs 100 times in many cases it might be better to increase t
13. T SSS Monte Carlo Simulation in Crystal Ball 7 3 Analytics Group Monte Carlo Simulation in Crystal Ball 7 3 Authors Niels Jacob Haaning Andersen Jeppe Brandstrup Last updated May 2008 AARHUS S AJ UNIVERSITY T SSS Monte Carlo Simulation in Crystal Ball 7 3 Analytics Group Table of contents BIC R Re p 9 ean AA PAR oy ea ey ey e a EIRE BA 2 3 USERSINTERF 0 okt a enh ee ht AA ee ee ee PAGA eee eee ds 3 e a e TT 6 RR ae I gym a T 6 SO ala gio siv ka L OO 2A PI AA 7 ere e alite e kocc Bl ele e 8 SAE a SA ee ee ee ee ee ee A E E 9 R RICH Aah a ARA 11 PABABA TIEG coisinn iiaii iiia Eia nS inanasan inisiasi 12 TE ULE ANO FER AA RA PN NN AN ARA 14 Bo PEO AA 14 BTT AA AA 15 BG MU E e HHT 16 8 4 Extract Data raw numbers statistics percentiles and FrEQUENCIES sese 16 HHT 17 9 CRYSTAL BALLS ADVANCED INTERET aoao 18 Map re e IK ca a AA A E E etre ee ie Sorter tr ren 18 sala Te ren Rn ere AA AA 18 AA E T A A L EEA AEE E T 20 CA HR a e RS e E E EEE T 21 CAE E AA AA 22 CE H dele a e a EEN E ESSENS TELEEN 22 CED e a AA 24 SANA AA AA 24 IHE 26 IER 2182 PACKAGE AND aaa 37 AARHUS S AJ UNIVERSITY _ vc a pnta Monte Carlo Simulation in Crystal Ball 7 3 Analytics Group 1 Introduction This manual is made in order to give an introduction to the basic functions in Crystal Ball and is primarily targeted towards bachelor students who use Crystal Ball in the course Management Science Models Erhvervs konomi
14. Units sold Normal distribution with a mean 100 000 and standard deviation 100 with a correlation between the years of 0 5 Cost per unit Unifrom distribution between 4 and 6 Effect of advertising campaign Triangular distribution with mini Same as the year before mum of 0 9 most likely outcome 1 1 and maximum 1 3 Cost of campaign 350 000 each year Interest rate There is no interest rate in the model This can be set up in a spreadsheet as the one shown below where the different cells are defined in Crystal Ball as As sumptions and Forecasts These are all shown and described in the figure below It is worth mentioning that the price in the second period is controlled by an IF function C9 AARHUS S AJ UNIVERSITY Monte Carlo Simulation in Crystal Ball 7 3 Analytics Group 27 B G D k F G H 7 2 Results report 3 2000 2001 Total A Sales 995 000 1 295 000 2 290 000 E Cost of sales 500 000 500 000 1 000 000 6 Gross profit 495 000 795 000 1 290 000 NAMAMANA T Cost of advertising 350 000 350 000 700 000 0 Result Korretation 0 5 9 10 Assumptions 11 Price 1 Units sold 43 Cost per unit 14 Effect of advertising 15 16 a Assumptions 19 20 21 33 The report below is a printout of the report that Crystal Ball has made after the simulation This is just a part of the details that can be used in the report But the report makes it possible to interpret the results from the model and at the same time se
15. advanced possibilities In this section the functions that are added to the usual functions in Excel will be presented Furthermore the most useful modules in the CBTools menu will be presented The modules that will be dealt will in this manual are only those that are mostly relevant for bachelor students 9 1 Crystal Ball functions With the Crystal Ball add in the number of functions in Excels will be expanded You will probably be familiar with the Excel functions These can either be inserted manually by starting with or by using the icon The Crystal Ball functions can be used as all the other Excel functions by writing the function name and in parentheses specifying the inputs In the Fx dialog box that pops up when one chooses to insert a function the Crystal Ball functions can be found in the category Crystal Ball The Crystal Ball functions can for example be used to draw random numbers ie Instead of Assumptions The big ad vantages by using the functions rather than the drag and drop way through the Crystal Ball menus is that the functions are dynamic like the Excel functions If one for example needs to draw random numbers for the demand in many periods one can easily copy this to the other cells Thereby one avoids clicking though a lot of windows Functions can furthermore include references to other cells like ordinary Excel functions Thus dynamic input values can be used rather than static values Besides the a
16. al distribution with a mean of 0 and standard deviation of 20 For the next period the inventory will be the inventory from the previous period This setup demands a macro in order for Crystal Ball to handle it Therefore a macro is record ed programmed which copies the value of the ending inventory and inserts it in the beginning inventory cell in Excel Paste Special where Values is selected Remember to name the macro as described above Now it will be possible to make the simulation in Crystal Ball which is now capable of handling the simulation With the macro facilities it is thus possible to expand the options in Crystal Ball which can be very useful in larger and complex simulation models AARHUS S AJ UNIVERSITY _ EL SEES cccceccG SSS SSS Monte Carlo Simulation in Crystal Ball 7 3 Analytics Group 14 8 Results and reports Below is a presentation of the possibilities and functions that Crystal Ball offers in connection to presenting the results from the simulation 8 1 Histograms During the simulation Crystal Ball can show the preliminary results for each cell which is defined as a Forecast cell These results will slowly be build up while the simulation is running Forecast Gross Profit Edit View Forecast Preferences Help 1 000 Trials Cumulative Frequency View 991 Displayed Gross Profit o o 7 EIS D 5 5 E 5 O Mean 58 158 20 7 500 00 7 800 00 8 100 00 8 400 00 p gt
17. best Distribution Gallery Cell E7 Edit View Categories Help JA Normal Trianqular Uniform Lognormal BetaPERT Normal Description The normal distribution describes many natural phenomena such as Is people s heights the inflation rate or errors in measurements tis a continuous probability distribution The parameters for the normal distribution are mean and standard deviation There are three conditions underlying the normal distribution 0k cancel nf Help When the distribution has been chosen the specific parameters for the distribution should be specified For this purpose a window pops up after the distribution has been selected Here the input parameters for the specific distribution can be put in the boxes below the chart AARHUS S A UNIVERSITY E SSS Monte Carlo Simulation in Crystal Ball 7 3 Analytics Group Fi Define Assumption Cell B7 Edit View Parameters Preferences Help Hame B2 Normal Distribution 5 m Hur L I CL p gt INF E d INF E7 Mean JODI f Sid Dev 0 10 35 OF Cancel Ene Gallery Correlate Help In the box above the figure a name for the Assumption cell can be put This will increase the easiness of reading printouts and reports Crystal ball will suggest a name based on the surrounding cells In the case where an automatic naming has not been made the cell reference e g B2 will be put in the box If one wants another distribution one ca
18. d have the same property it can be copied from the first cell If one wants to clear a cell the last of the tree buttons can be used This can be used for both Assumptions and Forecast cells AARHUS S AJ UNIVERSITY T BJ TE Monte Carlo Simulation in Crystal Ball 7 3 Analytics Group 4 The Buttons Select All Assumptions and Select All Forecast are used to select all i the Assumptions and Forecast cells This is advantageous if one wants a quick over 9 select AN Assumptions view of the cells that has been defined K N Select All Decisions sil Select All Forecasts Another way to do this is to use the Cell Prefs M Cell Prefs Where a color and shading rr for the types can be defined Assumptions are predefined as green and Forecast cells as light blue Decisions will only be relevant when the advanced functions are used e g OptQuest which will be described later To freeze Assumptions and there by keep them fixed in the simulation the Freeze Lai Freeze can be used gt l ial Ue Us E the navigation buttons for Ne EAT mie use Kong which aoe the simulation the next Stop stops the simulation If a simulation is stopped it will not automatically be reset hence Crystal Ball will continue with the previous simulation if it is started again The third button Reset is used at the end of a simulation to start the simulation from the beginning again The last button Step is used to carry out o
19. dvantages of making the spreadsheet more dynamic there are also some disadvantages of using the Crystal Ball functions When a Crystal Ball function is used to draw a value the cell cannot automatically by defined as an As sumption This means that some of the functionality is lost that one normally gets when using Assumption cells Hence it will not be possible to define correlations between the Assumptions and the Crystal Ball function that displays statistics for Assumptions does not work Moreover the documentation and help for the functions is very limited Finally it is not possible to make what if analysis by typing in a value in the specific cell without rewriting the formula in the cell 9 1 1 The most important functions The most important functions will in the following be presented as a supplement to the lack of documentation and help in Crystal Ball Excel Function Description CB Normal y o The function draws random numbers from a The functions are used when normal distribution with a mean H and a stand one needs to draw many ard deviation o random numbers This could CB Uniform Min Max The function draws a random value from a uni for example be in the inven form distribution with a minimum Min and a tory model when the de maximum Max mand and delivery time CB Triangular Min Lik Ma The function draws a random number from a should be drawn for many x triangular distribution with minimum Min most
20. e the assumptions for the simulation To make a small interpretation of the model it shows that there is an expected profit and that the variance of the return is larger in the second period compared to the first This variation can also be seen in the distribution charts for the two peri ods Here the second period looks like a normal distribution with a tail which is due to the pricing policy for this period In the sensitivity analysis it can be seen that the parameter that is most influential is the price Both the spreadsheet and the report 03 example section 10 xIs can be found in the file package More information can be found in the section File package and literature AARHUS S AJ UNIVERSITY T Monte Carlo Simulation in Crystal Ball 7 3 Crystal Ball Report Full Simulation started on 4 26 2008 at 18 47 40 Simulation stopped on 4 26 2008 at 18 47 43 Run preferences Number of trials run 1 000 Extreme speed Monte Carlo Random seed Precision control on Confidence level 95 00 Run statistics Total running time sec 2 41 Trials second average 415 Random numbers per sec 2 488 Crystal Ball data Assumptions 6 Correlations 1 Correlated groups 1 Decision variables 0 Forecasts 3 Forecasts Worksheet CB EX XLSJEKSEMPEL AARHUS S AJ UNIVERSITY L Analytics Group 28 Monte Carlo Simulation in Crystal Ball 7 3 Analytics Group 29 Forecast resultat00 Cell C8 Summary Entire range is from 31 133 95
21. eds a cost to a large enough extend to justify an investment AARHUS S AJ UNIVERSITY E SSS Monte Carlo Simulation in Crystal Ball 7 3 Analytics Group 16 8 3 Sensitivity Charts It is important to activate storing of assumption values under Run Preferences before the simulation starts When this has been done the chart can be used to see which Assumptions that have the largest effect on the variation in the Forecast variable Hence one can identify the best way to reduce variation in the Forecast cell if this is the goal of the simulation Sensitivity Gross Profit Edit View Sensitivity Preferences Help 1 000 Trials Contribution to Warance Yiew Sensitivity Gross Profit 0 096 10 0 20 0 30 0 talian Sausage unit profit Pepperoni unit profit Summer Sausage unit profit Bratvvorst unit profit Polish Sausage unit profit Casing bratwurst Under Preferences it is possible to chose which Forecast variable one wants the Sensitivity Chart to describe In the menu one can also specify whether one wants to see the correlation between each Assupmtion cell with the specific Forecast variable Rank Correlation It is also possible to see the Assumption variable s percentage contribution to the variation in the Forecast cell Contribution to Variance this is selected as default Under Choose Assumptions it is possible to chose which Assumption that should be presented Crystal Ball is as default set to pres
22. ent the most sensitive Assumptions in the case where one has many assumptions When there a few all will be displayed 8 4 Extract Data raw numbers statistics percentiles and frequencies In this menu raw numbers statistics percentiles and frequencies can be transferred to the spreadsheet rather than just displayed in a window This will make it possible to work further with the numbers and graphs if for example these should be used in a document If the numbers which are being generated in Assumptions and are extracted from the Forecast cells should be used for more advanced analysis in other programs such as SAS or SPSS the raw data can be extracted in this menu and inserted in a separate spreadsheet Hereafter the numbers can be exported to other programs AARHUS S AJ UNIVERSITY E E Monte Carlo Simulation in Crystal Ball 7 3 Analytics Group 17 8 5 Create Reports In this menu it is possible to extract almost the same data as in the Extract Data function it is just not possible to extract raw data On the other hand these extracts give some more readable reports which can be printed directly and used in presentations or documents AARHUS S AJ UNIVERSITY KA YY YY YU KC EI KG Monte Carlo Simulation in Crystal Ball 7 3 Analytics Group 18 9 Crystal Balls advanced functions Crystal Ball has a number of more advanced functions build in These ensure that Crystal Ball can be dynamic and cus tomized thus used for more
23. he number of simulations to e g 500 or 1000 Under Preferences different formatting settings can be made and it can be specified which simulation should be saved in Status and Solutions that displays the results from the simulation In other word whether one wants to save all the simula tions or just the optimal solution during the simulation Furthermore the path to a logfile can be specified Under the Ad vanced tab it can be specified whether the model should be stochastic i e whether an Assumption cell should be used to draw random numbers from When the desired settings have been made press OK and OptQuest will ask whether the optimization should run now press Yes to start the optimization 9 2 5 Results When the optimization is running the process and the current best solution can be seen in the Status and Solutions win dow The window Performance Graph shows the gross profit objective on the y axis and the number of simulations on the x axis This graph will show the simulation as it progresses and how the optimization converges to the optimal solution The Status and Solutions window will give more detailed information during the optimization for example the use of the decision variables These two windows will look as the following two screenshots AARHUS S AJ UNIVERSITY T a md Monte Carlo Simulation in Crystal Ball 7 3 Analytics Group 25 Status and Solutions Optimization is Complete BA
24. n Sausage lt 12520 Variables fo Summer sausage Bratwurst talian Sausage 4 Pepperoni Polish Sausage lt 14100 oum All Variables summer sausage 1 5 ltalian Sausage 3 Polish sausage lt b4o0 When the constraints are being typed in the buttons to the right can be used to insert the name of the decision variables and the sum of the variables Sum All Variables In this way it is ensured that the variable names are spelled correctly and thereby work properly Only the decision variables can be include as variables in the expressions Next step in the wizard is found by pressing OK 9 2 3 Forecast Selection In the Forecast Selection window the cell that should be maximized minimized which should be defined as Forecast cell on before hand is chosen together with other constraints on the model The list in the Forecast Selection window consists of the Forecast cells in the spreadsheet In this example it is the gross profit which should be maximized In addi tion to this there is uncertainty about the use of casing per unit which might be larger than expected because of vast in the production The gross profit cell is set as Maximize Objective i e the objective function is Z gross profit The casting requirement is set so 95 of the demand for casting is fulfilled therefore the 5 percentile is chosen with O as the lower bound Thereby the Forecast Selection window will look as the screenshot below AA
25. n simply press Gallery and select a new The button Correlate is more ad vanced as it allows for two cells to be correlated This could for example be useful in the case where one simulates a budget here the sales and advertising expenditures could be correlated 4 2 Change or delete an assumption To change an Assumption the cell is selected and one presses Define Assumption Hereafter the distribution window will open Here changes can be made to the distribution or a new distribution can be chosen To delete an Assumption the cell s is selected and one presses Clear Data A quick way to select all Assumptions is to press Select Assumptions AARHUS S A UNIVERSITY E D Monte Carlo Simulation in Crystal Ball 7 3 Analytics Group 8 4 3 Additional assumptions When one works with larger models it will be advantageous to highlight all the Assumptions cells these are predefined as green To highlight the cells in another way than with green color this can be changed under Cell Prefs Here a shading color or notes can be put on the Assumptions cells The note can include name distribution parameters and range If more than one assumptions should be tested the facility Freeze can be very useful With this selected Assumption cells can be fixed This is especially useful if for example one should test different sales strategies in the budget model Further notice that the Assumptions can be copied and in
26. ne iteration at a time i e step by step Start Stop Reset Step Under Tools the advanced options can be found Only a description of OptQuest is included in this manual section 9 2 Bly va In Save or Restore the results from the simulation can be saved and restored for Save Results later use Restore Results Here the properties for the simulation can be made The most important parameters are number of iterations seed values data collection and macros AARHUS S AJ UNIVERSITY Monte Carlo Simulation in Crystal Ball 7 3 Analytics Group 5 EN nm A After a simulation View Charts can be used to display histograms overlay trend im E and sensitivity charts In order to get the last types of charts it is important to tick off a Hag store assumption values for sensitivity analysis under Run Preferences gt options In Charts Reporty Data Create Report a report can be created displaying information from the simulation Under Extract Data one can extract data including forecast values statistics etc The data can hereafter be exported to for example SAS where more advanced analysis can be made hHep These buttons includes the help functions for Crystal ball where the functions in 0 Resources Crystal Ball are briefly described this can be an effective source of help In Re C about sources it is possible to get access to user manuals and the developer kit The ki
27. ntinuous WorkBook shows which sheet in the spreadsheet the cell is in and the Cell column shows the name of the cell All the columns can be used to adjust the optimization to the spreadsheet and variables thus OptQuest optimization files from other spreadsheets can be used by adjusting to the new spreadsheet AARHUS S AJ UNIVERSITY S SSS Monte Carlo Simulation in Crystal Ball 7 3 Analytics Group 22 In this example the decision variable should be defined as in the screenshot above One can for example see that all the decision variables should be between 0 and 5000 units When the decisions have been properly defined on can go to the next step Constraints by selecting OK 9 2 2 Constraints In the Constraints window the constraints in the optimization can be defined by using mathematic or logic expression as one would normally do in a LP problem in Excel The simple constraints have already been defined in this case that the decision variables should be above O and less than 5000 Other constraints in the model are that 4 Ibs of veal is used to produce a Bratwurst and 1 Ibs of veal is used to produce an Italian sausage The inventory of veal is 12 520 which is the maximum amount of veal that can be used in the production This is similar for pork and beef The constraints will look as they appear in the following screenshot Constraints Input any linear constraints on decision variables 4 Bratwurst Italia
28. ration Crystal Ball stores the values in the Forecast cells Thereby the Forecast values can be presented in histograms and descriptive statistics such as mean standard deviations and correlations In the budget case Crystal ball can calculate an estimate of the result in the coming period given the expected uncertainty AARHUS S AJ UNIVERSITY E FE Monte Carlo Simulation in Crystal Ball 7 3 Analytics Group 3 3 User Interface When Crystal Ball is installed the program is placed in the start menu When the program is opened Microsoft Excel is be ing started A startup screen will be shown where a new Crystal Ball project can be started an old project can be opened or examples for help can be started Please notice that if Crystal Ball is installed from the CD that is bundled with More amp Weatherford 2001 the program Will be installed in a trial version with a time limited license Be aware that the license will run out within a fixed period The user interface in Crystal Ball in Excel is a new tab in the toolbar in Excel This contains the most important functions Under Tools the advanced functions can be found Notice that the functions are grouped in four groups Define Run Analyze and Help The menu point Define is used to define the properties for the cells That means defining if the cell should be an Assump tion or Forecast which color the two should have and possibly freezing of Assumptions cells
29. rystal Ball cells that contain constant values can be defined as stochastic and a specific distribution can be as signed to the cells This is used for Monte Carlo simulations what if analysis where input cells in a spreadsheet through iterations can take different values which is drawn randomly from a statistical distribution One defines a range of out comes for the input cells based on the uncertainty that the specific data is exposed to Such a stochastic cell is name As sumption and Crystal Ball will at the start of each iteration draw a value from the distribution and put it in the cell Based on these assumptions and additional statistical input data one or more output cells are calculated which serves a as prediction of the real world Such an output cell is in Crystal Ball called a Forecast cell which is a prediction or fore cast In the above mentioned budget case one would forecast the result for the coming period based on uncertain inputs such price sales exchange rate and a number of fixed inputs as for example capacity cost and rent etc When the simulation is stated Crystal Ball will replace the values in the Assumption cells with a random number drawn from the specified distribution This will automatically update the calculations in the whole spreadsheet hence the fore cast cells will be updated with the new input values This process is repeated a predefined number of times called itera tions for each ite
30. s Group 38 THIS GUIDE HAS BEEN PRODUCED BY ANALYTICS GROUP Analytics Group a division comprised of student instructors under AU IT primarily offers support to researchers and employees Our field of competence is varied and covers questionnaire surveys analyses and processing of collected data etc AG also offers teaching assistance in a number of analytical resources such as SAS SPSS and Excel by hosting courses organised by our student assistants These courses are often an integrated part of the students learning process regarding their specific academic area which ensures the coherence between these courses and the students actual educational requirements In this respect AG represents the main support division in matters of analytical software ADVANCED MULTIMEDIA GROUP ADVANCED MULTIMEDIA GROUP Advanced Multimedia Group is a division under AU IT supported by student instructors Our primary objective is to convey knowledge to relevant user groups through manuals courses and workshops Our course activities are mainly focused on MS Office Adobe CS and CMS Furthermore we engage in e learning activities and auditive and visual communication of lectures and classes AMG handles video assignments based on the recording editing and distribution of lectures and we carry out a varied range of ad hoc assignments requested by employees In addition AMG offers solutions regarding web development and we suppor
31. serted by using the buttons in the menu as described in the section 3 AARHUS S AJ UNIVERSITY T UT Monte Carlo Simulation in Crystal Ball 7 3 Analytics Group 9 5 Forecasts That a cell is defined as Forecast means that data from the cell is collected at the end of each iteration The data can be used for histograms descriptive statistics trend and sensitivity charts see more under Results and reports In order to define a Forecast cell there should be a formula i e start with a equal to sign Define Forecast Cell G7 Mame G7 Es A Unite E7 Views Frequency om T Split view Window M Show automatically te While running simulation T Wher simulation stops Fit distribution Fit a continuous probability distribution to the forecast Fit Options Cancel Apply To Defaults Help In order to define a cell as a Forecast the cells should be selected By pressing Define Forecast a new window will ap pear press the arrow to the right in order to get more options In the window one can define the name and unit for the cell for example expected result in DKK This information will be used to make printouts and reports more readable Below one can specify whether the histogram Frequency should be made and shown automatically during the simula tion If it is a large simulation or an old computer that is used it is recommended to choose the option When simulation stops hence the hi
32. stogram will only be sown at the end of the simulation AARHUS S AJ UNIVERSITY ENR Ee Analytics Group 10 Monte Carlo Simulation in Crystal Ball 7 3 Forecast G Edit View Forecast Preferences Help 1 000 Trials Frequency View 999 Displayed G o o O CG E Aauanbal4 gt o PI 5 0 JO O CL pp FINE Certainty ln In the histogram window it is possible to change a number of parameters This is done under the dropdown menu Prefer ences Here one can among other things specify the number of iteration between each update formats and chart type When the change has been made Ok is pressed and it will be applied for the specific histogram If one selects Apply to all the changes will be applied to all histograms Notice that Forecasts like Assumptions can be copied and inserted by using the buttons in the Crystal Ball menu AARHUS S AJ UNIVERSITY BEES EL SEES EEC LSS Monte Carlo Simulation in Crystal Ball 7 3 Analytics Group 11 6 Run Preferences These settings can be found under Run Preferences in the Crystal Ball menu In Run Preferences the settings for the simu lation can be adjusted primarily to adjust the trade off between speed and precision and amount of information Run Preferences Trials Sampling Speed Options Statistics Number of trials to run f UUU fe Stop on calculation errors iv Stop when precision control limits are reached Confidence level
33. t contains extensive documentation and help for the more advanced functions Un der About information about e g expiration of the license can be found In general one can get a short definition of each button if one puts the curser over the button and keeps it there for short moment AARHUS S AJ UNIVERSITY E T Monte Carlo Simulation in Crystal Ball 7 3 Analytics Group 6 4 Assumptions An Assumption is a cell which can be random values sampled from a given distribution This is the input cells in the calcu lation Interesting functions here is to define change and delete Assumptions In addition Cell Preferences Freeze As sumptions are also useful options to know 4 1 Define assumptions In order to use a cell as an Assumption there should be a constant number in the cell This should be the only content of the cell Crystal Ball will for example not accept the cell if it contains 1 as it is understood as a formula When this is cor rectly made it is straight forward to define the cell select one of more cells press Define Assumption and select a distri bution There are further distributions available by pressing More and the Distribution Gallery will pop up Moreover it is possible to define the Assumptions based on a data series by chossing Fit in the Distribution Gallery Crystal Ball will then try to fit a distribution to the selected data and suggest the alternative that fits
34. t students and employees daily use of typo3 PLEASE ADDRESS QUESTIONS OR COMMENTS REGARDING THE CONTENTS OF THIS GUIDE TO ANALYTICS ASB DK AARHUS S AJ UNIVERSITY
35. tQuest it is recommend ed that the above mentioned example is used can be found in the file package The following description will be based on the steps in the Wizard in Crystal Ball It should however be noted that the steps in the optimization can be change by using the different buttons in the toolbar which was described above 9 2 1 Decision Variable Selection Open the Wizard function by selecting File gt New or by using the wizard icon in the toolbar this can always be used to reopen the wizard First step in the wizard is Decision Variable Selection that will appear in the OptQuest window In the window there will be a table including the decision variable that has been defined in the spreadsheet before OptQuest was opened The table will look like the screenshot below Decision Yariable Selection Select variables and set bounds The Select column makes it possible to enable and disable the decision variables Variable Name shows the name of the variable which was defined when the decision variable was defined in the spreadsheet Lower Bound and Upper Bound defines the constraints on the variables by a maximum and a minimum value The column Suggested Value specifies a value that should be used as starting point for the optimization this value will be equal to the value in the spe cific cell in the spreadsheet In the Type column the variable type can be specified as either discrete or co
36. to 401 150 51 Base case is 145 000 00 After 1 000 trials the std error of the mean is 2 388 62 resultat UU i Hi a 100 000 00 AARHUS S AJ UNIVERSITY Monte Carlo Simulation in Crystal Ball 7 3 Analytics Group 30 Statistics Forecast values Trials 1 000 Mean 194 179 64 Median 191 702 48 Mode iba Standard Deviation 75 534 91 Variance 5 705 522 964 11 Skewness 0 1465 Kurtosis 2 35 Coeff of Variability 0 3890 Minimum 31 133 99 Maximum 401 150 51 Range Width 370 016 56 Mean Std Error 2 388 62 Percentiles Forecast values 0 31 133 95 10 98 685 21 20 121 974 61 30 147 861 66 40 170 873 70 50 191 491 70 60 214 368 79 70 236 651 63 80 262 847 12 90 296 987 26 100 401 150 51 AARHUS S AJ UNIVERSITY FEE SEES errr SSS Monte Carlo Simulation in Crystal Ball 7 3 Analytics Group lt i Forecast resultat01 Cell D8 Summary Entire range is from 32 418 52 to 751 686 95 Base case is 445 000 00 After 1 000 trials the std error of the mean is 4 428 59 resultati 1 2 a o pai C dalal mlana dallas 110 00000 220 000 00 330 000 00 440 000 00 Statistics Forecast values Trials 1 000 Mean 243 096 24 Median 208 290 79 Mode S Standard Deviation 140 044 36 Variance 19 612 423 858 32 Skewness 1 37 AARHUS S AJ UNIVERSITY Monte Carlo Simulation in Crystal Ball 7 3 Analytics Group 32 Kurtosis 4 53 Coeff of Variability 0 5761 Minimum 32 418 52
37. tor and other CBTools modules is not part of this manual Further information about more advanced use of Crystal Ball can be found in the Crystal Ball User manual in the file package and on the website of the developer Decisioneering www decisioneering com On the website further information about versions compatibility etc can be found The spreadsheet which has been used in the section Example 03 Examplel section 10 xIs and the example used in the section OptQuest can also be found in the file package Crystal Ball xIs amp Product Mix opt The file package can be found under Crystal Ball on the website of the IKT department https softdistrib asb dk students Crystal 20Ball or directly from http aln hha dk ita manualer CB file package zip Content of the file package O Crystal Ball 7 3 User Manual pdf 02 Crystal Ball 7 3 Developer Kit User Manual pdf 03 Example Ball section 10 xIs Crystal Ball xls amp Product Mix opt The following literature can be recommended regarding the use of Crystal Ball Moore Jeffrey H Weatherford Larry R 2001 Decision modeling with Microsoft Excel 6 edition Prentice Hall Decisioneering 2007 Crystal Ball Developer Kit User Manual Version 7 3 1 Oracle included in file package Decisioneering 2007 Crystal Ball User Manual Version 7 3 1 Oracle included in file package AARHUS S AJ UNIVERSITY T Monte Carlo Simulation in Crystal Ball 7 3 L Analytic
38. uest is only included in the Student Version Professional Edition and the Premium Edition of Crystal Ball OptQuest runs a number of simulations in order to find the optimal solution to the LP problem Hence OptQuest searches through the process to improve the best solution OptQuest is using multiple metahuristic methods and techniques to ana lyze previous solutions and increase the quality and speed of the process For this manual an example of an LP problem in Excel Crystal Ball and an OptQuest file can be found in the file package The files Crystal Ball xls and Product Mix opt can be found in the file package on http aln hha dk ita manualer CB file package zip in order for the optimization to work it is important not to change the name of the files In order to use OptQuest one should define the decision variables before Crystal Ball is opened This is done by using the Define Decision in the menu The cell will thereafter be yellow to show that this is a decision variable When OptQuest is opened a window will open where it is possible to start a new optimazation or open an old one Here after the window Status and Solutions will open Files can also be opened through File in the menu similar to most oth er programs Below is an explanation of the icons on the toolbar only OptQuest specific icons will be accounted for The toolbar consists for the following functions i FS lul Es These buttons are used
Download Pdf Manuals
Related Search