# Excel to Analytica Translation

This guide outlines how to translate a spreadsheet in Microsoft Excel, or other application, into Analytica. It isn’t possible to fully automate the process, 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 great 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 explicit in most spreadsheets – for example, it identifies each table or multidimensional array as a single element, where Excel just treats them as a group of cells; it identifies the index of each table or array dimension, reusing the same index for arrays that use the same dimension, not just a header row or column; it distinguishes types (Classes) of variable (decision, chance, objective, or index). An experienced spreadsheet modeler may see these in a well-structured spreadsheet, but it takes human intelligence. Automating such translation would requires a system with comparable intelligence, which is a challenging problem. So, for the moment, we must be satisfied with human translators. Fortunately, such translation is quite rapid once you get the knack.

## Basic Steps

These are the basic steps to convert your Excel spreadsheet into Analytica. We describe them in more detail below.

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.

Go through your spreadsheet and identify what type of Analytica variable to use for each Excel cell or range (table). At this point in the process, don't worry too much about whether something should be a decision node or a constant, or even a variable, you can change the type of an object in the model at any time. Detailed explanations on when to use which type of Analytica variable can be found Classes of variables and other objects in the Analytica User Guide, with short descriptions presented below:

• Indexes: These will be the dimensions of your arrays of data. Indexes usually correspond to row and column headings in the Excel spreadsheet, particularly when the same headings are used in more than one place.
• Edit Tables: These are where the user enters the choices and input data that the model will use.  For example, the prices for various products, the cost of resources, etc
• Variables: These normally contain intermediate data based on applying formulas and rules to the input data.  For example, in your model, the price for products for each year is based on a formula that takes the starting price and increases it by a fixed percentage each year.  The price of a product can be modeled as a variable that is a function of a decision variable (e.g., the annual price increase percentage) and an constant (the baseline price). Spreadsheets usually don't identify the role or class of each variable, but it is often useful to use these distinctions corresponding to Classes of Variable in Analytica:
• Decision: A variable that the decision maker can change directly, such as a bid or offer price, or a budget allocation.

#### Numbers with commas separating thousands

Analytica parses numbers like “1,000,000” when pasted into a definition or Edit table. By default, Analytica won't display the commas separating the thousands. But, you can display the commas, by checking the Thousands Separators box in the Number Format dialog from Result menu.

#### Mixed number formats in a table

Analytica usually applies the same format to all the numbers in a table. However, it is possible to create a table with different formats for different slices by defining a table as a list of variables in square brackets, e.g. Y := [A, B, C].  If Y has no number format itself, it will display the slice corresponding to A, B, and C, using the number format defined for each of those variables. See Number formats in the Analytica User Guide.