What's Wrong with Spreadsheets?

The spreadsheet was the first “killer app,” the application which led to millions of people buying their first personal computers in the early 1980s. Spreadsheets were a brilliant innovation for replacing accountants’ paper spreadsheets. But, they are poorly suited for serious financial modeling. Empirical studies show that more than half of spreadsheets in regular operational use have serious errors. Thirty-five years after the release of VisiCalc, the first spreadsheet, it’s time for something better.

A modeling tool like Analytica that displays entities and lets you directly interact with them is much more intuitive to use. It reduces the need to mentally translate between “inner representations” and the one used by the software. Analytica makes it much easier to write, review, verify, explain, and extend models. It reduces the number of errors by preventing many kinds of errors from being made in the first place, and by making remaining errors easier to detect and fix.

The Top 10 problems with Excel and how Analytica offers solutions.

1. Meaningless names

Formulas in Excel refer to other variables using cell addresses—such as B2 for column B of row 2—rather than meaningful names like Revenues or Expenses. Analytica uses meaningful b=names to identify each variable.
 
Benefit:  Formulas are much easier to  write, read, and debug. 

2. No structured documentation

As an Excel modeler, you can type text as documentation into any cell that doesn’t contain a number or formula. The text may include the name, title, units, or even an explanation. It is conventional, but far from universal, to place this text in the cell to the left of each cell containing a number or formula. There is no consistent relationship between documentation and the formal model (numbers and formulas).
 
Without any inherent link between documentation and model, the spreadsheet cannot reliably assist the user by prompting for documentation, or maintaining consistency between documentation and model.
 
In Analytica, each variable is an object with fields for name, description, units, as well as definition (formula) and value.
 
Benefit:  Formulas are much easier to  write, read, and debug. 

Benefit: You can easily include clear, complete, model documentation as you go.

3. Variables don't have defined types or roles

An Excel cell can contain a number, formula, text value, documentary text, or empty space. It can be an input, output, or intermediate calculation—a decision variable, a constant, the index of a table, or an objective to be optimized—among many other things.

The problem is that a cell is just a cell as far as the spreadsheet is concerned. There is no explicit representation of what role it is intended to play in the model.  Without representing this information explicitly, Excel can’t assist you by providing appropriate options, checking that the contents of the cell is consistent with its role, and so forestalling common conceptual errors. In Analytica, each object has a class, such as decision, constant, chance variable, objective, or index, defining its role in the model.

Benefit:  The modeler and software both understand the role of each variable – which prevents common conceptual errors.

4. Invisible model structure

Excel offers no easy way to visualize the overall structure of a model. Analytica has influence diagrams that provide an intuitive graphical view, depicting variables as nodes and dependencies as arrows.

Benefit:  Modelers and decision makers  communicate clearly with each other about key assumptions and model structure. It’s easy to navigate large models. 

5. Little support for modularity

Excel supports one level of modularity: organizing a workbook into a number of worksheets, accessible by tabs along the bottom.
If you use multiple worksheets to represent a third dimension of a table, e.g. region or business unit, it will interfere with even this kind of modularity.
In Analytica, you organize a large model as a hierarchy of simple comprehensible modules.

Benefit: Complex models become manageable. Each diagram shows key variables and relations, and hides irrelevant details in submodules.

6. Formulas refer to cells not tables

Most Excel models consist largely of tables with one or two dimensions. You are forced to work at the level of cells rather than tables because Excel formulas usually refer to individual cells—and not to tables.
If someone changes a single formula in a table—by design or by accident—it is very difficult to detect, which is a common source of mistakes.
 
With Analytica’s Intelligent Arrays, a single formula expresses an operation on named tables. It automatically iterates over all dimensions.

Benefit: The number of formulas to write, verify, and debug is often 100 to 1,000 times less, hugely reducing chances for error.

7. Editing or adding a dimension requires considerable effort

When you build a model, the most important and challenging decisions are typically about how much detail to include in table dimensions. How far ahead should your time horizon be? What about dividing up by sales regions, by product or type?
 
Ideally, you should start out simply and experiment with adding detail to see how it affects the results.
 
Unfortunately, this is usually too much work when building an Excel model. Changing the size of a dimension or adding a dimension to Excel tables requires much more effort than is necessary.
Analytica understands the indexes that identify dimensions  of a table or array. 

Benefit: Editing or adding a dimension automatically updates all affected tables and keeps formulas correct.

8. No treatment of uncertainty

Many quantities in practical financial modeling are not accurate. It is often helpful to represent those uncertainties explicitly as probability distributions, so that you can evaluate and manage the risks, and discover which sources of uncertainty are the most important.
 
Excel doesn’t let you express probabilistic uncertainties directly—although there are add-ins that make this possible.
 
In Analytica, the value of any variable can be a probability distribution. Efficient Monte Carlo simulation generates the corresponding distribution on results.
 
Benefit:  Modelers without special training can treat uncertainties explicitly and analyze risks.
 
Benefit:  Formulas are much easier to  write, read, and debug. 

9. Minimal support for sensitivity analysis

Often the most potent source of insights from modeling arises from sensitivity analysis. With it, you can figure which inputs and assumptions have the most effect on the results—and why.  Excel also provides powerful tools for scenario analysis, allowing you to define and compare the results of scenarios. Parametric analysis or tornado analysis is much harder to achieve and requires a lot of manual work.
 
Automated sensitivity analysis shows the relative effect of each input or uncertain variable on affected outcomes and decisions.

Benefit: Modelers without special training can easily do importance analyses and generate valuable insights.

10. No separation of end-user interface from the model logic

Excel models are usually built by an expert user in an organization and used by others. Ideally, the builder identifies the input cells clearly by color or shading, and locks all the other cells to prevent accidental or deliberate tampering. However, audits of operational spreadsheets find that builders often fail to maintain this clear separation and locking, mainly because Excel provides them little assistance in doing so.
 
In Analytica, it is easy to create “dashboards” for users to access key inputs and outputs.
 
Benefit: Dashboard offers a simple user interface for model users, protecting them from seeing or messing with forbidden cell details.
Scroll to Top