RESEARCH ARTICLES | RISK + CRYSTAL BALL + ANALYTICS

Excel Simulation Show-Down: Comparing the top Monte-Carlo Simulation Tools

May 06 2011

Excel Simulation Show Down (Part 1) - Defining Inputs and Outputs

Over the last 3 months, we have seen 3 of the 4 major players in the Excel Monte-Carlo Simulation arena introduce new releases. We hear a lot of talk about which tool is best and the truth is there is no perfect answer – it’s a personal thing dictated by user skill, preference and need.

For this reason, we have created a series of videos showing comparing how each tool is used to apply Monte-Carlo simulation to a model / spreadsheet. Our focus will be on :

To keep the playing field level, we have used a simple additive model, which is simply defining a series of distributions (i.e. costs, budget items…), summing them up and analyzing the resulting sensitivity analysis. We have kept things simple, so we are not correlating any of the variables nor using any fancy math.

As you will see, there are definite differences AND similarities regarding how these packages tackle building a model. We are going to focus on those relating to inserting and copying input distributions as well as defining and analyzing model outputs. The objective is to compare the ease, usability and efficiency of each tool and give people the opportunity to choose for themselves which tool reflects their needs and preferences better.

At the time of this writing, all the tools have similar price points (around 800-1200$), with the exception of ModelRisk Standard, which is free.  If you would like to try any of these packages, with the exception of Solver (www.solver.com), you can download them from our Downloads sections and give them a whirl. You can also download the test model and compare for yourself.

Download Crystal Ball, @RISK, ModelRisk and ProModel Software, Example Models and White-Papers

PART 1: Introduction, Methodology and Oracle Crystal Ball 11.1.2.1

In this section, we present the comparison test, our objectives and the testing model. Since the first section is relatively short, we also present Crystal Ball’s sample model.

 

Loading the player ...

 

See on YouTube: PART 1: Introduction and Oracle Crystal Ball 11.1.2.1. Additive Model
Key Takeaways:

  • The interface is well thought through and very intuitive – as far as Monte-Carlo tools go.
  • Point & Click interface – Interface (ribbon) & menu driven
  • Distributions are automatically defined as Inputs.
  • Distribution logic is hidden
  • Automatic color coding ( a feature that is most appreciated by the reviewer)

 

PART 2:  ModelRisk Standard 4.0 Additive Model

ModelRisk Standard 4.0 was released a few weeks ago. Thought this is the 4th release of Vose’s ModelRisk,  ModelRisk is the latest entrant in the Excel Simulation Game. ModelRisk is different from the other tools in one major way… it uses copulas instead rank correlation (but that’s for another post). In the latest release they have repackaged the features to make the 4.0 Standard Edition all about Monte-Carlo simulation, therefore aptly suited for this comparison.

 

Loading the player ...

 

See on YouTube: PART 1: Introduction and Oracle Crystal Ball 11.1.2.1. Additive Model
Key Takeaways:

  • Comprehensive distribution gallery
  • Distributions need to be defined as Inputs in a separate step
  • Formula & Function based – can be applied through interface/ribbon
  • Detailed and information rich interface
  • Distribution logic is encapsulated in excel functions
  • Manual color coding using a special find function. This function makes sure you don’t miss anything and ensure some level of consistency.

PART 3:  Palisade @RISK Standard 5.7 Additive Model

Palisade’s @RISK like Crystal Ball has been around forever. They formula based approach to Monte-Carlo Simulation. They have a robust, usable and efficient modeling package with @RISK.

 

Loading the player ...

 

See on YouTube: PART 1: Introduction and Oracle Crystal Ball 11.1.2.1. Additive Model
Key Takeaways:

  • Comprehensive distribution gallery
  • Formula & Function based – can be applied through interface/ribbon
  • Good interface, though some of the buttons are hard to understand or are too small.
  • Distribution logic is encapsulated in excel functions
  • Automatic color coding when an @RISK function is entered ( a feature that is most appreciated by the reviewer)
  • Cell Pop ups for inputs and outputs.  Basically, when you highlight a cell, the Simulation & Model Details appear. A Cool feature.

PART 4:  Frontline Risk Solver 11.0 Additive Model

The basic version of Solver has been part Excel for more than 20 years… however they, like Vose, are relatively new to the simulation game. They have a nice interface and engaging graphics.  One of things that make them really different is that they are the makers of the Polymorphic Spreadsheet Interpreter.  This compiles a spreadsheet into code and simulates it out of Excel. This makes simulation extremely fast… in fact it is also used in Oracle Crystal Ball’s Extreme Speed Feature.

 

Loading the player ...

 

See on YouTube: PART 1: Introduction and Oracle Crystal Ball 11.1.2.1. Additive ModelKey Takeaways:

  • Formula & Function based – can be applied through interface/ribbon
  • Distribution logic is encapsulated in excel functions
  • Input definition feature needs work. Input naming is not handled well. And formulas resulting from the interface use absolute references. This translates into lots of manual formula editing.
  • No color coding feature. It’s up to the user to ensure modeling and color coding consistency
  • Cell Pop ups for inputs and outputs.  Basically, when you highlight a cell, the Simulation & Model Details appear.  A Cool feature.

 

PART 5:  Conclusion and Comparison

For this very simple additive model, all the packages are more than fully featured to handle it. Some wonder why use a sim tool at all.. Most of this stuff can be done natively in Excel. I would caution those users that most of us don’t have hours to run a simulation. These tools run the simulation and generate all the reporting in a few seconds.  You messed up? No problem, reset, correct and re-run. 

 

Loading the player ...

 

See on YouTube: PART 1: Introduction and Oracle Crystal Ball 11.1.2.1. Additive ModelKey Takeaways:

  • Crystal Ball is an efficient modeling package that is easy to use. Like the other tools, they have formulas but they cannot be included in the sensitivity analysis. So use these with care.
  • ModelRisk’s approach to building inputs  and outputs offers lots of flexibility but can introduce errors into model if the user is not paying attention. Same is to be said about the color coding.
  • @RISK is a very efficient modeling tool but some of the interface buttons could be clearer and easier to find. This makes rummaging around the interface more likely for inexperienced users.
  • Risk Solver is almost there but have some bugs in their input definition process that result in lots of extra work. Otherwise a very interesting solution.

In future posts, we will compare feature sets, as well as how each vendor has implemented their core features. Eventually we will move on to the big brother solutions – the professional, premium and industrial versions.
 

See the other viseos in the series  

If you have questions or suggestions for future comparisons, please don’t hesitate to call or drop me a line at [email protected]