Part 2. MM4XL Tools > 1. Strategic Tools > Risk Analyst > 4. Functions > 2. Utility Functions > mmOPTNUM(InputRng, [Optional: StablePeriods], [Optional: SelectionLimit])

Risk Analyst

mmOPTNUM(InputRng, [Optional: StablePeriods], [Optional: SelectionLimit])

This function finds the number of simulations for an Output variable where the standard deviation (sd) of the mean of the simulation trials tends to stabilize. This is useful information to reduce the number of trials and save time during the simulation or, alternately, to increase the number of trials if the analysis is not stable enough.

There is no real statistical backup to the assumption that the sd of the mean is an estimator of the time to stop producing simulation values because the next value will not contribute to the analysis with enough incremental information. However, Koller suggests that: A simple method for determining whether a sufficient number of comparisons have been made is to inspect the mean of the answer variable distribution. An answer variable in Risk Analyst software is clearly an Output variable.

This function takes three arguments

InputRng is the range with the simulation values for the variable under inspection.

StablePeriods is an optional argument that sets the number of periods that the sd of the mean has to be equal or smaller than the SelectionLimit. When missing, the default number of stable periods is set to 20.

SelectionLimit is an optional argument that sets the level at which the absolute difference between the sd at time t+1 has to be equal to or smaller than the same limit for the sd at time t. When missing, the default level is 0.0005.

 Monte Carlo Simulation Software: Management Process Risk Analysis

The function returns zero if it cannot find an optimal number of simulations. This suggests that more simulation trials have to be produced.

The following two functions produce the same result:

=mmOPTNUM(B14:B5013, 20, 0.0005)


The following chart refers to the sd of the mean of 5000 trials. The function mmOPTNUMSIM suggests that 976 trials are required to achieve stability at the 0.0005 level. Note that the maximum value of the vertical axis was rescaled to allow the curve to be seen, which otherwise was pushed down to the zero level by extreme values occurring in the initialization phase of the algorithm.

 Monte Carlo Simulation Software: Management Process Risk Analysis

Lifetime license:

Price: euro

Vote this tool
We proudly serve
Your vote
vote1 vote2 vote3 vote4 vote5