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.      

Step 1: Review your spreadsheet

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.
• Constant: A quantity that is fixed by definition, such as for units conversion -- e.g. kWh/therm (100,000 British thermal units), or the baseline price for a product (e.g. $2,300).
• Objective: A variable such as net present value or ROI that you are trying to maximize or minimize.
• Chance: An input variable that is uncertain, that might be represented by a range of values or a probability distribution.
• General variable: Any variable which does not match the other types, or for which you have not yet determined an appropriate type.

• Functions:  If you need to apply the same calculation to several different objects, you may want to create a function that contains this calculation.  Then you can call the function wherever it is needed.  This reduces the number of places you need to enter the calculation and makes it easy to change the calculation if you need to do that later, instead of having to track down everywhere it occurs in the model. You can also translate a Visual Basic function into an Analytica function.

• Modules: These are the groups of information and formulas or rules that are logically connected in your model.

• Objectives: These are the answers that the model is to give.  These are not strictly necessary -- an Analytica model can work just fine without any objectives, but adding them makes the model clearer to understand.  In your model the objectives may be things like Total Assets, Total Equity, etc.  

Step 2: Create the modules

Modules let you organize a model into a hierarchy of manageable parts. It often makes sense to start by creating an Analytica module for each Excel worksheet. You can copy the name from each worksheet/tab and paste it into the corresponding module node. If a worksheet is particularly complex, you may want to organize it into submodules. As when creating any Analytica module, it's often best just to start adding and linking variables as you go. Whenever a diagram gets too cluttered, you can always rearrange the nodes and add submodules to make it clearer after you understand the underlying structure better.

Sometimes large spreadsheets have one or more worksheets that contain the main user inputs and results. It's often useful to make these into the user interface module(s) in Analytica. For a large Analytica model, it's often useful to create a "Common Indexes" module to contain all the Index variables that are used in more than one module. That makes it easier to find these indexes when you need them to create a new array.

Step 3: Create the Indexes

Large spreadsheets often use the same column or row headers for many tables — such as for year, month, region, product, or scenario. In Analytica, you should create a single index variable for each type of header, and reuse it for each table (array) that uses it. That will greatly simplify writing formulas with arrays that use the same index(es). You can either go through the spreadsheet to identify all these indexes early on -- or you can create them as you go. But, when you see a table that uses the same column or row headers as another one you already converted, remember to use the index you already created rather than making a new index with the same values.

To create a new index (in Edit mode in a Diagram), just drag from the Index icon in the toolbar, and type in the Index title into its node. You can copy its values directly from the spreadsheet. Set the Definition of the new Index to List, and paste in the values. It works whether the cells copied from the spreadsheet are horizontal (column headers) or vertical (row headers). If there is more than one cell, it will ask if you want to expand the index to accommodate.

Spreadsheets often have a single time Index, such as years, months, days. It is often best to use the built-in Analytica Index Time for that, giving it the appropriate values. For example, to set dates from 2014 to 2020, by month:

 Time := Sequence(MakeDate(2014), MakeDate(2020), dateUnit:'M')

Sometimes a spreadsheet will have several time-related indexes – e.g. historical data for past years vs. projected data for future years , or some tables projecting numbers for the next year by month, and others for the next decade by year. In those cases, you will want to make one index for each time range.

In your spreadsheet the candidates for indexes are:

• Years (Year 1 to Year 10)
• Platforms/Products (Architect ci8200, Architect c8000, ...)
• Support Tiers (Tier 1, Tier 2, ...)
• Value-Added Services (Service Delivery Guarantee, Inventory Management, eQC, ...)
• Resource Categories (# of People CSC, # of People FS, System/Operational Support, etc.)

Missing indexes

Sometimes, an Excel table doesn’t have column headers or row labels as an explicit index. Then you might create a new Index, with numbers 1 to n, or letters.

Indexes over Worksheets

Occasionally spreadsheets use Worksheets as a third dimension for a three dimensional table. For example, a business model may have three scenarios, Low, Nominal, and High, each on a separate worksheet. Each worksheet may have an (almost) identical structure, but with different values for some of the inputs, or even a few different formulas. In that case, it is often best to create an index

Index Scenarios := [‘Low’, ‘Nominal’, ‘High’]

You can then make a single model, based on just one of the worksheets. You need to mention the Scenarios index explicitly only in the definition of those variables that have different values or formulas for each worksheet (scenario).  Analytica will automatically propagate these and produce results indexed by Scenario.

Group Indexes into an Indexes module

A large model may use common indexes in many different worksheets. It is usually a good idea to group the Indexes in their own “Indexes” module, so you can easily find them when you need them.

Step 4: Create the Objectives

Create the objectives that you want from your spreadsheet. This will be any information that is optimized or considered to be the final output data.

Step 5: Create the input data tables

You will want to convert tables of input values from your spreadsheet into edit tables.

A decision node refers to the meaning behind the node: Is this something the user can decide or set?  The term edit table refers to the fact that the information can be changed (hence "edit"), and that it is an array of values (hence "table").  A decision node does not have to be an edit table (for example, it can be a yes/no decision), and an edit table does not have to be a decision node (for example, a constant or a variable can also be an edit table).

Edit tables

Any cell range of related inputs should be translated into an Edit table.

Step 6: Copy the Excel data into Analytica

You can copy and paste from Excel into Analytica, and vice versa, in the usual ways, using Ctrl+c and Ctrl+v.  See Copy and Paste between applications for more. When pasting, you select the top left cell, and it automatically fills out the Edit table in Analytica as in Excel. It will give a warning if the pasted region is larger than the Edit table.

Pasting row or column headers into an Index

When you create a new Analytica index, select List of text as its definition. To copy and Index value, you simply copy the row or column header from Excel and paste it into the first definition. Analytica will automatically expand the List to accommodate the length being pasted. When pasting horizontal row headers from Excel, Analytica will automatically transpose it into its preferred vertical list format.

Rearrange an edit table when needed

Sometimes Analytica shows a 1D or 2D Edit table with rows and columns transposed relative to the Excel input table. In Analytica you can simply use the Index pull-down menus to transpose the row and column indexes. Then you can copy and paste the table from Excel into Analytica with the Indexes in the appropriate position relative to the existing data are organized.

Step 7: Create the intermediate variables

You can create the intermediate variables by following the descendants the input data in the Excel spreadsheets forward to the final results.  (Conversely, you can start at the final results and trace backwards, looking at the cells that feed into the final results, then the cells that feed into them, and so on).  You will create the same path in the Analytica model.

To trace the inputs or outputs to see what affects what:

In Excel

1. Open the Formula Audit toolbar
2. Select cell (or range)
3. Click on Trace Dependents (or Precedents) to follow how the data is used in the spreadsheet.
4. If the dependents (or precedents) are on the same worksheet but off view, scroll until arrow tail (or head) is visible
5. If dependents (or precedents) are on another worksheet, double click precisely on the arrow tail (or head) (this is hard to do!)
6. Select the desired output (or input) from the list in the dialog box that comes up, scrolling if necessary. Note that all you see is a list of cell references, so it can be difficult to know which you want to look at.
7. Click ‘OK’ to go to the desired output (or input).

In Analytica

1. If inputs (or outputs) are in the same diagram, you can see which they are and their titles immediately by looking at the influence arrows.
2. If inputs (or outputs) are in a sub-module of the diagram, you can see which sub-module is involved immediately.
3. If inputs (or outputs) are in another sub-module (whether it is in diagram or not), press just to left (right) of node for a pull-down listing the input (output) variables by title.
4. Select a variable to open diagram highlighting selected variable.

Alternative:

1. Open attribute pane
2. Set attribute to Inputs (outputs)
3. Double click on selected input (output) to open diagram containing the input (output) highlighted.

Tables and arrays

A crucial issue in translating is how to group Excel cells to make Analytica arrays. Usually, when an Excel range (row, column, 2D, or even 3D array) is created by stretching a formula over the range, it should be converted into a single Analytica array. Basically, if the formulas are the same -- except for changes in row, column, and worksheet references created by stretching -- the range is a good candidate for single array. 

Tables with different formulas

Sometimes a table contains different formulas in different cells (different meaning more than just the effect of relative references).  There are several ways to handle this in Analytica. If just one (or a few) cell (row, column, slice) has a definition different to the others, you can represent it using an If statement:

Variable T := IF Year = 2005 THEN x ELSE y

 If many of the entries over an Index j are different, you can define it as a table or array: 

Variable T :=Table(j)(x1, x2, x3, x4)

Variable T := Array(j, [x1, x2, x3, x4])

Step 8: Test the model

You should test the Analytica model against the spreadsheet to verify that you have set everything up correctly.  See Building Effective Models in the Analytica User Guide for more extenisve advice on testing and debugging your model.

Analytica provides several tools to help in making sure your model is complete:

• It shows nodes with a missing or incomplete definition with hashed lines.
• It may give a warning if the definition of avariable is not consistent with its class, for example if you try to put multiple dimensions into an index.
• You can add a Check attribute to variables — for example, requiring that costs be positive numbers, so that it gives a warning when the check fails. See Check attribute for more details.

You can set or unset these options in the Preferences dialog.

Step 9: Design the user interface

Analytica provides a rich set of features to allow you to create the look and feel you want for the user interface (UI). The user interface can be largely separated from the technical issues of creating the model's analysis functionality, but is a process during which you can creatively consider how Analytica's UI can enhance the model you are translating from Excel. See Creating Interfaces for End Users in the Analytica User Guide for information and examples of ways to create the user interface.

Step 10: Add documentation

Even well designed models can be ineffective if the user does not know how to use them or does not understand the assumptions built into the model.  It is best to add a Description (and Units where appropriate) to each variable as you create it. You may forget what they mean, or just never get around to it, if you leave it to the end.

In the Preferences window you can put a check in the "Flag nodes w/descriptions" to see which nodes are missing their description.

Miscellaneous Tips

Number formats

Copying numbers with dollar (currency) signs:

Analytica can parse numbers preceded by currency (e.g. dollar) signs. However, it will ignore the dollar signs. To display the $ in Analytica, check the Currency box in the Number Format dialog from Result menu.

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.

See Also

• Excel to Analytica Mappings
• Working with Excel Worksheets using the Excel 2013 Object Model.
• Excel spreadsheets read and write
• Functions To Read Excel Worksheets
• Functions to Write Data to Excel Worksheets