水晶球软件使用Crystal Ball

1、OPIM 5270 |Spring 2015,Project Management Session 9 Crystal Ball,OPIM 5270,OPIM 5270 |Spring 2015,Session 9 Goals,Understand why risk must be analyzed Know pros / cons for three ways to analyze risk Identify random variables in models Know the four steps of a simulation process Generate random numbe

2、rs with Crystal Ball Use the four steps of a simulation process Explain 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 it S

3、implify problem to make it analytically tractable, get solution, then ignore real-life complications Find a way to obtain an approximate solution to real-world problems,Dealing with Randomness,OPIM 5270 |Spring 2015,Monte Carlo simulation is a method by which approximate solutions are obtained to re

4、alistic (and therefore complicated) problems This is in contrast to analytical methods, which obtain exact solutions to highly stylized problems Tradeoff between rigor and relevance,Monte Carlo Simulation,OPIM 5270 |Spring 2015,What is this? Y = f(X1, X2, , Xk) Often, the values for one or more inpu

5、t cells are unknown or uncertain This creates uncertainty about the value of the output cell Simulation can be used to analyze these types of models,Introduction to Simulation,OPIM 5270 |Spring 2015,A random variable is any variable whose value cannot be predicted or set with certainty. Many “input

6、cells” in spreadsheet 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 demand Decisions made using uncertain information often involve risk. What risks?,Random Variables & Risk,OPIM 5270 |S

7、pring 2015,Using expected 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,

8、100? Alternatives with the same expected value may involve very different levels of risk.,Why Analyze Risk?,OPIM 5270 |Spring 2015,Best-Case/Worst-Case Analysis What-if Analysis Simulation,Methods of Risk Analysis,OPIM 5270 |Spring 2015,Best case - plug in the most optimistic values for each of the

9、uncertain cells. Worst case - 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 Anal

10、ysis,OPIM 5270 |Spring 2015,Possible Performance Measure Distributions Within a Range,OPIM 5270 |Spring 2015,Plug in different values for the uncertain cells and see what happens. Benefits: This is easy to do with spreadsheets Other? Problems: Values may be chosen in a biased way. Hundreds or thousa

11、nds 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 Analysis,OPIM 5270 |Spring 2015,Values for uncertain cells are selected randomly (and in an unbiased manner). The c

12、omputer 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.,Simulation,OPIM 5270 |Spring 2015,Proper risk assessment requires simulation. Simulation is a 4 st

13、ep 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 the performance measure.

14、,Simulation Continued,OPIM 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 distribution of values

15、expected for different cells.,Random Number Generators,OPIM 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”. Consider the followi

16、ng RNG: =IF(RAND( )0.5,1,2),How Random Number Generators Work,OPIM 5270 |Spring 2015,Crystal Ball provides two different ways for creating Random Number Generators in spreadsheets Crystal Ball functions Used in formulas like any other Excel function Require CB to be installed on the machine displayi

17、ng the spreadsheet & do not support all CB functionality The Distribution Gallery Display a number (not a formula) in a cell but generates random numbers for that cell when simulating the model Does not require CB to be installed on the machine to display the spreadsheet & supports all CB functional

18、ity,Generating Random Numbers with Crystal Ball,OPIM 5270 |Spring 2015,Click Define Assumption icon,Select distribution,Specify parameters,Using the Distribution Gallery,OPIM 5270 |Spring 2015,Discrete Probability Distributions,OPIM 5270 |Spring 2015,Continuous Probability Distributions,OPIM 5270 |S

19、pring 2015,Define Menu,Run Menu,Analyze Menu,Crystal Ball Toolbar,Crystal Ball in MS Excel,OPIM 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 Si

20、mulation Analyze Results Generate Report,How 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

21、 around the 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 Gallery,Define Your Distributions,OPIM 5270 |Spring 2015,Enter variety of parameters to define distributions Can fit d

22、istributions to raw data Can cell reference all fields Can correlate pairs of assumptions Marker lines,Assumption Dialog,OPIM 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 C

23、23).,Identify Your Forecasts,OPIM 5270 |Spring 2015,Number of simulation trials performed,Display range,Certainty (probability) that the forecast will reach $2,812,558,Parts within the spec limits are shown in blue, parts outside spec limits are shown red,Number of data points displayed in the chart

24、,Crystal Ball uses Monte Carlo simulation to randomly generate thousands of what-if scenarios Each scenario is then captured and presented in a frequency chart (Forecast Chart),Run Simulation,OPIM 5270 |Spring 2015,Whats responsible for most of the variation in the forecast? The sensitivity chart shows the influence each assumption cell has on the forecast.,Analyze Results,OPIM 5270 |Spring 2015,Reports Select a pre-defined report or create your own custom report. Reports now include new statistics and more control over data and charts.,Extract Data You can extract data