SCENARIOS

You are here:

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 2parameter1parameter2
distribution k parameter 1parameter 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:

norm5.51.2
beta3711.2
csample13710121520

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: