Practical Monte Carlo Simulation with Excel (Part 1 of 2)
|Title||Practical Monte Carlo Simulation with Excel (Part 1 of 2)|
|Size||210 pages (54,000 words)|
There is a fair number of stand alone applications as well as add on’s to Microsoft Excel in the market used to run Monte Carlo Simulation (MCS) models. However, out of the box, Excel has all the functions you need to develop such models. What is needed are robust modeling procedures, techniques and analytic formulations. Initially, I started with one book. This grew out of proportion as more and more applications and models were identified. Some of these had not been modeled with MCS before. I had to break the book into two parts.
Part 1 presents the basics of modeling always providing methods and typical models as applications of simulation. Part 1 also spends time on clarifying different ways of analyzing the simulation output using a variety of statistical functions and procedures all found within Excel. The eBook clarifies a variety of Excel facilities needed in different parts of simulation: sensitivity analysis, linear regression and the Analysis Toolpack. Finally, Part 1 presents a few standard modeling techniques that can be used in a variety of models, specifically in Part 2.
Part 2 concentrates on applications such as project management, acceptance sampling, sales and budget forecasting, queuing models, reliability engineering and more. Since these operations behave according to specific statistical distributions, time is spent on clarifying a variety of these functions. When one or two are not available in Excel, alternative methods of computation are presented. A special chapter addresses Markov Processes and shows how simulation can be coupled to such an analysis.
The uses and applications of statistical distributions in these operations are addressed in depth. Having covered Uniform, Normal and Discrete Distributions in Part 1, Part 2 proceeds to present and give applications for the following distributions: binomial, negative binomial, geometric, hypergeometric, triangular (not commonly used but is the basis as to why betaPERT is preferred), Poisson, exponential, Gamma and Weibull.
No programming is required although in one single case, an embedded VBA module is included. It is used to formulate a method that allows the analyst to develop a two level simulation. To get the results of each of the primary runs in the model, the model runs a further “sub-simulation”. No VBA competence is required.
The two eBooks come with 21 and 54 step by step models, respectively, and with supporting images. Whenever statistical functions are used, they are fully clarified using a common sense and non-theoretical approach. All the workouts are solved and are available for download from this page.
1) In every workout that uses COUNTIFS() to develop a frequency table, the formulation assumed that the first item in the Bin is the minimum value in the data column. Using COUNTIFS() to check the entry in the first row (which is a header label), resulted in a zero. This works well if the first bin is the minimum value. If you are going to use COUNTIFS() in this manner and the first bin value is not the minimum value, you need to enter a special formula in the first row which ensures that the first COUNTIFS criterion is > 0.
2) In Chapter 6, the intro Workout 1 is missing the steps between Step 8 and Step 15. In fact, these are not missing. What happened is that the procedure for setting up my standard results worksheet used to be in Chapter 6 and Chapter 7 was the "Our First Monte Carlo Simulation". When I found it logical to switch the chapters around, steps 9 to 14 in Workout 1 (in the new Chapter 6) would have been repeated and in more detail, in the new Chapter 7. I removed them and forgot to indicate that steps 9 to 14 are detailed in full in Chapter 7.
Introducing Part 1 of this eBook
Downloading the Supporting Files
Alerts, Guidelines, Exclusions and Apologies
The Rationale for Monte Carlo Simulation
Guidelines and Good Practices for Modeling with Excel
Our First Full Monte Carlo Simulation
Frequency Tables, Relative and Cumulative Frequencies
The Monte Carlo Simulation Process
From Frequency Tables to Probability Distributions
How to Generate Random Numbers in Excel
Models that Sample the Uniform Distribution
Models that Sample the Discrete Random Variable Distribution
Models with Primary and Secondary Runs: Hospital Lab Tests
Sensitivity Analysis and Simulation
Appendix A: Descriptive Statistics and Related Measures
Appendix B: Basics of Simple Linear Regression
Appendix C: Miscellaneous Excel Facilities
Appendix D: Setup the VBA Sub-Runs Module