MONTE CARLO SIMULATION & SPREADSHEET MODELING.ppt

Monte Carlo Simulation&Spreadsheet Modeling,Why Simulate?,When we use the word simulation,we refer to any analytical method meant to imitate a real-life system,especially when other analyses are too mathematically complex or too difficult to reproduce.Without the aid of simulation,a spreadsheet model will only reveal a single outcome,generally the most likely or average scenario.Spreadsheet risk analysis uses both a spreadsheet model and simulation to automatically analyze the effect of varying inputs on outputs of the modeled system.,Why Monte Carlo?,Monte Carlo simulation was named for Monte Carlo,Monaco,where the primary attractions are casinos containing games of chance.Games of chance such as roulette wheels,dice,and slot machines,exhibit random behavior.,The random behavior in games of chance is similar to how Monte Carlo simulation selects variable values at random to simulate a model.When you roll a die,you know that either a 1,2,3,4,5,or 6 will come up,but you dont know which for any particular roll.Its the same with the variables that have a known range of values but an uncertain value for any particular time or event(e.g.interest rates,staffing needs,stock prices,inventory,phone calls per minute).,Modeling Uncertainty,What do you do with uncertain variables in your spreadsheet?For each uncertain variable(one that has a range of possible values),you define the possible values with a probability distribution.The type of distribution you select is based on the conditions surrounding that variable.Distribution types include:Discreet Bernouilli(Success,Failure in one trial)Binomial(Number of Success/Failure in n trialsMulti-Nominal(number of times we observe a particular outcome in n trials.)Poisson(number of occurrences of a particular event during a time period 0 to time period t)Continuous Variables:Exponential Distribution Normal Distribution,Excel Functions:RAND():a random number between(0,1)RANDBETWEEN(a,b):a random number between values of(a,b),What happens during a simulation?,A simulation calculates multiple scenarios of a model by repeatedly sampling values from the probability distributions for the uncertain variables and using those values for the cell.Two ways to simulate multiple scenarios.SIMTABLE and DATA TABLESWhat do we do with the output of the simulation?-Measure Risk and optimize managerial choices,capacity,quality,markets to enter,product to invest R&D.Summary statistics:Averages,Standard Deviations,MedianPlot distribution of the values of the performance measure and the relative frequencies,using the Frequency function.To use the frequency function:1.put in the bin values to a column,2.highlight the empty column next to bin column,as many rows as the number of bin rows.=FREQUENCY(bin array(highlight the cells containing the bin values),Data Array(highlight the column that contains the data)then CTRL+SHIFT+ENTER,Simtable vs.Data Tables,Simulation Table from Simtools:By leaving an empty column to the left,Copy/Refer to the cell which contains the random formula.highlight as many rows as the number of simulations you want have for the random formula.Go to tools/simtools/simtable,Simulation by Data Tables:Use one-way table.Set-up a one-way table referring to the following.Do not input any values on the left column and refer to an empty cell for the input.,Random formula,Empty Column,Excel will replicate the formula as many times as the number of rows,each time(each row)randomly evaluating the formula,You need to copy and paste special/values after the table is calculated,Another common technique is just to copy the random formula several times to replicate the simulation.,Simulating a Discreet Variable,1.A Bernoulli Random Variable:generate a single random variable between(0,1),Baby,Boy,Girl,1/2,1/2,Generate RAND(),IF(RAND()1/2,boy,girl),2.A Binomial Random Variable with n trials:generate n Bernoulli variables and count the number of times you have success in n trials.Say success is defined as having a boy,in this case to simulate the number of boys in n births,Repeat(1)n times in a column and count the number of times,the outcome is a boy.You can use the function COUNTIF(datarange,condition),Or use BINOMINV(RAND(.),n,p),Given our assumption about the probability distribution of our random variable(normal,poisson,binomial distribution),we can generate a draw from the distribution by using the following“general”technique.,possible value of our random variable,Probability distribution of the random variable X,This is by assumptions of the model Assumption should be justifies based on some historical data,information about the variable.,Cumulative and inverse cumulative probability distributions,x,a,0,1,Generate a random number between 0,1 and use INVERSE cumulative functions available from excel to map the random number between 0,1 to an X value,Rand()=b,x,NORMDIST(x,m,s,0):Probability distributionNORMDIST(x,m,s,1):Cumulative distributionNORMINV(a,m,s):Inverse cumulative distribution function 0a1,y=P(X=x),Other Inverse Cumulative Functions,Binomial Random Variable BinomDist(s,n,p,0)=calculates the probability of having s successes out of n trials when the probability of success in each trial is p.BinomDist(s,n,p,1)=calculates the probability of having Less Than and Equal To s successes out of n trials when the probability of success in each trial is p.BinomInv(Rand(),n,p)=generates a random number of successes out of n trials.Poisson Random VariablePoison(x,m,0)=calculates the probability of x occurrences of an event in a given time period when the mean occurrence rate of an event is m.Poison(x,m,1)=calculates the probability of number occurrences of an event in a given time period is less than and equal to x,when the mean occurrence rate of an event during that time period is m.PoisonInv(Rand(),m)=generates a random number of occurences for an event during a time period,when the mean(expected)occurrence rate of that event during that time period is m.Exponential Random Variable Denotes the random time between occurences of two identical events.If customer arrivals are random,then the time between two arrivals can be modeled as exponential variable with the expected time between two arrivals=m.P(X=x)=1/mExp(x/m)exp()is the exponential function in excel.P(Xx)=1-Exp(x/m)ExpoInv(rand(),m):generates a random time between the two occurrences of a particular event(e.g.customer arrivals).,How should we choose the probability distribution for our random variables?,Nature of the Variable Data(fit distributions)or TheoryLaw of Large Numbers works if we are interested in the average of a sum of random variables with arbitrary distribution.Relationship between Distribution Functions&Approximations:Binomial-Poisson-Normal,