SCENARIOS
AXCEL.SCENARIOS function
This function generates scenarios by generating random observation from predefined distributions or empirical sample. It also takes into the account the correlation between scenarios, if a correlation matrix is provided.
Syntax
AXCEL.SCENARIOS(n, scenarios, [correlations], [method])
The AXCEL.SCENARIOS 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.
scenarios Required. This is a table of distributions and its parameters. The structure of table is like this:
| distribution 1 | parameter 1 | parameter 2 | … |
| distribution 2 | parameter1 | parameter2 | … |
| … | |||
| distribution k | parameter 1 | parameter 2 | … |
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, if you 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:
| norm | 5.5 | 1.2 | |||||
| beta | 3 | 7 | 1 | 1.2 | |||
| csample | 1 | 3 | 7 | 10 | 12 | 15 | 20 |
correlations Optional. This is matrix of correlation between your scenarios. Default is 0 correlation. For instance, in our previous example if scenario 1 (norm) has 0.45 correlation with scenario 2 (beta), and scenario 2 (beta) has -0.36 correlation with scenario 3 (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.
Example:
Imagine that our distribution table from previous example is located at cells A1:H3, the correlation matrix is defined in cells J1:L3, and we would like to run 50 random samples, you need to run:
=AXCEL.SCENARIOS(50,A1:H3,J1:L3,”LHS”)
which results in a matrix of 50 rows shows below:
