SIMULATION

You are here:

AXCEL.SIMULATION function

This function generates a monte carlo simulation by generating random observation from predefined distributions or empirical sample and recalculate the target cell based on generated scenarios. It also generates a comprehensive report if it requested by the user. It also takes into the account the correlation between scenarios, if a correlation matrix is provided.

Syntax

AXCEL.SIMULATION(n, input, [correlations], [method], [report], [names])


The AXCEL.SIMULATION function syntax has the following arguments:

n Required. This is the number of scenarios which the user would like to generate. If n is less than 5, the function runs with n = 5 and if n is greater than 10`000, the function sets n = 10000.

input Required. This is a table of input cells with corresponding distributions and parameters, and the target cell. The structure of the table is like this:

Cell 1 distribution 1 parameter 1 parameter 2
Cell 2distribution 2parameter1parameter2
Cell k distribution k parameter 1parameter 2
Cell m

Where Cell 1, Cell 2, … are the location (address) of the input cells and Cell m is the location (address) of target cell.

The available distributions with available parameters are:

norm mean sd
triangle min mode(likeliest) max
unified min max
lnormal (log normal) meanlog sdlog
beta min max shape1 shape2
cbpert (classic beta pert) min likeliest max
vbpert (vose beta pert) min likeliest max
gamma min shape scale
weibull min shame scale
maxExtrem (evd) likeliest scale
minExtrem likeliest scale
logistic mean scale
student t median df scale
exponential rate
pareto min shape
binomial trials probability
poisson lambda
hypergeometric success trials population
nbinomial (negative binomial) trials probability
geometric probability
discrete uniform min max
true-false (yes or no) size probability of true
csample (continuous sample) obs 1 obs 2 obs 3
dsample (discrete sample) obs1 obs 2 obs 3

For instance, we have three inputs located in Cells B1 to B3 and the result (target cell) is in B4. Also, imagine that for inputs from B1 to B3, we have a scenario with normal distribution with mean of 5.5 and standard deviation of 1.5, a scenario with beta distribution of minimum 3, maximum 7, shape 1 equal to 1 and shape 2 equal to 1.2, and lastly you have a sample of discrete numbers of [1, 3, 7, 10, 12, 15, 20] for the last scenario, your scenario table looks like this:

B1norm5.51.2
B2beta3711.2
B3csample13710121520
B4

correlations Optional. This is matrix of correlation between your scenarios. Default is 0 correlation. For instance, in our previous example if B1 (norm) has 0.45 correlation with B2 (beta), and B2 (beta) has -0.36 correlation with B3 (csample), your correlation matrix would be like this:

1
0.45 1
0 -0.36 1

You just need to provide the lower triangle of your correlation matrix. Anything in upper-triangle will be ignored by the function.

method Optional. Default is “SRS”. You have the option of SRS (Simple Random Sampling) or “LHS” (Latin Hypercube sampling). If any empirical sample is provided as part of distributions, the function sets method to “LHS”, automatically.

report Optional. If set True a comprehensive report is generated by the function. It is False by default.

names Optional. By default the generated report uses the cells address to identify them in the report. If you would like to use customized names, you should provide a vector of names with the same length of the number of total input and outputs (4 in previous example). Otherwise, the function ignores the inputs and use the cell addresses.

Example:

Imagine that our input table from previous example is located at cells A7:I10, the correlation matrix is defined in cells K7:M9, we would like to run 50 simulations, we want a comprehensive report and the cell names are located at A1 to A4, you need to run:

=AXCEL.SIMULATION(50,A7:I10,K7:M9,”LHS”,1,A1:A4)

which results in a vector of 50 simulations shows below:

As you can see, an interactive report is generated in the task pane which you can expand in your browser: