1、OPIM 5270 |Spring 2015 Project ManagementSession 9Crystal BallOPIM 5270OPIM 5270 |Spring 2015 Session 9 GoalsqUnderstand why risk must be analyzedqKnow pros / cons for three ways to analyze riskqIdentify random variables in modelsqKnow the four steps of a simulation processqGenerate random numbers w
2、ith Crystal BallqUse the four steps of a simulation processqExplain how Crystal Ball supports Proj. Mgmt.OPIM 5270 |Spring 2015 Most real-world business situations today are probabilistic, but the decision models used to deal with them are deterministic.How to deal with randomness?Ignore itSimplify
3、problem to make it analytically tractable, get solution, then ignore real-life complicationsFind a way to obtain an approximate solution to real-world problemsDealing with RandomnessOPIM 5270 |Spring 2015 Monte Carlo simulation is a method by which approximate solutions are obtained to realistic (an
4、d therefore complicated) problemsThis is in contrast to analytical methods, which obtain exact solutions to highly stylized problemsTradeoff between rigor and relevanceMonte Carlo SimulationOPIM 5270 |Spring 2015 What is this? Y = f(X1, X2, , Xk)Often, the values for one or more input cells are unkn
5、own or uncertain This creates uncertainty about the value of the output cellSimulation can be used to analyze these types of modelsIntroduction to SimulationOPIM 5270 |Spring 2015 A random variable is any variable whose value cannot be predicted or set with certainty.Many “input cells” in spreadshee
6、t models are actually random variables. For example: the future cost of raw materials future interest rates future number of employees in a firm expected product demandDecisions made using uncertain information often involve risk. What risks?Random Variables & RiskOPIM 5270 |Spring 2015 Using expect
7、ed values for uncertain cells tells us nothing about the variability of the performance measure. Suppose an $1,000 investment is expected to return $2,000 in two years. Would you invest if. the outcomes could range from $1,060 to $4,000? the outcomes could range from $0 to $2,100? Alternatives with
8、the same expected value may involve very different levels of risk. Why Analyze Risk?OPIM 5270 |Spring 2015 Best-Case/Worst-Case AnalysisWhat-if AnalysisSimulationMethods of Risk AnalysisOPIM 5270 |Spring 2015 Best case - plug in the most optimistic values for each of the uncertain cells.Worst case -
9、 plug in the most pessimistic values for each of the uncertain cells.This is easy to do and bounds the outcomes, but tells us nothing about the distribution of possible outcomes within the best and worst-case limits.Other problems or benefits?Best-Case/Worst-Case AnalysisOPIM 5270 |Spring 2015 worst
10、 casebest caseworst casebest caseworst casebest caseworst casebest casePossible Performance Measure Distributions Within a RangeOPIM 5270 |Spring 2015 Plug in different values for the uncertain cells and see what happens. Benefits:This is easy to do with spreadsheetsOther?Problems:Values may be chos
11、en in a biased way.Hundreds or thousands of scenarios may be required to generate a representative distribution.Does not supply the tangible evidence (facts and figures) needed to justify decisions to management.What-If AnalysisOPIM 5270 |Spring 2015 Values for uncertain cells are selected randomly
12、(and in an unbiased manner).The computer generates hundreds (or thousands) of scenarios.We analyze the scenario results to better understand the behavior of the performance measure.Allows decisions based on solid empirical evidence.SimulationOPIM 5270 |Spring 2015 Proper risk assessment requires sim
13、ulation. Simulation is a 4 step process:1) Identify the uncertain cells in the model.2) Implement appropriate Random Number Generators (RNGs) for each uncertain cell.3) Replicate复制the model n times, and record the value of the bottom-line performance measure.4) Analyze the sample values collected on
14、 the performance measure.Simulation ContinuedOPIM 5270 |Spring 2015 A Random Number Generator is a mathematical function that randomly generates (returns) a value from a particular probability distribution.We can implement Random Number Generators for uncertain cells to allow us to sample from the d
15、istribution of values expected for different cells.Random Number GeneratorsOPIM 5270 |Spring 2015 The RAND( ) function returns uniformly distributed random numbers between 0.0 and 0.9999999.Suppose we want to simulate the act of tossing a fair coin.Let 1 represent “heads” and 2 represent “tails”.Con
16、sider the following RNG:=IF(RAND( )0.5,1,2)How Random Number Generators WorkOPIM 5270 |Spring 2015 Crystal Ball provides two different ways for creating Random Number Generators in spreadsheetsCrystal Ball functionsUsed in formulas like any other Excel functionRequire CB to be installed on the machi
17、ne displaying the spreadsheet & do not support all CB functionalityThe Distribution GalleryDisplay a number (not a formula) in a cell but generates random numbers for that cell when simulating the modelDoes not require CB to be installed on the machine to display the spreadsheet & supports all CB fu
18、nctionalityGenerating Random Numbers with Crystal BallOPIM 5270 |Spring 2015 Click Define Assumption iconSelect distributionSpecify parametersUsing the Distribution GalleryOPIM 5270 |Spring 2015 Discrete Probability DistributionsOPIM 5270 |Spring 2015 Continuous Probability DistributionsOPIM 5270 |S
19、pring 2015 Define MenuRun MenuAnalyze MenuCrystal Ball ToolbarCrystal Ball in MS ExcelOPIM 5270 |Spring 2015 Determine which model inputs are uncertain and define a probability distribution. Identify which forecasts you want to analyze/measure (e.g., NPV, Sigma level, process efficiency)Run Simulati
20、onAnalyze ResultsGenerate ReportHow does Crystal Ball work?OPIM 5270 |Spring 2015 The first step to using Crystal Ball is to determine which model inputs are uncertain. Which values are estimates? Which are averages? Once you have identified these, you use your knowledge of the uncertainty around th
21、e input to create a probability distribution for that cell (what Crystal Ball calls an assumption). Crystal Ball lets you define these distributions using the Distribution GalleryDefine Your DistributionsOPIM 5270 |Spring 2015 Enter variety of parameters to define distributionsCan fit distributions
22、to raw dataCan cell reference all fieldsCan correlate pairs of assumptionsMarker linesAssumption DialogOPIM 5270 |Spring 2015 The next step is to identify a forecast. A forecast is a formula cell that you want to measure and analyze. In this model, you select the Net Profit (cell C23). Identify Your
23、 ForecastsOPIM 5270 |Spring 2015 Number of simulation trials performedDisplay rangeCertainty (probability) that the forecast will reach $2,812,558Parts within the spec limits are shown in blue, parts outside spec limits are shown redNumber of data points displayed in the chartlCrystal Ball uses Mont
24、e Carlo simulation to randomly generate thousands of what-if scenarioslEach scenario is then captured and presented in a frequency chart (Forecast Chart)Run SimulationOPIM 5270 |Spring 2015 Whats responsible for most of the variation in the forecast?The sensitivity chart shows the influence each ass
25、umption cell has on the forecast. Analyze ResultsOPIM 5270 |Spring 2015 ReportsSelect a pre-defined report or create your own custom report. Reports now include new statistics and more control over data and charts. Extract DataYou can extract data from both forecasts and assumptions and extract multiple types of data. Generate ReportsOPIM 5270 |Spring 2015 Go to Crystal Ball in ExcelLets Build Our Own Model
