Excel and Monte Carlo Applications



Monte Carlo Applications Using Excel

In deterministic modeling, we establish an outcome which corresponds to a particular set of model inputs. River oxygen models typically input flow, temperature, and values for rate constants and determine the dissolved oxygen concentration associated with the value for a control variable, e.g. the BOD load. A deterministic model yields a single value for that outcome (DO) based on the perturbation (BOD) and the set of model inputs provided.

In probabilistic analysis, we calculate the frequency of occurrence for an outcome associated with a particular set of model inputs. Here, a value for a control variable (e.g. BOD) is again used, but model inputs (e.g. flow, temperature, and values for rate constants) are now input from randomly sampled distributions representing their natural variability (flow, temperature) or uncertainty (rate constants). Probabilistic models generate a distribution for outcome frequencies for the value for the control variable and the distributions of model inputs. This approach is termed Monte Carlo analysis.

Variability in the model inputs used in surface water quality analysis may be described by one of several idealized distributions, including uniform, normal or log normal. The uniform distribution assumes that there is an equal likelihood of occurrence over the range specified by two bounds. The normal distribution is a symmetrical bell-shaped curve with the most likely value in the center and diminishing likelihoods at the extremes. The log normal distribution is similar to the normal distribution, but values are skewed, i.e. that do not fall symmetrically about the most likely value.

The key to Monte Carlo analysis lies in functions which describe the distribution of model inputs. Excel offers applications which utilize cumulative distribution functions to yield values for a model input given specification of the distribution type and a mean and standard deviation for the distribution.

Normal distribution

The cumulative distribution function identifies the probability that a data value would exceed all of the other values in the data set. For example, in the accompanying figure, a data value of 39.6 exceeds 40% (p = 0.4) of the data set.

Application of Excel to Monte Carlo analysis comes at it from the other direction.

x = Application.NormInv(rnd(), mean, sd)

A random number generator is utilized to establish a probability and that probability is applied to the cumulative distribution function to yield a data value.

Applied through many iterations, this process will yield the normal distribution of data values described by the specified mean and standard deviation.

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download