Monte Carlo Simulation using Norminv
Generating normally distributed random numbers for Monte Carlo Simulation requires only 4 cells:
the mean, the stzandard deviation, the uniformly distributed random number, and the normally-distributed number.
Suppose the mean of the normal distribution of the random variable we need for the simulation is in cell B7,
and the standard deviation is in cell B8.
If we put a uniformly distributed random number in cell B9 using the familiar
=RAND() function, then we can convert this into the desired normally distributed
random number by using the Excel formula =NORMINV(B9,B7,B8) -- put
this formula wherever your model needs the normal random variable to be.
Normal Approximation to the Binomial Distribution
If the desired distribution is not actually normal, but it
is a binomial distribution with a large number of "trials," we can use the
normal approximation to the binomial distribution. This requres two
additional cells, the probability of "success" and the number of "trials,"
and the mean and standard deviation cells become endogenous.
Suppose the number of "trials" is in cell B3 and the probability of "success"
on each trial is on cell B6. then the mean of the normal distribution
in cell B7 becomes the Excel formula =B6*B3 and the standard deviation
of the normal distribution in cell B8 becomes =SQRT(B3*B6*(1-B6))
Then the number calculated as before, =NORMINV(B9,B7,B8)
is the normal approximation to the binomoal. Since the binomial
distribution is restricted to integers, we need to round this number to zero
decimal places using the =ROUND function.
For an example, see the spreadsheet http://www.gsu.edu/~dscthw/3100/Modeling/overbook.xls