Excel to Analytica Translation

Translating from Excel to Analytica

Introduction

This guide outlines how to translate a spreadsheet in Microsoft Excel, or other spreadsheet application, into Analytica. It isn’t possible to fully automate the translation, but an experienced Analytica user can do the translation quite rapidly — with the aid of this Guide.  If you’re not an experienced Analytica user, doing a spreadsheet translation is a good way to learn Analytica. 

Why translate a spreadsheet into Analytica?

Translating a spreadsheet into Analytica usually creates dramatic improvements in:

• Clarity: Definitions using meaningful variable names are much easier to understand than meaningless cell references. Analytica’s modular influence diagrams let you create a clear visual representation of the variables and their dependencies, organized into a hierarchy of modules.
• Organization: Organizing a complex model into a hierarchy of modules makes each module easier to understand, and if well-chosen, makes elements easier to find.
• Simplicity: A multi-megabyte spreadsheet often results in an Analytica file that is 10 to 100 times smaller, with a corresponding reduction in the effort needed to write, verify, understand, and communicate the model. This is even though the Analytica model includes more information, notably the hierarchical influence diagram views. A major reason is Analytica’s Intelligent Arrays™, which enable you to use a single definition for a variable to replace an Excel table that contains hundreds, thousands, or even more cells each with its own formula.
• Uncertainty: It is much easier to add uncertainty analysis into an Analytica model, using it’s built-in probability distributions and Monte Carlo features.
• Extensibility: Intelligent arrays mean it is easy to modify or add a dimension of a model, simply by editing or adding an Index. The change automatically propagates throughout the model with no effort. The only task left is to enter data into any Edit tables indexed by an extended index. This makes extending a model vastly easier than a spreadsheet.
• Execution speed: Analytica models often run an order of magnitude faster than the equivalent spreadsheet. Models with large arrays benefit especially from the efficiency of Analytica's Intelligent Arrays features.

Reliability: Some people have translated a spreadsheet into Analytica just to verify that the spreadsheet has no errors. Recent research on spreadsheets find that a high percentage (50 to 90% depending on the study) contain serious errors. While it is not impossible to make an error in Analytica, an Analytica model offers many fewer opportunities for errors, and makes it easier to catch many errors. For example, the influence diagrams will show clearly if you have a variable depending on the wrong other variables. More importantly, by checking the results of the spreadsheet against the Analytica results, you have a way to “triangulate”. This is a powerful way to surface errors — much more effective than even the most thorough spreadsheet audit protocol.