When using add-ins such as @RISK™ or RiskSolver™ for simulation modelling within Excel, there can sometimes be a tendency to also use Excel’s RAND function to generate random numbers in some places. This would be particularly likely if one had attempted to build a simulation model without the use of add-ins (such as using RAND in the spreadsheet and some VBA code to do the repeated recalculations), and where – upon using the add-in – additional sources of uncertainty are introduced into the model, rather than a simple replacement of the RAND functions. But there are several reasons to fully replace the RAND functions by the equivalent Uniform distribution).
The main reasons for complete replacement of the RAND functions are:
- Sensitivity Analysis. Tornado charts and other sensitivity analysis tools within a particular add-in such as @RISK™ will only recognise their own distribution functions as a source of uncertainty; sources of uncertainty corresponding to RAND will generally not be represented as a source of uncertainty that is shown in the sensitivity analysis
- Controllability. Simulation packages such as @RISK™ allow for simulations to be repeated by fixing the random number seed, so that the same set of random numbers is regenerated when a simulation is re-run (for the same number of recalculations). Whereas the seed of the .Rnd function in Excel can be controlled, that of the RAND cannot, and hence a simulation will not (easily) be repeatable.
- Accuracy. Commercial add-ins will often have slightly better random number generation algorithms than those in Excel (in terms of representing the distribution of a population accurately with small samples), although this is probably only a relatively small point of advantage in most cases, as the difference in accuracy is generally of not too much importance