University:
Washington State University
Course:
MGTOP 470 | Business Modeling with Spreadsheets
Academic year:

2020
Views:

48

Pages:

26
Author:

palangresq3v

MgtOp 470—Business Modeling with Spreadsheets Topic 6 Monte Carlo Simulation Set 1: Computer Simulation and Output Analysis What is Monte Carlo Simulation? Uncertainty arises due to random variation, lack of knowledge, or error. Computer simulation has to do with using computer models to imitate real life or make predictions. Monte Carlo simulation not only tells you what could happen, but how likely it is to happen. Working Definition: Monte Carlo simulation is basically a sampling experiment whose purpose is to estimate the distribution of an outcome variable that depends on several probabilistic input variables. Three Primary Uses: 1. Predict an expected outcome 2. Predict a distribution of outcomes (best case/worst case, risk/reward) 3. Optimization (comparison of decisions) Two Major Types of Computer Simulation 1. Discrete event: Concerns the modeling of a system as it evolves over time by a representation in which the state variables change instantaneously at separate points in time. Examples: customers proceeding through a drive-through window at a restaurant, workers loading a truck, etc. (Note: continuous simulation models systems that change continuously over time. These typically involve differential equations.) Simulation models that involve the passage of time may or may not involve random inputs. 2. Monte Carlo: Concerns the modeling of a system that employs the use of random inputs. Many authors only define simulations as Monte Carlo if the passage of time plays no substantive role. Others define them more broadly as modeling systems whose relationships can be defined mathematically and entered into a spreadsheet. Monte Carlo Simulation as Risk Analysis Risk analysis is part of every decision we make. Simulation answers the question, “What are the risks?”. It can address questions such as, “What’s the likelihood that this investment will yield a $1 million return? How much does the inflation rate influence my economic evaluation? What are the odds of being over budget on this project?” Monte Carlo simulation lets you see all the possible outcomes of your decisions and assess the impact of risk, allowing for better decision making under uncertainty. Desired outcomes: (1) average outcome, (2) probability of a particular set of outcomes (“tail probabilities” are often suitable measures of the risk associated with a decision), (3) worst case/best case. Monte Carlo simulation performs risk analysis by building models of possible results by substituting a range a values—a probability distribution—for any factor that has inherent uncertainty. It then calculates results over and over, each time using a different set of random values from the probability functions. Depending upon the number of uncertainties and the ranges specified for them, a Monte Carlo simulation could involve thousands or tens of thousands of recalculations before it is complete. Monte Carlo simulation produces distributions of possible outcome values. By comparison, a decision tree only deals with expected values and thus inherently ignores risks. History A Monte Carlo method is a technique that involves using random numbers and probability to solve problems. The term was coined by Ulam and Metropolis in the 1940s in reference to games of chance, a popular attraction in Monte Carlo, Monaco. Monte Carlo methods were used during the Manhattan Project, and computerized Monte Carlo simulation was first used by physicists working on nuclear weapons projects in the Los Alamos National Laboratory. Advantages and Disadvantages of Simulation Advantages of Computer Simulation 1. Applicable in complex cases where analytical techniques cannot be employed. In general, the larger the number of probabilistic components in the system becomes, the more likely it is that simulation will be the best approach. 2. Provides an experimental laboratory. 3. Applicable where the system itself cannot be built, modified, destroyed, etc. Allows for “exploration of the impossible.” 4. Avoids risk and disruptive experiments with actual systems. 5. Compresses time to reveal long-term effects. 6. Generally less costly than experiments with real-world systems. 7. Promotes creativity (faster & less risk). 8. Ideas come alive with animation and graphs. 9. Can incorporate risk in the decision-making process. 10. Can identify and analyze a large number of possible solutions. 11. A tool for thinking and understanding before taking action. 12. Great tool for sensitivity analysis. 13. Extremely flexible tool. Disadvantages of Computer Simulation 1. An expert may have to write the computer program. Some programs contain > 10,000 lines of code. 2. Can be costly for data collection, modeling, and analysis. 3. Optimal solutions are not guaranteed. 4. Does not necessarily suggest a solution methodology. 5. May hide critical assumptions that invalidate the model. 6. Random numbers generated and used are only samples from a distribution. 7. Reality can never be exactly modeled, particularly with respect to human reactions. 8. May be difficult to assess uncertainties. 9. Quality modeling is not easy! CARELESS CODE RECYCLING CAUSES KILLER KANGAS Mutant Marsupials Take Up Arms Against Australian Air Force The reuse of some object-oriented code has caused tactical headaches for Australia’s armed forces. As virtual reality simulators assume larger roles in helicopter combat training, programmers have gone to great lengths to increase the realism of their scenarios, including detailed landscapes and—in the case of the Northern Territory’s Operation Phoenix—herds of kangaroos (since disturbed animals might well give away a helicopter’s position). The head of the Defense Science & Technology Organization’s Land Operations/Simulation division reportedly instructed developers to model the local marsupials’ movements and reactions to helicopters. Being efficient programmers, they just re-appropriated some code originally used to model infantry detachment reactions under the same stimuli, changed the mapped icon from a soldier to a kangaroo, and increase the figures’ speed of movement. Eager to demonstrate their flying skills for some visiting American pilots, the hotshot Aussies “buzzed” the virtual kangaroos in low flight during a simulation. The kangaroos scattered, as predicted, and the visiting Americans nodded appreciatively...then did a double-take as the kangaroos reappeared from behind a hill and launched a barrage of Stinger missiles at the hapless helicopter. (Apparently the programmers had forgotten to remove that part of the infantry coding.) The lesson? Objects are defined with certain attributes, and any new object defined in terms of an old one inherits all the attributes. The embarrassed programmers had learned to be careful when reusing object-oriented code, and the Yanks left with a newfound respect for Australian wildlife. Simulator supervisors report that pilots from that point onward have strictly avoided kangaroos, just as they were meant to. (June 15, 1999, Melbourne) Simulation Software Excel Add-Ins: Crystal Ball, @Risk, Risk Solver, Simtools (free) Discrete-Event Simulations: Arena, SIMPROCESS, Process Simulator, AGPSS, SIMSCRIPT III, SIMUL8 Steps of Monte Carlo Simulation 1. Create a parametric (base case) model, and determine which input parameters will be uncertain (sensitivity analysis and tornado charts can help) and their respective probability distributions. 2. Generate a set of random inputs. 3. Evaluate the model and store the results. 4. Repeat Steps 2 and 3 up to n trials. 5. Analyze the results using histograms, summary statistics, confidence intervals, etc. Uncertain Input Parameter Selection Only the most influential and uncertain parameters should be replaced with probability distributions. A tornado chart evaluates which parameters of a model most affect the valuation. In general, those showing the greatest effect should assigned probability distributions in the simulation. Excel Add-In “TopRank” This program (bundled with the text) creates a non-permanent Excel Add-In that disappears every time that you close Excel. After opening the program, Excel should open and create the add-in for you that can then be used in any other spreadsheets that you open or create. TopRank has a video tutorial that is worth viewing. General Approach 1. Select the model output (or outputs) of interest by clicking: 2. Place the cursor over the each input that you want to perform sensitivity analysis on and click: 3. Set the Min and Max ranges over which to vary the input (the default approach uses percentage changes—click on the function button to use a different approach (e.g., absolute changes or absolute values)) 4. Click on and then click 5. A tornado chart and spider graph will be available in a separate workbook Example Profit = (Price –Variable Cost)[Market Size × Our Share]−Fixed Cost Parameter Price Variable Cost Fixed Cost Market Size Our Share Lower Bound 140 30 150 8 .18 Most Likely 175 40 180 12 .25 Upper Bound 200 60 300 20 .35 To change to actual Min and Max, click then this button. Click : Tornado Graph of Profit Impact by Input Market Size (C8) Our Share (C9) Price (C5) Fixed Cost (C7) 550 500 450 400 350 300 250 200 150 100 50 Variable Cost (C6) Value of Profit Other TopRank Issues • You can perform a quick default Tornado chart on all inputs that affect the output by simply clicking: →→ • With the Add Input dialog displayed, just press to step through all inputs that will be changed in your spreadsheet • “Multi-Way” analysis can be performed that analyzes the impact of changing more than one input at the same time. Click on , then select the inputs you want to analyze via multi-way (), then right-click and select Creating Simulation Replications in Excel Three Potential Methods 1. Put the entire model into one row (column), and copy the model down (n – 1) rows (over (n – 1) columns)). 2. If only one or two inputs are random, generate a Data Table. 3. Write a macro (e.g., (rel. references)→ Paste Values→). Two-Variable Data Table The input values for one variable are listed down one column, while the values for the other variable are listed across one row. The output cell is placed at the intersection of the input column and input row. Fast Feet Revisited To add prices ranging from $20 to $45, in $5 increments: 1. Move the sales quantity range underneath the output cell. 2. Put the input prices in the 6 columns to the right of the output cell. 3. Prior to calling the Data Table tool, select the range covering all input cells. 4. The Row input cell will reference the price cell (C5), and the Column input cell will reference the quantity cell (C10). Random Number Generation Computers generate pseudo-random numbers, because they are developed according to an algorithm on a finite machine. Eventually, these algorithms either repeat or converge. However, the numbers appear random, and for practical purposes, work well on today’s computers using Excel and other simulation programs. Random number generators have a starting seed, which is the beginning number for the generation of pseudo-random numbers. In Excel, the default seed for a random number constantly varies (probably based on the clock), which is why a different number may appear in a random number cell every time that you open the same Excel file. However, Excel also has a Random Number Generation tool that allows the user to specify a starting seed. This is extremely useful in simulation because different systems (assumptions) can be compared, where the variation comes from the change in system (assumption), not the change in random numbers. For example, if you’re trying two different distributions for a particular input variable, then drawing from the same seed should result in both draws being either above or below their respective means. As explained below, if you want to use seeds for distributions that are not part of Excel’s Random Number Generation tool, you can generate a set of Uniform(0,1) random numbers and use Excel functions to convert those into the distribution of interest. Generating Random Numbers in Excel Two Methods for Random Number Generation: 1. Random Number Generation tool in Excel’s Analysis Toolpak Add-In to create a set of static random numbers for certain distributions. 2. Use specific functions to create random variables in cells using the RAND() function. Unless the automatic recalculation feature is suppressed, whenever any cell in the spreadsheet is modified (or is depressed), the values in any cell containing RAND() will change. Fitting Data to Distributions 3 Approaches to Input Distributions 1. Take raw data and use that data as the input to your simulation. Adv.: good way to validate the model Disadv.: you’re saying that the future will exactly mimic the past 2. Empirical distribution—use n possible outcomes (that you have observed) and assign a uniform probability to each outcome (Excel’s Sampling tool does this) Adv.: don’t need a distribution assumption Disadv: constrained by the data Note: When choosing a distribution based on empirical data, it is generally advisable to widen the range because actual results tend to underestimate the extremes. 3. A parameterized theoretical distribution Adv.: irregularities in your empirical distribution are smoothed out and “tails” are included that might not be in the original data Disadv: must estimate a distribution Empirical Distributions Sampling Without Replacement Using SIMTOOLS: the array function =SHUFFLE(n) This returns a random ordering of the numbers 1 to n, and should be entered into a row containing n cells. When entered into a row range of fewer than n cells, this function generates random samples from 1 to n without replacement. To sample from non-integers, the values in a given row range of n cells can be shuffled by entering the array formula: =INDEX(range of numbers,1,SHUFFLE(n)) Sampling With Replacement Use Excel’s Sampling tool. The Sampling analysis tool creates a sample from a population by treating the input range as a population. When the population is too large to process or chart, you can use a representative sample. You can also create a sample that contains only the values from a particular part of a cycle if you believe that the input data is periodic. For example, if the input range contains quarterly sales figures, sampling with a periodic rate of four places the values from the same quarter in the output range. →Data→Analysis:→Data Analysis→Sampling→ Input Range Enter the range of data containing the population of values that you want to sample. Labels Select if the first row or column of your input range contains labels. Period: If using periodic sampling, enter the desired periodic interval. This simply pulls every period-th value from the input range; it does not draw randomly. Number of Samples Enter the number of random values that you want in the output column. Each value is drawn from a random position in the input range, and any number can be selected more than once. Output Range Enter the reference for the upper-left cell of the output table. Data are written in a single column below the cell. Excel Functions for Drawing from Random Distributions Uniform(0, 1): =RAND() Uniform(a, b): =a+RAND()*(b−a) Normal(μ, ): =NORMINV(probability,mean,stdevn) Weibull(α, β): =β*(−LN(1−probability))^(1/α) Bernoulli(p): =IF(probabilityp,1,0) Discrete Uniform(a, b): =RANDBETWEEN(lbound,ubound) Using SIMTOOLS Exponential(μ): =EXPOINV(probability,mean) Lognormal(μ, ): =LNORMINV(probability,mean,stdevn) Gamma(μ, ): =GAMINV(probability,mean,stdevn) Beta(μ, , a, b): =BETA(probability,mean,stdevn,lbound,ubound) (default lower and upper bounds are 0 and 1, respectively) Triangular(a, b, c): =TRIANINV(prob,lbound,mostlikely,ubound) Binomial(n, p): =BINOMINV(probability,#trials,p) Poisson(λ): =POISINV(probability,mean) Discrete Distribution: DISCRINV(probability,values range,probabilities range) Excel’s Random Number Generator The Random Number Generation analysis tool fills a range with independent random numbers that are drawn from one of several distributions. Compared to using direct functions such as NORMDIST, a starting seed may be specified for comparison across options, and the numbers do not recalculate with each worksheet change. →Data→Analysis:→Data Analysis→Random Number Generator→ Number of Variables Enter the number of columns of values that you want in the output table. Default = 1 (or defined output range). Number of Random Numbers Enter the number of data points that you want to see. Each data point appears in a row of the output table. For example, 3 “Variables” and 20 “Random Numbers” = 60 actual random data points. Default = 1 row of numbers (or defined output range). Parameter Options for the Chosen Distribution There is also a Patterned distribution, which is actually just a way to generate a deterministic sequence of numbers (similar to Excel’s Fill capabilities). This is characterized by a lower bound and an upper bound, a step, repetition rate for values, and a repetition rate for the sequence. Number of Variables and Number of Random Numbers are not applicable. Discrete This is characterized by a value and the associated probability range. The range must contain two columns: The left column contains values, and the right column contains probabilities that are associated with the value in that row. The sum of the probabilities must be 1. 9 10 11 12 13 E Value 2 3 4 5 F Prob 0.10 0.35 0.25 0.30 In this case, input range = E10:F13. Random Seed Enter an optional integer value from which to generate random numbers. You can reuse this value later to produce the same random numbers. Output Range If specified Number of Variables and Number of Random Numbers, enter the reference for the upper-left cell of the output table. Excel automatically determines the size of the output area and displays a message if the output table will replace existing data. Otherwise, enter the range to be filled with random numbers. New Worksheet Ply Click to insert a new worksheet in the current workbook and paste the results starting at cell A1 of the new worksheet. To name the new worksheet, type a name in the box. New Workbook Click to create a new workbook in which results are added to a new worksheet. Run Length Typical simulations have 500-1000 trials, but they could be much larger. The precision of any simulation estimate increases as the run length increases. Repeated batches of samples are more like each other (i.e., they show less variability) as the sample size increases; therefore, they are more precise. No definitive optimal number of trials. Smaller samples may not be precise enough. Larger samples take more computer time and space. Two Methods to Compute Run Length 1. Experimental Method A. Pick an initial sample size, say 100. B. Perform 5-10 simulations (with different seeds) using this run length and compare the estimates of the outcome measure. C. If the estimates are too far apart, increase the run length and go to Step B. 2. Sampled Standard Error Method A. Pick an initial sample size, say 100. B. Perform the simulation once, and compute the sample standard deviation S of the output values (STDEV in Excel). C. Determine the desired confidence interval ±A , i.e., the accuracy that you wish to obtain in estimating the mean. For example, if you wish estimated profit to be no more than ± $5 from the true average profit, A = 5. D. Determine your desired confidence level 100(1−α)%, and compute the z-value of α/2. For example, if you wish 99% confidence (α=.01), enter =NORMSINV(1−(.01/2)), which will yield a z-value of 2.575. E. Compute an estimated required sample size n = (zS/A)2. F. Replicate the simulation an additional n−100 times. G. For completeness, it would be a good idea to calculate a new S based on the n simulations and re-compute n in step E. Repeat the procedure if the new required n has grown. Output Analysis Primary Types of Output Analysis for Simulations 1. Histogram 2. Summary Statistics 3. Risk Analysis Example Used Throughout this Section “Output Analysis.xlsx” A 1 B C D E F Sample Profit Output from a Simulation with 20 Trials 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 Trial Profit 1 $50,386 2 $38,888 3 $62,023 4 ($12,000) 5 $24,960 6 $24,756 7 $12,000 8 $14,744 9 $44,421 10 $23,000 11 $25,432 12 $36,987 13 $85,213 14 $54,122 15 $23,222 16 $13,000 17 $55,634 18 $33,352 19 $41,904 20 ($5,235) Profit $10,000 $20,000 $30,000 $40,000 $50,000 $60,000 $70,000 24 Histogram Typically the first phase of output analysis Essentially provides a probability distribution of output Provides a visual representation central tendency, skewness, dispersion, and outliers (extreme risk) Excel’s Histogram Tool →Data→Analysis:→Data Analysis→Histogram→ Input Range Range Containing the data to be analyzed. Bin Range (Optional: If omitted, Excel creates evenly distributed bins between the min and max values.) A “bin” is an interval of numbers. For each defined bin, a histogram counts the number of input range values that fall within the bin and then graphs it in a bar chart. A defined bin range in Excel is a sequence of increasing numbers representing the boundary values of each bin. The first bin, then, equals −∞ to the first number in the bin range. The second bin equals every value > the first number in the bin range and ≤ the second number in the bin range, etc. Any values counted above the final number in the bin range are given the label “More” in the histogram. (Note: the bins do not have to be equally spaced.) While bin selection may have a logical basis, it may well be based on judgment. Clearly, a different set of bins will change the appearance of the histogram to some degree. Labels Select if the first row (or column) of your bin range has a label. If checked, this label will print on the tables and charts. Output Range Enter the reference for (only) the upper-left cell of the output table. Excel automatically determines the size of the output area and displays a message if the table will replace existing data. New Worksheet Ply Click to insert a new worksheet in the current workbook and paste the results starting at cell A1 of the new worksheet. To name the new worksheet, type a name in the box. New Workbook Click to create a new workbook in which results are added to a new worksheet. A 1 B C D E F G Profit Frequency Sample Profit Output from a Simulation with 20 Trials 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 Trial Profit 1 $50,386 2 $38,888 3 $62,023 4 ($12,000) 5 $24,960 6 $24,756 7 $12,000 8 $14,744 9 $44,421 10 $23,000 11 $25,432 12 $36,987 13 $85,213 14 $54,122 15 $23,222 16 $13,000 17 $55,634 18 $33,352 19 $41,904 20 ($5,235) Profit $10,000 $20,000 $30,000 $40,000 $50,000 $60,000 $70,000 10000 2 20000 3 30000 5 40000 3 50000 2 60000 2 70000 1 More 1 24 In addition to the frequency table that’s automatically generated, three other output options are available in any combination. Pareto (sorted histogram) Presents the frequency chart in decreasing order of frequency. Cumulative Percentage Adds cumulative percentage column (line) to the frequency charts (histogram chart). Chart Output Select to generate an embedded histogram chart with the output table. Summary Statistics →Data→Analysis:→Data Analysis→Descriptive Statistics→ H 3 I Profit 4 5 Mean 32340.45 6 Standard Error 5162.526 7 Median 8 Mode 29392 #N/A 9 Standard Deviation 23087.52 10 Sample Variance 5.33E+08 11 Kurtosis 0.411262 12 Skewness 0.198427 13 Range 97213 14 Minimum -12000 15 Maximum 85213 16 Sum 17 Count 646809 20 18 Largest(3) 55634 19 Smallest(3) 12000 20 Confidence Level(95.0%) 10805.29 For a range of data, Excel can automatically calculate basic statistics, as shown above. Include the output heading in your input range and check the “Labels in first row” box if you want the heading printed on your report. In this example, no output value appeared more than once, so no mode was provided. The confidence interval was provided for a 5% significance level (which can be changed). The “Kth Largest” (“Kth Smallest) boxes are selected if you want to include a row in the output table for the kth largest (smallest) value in the data range. A 1 provides the maximum (minimum) in the data set. The skewness describes the asymmetry of the distribution relative to the mean. A positive skewness indicates that it has a longer right-hand tail. A negative skewness indicates skewness to the left. Kurtosis describes the peakedness or flatness of the distribution relative to the Normal distribution. A positive value indicates a more peaked distribution, while a negative value indicates a flatter one. Quartiles First quartile: =QUARTILE(B4:B23,1) Third quartile: =QUARTILE(B4:B23,3) Interquartile range (the central 50% of the data): =QUARTILE(B4:B23,3)−QUARTILE(B4:B23,1) Risk Analysis Simulations are often conducted to analyze risk in some way, e.g., the probability of being late on a construction project or the probability of losing money. This is where the distribution of the output, not just the mean, becomes particularly important. Multiple runs of the simulation are particularly advised to when seeking answers to these types of questions. Examples Probability of output being less than $10,000 (% of output < $10,000) =PERCENTRANK(B4:B23,10000) (Note that PERCENTRANK interpolates if no value matches.) Probability of output being greater than $72,000 =1−PERCENTRANK(B4:B23,72000) Probability of output being between $5000 and $67,000 =PERCENTRANK(B4:B23,67000)−PERCENTRANK(B4:B23,5000) What are the 95% central interval limits (α = .05)? (In other words, what are the .025 and .975 quantiles?) =PERCENTILE(B4:B23,.025) and =PERCENTILE(B4:B23,.975) (Note that this is not a 95% “confidence interval.” Instead, we are estimating the proportion of the data that we expect to be within the given limits based upon the results of the simulation. We are defining the interval based upon the central proportion of the data.) The Data Analysis “Rank and Percentile” tool provides cumulative distribution information. →Data→Analysis:→Data Analysis→Rank and Percentile→ 3 I Profit H Point K Percent J Rank 4 13 $85,213 1 100.00% 5 3 $62,023 2 94.70% 6 17 $55,634 3 89.40% 7 14 $54,122 4 84.20% 8 1 $50,386 5 78.90% 9 9 $44,421 6 73.60% 10 19 $41,904 7 68.40% 11 2 $38,888 8 63.10% 12 12 $36,987 9 57.80% 13 18 $33,352 10 52.60% 14 11 $25,432 11 47.30% 15 5 $24,960 12 42.10% 16 6 $24,756 13 36.80% 17 15 $23,222 14 31.50% 18 10 $23,000 15 26.30% 19 8 $14,744 16 21.00% 20 16 $13,000 17 15.70% 21 7 $12,000 18 10.50% 22 20 ($5,235) 19 5.20% 4 ($12,000) 20 0.00% 23

Description

Related Documents

Free up your schedule!

Take 5 seconds to unlock