Building models that are efficient

See also: Model design introduction, Building models that are easy to check and modify, Colour coding models for clarity, Using range names for model clarity

A model is most efficient when:

1. It takes the least time to run;

2. It takes the least effort to maintain;

3. It has a small file size;

4. It requires the least amount of assumptions; and

5. It supports the most decision options

Least time to run

ModelRisk is an add-in to Excel and, although it is well integrated into Excel, it will inevitably suffer somewhat in performance because of the interface. We will look at a few hints for making Excel run faster, then making your simulation software run faster, and then making a model that gets the answer faster.

Making Excel run faster

• Avoid array functions as they are slow to calculate, although faster than an equivalent VBA function;

• Use  megaformulae (with caution) as they run about twice as fast as intermediary calculations, and ten times as fast as VBA calculations;

• Custom Excel functions run more slowly than built-in functions but speed up model building and model reliability;

• Avoid links to external files; and

• Keep the simulation model in one workbook.

Making your simulation software run faster

• Turn off the Update Display feature. It makes an enormous difference if there are imbedded graphs;

• Use Multiple CPUs if your simulation software offers this. It can make a big difference;

• Avoid the VoseCumulA( ), VoseDiscrete( ), VoseDUniform( ), VoseRelative( ) and VoseHistogram( ) distributions with large arrays if possible as they take much longer to generate values than other distributions (see Figure 1 below);

• Run Bootstrap analyses and Bayesian distribution calculations in a separate spreadsheet when you are estimating uncorrelated parameters, fit the results using your simulation software's fitting tool, and if the fit is good use just the fitted distributions in your simulation model. This does have the disadvantage, however, of being more laborious to maintain when more data become available.

Smallest file size

• Megaformulae reduce the file size considerably;

• Maintaining large data sets in your model will increase the file size. ModelRisk will also run slower. It is better to do the analysis outside the spreadsheet, and copy across the results;

• Sometimes large data sets or calculation arrays are used to construct distributions (e.g. fitting first or second order non-parametric distributions to data, constructing Bayesian posterior distributions, and Bootstrap analysis). Replacing these calculations with a fitted distribution can have a marked effect on model size and speed.

Read on: Colour coding models for clarity