BI Tools Will Never Replace Excel for Financial Modeling — But What Else Will?


In the beginning Excel and other spreadsheets were used for accounting, but also for managing lists of items, such as product inventories, assets, contacts, and employees — essentially small databases. Today it is almost common sense that you could do that in Excel, but for real-life applications you definitely should use other tool that are better suited for these tasks.

Apart from these “backward-oriented” applications which deal with storing data and processing information, Excel is also used for forward-looking tasks like planning, budgeting, financial modeling… The weaknesses and flaws of spreadsheets for modeling are basically the same as for data storage and analysis. For modeling, you can check them out here or here for example.

You shouldn’t use spreadsheets for accounting and managing data — and not for planning and modeling either.

That’s why many see the solution for replacing Excel for planning and modeling in the use of BI tools like Business Objects, Oracle BI, Hyperion, IBM Cognos, etc.

Sounds logical at first glance.

But there is a fundamental flaw.

Database and BI tools are great for data.

BI tools and databases are great for dealing with tons of data. Today’s world is full of data, and we are producing even more of it every day — exponentially more. That’s why “Big Data” is such a hype. And it’s necessary and useful to cope with this sea of data and make sense of it. No doubt.

With BI tools, you basically categorize, sort, compare, analyze, find out patterns, structure, etc. All this based on data. To do this, you need data.

The problem, though, is that data is only available for the past. Data is “traces” of things that have already happened. For the future — and with planning and financial modeling we only deal with the future — there is no data.

The data of the past was generated by some kind of “mechanism” — whether we know and understand it or not. We could call this mechanism the “business logic,” which is all the many things that happen and influence each other when you run a business:Customer decide to buy your product. You order supplies and materials. Customers pay 30 days after they got the invoice. You take out a loan, pay monthly interest, and repay it at the end. The value of your assets is decreasing, so you write them off. You spend money on advertising in hope for more customers to buy your products. And many more things…

For accounting, it’s enough to record the “monetary traces” of the business — revenues, expenses, assets, liabilities, cash flows…

For accounting, it’s enough to record the “monetary traces” of the business — revenues, expenses, assets, liabilities, cash flows… You don’t need to know what actually happened before a customer bought your product. You don’t need to know what were the rationales behind the supplies order, its size and timing. — As long as you recognize the revenues and expenses correctly everything is fine.

Financial modeling is not about data. It’s about logic.

When you need to come up with revenues and expenses for the future, however, youmust know the logic of your business. Capturing this logic is the primary task of financial modeling. Not adding up numbers, but building a model of the business logic that reflects what’s “behind the numbers” — or actually before ;)

To capture information about the logic, BI tools and databases are not well suited. They are good at storing “atomic” pieces of information but not relationships, influences, formulas… That’s why Excel is used. Not because you can handle numbers, but because you can use formulas to describe how numbers are generated.

Rather than a database we need kind of a“logicbase” for financial modeling.

So, if we want to replace Excel for building financial models, it’s not very clever to aim for BI-like tools, is it? It’s not always the hammer you need…

Rather than storing and manipulating data we want to store and manipulate thebusiness logic. So rather than a database we need kind of a “logicbase.” A tool that allows us to keep track of the dependencies and influences of a model. That allows us to write formulas in a compact, human-readable, understandable way. Formulas that can handle multi-dimensional data with ease, so you can build the most complex models that cope with the complexity of the reality you want to model.

A “logicbase” — How the hell is that supposed to work?

Sure, the term logicbase is a terrible word, and I’m going to drop it immediately and will never use it again. I just wanted to highlight that we don’t need something like a database — which is a better replacement for Excel for backward-looking, data-related tasks — to replace Excel for forward-looking jobs like financial modeling and planning.

But how should a software look like that is capable of “storing business logic?”

Well, to me there are three essential aspects:

#1 Define variables as a functions of others

A killer feature of Excel and other spreadsheets is that you define the value of a cell by putting in a formula that tells the machine how to compute this value based on other values. This is important to trace back the things that influence a value. You can always tell — sometimes easier, sometimes harder — why this value is like it is.

A database, in contrast, doesn’t store a formula but the actual number. That number might be produced by a formula or an algorithm somewhere in the application, e.g. when yearly numbers are broken down to quarterly or monthly figures. But looking at the monthly values won’t tell you why they have that awkward seasonal pattern…

So, the software should allow for defining a variable via a formula. Even better if the formulation language is clear and intuitive and uses variable names so that the business logic is captured in an understandable form.

In the example above the variable Passengers is defined by a formula that tells that it’s calculated by Landings times Passengers per landing. This captures the business logic. It is easy to read and understand. It allows you to detect where the numbers come from. And it ensures that the numbers are always calculated using these inputs, and updated when inputs change.

So, in comparison to a database where the record is the building block, defined by properties and “values,” the variable is the smallest element in a model, mainly defined by its formula — the “logic.”

#2 Keep track of relationships and influences

Understanding a model logic from looking at each element and each formula individually is hard. A machine is capable of this but the human brain usually works differently. Humans like to draw diagrams with boxes and arrows when they to want to explain what is influenced by what and how things relate to each other. That’s whyinfluence diagrams where developed in the 1970s.

Excel offers no way to visualize the overall structure of a model. Using cell references in formulas makes it laborious to find out which variables — or rather cells — depend on each other by tracing formulas from one cell to another. Of course, you can trace the inputs to a selected cell, e.g. by color-coding cell references to identify its inputs or with the “audit tool” that displays arrows from the inputs (or to the outputs) of a selected cell. These tools, however, can show the dependencies of only one, or a few, cells at a time.

Database and BI tools aren’t better suited for this either. This is because relationships between the value and its computational origin (the algorithm that might have calculated that value) are not “physically” connected like in a formula. The various attempts in these tools to come up with charts, maps, diagrams to visualize the structure of a planning model are, by nature, just an afterthought and kind of artificially put on top of the actual model.

Instead, a modeling software should treat the visual representation of the model as an integral part, ideally being the actual interface to interact with the model.

Analytica is a software package that does exactly this: Models are organized as influence diagrams. Variables (and other objects) appear as nodes of various shapes on a diagram, connected by arrows that provide a visual representation of dependencies. These influence diagrams may be hierarchical, i.e. a single module node on a diagram represents an entire sub model so that even very large, real-live models can be built and navigated visually.

The influence diagram, in this sense, might be the equivalent of the linked tables in a database.

#3 Separate formulas from “data”

Business information is multidimensional by nature. Products are sold per time unit, by region, by distribution channel, etc. Database and BI tools are well suited for organizing multidimensional data — of the past.

When building financial models for the future, at least some of this multidimensionality is required, too. In Excel, dealing with multidimensional data is hard, laborious, and error-prone. 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. Each cell must contain a separate formula, even when all the cells in the table have identical relationships with the cells of other tables. Excel offers a relatively convenient way to create such tables by “stretching” one cell across the table — provided you use correct absolute and relative cell referencing in the initial formula. But, after you have created the table, the fact that all the formulas are essentially the same gets lost. Indeed, 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.

So, ideally, a modeling software should overcome these weaknesses of Excel and provide a way to write formulas without worrying about the dimensionality of the ingredients…

The same example from above shows that the variables Landings andPax_per_landings, which are used in the definition, are no single numbers but actually three-dimensional arrays indexed by Model_period, Region, andFlight_type. To calculate the actual values for Passengers, Analytica repeats the calculation over each dimension, but recognizes when the same dimension occurs in both values and treats it as the same dimension during the calculation.

This way, formulas are separated from the actual “data.” This allows that new dimensions can be introduced or removed from an existing model, without requiring changes to the model structure or changes to variable definitions.

Certainly, there are other features a financial modeling and planning software should offer, but these three are, in my opinion, the ones that are critical for dealing with “forward-looking” information — and the ones where BI tools are of no help.

BI tools will never replace Excel for financial modeling — but powerful visual software like Analytica eventually will…

Nobody using Excel for financial modeling will honestly deny the fact that spreadsheets are only the second best solution for the task. They are flexible and apparently easy to use, okay, but this comes at a price. There are numerous studies and examples showing that Excel models are extremely error-prone — if not dangerous. (At the end of the article you find a list of good reads on the topic)

Obviously, there is a need to replace Excel. But replacing it by tools that rely on the concept of “data” (a.k.a. professional planning software) won’t be a good replacement. A modeling tool must be able to deal with variables, influences, modules, hierarchies, dimensions and arrays. And it should, ideally, display these things in a human-friendly, visual way so the modeler can interact with them directly and intuitively.

BI tools are a hammer where a screwdriver is needed…

What do you think?

Let’s talk.

And here comes the list of some good reads on spreadsheet errors, problems, and dangers:

  • cio.com — Eight of the Worst Spreadsheet Blunders
  • bancpath.com — Modeling Problems in Excel
  • bbc.com — Financial modelling — is the government wasting millions?
  • nytimes.com — The Excel Depression
  • forbes.com — Microsoft’s Excel Might Be The Most Dangerous Software On The Planet

Originally published at www.linkedin.com.