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.  

Why can’t translation be automated?

An Analytica model contains higher level knowledge that is not explicitly present in most spreadsheets – for example, identifying a table or multidimensional array, knowing when arrays have a common index, separating user interfaces from model details, distinguishing types of variable (chance, decision, objective, and index). A sophisticated analyst may be able to detect these in a well-structured spreadsheet, but it takes human intelligence. Automating such translation requires a system with comparable intelligence, a challenging problem. So, for the moment, we must be satisfied with human translators. Fortunately, such translation can be quite rapid. 

Basic Steps

The following steps are a guideline to the order in which you can approach converting your Excel spreadsheet into you new Analytica model.

1.    Review your spreadsheet model identifying the sections that will be converted into the basic model objects in Analytica.

2.    Create the modules you will need.

3.    Create the indexes you will need.

4.    Create the objectives you want.

5.    Create the input data nodes: constants, decision nodes, etc.  Wherever possible these should be tables of data, not individual items.

6.    Copy the input data from your spreadsheet into the Analytica model.

7.    Follow forwards from the input data creating the intermediate variables that use that data until you get to the objectives.

8.    Test the model to make sure it does what you expect and want.

9.    Think about how to present this information to the user.  Here you can use Analytica's input and output nodes, button, modules and text nodes, to create forms, on-line help, highlight the key results, group results, etc.  You can also change the shape and color of nodes, add graphics, and other features to create the look and feel you want,

10.  Add documentation to the model.

This does not need to be a fixed process in Analytica; you can jump from step to step as desired.  For example, you may decide later on in the model development process to add a new module.  That is easy to do.  Existing nodes can simply be moved into the new module, if you wish, and additional nodes added.      

For detailed instructions explaining how to perform the 10 steps above, please see this paper A Guide to Translating from Excel to Analytica by Max Henrion and Richard Morgan.