Running multiple simulations

It is very useful to be able to run several scenarios on the same model in order to compare different decision strategies. For example, NPV of a capital invesment is built to assess whether it would be better to invest an extra amount in development to give access to a larger market.

There are three parts to running multiple simulations of a model, namely:

  • Set up the model to work with multiple simulations

  • Set up how the model simulation will run

  • Run the model and create reports

Set up the model to work with multiple simulations

The function VoseSimTable is the primary tool for changing values between different simulation runs. The rest of the model is linked to cells containing this function and the model logic adapted to ensure that it works with all the values that the VoseSimTable function returns.

In the NPV of a capital invesment example, the VoseSimTable(ListOfValuesWhenSimulating,ValueUsedWhenNotSimulating) function appears in cells H41 and I41. Cell H41 holds the formula =VoseSimTable(H39:H40,H39):

 

During the first simulation, this cell will take the value 0 from cell H39. In the second simulation, it will take the value 2400000 from cell H40. When a simulation is not running, as shown above, it takes its value from cell H39. The figure above shows which cells are dependent on these variables.

At the same time, cell I41 contains the formula =VoseSimTable(I39:I40,I39), so it also changes with each simulation. The result is that in the first simulation, cells H41 and I41 will return the values 0 and 0% respectively (the decision not to make an extra investment), and in the second simulation they take the values 2400000 and 16% respectively (the decision to make the extra investment).

In addition, the cell range G45:K46 contains a table which will report the results for the NPV (calculated in cell C4) of the project using the functions VoseSimMean and VoseSimPercentile.
 

 

There is a range of VoseSimXxxx functions available that will report the results like mean, a specific percentile, standard deviation, etc. directly into the spreadsheet after a simulation run. Each of these functions has an optional extra parameter where the user can specify the simulation number to report values from:

The formula in cell H45 is VoseSimMean($C$4,F45) where C4 is the location of the NPV calculation, and F45 is 1, so this function returns the mean NPV for simulation #1 (called “Base”). Similarly, H46 returns the mean NPV for simulation #2 (called “Extra”).

This provides a convenient way of seeing a summary of results within the spreadsheet. Tables like this can also be nominated to appear in the Results Viewer at the end of a simulation. To do this, we highlight the cell array:

Then select ‘Add Simulation Statistics Report’ from the Output/Input window:

And enter a recognizable name:

As we will see later, this will now appear in the Results Viewer.

Set up the model to work with multiple simulations

We need to click on Settings in the ModelRisk ribbon and specify a couple of settings:

The Simulation names can be typed in or preferably imported by clicking the icon to the right and selecting the appropriate array within the model. Whatever text is in these cells will be converted to "Name1", "Name2" format.

It is advisable to select All Use Same Seeds in the Multiple Simulation Seeds option. This will ensure that any differences between the results are not due to ModelRisk using different random numbers for the same variables. There is an option to use different seeds for each simulation – the only purpose this has is to run exactly the same model several times (i.e. no VoseSimTable functions) to see how different the answers can be with a particular number of samples.

In the above settings, the seed has also been manually set to 1 which ensures that we can reproduce the results precisely if required.

Now save the model. This will save all the settings made above.

 

Run the model and create the reports

Clicking the ModelRisk Start icon will make the model run both simulations, each for 5000 samples. The progress is shown in the Simulation Progress pop-up.

This screen capture, taken mid-simulation with the update model option on, shows the model in the middle of its second simulation. The values 2400000 and 16% are currently being used in cells H41 and I41. The statistics for Simulation #1 are complete (row 45), but not for Simulation #2 (which reports the mean as this can be calculated iteratively, but not the Percentiles which cannot).

Once complete, the Results Viewer will open:

 

Here a Cumulative Ascending plot has been selected, and both simulations ticked (top left window) so they appear together. In fact, this shows that for every cumulative probability value, the Extra scenario (blue line) gives a higher NPV so – all else equal – this says the decision should be to go with the extra investment.

Another tab, Statistics Report, shows the simulation results generated in the spreadsheet table:

These results can also now be exported to Word, PowerPoint, PDF or Excel.

 

ModelRisk

Monte Carlo simulation in Excel. Learn more

Tamara

Adding risk and uncertainty to your project schedule. Learn more

Navigation

FREE MONTE CARLO SIMULATION SOFTWARE

For Microsoft Excel

Download your free copy of ModelRisk Basic today. Professional quality risk modeling software and no catches

Download ModelRisk Basic now

FREE PROJECT RISK SOFTWARE

For Primavera & Microsoft Project

Download your free copy of Tamara Basic today. Professional quality project risk software and no catches.

Download Tamara Basic now
-->