SIMULATION
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 2 | distribution 2 | parameter1 | parameter2 | … |
| … | … | … | … | … |
| Cell k | distribution k | parameter 1 | parameter 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:
| B1 | norm | 5.5 | 1.2 | |||||
| B2 | beta | 3 | 7 | 1 | 1.2 | |||
| B3 | csample | 1 | 3 | 7 | 10 | 12 | 15 | 20 |
| 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:
