Revision as of 23:31, 10 November 2015

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.

1. Review your spreadsheet

Go through your spreadsheet and identify what type of Analytica variable to use for each excel cell or range (table):

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.

Inputs that are fixed values you do not want the user to change (such as the baseline price for products) can be made constants. (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.)

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.
General variable
Constant: A quantity that is fixed by definition, such as for units conversion -- e.g. KWh/therm (100, 000 Britith thermal units)
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.

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.

2. Create the modules

Modules should correspond to how the users think about organizing the information. Different worksheets in an Excel spreadsheet often correspond to different modules. In addition it is often useful to group related Analytica nodes into modules. For example, all the indexes in a model can be grouped into an Indexes module, and groups of nodes that are used to perform some complex calculation can be tucked away in a model so that they do not clutter the model.

In a financial spreadsheet some candidates for making modules may be:

Critical Inputs
Income Statement
Cash Flow
Balance Sheet
ROI
Indexes

You may also want a "technical details" module.

3. Create the indexes

Large spreadsheets often repeat table headers (for columns or rows) many times — such as year, month, geographic region, organizational division, product, or scenario. In Analytica, you should create a single index variable for each type of header, which can be reused for each table to which it applies. When translating, it is helpful to start by going through the spreadsheet to identify all such table headers, and make a single corresponding index, and its associated list of labels or numbers. Then you can reuse each Index to identify table dimensions wherever appropriate.

Usually, you should create only a single Index for time, days, months, years, or whatever. But occasionally, a spreadsheet has different time ranges for different tables – e.g. historical data for past months vs. projected data for future months, or some tables projecting numbers for the next year by month, and others for the next decade by year. In those cases, of course, 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 show an explicit index as column headers or row labels. In such cases, you may want to create a new Analytica index, with numbers 1 to n, or letters.

Indexes over Worksheets

Large Excel models sometimes 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 has an (almost) identical structure, but with different values for some of the inputs, or even a few different formulas. In these cases, it is often best to create an index

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

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

Grouping 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.

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.

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.

6. Copying the Excel data into Analytica

You can cut and paste from Excel into Analytica, and vice versa. See the beginning of chapter 17 of the Analytica User Guide for more information.

Arranging rows and columns

From rows to Index definition

Sometimes you want to copy and paste a horizontal row of cells into the definition of an Index, as a list (of numbers or labels) that shows vertically. To do this, copy the row and first make it into a column in on scrap sheet in Excel, by using “Paste special…” from Excel’s Edit menu and checking the Transpose option. You can then copy the column and paste it into the Index definition -- after adding enough elements to the list to accommodate it.

Rearranging 2D arrays for copying values

Sometimes Analytica shows a 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 simply copy and paste the table from Excel into Analytica.

Rearranging 1D arrays for copying values

Analytica displays 1D edit tables vertically as a single column. If you want to paste an Excel row into such a table, there are two options:

Use the method described above “From rows to Index definition”, using Excel’s Transpose option in “Paste special…”

Add a second “dummy” dimension to the Edit table, with a single element. E.g. Index Dummy := [‘A’]. Just draw an arrow from the "dummy" node to the edit table variable to add the dimension. You can then simply transpose the dummy and original index, to display the Edit table as a row.

7. Create the intermediate variables

You can create the intermediate variables by following the descendents 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

Open the Formula Audit toolbar
Select cell (or range)
Click on Trace Dependents (or Precedents) to follow how the data is used in the spreadsheet.
If the dependents (or precedents) are on the same worksheet but off view, scroll until arrow tail (or head) is visible
If dependents (or precedents) are on another worksheet, double click precisely on the arrow tail (or head) (this is hard to do)
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’s hard to know which you want to look at.
Click ‘OK’ to go to the desired output (or input).

In Analytica

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.
If inputs (or outputs) are in a sub-module of the diagram, you can see which sub-module is involved immediately.
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.
Select a variable to open diagram highlighting selected variable.

Alternative:

Open attribute pane
Set attribute to Inputs (outputs)
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:

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:

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

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

8. Test the model

You should test the Analytica model against the spreadsheet to verify that you have set everything up correctly. See chapter 5 of the Analytica User Guide for advice on testing and debugging your model.

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

Nodes that do not have a complete and consistent definition are shown with slashed lines.
Analytica can check to make sure the class of a node is consistent with its definition
If you add data checks to your variables, Analytica can run these checks to make sure the data is valid — for example, requiring that costs be positive numbers. See chapter 8 of the Analytica User Guide for more details.
Warnings can be generated when the model is run indicating possible problems with the model.

These tools can be set or turned off in the Preferences window of Analytica. See chapter 4 of the Analytica User Guide.

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. The user interface can be largely separated from the technical issues of creating the model's analysis functionality. See chapter 9 of the Analytica User Guide for information and examples of ways to create the user interface.

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. Analytica allows you to add documentation to every node you create in the model.

In the Preferences window you can put a check in the "Flag nodes w/descriptions" to see which nodes have a description and which still do not.

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 (since release 4.0) should parse 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 Analytica User Guide 3.1 page 135.

Difference between revisions of "Excel to Analytica Translation"