Comparison of risk analysis Microsoft Excel add-ins
From Wikipedia, the free encyclopedia
The following is a comparison of various add-in packages available to do Monte Carlo probabilistic modeling and risk analysis. Add-ins covered are for Microsoft Excel on Windows.
Mac software and tools for other platforms, such as R or Matlab are not included.
^The Risk Kit Suite is available as portable (no admin-rights required) or installer version. It consists of Risk Kit for Monte-Carlo simulations, Risk Kit R for advanced statistical analyses, and Risk Kit Data for an online data feed of financial data and social statistics from the European Central Bank ECB, the World Bank, the Federal Reserve Economic Data FRED, Eurostat and Yahoo!Finance. Risk Kit Suite on corporate website: https://www.wehrspohn.info/en/products/risk-kit-suite.html
^"Archived copy". Archived from the original on 2007-05-25. Retrieved 2014-02-06.{{cite web}}: CS1 maint: archived copy as title (link)
^Excel has tools for calculating correlation statistics for a data set. This refers to the software's ability to fit correlation structures to a data set and, where more than one correlation structure is available, to compare the levels of fit.
^Some add-ins provide VBA versions of their UDFs, which allow the user to build their own stochastic UDFs in VBA. Some add-ins also provide VBA functions that control a simulation. This allows the user to create VBA macros that will automatically run simulations and generate results.
^Some add-ins allow calls to C++ routines. These allow the user to create a stand-alone DLL that represents a part of the model which needs to be as fast as possible by making use of the faster performance of the original code (usually C++)
^Provides the facility to calculate probability mass (or density), cumulative probability, moments, etc. of a distribution directly in the spreadsheet.
^Standard error messages like #VALUE! when an input parameter is invalid is replaced by a message describing the reason of the error.
^Tools to evaluate the distribution of the minimum or maximum sample from a set of IIDs
^Graphical interfaces to facilitate determining distribution of uncertainty of some model parameter based on expert opinion.
^Some add-ins do not have a custom results viewer and export the data directly into the spreadsheet for analysis using the spreadsheet's native charting and statistical tools. The number of draws is then restricted by the ability of the spreadsheet to handle the size of the generated data.
^The ability to replace a random variable with a fixed value.
^Some add-ins allow the user to automatically run a model multiple times replacing decision variables with different values for each simulation run
^Some add-ins allow the user to run a VBA macro before a simulation (e.g. to import data from an external source), after a simulation (e.g. to export the results to a database) or before or after a random draw of the model (e.g. to perform an optimization).
^Stopping a simulation run when an output variable generates an error is a useful feature for debugging a model
^After a simulation run the user is able to insert a specific generated result back into the spreadsheet. This is useful, for example, if one wants to investigate the generated scenario that created the greatest profit or loss.
^The ability to specify a required level of precision for output results, usually the accuracy with which the mean or a percentile of the output has been determined. Precision control will stop a simulation run when the required accuracy is achieved
^Some add-ins will create a compiled version of the spreadsheet model before simulation. This has the benefit of a far faster simulation speed, but will not work with many types of models e.g. models that include VBA calls or functions like OFFSET and VLOOKUP.
^Only Monte Carlo sampling is compatible with copula correlation structures. Using Latin Hypercube or Sobol sampling removes the possibility of using other correlation structures like Archimedean copulas.
^The ability to select a subset of the simulation results (e.g. scenarios that result in a loss) thereby allowing the user to investigate scenarios of greatest interest.
^In Excel the Insert Function dialog box includes a 'Help on this function'-hyperlink. Some add-ins use this feature to provide a direct link to the applicable help file topic.
^Some add-ins with visual interfaces and dialog boxes include a help icon that directly links to the applicable help file topic for that interface
^ZH = Chinese, EN = English, FR = French, DE = German, JA = Japanese, PT = Portuguese, ES= Spanish
Further reading[]
Albright, S. (2011), Data Analysis and Decision Making, 4th Edition, USA: Cengage Learning, ISBN978-0-538-47612-6
Charnes, J. (2012), Financial Modeling with Oracle Crystal Ball and Excel (2nd edition), USA: Wiley, ISBN978-0-471-77972-8
Day, A. (2003), Mastering Risk Modeling, Great Britain: Prentice Hall, ISBN0-273-65978-2
Evans, J.; Olson, D. (1998), Introduction To Simulation And Risk Analysis, United States of America: Prentice Hall, ISBN0-13-621608-0
Iman, R. L. and Conover, W. J., (1982). 'A Distribution-Free Approach to Inducing Rank Order Correlation Among Input Variables', Commun Statist-Simula Computa 11(3) 311-334
Lehman, D.; Groenendaal, H.; Nolder, G. (2010), Practical Spreadsheet Risk Modeling for Management, United States of America: CRC Press, ISBN978-1-4398-5552-2