《MONTE CARLO SIMULATION & SPREADSHEET MODELING.ppt》由会员分享，可在线阅读，更多相关《MONTE CARLO SIMULATION & SPREADSHEET MODELING.ppt（10页珍藏版）》请在三一办公上搜索。
1、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 mode
2、l 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
3、 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 m
4、odel.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,invento
5、ry,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 conditi
6、ons 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
7、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 sampli
8、ng 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
9、,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
10、 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
11、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 a
12、nd 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 cop
13、y 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 var
14、iables 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 us
15、e 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 ra
16、ndom 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 fro
17、m 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)
18、=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)=gene
19、rates 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
20、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 R
21、andom 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)=
22、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,
