1. Monte-Carlo Simulation Simulation with Spreadsheets

2. Monte-Carlo Simulation • A method for explicitly modeling uncertainty in a decision support model such as the spreadsheets we’ve built • Descriptive – estimate probability distribution of key model outputs • Goes beyond expected values and point estimates • Quasi-static model – often doesn’t really capture detailed flow of events over time • Discrete event simulation used for this – we’ll do later • Often called Risk Analysis • widely used in financial services industry • @RISK and Crystal Ball • products that facilitate Monte-Carlo simulation from within spreadsheets • Facilitates comparing multiple scenarios over multiple performance measures • it’s really a way of running controlled experiments • like most experiments, output must be analyzed statistically

3. Why Important to Model Uncertainty? • The world is uncertain • Replacing random quantities with averages or single “guesstimates” can be dangerous • The Flaw of Averages • Allows prediction of distribution of results • Not just one predicted number or outcome • Sensitivity analysis of outputs to inputs • Which inputs really affect the outputs?

4. Useful Documents • Distributions, Simulation, and Excel Functions • This is the “Distributions, Simulation, and Excel Functions” document created by Prof. D. Doane. I’ve posted it on the web in Session 5. • HowToUse@Risk-JCHPCaseAnalysis.doc • We’ll do this in class today

5. What is @Risk? • @Risk is a widely used Excel add-in that facilitates creation and analysis of spreadsheet based simulation models • Contains many probability distributions for modeling random inputs • Includes tool (BestFit) for fitting input distributions • Automates bookkeeping for running many simulation replications and creates a ton of tabular and graphical output reports

6. Building a Spreadsheet Based Simulation Model (1) Build deterministic model Formulas Inputs Outputs Deterministic Inputs (2) Choose inputs to model as random Inputs Stochastic or Uncertain or Random Inputs (3) Model uncertain inputs with probability distributions Uniform Exponential Empirical Normal Poisson Many more…

7. See “Distributions, Simulation and Excel Functions” handout that Doane created.

8. See “Distributions, Simulation and Excel Functions” handout that Doane created.

9. Building a Spreadsheet Based Simulation Model “Running the model” (4.1) “Manually”, through formulas and either many rows or VBA (4) Recalculate spreadsheet many times 2 options (4.2) Use spreadsheet simulation add-in such as @Risk or Crystal Ball • Each recalculation of the spreadsheet is a “simulation run” or the result of an experiment • Like standard experiments, results must be analyzed using statistics • @Risk • www.palisade.com • Crystal Ball • www.decisioneering.com

10. Examples • JCHP – Break Even • Let’s add some uncertainty to this model • Demand • Costs • JCHP-BreakEven-Simulation-01.xls • Portable Monitoring Devices • We’ll look at this later this session or next session

11. (5) Analyze outputs JCHP-Simulation-01.xls Demand = RiskNormal(2870,200) Demand = RiskNormal(2609,200) Probability distribution of output variable, NOT just expected value.

12. Output Histogram Graph and Table are linked Sliders Stats

13. The Bigger Picture • Create several or many scenarios to simulation • Input variable values (including parameters for probability distributions • Perhaps model structure itself • Analyze performance measures for each scenario • Synthesize results • Pick “best” system or “best k out of n” systems • Interpret results and “make decision” • Simulation results just add another dimension to the discussion regarding the decision • Embed in optimization framework to formally compare many scenarios with respect to some objective function

14. Some Advantages • Much cheaper to experiment with simulation model than real system • Provides nice analytical framework for comparing multiple scenarios when uncertainty in key inputs is significant • Get a prediction of the distribution of the output variables we are interested in • Often models can be reused • Simulation models usually require fewer simplifying assumptions than analytical models are are often easier to use • We’ll explore queueing models soon and this will be clearer • Simulation may provide only available approach to analyzing complex systems

15. Some Disadvantages • Development may be time consuming • Data may be difficult or impossible to obtain • Decision makers may not understand the technique nor be able to properly interpret the results • Dazzle of the technique can mask flaws in design and/or input data • Simple analytical models might be overlooked • Run times may be “long” • You, as the decision maker, must use your brain to come up with the scenarios to be simulated