Lesson 4: Precision control

 

 

 

A question often asked about Monte Carlo simulation is how many samples should be run. If you simply want a smooth histogram or cumulative plot, then 3000 – 5000 samples will generally be enough unless the output takes an unusual shape.

However, if you need for example to know precisely a cumulative percentile like the P50 or P90, or another statistic like the standard deviation, the answer is more complicated. First of all, we need to state what we mean by ‘precisely’. For a cashflow analysis of a $1 billion investment, +/- $1 million might be considered sufficiently precise, but not for a $10 million investment.

Terminology like “+/- $1 million” also needs to be qualified by some explanation of certainty, for example:

95% confident that the P90 is precise to +/- $1 million for the operating costs

75% confident that the mean is precise to +/- $100,000 for the operating costs

In a model we may have several outputs, and for each we may have one or more precision requirements like:

95% confident that the P90 is precise to +/- $1 million and 75% confident that the mean is accurate to +/- $100,000 for the operating costs

and

95% confident that the P90 is precise to +/- $1 million and 75% confident that the mean is accurate to +/- $100,000 for the capital investment

ModelRisk offers a precision control feature that ensures that a Monte Carlo simulation has been run with a sufficient number of samples to ensure that any number of precision requirements have been achieved.

Setting up a precision requirement

It is recommended that you open the file Precision control.xls to follow the explanation.

The figure below illustrates a model in which a precision requirement has been set for the mean of the NPV output:

The function in cell C5 is:

=VosePrecisionMean(C4,10000,0.9)

It is checking to determine whether the mean of the simulated values for cell C4 has been determined to within $10000 with 90% confidence. The function, like all VosePrecisionXxxx functions, returns one of three results:

 

• "No simulation results" - if the model has not yet been run
• FALSE - if the level of precision has not yet been achieved
• TRUE - if the level of precision has been achieved

 

In row 18 another set of functions is checking the net income calculations for each year:

The function in cell C18 is:

=VosePrecisionPercentile(C17,0.95,10000,0.9)

It is checking to determine whether the 95^th percentile of the simulated values for cell C17 has been determined to within $10000 with 90% confidence. The formula is copied across the rest of the row to check the precision for each year of net income.

ModelRisk has the following set of VosePrecisionXxxx functions:

VosePrecisionMean(Cell Reference, Precision, Confidence, Simulation number)

VosePrecisionStdev(Cell Reference, Precision, Confidence, Simulation number)

VosePrecisionPercentile(Cell Reference, Cumulative Probability ,Precision, Confidence, Simulation number)

VosePrecisionProbability(Cell Reference, Value ,Precision, Confidence, Simulation number)

Where the parameters are as follows:

Cell Reference - a reference to a spreadsheet cell containing a simulated value

Precision - the required level of precision, e.g. 10000

Confidence - the required level of confidence that the specified precision has been met. A probability value, so it must be between 0 and 1 (but not 0 or 1).

Cumulative Probability - the required cumulative probability value. Must be between 0 and 1 (but not 0 or 1). This is used when one wants to know the provability of falling below (or equivalently above) a particular target Value has been determined with the required level of precision.

Value - the target value for which the cumulative probability is required.

Simulation number – an optional parameter specifying the simulation number. Used when running multiple simulations. It must be an integer >=1. Set to 1 if omitted.
 

More examples

In order to make the simulation stop when the required precision has been achieved, we link the VosePrecisionXxxx functions together with the function VoseSimStop. The parameters for this function are:

VoseSimStop(LogicalTestValue,Name)

LogicalTestValue - this must refer to a cell or include a formula that returns either TRUE or FALSE

Name - the name of the Stop condition, and is optional so it can be omitted. Its purpose is identify which conditions have and have not been met during the simulation.

In the example model we now add a VoseStop function for the mean:

 

We can also add VoseStop functions for the Net Income variables:

The formula in this cell is:

=VoseSimStop(VosePrecisionPercentile(C17,0.95,10000,0.9),"Net income "&C7)

where:

LogicalTestValue = VosePrecisionPercentile(C17,0.95,10000,0.9), which will make the VoseStop function request the simulation too stop when the precision is achieved.

Name = "Net income "&C7", which calculates to be "Net income 2016", and changes the year as the formula is copied across the row.

 

Start the simulation

In order to run the model with simulation control, we must have at least one VoseStop function in the model. Precision control must also be switched on in the ModelRisk ribbon ('P%' – colored background = ON):

 

A maximum number of samples must also be entered into the Samples dialog in the ribbon, say 1000000.

If we now click Start in the ribbon, ModelRisk will continue simulating until either ALL VoseSimStop functions have requested a termination (i.e. all conditions have been met), or the maximum number of samples has been run.

During the simulation, a window will appear showing the progress on achieving the required precisions.
 

All VosePrecisionXxxx functions can also be entered into two cells together as an array, as shown here (cells C5:C6). When this is done, the second cell returns an estimate of how many more samples are still needed to achieve the required precision (here, 16787 more samples):